Feeds:
Posts
Comments

Archive for July, 2013

SQL SERVER – How to concatenate int and varchar

Posted in Solutions, SQL SERVER, tagged , , , , on July 31, 2013| 2 Comments »

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.

Read Full Post »

SQL SERVER HEKATON – List of supportive and non-supportive data types in the memory optimized table

Posted in SQL SERVER, Tables, tagged , , , on July 30, 2013| 1 Comment »

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 »

SQL SERVER 2012 – How to add multiple files in a single filestream filegroup in the existing database.

Posted in Features, Solutions, SQL SERVER, tagged , , , , on July 28, 2013| Leave a Comment »

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 »

SQL SERVER – How to insert space before Capital letters – User Defined Function

Posted in Solutions, SQL SERVER, tagged , , , on July 27, 2013| 3 Comments »

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 »

SQL SERVER – Fix – Error – 11538 – EXECUTE statement failed because its WITH RESULT SETS clause

Posted in Errors, SQL SERVER, tagged , , , , , on July 26, 2013| Leave a Comment »

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 »

SQL SERVER – How to pass parameters to an SP using select command (Without variables)

Posted in Solutions, SQL SERVER, tagged , , , , on July 25, 2013| Leave a Comment »

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 »

SQL SERVER – String comparison as a Boolean in Select statement

Posted in Solutions, SQL SERVER, tagged , , , , on July 24, 2013| Leave a Comment »

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 »

SQL SERVER – Fix – Error – 205 – All queries combined using a UNION, INTERSECT or EXCEPT operator must have an equal number of expressions in their target lists.

Posted in Errors, SQL SERVER, tagged , , , , on July 22, 2013| Leave a Comment »

Union [ALL] operator is one of the frequently used operators in the SQL Server scripting. We usually use it to combine multiple query result set together. But it has one of the limitations that all the queries column must be equal. But there are multiple ways to by pass this limit.

Let’s discuss this in detail:

Message Number: 205

Severity : 16

Error Message: All queries combined using a UNION, INTERSECT or EXCEPT operator must have an equal number of expressions in their target lists.

Error Generation:
Let me create two sample tables & insert some sample data in it to demonstrate this error and its solutions.

USE tempdb
GO
--Create first table
CREATE TABLE tbl_sample_1
(
 [ID] int,
 [Name] varchar(50),
 [Modified Date] datetime
)
GO
--Insert few records in first table
INSERT INTO tbl_sample_1 VALUES (1,'SQL Server 2005','2005-01-01')
INSERT INTO tbl_sample_1 VALUES (2,'SQL Server 2008','2008-01-01')
GO
--Create second table
CREATE TABLE tbl_sample_2
(
 [ID] int,
 [Name] varchar(50),
)
GO
--Insert few records in second table
INSERT INTO tbl_sample_2 VALUES (1,'SQL Server 2012')
INSERT INTO tbl_sample_2 VALUES (2,'SQL Server 2014')
GO

Let me apply union operator on both tables. Given below is the script :

USE tempdb
GO
SELECT [ID],[Name],[Modified Date]
FROM tbl_sample_1
UNION ALL
SELECT [ID],[Name]
FROM tbl_sample_2
--OUTPUT

Msg 205, Level 16, State 1, Line 1
All queries combined using a UNION, INTERSECT or EXCEPT operator must have an equal number of expressions in their target lists.

Ooopps…… I am unable to apply union operator on above tables and the reason behind this is that both table’s have different number of columns.

Resolution:
Given below are the three different resolutions that I usually use to fix this error.

Solution 1 :
In this solution you can remove the additional column from the query. This additional column can be in any query. In our sample query we have additional column [Modified Date] in query 1. So lets remove it.

USE tempdb
GO
SELECT [ID],[Name]
FROM tbl_sample_1
UNION ALL
SELECT [ID],[Name]
FROM tbl_sample_2
--OUTPUT

unionallerror205.1.1

Solution 2 :
In this solution you can leave the additional column in the query but add the same addtional column in query 2 with the value of NULL. Given below is the script.

USE tempdb
GO
SELECT [ID],[Name],[Modified Date]
FROM tbl_sample_1
UNION ALL
SELECT [ID],[Name],NULL As [Modified Date]
FROM tbl_sample_2
--OUTPUT

unionallerror205.1.2

Solution 3 :
In this solution you can leave the additional column in the query but add the same additional column in query 2 with the DEFAULT value. Be sure that the DEFAULT value is compatible with the additional column. Given below is the script.

USE tempdb
GO
SELECT [ID],[Name],[Modified Date]
FROM tbl_sample_1
UNION ALL
SELECT [ID],[Name],GETDATE() As [Modified Date]
FROM tbl_sample_2
--OUTPUT

unionallerror205.1.3

Read Full Post »

SQL SERVER HEKATON – How to create memory optimized table in an existing database

Posted in Solutions, SQL SERVER, tagged , , , , , on July 21, 2013| 1 Comment »

In my earlier article, I wrote a solution to create memory optimized table in a new database. But the question is how to create memory optimized table in an existing database. Let me explain it step by step.

Step 1 :
In this step we need to create a simple database to demonstrate. Skip this step if you already have a database but make sure its compatibility level is 110.

USE master;
GO
CREATE DATABASE test
ON
( NAME = Sales_dat,
FILENAME = 'C:\DATA\test.mdf',
SIZE = 10,
MAXSIZE = 50,
FILEGROWTH = 5 )
LOG ON
( NAME = Sales_log,
FILENAME = 'C:\DATA\test_log.ldf',
SIZE = 5MB,
MAXSIZE = 25MB,
FILEGROWTH = 5MB ) ;
GO
--OUTPUT

Step 2 :
Create a memory optimized table.

USE Test
GO
CREATE TABLE tbl_sample_Memory_table
(
[ID] integer not null primary key nonclustered hash
with (bucket_count = 1024),
[Name] varchar(200) not null,
) WITH (MEMORY_OPTIMIZED = ON)
GO
--OUTPUT

Msg 41337, Level 16, State 0, Line 3
The MEMORY_OPTIMIZED_DATA filegroup does not exist or is empty. Memory optimized tables cannot be created for database until it has one MEMORY_OPTIMIZED_DATA filegroup that is not empty.

Ooopps…… I am unable to create memory optimized table in TEST database (existing database).

So, what’s the remedy for it ?

Step 3 :
Create a file group, add file in it containing memory optimized data option. Given below is the script.
Do not forget to change the path in the script.

USE [master]
GO
ALTER DATABASE [test] ADD  FILEGROUP [test_fg]
CONTAINS MEMORY_OPTIMIZED_DATA
GO
ALTER DATABASE [test] ADD FILE
( NAME = N'test_fg', FILENAME = N'C:\DATA\test_fg.ndf')
TO FILEGROUP [test_fg]
GO
USE [test]
GO
IF NOT EXISTS (SELECT name FROM sys.filegroups
WHERE is_default=1 AND name = N'test_fg')
ALTER DATABASE [test] MODIFY FILEGROUP [test_fg] DEFAULT
GO
--OUTPUT

Step 4 :
Once the file group is created, just create the memory optimized table and this time it will be created successfully.

USE Test
GO
CREATE TABLE tbl_sample_Memory_table
(
[ID] integer not null primary key nonclustered hash
with (bucket_count = 1024),
[Name] varchar(200) not null,
) WITH (MEMORY_OPTIMIZED = ON)
GO
--OUTPUT

Command(s) completed successfully.

Step 5 :
Now, browse the table and check whether it is created or not.

USE Test
GO
SELECT * FROM tbl_sample_Memory_table
--OUTPUT

memoryoptimizedtables_exisiting1.1

Conclusion :
Whenever you create a memory optimized table, you must consider two things before creating it.
1- The database must have the compatibility level 110
2- Database must have a file group having CONTAINS MEMORY_OPTIMIZED_DATA

Read Full Post »

SQL SERVER – How to find first/last occurrence of any character/ word in the string

Posted in Solutions, SQL SERVER, tagged , , , , on July 20, 2013| Leave a Comment »

How to find first/last occurrence of any character/ word in the string is one of the most frequent problems that we usually come across whenever we are dealing with the string manipulation in SQL Server.
Given below are the solutions :

  • Find First occurrence of any character/ word in the string :

In the given below example, we need to search for the first occurrence of word ‘the’ in the sentence.

DECLARE @String AS VARCHAR(100)
DECLARE @Search_String AS VARCHAR(100)

SET @String ='The SQL SERVER is one of the best applications of Microsoft'
SET @Search_String='the'

--Find First occurrence of any character/word in the string
SELECT CHARINDEX(@Search_String,@String) As [First occurrence]
--OUTPUT

First occurrence
—————-
1

  • Find Last occurrence of any character/ word in the string :

In the example given below, we need to search for the last occurrence of word ‘the’ in the sentence.

DECLARE @String AS VARCHAR(100)
DECLARE @Search_String AS VARCHAR(100)

SET @String ='The SQL SERVER is one of the best applications of Microsoft'
SET @Search_String='the'

--Find Last occurrence of any character/word in the string
SELECT DATALENGTH(@String)-CHARINDEX(REVERSE(@Search_String)
,REVERSE(@String))-1 As [Last occurrence]

Last occurrence
—————
26

Read Full Post »

Older Posts »