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 (
, s_id INT,
DECLARE @s_id INT
DECLARE @dateVal date
SET @s_id = 225
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 a.s_id=@s_id
SET @s_id = @s_id + 15
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
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