SQL

SQL syntax

LIKE: _ for single character (as opposed to %)

Datetimes:

SELECT * FROM Orders WHERE OrderDate < '20080101'; --other strings work but beware locale
SELECT CONVERT(DateTime, '20080101', 112); -- 103 is ddmmyyyy, 101 is mmddyyyy
SELECT DATEADD(DD, 7, GETDATE()); --7 days from today
SELECT CURRENT_TIMESTAMP; --(sql server and oracle) current date time
SELECT SYSUTCDATETIME(); --UTC time (DATETIME2, also SYSDATETIMEOFFSET() )

NULL will trip you up. In C#, null == null, but in SQL NULL <> NULL
- WHERE X = NULL doesn't work. It's WHERE X IS NULL (inverse: IS NOT NULL)
- IS NULL operator is not the same as ISNULL(x,x) function (which is closer to ANSI standard COALESCE)
- For sorting, it's best to use ISNULL/ COALESCE / CASE for NULLs
- SELECT COUNT(X) returns fewer rows than SELECT COUNT(*) because NULL rows are excluded (see also AVG() etc)
- You can't CREATE UNIQUE INDEX IX ON TABLE(X) (but you can add WHERE X IS NOT NULL for a filtered index)

Parameterized WHERE columns:

@Filter         nvarchar(256),
@FilterOn       nvarchar(256)
SELECT * FROM  USERS
WHERE (
 CASE 
   WHEN @FilterOn='UserID' THEN USERS.UserID 
   WHEN @FilterOn='UserRole' THEN USERS.Role 
   WHEN @FilterOn='Name' THEN USERS.Name 
   ELSE USERS.UserID
 END
  LIKE @Filter)

SQLServer2005 paging use ROW_NUMBER() OVER(ORDER BY col) as RowNum
Note you have to repeat the select list in the outer sql too.

SELECT ...
FROM
   (SELECT ... 
        , ROW_NUMBER() OVER(ORDER BY ColumnName) as RowNum
    FROM Employees e)
WHERE RowNum BETWEEN @startRowIndex AND (@startRowIndex + @maximumRows) - 1

Multi-table selects can deadlock against row-level update locks.
- In Sql2000 and Oracle, use dirty reads (or see below for 2005's snapshot)
```
SELECT * FROM USERS WITH (NOLOCK)
```
  using (TransactionScope tscope =
  new TransactionScope(TransactionScopeOption.Required,
  new TransactionOptions { IsolationLevel = System.Transactions.IsolationLevel.ReadUncommitted } ))
- In Sql2005+, avoid deadlocks with snapshot isolation (which must be enabled first)
```
ALTER DATABASE AdventureWorks
SET ALLOW_SNAPSHOT_ISOLATION ON
```
  using (var tscope =
  new TransactionScope(TransactionScopeOption.Required,
  new TransactionOptions { IsolationLevel = System.Transactions.IsolationLevel.Snapshot }))

Management Notes

DON'T PREFIX SPROCS WITH "SP_". That looks them up in master database first. A common error.
Command Line stopping: NET STOP MSSQLSERVER /Y (the Y is to stop SQLServerAgent too).
I have to do this to free up memory for VPCs.
Backup/restore across machines- use sp_change_users_login 'Update-One' 'user1', 'user1' to fix the database user to the server user

To get Identity after an insert:

INSERT INTO [Categories] (CategoryName) VALUES ('Random');
--use @@IDENTITY 
SET @iid = @@IDENTITY;
--SCOPE_IDENTITY() is safer as triggers may be firing
SET @sid = SCOPE_IDENTITY();

BCP: to bulk export a table:

--export
bcp database.dbo.MYTABLE out myfile.bcp -N -T

--import
sqlcmd -ddatabase -Q"delete from dbo.MYTABLE"
bcp database.dbo.MYTABLE in myfile.bcp -N -T

--with mixed mode authentication, append 
-Sserver_name\instance_name -Umylogin -Pmypassword

Rebuild all indexes (2005 version, with 2000 version commented out)

DECLARE @TableName nvarchar(255)
DECLARE @sql nvarchar(255)

DECLARE TableCursor CURSOR FOR
SELECT  table_schema + '.' + table_name FROM information_schema.tables
WHERE table_type = 'base table' Order by table_name 

OPEN TableCursor

FETCH NEXT FROM TableCursor INTO @TableName
WHILE @@FETCH_STATUS = 0
BEGIN
--DBCC DBREINDEX(@TableName,' ',90)
SET @sql = 'ALTER INDEX ALL ON ' + @TableName + ' REBUILD' 
EXEC (@sql)
FETCH NEXT FROM TableCursor INTO @TableName
END

CLOSE TableCursor

DEALLOCATE TableCursor

Shrinking the log file

Renaming constraints (Access can't read SQLServer primary keys with "." in the name)

DECLARE @sql NVARCHAR(MAX);

SELECT @sql = '';
SELECT @sql = @sql + 
	'EXEC sp_rename ''' + QUOTENAME(CONSTRAINT_SCHEMA) + '.' + QUOTENAME(CONSTRAINT_NAME) + 
	''', N'''  + LEFT(CONSTRAINT_NAME,3) + 'ems' + SUBSTRING(CONSTRAINT_NAME,8,99) + 
	''', ''OBJECT'';'+ char(13) + char(10)
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS 
WHERE 
	CONSTRAINT_NAME LIKE '%K_dbo.%' AND 
	CONSTRAINT_NAME NOT LIKE '%__MigrationHistory'

EXEC sp_executesql @sql;

ApplicationPoolIdentity doesn't have SQL login (and it's a virtual account):
- for local SQL, add login "IIS APPPOOL\AppPoolName" (change the appPoolname here!)
- for remote SQL, add "domain\serverName$"

Oracle

Oracle 11 has increased security (auto-expiring passwords, case sensitivity).

In SqlPlus, connect as SYSDBA: conn sys/secretpassword as sysdba
Find expired and locked accounts: select username, account_status from dba_users where account_status like 'EXPIRED%';
To reset an expired user, change the password: password HR New password:
Or alter user HR IDENTIFED BY new_password;
To unlock a user: alter user HR account unlock;
To disable password expiry: ALTER PROFILE DEFAULT LIMIT PASSWORD_LIFE_TIME UNLIMITED;
To find profile if it's not DEFAULT: select profile from dba_users where username = 'HR';

static void

SQL

Links

SQL syntax

Management Notes

Oracle