Feeds:
Posts
Comments

Archive for July, 2013

Concatenate int and varchar is a usual problem that we face in SQL scripting and the solution is easily available over the web. But what encourages me to write this article is, we can resolve this issue using a built in function in SQL Server 2012.
Let me show you the issue that we generally face when we concatenate int and varchar. Given below is the script.

Use AdventureWorks2012
GO
SELECT [DepartmentID] + ' ' + [Name] AS [Department ID & Name]
FROM [HumanResources].[Department]
GO
--OUTPUT

Msg 245, Level 16, State 1, Line 1
Conversion failed when converting the nvarchar value ‘Document Control’ to data type smallint.

Solution 1:
In the earlier version of SQL Server, we usually use CONVERT function to convert int into varchar and then concatenate it.
Given below is the script.

Use AdventureWorks2012
GO
SELECT CONVERT(VARCHAR(5),[DepartmentID]) + ' ' + [Name] 
AS [Department ID & Name]
FROM [HumanResources].[Department]
--OUTPUT

conactenate int to varchar1.1

Solution 2:
In this solution, we will use CONCAT function (a newly shipped function in SQL Server 2012) to convert int into varchar then concatenate it.
Given below is the script.

Use AdventureWorks2012
GO
SELECT CONCAT([DepartmentID] , ' ' , [Name] )
AS [Department ID & Name]
FROM [HumanResources].[Department]
--OUTPUT

conactenate int to varchar1.1

Conclusion :
I generally recommend solution 2 because as you can see that there is no difference in output between solution 1 and solution 2 but there is a remarkable difference between both solutions in performance. You can get the performance details from here.

Advertisements

Read Full Post »

Recently, I was doing research on memory optimized table and I found that most of the data types are supportive and few of them are non supportive as well. But even though few data data types are non supportive there are works around to deal with those data types as well. In my earlier article, I wrote about how to deal with BLOB data types even though they are non supportive. Rest of the non supportive data types work around, I will share in my upcoming posts.

Given below is the list of Supportive data types:

  • bigint
  • int
  • tinyint
  • smallint
  • int
  • real
  • smallmoney
  • money
  • float
  • decimal
  • numeric
  • bit
  • uniqueidentifier
  • smalldatetime
  • datetime
  • binary
  • nchar
  • sysname

Given below is the list of Non-supportive data types:

  • image
  • text
  • sql_variant
  • ntext
  • varbinary(Max)  (Limitation is 8060 bytes per row)
  • varchar(Max)  (Limitation is 8060 bytes per row)
  • timestamp
  • nvarchar(Max) (Limitation is 8060 bytes per row)
  • xml

Let me know if you have work around for the non supportive data types.

Read Full Post »

Adding multiple files in a single filestream filegroup was a very nice & helpful feature shipped with SQL Server 2012. Now, you can easily organize your data under multiple files in the same filestream filegroup. Also, it is not necessary that you place all of them in the same location. You can place them in multiple locations to divide the load as well.

Let me create a sample database to demonstrate. Given below is the script.
Note : Before executing the below script, kindly create a Data folder in C: drive.

USE [master]
GO
CREATE DATABASE [Sample_DB]
CONTAINMENT = NONE
ON PRIMARY
(
NAME = N'Sample_DB',
FILENAME = N'C:\Data\Sample_DB.mdf',
SIZE = 500MB,
MAXSIZE = UNLIMITED,
FILEGROWTH = 10MB
),
FILEGROUP [FS] CONTAINS FILESTREAM DEFAULT
(
NAME = N'Filestream1',
FILENAME = N'C:\Data\FileStream1',
MAXSIZE = UNLIMITED
)
LOG ON
(NAME = N'Sample_DB_log'
,FILENAME = N'C:\Data\Sample_DB_log.ldf',
SIZE = 500MB,
MAXSIZE = 1GB,
FILEGROWTH = 10MB
)
GO

Let me add an additional file to the same filegroup. Given below is the script.

ALTER DATABASE [Sample_DB]
ADD FILE
(
NAME = N'Filestream2',
FILENAME = N'C:\Data\Filestream2',
MAXSIZE = 100MB
)TO FILEGROUP [FS]
GO

Lets check if the file has been created or not. Given below is the script.

Select * from sys.master_files where database_id =db_id('Sample_DB')
GO

multiplefilestream1.1

Let me know if you have done this in real world and what was the solution.

Read Full Post »

Today, I  received an email from one of my friends. He was working on a legacy data and he executed a wrong update statement on a table. Basically, he removed the space between the names and then updated the Student name column. For example, if the student name was “Steve Masters”, it became “SteveMasters”.

Now, we need to insert a space before Capital letters to convert the data into its original state. As usual, I started the easy way… the web search but almost all solutions use loop to convert it. I normally avoid loop to perform any operation, unless it is the last option, due to performance issue.

So, I designed the solution via Xquery and inserted space before the capital letters without loop.

Let me create sample to explain it.

USE tempdb
GO
CREATE TABLE Student
(
 [Student ID] INT Identity(1,1),
 [Student Name] VARCHAR(50)
)
GO
INSERT INTO Student VALUES ('SteveMasters')
INSERT INTO Student VALUES ('DavidOrtiz')
INSERT INTO Student VALUES ('MichaelSeanRay')
INSERT INTO Student VALUES ('StevenSelikoff')
INSERT INTO Student VALUES ('CarolePoland')
INSERT INTO Student VALUES ('BjornRettig')
INSERT INTO Student VALUES ('MichikoOsada')
INSERT INTO Student VALUES ('CarolPhilips')
INSERT INTO Student VALUES ('MeravNetz')
GO
SELECT * FROM Student
GO
--OUTPUT

insertspacebeforecapitalletter1.1

Given below is the user defined function that can insert the space before each capital letter.

USE tempdb
GO
CREATE FUNCTION dbo.[UDF_Space_Before_Capital_Letters]
(
    @String VARCHAR(MAX)  -- Variable for string
)
RETURNS VARCHAR(MAX)
BEGIN
DECLARE @RETURN_STRING VARCHAR(MAX)

;WITH N1 (n) AS (SELECT 1 UNION ALL SELECT 1),
N2 (n) AS (SELECT 1 FROM N1 AS X, N1 AS Y),
N3 (n) AS (SELECT 1 FROM N2 AS X, N2 AS Y),
N4 (n) AS (SELECT ROW_NUMBER() OVER(ORDER BY X.n)
FROM N3 AS X, N3 AS Y)

SELECT @RETURN_STRING=ISNULL(@RETURN_STRING,'')+
(CASE WHEN SUBSTRING(@String,Nums.n,1)=
UPPER(SUBSTRING(@String,Nums.n,1)) collate Latin1_General_CS_As
AND Nums.n >1
THEN SPACE(1) + SUBSTRING(@String,Nums.n,1)
ELSE SUBSTRING(@String,Nums.n,1) END)

FROM N4 Nums
WHERE Nums.n<=LEN(@String)

RETURN @RETURN_STRING
END
GO
-- For Example :
USE tempdb
GO
SELECT [Student ID]
,dbo.[UDF_Space_Before_Capital_Letters]([Student Name]) As [Student Name]
from Student
GO

insertspacebeforecapitalletter1.2

Let me know if you come across this issue and its solution.

Read Full Post »

EXECUTE statement failed because its WITH RESULT SETS clause specified type ‘%ls’ for column #%d in result set #%d, and the corresponding type sent at run time was ‘%ls’; there is no conversion between the two types is one of the new error messages seen in SQL Server 2012. This error message is due to the invalid data type usage in the new feature EXEC WITH RESULT SETS.

Let’s discuss this in detail:
Message Number: 11538

Severity : 16

Error Message: EXECUTE statement failed because its WITH RESULT SETS clause specified type ‘%ls’ for column #%d in result set #%d, and the corresponding type sent at run time was ‘%ls’; there is no conversion between the two types.

Error Generation:
Let me create a sample stored procedure to generate this error.

USE tempdb
GO
Create Procedure Usp_Resultset
As
Select 1 as [Number]
GO
EXEC Usp_Resultset
GO
--OUTPUT

errormessage11538.1.1

You can see that the above stored procedure is executed successfully and it has ONE result sets with int data type column.
Lets try to execute it using WITH RESULT SETS clause.

USE tempdb
GO
EXEC Usp_Resultset
WITH RESULT SETS
(([Number] xml
));
--OUTPUT
Msg 11538, Level 16, State 1, Procedure Usp_Resultset, Line 3
EXECUTE statement failed because its WITH RESULT SETS clause specified type ‘xml’ for column #1 in result set #1, and the corresponding type sent at run time was ‘int’; there is no conversion between the two types.

Ooops…… I am unable to execute it. It returned the error message.

errormessage11538.1.2

Resolution:
Why this error ? Because, the above stored procedure returns one result sets with int data type column but we defined  XML Data type column in WITH RESULT SETS clause. This is true that you can change the data type & column name in result sets, but the issue is, we must look at the data type compatibility as well. And as we know, int & xml data types are not compatible with each other. Lets rewrite the script and define int data type column (or any data type that is compatible with int) in the result sets to avoid this error.

USE tempdb
GO
EXEC Usp_Resultset
WITH RESULT SETS
(([Number] int
));
--OUTPUT

errormessage11538.1.3

Conclusion :
Remember, whenever you use any stored procedure using WITH RESULT SETS clause, make sure you use data type in the result sets that is compatible with the source column data type to avoid such errors discussed above.

Read Full Post »

In SQL scripting, it is a common scenario when you need to pass parameters to a stored procedure using select command and we have multiple solutions for this. But before proceeding with the solutions, I need to create a table and a stored procedure to demonstrate the solution.

USE AdventureWorks2012
GO
--Create table
CREATE TABLE dbo.[Department_Audit]
(
DepartmentID smallint,
Name nvarchar(50),
)

GO
--Create Stored Procedure
CREATE PROCEDURE dbo.Usp_Department_Audit
@DepartmentID int,
@Name varchar(50)
AS
BEGIN
INSERT INTO [Department_Audit] VALUES(@DepartmentID,@Name)
END

Solution 1 :
In this solution you need to use extra variable to take the values from SELECT command and insert into a STORED PROCEDURE.
Given below is the solution.

USE AdventureWorks2012
GO
--Declare variables
DECLARE @DepartmentID int
DECLARE @Name varchar(50)

--Set value to variables
SELECT @DepartmentID=[DepartmentID],@Name=[Name]
FROM HumanResources.Department
Where [DepartmentID]=1

--pass variables to stored procedure and execute it.
EXEC dbo.Usp_Department_Audit @DepartmentID,@Name

GO
USE AdventureWorks2012
GO
--Browse the table
SELECT * FROM [Department_Audit]
GO
--OUTPUT

SP_parameter_using_Select.1.1

Solution 2 :
In this solution you do not need to use extra variable to take the values from SELECT command and insert into a STORED PROCEDURE. You can make a dynamic SQL string and insert the records directly into the STORED PROCEDURE using SELECT command. Given below is the solution.

USE AdventureWorks2012
GO
DECLARE @SQL nvarchar(max)
SET @SQL = ''
SELECT @SQL = @SQL + 'EXEC dbo.Usp_Department_Audit '
+ Convert(nvarchar(2),[DepartmentID]) + ', '
+ '''' + [Name] + '''' + '; '
FROM HumanResources.[Department] Where [DepartmentID] =2
EXEC (@SQL)

USE AdventureWorks2012
GO
SELECT * FROM [Department_Audit]
--OUTPUT

SP_parameter_using_Select.1.2

Read Full Post »

Most of the times, we usually maintain the status column of any table in varchar data type. But sometimes due to the requirements, we need to convert this Varchar data type column to Boolean data type to further use it for stats or other purposes as well. But how ?
Before proceeding with the solution, let me first create a sample to demonstrate the solution.

USE tempdb
GO
CREATE TABLE tbl_sample
(
[ID] int,
[Name] varchar(50),
[Status] varchar(50)
)
GO
INSERT INTO tbl_sample VALUES (1,'SQL SERVER 2000','Inactive')
INSERT INTO tbl_sample VALUES (2,'SQL SERVER 2005','Active')
INSERT INTO tbl_sample VALUES (3,'SQL SERVER 2008','Active')
INSERT INTO tbl_sample VALUES (4,'SQL SERVER 2012','Active')
GO
SELECT * FROM tbl_sample
GO
--OUTPUT

stringcomparision

Given below are the solutions.

Solution 1:
In this solution, we will use CASE statement to convert varchar status to boolean.

--This solution is compatibile with SQL Server 2005 and above.
SELECT
[ID]
,[NAME]
,[STATUS]
,(CASE WHEN [STATUS]='Active' THEN 1 ELSE 0 END) [Boolean Status]
FROM tbl_sample
GO
--OUTPUT

stringcomparision1.2

Solution 2:
In this solution, we will use IIF statement to convert varchar status to Boolean.

--This solution is compatibile with SQL Server 2012 and above.
SELECT
[ID]
,[NAME]
,[STATUS]
,IIF([STATUS]='Active', 1,0) [Boolean Status]
FROM tbl_sample
GO
--OUTPUT

stringcomparision1.3

Read Full Post »

Older Posts »