Archive

Archive for the ‘Sql Server’ Category

Improve Stored Procedure Performance in SQL SERVER using SET NOCOUNT ON

SET NOCOUNT ON

This one line of code, put at the top of a stored procedure turns off the messages that SQL Server sends back to the client after each T-SQL statement is executed.

You can read the detailed description here:

http://msdn.microsoft.com/en-us/library/ms189837.aspx

http://www.mssqltips.com/tip.asp?tip=1226

Using Order by in UNION Clause in SQL SERVER


SELECT * FROM
( SELECT Column1 FROM table1
  UNION
  SELECT Column1 FROM table2
)
ORDER BY Column1

Using Replace() function in Sql Server

In this article, we will be talking about one of the important inbuilt functions of sql server, Replace()

Syntax:

REPLACE ( string_expression , string_pattern , string_replacement )

Some examples of using replace are as follows:

1)

select replace('Nice Article', 'Nice','Cool')

returns Cool Article

2)

UPDATE emp set designation=replace(designation, 'Sales', 'Marketing') where department='Marketing'

Insert comma-delimited string value to table column in Sql

March 3, 2010 1 comment

I have string like ‘1,22,3,49,6,7,8’

I want to insert each comma separated value in a table column…my output look like this:

COLUMN
1
22
3
49
6
7
8

Here is the solution:

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
— =============================================
— Author:        <Author,,Name>
— Create date: <Create Date,,>
— Description:    <Description,,>

— =============================================
create PROCEDURE [dbo].[sp_InsertCommaSeparatedValues]

AS
BEGIN

DECLARE @FormId VARCHAR(5000)
DECLARE @FormRead varchar(5000)
DECLARE @FormWrite varchar(5000)

SET @FormId = ‘1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,’
SET @FormRead =’1,0,1,0,1,0,1,0,0,0,1,1,1,1,1,1,1,1,1,’
SET @FormWrite = ‘1,0,1,0,1,0,1,0,0,0,1,1,1,1,1,1,1,1,1,’

WHILE (CHARINDEX(‘,’, @FormId) > 0)
BEGIN

DECLARE @Form VARCHAR(30)
SET @Form = SUBSTRING(@FormId,0, CHARINDEX(‘,’, @FormId))
PRINT @Form

DECLARE @FRead VARCHAR(30)
SET @FRead = SUBSTRING(@FormRead,0, CHARINDEX(‘,’, @FormRead))
PRINT @FRead

DECLARE @FWrite VARCHAR(30)
SET @FWrite = SUBSTRING(@FormWrite,0, CHARINDEX(‘,’, @FormWrite))
PRINT @FWrite

INSERT INTO tbl_UserFormRights(Fk_UserId,Fk_FormId,[Read],Write)
VALUES(1,@Form,@FRead,@FWrite)

SET @FormId = SUBSTRING(@FormId, CHARINDEX(‘,’, @FormId) + 1, LEN(@FormId))
SET @FormRead  = SUBSTRING(@FormRead, CHARINDEX(‘,’, @FormRead) + 1, LEN(@FormRead))
SET @FormWrite  = SUBSTRING(@FormWrite, CHARINDEX(‘,’, @FormWrite) + 1, LEN(@FormWrite))

END

END

Migratation Tool for Sql Database To MySql Database

February 15, 2010 Leave a comment

Various migration tools are available for converting sql database to mysql database.  I personally use the Ispirer – Sqlways migration tool for my projects,  it supports conversion of tables and stored procedures ,you can download it from below:

http://www.ispirer.com/download

Inserting Data Into Table Using Select Query

September 1, 2009 Leave a comment

In SQL, sometimes we need to select some data from one table and insert data into another table, this can be done as follows with a simple sql query:

Insert into Table1 (c1, c2, c3, c4, c5)
(Select c1, c2, c3, c4, c5 from Table2)

But make sure that both the tables have same fields, data type..


Getting Last Modified/Created Table, Stored Procedure, Function.. in Sql Server

August 25, 2009 3 comments

Sometimes, we want to know how many tables, stored procedures or functions we have created on a particular day, or on which tables, sps or functions, modifications have occured. All this can be known through a very simple sql query, which is as below:

select name,type,type_desc, create_date, modify_date from sys.objects

where  convert(varchar,modify_date,101)  =  convert(varchar,getdate(),101)

order by modify_date desc

Here

name : – “Its the table or sp or function name”

type: – “To identify a table or sp or function etc.. P, S, U”

type_desc: -“Description whether it is table, sp etc..”

S = System Table, PK = Primary Key,U = User Table,

P= Stored Procedure, TF = Table Value Function,

FN = Scalar Function, D= Default Constraint

create_date: – “Date when it is created”

modify_date: – “Last modified datetime of table, sp,.. etc”

In above query, in place of getdate(), you can pass the date you want, also you can get complete list after removing where condition, just giving order by modified date from sys objects..

Very important when we want to generate scripts for particular tables, sps or function for a particular date.