How to Compare SQL Database Schema in Visual Studio

How to compare SQL database schema in Visual Studio. I have recorded video for same. Please watch below for same.

Read More

SET NOCOUNT ON improve SQL Server store procedure performance

Whenever we write the store procedure to make sure SET NOCOUNT is always ON because when SET NOCOUNT OFF then each store procedure statement sending some message back to client and which is not require on all case.

SET NOCOUNT ON

If you require affect row count without SET NOCOUNT ON  then you just need to require add @@ROWCOUNT after statement of execution. So @@ROWCOUNT will return the result of affected record count.

UPDATE student SET Name= 'Kalpesh'
where studentid = 1



PRINT(@@ROWCOUNT)

When you working on Management Studio and require the message on output window then it will be good to SET NOCOUNT OFF because that we require for development purpose only.

SET NOCOUNT OFF

UPDATE student SET Name= 'Kalpesh'
where studentid = 1

 

When we create new store procedure from SQL Server management studio at time store procedure default template also set a SET NOCOUNT ON.

-- ================================================
-- Template generated from Template Explorer using:
-- Create Procedure (New Menu).SQL
--
-- Use the Specify Values for Template Parameters 
-- command (Ctrl-Shift-M) to fill in the parameter 
-- values below.
--
-- This block of comments will not be included in
-- the definition of the procedure.
-- ================================================
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:  
-- Create date: 
-- Description: 
-- =============================================
CREATE PROCEDURE  
 -- Add the parameters for the stored procedure here
 <@Param1, sysname, @p1>  = , 
 <@Param2, sysname, @p2>  = 
AS
BEGIN
 -- SET NOCOUNT ON added to prevent extra result sets from
 -- interfering with SELECT statements.
 SET NOCOUNT ON;

    -- Insert statements for procedure here
 SELECT <@Param1, sysname, @p1>, <@Param2, sysname, @p2>
END
GO

asdas

Read More

What is the Difference between RANK() and DENSE_RANK() in SQL Server?

Rank()

Rank function will generates a unique number to each distinct row, but it leaves a gap between the groups.

Dense_Rank()

Dense_Rank function is similar to Rank with only difference, this will not leave gaps between groups.

DECLARE @Table TABLE (
      column varchar(2)
)

INSERT INTO @Table (column)
      VALUES ('A'),('A'),('A'),('B'),('B'),('C'),('C');

SELECT
      Col_Value,
      Rank() OVER (ORDER BY column) AS 'Rank',
      DENSE_RANK() OVER (ORDER BY Col_Value) AS 'DENSE_RANK'
FROM
      @Table;

Column	RANK	DENSE_RANK
A	1	1
A	1	1
A	1	1
B	4	2
B	4	2
C	6	3
C	6	3

Read More

Sql Server

Sql Server page is dedicated to database related stuff and blog post.

Please looking related post for Sql Server related post under the "Related Post" section.

Hope you like those posts and please keep reading blog.
Please give your suggestions and comments for blog improvement.

Read More

After Updating Windows 10 Sql Server Services Need to Start Manually

Recently I just facing Sql Server issue. Which is Sql Server Management Studio not connect with Sql Server.

So based on issue I had google it and find a solution. Which is about Sql Server Services.

So I need to start Sql Server Services manually after updating Windows 10.

Following are step to start Sql Server Services.

1. Press Window + R key so it will open Run window.
2. Now type "services.msc" and hit enter so it will open Service dialogue.
3. Now find sql server service from the Service list.
4. Once find it, just need to click on left panel of Start link.
5. Wait for few second and it will start it.
6. Now you can try to connect Sql Server Management studio to Sql Server.

Hope you got your solution.

Read More

Reset AutoIncrement in SqlServer after Delete

I got solution for reset auto identity key to 1.

Just run below query and get result

DBCC CHECKIDENT (TableName, RESEED,1)

hope it will help you

Read More

Price format using store procedure in sql server

CREATE FUNCTION [dbo].[format_currency] (@monetary_value money ) returns varchar(20)
as
begin
declare @return_value varchar(20)
declare @is_negative bit
select @is_negative = case when @monetary_value<0 then 1 else 0 end

if @is_negative = 1
set @monetary_value = -1*@monetary_value

set @return_value = convert(varchar, isnull(@monetary_value, 0))

declare @before varchar(20), @after varchar(20)

if charindex ('.', @return_value )>0
begin
set @after= substring(@return_value, charindex ('.', @return_value ), len(@return_value))
set @before= substring(@return_value,1, charindex ('.', @return_value )-1)
end
else
begin
set @before = @return_value
set @after=''
end
-- after every third character:
declare @i int
if len(@before)>3
begin
set @i = 3
while @i>1 and @i < len(@before)
begin
set @before = substring(@before,1,len(@before)-@i) + ',' + right(@before,@i)
set @i = @i + 4
end
end
set @return_value = @before + @after

if @is_negative = 1
set @return_value = '-' + @return_value

return '$' +@return_value
end

Read More

Split function in sql server

Below function useful in sql server from split string.

create function dbo.Split(@String varchar(8000), @Delimiter char(1))
returns @temptable TABLE (id bigint identity(1,1),items varchar(8000))
as
begin
declare @idx int
declare @slice varchar(8000)

select @idx = 1
if len(@String)<1>

while @idx!= 0
begin
set @idx = charindex(@Delimiter,@String)
if @idx!=0
set @slice = left(@String,@idx - 1)
else
set @slice = @String

if(len(@slice)>0)
insert into @temptable(Items) values(@slice)

set @String = right(@String,len(@String) - @idx)
if len(@String) = 0 break
end
return
end


Example

select * from dbo.Split('abc efg', ' ')

OutPut
---------
abc
efg

Read More