How to find the working days records in a month using SQL Server?

SELECT ActualTransactionDate FROM SubjectTransaction WHERE DATEPART(dw, ActualTransactionDate) IN (1, 7) and DATEPART (MONTH, ActualTransactionDate) IN (2)--Feb

What is C# Guid.empty() equivalent in Sql Server?

C#
--
Guid.empty()

SQL
---
cast(cast(0 as binary) as uniqueidentifier)

Syntax for Cursor in SQL Server

CREATE PROCEDURE sp_Test()
as

DECLARE @colA nvarchar(10)
DECLARE @colB nvarchar(10)
DECLARE @MyCursor CURSOR

SET @MyCursor = CURSOR FAST_FORWARD
FOR
Select colA,colB
From tableA

OPEN @MyCursor
FETCH NEXT FROM @MyCursor
INTO @ColA,@ColB

WHILE @@FETCH_STATUS = 0
BEGIN
PRINT @ColA
PRINT @ColB
FETCH NEXT FROM @MyCursor
INTO @ColA,@ColB
END

CLOSE @MyCursor
DEALLOCATE @MyCursor
GO

How to find duplicate records in SQL Server?

SELECT UserID FROM employee
GROUP BY userid
HAVING count(*) > 1

How to convert columns to a single Row in Sql Server?

declare @Name VARCHAR(8000)
select @Name = Coalesce(@Name,'') + pat_name + ',' from pat_info where pat_name like 'P%'
select @Name[Name]

SQL Server DatePart and Abbrevations

The table lists date parts and abbreviations recognized by Microsoft SQL Server.

Datepart	Abbreviations
year	yy, yyyy
quarter	qq, q
month	mm, m
dayofyear	dy, y
day	dd, d
week	wk, ww
weekday	dw
hour	hh
minute	mi, n
second	ss, s
millisecond	ms

Eg:
SELECT DATEPART(m, 0), DATEPART(d, 0), DATEPART(yy, 0)

How to Calculate Median in SQL Server 2005?

create table tblmedian(iSno int identity(1,1),iMedian int)

insert into tblmedian values(20)
insert into tblmedian values(21)
insert into tblmedian values(22)
insert into tblmedian values(23)
insert into tblmedian values(24)


create function [dbo].[UDF_Find_Median]()
returns int
as
begin
declare @Cnt int
declare @MidId1 int
declare @MidId2 int
declare @Result1 int
declare @Result2 int
declare @Retval int

select @cnt=count(*) FROM tblmedian;
if(@cnt%2=1)--odd number
begin
set @midid1=floor(round(cast(@cnt as float) / cast(2 as float),0))
select @retval=imedian from (SELECT ROW_NUMBER() OVER (ORDER BY imedian) AS RowID,floor(round(imedian,0)) as imedian FROM
tblmedian)Median where rowid=@midid1;
end
else
begin
set @midid1=floor(@cnt/2);
set @midid2=(@midid1+1);

select @result1=imedian from (SELECT ROW_NUMBER() OVER (ORDER BY imedian) AS RowID,floor(round(imedian,0)) as imedian FROM
tblmedian)Median1 where rowid=@midid1;

select @result2=imedian from (SELECT ROW_NUMBER() OVER (ORDER BY imedian) AS RowID,floor(round(imedian,0)) as imedian FROM
tblmedian)Median2 where rowid=@midid2;

set @retval=floor((@result1+@result2)/2);
end
return @retval;
end

select * from tblmedian

select [dbo].[udf_find_median]()

How to create Database,User Name and Password in Sql Server?

if db_id('TestDB') is null
begin
create database [TestDB]
end

if DATABASE_PRINCIPAL_ID('[TestDB]') is null
begin
use [TestDB]
if DATABASE_PRINCIPAL_ID('testlogin') is null
CREATE LOGIN [testlogin]
WITH PASSWORD='testpwd',
DEFAULT_DATABASE=[TestDB],
DEFAULT_LANGUAGE=[us_english],
CHECK_EXPIRATION=OFF,
CHECK_POLICY=OFF
end

if DATABASE_PRINCIPAL_ID('testlogin') is null
begin
use [TestDB]
CREATE USER testlogin
Grant Insert,Update,Delete,Execute,Select,Create Procedure to testlogin
end

How to change the Date Format in SQL Server?

To view the current format use the below query.
----------------------------------------------
select name ,alias, dateformat
from sys.syslanguages
where langid =
(select value from master..sysconfigures
where comment = 'default language')


To Change Date Format as DMY
-----------------------------
sp_configure 'default language', 23
reconfigure with override

To reset DMY to MDY replace 23 to 0

Note: It will affect only for the particular User login.

How to Convert Numbers to Words using Sql Server?

SET QUOTED_IDENTIFIER ON
SET ANSI_NULLS ON
SET NOCOUNT ON
GO



CREATE FUNCTION dbo.udf_Num_ToWords (

@Number Numeric (38, 0) -- Input number with as many as 18 digits

) RETURNS VARCHAR(8000)

AS BEGIN

DECLARE @inputNumber VARCHAR(38)
DECLARE @NumbersTable TABLE (number CHAR(2), word VARCHAR(10))
DECLARE @outputString VARCHAR(8000)
DECLARE @length INT
DECLARE @counter INT
DECLARE @loops INT
DECLARE @position INT
DECLARE @chunk CHAR(3) -- for chunks of 3 numbers
DECLARE @tensones CHAR(2)
DECLARE @hundreds CHAR(1)
DECLARE @tens CHAR(1)
DECLARE @ones CHAR(1)

IF @Number = 0 Return 'Zero'

-- initialize the variables
SELECT @inputNumber = CONVERT(varchar(38), @Number)
, @outputString = ''
, @counter = 1
SELECT @length = LEN(@inputNumber)
, @position = LEN(@inputNumber) - 2
, @loops = LEN(@inputNumber)/3

-- make sure there is an extra loop added for the remaining numbers
IF LEN(@inputNumber) % 3 <> 0 SET @loops = @loops + 1

-- insert data for the numbers and words
INSERT INTO @NumbersTable SELECT '00', ''
UNION ALL SELECT '01', 'one' UNION ALL SELECT '02', 'two'
UNION ALL SELECT '03', 'three' UNION ALL SELECT '04', 'four'
UNION ALL SELECT '05', 'five' UNION ALL SELECT '06', 'six'
UNION ALL SELECT '07', 'seven' UNION ALL SELECT '08', 'eight'
UNION ALL SELECT '09', 'nine' UNION ALL SELECT '10', 'ten'
UNION ALL SELECT '11', 'eleven' UNION ALL SELECT '12', 'twelve'
UNION ALL SELECT '13', 'thirteen' UNION ALL SELECT '14', 'fourteen'
UNION ALL SELECT '15', 'fifteen' UNION ALL SELECT '16', 'sixteen'
UNION ALL SELECT '17', 'seventeen' UNION ALL SELECT '18', 'eighteen'
UNION ALL SELECT '19', 'nineteen' UNION ALL SELECT '20', 'twenty'
UNION ALL SELECT '30', 'thirty' UNION ALL SELECT '40', 'forty'
UNION ALL SELECT '50', 'fifty' UNION ALL SELECT '60', 'sixty'
UNION ALL SELECT '70', 'seventy' UNION ALL SELECT '80', 'eighty'
UNION ALL SELECT '90', 'ninety'

WHILE @counter <= @loops BEGIN

-- get chunks of 3 numbers at a time, padded with leading zeros
SET @chunk = RIGHT('000' + SUBSTRING(@inputNumber, @position, 3), 3)

IF @chunk <> '000' BEGIN
SELECT @tensones = SUBSTRING(@chunk, 2, 2)
, @hundreds = SUBSTRING(@chunk, 1, 1)
, @tens = SUBSTRING(@chunk, 2, 1)
, @ones = SUBSTRING(@chunk, 3, 1)

-- If twenty or less, use the word directly from @NumbersTable
IF CONVERT(INT, @tensones) <= 20 OR @Ones='0' BEGIN
SET @outputString = (SELECT word
FROM @NumbersTable
WHERE @tensones = number)
+ CASE @counter WHEN 1 THEN '' -- No name
WHEN 2 THEN ' thousand ' WHEN 3 THEN ' million '
WHEN 4 THEN ' billion ' WHEN 5 THEN ' trillion '
WHEN 6 THEN ' quadrillion ' WHEN 7 THEN ' quintillion '
WHEN 8 THEN ' sextillion ' WHEN 9 THEN ' septillion '
WHEN 10 THEN ' octillion ' WHEN 11 THEN ' nonillion '
WHEN 12 THEN ' decillion ' WHEN 13 THEN ' undecillion '
ELSE '' END
+ @outputString
END
ELSE BEGIN -- break down the ones and the tens separately

SET @outputString = ' '
+ (SELECT word
FROM @NumbersTable
WHERE @tens + '0' = number)
+ '-'
+ (SELECT word
FROM @NumbersTable
WHERE '0'+ @ones = number)
+ CASE @counter WHEN 1 THEN '' -- No name
WHEN 2 THEN ' thousand ' WHEN 3 THEN ' million '
WHEN 4 THEN ' billion ' WHEN 5 THEN ' trillion '
WHEN 6 THEN ' quadrillion ' WHEN 7 THEN ' quintillion '
WHEN 8 THEN ' sextillion ' WHEN 9 THEN ' septillion '
WHEN 10 THEN ' octillion ' WHEN 11 THEN ' nonillion '
WHEN 12 THEN ' decillion ' WHEN 13 THEN ' undecillion '
ELSE '' END
+ @outputString
END

-- now get the hundreds
IF @hundreds <> '0' BEGIN
SET @outputString = (SELECT word
FROM @NumbersTable
WHERE '0' + @hundreds = number)
+ ' hundred '
+ @outputString
END
END

SELECT @counter = @counter + 1
, @position = @position - 3

END

-- Remove any double spaces
SET @outputString = LTRIM(RTRIM(REPLACE(@outputString, ' ', ' ')))
SET @outputstring = UPPER(LEFT(@outputstring, 1)) + SUBSTRING(@outputstring, 2, 8000)


RETURN @outputString -- return the result
END
GO

GRANT EXEC on dbo.udf_Num_ToWords TO PUBLIC
GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO



--Example
select dbo.udf_Num_ToWords (4670)

How to format auto id number to 0000001 in Sql Server?

declare @num int
declare @fixed_size int

set @fixed_size = 10
set @num = 40

SELECT @num as [orig], RIGHT(REPLICATE('0',@fixed_size) + CAST(@num AS varchar(10)),@fixed_size) As [modif]

How to get all the primary key,unique,foreign key in a Sql Server database?

SELECT A.TABLE_NAME, A.CONSTRAINT_NAME, B.COLUMN_NAME
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS A, INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE B
WHERE CONSTRAINT_TYPE = 'PRIMARY KEY' AND A.CONSTRAINT_NAME = B.CONSTRAINT_NAME
ORDER BY A.TABLE_NAME

How to remove special characters in a string using SQL Server?

CREATE FUNCTION dbo.Fun_ParseAlphaChars
(
@string VARCHAR(8000)
)
RETURNS VARCHAR(8000)
AS
BEGIN
DECLARE @IncorrectCharLoc SMALLINT
SET @IncorrectCharLoc = PATINDEX('%[^0-9A-Za-z]%', @string)
WHILE @IncorrectCharLoc > 0
BEGIN
SET @string = STUFF(@string, @IncorrectCharLoc, 1, '')
SET @IncorrectCharLoc = PATINDEX('%[^0-9A-Za-z]%', @string)
END
SET @string = @string
RETURN @string
END
GO


Test
-----
SELECT dbo.Fun_ParseAlphaChars('123k@nn@/;')

Output:
-------
123knn

How to find the Particular Table dependent Table List in Sql Server?

SELECT
   pt.COLUMN_NAME AS PrimaryField,
   c.CONSTRAINT_NAME AS KeyName,
   fk.TABLE_NAME AS ForeignTable,
   cu.COLUMN_NAME AS ForeignField
FROM
   INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS c
   INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS fk ON
   c.CONSTRAINT_NAME = fk.CONSTRAINT_NAME
   INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS pk ON
   c.UNIQUE_CONSTRAINT_NAME = PK.CONSTRAINT_NAME
   INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE cu ON
   c.CONSTRAINT_NAME = CU.CONSTRAINT_NAME
   INNER JOIN (
   SELECT tc.TABLE_NAME, kcu.COLUMN_NAME
   FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc
   INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE kcu
   ON tc.CONSTRAINT_NAME = kcu.CONSTRAINT_NAME
   WHERE tc.CONSTRAINT_TYPE = 'PRIMARY KEY'
   ) pt ON pt.TABLE_NAME = pk.TABLE_NAME
   WHERE pk.TABLE_NAME = 'MyTableName'
ORDER BY foreignTable ASC;

How to write query for selecting the age using DOB in SQL Server?

select datediff (year, convert (datetime, '01/12/1983'), getdate())--mm/dd/yyyy

How do I get a list of SQL Server tables and their row counts?

SELECT
[TableName] = so.name,
[RowCount] = MAX(si.rows)
FROM
sysobjects so,
sysindexes si
WHERE
so.xtype = 'U'
AND
si.id = OBJECT_ID(so.name)
GROUP BY
so.name
ORDER BY
2 DESC

Some Alter Queries For SQL Server (Tested in SQL Server 2005)

ABOUT WHAT	QUERIES
Adding Primary Key	ALTER TABLE Table_Name ADD PRIMARY KEY (Column_Name)
Adding Foreign Key with Cascade	ALTER TABLE child_name add CONSTRAINT constraint_name FOREIGN KEY (childtable column1, column2, ... column_n) REFERENCES parent_table(column1, column2, ... column_n) on update cascade;
Adding Unique Key	ALTER TABLE Table_Name ADD UNIQUE (Column_Name);
Adding New Column	ALTER TABLE Table_Name ADD NewCol VARCHAR(50);
Altering Column Type/Width	ALTER TABLE Table_Name ALTER COLUMN NewCol varchar(80) NOT NULL;
Addng Identity(Note:Table Should Be Empty)	ALTER TABLE Table_Name ALTER COLUMN ColName ADD CONSTRAINT IDENTITY(1,1);
Adding Default Value	ALTER TABLE Table_Name ADD CONSTRAINT Constraint_Name DEFAULT 'dotnetkanna' FOR Column_Name;
Drop Constraint	ALTER TABLE Table_Name DROP CONSTRAINT Constraint_Name;
Drop Column	ALTER TABLE Table_Name DROP Column Column_Name;
Rename The Table	SP_RENAME 'Old_TableName','New_TableName';
Rename The Column	sp_rename @objname = 'table_name.old_column_name', @newname = 'new_column_name', @objtype = 'COLUMN';
Rename The Database	sp_renamedb 'Old_DBName', 'New_DBName';

How to generate row number in SQL Server?

SELECT (SELECT COUNT(i.ref_no) FROM student_information i
WHERE i.ref_no >= o.ref_no ) AS RowID, sname AS 'Name' FROM student_information o ORDER BY RowID;

How to Randomly select the records in SQL Server?

select top 5 * from question_settings where class ='1' order by newid();

How to Clean your Database Records & Reset Identity Columns in Sql Server 2005?

/*Disable Constraints & Triggers*/
exec sp_MSforeachtable 'ALTER TABLE ? NOCHECK CONSTRAINT ALL'
exec sp_MSforeachtable 'ALTER TABLE ? DISABLE TRIGGER ALL'

/*Perform delete operation on all table for cleanup*/
exec sp_MSforeachtable 'DELETE ?'

/*Enable Constraints & Triggers again*/
exec sp_MSforeachtable 'ALTER TABLE ? CHECK CONSTRAINT ALL'
exec sp_MSforeachtable 'ALTER TABLE ? ENABLE TRIGGER ALL'

/*Reset Identity on tables with identity column*/
exec sp_MSforeachtable 'IF OBJECTPROPERTY(OBJECT_ID(''?''), ''TableHasIdentity'') = 1 BEGIN DBCC CHECKIDENT (''?'',RESEED,0) END'