Friday, March 27, 2009

Users get blocked when access same table while getting Unique IDs

--Table which will have the Unique IDs
CREATE TABLE dbo.CounterTable
(
Counter int IDENTITY(1,1) NOT NULL
)
GO
--Procedure which Inserts unique IDs in table and returns the New ID

Wednesday, March 18, 2009

Simple cursor Example in Microsoft SQL Server

DECLARE @String nVARCHAR(MAX)
DECLARE @getInputBuffer CURSOR
SET @getInputBuffer = CURSOR FOR
SELECT Text FROM Table

OPEN @getInputBuffer

FETCH NEXT
FROM @getInputBuffer INTO @String

WHILE @@FETCH_STATUS = 0

Get foreign keys of a table in SQL Server

SELECT f.name AS ForeignKey
,OBJECT_NAME(f.parent_object_id) AS TableName
,COL_NAME(fc.parent_object_id
,fc.parent_column_id) AS ColumnName
,OBJECT_NAME (f.referenced_object_id) AS ReferenceTableName
,COL_NAME(fc.referenced_object_id
,fc.referenced_column_id) AS ReferenceColumnName
FROM sys.foreign_keys AS f
INNER JOIN sys.foreign_key_columns AS fc
ON f.OBJECT_ID = fc.constraint_object_id
WHERE OBJECT_NAME (f.referenced_object_id)   = 'TableName'
ORDER BY 2

Find /Remove New Line or Carriage Return Character in String or Text in SQL Server

1)

DECLARE @NewLine char(2)
SET @NewLine=char(13)+char(10)

SELECT *
FROM TABLENAME
WHERE CHARINDEX(@NewLine,ColumnName) > 0


2)

DECLARE @NewLine char(2)
SET @NewLine=char(13)

SELECT *
FROM TABLENAME
WHERE CHARINDEX(@NewLine,ColumnName) > 0



3)

DECLARE @NewLine char(2)
SET @NewLine=char(10)

SELECT *
FROM TABLENAME
WHERE CHARINDEX(@NewLine,ColumnName) > 0

Delete Duplicate Records Using Common Tabel Expression


there are many ways to delete the duplicates in a table but sometimes the group by doesnt seem to work so you can try out the new feature of Microsoft SQL Server "Common Table Expression"

With Dups as 
(
SELECT  row_number() over (
partition by ColumnName 
order by  ColumnName ) as RowNum
FROM Table
)
DELETE 
FROM Dups 
WHERE rownum > 1