Best performance for SQL2000

Hi
i must configure a RAID on external storage disk array for my SQL 2000
cluster ad have think this configuration :
1- DataFile on separate RAID5 LUN
2- LogFile on another separate RAID5 LUN
3- Quorum on another separate RAID1 LUN
This configugation is good for performace (datafile e logfile separated) and
security (Quorum on Mirror) !'!
Thanks in advanceMake the Log on a RAID 1 or a 10 and not a 5. Raid 5 has too many writes
for peak performance of logs.Put the extra disks into the data raid 5 or
make it a raid 10 for better performance.
--
Andrew J. Kelly SQL MVP
<io.com> wrote in message news:OfbXh0YoEHA.1800@.TK2MSFTNGP15.phx.gbl...
> Hi
> i must configure a RAID on external storage disk array for my SQL 2000
> cluster ad have think this configuration :
> 1- DataFile on separate RAID5 LUN
> 2- LogFile on another separate RAID5 LUN
> 3- Quorum on another separate RAID1 LUN
> This configugation is good for performace (datafile e logfile separated)
and
> security (Quorum on Mirror) !'!
> Thanks in advance
>|||Ok therefore :
datafile RAID5
logfile RAID1
quorum RAID1
it's ok ?
thanks
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:uFE6MNaoEHA.324@.TK2MSFTNGP11.phx.gbl...
> Make the Log on a RAID 1 or a 10 and not a 5. Raid 5 has too many writes
> for peak performance of logs.Put the extra disks into the data raid 5 or
> make it a raid 10 for better performance.
> --
> Andrew J. Kelly SQL MVP
>
> <io.com> wrote in message news:OfbXh0YoEHA.1800@.TK2MSFTNGP15.phx.gbl...
> > Hi
> >
> > i must configure a RAID on external storage disk array for my SQL 2000
> > cluster ad have think this configuration :
> >
> > 1- DataFile on separate RAID5 LUN
> > 2- LogFile on another separate RAID5 LUN
> > 3- Quorum on another separate RAID1 LUN
> >
> > This configugation is good for performace (datafile e logfile separated)
> and
> > security (Quorum on Mirror) !'!
> >
> > Thanks in advance
> >
> >
>|||Yes
--
Andrew J. Kelly SQL MVP
<io.com> wrote in message news:ORYQO1aoEHA.1608@.TK2MSFTNGP15.phx.gbl...
> Ok therefore :
> datafile RAID5
> logfile RAID1
> quorum RAID1
> it's ok ?
> thanks
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:uFE6MNaoEHA.324@.TK2MSFTNGP11.phx.gbl...
> > Make the Log on a RAID 1 or a 10 and not a 5. Raid 5 has too many
writes
> > for peak performance of logs.Put the extra disks into the data raid 5 or
> > make it a raid 10 for better performance.
> >
> > --
> > Andrew J. Kelly SQL MVP
> >
> >
> > <io.com> wrote in message news:OfbXh0YoEHA.1800@.TK2MSFTNGP15.phx.gbl...
> > > Hi
> > >
> > > i must configure a RAID on external storage disk array for my SQL 2000
> > > cluster ad have think this configuration :
> > >
> > > 1- DataFile on separate RAID5 LUN
> > > 2- LogFile on another separate RAID5 LUN
> > > 3- Quorum on another separate RAID1 LUN
> > >
> > > This configugation is good for performace (datafile e logfile
separated)
> > and
> > > security (Quorum on Mirror) !'!
> > >
> > > Thanks in advance
> > >
> > >
> >
> >
>|||Even better:
datafile RAID10
logfile RAID1
quorum RAID1
Regards
Mike
"io.com" wrote:
> Ok therefore :
> datafile RAID5
> logfile RAID1
> quorum RAID1
> it's ok ?
> thanks
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:uFE6MNaoEHA.324@.TK2MSFTNGP11.phx.gbl...
> > Make the Log on a RAID 1 or a 10 and not a 5. Raid 5 has too many writes
> > for peak performance of logs.Put the extra disks into the data raid 5 or
> > make it a raid 10 for better performance.
> >
> > --
> > Andrew J. Kelly SQL MVP
> >
> >
> > <io.com> wrote in message news:OfbXh0YoEHA.1800@.TK2MSFTNGP15.phx.gbl...
> > > Hi
> > >
> > > i must configure a RAID on external storage disk array for my SQL 2000
> > > cluster ad have think this configuration :
> > >
> > > 1- DataFile on separate RAID5 LUN
> > > 2- LogFile on another separate RAID5 LUN
> > > 3- Quorum on another separate RAID1 LUN
> > >
> > > This configugation is good for performace (datafile e logfile separated)
> > and
> > > security (Quorum on Mirror) !'!
> > >
> > > Thanks in advance
> > >
> > >
> >
> >
>
>

BEST NAS on the MARKET?

Hi all the other GURU out there,
My company is trying to find a good NAS system for storage on our
network. We previously bought a IOMega 640Gbytes NAS, which is a
headless Win 2000 Server, it works but not the greatest.
What NAS out there would you recommand me to buy if I am looking for a
1 to 2 Tbytes NAS?
Thanks in advance.
CompGuru WannabeCompGuRu wrote:
> Hi all the other GURU out there,
> My company is trying to find a good NAS system for storage on our
> network. We previously bought a IOMega 640Gbytes NAS, which is a
> headless Win 2000 Server, it works but not the greatest.
> What NAS out there would you recommand me to buy if I am looking for a
> 1 to 2 Tbytes NAS?
> Thanks in advance.
> CompGuru Wannabe
Is this for use with SQL Server? If so, have a look here for issues and
requirements for using NAS devices with SQL Server.
http://support.microsoft.com/defaul...kb;en-us;304261
David Gugick
Quest Software
www.imceda.com
www.quest.com|||Thanks.. It probably just be a backup server for storing drive images
and source files.. I will give it a try..
COmp GuRU|||Decide on the application before you decide the hardware. NAS is not
for SQL Server.
David Portas
SQL Server MVP
--

BEST NAS on the MARKET?

Hi all the other GURU out there,
My company is trying to find a good NAS system for storage on our
network. We previously bought a IOMega 640Gbytes NAS, which is a
headless Win 2000 Server, it works but not the greatest.
What NAS out there would you recommand me to buy if I am looking for a
1 to 2 Tbytes NAS?
Thanks in advance.
CompGuru WannabeCompGuRu wrote:
> Hi all the other GURU out there,
> My company is trying to find a good NAS system for storage on our
> network. We previously bought a IOMega 640Gbytes NAS, which is a
> headless Win 2000 Server, it works but not the greatest.
> What NAS out there would you recommand me to buy if I am looking for a
> 1 to 2 Tbytes NAS?
> Thanks in advance.
> CompGuru Wannabe
Is this for use with SQL Server? If so, have a look here for issues and
requirements for using NAS devices with SQL Server.
http://support.microsoft.com/default.aspx?scid=kb;en-us;304261
--
David Gugick
Quest Software
www.imceda.com
www.quest.com|||Thanks.. It probably just be a backup server for storing drive images
and source files.. I will give it a try..
COmp GuRU|||Decide on the application before you decide the hardware. NAS is not
for SQL Server.
--
David Portas
SQL Server MVP
--

BEST NAS on the MARKET?

Hi all the other GURU out there,
My company is trying to find a good NAS system for storage on our
network. We previously bought a IOMega 640Gbytes NAS, which is a
headless Win 2000 Server, it works but not the greatest.
What NAS out there would you recommand me to buy if I am looking for a
1 to 2 Tbytes NAS?
Thanks in advance.
CompGuru Wannabe
CompGuRu wrote:
> Hi all the other GURU out there,
> My company is trying to find a good NAS system for storage on our
> network. We previously bought a IOMega 640Gbytes NAS, which is a
> headless Win 2000 Server, it works but not the greatest.
> What NAS out there would you recommand me to buy if I am looking for a
> 1 to 2 Tbytes NAS?
> Thanks in advance.
> CompGuru Wannabe
Is this for use with SQL Server? If so, have a look here for issues and
requirements for using NAS devices with SQL Server.
http://support.microsoft.com/default...b;en-us;304261
David Gugick
Quest Software
www.imceda.com
www.quest.com
|||Thanks.. It probably just be a backup server for storing drive images
and source files.. I will give it a try..
COmp GuRU
|||Decide on the application before you decide the hardware. NAS is not
for SQL Server.
David Portas
SQL Server MVP

best location for the tran log?

I have a SQL 2000 database on RAID 5 storage. The data files and the
tran log are all on the same RAID 5 volume - the E drive. My only other
option for placement of the tran log is a partition of the system drive
- the D drive. The system drive is mirrored. The operating system
resides on the other logical partition of the system drive - the C
drive. I'm concerned that if I move the tran log to the D drive it
might conflict with operating system activity. This is a dedicated SQL
Server so I'm hoping that it won't be too much overhead on the system
drive to place the tran log there as well. Am I better off moving the
tran log to the system drive or keeping it on the E drive with the rest
of the data?
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!There shouldn't be a problem with sharing a mirrored drive with the OS for
the logs if SQL Server is the only app running on the server.
Andrew J. Kelly SQL MVP
"T Dubya" <timber_toes@.bigfoot.com> wrote in message
news:%23Qx5Um0LFHA.3340@.TK2MSFTNGP14.phx.gbl...
>I have a SQL 2000 database on RAID 5 storage. The data files and the
> tran log are all on the same RAID 5 volume - the E drive. My only other
> option for placement of the tran log is a partition of the system drive
> - the D drive. The system drive is mirrored. The operating system
> resides on the other logical partition of the system drive - the C
> drive. I'm concerned that if I move the tran log to the D drive it
> might conflict with operating system activity. This is a dedicated SQL
> Server so I'm hoping that it won't be too much overhead on the system
> drive to place the tran log there as well. Am I better off moving the
> tran log to the system drive or keeping it on the E drive with the rest
> of the data?
>
> *** Sent via Developersdex http://www.codecomments.com ***
> Don't just participate in USENET...get rewarded for it!|||I'd recommend that you move the transaction log for a couple of reasons.
1. By having the transaction log and data files on the same drives you risk
a loss on failure, or an extension in time to restore.
2. RAID 5 is not as efficient as RAID 1 for performance of the write
intensive transaction log.
If the server is dedicated to SQL server then there should be very little
competition for the drive from other processes.
"T Dubya" wrote:

> I have a SQL 2000 database on RAID 5 storage. The data files and the
> tran log are all on the same RAID 5 volume - the E drive. My only other
> option for placement of the tran log is a partition of the system drive
> - the D drive. The system drive is mirrored. The operating system
> resides on the other logical partition of the system drive - the C
> drive. I'm concerned that if I move the tran log to the D drive it
> might conflict with operating system activity. This is a dedicated SQL
> Server so I'm hoping that it won't be too much overhead on the system
> drive to place the tran log there as well. Am I better off moving the
> tran log to the system drive or keeping it on the E drive with the rest
> of the data?
>
> *** Sent via Developersdex http://www.codecomments.com ***
> Don't just participate in USENET...get rewarded for it!
>|||Thanks, Andrew, for the response. I'll give it a try. The main reason
I had reservations about it is because before I realized that the D
drive was in fact just a logical partition of the system drive, I tried
to dump the entire database to that location. The database dump ran
much slower that way than when I dumped it to the same raid 5 where the
data itself resides - about twice as slow, in fact. I just wanted to be
sure that putting the tran logs on the operating system drive wouldn't
cause a similar slow down. Since it is a dedicated SQL Server hopefully
it won't pose a problem. Thanks again.
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!

best location for the tran log?

I have a SQL 2000 database on RAID 5 storage. The data files and the
tran log are all on the same RAID 5 volume - the E drive. My only other
option for placement of the tran log is a partition of the system drive
- the D drive. The system drive is mirrored. The operating system
resides on the other logical partition of the system drive - the C
drive. I'm concerned that if I move the tran log to the D drive it
might conflict with operating system activity. This is a dedicated SQL
Server so I'm hoping that it won't be too much overhead on the system
drive to place the tran log there as well. Am I better off moving the
tran log to the system drive or keeping it on the E drive with the rest
of the data?
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!There shouldn't be a problem with sharing a mirrored drive with the OS for
the logs if SQL Server is the only app running on the server.
--
Andrew J. Kelly SQL MVP
"T Dubya" <timber_toes@.bigfoot.com> wrote in message
news:%23Qx5Um0LFHA.3340@.TK2MSFTNGP14.phx.gbl...
>I have a SQL 2000 database on RAID 5 storage. The data files and the
> tran log are all on the same RAID 5 volume - the E drive. My only other
> option for placement of the tran log is a partition of the system drive
> - the D drive. The system drive is mirrored. The operating system
> resides on the other logical partition of the system drive - the C
> drive. I'm concerned that if I move the tran log to the D drive it
> might conflict with operating system activity. This is a dedicated SQL
> Server so I'm hoping that it won't be too much overhead on the system
> drive to place the tran log there as well. Am I better off moving the
> tran log to the system drive or keeping it on the E drive with the rest
> of the data?
>
> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!|||I'd recommend that you move the transaction log for a couple of reasons.
1. By having the transaction log and data files on the same drives you risk
a loss on failure, or an extension in time to restore.
2. RAID 5 is not as efficient as RAID 1 for performance of the write
intensive transaction log.
If the server is dedicated to SQL server then there should be very little
competition for the drive from other processes.
"T Dubya" wrote:
> I have a SQL 2000 database on RAID 5 storage. The data files and the
> tran log are all on the same RAID 5 volume - the E drive. My only other
> option for placement of the tran log is a partition of the system drive
> - the D drive. The system drive is mirrored. The operating system
> resides on the other logical partition of the system drive - the C
> drive. I'm concerned that if I move the tran log to the D drive it
> might conflict with operating system activity. This is a dedicated SQL
> Server so I'm hoping that it won't be too much overhead on the system
> drive to place the tran log there as well. Am I better off moving the
> tran log to the system drive or keeping it on the E drive with the rest
> of the data?
>
> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!
>

best location for the tran log?

I have a SQL 2000 database on RAID 5 storage. The data files and the
tran log are all on the same RAID 5 volume - the E drive. My only other
option for placement of the tran log is a partition of the system drive
- the D drive. The system drive is mirrored. The operating system
resides on the other logical partition of the system drive - the C
drive. I'm concerned that if I move the tran log to the D drive it
might conflict with operating system activity. This is a dedicated SQL
Server so I'm hoping that it won't be too much overhead on the system
drive to place the tran log there as well. Am I better off moving the
tran log to the system drive or keeping it on the E drive with the rest
of the data?
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!
There shouldn't be a problem with sharing a mirrored drive with the OS for
the logs if SQL Server is the only app running on the server.
Andrew J. Kelly SQL MVP
"T Dubya" <timber_toes@.bigfoot.com> wrote in message
news:%23Qx5Um0LFHA.3340@.TK2MSFTNGP14.phx.gbl...
>I have a SQL 2000 database on RAID 5 storage. The data files and the
> tran log are all on the same RAID 5 volume - the E drive. My only other
> option for placement of the tran log is a partition of the system drive
> - the D drive. The system drive is mirrored. The operating system
> resides on the other logical partition of the system drive - the C
> drive. I'm concerned that if I move the tran log to the D drive it
> might conflict with operating system activity. This is a dedicated SQL
> Server so I'm hoping that it won't be too much overhead on the system
> drive to place the tran log there as well. Am I better off moving the
> tran log to the system drive or keeping it on the E drive with the rest
> of the data?
>
> *** Sent via Developersdex http://www.codecomments.com ***
> Don't just participate in USENET...get rewarded for it!
|||I'd recommend that you move the transaction log for a couple of reasons.
1. By having the transaction log and data files on the same drives you risk
a loss on failure, or an extension in time to restore.
2. RAID 5 is not as efficient as RAID 1 for performance of the write
intensive transaction log.
If the server is dedicated to SQL server then there should be very little
competition for the drive from other processes.
"T Dubya" wrote:

> I have a SQL 2000 database on RAID 5 storage. The data files and the
> tran log are all on the same RAID 5 volume - the E drive. My only other
> option for placement of the tran log is a partition of the system drive
> - the D drive. The system drive is mirrored. The operating system
> resides on the other logical partition of the system drive - the C
> drive. I'm concerned that if I move the tran log to the D drive it
> might conflict with operating system activity. This is a dedicated SQL
> Server so I'm hoping that it won't be too much overhead on the system
> drive to place the tran log there as well. Am I better off moving the
> tran log to the system drive or keeping it on the E drive with the rest
> of the data?
>
> *** Sent via Developersdex http://www.codecomments.com ***
> Don't just participate in USENET...get rewarded for it!
>
|||Thanks, Andrew, for the response. I'll give it a try. The main reason
I had reservations about it is because before I realized that the D
drive was in fact just a logical partition of the system drive, I tried
to dump the entire database to that location. The database dump ran
much slower that way than when I dumped it to the same raid 5 where the
data itself resides - about twice as slow, in fact. I just wanted to be
sure that putting the tran logs on the operating system drive wouldn't
cause a similar slow down. Since it is a dedicated SQL Server hopefully
it won't pose a problem. Thanks again.
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!

Best GUID Storage

Because of the problem getting IDENTITY primary key values back when
inserting batches of rows, I would like to experiment with using
GUIDs. Within an application, I would like to assign the primary keys
to the rows and then pass them into the INSERT statements. Then I
wouldn't have to worry about using triggers or Identity scope to
determine the new primary keys.
My question is basically, what's the best datatype to store the GUIDs
in the column? From what I've read so far, it looks like
UniqueIdentifier or CHAR(40) are my options. Is there any drawback to
using UniqueIdentifier?If you are storing a GUID, then why not use Uniqueidentifier data type.
In SQL Server Books Online, read the page titled "Using uniqueidentifier
Data". This page discusses the advantages and disadvantages of this
datatype.
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"- TW" <Thumper@.kqrsrocks.com> wrote in message
news:151a6e6b.0402231323.29f24d45@.posting.google.com...
Because of the problem getting IDENTITY primary key values back when
inserting batches of rows, I would like to experiment with using
GUIDs. Within an application, I would like to assign the primary keys
to the rows and then pass them into the INSERT statements. Then I
wouldn't have to worry about using triggers or Identity scope to
determine the new primary keys.
My question is basically, what's the best datatype to store the GUIDs
in the column? From what I've read so far, it looks like
UniqueIdentifier or CHAR(40) are my options. Is there any drawback to
using UniqueIdentifier?|||TW,
I've used both, and it nearly always comes down to interoperability.
Some systems cannot deal with binary so you have to go varchar(36)/char(36).
Where did you get 40, incidentally?
James Hokes
"- TW" <Thumper@.kqrsrocks.com> wrote in message
news:151a6e6b.0402231323.29f24d45@.posting.google.com...
> Because of the problem getting IDENTITY primary key values back when
> inserting batches of rows, I would like to experiment with using
> GUIDs. Within an application, I would like to assign the primary keys
> to the rows and then pass them into the INSERT statements. Then I
> wouldn't have to worry about using triggers or Identity scope to
> determine the new primary keys.
> My question is basically, what's the best datatype to store the GUIDs
> in the column? From what I've read so far, it looks like
> UniqueIdentifier or CHAR(40) are my options. Is there any drawback to
> using UniqueIdentifier?|||Excellent choice to use Guids, imho.
Vyas has already pointed you to a good article on the topic, but here are a
couple of extra things not mentioned in that article:
(a) A benefit of using Guids instead of Identities is that if you ever need
to implement horizontal partitioning on the table, it will be substantially
easier with Guids. With Guids, the partitioning process is virtually
seemless to the application but partitioning tables with Identities nearly
always breaks the application.
(b) On the other hand, a problem with using Guids which is not mentioned in
that article is that T-SQL has no ISGUID() type function which causes minor
coding issues. Of course, it's possible to roll your own though.
Regards,
Greg Linwood
SQL Server MVP
"- TW" <Thumper@.kqrsrocks.com> wrote in message
news:151a6e6b.0402231323.29f24d45@.posting.google.com...
> Because of the problem getting IDENTITY primary key values back when
> inserting batches of rows, I would like to experiment with using
> GUIDs. Within an application, I would like to assign the primary keys
> to the rows and then pass them into the INSERT statements. Then I
> wouldn't have to worry about using triggers or Identity scope to
> determine the new primary keys.
> My question is basically, what's the best datatype to store the GUIDs
> in the column? From what I've read so far, it looks like
> UniqueIdentifier or CHAR(40) are my options. Is there any drawback to
> using UniqueIdentifier?

Best GUID Storage

Because of the problem getting IDENTITY primary key values back when
inserting batches of rows, I would like to experiment with using
GUIDs. Within an application, I would like to assign the primary keys
to the rows and then pass them into the INSERT statements. Then I
wouldn't have to worry about using triggers or Identity scope to
determine the new primary keys.
My question is basically, what's the best datatype to store the GUIDs
in the column? From what I've read so far, it looks like
UniqueIdentifier or CHAR(40) are my options. Is there any drawback to
using UniqueIdentifier?If you are storing a GUID, then why not use Uniqueidentifier data type.
In SQL Server Books Online, read the page titled "Using uniqueidentifier
Data". This page discusses the advantages and disadvantages of this
datatype.
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"- TW" <Thumper@.kqrsrocks.com> wrote in message
news:151a6e6b.0402231323.29f24d45@.posting.google.com...
Because of the problem getting IDENTITY primary key values back when
inserting batches of rows, I would like to experiment with using
GUIDs. Within an application, I would like to assign the primary keys
to the rows and then pass them into the INSERT statements. Then I
wouldn't have to worry about using triggers or Identity scope to
determine the new primary keys.
My question is basically, what's the best datatype to store the GUIDs
in the column? From what I've read so far, it looks like
UniqueIdentifier or CHAR(40) are my options. Is there any drawback to
using UniqueIdentifier?|||TW,
I've used both, and it nearly always comes down to interoperability.
Some systems cannot deal with binary so you have to go varchar(36)/char(36).
Where did you get 40, incidentally?
James Hokes
"- TW" <Thumper@.kqrsrocks.com> wrote in message
news:151a6e6b.0402231323.29f24d45@.posting.google.com...
> Because of the problem getting IDENTITY primary key values back when
> inserting batches of rows, I would like to experiment with using
> GUIDs. Within an application, I would like to assign the primary keys
> to the rows and then pass them into the INSERT statements. Then I
> wouldn't have to worry about using triggers or Identity scope to
> determine the new primary keys.
> My question is basically, what's the best datatype to store the GUIDs
> in the column? From what I've read so far, it looks like
> UniqueIdentifier or CHAR(40) are my options. Is there any drawback to
> using UniqueIdentifier?|||Excellent choice to use Guids, imho.
Vyas has already pointed you to a good article on the topic, but here are a
couple of extra things not mentioned in that article:
(a) A benefit of using Guids instead of Identities is that if you ever need
to implement horizontal partitioning on the table, it will be substantially
easier with Guids. With Guids, the partitioning process is virtually
seemless to the application but partitioning tables with Identities nearly
always breaks the application.
(b) On the other hand, a problem with using Guids which is not mentioned in
that article is that T-SQL has no ISGUID() type function which causes minor
coding issues. Of course, it's possible to roll your own though.
Regards,
Greg Linwood
SQL Server MVP
"- TW" <Thumper@.kqrsrocks.com> wrote in message
news:151a6e6b.0402231323.29f24d45@.posting.google.com...
> Because of the problem getting IDENTITY primary key values back when
> inserting batches of rows, I would like to experiment with using
> GUIDs. Within an application, I would like to assign the primary keys
> to the rows and then pass them into the INSERT statements. Then I
> wouldn't have to worry about using triggers or Identity scope to
> determine the new primary keys.
> My question is basically, what's the best datatype to store the GUIDs
> in the column? From what I've read so far, it looks like
> UniqueIdentifier or CHAR(40) are my options. Is there any drawback to
> using UniqueIdentifier?

Benefits/drawbacks with NVarChar(max)

Hi,

I wonder if there are any drawbacks with NVarChar(max) contra e.g. NVarChar(40) when it comes to performance and amount of storage used?

Are there other arguments to use e.g. NVarChar(40) than that you have more control of how long the strings are when you set the upper limit?

I'm using Sql Server 2005.

Tomsi

Hey Tomsi. Using any of the (max) datatypes will basically tell the server that the data in that column could possibly grow to 2gb if desired. To achieve this, the database engine will evaluate the size of the data being inserted/updated into the column and store it appropriately depending on the size. If the size of the data being stored will fit in-row with the rest of the data for that row (i.e. if it's less than 8k minus the size of the other columns in the row give or take some other considerations), then SQL Server will store the data in-row with the rest of the row data. If it is larger than that and can't be stored in-row with the rest of the row's data, then SQL Server will store a pointer in the row with the data that points to the location of the actual data elsewhere. During read/write operations for that row from now on, the engine will have to jump from the pointer to the data, get/update the data, then jump back to the pointer, so read/write time is slowed in this case.

You'll also see a slight additional overhead for determining if the data can fit in row or not, but that will be miniscule.

General recommendation would be that if you know the size of the data will never exceed 'x', and x is <= ~8000 bytes, then use nvarchar(x)...if the data will in some cases or always exceed that size, then use the (max) indicator...

You could see this article for a bit more info and go from there

http://msdn2.microsoft.com/en-us/library/ms178158.aspx

HTH