Feeds:
Posts
Comments

Archive for the ‘Solutions’ Category

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 – 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 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 »

SQL SERVER HEKATON – How to implement BLOB data type columns in the memory optimized table

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

In my earlier article, I had discussed about how to implement identity column in the Memory optimized table. Now, lets try to create a BLOB data type column in memory optimized table.

Given below is the script that we usually use to implement BLOB data type columns in the disk based table (normal table). Lets try the same script for memory optimized table.

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

Msg 10770, Level 16, State 94, Line 3
The type ‘varchar(max)’ is not supported with memory optimized tables.

Oh no.. I am unable to create a BLOB data type column in memory optimized table and as we know, BLOB data types are one of the most commonly used data types in the database.

So what is the solution?

The solution is very simple. What you need to do is, actually split the above table into two tables and one as memory optimized table and 2nd one should be disk based table. And the logic behind this is, we will keep all the data types columns except BLOB in memory optimized table and will put all the BLOB data types column in disk based tables. But do not forget to keep the same ID in both tables.
Given below is the script.

USE hkNorthwind
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

CREATE TABLE tbl_sample_Normal_Table
(
[ID] integer not null,
[Address] varchar(Max)
)
GO
--OUTPUT

Command(s) completed successfully.

As you can see that, both tables are created successfully.

Note : You can download the hkNorthwind database from here.

Reference : MSDN

Read Full Post »

SQL SERVER – Connectivity with Excel

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

Reports are the backbone for each and every application and most of the reports are designed using SQL Server Reporting or crystal report in dot net applications. But most of these reports are usually exported by end users in excel for further processing and in most cases we spend a lot of time to design and develop those reports in SSRS or Crystal. So is there a short cut ?

Certainly. You can populate the data in excel directly from SQL Server and prepare the same report in few minutes.

Lets do it step by step.

Step 1 :
In the first step you need to create a table or view, that you will be using in the excel for reporting.
For this demonstration, we will use an already created view namely “vEmployee” in the AdventureWorks2012.
You can download this AdventureWorks2012 database from here.

Step 2 :
Open excel 2010 and select Data tab and then click on From Other Sources and select From SQL Server, as shown in the picture below.

SQL excel connectivity1.1

Step 3 :
Once you click on From SQL Server, it will open a Data Connection wizard. In this wizard, you need to enter SQL Server credentials and click NEXT, as shown in the picture below.

SQL excel connectivity1.2

Step 4 :
Now, you are in the database and table/view selection window. Just select the appropriate database and table or view and click NEXT, as shown in the picture below.

SQL excel connectivity1.3

Step 5 :
This is the last window of Data connection wizard, where you need to save the connection and click FINISH.

SQL excel connectivity1.4

Step 6 :
After that you need to select format (Table, Pivot Table Report, Pivot Chart and Pivot Table Report) of data in excel and where (Existing sheet, new worksheet) to place the data. Lets select a table and the existing sheet from the options.

SQL excel connectivity1.5

Step 7 :
Now, we have the data and beauty is that, whenever you press refresh button, excel refreshes the data from SQL Server and displays the up to date data in excel. Now, you can do pivot and other reporting stuff with this data in few minutes.

SQL excel connectivity1.6

Conclusion :

This tool usually saves a lot of time in developing complex reports. I usually recommend people to prepare some templates of these reports and whenever there is a requirement, just refresh the data and send it to end users.

Let me know if you have implemented this technique in real world.

Read Full Post »

SQL SERVER HEKATON – How to implement identity column in the memory optimized table

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

Memory optimized table is a new feature introduced in SQL Server Hekaton.  As it is new for all of us, I am doing my research with different aspects of memory optimized table. In this article, we will create an identity field in the memory optimized table.

Given below is the script that we usually use to implement identity in the disk based table (normal table). Lets try the same script for memory optimized table.

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

Msg 10771, Level 16, State 8, Line 3
The feature ‘identity column’ is not yet implemented with memory optimized tables.

Oooops.. I am unable to create identity column in memory optimized table.

So, how can we create an identity column in memory optimized table ? Here comes the SEQUENCE object solution to the rescue.

Let me create an identity column in memory optimized table in sequential steps.

Step 1 :
First of all, you need to create a memory optimized table without any identity column.
Given below is the script.

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

Step 2 :
Once you have created the memory optimized table, create a Sequence object.

USE hkNorthwind
GO
CREATE SEQUENCE [Seq_Sample]
AS [int]
START WITH 1
INCREMENT BY 1
MINVALUE 1
MAXVALUE 1000
GO

Step 3 :
Insert the records using sequence in the memory optimized table.
Given below is the script.

USE hkNorthwind
GO
INSERT INTO tbl_sample ([ID],[Name])
VALUES (NEXT VALUE FOR [Seq_Sample],'raresql-1')
GO
INSERT INTO tbl_sample ([ID],[Name])
VALUES (NEXT VALUE FOR [Seq_Sample],'raresql-2')
GO

Step 4 :
Just browse the table and check whether identity is implemented or not.

USE hkNorthwind
GO
SELECT * FROM tbl_sample
GO
--OUTPUT

identity_field_for_memory_optimized_table.1.1

Note : You can download the hkNorthwind database from here.

Reference : MSDN

Read Full Post »

SQL SERVER – Extract file name from file path

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

Recently, I was working on a legacy database and I came across an issue where I had a full file path of a document but the file name was not separated / isolated from the file path.

Let me create a sample to demonstrate it.

USE Tempdb
GO
CREATE TABLE tbl_Sample
(
[ID] INT,
[File Path] VARCHAR(200)
)
GO
INSERT INTO tbl_Sample ([ID],[File Path])
VALUES (1,'C:\Users\imran\Documents\worksheet.xlsx')

INSERT INTO tbl_Sample ([ID],[File Path])
VALUES (2,'C:\Users\imran\image.png')

INSERT INTO tbl_Sample ([ID],[File Path])
VALUES (3,'C:\Users\imran\Documents\Document.doc')
GO

Given below is a script that separates file name from file path.

SELECT
[ID]
,[File Path]
,SUBSTRING([File Path],LEN([File Path])
- CHARINDEX('\',REVERSE([File Path]))+2,LEN([File Path]))
AS [File Name]
FROM tbl_Sample
GO
--OUTPUT

extractfilename.1.1

Read Full Post »

« Newer Posts - Older Posts »