Disk Layout for SQL Server

Typically, it's sufficient to place the transaction log on a RAID 1 disk system, which doesn't provide striping of the data,
because the transaction log writes sequentially and will not benefit from striping. However, if you have intensive
activities (such as triggers or transaction log replication) that read from the log, it's a good idea to stripe the log on
multiple disk drives using a RAID 10 controller. By taking this approach, you can have different disk arms working on
the different read/write activities

Triggers

The inserted and deleted tables are structured the same as the table on which the trigger was defined—that is, they have
the same columns as the base table. Note that these tables are not indexed; therefore, every time you query them, you're
scanning the whole thing. There are some exceptions, of course. For example, if you use the EXISTS predicate or a
TOP query with no ORDER BY clause, SQL Server won't need to scan the whole table.

Pivot (Dynamic) vaildate SQL injection

USE [Northwind]
GO

/****** Object: StoredProcedure [dbo].[usp_pivot] Script Date: 07/07/2010 16:03:59 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE PROC [dbo].[usp_pivot]
@schema_name AS sysname = N'dbo', -- schema of table/view
@object_name AS sysname = NULL, -- name of table/view
@on_rows AS sysname = NULL, -- group by column
@on_cols AS sysname = NULL, -- rotation column
@agg_func AS NVARCHAR(12) = N'MAX', -- aggregate function
@agg_col AS sysname = NULL -- aggregate column
AS
DECLARE
@object AS NVARCHAR(600),
@sql AS NVARCHAR(MAX),
@cols AS NVARCHAR(MAX),
@newline AS NVARCHAR(2),
@msg AS NVARCHAR(500);
SET @newline = NCHAR(13) + NCHAR(10);
SET @object = QUOTENAME(@schema_name) + N'.' + QUOTENAME(@object_name);
-- Check for missing input
IF @schema_name IS NULL
OR @object_name IS NULL
OR @on_rows IS NULL
OR @on_cols IS NULL
OR @agg_func IS NULL
OR @agg_col IS NULL
BEGIN
SET @msg = N'Missing input parameters: '
+ CASE WHEN @schema_name IS NULL THEN N'@schema_name;' ELSE N'' END
+ CASE WHEN @object_name IS NULL THEN N'@object_name;' ELSE N'' END
+ CASE WHEN @on_rows IS NULL THEN N'@on_rows;' ELSE N'' END
+ CASE WHEN @on_cols IS NULL THEN N'@on_cols;' ELSE N'' END
+ CASE WHEN @agg_func IS NULL THEN N'@agg_func;' ELSE N'' END
+ CASE WHEN @agg_col IS NULL THEN N'@agg_col;' ELSE N'' END
RAISERROR(@msg, 16, 1);
RETURN;
END
-- Allow only existing table or view name as input object
IF COALESCE(OBJECT_ID(@object, N'U'),
OBJECT_ID(@object, N'V')) IS NULL
BEGIN
SET @msg = N'%s is not an existing table or view in the database.';
RAISERROR(@msg, 16, 1, @object);
RETURN;
END
-- Verify that column names specified in @on_rows, @on_cols, @agg_col exist
IF COLUMNPROPERTY(OBJECT_ID(@object), @on_rows, 'ColumnId') IS NULL
OR COLUMNPROPERTY(OBJECT_ID(@object), @on_cols, 'ColumnId') IS NULL
OR COLUMNPROPERTY(OBJECT_ID(@object), @agg_col, 'ColumnId') IS NULL
BEGIN
SET @msg = N'%s, %s and %s must'
+ N' be existing column names in %s.';
RAISERROR(@msg, 16, 1, @on_rows, @on_cols, @agg_col, @object);
RETURN;
END
-- Verify that @agg_func is in a known list of functions
-- Add to list as needed and adjust @agg_func size accordingly
IF @agg_func NOT IN
(N'AVG', N'COUNT', N'COUNT_BIG', N'SUM', N'MIN', N'MAX',
N'STDEV', N'STDEVP', N'VAR', N'VARP')
BEGIN
SET @msg = N'%s is an unsupported aggregate function.';
RAISERROR(@msg, 16, 1, @agg_func);
RETURN;
END
-- Construct column list
SET @sql =
N'SET @result = ' + @newline +
N' STUFF(' + @newline +
N' (SELECT N'','' + '
+ N'QUOTENAME(pivot_col) AS [text()]' + @newline +
N' FROM (SELECT DISTINCT('
+ QUOTENAME(@on_cols) + N') AS pivot_col' + @newline +
N' FROM ' + @object + N') AS DistinctCols' + @newline +
N' ORDER BY pivot_col' + @newline +
N' FOR XML PATH('''')),' + @newline +
N' 1, 1, N'''');'
EXEC sp_executesql
@stmt = @sql,
@params = N'@result AS NVARCHAR(MAX) OUTPUT',
@result = @cols OUTPUT;
-- Check @cols for possible SQL injection attempt
IF UPPER(@cols) LIKE UPPER(N'%0x%')
OR UPPER(@cols) LIKE UPPER(N'%;%')
OR UPPER(@cols) LIKE UPPER(N'%''%')
OR UPPER(@cols) LIKE UPPER(N'%--%')
OR UPPER(@cols) LIKE UPPER(N'%/*%*/%')
OR UPPER(@cols) LIKE UPPER(N'%EXEC%')
OR UPPER(@cols) LIKE UPPER(N'%xp_%')
OR UPPER(@cols) LIKE UPPER(N'%sp_%')
OR UPPER(@cols) LIKE UPPER(N'%SELECT%')
OR UPPER(@cols) LIKE UPPER(N'%INSERT%')
OR UPPER(@cols) LIKE UPPER(N'%UPDATE%')
OR UPPER(@cols) LIKE UPPER(N'%DELETE%')
OR UPPER(@cols) LIKE UPPER(N'%TRUNCATE%')
OR UPPER(@cols) LIKE UPPER(N'%CREATE%')
OR UPPER(@cols) LIKE UPPER(N'%ALTER%')
OR UPPER(@cols) LIKE UPPER(N'%DROP%')
-- look for other possible strings used in SQL injection here
BEGIN
SET @msg = N'Possible SQL injection attempt.';
RAISERROR(@msg, 16, 1);
RETURN;
END
-- Create the PIVOT query
SET @sql =
N'SELECT *' + @newline +
N'FROM' + @newline +
N' ( SELECT ' + @newline +
N' ' + QUOTENAME(@on_rows) + N',' + @newline +
N' ' + QUOTENAME(@on_cols) + N' AS pivot_col,' + @newline +
N' ' + QUOTENAME(@agg_col) + N' AS agg_col' + @newline +
N' FROM ' + @object + @newline +
N' ) AS PivotInput' + @newline +
N' PIVOT' + @newline +
N' ( ' + @agg_func + N'(agg_col)' + @newline +
N' FOR pivot_col' + @newline +
N' IN(' + @cols + N')' + @newline +
N' ) AS PivotOutput;';
EXEC sp_executesql @sql;
GO

Pivot (Dynamic)

USE [master]
GO

/****** Object: StoredProcedure [dbo].[sp_pivot] Script Date: 07/07/2010 14:26:48 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE PROC [dbo].[sp_pivot]
@query AS NVARCHAR(MAX),
@on_rows AS NVARCHAR(MAX),
@on_cols AS NVARCHAR(MAX),
@agg_func AS NVARCHAR(MAX) = N'MAX',
@agg_col AS NVARCHAR(MAX)

AS
DECLARE
@sql AS NVARCHAR(MAX),
@cols AS NVARCHAR(MAX),
@newline AS NVARCHAR(2);
SET @newline = NCHAR(13) + NCHAR(10);
-- If input is a valid table or view
-- construct a SELECT statement against it
IF COALESCE(OBJECT_ID(@query, N'U'),
OBJECT_ID(@query, N'V')) IS NOT NULL
SET @query = N'SELECT * FROM ' + @query;
-- Make the query a derived table
SET @query = N'(' + @query + @newline + N' ) AS Query';
-- Handle * input in @agg_col
IF @agg_col = N'*'
SET @agg_col = N'1';
-- Construct column list
SET @sql =
N'SET @result = ' + @newline +
N' STUFF(' + @newline +
N' (SELECT N'','' + '
+ N'QUOTENAME(pivot_col) AS [text()]' + @newline +
N' FROM (SELECT DISTINCT('
+ @on_cols + N') AS pivot_col' + @newline +
N' FROM' + @query + N') AS DistinctCols' + @newline +
N' ORDER BY pivot_col' + @newline +
N' FOR XML PATH('''')),' + @newline +
N' 1, 1, N'''');'
EXEC sp_executesql
@stmt = @sql,
@params = N'@result AS NVARCHAR(MAX) OUTPUT',
@result = @cols OUTPUT;
-- Create the PIVOT query
SET @sql =
N'SELECT *' + @newline +
N'FROM' + @newline +
N' ( SELECT ' + @newline +
N' ' + @on_rows + N',' + @newline +
N' ' + @on_cols + N' AS pivot_col,' + @newline +
N' ' + @agg_col + N' AS agg_col' + @newline +
N' FROM ' + @newline +
N' ' + @query + @newline +
N' ) AS PivotInput' + @newline +
N' PIVOT' + @newline +
N' ( ' + @agg_func + N'(agg_col)' + @newline +
N' FOR pivot_col' + @newline +
N' IN(' + @cols + N')' + @newline +
N' ) AS PivotOutput;'
EXEC sp_executesql @sql;
GO

Disabling (or Enabling) SQL Server Agent Jobs

http://www.mssqltips.com/tip.asp?tip=1400

Current login user

select @@IDENTITY ,@@CONNECTIONS,CURRENT_USER, SUSER_SNAME(),@@version

VIEW_METADATA

1) Minor Security nit: because you do not want the client software to know the names of your base tables.

and

2) Client App problem: The client app does not behave correctly unless you turn this on. This could happen for instance, because you restructured a former base table into a new structure and organization in your DB and used a view of the same name make the schema appear the same to the app. But the app is trapping the base-table name of the columns, whic is no longer correct unless you turn VIEW_METADATA on.

also:

3) Possibly to conceal base tables from some reporting packages that insist on trying to copy the whole base table to the client. Make them think that the view is the base table so that they will only try to copy the view's data to the client