Clean all the data from tables in particular DB

 EXEC sp_msforeachtable 'alter table ? NOCHECK CONSTRAINT all'

EXEC sp_MSForEachTable 'delete from ?'

exec sp_msforeachtable 'alter table ? WITH CHECK CHECK CONSTRAINT all'

NULLs and the NOT IN predicate

create table a (id int , colour varchar(10))
insert into a select 1,'Red' union all select 2,'Green' union all select 3,null

select * from a
create table b (colour varchar(10))insert into b select 'Red'union allselect 'Green' union all select 'Blue'


select * from b where b.colour not in (select a.colour from a )


Obviously this is 'incorrect'. What is the problem? It's simply that SQL uses three-valued logic, driven by the existence of NULL, which is not a value but a marker to indicate missing (or UNKNOWN) information. When the NOT operator is applied to the list of values from the subquery, in the IN predicate, it is translated like this:
The expression "color=NULL" evaluates to UNKNOWN and, according to the rules of three-valued logic, NOT UNKNOWN also evaluates to UNKNOWN. As a result, all rows are filtered out and the query returns an empty set.
This mistake will often surface if requirements change, and a non-nullable column is altered to allow NULLs. It also highlights the need for thorough testing. Even if, in the initial design, a column disallows NULLs, you should make sure your queries continue to work correctly with NULLs.
One solution is to use the EXISTS predicate in place of IN, since EXISTS uses two-valued predicate logic evaluating to TRUE/FALSE:


correct:
select * from b where not exists (select a.colour from a where a.colour =b.colour ) 

How to tell who did a backup when

SELECT      db.name AS DatabaseName,

            bf.logical_name AS LogicalName,

            CASE bs.[type]

                        WHEN 'D' THEN 'Database'

                        WHEN 'I' THEN 'Differential database'

                        WHEN 'L' THEN 'Log'

                        WHEN 'F' THEN 'File or filegroup'

                        WHEN 'G' THEN 'Differential file'

                        WHEN 'P' THEN 'Partial'

                        WHEN 'Q' THEN 'Differential partial'

                        ELSE 'Unknown'

            END AS BackupType,

            CASE bf.file_type

                        WHEN 'D' THEN 'SQL Server data file'

                        WHEN 'L' THEN 'SQL Server log file'

                        WHEN 'F' THEN 'Full text catalog'

                        ELSE 'Unknown'

            END AS FileType,

            bs.user_name AS UserName,

            bs.backup_start_date AS StartDate,

            bs.backup_finish_date AS FinishDate,

            CAST(bs.software_major_version AS VARCHAR(11)) + '.'

                        + CAST(bs.software_minor_version AS VARCHAR(11)) + '.'

                        + CAST(bs.software_build_version AS VARCHAR(11)) AS ServerVersion,

            bs.[compatibility_level] AS CompatibilityLevel,

            bs.backup_size AS BackupSize

FROM        msdb..backupfile AS bf

INNER JOIN msdb..backupset AS bs ON bs.backup_set_id = bf.backup_set_id

INNER JOIN master..sysaltfiles AS af ON af.name = bf.logical_Name

INNER JOIN master..sysdatabases AS db ON db.dbid = af.dbid

ORDER BY    db.name,

            bf.logical_name,

            bs.backup_finish_date DESC

Change schema for all tables

exec sp_MSforeachtable "PRINT '? modify'; ALTER SCHEMA dbo TRANSFER ?; IF @@ERROR = 0 PRINT '? modified'; PRINT ''"

Disable all constraints of all tables in a database using SQL Server

If you are looking out for a fast and a simple way of disabling all constraints of all tables in a database using SQL Server, then here is a undocumented stored procedure that helps you achieve the same

USE YOURDBNAME
EXEC sp_MSforeachtable @command1="ALTER TABLE ? NOCHECK CONSTRAINT ALL"

Quickest way to change the location of a log file in SQL Server

The quickest way to move the log file to a different location is to Detach the database, Move the .ldf and then Attach the database

Step 1: Detach the Database:

Use this command to detach the database

sp_detach_db 'YourDBName'

Step 2: Move the log file (.ldf):

Move the log file to a different location. For eg: Move it from 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\YourDBName_Log.ldf' to 'D:\Logback\YourDBName_Log.ldf'

Step 3: Attach the Database:

Once the file has been moved, attach the database again

sp_attach_db 'mydb','C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\YourDBName.mdf','D:\Logback\YourDBName_Log.ldf'

Note: To find out the current location of your database files, use the command 'sp_helpfile'

Increase Memory for Queries in SQL Server

The default memory for query execution 'min memory per query' allocated by SQL Server is equal to 1024 KB.

When should I increase the default memory allocated to queries?
1024 KB is sufficient to run queries, however you may need to increase the memory if you have an extremely busy server that runs many concurrent queries simultaneously or your query is quiet resource intensive. Also before increasing the memory, set a few performance benchmarks like the 'Expected Query Time' to determine if you really require an increase.

Note: Do not increase the memory unnecessarily as SQL Server may require it for other operations.

How can I increase memory for query execution?

Just use this query to increase the memory to 1536KB

EXEC sp_configure 'show advanced options', 1
EXEC sp_configure 'min memory per query', 1536
RECONFIGURE WITH OVERRIDE
EXEC sp_configure 'show advanced options', 0
RECONFIGURE WITH OVERRIDE