sql server tsql tasks tips

remove duplicate entries from table sql server

WITH CTE(col1, col2, col3,ranking) AS

(SELECT col1, col2, col3,ranking = DENSE_RANK() OVER(PARTITION BY col1, col2, col3 ORDER BY NEWID() ASC) FROM MyTable)

DELETE FROM CTE WHERE Ranking > 1

Here col 1 , col2 , col3  are the columns that uniquely determines the duplicate entry for the table row.

To understand the query run only the nested part “SELECT col1, col2, col3,ranking = DENSE_RANK() OVER(PARTITION BY col1, col2, col3 ORDER BY NEWID() ASC) FROM MyTable”

Combine output of while loop into single window in SQL Server:

This is done by inserting output for every iteration in a temp table and then selecting all values from it.

DECLARE @TempTable TABLE (

fromname VARCHAR(255)

, s_id INT,

DateValue date

)

DECLARE @s_id INT

DECLARE @dateVal date

SET @s_id = 225

while(@s_id <=630)

BEGIN

INSERT INTO @TempTable

select * from(SELECT  distinct fromname ,((60)* DATEPART(hour, lUpdate) + DATEPART(minute, lUpdate)/1) as s_id,CONVERT(date,lUpdate) as DateValue

FROM [<table_name>] with (nolock) where CONVERT(date,lUpdate) = ‘2015-03-08’)a where  [email protected]_id

SET @s_id = @s_id + 15

END

select * from @TempTable

The above query combines all different output window results into same window so that it can be selected as one and can probably be copied to excel sheet or any required analysis can be carried out.

TRUNCATE TABLE and DROP TABLE commands are not logged in the online transaction log  ? Is it true ?

The exact deleted values are not logged in the online SQL Server transaction log, only the IDs of the pages that held the truncated records are logged.  These pages are marked for overwriting in the database data file and the truncated data will be gone for good when the new transactions are written to these pages.

This myth is also based on the fact that these commands take little time to execute, they are almost instantaneous

The SQL Server transaction log shrinking will make free space in the online transaction log so don’t need to create transaction log backups ? Is it true ?

The shrink operation is not a good maintenance practice because it doesn’t solve the transaction log size issue permanently. After the initial shrinking, the transaction log will grow again. As the auto-growth event is one of the most intensive SQL Server operations, it should be avoided. The recommended method to keep the size of the online transaction log is to create transaction log backups regularly. Or, switching to the Simple recovery model, if you can tolerate data loss.

Sql Server order by based on IN clause

Following query orders result based on IN clause values
SELECT field1, field2, field3
FROM table
WHERE field1 IN (val1, val2, val3, val4,val5, val6, val7)
ORDER BY CASE field1
WHEN val1 THEN 1
WHEN val2 THEN 2
WHEN val3 THEN 3
WHEN val4 THEN 4
WHEN val5 THEN 5
WHEN val6 THEN 6
WHEN val7 THEN 7
END