|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Identity or GUID?OK Guys,
We are considering replacing the standard IDENTITY column with the UNIQUEIDENTIFIER column for all our primary keys. Good? Bad? Crazy? WTF!? Any thoughts on index clustering, performance, portability, managability, etc... would be appreciated. Robert Hi
http://www.sql-server-performance.com/clustered_indexes.asp "rmg66" <rgwathney__xXx__primepro.com> wrote in message news:eZPZpnWiGHA.4284@TK2MSFTNGP05.phx.gbl... OK Guys, We are considering replacing the standard IDENTITY column with the UNIQUEIDENTIFIER column for all our primary keys. Good? Bad? Crazy? WTF!? Any thoughts on index clustering, performance, portability, managability, etc... would be appreciated. Robert >> We are considering replacing the standard IDENTITY column with the UNIQUEIDENTIFIER column for all our primary keys. Good? Bad? Crazy? WTF!? << I'd give it a "WTF++" , but you see this kind of disaster all the time.Somebody might want to read a book on RDBMS. By definition, this thing has no keys and therefore no tables. I would start with a full data audit just to find how bad things are. And I will bet that there are lots of orphaned rows, no DRI actions, redundant duplicates, etc. Instead of mimicing a sequential file system, why don't you look for industry standard identifiers? (answer: because it would require research and professionalism instead of chance to start programming immediately) When you use things that you can actually validate and verify in the real world, you then have to do that extra work. Gerts in the way of "Cowboy Coding" Things that can survive an audit, port and be maintained.are nice to have in a business. > Things that can survive an audit, port and be Yes a NICE TO HAVE.> maintained.are nice to have in a business. It would also be NICE TO HAVE a decent standard SQL language that works out in the real world and doesn't have to be supplemented we properitary features. -- Show quoteHide quoteTony Rogerson SQL Server MVP http://sqlblogcasts.com/blogs/tonyrogerson - technical commentary from a SQL Server Consultant http://sqlserverfaq.com - free video tutorials "--CELKO--" <jcelko***@earthlink.net> wrote in message news:1149627837.141445.183740@c74g2000cwc.googlegroups.com... >>> We are considering replacing the standard IDENTITY column with the >>> UNIQUEIDENTIFIER column for all our primary keys. Good? Bad? Crazy? >>> WTF!? << > > I'd give it a "WTF++" , but you see this kind of disaster all the time. > > > Somebody might want to read a book on RDBMS. By definition, this thing > has no keys and therefore no tables. I would start with a full data > audit just to find how bad things are. And I will bet that there are > lots of orphaned rows, no DRI actions, redundant duplicates, etc. > > Instead of mimicing a sequential file system, why don't you look for > industry standard identifiers? (answer: because it would require > research and professionalism instead of chance to start programming > immediately) > > When you use things that you can actually validate and verify in the > real world, you then have to do that extra work. Gerts in the way of > "Cowboy Coding" Things that can survive an audit, port and be > maintained.are nice to have in a business. > From what I have read and understand Indexes which gets built on GUID wud be
pretty heavy and so it might have a performance hit. http://www.thescripts.com/forum/thread82632.html -- this might help. Best Regards Vadivel http://vadivel.blogspot.com Show quoteHide quote "rmg66" wrote: > OK Guys, > > We are considering replacing the standard IDENTITY column with the UNIQUEIDENTIFIER column for all our primary keys. > > Good? Bad? Crazy? WTF!? > > Any thoughts on index clustering, performance, portability, managability, etc... would be appreciated. > > Robert > > Hi Robert,
Why? The only reason I can think is that you are moving more to a distributed database architecture and you want to guarentee that the surrogate key (its not really a primary key, the primary key is part of your data) is unique across databases. You can still use IDENTITY but encode a site ID into the schema. NEWID() is random in its generation so the insert will be random across your index so, you will cause additional IO because the data will be more spread across the disk (array) so, you might end up with more locking contention too. In a word - don't do it. Oh, also - its a lot harder to debug and 'see' guids when you are working with the data under DBA mode ;). -- Tony Rogerson SQL Server MVP http://sqlblogcasts.com/blogs/tonyrogerson - technical commentary from a SQL Server Consultant http://sqlserverfaq.com - free video tutorials "rmg66" <rgwathney__xXx__primepro.com> wrote in message We are considering replacing the standard IDENTITY column with the news:eZPZpnWiGHA.4284@TK2MSFTNGP05.phx.gbl... OK Guys, UNIQUEIDENTIFIER column for all our primary keys. Good? Bad? Crazy? WTF!? Any thoughts on index clustering, performance, portability, managability, etc... would be appreciated. Robert 2005 will have a new function called:
newsequentialid http://msdn2.microsoft.com/en-us/library/ms189786.aspx for 2000 http://www.sqldev.net/xp/xpguid.htm is an option to overcome the "randomness" of NEWID() There is a performance hit for using NEWID() in 2000. I won't deny that. However.... . The big advantage of using GUIDS is that I can Create my Relationships OUTSIDE of tsql code, aka, (for me) inside DotNet code. Read my previous post at: http://groups.google.com/group/microsoft.public.sqlserver.programming/browse_thread/thread/567a033a448e7a10/2be27c58c993dab7?lnk=st&q=%22the+Relationship+outside%22&rnum=2&hl=en#2be27c58c993dab7 I don't know if there is a super correct answer. It depends on what you got going on. Personally, me and my company are making great strides to get the business logic OUT OF THE Database, and into the business layer. See http://www.codeproject.com/gen/design/DudeWheresMyBusinessLogic.asp for more info Most times, I'm going with some kind of GUID usage, but not the NEWID stuff. If replication is in your plans, then you need to seriously consider abandoning IDENTITY's. But you should research and judge for yourself. Show quoteHide quote "Tony Rogerson" <tonyroger***@sqlserverfaq.com> wrote in message news:%23ih$yCXiGHA.3904@TK2MSFTNGP02.phx.gbl... > Hi Robert, > > Why? The only reason I can think is that you are moving more to a > distributed database architecture and you want to guarentee that the > surrogate key (its not really a primary key, the primary key is part of your > data) is unique across databases. > > You can still use IDENTITY but encode a site ID into the schema. > > NEWID() is random in its generation so the insert will be random across your > index so, you will cause additional IO because the data will be more spread > across the disk (array) so, you might end up with more locking contention > too. > > In a word - don't do it. > > Oh, also - its a lot harder to debug and 'see' guids when you are working > with the data under DBA mode ;). > > -- > Tony Rogerson > SQL Server MVP > http://sqlblogcasts.com/blogs/tonyrogerson - technical commentary from a SQL > Server Consultant > http://sqlserverfaq.com - free video tutorials > > > "rmg66" <rgwathney__xXx__primepro.com> wrote in message > news:eZPZpnWiGHA.4284@TK2MSFTNGP05.phx.gbl... > OK Guys, > > We are considering replacing the standard IDENTITY column with the > UNIQUEIDENTIFIER column for all our primary keys. > > Good? Bad? Crazy? WTF!? > > Any thoughts on index clustering, performance, portability, managability, > etc... would be appreciated. > > Robert > > > > I don't know if there is a super correct answer. You've missed the boat Sloan, the current thinking is to put the business > It depends on what you got going on. > Personally, me and my company are making great strides to get the business > logic OUT OF THE Database, and into the business layer. > logic back into the database because its centralised and easier to manage - plus you get better resource usage through cached execution code etc... Google Jim Gray and look up some of his thinking on this. -- Show quoteHide quoteTony Rogerson SQL Server MVP http://sqlblogcasts.com/blogs/tonyrogerson - technical commentary from a SQL Server Consultant http://sqlserverfaq.com - free video tutorials "sloan" <sl***@ipass.net> wrote in message news:%23UU$cfXiGHA.3408@TK2MSFTNGP05.phx.gbl... > 2005 will have a new function called: > newsequentialid > http://msdn2.microsoft.com/en-us/library/ms189786.aspx > > for 2000 > http://www.sqldev.net/xp/xpguid.htm > is an option to overcome the "randomness" of NEWID() > > > There is a performance hit for using NEWID() in 2000. I won't deny that. > > However.... . > The big advantage of using GUIDS is that I can > Create my Relationships OUTSIDE of tsql code, aka, (for me) inside DotNet > code. > > > Read my previous post at: > > http://groups.google.com/group/microsoft.public.sqlserver.programming/browse_thread/thread/567a033a448e7a10/2be27c58c993dab7?lnk=st&q=%22the+Relationship+outside%22&rnum=2&hl=en#2be27c58c993dab7 > > > I don't know if there is a super correct answer. > It depends on what you got going on. > Personally, me and my company are making great strides to get the business > logic OUT OF THE Database, and into the business layer. > > See > http://www.codeproject.com/gen/design/DudeWheresMyBusinessLogic.asp > for more info > > > Most times, I'm going with some kind of GUID usage, but not the NEWID > stuff. > If replication is in your plans, then you need to seriously consider > abandoning IDENTITY's. > > But you should research and judge for yourself. > > > "Tony Rogerson" <tonyroger***@sqlserverfaq.com> wrote in message > news:%23ih$yCXiGHA.3904@TK2MSFTNGP02.phx.gbl... >> Hi Robert, >> >> Why? The only reason I can think is that you are moving more to a >> distributed database architecture and you want to guarentee that the >> surrogate key (its not really a primary key, the primary key is part of > your >> data) is unique across databases. >> >> You can still use IDENTITY but encode a site ID into the schema. >> >> NEWID() is random in its generation so the insert will be random across > your >> index so, you will cause additional IO because the data will be more > spread >> across the disk (array) so, you might end up with more locking contention >> too. >> >> In a word - don't do it. >> >> Oh, also - its a lot harder to debug and 'see' guids when you are working > >> with the data under DBA mode ;). >> >> -- >> Tony Rogerson >> SQL Server MVP >> http://sqlblogcasts.com/blogs/tonyrogerson - technical commentary from a > SQL >> Server Consultant >> http://sqlserverfaq.com - free video tutorials >> >> >> "rmg66" <rgwathney__xXx__primepro.com> wrote in message >> news:eZPZpnWiGHA.4284@TK2MSFTNGP05.phx.gbl... >> OK Guys, >> >> We are considering replacing the standard IDENTITY column with the >> UNIQUEIDENTIFIER column for all our primary keys. >> >> Good? Bad? Crazy? WTF!? >> >> Any thoughts on index clustering, performance, portability, managability, >> etc... would be appreciated. >> >> Robert >> >> >> > > Not trying to pick a fight, Tony, but you're one of the few that I've
seen advocate this. I haven't read any of Jim Gray's stuff, but I'll take a look at it. It seems odd to me to put business logic back into the database because of the issues of object-relational impedance, scalability, and general performance considerations. I prefer clean seperation, myself; I'm even beginning tto question the need for stored procs because of the failure to seperate business logic from data retrieval. Stu Tony Rogerson wrote: Show quoteHide quote > > I don't know if there is a super correct answer. > > It depends on what you got going on. > > Personally, me and my company are making great strides to get the business > > logic OUT OF THE Database, and into the business layer. > > > > You've missed the boat Sloan, the current thinking is to put the business > logic back into the database because its centralised and easier to manage - > plus you get better resource usage through cached execution code etc... > > Google Jim Gray and look up some of his thinking on this. > > -- > Tony Rogerson > SQL Server MVP > http://sqlblogcasts.com/blogs/tonyrogerson - technical commentary from a SQL > Server Consultant > http://sqlserverfaq.com - free video tutorials > > > "sloan" <sl***@ipass.net> wrote in message > news:%23UU$cfXiGHA.3408@TK2MSFTNGP05.phx.gbl... > > 2005 will have a new function called: > > newsequentialid > > http://msdn2.microsoft.com/en-us/library/ms189786.aspx > > > > for 2000 > > http://www.sqldev.net/xp/xpguid.htm > > is an option to overcome the "randomness" of NEWID() > > > > > > There is a performance hit for using NEWID() in 2000. I won't deny that. > > > > However.... . > > The big advantage of using GUIDS is that I can > > Create my Relationships OUTSIDE of tsql code, aka, (for me) inside DotNet > > code. > > > > > > Read my previous post at: > > > > http://groups.google.com/group/microsoft.public.sqlserver.programming/browse_thread/thread/567a033a448e7a10/2be27c58c993dab7?lnk=st&q=%22the+Relationship+outside%22&rnum=2&hl=en#2be27c58c993dab7 > > > > > > I don't know if there is a super correct answer. > > It depends on what you got going on. > > Personally, me and my company are making great strides to get the business > > logic OUT OF THE Database, and into the business layer. > > > > See > > http://www.codeproject.com/gen/design/DudeWheresMyBusinessLogic.asp > > for more info > > > > > > Most times, I'm going with some kind of GUID usage, but not the NEWID > > stuff. > > If replication is in your plans, then you need to seriously consider > > abandoning IDENTITY's. > > > > But you should research and judge for yourself. > > > > > > "Tony Rogerson" <tonyroger***@sqlserverfaq.com> wrote in message > > news:%23ih$yCXiGHA.3904@TK2MSFTNGP02.phx.gbl... > >> Hi Robert, > >> > >> Why? The only reason I can think is that you are moving more to a > >> distributed database architecture and you want to guarentee that the > >> surrogate key (its not really a primary key, the primary key is part of > > your > >> data) is unique across databases. > >> > >> You can still use IDENTITY but encode a site ID into the schema. > >> > >> NEWID() is random in its generation so the insert will be random across > > your > >> index so, you will cause additional IO because the data will be more > > spread > >> across the disk (array) so, you might end up with more locking contention > >> too. > >> > >> In a word - don't do it. > >> > >> Oh, also - its a lot harder to debug and 'see' guids when you are working > > > >> with the data under DBA mode ;). > >> > >> -- > >> Tony Rogerson > >> SQL Server MVP > >> http://sqlblogcasts.com/blogs/tonyrogerson - technical commentary from a > > SQL > >> Server Consultant > >> http://sqlserverfaq.com - free video tutorials > >> > >> > >> "rmg66" <rgwathney__xXx__primepro.com> wrote in message > >> news:eZPZpnWiGHA.4284@TK2MSFTNGP05.phx.gbl... > >> OK Guys, > >> > >> We are considering replacing the standard IDENTITY column with the > >> UNIQUEIDENTIFIER column for all our primary keys. > >> > >> Good? Bad? Crazy? WTF!? > >> > >> Any thoughts on index clustering, performance, portability, managability, > >> etc... would be appreciated. > >> > >> Robert > >> > >> > >> > > > > Hi Stu.
hmm, nice clean black and white stuff. I wish it was that simple. I used to think like that myself, but found that it is far better to find the balancing point where business logic is split between the data access layer and the database itself. This allows the business logic to take place in the place where it can operate with the most performance. My understanding on this goes a little like a history lesson.... 1. The bad old days - no stored procs - everything done via ad-hoc SQL, All business logic Had to happen on the Application Layer, as there was no place for it in the DB. 2. Client-Server days - stored procs added - was supposed to put all the business logic into the DB. (yeah right) 3. Three tier - Application layer deals with display, Middle tier for all business logic, database for storing data. 4. n-tier. basically, like Three Tier, but the line get blurred, and there could be lost more tiers. My currenting thinking of n-tier is something like this... (it'll probably change in the morning) 1. Front end - deals with basic input validation, and display to the users. Nothing more. 2. Application layer - This is the first place for business logic, validates inputs for correctness. 3. business layer - processes stuff, does more validation and whatever else needs to be done. 4. data access layer - Again, more validation, initiates calls to the db layer, but still isn't database specifc. 5. db layer - this is where the stored proces lie. alot of data processing is done here. 6. data layer - this is where the actual tables are modified. Here's where things get complicated, none of these layers are hard and fast - there's no reason why the data access layer can't communicate directly with the data layer. The Front-end may bypass the application layer and go directly to the business layer. There's no hard and fast rule. This allow enough flexibility to place the business logic in the place where the biggest bang for the buck can take place. I have a routine as work which performs a search, the user enters data into the Front End, then this data is sent unvalidated to the db layer, via the application layer. The search is then executed directly on the db layer, and the result is returned back to the application layer, and on to the front end to display. This skipped 3 of the steps as it wasn't needed. But there may be times when more layer's are needed as the routine is more complicated. But the layers can be switched in and out as required. What language do each of these layer's represent? it does not matter, you can change the language as often as you like. Regards Colin Dawson www.cjdawson.com Show quoteHide quote "Stu" <stuart.ainswo***@gmail.com> wrote in message news:1149625630.387982.203380@g10g2000cwb.googlegroups.com... > Not trying to pick a fight, Tony, but you're one of the few that I've > seen advocate this. I haven't read any of Jim Gray's stuff, but I'll > take a look at it. It seems odd to me to put business logic back into > the database because of the issues of object-relational impedance, > scalability, and general performance considerations. > > I prefer clean seperation, myself; I'm even beginning tto question the > need for stored procs because of the failure to seperate business logic > from data retrieval. > > Stu > > > Tony Rogerson wrote: >> > I don't know if there is a super correct answer. >> > It depends on what you got going on. >> > Personally, me and my company are making great strides to get the >> > business >> > logic OUT OF THE Database, and into the business layer. >> > >> >> You've missed the boat Sloan, the current thinking is to put the business >> logic back into the database because its centralised and easier to >> manage - >> plus you get better resource usage through cached execution code etc... >> >> Google Jim Gray and look up some of his thinking on this. >> >> -- >> Tony Rogerson >> SQL Server MVP >> http://sqlblogcasts.com/blogs/tonyrogerson - technical commentary from a >> SQL >> Server Consultant >> http://sqlserverfaq.com - free video tutorials >> >> >> "sloan" <sl***@ipass.net> wrote in message >> news:%23UU$cfXiGHA.3408@TK2MSFTNGP05.phx.gbl... >> > 2005 will have a new function called: >> > newsequentialid >> > http://msdn2.microsoft.com/en-us/library/ms189786.aspx >> > >> > for 2000 >> > http://www.sqldev.net/xp/xpguid.htm >> > is an option to overcome the "randomness" of NEWID() >> > >> > >> > There is a performance hit for using NEWID() in 2000. I won't deny >> > that. >> > >> > However.... . >> > The big advantage of using GUIDS is that I can >> > Create my Relationships OUTSIDE of tsql code, aka, (for me) inside >> > DotNet >> > code. >> > >> > >> > Read my previous post at: >> > >> > http://groups.google.com/group/microsoft.public.sqlserver.programming/browse_thread/thread/567a033a448e7a10/2be27c58c993dab7?lnk=st&q=%22the+Relationship+outside%22&rnum=2&hl=en#2be27c58c993dab7 >> > >> > >> > I don't know if there is a super correct answer. >> > It depends on what you got going on. >> > Personally, me and my company are making great strides to get the >> > business >> > logic OUT OF THE Database, and into the business layer. >> > >> > See >> > http://www.codeproject.com/gen/design/DudeWheresMyBusinessLogic.asp >> > for more info >> > >> > >> > Most times, I'm going with some kind of GUID usage, but not the NEWID >> > stuff. >> > If replication is in your plans, then you need to seriously consider >> > abandoning IDENTITY's. >> > >> > But you should research and judge for yourself. >> > >> > >> > "Tony Rogerson" <tonyroger***@sqlserverfaq.com> wrote in message >> > news:%23ih$yCXiGHA.3904@TK2MSFTNGP02.phx.gbl... >> >> Hi Robert, >> >> >> >> Why? The only reason I can think is that you are moving more to a >> >> distributed database architecture and you want to guarentee that the >> >> surrogate key (its not really a primary key, the primary key is part >> >> of >> > your >> >> data) is unique across databases. >> >> >> >> You can still use IDENTITY but encode a site ID into the schema. >> >> >> >> NEWID() is random in its generation so the insert will be random >> >> across >> > your >> >> index so, you will cause additional IO because the data will be more >> > spread >> >> across the disk (array) so, you might end up with more locking >> >> contention >> >> too. >> >> >> >> In a word - don't do it. >> >> >> >> Oh, also - its a lot harder to debug and 'see' guids when you are >> >> working >> > >> >> with the data under DBA mode ;). >> >> >> >> -- >> >> Tony Rogerson >> >> SQL Server MVP >> >> http://sqlblogcasts.com/blogs/tonyrogerson - technical commentary from >> >> a >> > SQL >> >> Server Consultant >> >> http://sqlserverfaq.com - free video tutorials >> >> >> >> >> >> "rmg66" <rgwathney__xXx__primepro.com> wrote in message >> >> news:eZPZpnWiGHA.4284@TK2MSFTNGP05.phx.gbl... >> >> OK Guys, >> >> >> >> We are considering replacing the standard IDENTITY column with the >> >> UNIQUEIDENTIFIER column for all our primary keys. >> >> >> >> Good? Bad? Crazy? WTF!? >> >> >> >> Any thoughts on index clustering, performance, portability, >> >> managability, >> >> etc... would be appreciated. >> >> >> >> Robert >> >> >> >> >> >> >> > >> > > I'm not necessarily advocating that the business logic NEVER belongs in
the database; I, too, work in the real world. I'm just advocating a cleaner design, with decisions made ahead of time. Too often I walk into a shop and see a split middle tier, with some business logic done in the databas, and some business logic done in the application layer. Very rarely is there ever a data access layer at all, unless you count half of the stored procedures, and half of the embedded SQL as a DAL. I agree, there are times when it makes sense to have business logic in the database; more often than not, however, the business rules I encounter are more complicated than SQL (or T-SQL) can handle well (not saying it can;t be done; just saying it's probably better handled by an OOP language). I just think that a clean design should make a decision about the business logic, and stick to it. Colin Dawson wrote: Show quoteHide quote > Hi Stu. > > hmm, nice clean black and white stuff. I wish it was that simple. I used > to think like that myself, but found that it is far better to find the > balancing point where business logic is split between the data access layer > and the database itself. This allows the business logic to take place in > the place where it can operate with the most performance. > > My understanding on this goes a little like a history lesson.... > > 1. The bad old days - no stored procs - everything done via ad-hoc SQL, All > business logic Had to happen on the Application Layer, as there was no place > for it in the DB. > 2. Client-Server days - stored procs added - was supposed to put all the > business logic into the DB. (yeah right) > 3. Three tier - Application layer deals with display, Middle tier for all > business logic, database for storing data. > 4. n-tier. basically, like Three Tier, but the line get blurred, and there > could be lost more tiers. > > My currenting thinking of n-tier is something like this... (it'll probably > change in the morning) > > 1. Front end - deals with basic input validation, and display to the users. > Nothing more. > 2. Application layer - This is the first place for business logic, validates > inputs for correctness. > 3. business layer - processes stuff, does more validation and whatever else > needs to be done. > 4. data access layer - Again, more validation, initiates calls to the db > layer, but still isn't database specifc. > 5. db layer - this is where the stored proces lie. alot of data processing > is done here. > 6. data layer - this is where the actual tables are modified. > > Here's where things get complicated, none of these layers are hard and > fast - there's no reason why the data access layer can't communicate > directly with the data layer. The Front-end may bypass the application > layer and go directly to the business layer. There's no hard and fast rule. > > This allow enough flexibility to place the business logic in the place where > the biggest bang for the buck can take place. > I have a routine as work which performs a search, the user enters data into > the Front End, then this data is sent unvalidated to the db layer, via the > application layer. The search is then executed directly on the db layer, > and the result is returned back to the application layer, and on to the > front end to display. > > This skipped 3 of the steps as it wasn't needed. But there may be times > when more layer's are needed as the routine is more complicated. But the > layers can be switched in and out as required. What language do each of > these layer's represent? it does not matter, you can change the language as > often as you like. > > Regards > > Colin Dawson > www.cjdawson.com > > > "Stu" <stuart.ainswo***@gmail.com> wrote in message > news:1149625630.387982.203380@g10g2000cwb.googlegroups.com... > > Not trying to pick a fight, Tony, but you're one of the few that I've > > seen advocate this. I haven't read any of Jim Gray's stuff, but I'll > > take a look at it. It seems odd to me to put business logic back into > > the database because of the issues of object-relational impedance, > > scalability, and general performance considerations. > > > > I prefer clean seperation, myself; I'm even beginning tto question the > > need for stored procs because of the failure to seperate business logic > > from data retrieval. > > > > Stu > > > > > > Tony Rogerson wrote: > >> > I don't know if there is a super correct answer. > >> > It depends on what you got going on. > >> > Personally, me and my company are making great strides to get the > >> > business > >> > logic OUT OF THE Database, and into the business layer. > >> > > >> > >> You've missed the boat Sloan, the current thinking is to put the business > >> logic back into the database because its centralised and easier to > >> manage - > >> plus you get better resource usage through cached execution code etc... > >> > >> Google Jim Gray and look up some of his thinking on this. > >> > >> -- > >> Tony Rogerson > >> SQL Server MVP > >> http://sqlblogcasts.com/blogs/tonyrogerson - technical commentary from a > >> SQL > >> Server Consultant > >> http://sqlserverfaq.com - free video tutorials > >> > >> > >> "sloan" <sl***@ipass.net> wrote in message > >> news:%23UU$cfXiGHA.3408@TK2MSFTNGP05.phx.gbl... > >> > 2005 will have a new function called: > >> > newsequentialid > >> > http://msdn2.microsoft.com/en-us/library/ms189786.aspx > >> > > >> > for 2000 > >> > http://www.sqldev.net/xp/xpguid.htm > >> > is an option to overcome the "randomness" of NEWID() > >> > > >> > > >> > There is a performance hit for using NEWID() in 2000. I won't deny > >> > that. > >> > > >> > However.... . > >> > The big advantage of using GUIDS is that I can > >> > Create my Relationships OUTSIDE of tsql code, aka, (for me) inside > >> > DotNet > >> > code. > >> > > >> > > >> > Read my previous post at: > >> > > >> > http://groups.google.com/group/microsoft.public.sqlserver.programming/browse_thread/thread/567a033a448e7a10/2be27c58c993dab7?lnk=st&q=%22the+Relationship+outside%22&rnum=2&hl=en#2be27c58c993dab7 > >> > > >> > > >> > I don't know if there is a super correct answer. > >> > It depends on what you got going on. > >> > Personally, me and my company are making great strides to get the > >> > business > >> > logic OUT OF THE Database, and into the business layer. > >> > > >> > See > >> > http://www.codeproject.com/gen/design/DudeWheresMyBusinessLogic.asp > >> > for more info > >> > > >> > > >> > Most times, I'm going with some kind of GUID usage, but not the NEWID > >> > stuff. > >> > If replication is in your plans, then you need to seriously consider > >> > abandoning IDENTITY's. > >> > > >> > But you should research and judge for yourself. > >> > > >> > > >> > "Tony Rogerson" <tonyroger***@sqlserverfaq.com> wrote in message > >> > news:%23ih$yCXiGHA.3904@TK2MSFTNGP02.phx.gbl... > >> >> Hi Robert, > >> >> > >> >> Why? The only reason I can think is that you are moving more to a > >> >> distributed database architecture and you want to guarentee that the > >> >> surrogate key (its not really a primary key, the primary key is part > >> >> of > >> > your > >> >> data) is unique across databases. > >> >> > >> >> You can still use IDENTITY but encode a site ID into the schema. > >> >> > >> >> NEWID() is random in its generation so the insert will be random > >> >> across > >> > your > >> >> index so, you will cause additional IO because the data will be more > >> > spread > >> >> across the disk (array) so, you might end up with more locking > >> >> contention > >> >> too. > >> >> > >> >> In a word - don't do it. > >> >> > >> >> Oh, also - its a lot harder to debug and 'see' guids when you are > >> >> working > >> > > >> >> with the data under DBA mode ;). > >> >> > >> >> -- > >> >> Tony Rogerson > >> >> SQL Server MVP > >> >> http://sqlblogcasts.com/blogs/tonyrogerson - technical commentary from > >> >> a > >> > SQL > >> >> Server Consultant > >> >> http://sqlserverfaq.com - free video tutorials > >> >> > >> >> > >> >> "rmg66" <rgwathney__xXx__primepro.com> wrote in message > >> >> news:eZPZpnWiGHA.4284@TK2MSFTNGP05.phx.gbl... > >> >> OK Guys, > >> >> > >> >> We are considering replacing the standard IDENTITY column with the > >> >> UNIQUEIDENTIFIER column for all our primary keys. > >> >> > >> >> Good? Bad? Crazy? WTF!? > >> >> > >> >> Any thoughts on index clustering, performance, portability, > >> >> managability, > >> >> etc... would be appreciated. > >> >> > >> >> Robert > >> >> > >> >> > >> >> > >> > > >> > > > Thats what I was referring to in that we can now do OOP inside SQL Server
using CLR, there is nothing to stop us writing a CHECK constraint that executes the business logic in CLR using C#. The benefit of it being centrally held in SQL Server and in one location shared by the data engine. The UI is left to do simple validation and the data is absolutely protected at the final layer - the data layer. Too many people just do validation in the UI and don't even enforce foreign keys let alone data validation in columns in the database tables! -- Show quoteHide quoteTony Rogerson SQL Server MVP http://sqlblogcasts.com/blogs/tonyrogerson - technical commentary from a SQL Server Consultant http://sqlserverfaq.com - free video tutorials "Stu" <stuart.ainswo***@gmail.com> wrote in message news:1149681234.662511.268920@i40g2000cwc.googlegroups.com... > I'm not necessarily advocating that the business logic NEVER belongs in > the database; I, too, work in the real world. I'm just advocating a > cleaner design, with decisions made ahead of time. Too often I walk > into a shop and see a split middle tier, with some business logic done > in the databas, and some business logic done in the application layer. > Very rarely is there ever a data access layer at all, unless you count > half of the stored procedures, and half of the embedded SQL as a DAL. > > I agree, there are times when it makes sense to have business logic in > the database; more often than not, however, the business rules I > encounter are more complicated than SQL (or T-SQL) can handle well (not > saying it can;t be done; just saying it's probably better handled by an > OOP language). I just think that a clean design should make a decision > about the business logic, and stick to it. > > Colin Dawson wrote: >> Hi Stu. >> >> hmm, nice clean black and white stuff. I wish it was that simple. I >> used >> to think like that myself, but found that it is far better to find the >> balancing point where business logic is split between the data access >> layer >> and the database itself. This allows the business logic to take place in >> the place where it can operate with the most performance. >> >> My understanding on this goes a little like a history lesson.... >> >> 1. The bad old days - no stored procs - everything done via ad-hoc SQL, >> All >> business logic Had to happen on the Application Layer, as there was no >> place >> for it in the DB. >> 2. Client-Server days - stored procs added - was supposed to put all the >> business logic into the DB. (yeah right) >> 3. Three tier - Application layer deals with display, Middle tier for all >> business logic, database for storing data. >> 4. n-tier. basically, like Three Tier, but the line get blurred, and >> there >> could be lost more tiers. >> >> My currenting thinking of n-tier is something like this... (it'll >> probably >> change in the morning) >> >> 1. Front end - deals with basic input validation, and display to the >> users. >> Nothing more. >> 2. Application layer - This is the first place for business logic, >> validates >> inputs for correctness. >> 3. business layer - processes stuff, does more validation and whatever >> else >> needs to be done. >> 4. data access layer - Again, more validation, initiates calls to the db >> layer, but still isn't database specifc. >> 5. db layer - this is where the stored proces lie. alot of data >> processing >> is done here. >> 6. data layer - this is where the actual tables are modified. >> >> Here's where things get complicated, none of these layers are hard and >> fast - there's no reason why the data access layer can't communicate >> directly with the data layer. The Front-end may bypass the application >> layer and go directly to the business layer. There's no hard and fast >> rule. >> >> This allow enough flexibility to place the business logic in the place >> where >> the biggest bang for the buck can take place. >> I have a routine as work which performs a search, the user enters data >> into >> the Front End, then this data is sent unvalidated to the db layer, via >> the >> application layer. The search is then executed directly on the db layer, >> and the result is returned back to the application layer, and on to the >> front end to display. >> >> This skipped 3 of the steps as it wasn't needed. But there may be times >> when more layer's are needed as the routine is more complicated. But the >> layers can be switched in and out as required. What language do each of >> these layer's represent? it does not matter, you can change the language >> as >> often as you like. >> >> Regards >> >> Colin Dawson >> www.cjdawson.com >> >> >> "Stu" <stuart.ainswo***@gmail.com> wrote in message >> news:1149625630.387982.203380@g10g2000cwb.googlegroups.com... >> > Not trying to pick a fight, Tony, but you're one of the few that I've >> > seen advocate this. I haven't read any of Jim Gray's stuff, but I'll >> > take a look at it. It seems odd to me to put business logic back into >> > the database because of the issues of object-relational impedance, >> > scalability, and general performance considerations. >> > >> > I prefer clean seperation, myself; I'm even beginning tto question the >> > need for stored procs because of the failure to seperate business logic >> > from data retrieval. >> > >> > Stu >> > >> > >> > Tony Rogerson wrote: >> >> > I don't know if there is a super correct answer. >> >> > It depends on what you got going on. >> >> > Personally, me and my company are making great strides to get the >> >> > business >> >> > logic OUT OF THE Database, and into the business layer. >> >> > >> >> >> >> You've missed the boat Sloan, the current thinking is to put the >> >> business >> >> logic back into the database because its centralised and easier to >> >> manage - >> >> plus you get better resource usage through cached execution code >> >> etc... >> >> >> >> Google Jim Gray and look up some of his thinking on this. >> >> >> >> -- >> >> Tony Rogerson >> >> SQL Server MVP >> >> http://sqlblogcasts.com/blogs/tonyrogerson - technical commentary from >> >> a >> >> SQL >> >> Server Consultant >> >> http://sqlserverfaq.com - free video tutorials >> >> >> >> >> >> "sloan" <sl***@ipass.net> wrote in message >> >> news:%23UU$cfXiGHA.3408@TK2MSFTNGP05.phx.gbl... >> >> > 2005 will have a new function called: >> >> > newsequentialid >> >> > http://msdn2.microsoft.com/en-us/library/ms189786.aspx >> >> > >> >> > for 2000 >> >> > http://www.sqldev.net/xp/xpguid.htm >> >> > is an option to overcome the "randomness" of NEWID() >> >> > >> >> > >> >> > There is a performance hit for using NEWID() in 2000. I won't deny >> >> > that. >> >> > >> >> > However.... . >> >> > The big advantage of using GUIDS is that I can >> >> > Create my Relationships OUTSIDE of tsql code, aka, (for me) inside >> >> > DotNet >> >> > code. >> >> > >> >> > >> >> > Read my previous post at: >> >> > >> >> > http://groups.google.com/group/microsoft.public.sqlserver.programming/browse_thread/thread/567a033a448e7a10/2be27c58c993dab7?lnk=st&q=%22the+Relationship+outside%22&rnum=2&hl=en#2be27c58c993dab7 >> >> > >> >> > >> >> > I don't know if there is a super correct answer. >> >> > It depends on what you got going on. >> >> > Personally, me and my company are making great strides to get the >> >> > business >> >> > logic OUT OF THE Database, and into the business layer. >> >> > >> >> > See >> >> > http://www.codeproject.com/gen/design/DudeWheresMyBusinessLogic.asp >> >> > for more info >> >> > >> >> > >> >> > Most times, I'm going with some kind of GUID usage, but not the >> >> > NEWID >> >> > stuff. >> >> > If replication is in your plans, then you need to seriously consider >> >> > abandoning IDENTITY's. >> >> > >> >> > But you should research and judge for yourself. >> >> > >> >> > >> >> > "Tony Rogerson" <tonyroger***@sqlserverfaq.com> wrote in message >> >> > news:%23ih$yCXiGHA.3904@TK2MSFTNGP02.phx.gbl... >> >> >> Hi Robert, >> >> >> >> >> >> Why? The only reason I can think is that you are moving more to a >> >> >> distributed database architecture and you want to guarentee that >> >> >> the >> >> >> surrogate key (its not really a primary key, the primary key is >> >> >> part >> >> >> of >> >> > your >> >> >> data) is unique across databases. >> >> >> >> >> >> You can still use IDENTITY but encode a site ID into the schema. >> >> >> >> >> >> NEWID() is random in its generation so the insert will be random >> >> >> across >> >> > your >> >> >> index so, you will cause additional IO because the data will be >> >> >> more >> >> > spread >> >> >> across the disk (array) so, you might end up with more locking >> >> >> contention >> >> >> too. >> >> >> >> >> >> In a word - don't do it. >> >> >> >> >> >> Oh, also - its a lot harder to debug and 'see' guids when you are >> >> >> working >> >> > >> >> >> with the data under DBA mode ;). >> >> >> >> >> >> -- >> >> >> Tony Rogerson >> >> >> SQL Server MVP >> >> >> http://sqlblogcasts.com/blogs/tonyrogerson - technical commentary >> >> >> from >> >> >> a >> >> > SQL >> >> >> Server Consultant >> >> >> http://sqlserverfaq.com - free video tutorials >> >> >> >> >> >> >> >> >> "rmg66" <rgwathney__xXx__primepro.com> wrote in message >> >> >> news:eZPZpnWiGHA.4284@TK2MSFTNGP05.phx.gbl... >> >> >> OK Guys, >> >> >> >> >> >> We are considering replacing the standard IDENTITY column with the >> >> >> UNIQUEIDENTIFIER column for all our primary keys. >> >> >> >> >> >> Good? Bad? Crazy? WTF!? >> >> >> >> >> >> Any thoughts on index clustering, performance, portability, >> >> >> managability, >> >> >> etc... would be appreciated. >> >> >> >> >> >> Robert >> >> >> >> >> >> >> >> >> >> >> > >> >> > >> > > With the adoption of SQL Server 2005 and the CLR and the fact that the
assemblies go with the database (in terms of backup and restore etc...) we now have a nice central repository for business logic and data access logic, that allows other applications to more easily use those 'central' libraries rather than having a seperate middle tier - SQL Server can now be the 'middle tier', I am not advocating doing the business logic in SQL specifically but whatever it needs to be - so, data orientated stuff you'd look to SQL first and then for domain validation that can't easily or for performance be done in SQL use CLR, those same routines can be very easily shared. They are easy to deploy because its almost a simple restore database. The 'client tier' should really just be for validating user input down at the user level (the database should still protect at the core from bad data so all your check constraints should be in there) and for formatting data - the UI rather than the UI come business logic layer... Hopefully the next version of SQL will have something to compete with Oracle RAC so we can have proper scale out, having said that we can still use partitioning to scale out and load balance the database but by partition column rather than CPU loading. -- Show quoteHide quoteTony Rogerson SQL Server MVP http://sqlblogcasts.com/blogs/tonyrogerson - technical commentary from a SQL Server Consultant http://sqlserverfaq.com - free video tutorials "Stu" <stuart.ainswo***@gmail.com> wrote in message news:1149625630.387982.203380@g10g2000cwb.googlegroups.com... > Not trying to pick a fight, Tony, but you're one of the few that I've > seen advocate this. I haven't read any of Jim Gray's stuff, but I'll > take a look at it. It seems odd to me to put business logic back into > the database because of the issues of object-relational impedance, > scalability, and general performance considerations. > > I prefer clean seperation, myself; I'm even beginning tto question the > need for stored procs because of the failure to seperate business logic > from data retrieval. > > Stu > > > Tony Rogerson wrote: >> > I don't know if there is a super correct answer. >> > It depends on what you got going on. >> > Personally, me and my company are making great strides to get the >> > business >> > logic OUT OF THE Database, and into the business layer. >> > >> >> You've missed the boat Sloan, the current thinking is to put the business >> logic back into the database because its centralised and easier to >> manage - >> plus you get better resource usage through cached execution code etc... >> >> Google Jim Gray and look up some of his thinking on this. >> >> -- >> Tony Rogerson >> SQL Server MVP >> http://sqlblogcasts.com/blogs/tonyrogerson - technical commentary from a >> SQL >> Server Consultant >> http://sqlserverfaq.com - free video tutorials >> >> >> "sloan" <sl***@ipass.net> wrote in message >> news:%23UU$cfXiGHA.3408@TK2MSFTNGP05.phx.gbl... >> > 2005 will have a new function called: >> > newsequentialid >> > http://msdn2.microsoft.com/en-us/library/ms189786.aspx >> > >> > for 2000 >> > http://www.sqldev.net/xp/xpguid.htm >> > is an option to overcome the "randomness" of NEWID() >> > >> > >> > There is a performance hit for using NEWID() in 2000. I won't deny >> > that. >> > >> > However.... . >> > The big advantage of using GUIDS is that I can >> > Create my Relationships OUTSIDE of tsql code, aka, (for me) inside >> > DotNet >> > code. >> > >> > >> > Read my previous post at: >> > >> > http://groups.google.com/group/microsoft.public.sqlserver.programming/browse_thread/thread/567a033a448e7a10/2be27c58c993dab7?lnk=st&q=%22the+Relationship+outside%22&rnum=2&hl=en#2be27c58c993dab7 >> > >> > >> > I don't know if there is a super correct answer. >> > It depends on what you got going on. >> > Personally, me and my company are making great strides to get the >> > business >> > logic OUT OF THE Database, and into the business layer. >> > >> > See >> > http://www.codeproject.com/gen/design/DudeWheresMyBusinessLogic.asp >> > for more info >> > >> > >> > Most times, I'm going with some kind of GUID usage, but not the NEWID >> > stuff. >> > If replication is in your plans, then you need to seriously consider >> > abandoning IDENTITY's. >> > >> > But you should research and judge for yourself. >> > >> > >> > "Tony Rogerson" <tonyroger***@sqlserverfaq.com> wrote in message >> > news:%23ih$yCXiGHA.3904@TK2MSFTNGP02.phx.gbl... >> >> Hi Robert, >> >> >> >> Why? The only reason I can think is that you are moving more to a >> >> distributed database architecture and you want to guarentee that the >> >> surrogate key (its not really a primary key, the primary key is part >> >> of >> > your >> >> data) is unique across databases. >> >> >> >> You can still use IDENTITY but encode a site ID into the schema. >> >> >> >> NEWID() is random in its generation so the insert will be random >> >> across >> > your >> >> index so, you will cause additional IO because the data will be more >> > spread >> >> across the disk (array) so, you might end up with more locking >> >> contention >> >> too. >> >> >> >> In a word - don't do it. >> >> >> >> Oh, also - its a lot harder to debug and 'see' guids when you are >> >> working >> > >> >> with the data under DBA mode ;). >> >> >> >> -- >> >> Tony Rogerson >> >> SQL Server MVP >> >> http://sqlblogcasts.com/blogs/tonyrogerson - technical commentary from >> >> a >> > SQL >> >> Server Consultant >> >> http://sqlserverfaq.com - free video tutorials >> >> >> >> >> >> "rmg66" <rgwathney__xXx__primepro.com> wrote in message >> >> news:eZPZpnWiGHA.4284@TK2MSFTNGP05.phx.gbl... >> >> OK Guys, >> >> >> >> We are considering replacing the standard IDENTITY column with the >> >> UNIQUEIDENTIFIER column for all our primary keys. >> >> >> >> Good? Bad? Crazy? WTF!? >> >> >> >> Any thoughts on index clustering, performance, portability, >> >> managability, >> >> etc... would be appreciated. >> >> >> >> Robert >> >> >> >> >> >> >> > >> > > Well, that's great if you're married to Sql Server.
I love Sql Server, don't get me wrong, its my bread and butter. But you can't guarantee you'll always be in a Sql Server world. And I don't think I've "missed the boat". My DataLayer objects return: IDataReader's DataSets (typed and untyped) XmlDocuments Scalars voids (or nothings... as in, just make sure what I called worked) Because I have a good DataLayer, I can switch out the backend database at any given moment. Yeah, there will be some issues, but not as drastic as complicated business logic in my tsql. The database is usually the bottleneck of any well designed system. And the quicker I get in and get out, the better. I'll take a look at Jim Gray's stuff. (is this the same Jim Gray who does interviews for espn/nba?) That's fine to say "There are other options out there, which have BL in the database" But "you missed the boat", ... thats a little strong for for advocating another opinion in favor of what I and alot of others have proposed. Perhaps the experience of having my company merge with another company, with diffrent RDBMS systems has influenced me somewhat. I'll stick with the good DataLayer design for now. Show quoteHide quote "Tony Rogerson" <tonyroger***@sqlserverfaq.com> wrote in message http://groups.google.com/group/microsoft.public.sqlserver.programming/browse_thread/thread/567a033a448e7a10/2be27c58c993dab7?lnk=st&q=%22the+Relationship+outside%22&rnum=2&hl=en#2be27c58c993dab7news:OHgQiOaiGHA.4284@TK2MSFTNGP05.phx.gbl... > > I don't know if there is a super correct answer. > > It depends on what you got going on. > > Personally, me and my company are making great strides to get the business > > logic OUT OF THE Database, and into the business layer. > > > > You've missed the boat Sloan, the current thinking is to put the business > logic back into the database because its centralised and easier to manage - > plus you get better resource usage through cached execution code etc... > > Google Jim Gray and look up some of his thinking on this. > > -- > Tony Rogerson > SQL Server MVP > http://sqlblogcasts.com/blogs/tonyrogerson - technical commentary from a SQL > Server Consultant > http://sqlserverfaq.com - free video tutorials > > > "sloan" <sl***@ipass.net> wrote in message > news:%23UU$cfXiGHA.3408@TK2MSFTNGP05.phx.gbl... > > 2005 will have a new function called: > > newsequentialid > > http://msdn2.microsoft.com/en-us/library/ms189786.aspx > > > > for 2000 > > http://www.sqldev.net/xp/xpguid.htm > > is an option to overcome the "randomness" of NEWID() > > > > > > There is a performance hit for using NEWID() in 2000. I won't deny that. > > > > However.... . > > The big advantage of using GUIDS is that I can > > Create my Relationships OUTSIDE of tsql code, aka, (for me) inside DotNet > > code. > > > > > > Read my previous post at: > > > > Show quoteHide quote > > > > > > I don't know if there is a super correct answer. > > It depends on what you got going on. > > Personally, me and my company are making great strides to get the business > > logic OUT OF THE Database, and into the business layer. > > > > See > > http://www.codeproject.com/gen/design/DudeWheresMyBusinessLogic.asp > > for more info > > > > > > Most times, I'm going with some kind of GUID usage, but not the NEWID > > stuff. > > If replication is in your plans, then you need to seriously consider > > abandoning IDENTITY's. > > > > But you should research and judge for yourself. > > > > > > "Tony Rogerson" <tonyroger***@sqlserverfaq.com> wrote in message > > news:%23ih$yCXiGHA.3904@TK2MSFTNGP02.phx.gbl... > >> Hi Robert, > >> > >> Why? The only reason I can think is that you are moving more to a > >> distributed database architecture and you want to guarentee that the > >> surrogate key (its not really a primary key, the primary key is part of > > your > >> data) is unique across databases. > >> > >> You can still use IDENTITY but encode a site ID into the schema. > >> > >> NEWID() is random in its generation so the insert will be random across > > your > >> index so, you will cause additional IO because the data will be more > > spread > >> across the disk (array) so, you might end up with more locking contention > >> too. > >> > >> In a word - don't do it. > >> > >> Oh, also - its a lot harder to debug and 'see' guids when you are working > > > >> with the data under DBA mode ;). > >> > >> -- > >> Tony Rogerson > >> SQL Server MVP > >> http://sqlblogcasts.com/blogs/tonyrogerson - technical commentary from a > > SQL > >> Server Consultant > >> http://sqlserverfaq.com - free video tutorials > >> > >> > >> "rmg66" <rgwathney__xXx__primepro.com> wrote in message > >> news:eZPZpnWiGHA.4284@TK2MSFTNGP05.phx.gbl... > >> OK Guys, > >> > >> We are considering replacing the standard IDENTITY column with the > >> UNIQUEIDENTIFIER column for all our primary keys. > >> > >> Good? Bad? Crazy? WTF!? > >> > >> Any thoughts on index clustering, performance, portability, managability, > >> etc... would be appreciated. > >> > >> Robert > >> > >> > >> > > > > > > >> But "you missed the boat", ... thats a little strong for for advocating Thats what happens when you shout at me :)>> another opinion in favor of what I and alot of others have proposed. If you are going for portability then thats fine until you hit a client who doesn't want to use .NET either. Can you honestly say that clients have asked specifically for a different database and that would absolutely have turned your offering down if you told them they had to use SQL Server, that goes against what I'm seeing out in the field, companies prefer their adopted database but will use others if the business deams that the value to the business is above the inconvenience to their IT. I'm starting to take a look at LINQ now too and it worries me, they appear to be shifting the problem back into the database, the problem has always been the developer not understanding how sets work and how to design a proper interface between the object based application and teh set based database. All it will mean is that I will get more consultancy doing SQL performance problems but be unable to do anything because all the SQL code has been embedded where it can't be touched nor even seen! At least for projects using stored procedures (a nice unit of coupling and modularisation) I can do something to optimise but with embedded SQL it takes a heavier life cycle to do a change - more resource and significantly more risk. -- Show quoteHide quoteTony Rogerson SQL Server MVP http://sqlblogcasts.com/blogs/tonyrogerson - technical commentary from a SQL Server Consultant http://sqlserverfaq.com - free video tutorials "sloan" <sl***@ipass.net> wrote in message news:%23fSJ4waiGHA.1612@TK2MSFTNGP04.phx.gbl... > > Well, that's great if you're married to Sql Server. > > I love Sql Server, don't get me wrong, its my bread and butter. > But you can't guarantee you'll always be in a Sql Server world. > > And I don't think I've "missed the boat". > > My DataLayer objects return: > IDataReader's > DataSets (typed and untyped) > XmlDocuments > Scalars > voids (or nothings... as in, just make sure what I called worked) > > Because I have a good DataLayer, I can switch out the backend database at > any given moment. > Yeah, there will be some issues, but not as drastic as complicated > business > logic in my tsql. > > The database is usually the bottleneck of any well designed system. > And the quicker I get in and get out, the better. > > I'll take a look at Jim Gray's stuff. (is this the same Jim Gray who does > interviews for espn/nba?) > > That's fine to say "There are other options out there, which have BL in > the > database" > > But "you missed the boat", ... thats a little strong for for advocating > another opinion in favor of what I and alot of others have proposed. > > > Perhaps the experience of having my company merge with another company, > with > diffrent RDBMS systems has influenced me somewhat. > > > I'll stick with the good DataLayer design for now. > > > > > "Tony Rogerson" <tonyroger***@sqlserverfaq.com> wrote in message > news:OHgQiOaiGHA.4284@TK2MSFTNGP05.phx.gbl... >> > I don't know if there is a super correct answer. >> > It depends on what you got going on. >> > Personally, me and my company are making great strides to get the > business >> > logic OUT OF THE Database, and into the business layer. >> > >> >> You've missed the boat Sloan, the current thinking is to put the business >> logic back into the database because its centralised and easier to > manage - >> plus you get better resource usage through cached execution code etc... >> >> Google Jim Gray and look up some of his thinking on this. >> >> -- >> Tony Rogerson >> SQL Server MVP >> http://sqlblogcasts.com/blogs/tonyrogerson - technical commentary from a > SQL >> Server Consultant >> http://sqlserverfaq.com - free video tutorials >> >> >> "sloan" <sl***@ipass.net> wrote in message >> news:%23UU$cfXiGHA.3408@TK2MSFTNGP05.phx.gbl... >> > 2005 will have a new function called: >> > newsequentialid >> > http://msdn2.microsoft.com/en-us/library/ms189786.aspx >> > >> > for 2000 >> > http://www.sqldev.net/xp/xpguid.htm >> > is an option to overcome the "randomness" of NEWID() >> > >> > >> > There is a performance hit for using NEWID() in 2000. I won't deny > that. >> > >> > However.... . >> > The big advantage of using GUIDS is that I can >> > Create my Relationships OUTSIDE of tsql code, aka, (for me) inside > DotNet >> > code. >> > >> > >> > Read my previous post at: >> > >> > > http://groups.google.com/group/microsoft.public.sqlserver.programming/browse_thread/thread/567a033a448e7a10/2be27c58c993dab7?lnk=st&q=%22the+Relationship+outside%22&rnum=2&hl=en#2be27c58c993dab7 >> > >> > >> > I don't know if there is a super correct answer. >> > It depends on what you got going on. >> > Personally, me and my company are making great strides to get the > business >> > logic OUT OF THE Database, and into the business layer. >> > >> > See >> > http://www.codeproject.com/gen/design/DudeWheresMyBusinessLogic.asp >> > for more info >> > >> > >> > Most times, I'm going with some kind of GUID usage, but not the NEWID >> > stuff. >> > If replication is in your plans, then you need to seriously consider >> > abandoning IDENTITY's. >> > >> > But you should research and judge for yourself. >> > >> > >> > "Tony Rogerson" <tonyroger***@sqlserverfaq.com> wrote in message >> > news:%23ih$yCXiGHA.3904@TK2MSFTNGP02.phx.gbl... >> >> Hi Robert, >> >> >> >> Why? The only reason I can think is that you are moving more to a >> >> distributed database architecture and you want to guarentee that the >> >> surrogate key (its not really a primary key, the primary key is part >> >> of >> > your >> >> data) is unique across databases. >> >> >> >> You can still use IDENTITY but encode a site ID into the schema. >> >> >> >> NEWID() is random in its generation so the insert will be random >> >> across >> > your >> >> index so, you will cause additional IO because the data will be more >> > spread >> >> across the disk (array) so, you might end up with more locking > contention >> >> too. >> >> >> >> In a word - don't do it. >> >> >> >> Oh, also - its a lot harder to debug and 'see' guids when you are > working >> > >> >> with the data under DBA mode ;). >> >> >> >> -- >> >> Tony Rogerson >> >> SQL Server MVP >> >> http://sqlblogcasts.com/blogs/tonyrogerson - technical commentary from > a >> > SQL >> >> Server Consultant >> >> http://sqlserverfaq.com - free video tutorials >> >> >> >> >> >> "rmg66" <rgwathney__xXx__primepro.com> wrote in message >> >> news:eZPZpnWiGHA.4284@TK2MSFTNGP05.phx.gbl... >> >> OK Guys, >> >> >> >> We are considering replacing the standard IDENTITY column with the >> >> UNIQUEIDENTIFIER column for all our primary keys. >> >> >> >> Good? Bad? Crazy? WTF!? >> >> >> >> Any thoughts on index clustering, performance, portability, > managability, >> >> etc... would be appreciated. >> >> >> >> Robert >> >> >> >> >> >> >> > >> > >> >> > > My organization is in the midst of trying to replace our UNIQUEIDENTIFIER
clusetered primary keys with IDENTITY fields. Two reasons: 1) making the clustered index a UNIQUEIDENTIFIER field increases the size of all the nonclustered indexes; and 2) UNIQUEIDENTIFIER fields generated with the NEWID() function are not sequential, so your joins will be much less efficient. Oh, and I forgot the last one: changing back is a pain! "rmg66" <rgwathney__xXx__primepro.com> wrote in message We are considering replacing the standard IDENTITY column with the news:eZPZpnWiGHA.4284@TK2MSFTNGP05.phx.gbl... OK Guys, UNIQUEIDENTIFIER column for all our primary keys. Good? Bad? Crazy? WTF!? Any thoughts on index clustering, performance, portability, managability, etc... would be appreciated. Robert Why are you considering this? What gains do you expect, or why do you
think you need GUIDs? Aren't Identities working for you? Have you seen the presentation of Kimberly Tripp on Index Optimization? (see http://www.microsoft.com/uk/technet/itsshowtime/sessionh.aspx?videoid=29) In a fragment of it, she discusses the use of GUIDs as the clustered index key, and how this use can cause massive fragmentation and (as a result) abysmal performance. BTW: there are different opinions about the policy to use an Identity (or other surrogate key) as the Primary Key for each table. My personal opinion is, that such a key should never be the first choice. So IMO you should not have such a policy. One should always try to find a natural key, and only choose a surrogate key if no useful natural key is found, or for performance reasons (which means the natural key would still be an alternate key, enforced with a Unique constraint). HTH, Gert-Jan Show quoteHide quote > rmg66 wrote: > > OK Guys, > > We are considering replacing the standard IDENTITY column with the > UNIQUEIDENTIFIER column for all our primary keys. > > Good? Bad? Crazy? WTF!? > > Any thoughts on index clustering, performance, portability, > managability, etc... would be appreciated. > > Robert > >  
Other interesting topics
Index Tuning
Running Sum Query ? Creating dynamic table in SP Identity Columns - Design Question User defined fields via application Access "inserted" / "deleted" from stored procedure Passing Date Variables SQL 2005: How to store output from Stored Proc in a Table? query plan on sql server 2005 Custom SQL library ? |
|||||||||||||||||||||||
