SQL Server Errors: ORDER BY items must appear in the select list if SELECT DISTINCT is specified

SQL Server force you to put columns in SELECT DISTINCT list which are part of ORDER BY clause. But what if we don’t want to add that column/s in SELECT list. Lets try it.

--create temporary table to hold records

CREATE TABLE #DistinctSortTest (Val1 INT, Val2 INT)

GO

--insert some records

INSERT INTO #DistinctSortTest

VALUES (1,100),(8,55),(3,33),(1,1),(9,999)

GO

--lets see what we have in table

SELECT * FROM #DistinctSortTest

GO

 
From this table we need only “Val1” column with distinct values BUT sorting output with “Val2”. Lets try simple query.

SELECT DISTINCT Val1

FROM #DistinctSortTest

ORDER BY Val2

Opps. Error

Msg 145, Level 15, State 1, Line 1

ORDER BY items must appear in the select list if SELECT DISTINCT is specified.

To resolve this problem, we can use GROUP BY clause with MIN()or MAX() function in ORDER BY clause. 

SELECT  Val1

FROM #DistinctSortTest

GROUP BY Val1

ORDER BY MIN(Val2)

Through GROUP BY (all select columns) we will achive functionality of DISTINCT and MIN()/MAX() functions for sorting. MIN() in Order By clause can be used for ASC sort and MAX() for DESC sort.

--drop temporary table when not required

DROP TABLE #DistinctSortTest

SQL Server: Simple Way to Swap Columns values

To resolve a problem, sometime we start thinking at high level while, simple solutions of said problem are available. This is what happened to me, when one of my colleagues (Tehman) asked me how to swap two column values in a table.

My answer was, create a third column (temporary) and swap using this third column, which you can remove later on. Here was the plan.

1. Move Col2 data to Col3
2. Move Col1 data to Col2
3. Move Col3 data to Col1
4. Drop Col3

-- Create Temporary table to hold values

CREATE TABLE #ForSwappingTest ( Col1 VARCHAR(50), Col2 VARCHAR(50))
-- Insert test reocrds
INSERT INTO #ForSwappingTest (Col1,Col2)
VALUES ('A','X'),
('B','Y'),
('C','Z')
-- Check Results
SELECT * FROM #ForSwappingTest
-- Add third column to hold data temporarily
ALTER TABLE #ForSwappingTest ADD  Col3 VARCHAR(50)
-- Start Swaping
UPDATE #ForSwappingTest
SET COL3 = COL2

UPDATE #ForSwappingTest

SET COL2 = COL1

UPDATE #ForSwappingTest

SET COL1 = COL3
-- Remove additional temporary column
ALTER TABLE #ForSwappingTest DROP COLUMN Col3
--Drop temporary table when not required
DROP TABLE #ForSwappingTest

But he came with a very simple solution, by writing following simple query.

UPDATE #ForSwappingTest
SET Col2 = Col1,
Col1 = Col2

SQL Server: Why We Should Avoid NOLOCK Table Hint in DELETE/UPDATE Queries

Recently, I was asked to review, already written stored procedures for optimization purpose. During this review process I have found that a group of developers is regularly committing a big mistake. This group of developers believes that table hint NOLOCK is used to execute queries quickly, as this hint will avoid placing any lock on target table records and it can you used in any query. Even they have applied this NOLOCK in DML statements.

WRONG

First thing, NOLOCK hint means, it will not take care of any lock (instead of placing lock). It will return data, that could be dirty (NOT YET COMMITTEED by other transactions). We can use this table hint to get results quickly when we are dead sure that dirty data is TOTALLY bearable.

In DELETE/UPDATE queries it should be totally avoided as it can produce junk results. Let’s prove.

In following example, we need to correct discount column of SalesOrderDetail, but according to discount provided in lookup table of SpecialOffer. Before we execute our update statement (Statement #2 in Transaction# 2), someone has accidently changed SpecialOffer, but good thing is that, he has not committed these changes yet. But as we have placed NOLOCK hint in our Statement #2 in Transaction# 2, it will change data according to dirty data, though, later on transaction#1 is rolledback.

SQL Server: Query to Find Upcoming birthdays for Current Week

 A common query, for Human Resource databases or different social sites is to find out employee/subscribers name who’s birthday is coming in near future i.e. (In current week, or in next given days).

To find out, whose birthday is coming in given number of days is bit simple.

--Create table variable to hold our test records

DECLARE  @Workers  TABLE (WorderName VARCHAR(50), DOB DATETIME)

--Insert test records

INSERT INTO @Workers

SELECT 'Ryan','1972-08-24 00:00:00' UNION ALL

SELECT 'James','1985-09-26 00:00:00' UNION ALL

SELECT 'Jasson','1983-08-25 00:00:00' UNION ALL

SELECT 'Tara','1991-09-24 00:00:00' UNION ALL

SELECT 'William','1992-08-19 00:00:00' UNION ALL

SELECT 'Judy','1989-09-23 00:00:00'

--Variable to provide requried number of days

DECLARE @InNextDays INT

SET @InNextDays = 3       

-- Query to find workers, whose birthday is in given number of days

SELECT  *

FROM    @Workers e

WHERE   1 =

CASE WHEN MONTH(GETDATE()) < MONTH(GETDATE() + @InNextDays)

     THEN CASE WHEN MONTH(DOB) = MONTH(GETDATE() + @InNextDays)

            AND DAY(DOB) BETWEEN DAY(DATEADD(s, -1,

                                    DATEADD(mm, DATEDIFF(m, 0,

                                    GETDATE()) + 1, 0) + 1))

                     AND     DAY(GETDATE()

                                    + @InNextDays) THEN 1

               WHEN MONTH(DOB) = MONTH(GETDATE())

                    AND DAY(DOB) BETWEEN DAY(GETDATE()) + 1

                                 AND     DAY(GETDATE())

                                         + @InNextDays THEN 1

               ELSE 0

          END

     ELSE CASE WHEN MONTH(DOB) = MONTH(GETDATE())

                    AND DAY(DOB) BETWEEN DAY(GETDATE()) + 1

                                 AND     DAY(GETDATE())

                                         + @InNextDays THEN 1

               ELSE 0

          END

END

 

And following query will help you to find out workers with birthday in current week.

-- Query to find workers, whose birthday is in current week

SELECT  *

FROM    @Workers e

WHERE   1 = CASE WHEN MONTH(GETDATE()) < MONTH(DATEADD(WK,

                                       DATEDIFF(WK, 0, GETDATE())+1,-1))

THEN CASE WHEN MONTH(DOB) = MONTH(GETDATE()) + 1

            AND DAY(DOB) >= 1

            AND DAY(DOB) < DAY(DATEADD(WK,

                                 DATEDIFF(WK, 0, GETDATE())

                                       + 1, -1)) THEN 1

    WHEN MONTH(DOB) = MONTH(GETDATE())

            AND DAY(DOB) >= DAY(GETDATE())

            AND DAY(DOB) <= DAY(DATEADD(s,-1,DATEADD(mm,

                                                            DATEDIFF(m,0,GETDATE()),0))) THEN 1

     

       ELSE 0 END

 

ELSE CASE WHEN MONTH(DOB) = MONTH(GETDATE())

            AND DAY(DOB) >= DAY(GETDATE())+1

            AND DAY(DOB) < DAY(DATEADD(WK,

                                 DATEDIFF(WK, 0, GETDATE())

                                       + 1, -1)) THEN 1

       ELSE 0

  END

END

SQL Server: Applying Filter on sp_MSforeachDB

Working on multiple databases on a single instance, sometime you need to execute a query for each database and for that sp_MSforeachdb is the best choice.

Recently talking to my development team I came to know that a very few guys have idea about filter for sp_MSforeachDB.

For example, if I need to get database physical files information for each database on my instance, I will use following simple query

EXEC sp_MSforeachdb '

BEGIN

       SELECT name,physical_name,state,size

       FROM ?.sys.database_files

END'

BUT what if, I need to omit MSDB, TempDB and Model databases for this query. Now I have to apply filter. This can be achieved by simple IF statement.

EXEC sp_MSforeachdb 'IF ''?''  NOT IN (''tempDB'',''model'',''msdb'')

BEGIN

       SELECT name,physical_name,state,size

       FROM ?.sys.database_files

END'

You can even use ? sign in WHERE clause.

EXEC sp_MSforeachdb 'IF ''?''  NOT IN (''tempDB'',''model'',''msdb'')

BEGIN

       SELECT name,physical_name,state,size

       FROM ?.sys.database_files

       WHERE name  LIKE ''?%'' -- Only Files starting with DB name

END'

Output can be saved in tables (user, temporary) or table variables

DECLARE   @DatabasesSize TABLE

    (

      name VARCHAR(50),

      physical_name VARCHAR(500),

      state BIT,

      size INT

    )

INSERT  INTO @DatabasesSize

EXEC sp_MSforeachdb 'IF ''?''  NOT IN (''tempDB'',''model'',''msdb'')

BEGIN

       SELECT name,physical_name,state,size

       FROM ?.sys.database_files

END'

SQL Server : Tables Relationship Diagram Using TSQL Script

How do you create relationship diagram/report between tables of a given database ? Mostly people use Database Diagram for this purpose, but this can be achieved by TSQL script as following.

Download Script

 

-- Tables Relationship Script

-- Script By: Syed Muhammad Yasir for http://connectsql.blogspot.com
-- Updated August 1, 2012
 

SELECT  CASE WHEN a.parent_object_id IS NULL

THEN parent.name + '-1--*-' + child.name
ELSE parent.name + '-1--1-' + child.name
END AS TablesWithRelations
FROM    ( SELECT DISTINCT
parent_object_id, referenced_object_id
FROM      sys.foreign_keys ) fk
LEFT JOIN ( SELECT DISTINCT
fkindexes.parent_object_id,
fkindexes.referenced_object_id
FROM    ( SELECT    fk.parent_object_id,
fk.referenced_object_id,
ixcolumns.index_id, COUNT(*) cindexes
FROM      ( SELECT    object_id,
            parent_object_id,
            referenced_object_id
  FROM      ( SELECT    row_number() OVER ( PARTITION BY parent_object_id, referenced_object_id
ORDER BY object_id ) rid,
              object_id, parent_object_id, referenced_object_id
              FROM      sys.foreign_keys ) fk
  WHERE     rid = 1 ) fk
JOIN sys.foreign_key_columns fkc ON fkc.constraint_object_id = fk.object_id
JOIN sys.index_columns ixcolumns ON ixcolumns.object_id = fkc.parent_object_id
                                    AND ixcolumns.column_id = fkc.parent_column_id
JOIN sys.indexes ix ON ix.object_id = ixcolumns.object_id
                       AND ix.index_id = ixcolumns.index_id
WHERE     ix.is_unique = 1
GROUP BY  fk.parent_object_id,
fk.referenced_object_id,
ixcolumns.index_id ) fkindexes
JOIN ( SELECT   fk.parent_object_id,
    ixcolumns.index_id,
    COUNT(*) cindexestotal
FROM     ( SELECT DISTINCT
                parent_object_id
      FROM      sys.foreign_keys ) fk
    JOIN sys.index_columns ixcolumns ON ixcolumns.object_id = fk.parent_object_id
GROUP BY fk.parent_object_id,
    ixcolumns.index_id ) totalindexes ON totalindexes.parent_object_id = fkindexes.parent_object_id
                                         AND totalindexes.index_id = fkindexes.index_id

WHERE   cindexestotal - cindexes = 0 ) a ON a.parent_object_id = fk.parent_object_id

                        AND a.referenced_object_id = fk.referenced_object_id
JOIN sys.tables child ON fk.parent_object_id = child.object_id
JOIN sys.tables parent ON fk.referenced_object_id = parent.object_id
ORDER BY TablesWithRelations