Coding practices for SET operations.

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.

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

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]

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

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.

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.

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.

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.

<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='' />]

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

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

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

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.

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.

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.