Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««34567»»

Design Expand / Collapse
Author
Message
Posted Friday, September 24, 2010 9:22 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Thursday, August 28, 2014 6:00 AM
Points: 3,022, Visits: 912
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.
Post #992894
Posted Friday, September 24, 2010 11:39 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Wednesday, July 16, 2014 9:36 AM
Points: 340, Visits: 406
Where is the option for violating the rule "Don't attempt to cram 10 table's worth of entities and attributes into 1..."
Post #992996
Posted Friday, September 24, 2010 1:28 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, July 14, 2014 9:47 AM
Points: 2,919, Visits: 2,525
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
Post #993054
Posted Friday, September 24, 2010 2:19 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, June 16, 2014 9:38 AM
Points: 2,163, Visits: 2,189
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.
Post #993095
Posted Friday, September 24, 2010 2:34 PM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Friday, August 29, 2014 10:33 AM
Points: 551, Visits: 652
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.
Post #993102
Posted Friday, September 24, 2010 2:36 PM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Monday, August 18, 2014 7:25 AM
Points: 989, Visits: 1,823
Well, you CAN use SET IDENTITY_INSERT ON to create duplicates. Unless you have a unique constraint nothing prevents this from happening.
Post #993106
Posted Friday, September 24, 2010 2:43 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, June 16, 2014 9:38 AM
Points: 2,163, Visits: 2,189
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.
Post #993111
Posted Saturday, September 25, 2010 3:35 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Tuesday, August 26, 2014 4:51 AM
Points: 1,854, Visits: 3,451
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.
Post #993240
Posted Monday, September 27, 2010 7:17 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Friday, August 29, 2014 10:33 AM
Points: 551, Visits: 652
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.
Post #993631
Posted Monday, September 27, 2010 8:56 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Friday, August 29, 2014 1:51 PM
Points: 21,644, Visits: 15,317
Thanks for the ?



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


SQL RNNR

Posting Performance Based Questions - Gail Shaw
Posting Data Etiquette - Jeff Moden
Hidden RBAR - Jeff Moden
VLFs and the Tran Log - Kimberly Tripp
Post #993718
« Prev Topic | Next Topic »

Add to briefcase «««34567»»

Permissions Expand / Collapse