SQL Server Tips

SQL Server 2000 Survival Guide ● SQL Server DB File

Unicode - Stored Procedures - newid() - Constraints - Case Sensitive search - Backup - Enumerate databases

Use N'some text' to mark "some text" as a unicode text in SQL statements. Useful for Cyrillic, for example.

Stored Procedures

Check whether a stored procedure exists, create it, execute it.

-- Create Stored Procedure
USE ClassNorthwind
GO

IF EXISTS (SELECT * FROM dbo.sysobjects
		WHERE id = object_id(N'[dbo].[MyOrders]')
		AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
	DROP PROCEDURE [dbo].[MyOrders]
GO

CREATE PROCEDURE MyOrders
	AS
	SELECT * FROM Orders
	WHERE RequiredDate < GETDATE() AND ShippedDate IS NULL
GO

-- Execute Stored Procedure by Itself
EXEC MyOrders
GO

newid() function is used as a default value to create a GUID (unique) value upon Insert of a row.

Constraints

Here is an SQL script that deletes the rows from a table. It disables the constraints before deleting and restores them afterwards.

SET NOCOUNT ON;

-- set constraints off.
ALTER TABLE [Tcountry] NOCHECK CONSTRAINT ALL;

delete from Tcountry;

-- set constraints on.
ALTER TABLE [Tcountry] CHECK CONSTRAINT ALL;

Case Sensitive search

Database collation determines whether the string comparison is case-sensitive or case-insensitive.

Backup

use master

backup database database_name to disk = 'c:\temp\file.dat'

Restore

use master
go

restore database alice from disk = 'c:\temp\file.dat' with replace
go

Case 2:

Enumerate Databases at server

sp_databases

Clear Query Cache

To clear the cache after running a query execute the code below. Otherwise, the query cache is cleared only on server restart.

CHECKPOINT

DBCC DROPCLEANBUFFERS