SQL Server Management Studio: Basic Startup Options

SQL Server Management Studio is a powerful tool to manage SQL Server databases. Let’s discuss its two very common properties which can make our daily life easy.

• Why every time, a new query window is open with MASTER database.

On open a new query window, It opens it with MASTER database in use, because on creation of a new user, SQL Server sets default database as MASTER. You change it by opening properties window for your user and then change default database value to your desired one.

Now opening a new query window, your own database will be selected by default.

• On opening SQL Server Management Studio, I need a new query window automatically.

SQL Server Management Studio gives you five options to change its startup behavior. You can select one of these options by moving your mouse to TOOLS….Options in top menu.

 

Open Object Explorer

Using this option, only Object Explorer will be opened on startup and will ask you to login to an instance

Open new query window

This option will help you to start SSMS with a new query window only.

Open Object Explorer and new query window

This option is the most common one, when you need both Object Explorer and Query Window

Open Object Explorer and Activity Monitor

This option will open Activity Monitor with Object Explorer

Open empty environment

Option, which is never used (at least I never used it). As it will just open SQL Server Management Studio, No query window, No object explore, just main menu with tool bars.

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 : @@VERSION Showing Incorrect Service Pack Information

Recently, due to SQL Server Log message The time stamp counter of CPU on scheduler id 13 is not synchronized with other CPUs. we have decided to resolve it by installing Service Pack 3 for SQL Server 2005. To confirm, that target server has version prior to Service Pack 3, I have used following simplest query.

SELECT @@VERSION

From query result it became sure that said server has Service Pack 2 installed and need to be upgraded to Service Pack3. 
On receiving confirmation mail from Systems department that said instance is upgraded, I went for same above query to verify and surprisingly, it was still showing the same result with Service Pack 2.

For further confirmation I have used a different query (SERVERPROPERTY('PRODUCTLEVEL') and found actual results (i.e. Service Pack 3). 

Basically, it was a simple misconception. Till date i thought @@version shows patch information of SQL Server, but actually it shows operating system patch.

SQL Server : Query Result Showing Incomplete Text

Sometime, simple and basic problem can trap experts. This happened last week with one of my senior, who was trying to generate some scripts and saving output scripts to an output text file. On execution of these scripts he found that for some queries text was not complete. He tried to get result in text format but problem was same.  

File output

If you are facing same basic problem of SQL Server Management Studio then no need to worry, as you just need to make some changes in SSMS options.

Go to TOOLS -- > OPTIONS -- > QUERY RESULTS -- > Result to Text -- > Maximum number of characters displayed in each column

Default value is 256, which is too less, update it to your desired length. You can extend it to 8192 characters maximum.

SQL Server: How to Create a Parameterized Views

In SQL Server functionality of parametrized views can be achieved by creating an in-line table valued function. Let’s see how to convert a commonly used view HumanResources.vEmployee in AdventureWorks to a parametrized view.

CREATE FUNCTION PV_GetEmployeeInformationBySSN

(     

       -- Add the parameters for the function here

       @NationalIDNumber VARCHAR(9)

)

RETURNS TABLE

AS

RETURN

(

       -- Add the SELECT statement with parameter references here

       SELECT   e.BusinessEntityID, p.Title, p.FirstName, p.MiddleName,

              p.LastName, p.Suffix, e.JobTitle, pp.PhoneNumber,

              pnt.Name AS PhoneNumberType, ea.EmailAddress,

              p.EmailPromotion, a.AddressLine1, a.AddressLine2, a.City,

              sp.Name AS StateProvinceName, a.PostalCode,

              cr.Name AS CountryRegionName,

              p.AdditionalContactInfo

       FROM            HumanResources.Employee AS e INNER JOIN

              Person.Person AS p

              ON p.BusinessEntityID = e.BusinessEntityID INNER JOIN

              Person.BusinessEntityAddress AS bea

              ON bea.BusinessEntityID = e.BusinessEntityID INNER JOIN

              Person.Address AS a ON a.AddressID = bea.AddressID INNER JOIN

              Person.StateProvince AS sp

              ON sp.StateProvinceID = a.StateProvinceID INNER JOIN

              Person.CountryRegion AS cr

              ON cr.CountryRegionCode = sp.CountryRegionCode LEFT OUTER JOIN

              Person.PersonPhone AS pp

              ON pp.BusinessEntityID = p.BusinessEntityID LEFT OUTER JOIN

              Person.PhoneNumberType AS pnt

              ON pp.PhoneNumberTypeID = pnt.PhoneNumberTypeID LEFT OUTER JOIN

              Person.EmailAddress AS ea

              ON p.BusinessEntityID = ea.BusinessEntityID

       WHERE e.NationalIDNumber = @NationalIDNumber

)

GO

How to use it. Very Simple :)

SELECT * FROM PV_GetEmployeeInformationBySSN ('112457891')