European Windows 2012 Hosting BLOG

BLOG about Windows 2012 Hosting and SQL 2012 Hosting - Dedicated to European Windows Hosting Customer

SQL Server 2014 with Free ASP.NET Hosting - HostForLIFE.eu :: How to List Out SQL Logins and Database User Mappings

clock May 21, 2015 08:14 by author Peter

In this post, let me explain about How to List Out SQL Logins and Database User Mappings.

You can use system stored procedure sp_msloginmappings to list out the SQL logins and database user mappings. And here is the syntax:
sp_msloginmappings @Loginname , @Flags

@Loginname – Optional argument, in case if you not specify the Login name procedure will return the result for all the SQL Server logins
@Flags – You can specify value 0 or 1, 0 value will show user mapping to all databases and 1 will show the user mapping to current database only. Default value is 0
use master
go
exec sp_msloginmappings 'sa', 0

use master
go
exec sp_msloginmappings 'sa', 1

 

If you want to run the sp_msloginmappings across multiple SQL Instance using either Central management server or Powershell, write the following script:
create table #loginmappings( 
 LoginName  nvarchar(128) NULL, 
 DBName     nvarchar(128) NULL, 
 UserName   nvarchar(128) NULL, 
 AliasName  nvarchar(128) NULL
)   
insert into #loginmappings
EXEC master..sp_msloginmappings
select * from #loginmappings
 drop table #loginmappings

HostForLIFE.eu SQL Server 2014 with Free ASP.NET Hosting
Try our SQL Server 2014 with Free ASP.NET Hosting today and your account will be setup soon! You can also take advantage of our Windows & ASP.NET Hosting support with Unlimited Domain, Unlimited Bandwidth, Unlimited Disk Space, etc.



SQL Server 2014 with Free ASP.NET Hosting - HostForLIFE.eu :: How to Identify Unused Indexes in SQL Server

clock May 18, 2015 07:44 by author Rebecca

Indexes play an important role in SQL Server performance. Coins always have two sides, just like a well-designed index can improve query performance and an incorrect index can impact query performance. So it is important to find which indexes are not being used. It helps us to reduce storage and reduce the overhead of the database engine to maintain unused indexes. But how can we find indexes that are not being used? In this article, I'm gonna tell you how to identify unused indexes in SQL Server.

The absence of an index can result in table or index scans that reduce performance in some case and also too many indexes require extra storage and extra effort to maintain the database and it might slow down insert / update operations. One of the approaches to improve the overall performance is keep used indexes but drop all unused indexes.

"dm_db_index_physical_stats" is a dynamic management view related to index statistics. This view gives information about indexes used or unused, it complete or missing some columns is irrelevant. This dynamic view has many important columns like user_seeks (number of seeks by user queries), user_scans (number of scans by user queries), user_lookups (number of bookmark lookups by user queries) and a combination of these three columns provide us a total read count. The column user updates (number of updates by user queries) indicates the level of maintenance on the index caused by insert / update / delete operations on the table or view. A proper join among these DVM and system tables such as indexes, objects and schemas enable us to list all unused indexes for a single database.

The following query helps us to find unused indexes in our database:

    SELECT  
    o.name AS TableName, 
    i.name AS Indexname, 
    i.is_primary_key AS PrimaryKey, 
    s.user_seeks + s.user_scans + s.user_lookups AS NumOfReads, 
    s.user_updates AS NumOfWrites, 
    (SELECT SUM(p.rows) FROM sys.partitions p WHERE p.index_id = s.index_id AND s.object_id = p.object_id) AS TableRows, 
    'DROP INDEX ' + QUOTENAME(i.name) + ' ON ' + QUOTENAME(c.name) + '.' + QUOTENAME(OBJECT_NAME(s.object_id)) AS 'DropStatement' 
    FROM sys.dm_db_index_usage_stats s  
    INNER JOIN sys.indexes i ON i.index_id = s.index_id AND s.object_id = i.object_id  
    INNER JOIN sys.objects o ON s.object_id = o.object_id 
    INNER JOIN sys.schemAS c ON o.schema_id = c.schema_id 
    WHERE OBJECTPROPERTY(s.object_id,'IsUserTable') = 1 
    AND s.databASe_id = DB_ID()  
    AND i.type_desc = 'NONCLUSTERED' 
    AND i.is_primary_key = 0 
    AND i.is_unique_constraint = 0 

The preceding query includes the following helpful information.

  •     Table name
  •     Index name
  •     Primary key
  •     Number of read count
  •     Number of writes
  •     Total number of rows
  •     Drop statement

The following  is the output of the preceding query:

After running the preceding query for the database it will list all the non-clustered indexes for all tables. Now we can determine the unused indexes by comparing the number of reads applied to an index with the number of writes. If we have a number of reads (NumOfReads column in the preceding query) then the indexes are not being used.

Base on the query result and application knowledge, we may decide which index needs to be dropped and the last column of the query contains a drop index statement.

HostForLIFE.eu SQL Server 2014 with Free ASP.NET Hosting
Try our SQL Server 2014 with Free ASP.NET Hosting today and your account will be setup soon! You can also take advantage of our Windows & ASP.NET Hosting support with Unlimited Domain, Unlimited Bandwidth, Unlimited Disk Space, etc.



SQL Server 2014 with free ASP.NET Hosting - HostForLIFE.eu :: How to Use RAND() Function in SQL Server 2014

clock May 15, 2015 07:01 by author Rebecca

You know that RAND() function in SQL Server generates a random float value 0 through 1 (excluding 0 and 1). In this article, I will show you how to generate different random number for each group using RAND() Function.

First, let's us create the following dataset:

CREATE TABLE #random(no INT)
INSERT INTO #random(no)
SELECT 1 UNION ALL
SELECT 1 UNION ALL
SELECT 1 UNION ALL
SELECT 2 UNION ALL
SELECT 3 UNION ALL
SELECT 3


If you want to generate a random value, you can use the following code:

SELECT no, RAND() AS random_number FROM #random

This code will result to:
no random_number
 1 0.370366365964781
 1 0.370366365964781
 1 0.370366365964781
 2 0.370366365964781
 3 0.370366365964781
 3 0.370366365964781

Please note that when you execute it , you may get different random number than what I got for column 2 but all will be same. What if you want to generate random number in such a way that it is reset to each column value (in this case the column is no)? Did you know that RAND() accepts a seed value as well?

If you execute the following code:

SELECT no, RAND() AS random_number,RAND(no) AS random_number_reset FROM #random

Then, it will comes to the result:
no random_number random_number_reset
 1 0.58334760467751 0.713591993212924
 1 0.58334760467751 0.713591993212924
 1 0.58334760467751 0.713591993212924
 2 0.58334760467751 0.713610626184182
 3 0.58334760467751 0.71362925915544
 3 0.58334760467751 0.71362925915544


Please note that when you execute it , you may get different random number than what I got for column2 2 and 3. If you notice the result the values of second column is same for all rows, whereas the values of third column is same withing a group (column no), and different across groups. So if you want to generate random number differently for each group, you can make use of this method.

HostForLIFE.eu SQL Server 2014 with Free ASP.NET Hosting
Try our SQL Server 2014 with Free ASP.NET Hosting today and your account will be setup soon! You can also take advantage of our Windows & ASP.NET Hosting support with Unlimited Domain, Unlimited Bandwidth, Unlimited Disk Space, etc.



SQL Server 2014 with Free ASP.NET Hosting - HostForLIFE.eu :: How to Move TempDB from one Drive to Another Drive

clock May 12, 2015 07:30 by author Rebecca

When you found that your TempDB log file is filled up, you will come across following errors in log file:
Source: MSSQLSERVER
Event ID: 17052
Description: The LOG FILE FOR DATABASE 'tempdb' IS FULL.
Back up the TRANSACTION LOG FOR the DATABASE TO free
up SOME LOG SPACE

So, you have to move the TempDB to different drive. Moving the TempDB to another drive will help the growth of the file. Sometimes user also moves to different drive due to performance reasons as keeping TempDB on a different drive from your main database helps. By this article, I'm gonna show you how to move TempDB from one drive to another drive.

There are major two reasons why TempDB needs to move from one drive to other drive:
1) TempDB grows big and the existing drive does not have enough space.
2) Moving TempDB to another file group which is on different physical drive helps to improve database disk read, as they can be read simultaneously.

You can follow the direction below exactly to move database and log from one drive (c:) to another drive (d:) and (e:)

Make sure that TempDB is set to autogrow and do not set a maximum size for TempDB. If the current drive is too full to allow autogrow events, then arrange a bigger drive, or add files to TempDB on another device (using ALTER DATABASE as described below and allow those files to autogrow).

1. Open Query Analyzer and connect to your server. Run this script to get the names of the files used for TempDB:

USE TempDB
GO
EXEC sp_helpfile
GO


Results will be something like:
name fileid filename filegroup size
——- —— ————————————————————– ———- ——-
tempdev 1 C:Program FilesMicrosoft SQL ServerMSSQLdatatempdb.mdf PRIMARY 16000 KB
templog 2 C:Program FilesMicrosoft SQL ServerMSSQLdatatemplog.ldf NULL 1024 KB

2. Along with other information related to the database, the names of the files are usually tempdev and demplog by default. These names will be used in next statement. Run following code, to move mdf and ldf files:

USE master
GO
ALTER DATABASE TempDB MODIFY FILE
(NAME = tempdev, FILENAME = 'd:datatempdb.mdf')
GO
ALTER DATABASE TempDB MODIFY FILE
(NAME = templog, FILENAME = 'e:datatemplog.ldf')
GO


The definition of the TempDB is changed. However, no changes are made to TempDB till SQL Server restarts. Please stop and restart SQL Server and it will create TempDB files in new locations.

HostForLIFE.eu SQL Server 2014 with Free ASP.NET Hosting
Try our SQL Server 2014 with Free ASP.NET Hosting today and your account will be setup soon! You can also take advantage of our Windows & ASP.NET Hosting support with Unlimited Domain, Unlimited Bandwidth, Unlimited Disk Space, etc.



SQL Server 2014 with Free ASP.NET Hosting - HostForLIFE.eu :: How to Change Compatibility of Database to New SQL Version

clock April 28, 2015 06:12 by author Rebecca

In this post, I would like to show you how to change compatibility of database to SQL Server 2014. Maybe you have installed SQL Server 2014 and attached a database file from previous version of SQL Server. Right after attaching database, you were not able to work with the latest features of Cardinality Estimation. This problem is caused by the database compatibility was still set of the earlier version of SQL Server. To use most of the latest features of SQL Server 2014, you have to change the compatibility level of the database to the latest version.

Here are two different ways how we can change the compatibility of database to SQL Server 2014’s version:

1. Using Management Studio

For this method first to go database and right click over it. Now select properties.

On this below screen user can change the compatibility level to 120:

2. Using T-SQL Script

You can execute following script and change the compatibility settings to 120:
USE [master]
GO
ALTER DATABASE [AdventureWorks2012] SET COMPATIBILITY_LEVEL = 120
GO

Congratulations, you're done!

HostForLIFE.eu SQL Server 2014 with Free ASP.NET Hosting
Try our SQL Server 2014 with Free ASP.NET Hosting today and your account will be setup soon! You can also take advantage of our Windows & ASP.NET Hosting support with Unlimited Domain, Unlimited Bandwidth, Unlimited Disk Space, etc. You will not be charged a cent for trying our service. Once your trial period is complete, you decide whether you'd like to continue.



SQL Server 2014 with Free ASP.NET Hosting - HostForLIFE.eu :: How to encrypt/decrypt string in SQL Server ?

clock April 21, 2015 07:36 by author Peter

In this tutorial, I will show you how to  to encrypt/decrypt string in SQL Server. First step, you must write the following DB Script:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:        <Author,,Name>
-- Create date: <Create Date,,>
-- Description:   <Description,,>
-- =============================================
ALTER PROCEDURE Enc_Dec
    @username varchar(20),
      @password varchar(10)
AS
BEGIN
      SET NOCOUNT ON;
      DECLARE @encrypt_val VARBINARY(200)
    SELECT @encrypt_val = EncryptByPassPhrase('xxx', @password)
    SELECT @encrypt_val
    INSERT INTO reg_users (username,r_pwd)VALUES(@username ,CONVERT(VARCHAR(200), @encrypt_val, 1))
    SELECT CONVERT(VARCHAR(200),DecryptByPassPhrase('xxx', @encrypt_val ))
END
GO

The output of the above code as shown in the following picture:

 

HostForLIFE.eu SQL Server 2014 with Free ASP.NET Hosting

Try our SQL Server 2014 with Free ASP.NET Hosting today and your account will be setup soon! You can also take advantage of our Windows & ASP.NET Hosting support with Unlimited Domain, Unlimited Bandwidth, Unlimited Disk Space, etc. You will not be charged a cent for trying our service. Once your trial period is complete, you decide whether you'd like to continue.



SQL Server 2014 Hosting - HostForLIFE.eu :: How to Handling NULL Character \x00 when Exporting to File Using BCP?

clock April 9, 2015 07:21 by author Peter

In this tutorial, I will show you how to handling NULL Character \x00 when Exporting to File Using BCP in SQL Server.

This article talks about the conduct of the BCP utility when removing information from SQL Server to records and all the more particularly to CSV documents. Imagine that you have the underneath table:

As you can see on the above picture, records 2 and 3 contain null values. Now, you need to export the table contents to CSV using BPC: bcp "SELECT [code],[description],[price] FROM [TestDB1].[dbo].[product]" queryout "c:\tmp\testExtract.csv" -c -C 1253 -t ; -S ".\SQL2K14" -T

And here is the output from the code:

As should be obvious on the above screenshot, the output appears to be correct. The records' NULL value have been supplanted with unfilled strings. Now, consider that the first table, rather than NULL qualities has unfilled strings:

Let's try again to export the new table contents to CSV using BPC:
bcp "SELECT [code],[description],[price] FROM [TestDB1].[dbo].[product]" queryout "c:\tmp\testExtract.csv" -c -C 1253 -t ; -S ".\SQL2K14" –T

And here is the output:

As should be obvious, the unfilled strings have been replaced by NULLs which relate to the hexadecimal character \x00. It appears from the above sample, that the queryout keyword has the same behavior regarding null and empty strings.

Presently, if you are going to nourish the CSV document to another application for parsing and handling it, if the application does not expressly handle conceivable events of the NULL character then most probably an error will be occurred. To this end, dependably have as a top priority the above conduct when extracting data to files using BCP.

HostForLIFE.eu SQL Server 2014 Hosting
HostForLIFE.eu is European Windows Hosting Provider which focuses on Windows Platform only. We deliver on-demand hosting solutions including Shared hosting, Reseller Hosting, Cloud Hosting, Dedicated Servers, and IT as a Service for companies of all sizes. We have customers from around the globe, spread across every continent. We serve the hosting needs of the business and professional, government and nonprofit, entertainment and personal use market segments.



SQL Server 2014 Hosting UK - HostForLIFE.eu :: Script TSQL Database Level Security

clock March 12, 2015 08:42 by author Peter

Here's a convenient script that is a piece of my tool stash all over I go. Scripts out and identifies basic database level security objects, and creates a TSQL proclamation to reproduce the items. Note that this script just chips away at SQL 2005 or above. This is a long way from an authority script.

I invite to all input on these scripts. I likewise have a server-level security script here.
--Run the below on each database for database-level security. 
SELECT DB_NAME() as Database_Name 
--Database Level Roles
SELECT DISTINCT
     QUOTENAME(r.name) as database_role_name, r.type_desc, QUOTENAME(d.name) as principal_name, d.type_desc
,    TSQL = 'EXEC sp_addrolemember @membername = N''' + d.name COLLATE DATABASE_DEFAULT + ''', @rolename = N''' + r.name + ''''
FROM sys.database_role_members AS rm
inner join sys.database_principals r on rm.role_principal_id = r.principal_id
inner join sys.database_principals d on rm.member_principal_id = d.principal_id
where d.name not in ('dbo', 'sa', 'public') 
--Database Level Security
SELECT     rm.state_desc
     ,   rm.permission_name
    ,   QUOTENAME(u.name) COLLATE database_default
    ,   u.TYPE_DESC
     ,   TSQL = rm.state_desc + N' ' + rm.permission_name + N' TO ' + cast(QUOTENAME(u.name COLLATE DATABASE_DEFAULT) as nvarchar(256))  
FROM sys.database_permissions AS rm
     INNER JOIN
     sys.database_principals AS u
     ON rm.grantee_principal_id = u.principal_id
WHERE rm.major_id = 0
and u.name not like '##%'
and u.name not in ('dbo', 'sa', 'public')
ORDER BY rm.permission_name ASC, rm.state_desc ASC
 --Database Level Explicit Permissions
SELECT     perm.state_desc
    , perm.permission_name
    ,   QUOTENAME(USER_NAME(obj.schema_id)) + '.' + QUOTENAME(obj.name)
       + CASE WHEN cl.column_id IS NULL THEN SPACE(0) ELSE '(' + QUOTENAME(cl.name COLLATE DATABASE_DEFAULT) + ')' END AS [Object]
     , QUOTENAME(u.name COLLATE database_default) as Usr_Name
    ,   u.type_Desc
    , obj.type_desc
   ,  TSQL = perm.state_desc + N' ' + perm.permission_name
           + N' ON ' + QUOTENAME(USER_NAME(obj.schema_id)) + '.' + QUOTENAME(obj.name)
           + N' TO ' + QUOTENAME(u.name COLLATE database_default)
FROM sys.database_permissions AS perm
     INNER JOIN
     sys.objects AS obj
     ON perm.major_id = obj.[object_id]
     INNER JOIN
     sys.database_principals AS u
     ON perm.grantee_principal_id = u.principal_id
     LEFT JOIN
    sys.columns AS cl
     ON cl.column_id = perm.minor_id AND cl.[object_id] = perm.major_id
where
     obj.name not like 'dt%'
and obj.is_ms_shipped = 0
and u.name not in ('dbo', 'sa', 'public')
ORDER BY perm.permission_name ASC, perm.state_desc ASC


Alternately, wrap the entire thing in a msforeachdb:
exec sp_msforeachdb 'use [?];
SELECT DB_NAME() as Database_Name
--Database Level Roles
SELECT DISTINCT
 QUOTENAME(r.name) as database_role_name, r.type_desc, QUOTENAME(d.name) as principal_name, d.type_desc
, TSQL = ''EXEC sp_addrolemember @membername = N'''''' + d.name COLLATE DATABASE_DEFAULT + '''''', @rolename = N'''''' + r.name + ''''''''
FROM sys.database_role_members AS rm
inner join sys.database_principals r on rm.role_principal_id = r.principal_id
inner join sys.database_principals d on rm.member_principal_id = d.principal_id
where d.name not in (''dbo'', ''sa'', ''public'')
--Database Level Security
SELECT  rm.state_desc
 ,   rm.permission_name
    ,   QUOTENAME(u.name) COLLATE database_default
    ,   u.TYPE_DESC
 ,   TSQL = rm.state_desc + N'' '' + rm.permission_name + N'' TO '' + cast(QUOTENAME(u.name COLLATE DATABASE_DEFAULT) as nvarchar(256))  
FROM sys.database_permissions AS rm
 INNER JOIN
 sys.database_principals AS u
 ON rm.grantee_principal_id = u.principal_id
WHERE rm.major_id = 0
and u.name not like ''##%''
and u.name not in (''dbo'', ''sa'', ''public'')
ORDER BY rm.permission_name ASC, rm.state_desc ASC
 --Database Level Explicit Permissions
SELECT perm.state_desc
    , perm.permission_name
    ,   QUOTENAME(USER_NAME(obj.schema_id)) + ''.'' + QUOTENAME(obj.name)
       + CASE WHEN cl.column_id IS NULL THEN SPACE(0) ELSE ''('' + QUOTENAME(cl.name COLLATE DATABASE_DEFAULT) + '')'' END AS [Object]
 , QUOTENAME(u.name COLLATE database_default) as Usr_Name
    ,   u.type_Desc
    , obj.type_desc
    ,  TSQL = perm.state_desc + N'' '' + perm.permission_name
  + N'' ON '' + QUOTENAME(USER_NAME(obj.schema_id)) + ''.'' + QUOTENAME(obj.name)
  + N'' TO '' + QUOTENAME(u.name COLLATE database_default)
FROM sys.database_permissions AS perm
 INNER JOIN
 sys.objects AS obj
 ON perm.major_id = obj.[object_id]
 INNER JOIN
 sys.database_principals AS u
 ON perm.grantee_principal_id = u.principal_id
 LEFT JOIN
 sys.columns AS cl
 ON cl.column_id = perm.minor_id AND cl.[object_id] = perm.major_id
where
 obj.name not like ''dt%''
and obj.is_ms_shipped = 0
and u.name not in (''dbo'', ''sa'', ''public'')
ORDER BY perm.permission_name ASC, perm.state_desc ASC
';

HostForLIFE.eu SQL Server 2014 Hosting
HostForLIFE.eu is European Windows Hosting Provider which focuses on Windows Platform only. We deliver on-demand hosting solutions including Shared hosting, Reseller Hosting, Cloud Hosting, Dedicated Servers, and IT as a Service for companies of all sizes. We have customers from around the globe, spread across every continent. We serve the hosting needs of the business and professional, government and nonprofit, entertainment and personal use market segments.



SQL Server 2014 Hosting Germany - HostForLIFE.eu :: How to Use a Filtered Index to Enforce Filtered Uniqueness in SQL Server ?

clock March 5, 2015 06:37 by author Peter

One of the benefits and uses of Filtered Indexes in SQL Server is to produce filtered uniqueness. clearly this has some implications, thus please perceive what you are making an attempt to accomplish. In the below example, we've a compound natural key of the 2 fields key1 and key2. the field bit1 isn't a member of the natural key, however will inform us as to some table usage. maybe bit1 indicates that this record is Active (1) or Inactive (0), or whether or not the info is Confirmed (1) or unconfirmed  (0), or Deleted (0) or Not Deleted (1).

In any case, we have a tendency to solely wish to enforce uniqueness for when bit1 = 1, that indicates:
This value is often filtered to be used wherever bit1 = 1
We do not care whether or not there are duplicate records for once bit1 = 0.
In this means, you'll "deactivate" (in business terms) a record by setting bit1 = 0, while not violating your natural key's uniqueness on (key1, key2).
drop table dbo.tabletestdups
go
create table dbo.tabletestdups
( key1 int not null
, key2 int not null
, bit1 bit not null
)
go
create unique nonclustered index idx_nc_u_f_tabletestdups_key1_key2_bit1 on dbo.tabletestdups (key1, key2, bit1)
go
insert into dbo.tabletestdups (key1, key2, bit1) values (1,1,1) –succeed
insert into dbo.tabletestdups (key1, key2, bit1) values (2,2,1) –succeed
insert into dbo.tabletestdups (key1, key2, bit1) values (3,3,1) –succeed
insert into dbo.tabletestdups (key1, key2, bit1) values (1,1,0) –succeed
insert into dbo.tabletestdups (key1, key2, bit1) values (2,2,0) –succeed
insert into dbo.tabletestdups (key1, key2, bit1) values (3,3,0) –succeed
go
insert into dbo.tabletestdups (key1, key2, bit1) values (1,1,1) –fails
go
insert into dbo.tabletestdups (key1, key2, bit1) values (1,1,0) –fails
go
drop index idx_nc_u_f_tabletestdups_key1_key2_bit1 on dbo.tabletestdups
go
create unique nonclustered index idx_nc_u_f_tabletestdups_key1_key2_bit1 on dbo.tabletestdups (key1, key2, bit1) WHERE bit1 = 1 --Note the important WHERE clause here at the end of the index.
go
insert into dbo.tabletestdups (key1, key2, bit1) values (1,1,1) –fails
go
insert into dbo.tabletestdups (key1, key2, bit1) values (1,1,0) --succeeds because the unique constraint only enforces bit1 = 1.
go
select * from dbo.tabletestdups

And here is the output:

Note that rows four and seven have allowed duplicate combination of key1 =1, key2= 1 and bit1 = 0, but that previous attempts to insert a duplicate combination of key1 =1, key2= 1 and bit1 = 1 unsuccessful.

HostForLIFE.eu SQL Server 2014 Hosting
HostForLIFE.eu is European Windows Hosting Provider which focuses on Windows Platform only. We deliver on-demand hosting solutions including Shared hosting, Reseller Hosting, Cloud Hosting, Dedicated Servers, and IT as a Service for companies of all sizes. We have customers from around the globe, spread across every continent. We serve the hosting needs of the business and professional, government and nonprofit, entertainment and personal use market segments.



SQL Server 2014 Hosting Italy - HostForLIFE.eu :: How to Split & Convert Comma Separated String in SQL Server 2014?

clock February 5, 2015 05:49 by author Peter

In this post, I will explain you about Split and convert Comma Separated String. In this article I will explain with example code, how to split and convert a comma separated / delimited string to a table using Split function in SQL Server 2005, 2008, 2012 and MSSQL 2014 versions. The string containing words or letters or numbers separated (delimited) by comma or underscore or  plus(+) ,etc,. are going to be split into Table values.

I will conjointly explain a way to use the Split function to separate a string in an exceedingly SQL query or stored Procedures in SQL Server 2005, 2008 and 2012 versions. And here is the code that I used:
CREATE FUNCTION [dbo].[SplitString] (@InputString NVARCHAR(MAX),@delimiter CHAR(1))
RETURNS @tbl TABLE (
  Item NVARCHAR(50) NOT NULL
)
AS
BEGIN
  DECLARE @StartIndex int,
          @NextIndex int,
          @ItemLen int
  SELECT
    @StartIndex = 0,
    @NextIndex = 1
  WHILE @NextIndex > 0
  BEGIN
    SELECT
      @NextIndex = CHARINDEX(@delimiter, @InputString, @StartIndex + 1)
    SELECT
      @ItemLen =
                CASE
                  WHEN @NextIndex > 0 THEN @NextIndex
                  ELSE LEN(@InputString) + 1
                END - @StartIndex – 1
    INSERT @tbl (Item)
      VALUES (CONVERT(varchar(50), SUBSTRING(@InputString, @StartIndex + 1, @ItemLen)))
    SELECT
      @StartIndex = @NextIndex
  END
  RETURN
END


Now, this is the example 1:

SELECT * FROM [SplitString]('Apple-Dell-HP-Lenovo-Sony','-')


Now, write the example 2:
SELECT * FROM [SplitString]('Apple,Dell,HP,Lenovo,Sony',',')
Here is the output from the code above:

Example code 3:
SELECT * FROM [SplitString]('1,2,3,4,5')

The output of the above code snippet is shown on the following picture:

HostForLIFE.eu SQL Server 2014 Hosting
HostForLIFE.eu is European Windows Hosting Provider which focuses on Windows Platform only. We deliver on-demand hosting solutions including Shared hosting, Reseller Hosting, Cloud Hosting, Dedicated Servers, and IT as a Service for companies of all sizes. We have customers from around the globe, spread across every continent. We serve the hosting needs of the business and professional, government and nonprofit, entertainment and personal use market segments.



About HostForLIFE.eu

HostForLIFE.eu is European Windows Hosting Provider which focuses on Windows Platform only. We deliver on-demand hosting solutions including Shared hosting, Reseller Hosting, Cloud Hosting, Dedicated Servers, and IT as a Service for companies of all sizes.

We have offered the latest Windows 2016 Hosting, ASP.NET Core 2.2.1 Hosting, ASP.NET MVC 6 Hosting and SQL 2017 Hosting.


Tag cloud

Sign in