July 15, 2011 at 6:21 am
GilaMonster (7/15/2011)
The one case that comes to mind is a staging table. If I'm importing data from some other system and I know that it's got data integrity issues, I'll use a staging table with no keys at all. No benefits to having keys on the table and having them could cause the data import to fail.
Excellent example.
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
July 15, 2011 at 6:53 am
GilaMonster (7/15/2011)
The one case that comes to mind is a staging table. If I'm importing data from some other system and I know that it's got data integrity issues, I'll use a staging table with no keys at all. No benefits to having keys on the table and having them could cause the data import to fail.
Dear Sir,
I will add a Column Like Entry No. and define it as Primary Key in the Table. I will increment the value of this Column at the Time of Data Insertion in this CASE.
Advantage(s) :
1. Easy to Count how many rows(Records) are there.
2. Easy to differentiate a row due this Entry No. (Suppose Others Rows have the Same Value).
Note :- If adding a New Column is not disturbing any thing.
July 15, 2011 at 7:01 am
Subrata Bauri (7/15/2011)
GilaMonster (7/15/2011)
Dear Sir,I will add a Column Like Entry No. and define it as Primary Key in the Table. I will increment the value of this Column at the Time of Data Insertion in this CASE.
Not everyone on this forum is a Sir and quite frankly I know of some very smart Ladies that are the most bulbs on the Christmas Tree. Sorry not politically correct.
I missing something but you blew me away with the sugesstion to increment the value of the primary key.:-D
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
July 15, 2011 at 7:39 am
Advantage(s) :
1. Easy to Count how many rows(Records) are there.
This really won't let you get an accurate count. It will work until you delete a record. If you want an arbitrary number as your primary key use an identity. Trying to maintain your own key like this is not worth the hassles of making it work correctly. You have to take into account so many things. Just don't do it. Set a int/bigint column as identity and move on.
2. Easy to differentiate a row due this Entry No. (Suppose Others Rows have the Same Value).
That is pretty much the definition of a primary key. You can now identify the row by a unique value.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
July 15, 2011 at 8:30 am
Welsh Corgi (7/15/2011)
Not everyone on this forum is a Sir and quite frankly I know of some very smart Ladies that are the most bulbs on the Christmas Tree.
Definitely not a sir here. But I'm so used to being called 'sir' that it really doesn't bother me any longer.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 15, 2011 at 8:33 am
Subrata Bauri (7/15/2011)
I will increment the value of this Column at the Time of Data Insertion in this CASE.
If you're doing an INSERT.. SELECT, or bulk-insert, all the rows will have the same insert date/time. Manually managing an incrementing column is a nightmare, seriously there are better things to spend a week or so on.
1. Easy to Count how many rows(Records) are there.
Primary key has no advantages there. Want to count rows - select count(*).
2. Easy to differentiate a row due this Entry No. (Suppose Others Rows have the Same Value).
Supposing I wanted something like that I could use a row_number or an identity without having to make it the primary key. With a staging table that I'm going to load, clean and truncate there are few advantages to bothering with a primary key at all.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 16, 2011 at 5:19 am
GilaMonster (7/15/2011)
Jayanth_Kurup (7/15/2011)
However the first can be achieved simply by adding a identity column while the second is driven by the kind of data being inserted into the table.Identities alone don't prevent duplicates. I can insert a specific value into an identity column multiple times, SQL won't complain. To enforce uniqueness you need a primary key, unique constraint or unique index.
Yes we are on the same page , The primary key enforces uniqueness on the columns which are part of its definition. I never suggested adding a primary key on the identity column would prevent duplicates from being inserted in other column. Or that an indentity column would maintain uniqueness on its own without a primary key.
The same applies for unique indexes as well , i figured it didn't need to be called out separately.
July 16, 2011 at 5:23 am
Subrata Bauri (7/15/2011)
Jayanth_Kurup (7/15/2011)
Enforcing primary key on a table is purely a design decision , while sql doesn't force it; it allows suitable substitutes in columns such as identity columns , timestamps etc .The role of the primary key is dual :-
To help uniquely identify each row in the table
To ensure duplicate rows are not inserted .
However the first can be achieved simply by adding a identity column while the second is driven by the kind of data being inserted into the table.
SQL will not intuitively decide one over the other for you.
Dear sir,
I know the role of the primary key in a Table very well.
I think design a Table with the Primary key(s) is logically correct compare to without Primary key concept.
Can you Show me that you are getting more advantage(s) from a non-primary Key Table compare to a Table which has primary key.
Note :- Suppose both the Table have more or less same structure.
Like Gail ( btw its Maam 🙂 ) said a staging table is a good example, similarly a mapping table much like the FACT in a DW would be another example.
July 16, 2011 at 5:27 am
Subrata Bauri (7/15/2011)
GilaMonster (7/15/2011)
The one case that comes to mind is a staging table. If I'm importing data from some other system and I know that it's got data integrity issues, I'll use a staging table with no keys at all. No benefits to having keys on the table and having them could cause the data import to fail.Dear Sir,
I will add a Column Like Entry No. and define it as Primary Key in the Table. I will increment the value of this Column at the Time of Data Insertion in this CASE.
Advantage(s) :
1. Easy to Count how many rows(Records) are there.
2. Easy to differentiate a row due this Entry No. (Suppose Others Rows have the Same Value).
Note :- If adding a New Column is not disturbing any thing.
[/b]
All the above come down to the last line you mentioned , its purely a design choice.
I.e if its not disturbing anything why not include it and while you choose to others may not.
July 16, 2011 at 8:02 am
Subrata Bauri (7/15/2011)
Why does SQL Server allow to create a Table Without a Primary key ?What is the intention behind this ?
If you are not planning to enforce Referential Integrity on the particular table then PK is not needed.
By the way, not only SQL Server allows for it - that's a feature of all RDBMS out there.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.July 17, 2011 at 11:20 pm
Sean Lange (7/15/2011)
This really won't let you get an accurate count. It will work until you delete a record. If you want an arbitrary number as your primary key use an identity. Trying to maintain your own key like this is not worth the hassles of making it work correctly. You have to take into account so many things. Just don't do it. Set a int/bigint column as identity and move on.
The PK will be int/bigint and it will increment by 1,2,3 ...
The Last value of this column will show the numbers of the Total Records.
That is pretty much the definition of a primary key. You can now identify the row by a unique value.
Yes.
July 17, 2011 at 11:25 pm
PaulB-TheOneAndOnly (7/16/2011)
If you are not planning to enforce Referential Integrity on the particular table then PK is not needed.
By the way, not only SQL Server allows for it - that's a feature of all RDBMS out there.
Thanks !:-P
July 18, 2011 at 3:12 am
Subrata Bauri (7/17/2011)
Sean Lange (7/15/2011)
This really won't let you get an accurate count. It will work until you delete a record. If you want an arbitrary number as your primary key use an identity. Trying to maintain your own key like this is not worth the hassles of making it work correctly. You have to take into account so many things. Just don't do it. Set a int/bigint column as identity and move on.
The PK will be int/bigint and it will increment by 1,2,3 ...
The Last value of this column will show the numbers of the Total Records.
Assuming no deletes, assuming no mistakes in your assignment of it.
Seriously, manually assigning an identity is a poor idea. Way too much that needs to be taken into account to get it right.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 18, 2011 at 7:03 am
Subrata Bauri (7/15/2011)
Jayanth_Kurup (7/15/2011)
Enforcing primary key on a table is purely a design decision , while sql doesn't force it; it allows suitable substitutes in columns such as identity columns , timestamps etc .The role of the primary key is dual :-
To help uniquely identify each row in the table
To ensure duplicate rows are not inserted .
However the first can be achieved simply by adding a identity column while the second is driven by the kind of data being inserted into the table.
SQL will not intuitively decide one over the other for you.
Dear sir,
I know the role of the primary key in a Table very well.
I think design a Table with the Primary key(s) is logically correct compare to without Primary key concept.
Can you Show me that you are getting more advantage(s) from a non-primary Key Table compare to a Table which has primary key.
Note :- Suppose both the Table have more or less same structure.
Welsh Corgi stated one of the best reasons. Staging table which may contain duplicate raw data that needs to be cleaned.
--Jeff Moden
Change is inevitable... Change for the better is not.
July 19, 2011 at 12:27 am
And some of us just like to denormalise just for the fun of it.
😛
Sorry couldn't help myself
Viewing 15 posts - 16 through 30 (of 32 total)
You must be logged in to reply to this topic. Login to reply