Select Random Rows from Table in SQL Server

To Select 1 Random Records from a table in SQL Server

 

SELECT TOP 1 * FROM Table_Name
ORDER BY NEWID() ;

 

To Select 10 Random Records from a table in SQL Server

 

SELECT TOP 10 * FROM tAudit
ORDER BY NEWID()

Advertisements

Remove Null Rows SQL Server

DELETE
FROM TABLENAME
WHERE
(COLUMN_NAME IS NULL OR COLUMN_NAME = 0);

If you have additional columns containing NULL values and you want to refine your delete criteria

DELETE
FROM TABLENAME
WHERE
(COLUMN_NAME IS NULL OR COLUMN_NAME = 0) AND
(COLUMN_NAME_1 IS NULL OR COLUMN_NAME_1 = 0) AND
(COLUMN_NAME_2 IS NULL OR COLUMN_NAME_2 = 0) ;

Create Snapshot on Mirror Database SQL Server 2008

Create snapshot on mirror database Sql Server 2008

— Run below command to find logical filename of database against which you creating a mirror

select name, type_desc, physical_name from sys.master_files
where database_id = db_id(‘QIIB_MSCRM’)

mscrm ROWS C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\QIIB_MSCRM.mdf
mscrm_log LOG C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\QIIB_MSCRM_log.LDF

— mscrm is name of file. Now go ahead and create snapshot called snapshotdb with filename snpss1.ss in d: drive

CREATE DATABASE snapshotdb
ON
(NAME = ‘mscrm’,FILENAME = ‘d:\snpss1.ss’
)
AS SNAPSHOT OF qiib_mscrm

select name, type_desc, physical_name from sys.master_files
where database_id = db_id(‘QIIB_MSCRM’)