Re: Data Types

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

From: Andrew J. Kelly (sqlmvpnooospam_at_shadhawk.com)
Date: 02/20/04 

Date: Fri, 20 Feb 2004 12:22:42 -0500

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.
> > >
> > >
> >
> >
>
>

Relevant Pages

  • Re: Data Types
    ... > moved to SQL Server and I don't know the best newsgroups yet. ... > Does a Unique Identifier data type take up less storage space than a 32 ... A uniqueidentifier uses 16 bytes. ... but I'm looking at the data types and think that the TimeStamp ...
    (microsoft.public.sqlserver.programming)
  • Re: MS-SQL Related
    ... What error message do you see? ... What is uniqueidentifier as a data type? ... Also what is the data type for setting unique STRINGS ((nchar, ... SQL Server does not allow me set primary keys for columns where data ...
    (comp.databases.ms-sqlserver)
  • Re: Need help grouping members into families
    ... Thank you so much Brendan. ... Using the integer data type and the identify property worked! ... >> FamilyID, etc.) are the SQL Server data type uniqueidentifier, what should ...
    (microsoft.public.access.queries)
  • Re: Access 97 to SQL
    ... The timestamp is a data type, ... the timestamp field does not need to be part of the SELECT ... > converted the Access tables to a SQL Server 2000 database and linked the ... > the way I set up Autonumbering in SQL? ...
    (microsoft.public.access.externaldata)
  • Re: The tricky thing is that you can even multiply by numeric to make it
    ... Arithmetic overflow error converting expression to data type numeric. ... I'm using SQL Server 2008 version 10.50.1600. ... I have been trying to use the ROUND() function: ... Is an expression of the exact numeric or approximate numeric data type category, except for the bit data type. ...
    (microsoft.public.sqlserver.programming)