Delete Duplicte Rows from Table

;WITH CTE AS (
 SELECT ROW_NUMBER() OVER(PARTITION BY Column1,Column2 ORDER BY Column3) AS RowID
   ,*
 FROM dbo.TableName (NOLOCK)
)
DELETE x
--SELECT Count(*)
FROM cte  x
WHERE RowID > 1

Reset Identity column in SQL Server

If you have deleted all records in your table and you want to start new identity value from 1 then you need to run below command
 

The following line resets the Identity value for the Employee table to 0 so that the next record added starts at 1.

DBCC CHECKIDENT('Employee', RESEED, 0)

Also, there are 40 records in your table and you want to start next record from 51 then you need to run below command

DBCC CHECKIDENT('Employee', RESEED, 50)




 

Querying XML in Sql Server

declare @xml xml = ' Viral Bhatt Petlad Anand Jinal Shah Ahmedabad Ahmedabad Rajesh Davda Wadhwan City Surendrabagar ' SELECT ISNULL(b.value('Name[1]','NVARCHAR(50)'),NULL) AS Name, ISNULL(b.value('City[1]','NVARCHAR(50)'),NULL) AS City, ISNULL(b.value('District[1]','NVARCHAR(50)'),NULL) AS District FROM @xml.nodes('/xmlroot/xmlattribute') a(b)

Sending E-Mail through SQL Server stored procedures

This summary is not available. Please click here to view the post.

Inline variable assignment in sql server 2008

Instead of:

DECLARE @myVar int
SET @myVar = 5

you can do it in one line:

DECLARE @myVar int = 5

Reset Identity Column in SQL Server

Run Following SQL Query in your Query Window and Execute it. This will reset the identity column’s value once gain to 0, so that the new record will start from 1.

DBCC CHECKIDENT(‘Users’, RESEED, 0)

Here ‘Users’ is table name.This will reset the Identity column’s value to 0. So, next record will start from 1.

Enjoy!!!

The transaction log for database 'mydatabase' is full

Issue : The transaction log for database 'mydatabase' is full. To find out why space in the log cannot be reused, see the log_reuse_wait_desc column in sys.databases

Resolution :

BACKUP LOG WITH TRUNCATE_ONLY
GO
DBCC SHRINKFILE (, 1)
GO

Basic Understanding for Sql server

* Difference between Clustered Index and Non Clustered Index

- A Clustered Index consists of index as well as data pages. Clustered Index is not just an index but also contains the table data. A clustered index is organized as a B-tree where the non-leaf nodes are index pages and the leaf nodes are data pages.

- A Non-clustered index is organized as a B-tree but it consists of only index pages. The leaf nodes in a non-clustered index are not data pages, but contains pointer for individual rows in a data pages.

* Master Database contains login Information.
* MSDB Database contains Job Information.
* Model system database is default to FULL recovery model.


* Difference between Primary Key and Unique Key is as follows

- Primary key prevents the duplication of key values and does not allow NULL values. It allows each row in a table to be identified uniquely.
- Unique Key does not same what Primary Key does except it allows NULL record.

* Only One NULL values can be inserted for column that has Unique Key defined.

* Difference between Clustered Index and Non Clustered Index

- A Clustered Index consists of index as well as data pages. Clustered Index is not just an index but also contains the table data. A clustered index is organized as a B-tree where the non-leaf nodes are index pages and the leaf nodes are data pages.

- A Non-clustered index is organized as a B-tree but it consists of only index pages. The leaf nodes in a non-clustered index are not data pages, but contains pointer for individual rows in a data pages.

Stuff Keyword in Sqlserver

STUFF - The STUFF function inserts a string into another string. It deletes a specified length of characters in the first string at the start position and then inserts the second string into the first string at the start position.

Syntex - STUFF (character_expression , start , length ,character_expression )

Example -

SELECT STUFF('VABCDERBHATT', 2, 6, 'iral');
GO

Get Row number from query

use Row_Number() function to get row numbers in rows

ex.

select Row_Number() over (order by fieldname) as rowid, * from [Tablename]

Export EXCEL File To SQL Server 2000 /2005 /2008

CREATE procedure [dbo].[InsertEXCELToSQLSERVER]
as

declare @Field1 nvarchar(50)
declare @Field2 nvarchar(50)

declare @CursorName cursor

set @CursorName = cursor
for
select * from openrowset('Microsoft.Jet.OLEDB.4.0','Excel 8.0;Database=\\path\datafile.xls','select @Field1,@Field2 from [Sheet1$]')
open @CursorName
fetch next from @CursorName into @Field1, @Field2

WHILE @@FETCH_STATUS = 0
BEGIN
select @Field1, @Field2 ;

INSERT INTO Tablename (Field1,Field2) VALUES (@Field1, @Field2 )

fetch next from @UserCursor into @Field1,@Field2

END

CLOSE @CursorName
DEALLOCATE @CursorName

OutPut : following will execute and insert data in to table
exec InsertEXCELToSQLSERVER

Functions In Sqlserver

Table valued Functions

create function [dbo].[GetInformation](@ParameterId int)
returns table as
return (
select count(*) as total from tablename where condition=@ParameterId)

to view results
select * from GetInformation(10) where 10 is parent table's primary key Id

Scalar valued Functions

ALTER FUNCTION [dbo].[GenerateReceiptNumber](@FirstName varchar(50),@Year varchar(50))
RETURNS varchar(50)
AS
BEGIN
DECLARE @Result varchar(50)
Declare @FN varchar(50)
Declare @YR varchar(50)

set @FN = @FirstName
set @YR = @Year


SELECT @Result = upper(@FN + @YR)

RETURN @Result

END

to view results write

select dbo.GenerateReferenceNumber('MyName','2009')
OutOut -- > MyName2009

Use Of Pivot Table in Sqlserver 2005

Display Columns into Rows Using Pivot Table

here is the example that from Attendence table Want to get Yearly Attendence report By Student Wise

select StudentID,
StudentName,
isnull(January,0) January,
isnull(February,0) February,
isnull(March,0) March,
isnull(April,0) April,
isnull(May,0) May,
isnull(June,0) June,
isnull(July,0) July,
isnull(August,0) August,
isnull(September,0) September,
isnull(October,0) October,
isnull(November,0) November,
isnull(December,0) December
from
(
select StudentID, StudentName, DateName(m,AttendenceDate) [Month], Count(*) Total from AttendenceTable
Group by StudentID, DateName(m,AttendenceDate),StudentName
) as source
Pivot
(
sum(Total)
for [month] in (January,February,March,April,May,June,July,August,September, October, November, December)

) as result

Useful Sql Querys

Print Row number in Sqlserver 2005

SELECT (ROW_NUMBER() OVER (ORDER BY field_name) )as RowNumber,
field1, field2,fieldn FROM tablename

Get random record from database

SELECT TOP 1 field1, field2,fieldn FROM tablename ORDER BY NEWID()

Find Recenty executed Query in SQLSERVER 2005

SELECT

DMExQryStats.last_execution_time AS [Executed At], DMExSQLTxt.text AS [Query]
FROM
sys.dm_exec_query_stats AS DMExQryStats
CROSS APPLY
sys.dm_exec_sql_text(DMExQryStats.sql_handle) AS DMExSQLTxt
ORDER BY
DMExQryStats.last_execution_time DESC


Taking Multiple backups in SQL SERVER 2005

BACKUP DATABASE Northwind
TO DISK = 'D:\DataabseBkps1\Northwind.bak'
MIRROR
TO DISK = 'E:\DataabseBkps2\Northwind.bak'
WITH FORMAT;

Simple Trigger

Simple trigger that Delete records from particular table.

Situation : There is one field type is single/joint if user select joint one field enter joint account name and if single then that fields disapears so now if user change joint type to single then joint account name is deleted form database using this trigger.

Answer :

CREATE TRIGGER Tr_Name_UpdateRecordTable
On Table_name
FOR UPDATE,insert AS
declare @Id int
declare @Type int

select @Id=Id from inserted
select @Type=Type from inserted

// @Type=1 mean single else joint

IF @Type=1

BEGIN

Update Table_name set JointAccountFirstName='' ,JointAccountMiddleName='' ,
JointAccountLastName='' where Id=@Id

END

What's New in SQL Server 2008?

Tuesday December 4, 2007

In a recent interview, SQL Server MVP Brad McGehee offered some observations about Microsoft's pending release of SQL Server 2008. His thoughts, in a nutshell, were:

• SQL Server continues to get more complex and many DBAs are behind the curve because they haven't yet upgraded to SQL Server 2005
• PowerShell, included in SQL Server 2008, is unlikely to see much adoption among DBAs
• The rewritten Reporting Services and Analysis Services are scalable and don't require IIS
• The Declarative Management Framework (DMF) is the big selling point for 2008, offering consistent enterprise management