Feeds:
Posts
Comments

Archive for June, 2013

In my earlier article, I had discussed about new features of SQL Server 2014. Those features are very exciting and I was waiting for the CTP. Now, CTP 1 is available for download.

Given below are the few instructions that you should follow to install CTP 1.

  • The machine should not have any earlier versions of SQL Server.
  • There is no upgrade / side by side installation available in this CTP.
  • This CTP is only available for X64 architecture.

System requirement for SQL Server 2014 is available here.

Let me know if you face any issues with the installation.

Advertisements

Read Full Post »

In my earlier article, I had discussed about the omitted leading zeros in excel and I received a very nice solution from Mr. Harsh, one of my blog readers. (This solution works in Excel 2010 and above).

Let me explain his solution step by step.

Step A :
Let me create a sample to demonstrate the error.

USE tempdb
GO
CREATE TABLE [dbo].[tbl_Employee](
	[Employee ID] [varchar](5) NULL,
	[Employee Name] [varchar](50) NOT NULL
) ON [PRIMARY]

GO
INSERT [dbo].[tbl_Employee] ([Employee ID], [Employee Name])
VALUES (N'00001', N'A. Scott')

INSERT [dbo].[tbl_Employee] ([Employee ID], [Employee Name])
VALUES (N'00002', N'Alan')

INSERT [dbo].[tbl_Employee] ([Employee ID], [Employee Name])
VALUES (N'00003', N'Alejandro')

INSERT [dbo].[tbl_Employee] ([Employee ID], [Employee Name])
VALUES (N'00004', N'Alex')

INSERT [dbo].[tbl_Employee] ([Employee ID], [Employee Name])
VALUES (N'00005', N'Alice')
GO
SELECT [Employee ID], [Employee Name] FROM [tbl_Employee]
GO
--OUTPUT

Remove_zeros_in_excel.1.1

Step B :
Copy the data from SQL Server, open an excel sheet, click on Paste Button and select Use Text Import Wizard (as shown in the picture below).

omittingleadingzeros1.1

Step C :
It then takes you to 3 steps of Text Import Wizard. Under Text Import Wizard – Step 1 of 3, you need to determine the data type. Select Delimited and press Next (as shown in the picture below).

omittingleadingzeros1.2

Step D :
Text Import Wizard – Step 2 of 3 lets you to set the delimiters your data contains. Select Tab and press Next (as shown in the picture below).
omittingleadingzeros1.3

Step E :
Text Import Wizard – Step 3 of 3 lets you set the column data format. Select Text and press Finish (as shown in the picture below).

omittingleadingzeros1.6

Step F :
Once finished, the data would be pasted in the required format, meaning along with the zeros as shown in the picture below.

omittingleadingzeros1.5

Let me know if you know any better solution.

Read Full Post »

IF… ELSE clause is very handy and whenever you need to perform any conditional operation, you can achieve your results using it. But there are some limitations in IF… ELSE,  and one of the limitations is that you cannot use it in WHERE clause.

Let me demonstrate the limitations.

USE AdventureWorks2012
GO
DECLARE @City AS VARCHAR(50)
SELECT
  BusinessEntityID
, FirstName
, LastName
, City
FROM [HumanResources].[vEmployee]
WHERE
IF @City IS NULL
	City='Renton'
ELSE
    City=@City
--OUTPUT

Msg 156, Level 15, State 1, Line 9
Incorrect syntax near the keyword ‘IF’.
Msg 102, Level 15, State 1, Line 10
Incorrect syntax near ‘City’.

Nevertheless, there are solutions which are given below:

SOLUTION 1 :
You can use CASE statement instead of IF..ELSE clause to do conditional where clause.
Given below is the script.

--This script is compatible with SQL Server 2005 and above.
USE AdventureWorks2012
GO
DECLARE @City AS VARCHAR(50)
SELECT
  BusinessEntityID
, FirstName
, LastName
, City
FROM [HumanResources].[vEmployee]
WHERE City =
(CASE WHEN @City IS NULL THEN 'Renton' ELSE @City END)
ORDER BY BusinessEntityID
--OUTPUT

ifelse1.1

SOLUTION 2 :
In this solution, you can use IIF clause instead of IF..ELSE  to do conditional where clause.
Given below is the script.

--This script is compatible with SQL Server 2012 and above
USE AdventureWorks2012
GO
DECLARE @City AS VARCHAR(50)
SELECT
  BusinessEntityID
, FirstName
, LastName
, City
FROM [HumanResources].[vEmployee]
WHERE City = IIF (@City IS NULL, 'Renton', @City)
ORDER BY BusinessEntityID
--OUTPUT

ifelse1.1

Read Full Post »

In my earlier article, I had discussed about how to sort month names in month order instead of alphabetical order . In that article, we had given the date and we had to convert into month and sort it. But, in today’s case we have month & year in the table and we need to sort it in month & year order instead of alphabetical order. To achieve this, we usually use lots of case statements and sort it.

Let me create an example to demonstrate it.

USE tempdb
GO
CREATE TABLE tbl_MonthName
(
    [ID] INT,
    [MONTH_NAME] VARCHAR(50),
    [YEAR] VARCHAR(4)
)
GO

INSERT INTO tbl_MonthName
SELECT 1, N'January',2014 UNION ALL
SELECT 2, N'July',2013 UNION ALL
SELECT 3, N'February',2014 UNION ALL
SELECT 4, N'September',2013 UNION ALL
SELECT 5, N'March',2014 UNION ALL
SELECT 6, N'April',2014 UNION ALL
SELECT 7, N'November',2013 UNION ALL
SELECT 8, N'May',2014 UNION ALL
SELECT 9, N'June',2014 UNION ALL
SELECT 10, N'August',2013 UNION ALL
SELECT 11, N'October',2013 UNION ALL
SELECT 12, N'December',2013
GO

SELECT * FROM tbl_MonthName
GO
--OUTPUT

Monthnameandyear1.1

As you can see, the above result set is not sorted properly.

Given below is a simple solution to sort data in month and year order.

--This script is compatible with SQL Server 2005 and above
USE tempdb
GO
SELECT * FROM tbl_MonthName
ORDER BY [YEAR],MONTH('1' + [MONTH_NAME] +'00')
--OUTPUT

Monthnameandyear1.2

Read Full Post »

In most of the applications, we create some multipurpose stored procedure that we use for multiple forms, reports, exports, etc. The challenge we usually face with this kind of stored procedure is that whatever columns are available in the result set, stored procedure will return the same number of columns as an output and not the selected/required information.

Let me take a sample stored procedure from AdventureWorks2012 database and view its result set.

USE AdventureWorks2012
GO
EXEC [dbo].[uspGetManagerEmployees] 16
GO
--OUTPUT

selectcolumn1.1

As you can see that a number of columns are available in the above stored procedure result set but I need to select [BusinessEntityID], [FirstName], [LastName] ONLY.

Given below are the two methods to achieve it.

Method 1 :
In this method, we need to follow three steps :

Step 1:
Create a temporary table according to the number of columns in the result set of the stored procedure.

USE AdventureWorks2012
GO
CREATE TABLE #TEMP
(
  [RecursionLevel] INT
, [OrganizationNode] VARCHAR(50)
, [ManagerFirstName] NVARCHAR(50)
, [ManagerLastName]  NVARCHAR(50)
, [BusinessEntityID] INT
, [FirstName] NVARCHAR(50)
, [LastName] NVARCHAR(50)
)
GO

Step 2:
Insert the complete result set of the stored procedure in the table.

USE AdventureWorks2012
GO
INSERT INTO #TEMP EXEC [dbo].[uspGetManagerEmployees] 16
GO

Step 3:
Select the required columns from the temporary table.

USE AdventureWorks2012
GO
SELECT
  [BusinessEntityID]
, [FirstName]
, [LastName]
FROM #TEMP
GO
--OUTPUT

selectcolumn1.2

Method 2 :
In this method, we need to use OPENROWSET and can select the column of the stored procedure result set directly.

SELECT
  [BusinessEntityID]
, [FirstName]
, [LastName]
FROM OPENROWSET('SQLNCLI', 'Server=(local);uid=test;pwd=test',
     'EXEC AdventureWorks2012.[dbo].[uspGetManagerEmployees] 16') ;
GO
--OUTPUT

selectcolumn1.3

Read Full Post »

Problem :
Most of the time, I find developers surrounded by hundreds of scripts and frantically looking for the required file amongst the open documents.

Given blew is the screen shots :

reusedoc1.1

And also, it is near impossible that you are using all the documents at once. So, how to avoid this issue ?

Solution :
You just need to enable a very nice feature in SQL Server “Reuse current document window, if saved”. You can find this option in
Tools >> Options >> Environment >> Documents, as shown in the given below screen shot.

reusedoc1.2

reusedoc1.3

How it works :
You just need to enable this option and once it is enabled, just save the script which you have finished editing, and click on new query. So it will close the saved script (document) and open a new query in the same place.

Note : All the above screen shots are taken from SQL Server 2012.

Read Full Post »

How to sort month names in month order instead of alphabetical order is a common problem and has been discussed on multiple forums. But what encourages me to write this post is, I found new methods to do it.

Let me create a sample to explain multiple methods to achieve it.

USE tempdb
GO
CREATE TABLE tbl_Sample
(
 [ID] INT,
 [Date] DATETIME
)
GO
INSERT INTO tbl_Sample VALUES (1,'2013-04-04')
INSERT INTO tbl_Sample VALUES (2,'2013-07-07')
INSERT INTO tbl_Sample VALUES (3,'2013-10-10')
INSERT INTO tbl_Sample VALUES (4,'2013-01-01')
INSERT INTO tbl_Sample VALUES (5,'2013-02-02')
INSERT INTO tbl_Sample VALUES (6,'2013-03-03')
INSERT INTO tbl_Sample VALUES (7,'2013-05-05')
INSERT INTO tbl_Sample VALUES (8,'2013-06-06')
INSERT INTO tbl_Sample VALUES (9,'2013-08-08')
INSERT INTO tbl_Sample VALUES (10,'2013-09-09')
INSERT INTO tbl_Sample VALUES (11,'2013-11-11')
INSERT INTO tbl_Sample VALUES (12,'2013-12-12')
GO

Method 1 :
This is the simplest method, wherein you just need to sort on the basis of column [date] instead of DATENAME(month,Date).
Given below is the script.

--This script is compatible with SQL Server 2005 and above
USE tempdb
GO
SELECT
  DATENAME(month,Date) AS [Month Name]
, [Date]
FROM tbl_Sample
ORDER BY [Date]
--OUTPUT

monthnumberorder1.1

Method 2 :
In this method, you need to get the month number using Month function and sort it on month number.
Given below is the script.

--This script is compatible with SQL Server 2005 and above
USE tempdb
GO
SELECT
  DATENAME(month,Date) AS [Month Name]
, [Date]
FROM tbl_Sample
ORDER BY Month(Date)
--OUTPUT

monthnumberorder1.1

Method 3 :
In this method, you need to get the month number using DatePart function and sort it on month number.
Given below is the script.

--This script is compatible with SQL Server 2005 and above
USE tempdb
GO
SELECT
  DATENAME(month,Date) AS [Month Name]
, [Date]
FROM tbl_Sample
ORDER BY DATEPART(m,Date)
--OUTPUT

monthnumberorder1.1

Method 4 :
In this method, you need to get the month number using Format function and sort it on month number.
Given below is the script.

--This script is compatible with SQL Server 2012 and above
USE tempdb
GO
SELECT
  DATENAME(month,Date) AS [Month Name]
, [Date]
FROM tbl_Sample
Order By FORMAT([Date],'MM')
--OUTPUT

monthnumberorder1.1

Read Full Post »

Older Posts »