Re: Data Types
From: Andrew J. Kelly (sqlmvpnooospam_at_shadhawk.com)
Date: 02/20/04
- Next message: Mark Frank: "Query for dropped objects"
- Previous message: Venkatesh: "Re: Row Locking and Transaction"
- In reply to: Steve: "Re: Data Types"
- Next in thread: Steve Amey: "Re: Data Types"
- Reply: Steve Amey: "Re: Data Types"
- Messages sorted by: [ date ] [ thread ]
Date: Fri, 20 Feb 2004 13:30:01 -0500
Other than straight binary it is pretty hard to beat the efficiency of an
INTEGER. It's compact and easier to match, sort etc plus it takes up a lot
less room to store it. That can make a big difference if you don't have
enough ram to fit the entire workable data set in cache. Just think if you
went with a 4 byte INT vs. a 32 Btye Char you would save 28 bytes per
column, per row plus the same savings in all indexes. That can add up to a
lot more data or indexes that actually fit in cache. If you want to
generate your own ID vs using IDENTITY that is fine. It's a few lines of
code in a stored proc to do this. A primary key should only be a unique
number. So why use a 32 byte char when you can use a 4 byte INT?
-- Andrew J. Kelly SQL Server MVP "Steve" <stevea@centurion-ms_RemoveThis_.co.uk> wrote in message news:edp6Hr99DHA.4020@TK2MSFTNGP09.phx.gbl... > Hi Andrew > > I did look at using an INT type, but to be honest I think I'm going to have > a difficult time trying to convince my boss to use the Unique Identifier > type, let alone the Int type. We currently create our own ID's of varchar 32 > characters which are basically the same as UID's but without the braces and > hyphens. > > I shall give it a go on Monday. Is there a performance advantage using the > INT over the UID? I know that the reduced storage capacity will yield better > performance anyway, but does it result in faster queries? If it does then > it'll be harder for the boss to reject it :o) > > Thanks for your help. > > Steve. > > "Andrew J. Kelly" <sqlmvpnooospam@shadhawk.com> wrote in message > news:OeGumY99DHA.1312@TK2MSFTNGP09.phx.gbl... > > Steve, > > > > If what your looking for is an automatically generated value that takes up > > less space why not use INT and IDENTITY()? It's only 4 bytes and a LOT > > easier to use. > > > > -- > > > > Andrew J. Kelly > > SQL Server MVP > > > > > > "Steve" <stevea@centurion-ms_RemoveThis_.co.uk> wrote in message > > news:%23ZNqBG99DHA.4064@TK2MSFTNGP09.phx.gbl... > > > Hi Tibor > > > > > > Thanks for the welcome and the answers :) > > > > > > I was looking about for the answers in BOL, I just wanted claraification > > > before I make my final decision. A UID takes up 16 bytes, and a varchar > > with > > > a length of 32 characters takes up 32 bytes. Given the choice I would > want > > > to use UID as it gets generated automatically and it takes up less > > storage. > > > > > > Do you know how I can set the Default Value for the CreateDate to be the > > > current date/time? I guess I'll have to take care of the ModifyDate some > > > other way. The values will be Null if there isn't an appropriate date in > > > there. > > > > > > Thanks. > > > > > > Regards, > > > Steve. > > > > > > "Tibor Karaszi" <tibor_please.no.email_karaszi@hotmail.nomail.com> wrote > > in > > > message news:%23FTFF089DHA.2524@TK2MSFTNGP11.phx.gbl... > > > > Steve, > > > > > > > > Inline... > > > > > > > > -- > > > > Tibor Karaszi, SQL Server MVP > > > > Archive at: > > > > > > > > > > http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver > > > > > > > > > > > > "Steve" <stevea@centurion-ms_RemoveThis_.co.uk> wrote in message > > > > news:u$EJCk89DHA.2324@tk2msftngp13.phx.gbl... > > > > > Hi all > > > > > > > > > > Sorry if this isn't the best place to ask this question. I've only > > > > recently > > > > > moved to SQL Server and I don't know the best newsgroups yet. > > > > > > > > This group is the right one for these types of questions. And, Welcome > > > :-). > > > > > > > > > > > > > > Does a Unique Identifier data type take up less storage space than a > > 32 > > > > > character varchar data type? > > > > > > > > I encourage you to find the documentation for these things (Books > > Online), > > > > the Transact-SQL reference. A uniqueidentifier uses 16 bytes. > > > > DECLARE @a uniqueidentifier > > > > SET @a = NEWID() > > > > SELECT DATALENGTH(@a), LEN(@a) > > > > > > > > > > > > > Also, I have 2 date fields in my tables, one > > > > > for when the record is created and the other for when the record is > > > > > modified. In the past I have set these values from my application > > > > (seperate > > > > > VB app), but I'm looking at the data types and think that the > > TimeStamp > > > > type > > > > > would update to the current time when the record is modified (is > that > > > > > correct?), > > > > > > > > No, timestamp in SQL Server is really a binary "counter", it doesn't > > carry > > > a > > > > datetime value. This partly is why MS is trying to make us use the > name > > > > 'rowversion' for the datatype instead. > > > > > > > > > but what's the best method to set the create date? I would prefer > > > > > not to have to create triggers on all my tables (there are quite a > > few). > > > > > > > > You can have a default for your INSERT statements, but that won't take > > > care > > > > of UPDATEs. Your choices are, as far as I can see, triggers or > > > client/stored > > > > procedure code. > > > > > > > > > > > > > When specifying a TimeStamp field, can you tell it only to update > the > > > time > > > > > if there isn't a value in there already, as will be the case for new > > > > > records? > > > > > > > > By "isn't a value", do you mean NULL or time portion is 00:00:00? > > Doesn't > > > > matter much as you would need to set the "whole " value in your code, > > > there > > > > are no built-in mechanisms to only set one part of the value. > > > > > > > > > > > > > > Thanks for any help. > > > > > > > > > > Kind Regards, > > > > > Steve. > > > > > > > > > > > > > > > > > > > > > > > > > > > > > >
- Next message: Mark Frank: "Query for dropped objects"
- Previous message: Venkatesh: "Re: Row Locking and Transaction"
- In reply to: Steve: "Re: Data Types"
- Next in thread: Steve Amey: "Re: Data Types"
- Reply: Steve Amey: "Re: Data Types"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
