SQL Null: Grouping and sorting

Because SQL Server defines all Null markers as being unequal to one another, a special definition was required in order to group by Nulls together when performing certain operations.  SQL defines “any two values that are equal to one another, or any two Nulls”, as “not distinct”.  This definition of not distinct allows SQL to group and sort Nulls when the “GROUP BY” clause are used.  Let’s look at an example using group by and nulls.  You will see, that SQL Server does group them together by using the not distinct definition: DECLARE @table1 TABLE( ID INT, myStr VARCHAR(100)) INSERT INTO @table1( ID, myStr) Values (1, 'value a'), (2, 'value b'), (3, 'value a'), (4, 'value a'), (5, 'value b'), (6, null), (7, 'value b'), (8, null), (9, null), (10, 'value a'), (11, null), (12, null) SELECT * FROM @table1 SELECT myStr,       COUNT(1) as CountFROM   @table1GROUP  BY myStr The 1st select results show the raw values: ID myStr 1 value a 2 value b 3 value a 4 value a 5 value b 6 NULL 7 value b 8 NULL 9 NULL 10 value a 11 NULL 12 NULL The 2nd results show the group by: myStr Count NULL 5 value a 4 value b 3 As you can see, when SQL Server completes the group by clause it combines rows with NULL values.  When thinking about how it handles string concatenations, this is a little twist that can catch you off guard or make you think.  Distinct, same thing: SELECT distinct myStr FROM @table1 The result is 3 rows: NULL, value a, value b Hope this helps you when thinking about null values and grouping.  Try the example and look how SQL Server handles the default sort order for Nulls.

TSQL Tuesday #11 Misconceptions – Enable AWE on 64bit SQL Servers
This post is part of the monthly community event called T-SQL Tuesday started by Adam Machanic ( blog | twitter ) and hosted by someone else each month. This month the host is Sankar Reddy ( blog | twitter ) and the topic is Misconceptions in SQL Server...(read more)

Security Auditing a Database
A new blogpost has been posted by thirster42 on Oct 8, 2010 in category Microsoft SQL Server Admin

SQL Null: a set of posts on the null
Null is a special marker used in Structured Query Language (SQL) to indicate that a data value does not exist in the database. Introduced by the creator of the relational database model, E. F. Codd, SQL Null serves to fulfill the requirement that all true relational database management systems (RDBMS) support a representation of "missing information and inapplicable information". Codd also introduced the use of the lowercase Greek omega (ω) symbol to represent Null in database theory. NULL is also an SQL reserved keyword used to identify the Null special marker. Null has been the focus of controversy and a source of debate because of its associated three-valued logic (3VL), special requirements for its use in SQL joins, and the special handling required by aggregate functions and SQL grouping operators. Although special functions and predicates are provided to properly handle Nulls, opponents feel that resolving these issues introduces unnecessary complexity and inconsistency into the relational model of databases. Since Null is not a member of any data domain, it is not considered a "value", but rather a marker (or placeholder) indicating the absence of value. Because of this, comparisons with Null can never result in either True or False, but always in a third logical result, Unknown. In my next few posts, I will explore the null.

PASS 2010 Birds of a Feather Lunch
We are just weeks away from the #SQLPASS North American Summit for 2010 and yesterday. Mike Walsh ( Blog | Twitter ) posted the list of topics for the Birds of a Feather Lunch event that was started last year and has been continued into this year. ...(read more)

List_all_SQL_Agent_jobs_and_the_owner_of_those_jobs
A new article has been posted in the wiki.

TSQL Tuesday #11 – Physical IO’s Don’t Always Accumulate Wait Times
It is time again for another TSQL Tuesday, this time hosted by my good friend and fellow MVP, Sankar Reddy ( Blog | Twitter ). This month’s topic is Misconceptions about SQL Server and as Sankar points out in this months, there are so many misconceptions...(read more)

Scripting Database Mail Configuration with Powershell and SMO
Setting up Database Mail in SQL Server 2005 and 2008 is a common task that is performed post server setup. However, if you just took over a fairly large environment and faced having to configure Database Mail on dozens, maybe hundreds of servers, SQL...(read more)

SSIS runs in BIDS but not with SQL Agent
A new blogpost has been posted by Ted Krueger (onpnt) on Oct 6, 2010 in category Database Administration

Working with the SQL Server Powershell Provider and Central Management Servers from Powershell
I am a big fan of the Central Management Server setup that exists in SQL Server 2008, as well as how you can leverage this for the Enterprise Policy Management Framework that Laura Rubbelke ( Blog | Twitter ) created on Codeplex.  Today one of my...(read more)

SQLBits 7 Wrap Up
Last week I was in Great Britain for SQL Bits 7.  This was the first conference outside of the United States that I have attended, and I was honored to attend SQL Bits 7 as a speaker during Friday’s Sessions.  The city of York is absolutely...(read more)

T-SQL Tuesday #008: Turning Civilians into Soldiers
          This month’s T-SQL Tuesday, started by Adam Machanic ( Blog | Twitter ),  is being put on by SQLServerCentral author and MCM, Robert Davis ( Blog | Twitter ).  This month the topic is learning and teaching...(read more)

Wisconsin SQL Server User Group
A new blogpost has been posted by Ted Krueger (onpnt) on Oct 11, 2010 in category Database Administration

The History of SQL Server Integration Services
A new blogpost has been posted by Ted Krueger (onpnt) on Oct 11, 2010 in category Database Administration

SQL Server 2008 R2 DACPAC Bug - Cannot insert the value NULL into column 'created_by', table 'msdb.dbo.sysdac_instances_internal'
While trying out the new DACPAC feature in SQL Server 2008 R2 recently, I ran into a bug that makes no sense to me at all.  In my environment we use an Active Directory Group for the SQL Database Administrators and that group is a member of the sysadmins...(read more)

TSQL Tuesday #11 – Misconceptions - The Tempdb Log File and VLF Counts
This post is part of the monthly community event called T-SQL Tuesday started by Adam Machanic ( blog | twitter ) and hosted by someone else each month. This month the host is Sankar Reddy ( blog | twitter ) and the topic is Misconceptions in SQL Server...(read more)

There is no such thing as a “Small Change” to a production database
It seems like every week I get hit up with some kind of “Its a view only for this specific report,” or “It is a simple change that the vendor would have made if they had access” type of request related to SQL Server.  If you’ve never dealt with me,...(read more)