SQL Server Performance

Coding practices for SET operations.

Discussion in 'General Developer Questions' started by Haywood, Nov 10, 2005.

  1. Haywood New Member

    Is it generally considered good practice to use parens () around set operations that do not require a SELECT statement?

    SET @Foo = (CONVERT(CHAR(10),getdate(),100))

    Or

    SET @Foo = CONVERT(CHAR(10),getdate(),100)


    Just looking for some advice/experiences/best practices.

    Thank you.
  2. benwilson New Member

    i can only speak for myself, but i dont use () unless they are needed- so i would go with

    SET @Foo = CONVERT(CHAR(10),getdate(),100)




    'I reject your reality and substitute my own' - Adam Savage
  3. vsnreddi New Member

    CAST and CONVERT<br />Explicitly converts an expression of one data type to another. CAST and CONVERT provide similar functionality.<br /><br />Syntax<br />Using CAST:<br /><br />CAST ( expression AS data_type ) <br /><br />Using CONVERT:<br /><br />CONVERT ( data_type [ ( length ) ] , expression [ , style ] )<br /><br />Arguments<br />expression<br /><br />Is any valid Microsoft® SQL Server™ expression. For more information, see Expressions. <br /><br />data_type<br /><br />Is the target system-supplied data type, including bigint and sql_variant. User-defined data types cannot be used. For more information about available data types, see Data Types. <br /><br />length<br /><br />Is an optional parameter of nchar, nvarchar, char, varchar, binary, or varbinary data types. <br /><br />style<br /><br />Is the style of date format used to convert datetime or smalldatetime data to character data (nchar, nvarchar, char, varchar, nchar, or nvarchar data types), or the string format when converting float, real, money, or smallmoney data to character data (nchar, nvarchar, char, varchar, nchar, or nvarchar data types).<br /><br />SQL Server supports the date format in Arabic style, using Kuwaiti algorithm.<br /><br />In the table, the two columns on the left represent the style values for datetime or smalldatetime conversion to character data. Add 100 to a style value to get a four-place year that includes the century (yyyy).<br /><br />Without century (yy) With century (yyyy) <br />Standard <br />Input/Output** <br />- 0 or 100 (*) Default mon dd yyyy hh:miAM (or PM) <br />1 101 USA mm/dd/yy <br />2 102 ANSI yy.mm.dd <br />3 103 British/French dd/mm/yy <br />4 104 German dd.mm.yy <br />5 105 Italian dd-mm-yy <br />6 106 - dd mon yy <br />7 107 - Mon dd, yy <br />8 108 - hh:mm<img src='/community/emoticons/emotion-7.gif' alt=':s' />s <br />- 9 or 109 (*) Default + milliseconds mon dd yyyy hh:mi<img src='/community/emoticons/emotion-7.gif' alt=':s' />s:mmmAM (or PM) <br />10 110 USA mm-dd-yy <br />11 111 JAPAN yy/mm/dd <br />12 112 ISO yymmdd <br />- 13 or 113 (*) Europe default + milliseconds dd mon yyyy hh:mm<img src='/community/emoticons/emotion-7.gif' alt=':s' />s:mmm(24h) <br />14 114 - hh:mi<img src='/community/emoticons/emotion-7.gif' alt=':s' />s:mmm(24h) <br />- 20 or 120 (*) ODBC canonical yyyy-mm-dd hh:mi<img src='/community/emoticons/emotion-7.gif' alt=':s' />s(24h) <br />- 21 or 121 (*) ODBC canonical (with milliseconds) yyyy-mm-dd hh:mi<img src='/community/emoticons/emotion-7.gif' alt=':s' />s.mmm(24h) <br />- 126(***) ISO8601 yyyy-mm-dd Thh:mm<img src='/community/emoticons/emotion-7.gif' alt=':s' />s:mmm(no spaces) <br />- 130* Kuwaiti dd mon yyyy hh:mi<img src='/community/emoticons/emotion-7.gif' alt=':s' />s:mmmAM <br />- 131* Kuwaiti dd/mm/yy hh:mi<img src='/community/emoticons/emotion-7.gif' alt=':s' />s:mmmAM <br /><br /><br />* The default values (style 0 or 100, 9 or 109, 13 or 113, 20 or 120, and 21 or 121) always return the century (yyyy).<br />** Input when converting to datetime; output when converting to character data.<br />*** Designed for XML use. For conversion from datetime or smalldatetime to character data, the output format is as described in the table. For conversion from float, money, or smallmoney to character data, the output is equivalent to style 2. For conversion from real to character data, the output is equivalent to style 1.<br /><br /><br /><br />VSNREDDY<br /><br />Be pure to do good.[Vivekananda]
  4. Madhivanan Moderator


    SET @Foo = CONVERT(CHAR(10),getdate(),100) is enough

    Why do you need the conversion on Date Column?

    After that conversion it is Char although it looks Date so that you may not be able to do comparison, calculation, etc

    Madhivanan

    Failing to plan is Planning to fail
  5. ranjitjain New Member

    But instead of using SET you should use ANSI Standard Select.

    Select also performs faster then SET.
    Consider this,

    select @a=1,@b=2,@c=3

    but you can not assign values using SET in one line so three SET operation needed.

    SET @a=1
    SET @b=2
    SET @c=3
  6. Madhivanan Moderator

  7. FrankKalis Moderator

    Hm, assigning variables with SELECT is AFAIK *not* ANSI standard. ANSI always uses SET. When you read through the article Madhivanan referenced you'll find that there is no really significant performance advantage from one over the other. The biggest advantage of SELECT however is, that you can assign multiple values at one time.

    --
    Frank Kalis
    Microsoft SQL Server MVP
    http://www.insidesql.de
    Heute schon gebloggt?http://www.insidesql.de/blogs
    Ich unterstütze PASS Deutschland e.V. http://www.sqlpass.de)
  8. ranjitjain New Member

    FYI
    Refer this article
    http://www.sql-server-performance.com/q&a102.asp

    which says
    "In most cases, you will want to use the ANSI standard, which says you should use SELECT."

    Afterall even i have read the vyaskan article and from that itself i had given eg:

    Select also performs faster then SET.
    Consider this,

    select @a=1,@b=2,@c=3

    but you can not assign values using SET in one line so three SET operation needed.
    SET @a=1
    SET @b=2
    SET @c=3

    Where i used select for multiple assignments which proves its faster and even in the eg given by Vyaskan Single Statement in while loop performs faster by milliseconds.
  9. Adriaan New Member

    quote:Originally posted by Madhivanan


    SET @Foo = CONVERT(CHAR(10),getdate(),100) is enough

    Why do you need the conversion on Date Column?

    After that conversion it is Char although it looks Date so that you may not be able to do comparison, calculation, etc

    Madhivanan

    Failing to plan is Planning to fail
    This is - very unfortunately - the only way in SQL 6.5/7.0/2000 to get rid of the time part of GETDATE(). I never understood why they have a function called GETDATE() that returns more than just the date, but that's just me.
  10. Madhivanan Moderator

    Well. If it is for display purpose I prefer to do it in the presentation layer by using Format function. If data are to be exported then I use Convert function. But anyway we can get only date part in some methods<br /><br />Select DateAdd(day,DateDiff(day,0,getdate()),0)<br />Select Cast(Convert(varchar,getdate(),112) as DateTime)<br /><br />which are still Dates although having zero time part<br /><br />But it would be nice if there is way to omit that zero time part and still have Valid Date [<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br /><br />Madhivanan<br /><br />Failing to plan is Planning to fail
  11. FrankKalis Moderator

    quote:Originally posted by ranjitjain

    FYI
    Refer this article
    http://www.sql-server-performance.com/q&a102.asp

    which says
    "In most cases, you will want to use the ANSI standard, which says you should use SELECT."
    I don't think this is a correct statement. I tend to agree with Vyas Kondreddi here

    quote:
    Now coming to the differences between SET and SELECT! Are standards important to you? If your answer is 'yes', then you should be using SET. This is because, SET is the ANSI standard way of assigning values to variables, and SELECT is not.
    This is also my understanding, but I'll check this out and rewrite the article


    --
    Frank Kalis
    Microsoft SQL Server MVP
    http://www.insidesql.de
    Heute schon gebloggt?http://www.insidesql.de/blogs
    Ich unterstütze PASS Deutschland e.V. http://www.sqlpass.de)
  12. Adriaan New Member

    Yes, the problem is that in our systems we really DON'T need the time part. We don't want to see it, we don't want to have it, and we most certainly don't want to have to remove it. All we can do now is wait until we upgrade to SQL 2005 - rumour has it that SQL 2005 has a data type that holds the date, the whole date, and nothing but the date.
  13. Adriaan New Member

    About SET vs. SELECT ...

    Not really a technical argument, but I do prefer the shortness of

    SELECT @var1 = 'a', @Var2 = 'b'

    over

    SET @var1 = 'a'
    SET @var2 = 'b'

    ... at least most of the time - of course there will always be a point where such a SELECT statement becomes difficult to read.
  14. ranjitjain New Member

    <blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"><i>Originally posted by FrankKalis</i><br /><br /><blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"><i>Originally posted by ranjitjain</i><br /><br />FYI<br />Refer this article<br /<a target="_blank" href=http://www.sql-server-performance.com/q&a102.asp>http://www.sql-server-performance.com/q&a102.asp</a><br />which says <br />"In most cases, you will want to use the ANSI standard, which says you should use SELECT."<br /><hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote"><br />I don't think this is a correct statement. I tend to agree with Vyas Kondreddi here<br /><blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"><br />Now coming to the differences between SET and SELECT! Are standards important to you? If your answer is 'yes', then you should be using SET. This is because, SET is the ANSI standard way of assigning values to variables, and SELECT is not.<br /><hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote"><br />This is also my understanding, but I'll check this out and rewrite the article<br />--<br />Frank Kalis<br />Microsoft SQL Server MVP<br /<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a><br />Heute schon gebloggt?<a target="_blank" href=http://www.insidesql.de/blogs>http://www.insidesql.de/blogs</a><br />Ich unterstí²“íº¥ PASS Deutschland e.V. <a target="_blank" href=http://www.sqlpass.de>http://www.sqlpass.de</a>) <br /><hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote"><br />Hi Frank,<br />Yes you should check this out and rewrite the article.<br />It's really confusing.[<img src='/community/emoticons/emotion-1.gif' alt=':)' />]
  15. Haywood New Member

    Thanks for the replies, I'll stick to going without the parens for my set operations.<br /><br />Myself, if I don't have to SELECT the value for a parameter I prefer to use SET.<br /><br />The original post was merely an example that I pulled from some dynamic sql. I'm quite familiar with CAST/CONVERT operations. <img src='/community/emoticons/emotion-1.gif' alt=':)' /><br /><br />And I agree too that it is very frustrating to not have a seperate date and time function....
  16. Haywood New Member

    quote:Originally posted by Adriaan

    Yes, the problem is that in our systems we really DON'T need the time part. We don't want to see it, we don't want to have it, and we most certainly don't want to have to remove it. All we can do now is wait until we upgrade to SQL 2005 - rumour has it that SQL 2005 has a data type that holds the date, the whole date, and nothing but the date.


    Sorry, but you're still gonna have to wait. 2005 still doesn't provide a seperate set of functions.

    You can use ODBC and the timestamp datatype which supports a 'split' of the data type. But it's pretty much the same as having to convert and with less options it appears at first glance...

    ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/acab9375-ce14-4a9e-83f0-ba05d900e94a.htm
  17. derrickleggett New Member

    [<img src='/community/emoticons/emotion-1.gif' alt=':)' />] I still don't see why everyone makes such a huge deal out of datetime with time. We created a couple really simple functions for a date as start time and a date as end time. The start time changes the date to beginning time of day. The end time changes the date to ending time of day. Use those functions in select, where, etc and you're gold. It's easy and straightforward. Just an idea and fyi. <br /><br />And no, SQL Server 2005 does not have date and time as seperate elements. I believe they gave up hope on that after the April CTP, although I couldn't swear by it.<br /><br />MeanOldDBA<br />derrickleggett@hotmail.com<br /><br />When life gives you a lemon, fire the DBA.
  18. Adriaan New Member

    Derrick,

    If information is irrelevant, it shouldn't be stored in the database - so why can't we just have a timeless data type for dates.

    The time part is just causing additional work that we would very much prefer to shed off.
  19. FrankKalis Moderator

    Well, first I am suprised that the long awaited DATE data type now isn't available. [<img src='/community/emoticons/emotion-6.gif' alt=':(' />]<br />But actually you can have a "DATE-only" column. Just use an INT column. You only have to make sure that either SQL Server or your front-end correctly catches rounding issues. A simple CAST( ... AS INT) can fool you. But a CAST(... -0.5 AS INT) should always yield the correct INT value. Still a workaround, but it could be worse, since you can use all date functions with an INT column. <br /><br /><br />Btw: I haven't found the exact passage in the SQL 92 standard about SET as standard for variable assignment, but when you search the Google groups and take Joe Celko as authority for this standard, you'll find that SET *is* the ANSI SQL way of assigning values to variables. It is *not* SELECT. I'll update the text here on the site asap. Thanks ranjitjain fort making em aware of this! [<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br /><br />--<br />Frank Kalis<br />Microsoft SQL Server MVP<br /<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a><br />Heute schon gebloggt?<a target="_blank" href=http://www.insidesql.de/blogs>http://www.insidesql.de/blogs</a><br />Ich unterstütze PASS Deutschland e.V. <a target="_blank" href=http://www.sqlpass.de>http://www.sqlpass.de</a>) <br />
  20. Haywood New Member

    I can give a great example of where seperate date & time would be usefull...

    Have you ever looked at last_occurred_time in msdb.dbo.sysalerts? It's an integer representation of a 24/hr time format. sysalerts also contains a column called last_occurred_date which is an int, but you can convert the last_occurred_date to a proper date value for display purposes. However, you cannot convert the time column, because it associates the time with a date conversion...

    Unless someone else knows how to convert last_occurred_time to just a time value.
  21. FrankKalis Moderator

    Not that I think these are optimal ways to convert last_occurrence_time into a DATETIME, but I think they will work:<br /><pre id="code"><font face="courier" size="2" id="code"><br />SELECT <br /> DATEADD(second,(((last_occurrence_time*1.0)/100)-CAST(last_occurrence_time/100 AS INT))*100,<br /> DATEADD(minute,(last_occurrence_time/100)-(last_occurrence_time/10000*100),<br /> DATEADD(hour,last_occurrence_time/10000, CAST(last_occurrence_date AS CHAR(<img src='/community/emoticons/emotion-11.gif' alt='8)' />)))) last_occurrence_time<br /> FROM msdb.dbo.sysalerts<br /> WHERE ISDATE(last_occurrence_date) = 1<br /><br />SELECT <br /> DATEADD(second,CAST(SUBSTRING(REPLACE(STR(last_occurrence_time,6), ' ' ,'0'),5,2) AS INT),<br /> DATEADD(minute,CAST(SUBSTRING(REPLACE(STR(last_occurrence_time,6), ' ' ,'0'),3,2) AS INT),<br /> DATEADD(hour, CAST(SUBSTRING(REPLACE(STR(last_occurrence_time,6), ' ' ,'0'),1,2) AS INT), CAST(last_occurrence_date AS CHAR(<img src='/community/emoticons/emotion-11.gif' alt='8)' />))))<br /> FROM msdb.dbo.sysalerts<br /> WHERE ISDATE(last_occurrence_date) = 1<br /></font id="code"></pre id="code"><br /><br />--<br />Frank Kalis<br />Microsoft SQL Server MVP<br /<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a><br />Heute schon gebloggt?<a target="_blank" href=http://www.insidesql.de/blogs>http://www.insidesql.de/blogs</a><br />Ich unterstütze PASS Deutschland e.V. <a target="_blank" href=http://www.sqlpass.de>http://www.sqlpass.de</a>) <br />
  22. Haywood New Member

    Frank, that is similar to what I was crufting up to display it yesterday afternoon.

    It still creates a darn good argument for seperate data types tho...one of these days maybe we'll get them.

Share This Page