|
Another issue caused by using natural keys instead of surrogates (identities) is index fragmentation. Because identities are sequential there are less page splits required for inserts on clustered indexes (as well as other indexes). When you use a sequential identity the new record is appended to the "end" of the index. This certainly still causes page splits when the leaf nodes must balance the tree, but compare this to when you use a natural key the values you are inserting are almost always being inserted somewhere in the middle of a range of leaf nodes.
If your natural key is used a foreign key and you need to insert a large number of child records, then you're inserting a large number of records right in the middle of your index. Causing more fragmentation.
As your database grows in size this will increase the amount of maintenance your indexes will require to constantly rebuild and defragment them to maintain performance.
That brings us to read performance. With a surrogate key the data you need most often is physically adjacent. Think of reporting on orders or events which all occured w/in a specified time period. Yes you could use a clustered index on a date column, but that means 8 bytes of storage for each entry before you get to the leaf nodes - twice what's required for an INT. But with a natural key your data would be all over the map. Disk I/O is the worst bottleneck in your entire system. Imagine how much more work the disk heads need to work when the data is randomly placed, compared with scanning data in adjacent sectors.
You can use more padding on your indexes to reduce fragmentation and increase performance for modifications but this increases the I/O required to read your index on top of the fact that the natural key has already increased the size of your index in the first place.
And that is just the performance reasons for using surrogate keys. There are other arguments against natural keys.
In case you hadn't guessed, I don't think natural keys should ever be used - with one small exception. If we're talking about a very small set of possible keys which are tied to some sort of lookup data. The reason for this exception is that they are the exception to the performance problems cited above. Additionally, by using natural keys for a small dataset you can often store only the key in the child tables and use some sort of enumeration in your application code. This means you'll have less overhead because you won't have to maintain tables for all the different lookup types in your application.
Hope this helps your delimma. Of course you will need to decide what works best for your requirements and weight them to determine what the most important factors are in developing your application.
Mark's blog: developMENTALmadness.blogspot.com
Funniest variable name:
lLongDong - spotted in legacy code, was used to determine how long a beep should be. - Dave Bacher
|
|
|
|
|
Just a small point on clustering tables.
You probably know this but if you cluster tables that have a high rate of delete/insert/update transactions on them - clustering may not be a good idea with a very large table.
If you have to cluster then an identity index can make sense due to the insertions being added at the end.
Just bear in mind that OLTP heavy tables do not always lend themselves to clustering.
Hopefully I'm not teaching my grandmother to suck eggs too much here...
You always pass failure on the way to success.
|
|
|
|
|
I have a DateTime column in my SQL database with a regular entry looking like this: 2008/03/01 08:57:06 PM
Now when I want to use a calender to be able to choose between what dates I must find specific entries, it gives me no result. Is that because of a clash between what the calender is asking for and what the database is saying?
|
|
|
|
|
Probably. Have you tried formatting the date from the database?
Bob
Ashfield Consultants Ltd
|
|
|
|
|
yes. I have currently set my date format in my database to yyyy/mm/dd hh:mm:ss as it is a datetime type
|
|
|
|
|
A little tip - if you convert your SQL dates as follows:
select convert(char(8),getdate(),112)
For 11 April 2008 you will get a result of "20080411".
Format your VS dates in the same manner.
Now the advantage of changing the date to this format is that you can do comparisons with confidence.
You always pass failure on the way to success.
|
|
|
|
|
will this allow me to use the calender function on VS to select certain data from within two dates? As my data is in a datetime format in my database.
All I want to do is to select data from x date to y date and pull all the data from within that period. And with the format I have at the moment it is messing me around.
|
|
|
|
|
Hi!
I would like to put in a variable in a xquery function.
I would like to dynamicly increase the [1] number.
Content.value('(//mislife/insurance/party/@pno)[1]', 'varchar(max)')
The line above works!
This is how I failed.
declare @iCounter int
set iCounter = 1
Content.value('(//mislife/insurance/party/@pno)' + str(@iCounter) , 'varchar(max)')
The error I receive from SQL:
"The argument 1 of the xml data type method "value" must be a string literal."
I have tried with cast(1 as varchar(max)) and whith the str() function but the compilater wont accept it.
Is it possible the change/break up this type of string?
/Tommy
|
|
|
|
|
It looks like you're trying to perform some iterative operation. Have you looked at FLWOR? http://technet.microsoft.com/en-us/library/ms190945.aspx[^]
I haven't had cause to use much XQuery in SQL, but it would seem this is better suited. If that's not what you're looking for have you tried constructing the string outside the value() function? Sometimes SQL doesn't like it when you try and pass operations to parameters and you have to pass a ready-made value. Like this:
<br />
SET @xQueryExpression = '(//mislife/insurance/party/@pno)' + str(@iCounter)<br />
Content.value(@xQueryExpression, 'varchar(max)')<br />
Mark's blog: developMENTALmadness.blogspot.com
Funniest variable name:
lLongDong - spotted in legacy code, was used to determine how long a beep should be. - Dave Bacher
|
|
|
|
|
Thank you!
The .value can just handle an complete string. I have desided to do the operation in .net code instead.
I will read the data into xmlnodes.
Thank you very much!
/Tommy
|
|
|
|
|
Just got asked about this, so how can we do it? I'm pretty sure that they want it into SQL, but we also have a DB2 environment, so if anyone knows how to do that it would be cool.
|
|
|
|
|
I think you can do it via SSIS? If not, you can always use OLEDB to pull the data and fill the SQL...
|
|
|
|
|
Now i am binding the ddl with DS which created from my table.
But it wll affect my original table,So i make another temp table having same structure of my original table and start creating ds from that with updations. So tell me some tech. that wll avoid creation of tem table as wll as hold record temporary in structures like ArrayList,RecordSet,DataTable etc.
Plz help me for that..
modified on Friday, April 11, 2008 8:52 AM
|
|
|
|
|
I am storing images in SQL server in binary format... now i want to display those images using image control.. so can any one help me for the same...
Jeevamohan
|
|
|
|
|
Image.FromStream - read the data out into a stream and then use this command to generate an appropriate image.
|
|
|
|
|
Hi All,
I have got a problem with an mdb file which is not opening.My friend has sent me one mdb file of her project. I downloaded it but it is giving a message as follows:
"The file is located outside the intranet or on an untrusted site.MS Access will not open the file due to potential security problems.
To open the file copy it to your machine or an accessible network."
Please solve my problem as it is very important for me....
Thank You
Gayatri
|
|
|
|
|
|
|
I'm Using Asp.Net with C# & SSRS 2005 for Generating Reports..
I'm Having HUGE Data in Microsoft Excel Sheets .. I want to Get this Data from this Microsoft Excel Sheets & to Generate REPORTS in SSRS 2005..
Please give me the Suugestions for Doing this..
Thank You..
|
|
|
|
|
Good Morning All
i have the Following quey,that i want to put their results into a table like this
SELECT DISTINCT(SS.FUNCTION_KEY),P.FUNC_KEY,P.PROPERTY_ID,SS.LIS_KEY,P.LIS_KEY,SS.FLOOR_AREA,P.ACTUAL_EXTENT<br />
Into Final_Area_GEOSS FROM SDE.PROPERTY P INNER JOIN Scheme_Area_GEOSS SS ON<br />
P.LIS_KEY = SS.LIS_KEY AND SS.FUNCTION_KEY = SUBSTRING(P.FUNC_KEY,1,5)+'0'+substring(P.FUNC_KEY,6,3)<br />
WHERE P.FUNC_KEY LIKE 'GEOSS%' <br />
order by ss.lis_key,p.lis_key
when i run this Query i get an Error that says
Msg 2705, Level 16, State 3, Line 1
Column names in each table must be unique. Column name 'LIS_KEY' in table 'Final_Area_GEOSS' is specified more than once.
i understands what it mean, but it does not matter how many lis_key repeat itself, as long as that data gets into the table.
how can i achieve that
thanks
Vuyiswa Maseko,
Sorrow is Better than Laughter, it may Sadden your Face, but It sharpens your Understanding
VB.NET/SQL7/2000/2005
http://vuyiswamb.007ihost.com
http://Ecadre.007ihost.com
vuyiswam@tshwane.gov.za
|
|
|
|
|
Your problem is you cannot have duplicate column names in a table - obviously - and in your query you select SS.LIS_KEY twice. You need to alias one or both of them for example:
SELECT DISTINCT(SS.FUNCTION_KEY),P.FUNC_KEY,P.PROPERTY_ID,SS.LIS_KEY as Geoss_LIS_KEY,P.LIS_KEY as Property_Lis_Key,SS.FLOOR_AREA,P.ACTUAL_EXTENT<br />
Into Final_Area_GEOSS FROM SDE.PROPERTY P INNER JOIN Scheme_Area_GEOSS SS ON<br />
P.LIS_KEY = SS.LIS_KEY AND SS.FUNCTION_KEY = SUBSTRING(P.FUNC_KEY,1,5)+'0'+substring(P.FUNC_KEY,6,3)<br />
WHERE P.FUNC_KEY LIKE 'GEOSS%' <br />
order by ss.lis_key,p.lis_key
That will fix your problem.
Bob
Ashfield Consultants Ltd
|
|
|
|
|
You are a Star my man,
thanks it works
Vuyiswa Maseko,
Sorrow is Better than Laughter, it may Sadden your Face, but It sharpens your Understanding
VB.NET/SQL7/2000/2005
http://vuyiswamb.007ihost.com
http://Ecadre.007ihost.com
vuyiswam@tshwane.gov.za
|
|
|
|
|
Thats OK. By the way, you should not do it this way as it locks the system tables in the database for the duration of the query. That prevents any other queries using them and could have a big perforamnce hit.
I would do it like this
SELECT DISTINCT(SS.FUNCTION_KEY),P.FUNC_KEY,P.PROPERTY_ID,SS.LIS_KEY as Geoss_LIS_KEY,P.LIS_KEY as Property_Lis_Key,SS.FLOOR_AREA,P.ACTUAL_EXTENT<br />
Into Final_Area_GEOSS FROM SDE.PROPERTY P INNER JOIN Scheme_Area_GEOSS SS ON<br />
P.LIS_KEY = SS.LIS_KEY AND SS.FUNCTION_KEY = SUBSTRING(P.FUNC_KEY,1,5)+'0'+substring(P.FUNC_KEY,6,3)<br />
<big>WHERE 1 = 2 </big>
which creates an empty table (almost) instantly, and then
<big>Insert Into Final_Area_GEOSS </big><br />
SELECT DISTINCT(SS.FUNCTION_KEY),P.FUNC_KEY,P.PROPERTY_ID,SS.LIS_KEY as Geoss_LIS_KEY,P.LIS_KEY as Property_Lis_Key,SS.FLOOR_AREA,P.ACTUAL_EXTENT<br />
FROM SDE.PROPERTY P INNER JOIN Scheme_Area_GEOSS SS ON<br />
P.LIS_KEY = SS.LIS_KEY AND SS.FUNCTION_KEY = SUBSTRING(P.FUNC_KEY,1,5)+'0'+substring(P.FUNC_KEY,6,3)<br />
WHERE P.FUNC_KEY LIKE 'GEOSS%'
to populate it.
Bob
Ashfield Consultants Ltd
|
|
|
|
|
Thanks it worked.
Vuyiswa Maseko,
Sorrow is Better than Laughter, it may Sadden your Face, but It sharpens your Understanding
VB.NET/SQL7/2000/2005
http://vuyiswamb.007ihost.com
http://Ecadre.007ihost.com
vuyiswam@tshwane.gov.za
|
|
|
|
|
interesting... could you please let me in on a secret how this works?  it is techniques like this i would like to know... TIA!
I am a SysAdmin, I battle my own daemons.
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
