SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Design


Design

Author
Message
Rune Bivrin
Rune Bivrin
SSCarpal Tunnel
SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)

Group: General Forum Members
Points: 4370 Visits: 1554
MHilsher (9/24/2010)
Rune,

I've got some similar situations in a database that I'm working with and found that the alternative seems to be just as bad. We've created functions to get and set min and max dates and usually will default a date to the f_MinDate() just so we don't have NULLs in the database. But now you still have to check if the value is "MinDate" and if so leave it out of your comparison.

That usually has me writting code that looks like:
WHERE ( Table.Date = f_MinDate() OR Table.Date BETWEEN @StartDate AND @EndDate )

Not sure if that is much better? And it gets even uglier if you allow the start and end dates of the period in question to be allowed to be null. Don't really have an answer to the issue, seems like you're damned if you do and damned if you don't.


Well, that's a different scenario where the range is in your parameters and the single date is in the table. I would definitely advocate NULL there, because no default will make sense there. My case is when the range is in the table, and you provide a single date as a parameter.
In your case it depends on whether entries with NULL are to be included in the range or not. If not you don't need to do anything, otherwise it's
WHERE (Table.DateCol IS NULL OR Table.DateCol BETWEEN @StartDate AND @EndDate)

The optimizer has no issue with that.


Just because you're right doesn't mean everybody else is wrong.
getoffmyfoot
getoffmyfoot
Ten Centuries
Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)

Group: General Forum Members
Points: 1020 Visits: 412
Where is the option for violating the rule "Don't attempt to cram 10 table's worth of entities and attributes into 1..."
sjimmo
sjimmo
SSCertifiable
SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)

Group: General Forum Members
Points: 5346 Visits: 2907
Where is the option for violating the rule "Don't attempt to cram 10 table's worth of entities and attributes into 1..."


OFF WITH THEIR FINGERS;-)

Steve Jimmo
Sr DBA
“If we ever forget that we are One Nation Under God, then we will be a Nation gone under." - Ronald Reagan
UMG Developer
UMG Developer
SSCarpal Tunnel
SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)

Group: General Forum Members
Points: 4728 Visits: 2204
sknox (9/24/2010)
So that gives us 2 identifier columns, remarkably similar to the two in this QotD -- and that's where you may have a point. But I'd caution here as well. First, we need to know why that surrogate UID is there.


No, this QotD only has one identifier:


OrderRefNo UNIQUEIDENTIFIER NOT NULL CONSTRAINT DF_OrderRefNo DEFAULT NEWID() CONSTRAINT PK_Order PRIMARY KEY CLUSTERED,



The other column, OrderNo, wouldn't qualify as a surrogate key or identifier in my book:


OrderNo INT NOT NULL IDENTITY(1,1),



The reason is there is no INDEX or CONSTRAINT that prevents duplicate values. Essentially is it just an INT field with a DEFAULT that creates sequential numbers, but someone can easily add rows with duplicate values.
Robert Frasca
Robert Frasca
SSCommitted
SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)

Group: General Forum Members
Points: 1945 Visits: 798
UMG Developer (9/24/2010)
sknox (9/24/2010)
So that gives us 2 identifier columns, remarkably similar to the two in this QotD -- and that's where you may have a point. But I'd caution here as well. First, we need to know why that surrogate UID is there.


No, this QotD only has one identifier:


OrderRefNo UNIQUEIDENTIFIER NOT NULL CONSTRAINT DF_OrderRefNo DEFAULT NEWID() CONSTRAINT PK_Order PRIMARY KEY CLUSTERED,



The other column, OrderNo, wouldn't qualify as a surrogate key or identifier in my book:


OrderNo INT NOT NULL IDENTITY(1,1),



The reason is there is no INDEX or CONSTRAINT that prevents duplicate values. Essentially is it just an INT field with a DEFAULT that creates sequential numbers, but someone can easily add rows with duplicate values.


I'm confused as to why you don't think a field with the IDENTITY attribute is not a surrogate key or identifier. The only reason to use the IDENTITY attribute is to create a surrogate key or identifier. It's not possible to create duplicate values either as the value automatically increments by the value in the second position of the declaration.

Did I miss something here?

"Beliefs" get in the way of learning.
jeff.mason
jeff.mason
Hall of Fame
Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)

Group: General Forum Members
Points: 3205 Visits: 2137
Well, you CAN use SET IDENTITY_INSERT ON to create duplicates. Unless you have a unique constraint nothing prevents this from happening.
UMG Developer
UMG Developer
SSCarpal Tunnel
SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)

Group: General Forum Members
Points: 4728 Visits: 2204
Robert Frasca (9/24/2010)
I'm confused as to why you don't think a field with the IDENTITY attribute is not a surrogate key or identifier. The only reason to use the IDENTITY attribute is to create a surrogate key or identifier. It's not possible to create duplicate values either as the value automatically increments by the value in the second position of the declaration.

Did I miss something here?


Yes, IDENTITY does not guarantee non-duplicate values by itself. (A common misconception.)

As Jeff Mason said you can use SET IDENTITY_INSERT ON and then insert rows with duplicate values unless the column has a CONSTRAINT defined on it to prevent it. (PRIMARY KEY, UNIQUE INDEX, etc.)

The reason to use IDENTITY is to get a sequential set of values by default for a column, it doesn't become valid as a surrogate key/identifier until it is guaranteed to not ever contain duplicates.
Nils Gustav Stråbø
Nils Gustav Stråbø
SSCarpal Tunnel
SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)

Group: General Forum Members
Points: 4327 Visits: 3575
sjimmo (9/24/2010)
Hugo Kornelis (9/24/2010)
--------------------------------------------------------------------------------
if the table is used in replication, ..., SQL Server requires that a surrogate key with the UNIQUEIDENTIFIER data type is also present in the table.


I cannot speak for FILESTREAM, but in order to replicate a table, there must be a primary key on the table. There is not a requirement for UNIQUEIDENTIFIER.

For merge replication there must be a uniqueidentifier.
Robert Frasca
Robert Frasca
SSCommitted
SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)

Group: General Forum Members
Points: 1945 Visits: 798
UMG Developer (9/24/2010)
Robert Frasca (9/24/2010)
I'm confused as to why you don't think a field with the IDENTITY attribute is not a surrogate key or identifier. The only reason to use the IDENTITY attribute is to create a surrogate key or identifier. It's not possible to create duplicate values either as the value automatically increments by the value in the second position of the declaration.

Did I miss something here?


Yes, IDENTITY does not guarantee non-duplicate values by itself. (A common misconception.)

As Jeff Mason said you can use SET IDENTITY_INSERT ON and then insert rows with duplicate values unless the column has a CONSTRAINT defined on it to prevent it. (PRIMARY KEY, UNIQUE INDEX, etc.)

The reason to use IDENTITY is to get a sequential set of values by default for a column, it doesn't become valid as a surrogate key/identifier until it is guaranteed to not ever contain duplicates.


Well, I suppose you could do that but that defeats the purpose of using the identity attribute. I always make the column with the identity attribute the primary key. (Not necessarily the clustered key.) I can't think of any sensible scenario where I would want to allow duplicate identities. It's the IDENTITY attribute not the "quasi-unless-I-change-my-mind-Identity" attribute. Why bother? As soon as I allow that, it's just another integer column. Besides, the SET IDENTITY_INSERT ON isn't, in my opinion, really intended as a mechanism for inserting duplicates. It's a mechanism for inserting rows that already have identities. I've used it many times particulary when performing some sort of data integration.

"Beliefs" get in the way of learning.
SQLRNNR
SQLRNNR
SSC Guru
SSC Guru (82K reputation)SSC Guru (82K reputation)SSC Guru (82K reputation)SSC Guru (82K reputation)SSC Guru (82K reputation)SSC Guru (82K reputation)SSC Guru (82K reputation)SSC Guru (82K reputation)

Group: General Forum Members
Points: 82236 Visits: 18575
Thanks for the ?



Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server, MVP


SQL RNNR

Posting Performance Based Questions - Gail Shaw

Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search