Know your UNION(s), NULL(s), COUNT(s) ?

Question

Post reply

Know your UNION(s), NULL(s), COUNT(s) ?

Viewing 10 posts - 16 through 24 (of 24 total)

You must be logged in to reply to this topic. Login to reply

Yelena Varshal SSC-Dedicated Points: 34286 More actions · Answer 1

This is in regards to the previous post:

Why we can select count from other positions?

SELECT COUNT (25) FROM #TEST

We can not order by 25:

SELECT * FROM #TEST ORDER BY 25 DESC

this returns a message:

Msg 108, Level 16, State 1, Line 1

The ORDER BY position number 25 is out of range of the number of items in the select list.

But we do can do COUNT(25)

Regards,Yelena Varsha

Todd Carrier Hall of Fame Points: 3951 More actions · Answer 2

Aha,

I am wrong in assuming COUNT (1) refers to the first column.

SELECT COUNT (25)

--or

SELECT COUNT ('something')

these both return 1, as the count of the literal expression, be it integer or character

Similar to

SELECT 1,2,3,'something' FROM #TEMP

Notice that 1,2,3, and the string above are literals.

Can anyone thing of another instance where you would refer to the ordinal positions of columns as 1,2, or 3... other than and ORDER BY?

Todd Carrier
MCITP - Database Administrator (SQL 2008)
MCSE: Data Platform (SQL 2012)

Yelena Varshal SSC-Dedicated Points: 34286 More actions · Answer 3

Sure! A lot. In VB or other front end in relation to Recordset or Dataset references or controls like GRID control.

How it relates to SQL? We can use this code in CLR Stored Procedures for example.

This is an example for VBA:

http://msdn.microsoft.com/en-us/library/aa192404.aspx

If Activedocument.Tables.Count >= 1 Then

Set myTable = Activedocument.Tables(1)

myTable.Columns.Add BeforeColumn:=myTable.Columns(1)

myTable.Columns.DistributeWidth

End If

OR

http://msdn.microsoft.com/en-us/library/wc06dx4f.aspx

// Set the column header names.

dataGridView.Columns[0].Name = "Recipe";

dataGridView.Columns[1].Name = "Category";

dataGridView.Columns[2].Name = thirdColumnHeader;

dataGridView.Columns[3].Name = "Rating";

Regards,Yelena Varsha

Kangana Beri SSCrazy Points: 2134 More actions · Answer 4

So, in which situation would you use count(1) instead of a count(*)? Or is it even a good practice to use count(1)??

To me it seems that they are essentially doing the same job when used with a FROM clause but the meaning of count(*) is very clear.

Pat Reiter Old Hand Points: 378 More actions · Answer 5

I suppose that one would use count(1) to increase the mystery in one's uncommented code.:hehe:

Here's a justification for count(1):

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1242198&SiteID=1

Pat Reiter Old Hand Points: 378 More actions · Answer 6

re PL/SQL: I confess that I have been visiting the 'dark side'.

Brian Munier SSCrazy Points: 2875 More actions · Answer 7

I went to check my guess and ran:

WITH DATA (Numbers) AS

(SELECT NULL UNION ALL

SELECT NULL UNION ALL

SELECT 1 UNION ALL

SELECT 2 UNION

SELECT 3)

SELECT COUNT(ALL Numbers) AS NULLNumberCount FROM DATA

WHERE Numbers IS NULL

and got:

Msg 156, Level 15, State 1, Line 1

Incorrect syntax near the keyword 'WITH'.

Ooops

Christopher Stobbs SSC-Insane Points: 21098 More actions · Answer 8

if your with is not the first line of code, then you need a ; before it

----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley

:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]

SQL-4-Life

Brian Munier SSCrazy Points: 2875 More actions · Answer 9

Christopher Stobbs (9/16/2008)
if your with is not the first line of code, then you need a ; before it

Ok, it worked. Thanks.

kapil_kk SSC-Insane Points: 21316 More actions · Answer 10

gr8 question....

_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/