CopyPaste.Net: August 2011

GROUPING SETS

A GROUP BY clause that uses GROUPING SETS can generate a result set equvalent to that generated by a UNION ALL of multiple simple GROUP BY clauses.

SELECT TestName, year, SUM(TestSales)FROM TestTable

GROUP BY GROUPING SETS ((TestName), (TestYear))

The results will be the same as:

SELECT NULL as TestName, TestYear, SUM(TestSales)FROM TestTable GROUP BY TestYear

UNION ALL

SELECT TestName, NULL as TestYear, SUM(TestSales)FROM TestTable GROUP BY TestName

http://msdn.microsoft.com/en-us/library/bb510427.aspx

sp_executesql ERROR: Procedure expects parameter '@parameters' of type 'ntext/nchar/nvarchar'

Error:

EXEC sp_executesql N'SELECT @x', '@x int', @x = 2

--or:

EXEC sp_executesql 'SELECT @x', N'@x int', @x = 2

This error message will be displayed:

Msg 214, Level 16, State 3, Procedure sp_executesql, Line 1

Procedure expects parameter '@parameters' of type 'ntext/nchar/nvarchar'.

Explanation:

The first and the second parameters of sp_executesql are Unicode - nvarchar/ntext (depend on the MSSQL version).

varchar is not valid!

Solutions:

--1.

EXEC sp_executesql N'SELECT @x', N'@x int', @x = 2

-- 2.

DECLARE @stmt nvarchar(max), @params nvarchar(max)

SET @stmt = 'SELECT @x' -- @stmt was declared as nvarchar, so the N is not required

SET @params = '@x int' -- @params was declared as nvarchar, so the N is not required

EXEC sp_executesql @stmt, @params, 2

QUOTENAME

QUOTENAME returns a Unicode string with the delimiters added to make the input string a valid SQL Server delimited identifier.

SELECT QUOTENAME('abcdef') --result: [abcdef]

SELECT QUOTENAME('abc-def') --result: [abc-def]

SELECT QUOTENAME('abc[]def') --result: [abc[]]def]

http://msdn.microsoft.com/en-us/library/ms176114.aspx

Slow in Application, Fast in SSMS

There is not unusual situation that query or stored procedure run very slow from the application, but run fast from the SSMS (SQL Server Management Studio).

There are some optional reasons for this situation, I'll describe in this post.

Parameter Sniffing

http://copypastenet.blogspot.com/2011/01/sql-server-parameter-sniffing.html

Different Plans for Different Settings

Different Settings between SSMS and the application create different plans.

Different Default Setting between SSMS and the application is for ARITHABORT – in Applications using ADO .Net, ODBC or OLE DB is OFF, and in SSMS is ON. This can cause different plans. In many the ARITHABORT settings is the reason for performance problems in the application.

Check settings of sessions using:

SELECT * FROM sys.dm_exec_sessions
WHERE session_id in (68,61) --SSMS session VS APP session.

http://copypastenet.blogspot.com/2011/10/get-set-options-for-current-session.html

Blocking

Blocking between statements and/or transactions from/in the applications.

Permissions on the remote database

Permissions on the remote database can be different than the permissions of the user in SSMS, and this can cause to different statistics, query plans, etc.

For simulate the application execution, try to execute in SSMS with:

EXECUTE AS USER = 'AppUser'

Bad SQL Code

Bad SQL Code is always bad.....

Plans for Dynamic SQL

Unparameterised SQL is bad for the performance:

Not good:

SELECT @sql = 'SELECT mycol FROM tbl WHERE keycol = ' + convert(varchar, @value)

EXEC(@sql)

Or in C#:

cmd.CommandText = "SELECT mycol FROM tbl WHERE keycol = " + value.ToString();

Good:

EXEC sp_executesql N'SELECT mycol FROM dbo.tbl WHERE keycol = @value',

N'@value int', @value = @value

Or in C#:

cmd.CommandText = "SELECT mycol FROM dbo.tbl WHERE keycol = @value";

cmd.Parameters.Add("@value", SqlDbType.Int);

cmd.Parameters["@value"].Value = value;

Application cache

Similar to Parameter Sniffing.

Note: Beside of my experience, in this post I also based on one of the best articles that I ever read:

http://www.sommarskog.se/query-plan-mysteries.html

NULLIF

NULLIF returns a null value if the two specified expressions are equal.

SELECT NULLIF (colA, 0)

http://msdn.microsoft.com/en-us/library/ms177562.aspx

NULLIF is good to prevent 'divide by zero' issues, by converts the column that can be zero to NULL, and than the result will be NULL and not ERROR (divide by NULL = NULL).

SQL Server Denali (2011) main features and innovations

WINDOW Functions

Window Functions are functions on subsets of rows that meet a pre-defined, user defined criteria. Any subset of user-defined records or rows is called a “window”.

SQL Server Denali takes the Window Functions to the next step by adding functionality.

Window Functions are, as examples, ROW_NUMBER(), RANK(), DENSE_RANK(), and NTILE(), OVER(), LEAD and LAG.

LEAD and LAG

Return the previous (LAG) and the next (LEAD) value

LAG (scalar_expression [,offset] [,default]) OVER ( [ partition_by_clause ] order_by_clause )

SELECT LAG(SalesQuota, 1,0) OVER (ORDER BY YEAR(QuotaDate)) AS 'LAG' FROM .....

http://msdn.microsoft.com/en-us/library/hh231256%28v=SQL.110%29.aspx

LEAD ( scalar_expression [ ,offset ] , [ default ] ) OVER ( [ partition_by_clause ] )

SELECT LEAD(SalesQuota, 1,0) OVER (ORDER BY YEAR(QuotaDate)) AS 'LEAD' FROM .....

http://msdn.microsoft.com/en-us/library/hh213125%28v=SQL.110%29.aspx

FIRST_VALUE and LAST_VALUE

Return the first or the last value in the "window".

FIRST_VALUE ( [scalar_expression ) OVER ( [ partition_by_clause ] order_by_clause [rows_range_clause ] )

http://msdn.microsoft.com/en-us/library/hh213018(v=sql.110).aspx

LAST_VALUE ( [scalar_expression ) OVER ( [ partition_by_clause ] order_by_clause rows_range_clause )

http://msdn.microsoft.com/en-us/library/hh231517(v=sql.110).aspx

More T-SQL stuff

TRY_CONVERT(), PARSE() and TRY_PARSE()

TRY_CONVERT() - Returns a value cast to the specified data type if the cast succeeds; otherwise, returns null.

TRY_CONVERT ( data_type [ ( length ) ], expression [, style ] )

SELECT TRY_CONVERT(float,'test') -- result: NULL

SELECT TRY_CONVERT(datetime2, '12/31/2010') -- result: 2010-12-31 00:00:00.0000000

PARSE() - Returns a value cast to the specified data type if the cast succeeds; otherwise, raise an error.

SELECT PARSE('10' AS TIME) -- result: ERROR!

SELECT PARSE('10' AS Float(10,2)) -- result: 10.00

TRY_PARSE() - Returns a value cast to the specified data type if the cast succeeds; otherwise, return null.

SELECT TRY_PARSE('10' AS TIME) -- result: NULL

SELECT TRY_PARSE('10' AS Float(10,2)) -- result: 10.00

FORMAT()

Returns a value formatted with the specified format and optional culture.

Mainly useful for Dynamic SQL.

FORMAT ( value, format [, culture ] )

SELECT FORMAT(@d, N'yyyy-MM')

SELECT FORMAT(@d, N'yyyy-MM-dd'),

SELECT FORMAT(@d, N'dddd, MMMM dd, yyyy', N'fr-fr')

SELECT Format(0.5,'000.0')

SELECT Format(1234,'#,###')

http://technet.microsoft.com/en-us/library/hh213505%28SQL.110%29.aspx

IIf()

IIf(Condition, value_if_true, value_if_false)

SELECT IIF(GETDATE() > '20110101', '2011', 'earlier)'

http://msdn.microsoft.com/en-us/library/hh213574(v=sql.110).aspx

Choose

Choose(index, val_1, val_2 [, val_n ])

SELECT Choose(3, 'A', 'B', 'C', 'D') -- result: 'C'

SELECT Choose(ColInt, 'one', 'two', 'three', 'four', ...) FROM TABLENAME

http://msdn.microsoft.com/en-us/library/hh213019(v=sql.110).aspx

Concat ()

Concat is similar to the operator +, with those differences:

convertion of numbers and dates without 'Cast'.
Null = '' and not make all the result to be null.

Select Concat(@S,',',object_id) From sys.objects

-- result: all the IDs from sys.objects, separated by ','.

Datetime Functions creation

DateTimeFromParts

DateTime2FromParts

DateTimeOffsetFromParts

SmallDateTimeFromParts

Functions that create datetime from divided parts.

WITH RESULT SETS

Change the names and/or data types of the returning result set of a stored procedure.

http://copypastenet.blogspot.com/2011/04/with-result-sets.html

SQL built-in Paging

http://copypastenet.blogspot.com/2010/12/sql-paging-in-sql-server-2011.html

Returns only metadata

SET FMTONLY returns only metadata to the client.

http://copypastenet.blogspot.com/2011/06/set-fmtonly.html

SEQUENCE

SEQUENCE allows you to define a central place where SQL Server will maintain a counter of values for you.

Using a sequence number in multiple tables is one of the adventages of SEQUENCE.

http://msdn.microsoft.com/en-us/library/ff878058(v=sql.110).aspx

--CREATE SEQUENCE:
CREATE SEQUENCE dbo.Seq1
AS INT -- could be any type of int
MINVALUE 1 -- default: min value of the defined type
MAXVALUE 100 -- default: max value of the defined type
START WITH 1
INCREMENT BY 1 -- or any other number
CYCLE
GO
-- note: CYCLE is not must parameter - if it chosen, when arrive to the max. vakue, the SEQUENCE will start again from the min. value.

http://msdn.microsoft.com/en-us/library/ff878091(v=sql.110).aspx

-- get new number from SEQUENCE:
SELECT NextID = NEXT VALUE FOR dbo.dbo.Seq1
http://msdn.microsoft.com/en-us/library/ff878370(v=sql.110).aspx

-- ALTER SEQUENCE:
ALTER SEQUENCE dbo.Seq1 RESTART WITH 15

Notes:

1. SEQUENCE will not provide you a transactionally consistent and gap-free stream of values. Like IDENTITY, if your transaction rolls back, the next value is still taken.

2. One of the nice adventage of SEQUENCE relatively to IDENTITY is that you can get the next ID before the insert statement:
DECLARE @NextID int ;
SET @NextID = NEXT VALUE FOR dbo.Seq1
.........
INSERT TABLENEME (ID, Name) VALUES (@NextID, 'Itai')

Column based database - Apollo Project

Column Store Index

Column Store Index is new type of index which stored columns in separate set of data pages instead of rows stored in data pages.

Column Store Index, that can improve performance, mostly for queries which are used aggregation , warehouse processing and BI application.

Column Store Index is read only.

CREATE NONCLUSTERED COLUMNSTORE INDEX MyIndex ON MyTable(col1, col2, …)

SQL Server Developer Tools, Codename "Juneau"

"Juneau" is the new SSMS for developers in Denali.

"Juneau" (SSDT) is a tool for on and off-premise database development within Visual Studio.

The Server Explorer in VS gives you an SSMS-like view of your live database objects.

SSDT also provides database developers with code navigation, IntelliSense, language support that parallels what is available for C# and VB, platform specific validation, debugging and declarative editing in the TSQL Editor, as well as a visual Table Designer for both database projects and online database instances.

http://www.microsoft.com/web/gallery/install.aspx?appid=JUNEAU10

Reporting Services - Project Crescent

"Crescent" is the new Reporting Services in Denali.

Project “Crescent” is a new interactive data exploration and visual presentation experience. It will offer a fun, visual, and powerful drag-and-drop ad hoc reporting experience. It is an web-based end-user BI tool based on Silverlight.

Demo for SQL Server Project "Crescent":

http://www.youtube.com/watch?v=75szAtMrkNs&feature=player_embedded

HADR - AlwaysOn

HADR, (or AlwaysOn, or Availability Groups)

The Always On Availability Groups feature is a high-availability and disaster-recovery solution that provides an enterprise-level alternative to database mirroring.

AlwaysOn Availability Groups maximizes the availability of a set of user databases for an enterprise.

Deploying AlwaysOn Availability Groups involves creating and configuring one or more availability groups.

http://msdn.microsoft.com/en-us/library/ff877884(SQL.110).aspx

More Useful links:

MSDN: What's New (SQL Server "Denali")

http://msdn.microsoft.com/en-us/library/bb500435(v=sql.110).aspx

Download the SQL Server Denali Community Technical Preview 3 (CTP3) Product Guide:

http://www.microsoft.com/download/en/details.aspx?id=27069

SQL Server code name Denali Links:

http://copypastenet.blogspot.com/2011/06/sql-server-code-name-denali-home-page.html

Script of DB backup Job creation

-- 1. replace all 'DataBaseName' to your DB Name.

-- 2. replace DomainName\master to your user name.

-- 3. execute on master DB.

BEGIN TRANSACTION

DECLARE @ReturnCode INT

SELECT @ReturnCode = 0

IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]' AND category_class=1)

BEGIN

EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Uncategorized (Local)]'

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

END

DECLARE @jobId BINARY(16)

EXEC @ReturnCode = msdb.dbo.sp_add_job @job_name=N'DataBaseNameBackup',

@enabled=1,

@notify_level_eventlog=0,

@notify_level_email=0,

@notify_level_netsend=0,

@notify_level_page=0,

@delete_level=0,

@description=N'DataBaseName Backup',

@category_name=N'[Uncategorized (Local)]',

@owner_login_name=N'DomainName\master', @job_id = @jobId OUTPUT

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'DataBaseNameBackup',

@step_id=1,

@cmdexec_success_code=0,

@on_success_action=1,

@on_success_step_id=0,

@on_fail_action=2,

@on_fail_step_id=0,

@retry_attempts=0,

@retry_interval=0,

@os_run_priority=0, @subsystem=N'TSQL',

@command=N'BACKUP DATABASE [DataBaseName] TO DISK = N''D:\Backup\DataBaseName.Bak'' WITH INIT , NOUNLOAD , NAME = N''DataBaseName Backup'', SKIP , STATS = 10, NOFORMAT',

@database_name=N'master',

@flags=0

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'DataBaseNameBackup',

@enabled=1,

@freq_type=4,

@freq_interval=1,

@freq_subday_type=1,

@freq_subday_interval=0,

@freq_relative_interval=0,

@freq_recurrence_factor=0,

@active_start_date=20110201,

@active_end_date=99991231,

@active_start_time=30000,

@active_end_time=235959,

@schedule_uid=N'47e12b03-58e7-4103-b196-f6f812f34a79'

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

COMMIT TRANSACTION

GOTO EndSave

QuitWithRollback:

IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION

EndSave:

WITH CHECK OPTION

'WITH CHECK OPTION' Forces all data modification statements executed against the view to follow the criteria set within select_statement.

When a row is modified through a view, the WITH CHECK OPTION makes sure the data remains visible through the view after the modification is committed.

Example:

CREATE VIEW CheckOptionTest

SELECT [StamTableId], [StamTableStr], [colIntA]

FROM StamTable

WHERE [colIntA] > 5

-- select #1:

select * from CheckOptionTest

-- update #1:

update CheckOptionTest set [colIntA] = [colIntA] - 2

-- select #2:

select * from CheckOptionTest

-- less rows than select #1 (because of the update)

ALTER VIEW CheckOptionTest

SELECT [StamTableId], [StamTableStr], [colIntA]

FROM StamTable

WHERE [colIntA] > 5

WITH CHECK OPTION

-- update #2:

update CheckOptionTest set [colIntA] = [colIntA] - 2

-- in case of rows that colIntA is not bigger from 5 in more than 2:

-- update will be failed: can't update rows from the view with values that don't follow the criteria set within the select statement.

http://msdn.microsoft.com/en-us/library/ms187956.aspx

@@TRANCOUNT - TRANSACTIONs count

@@TRANCOUNT returns the number of BEGIN TRANSACTION statements that have occurred on the current connection.

SELECT @@TRANCOUNT

http://msdn.microsoft.com/en-us/library/ms187967.aspx

Plan guide for associating query

sp_create_plan_guide creates a plan guide for associating query hints or actual query plans with queries in a database.

EXEC sp_create_plan_guide

@name = N'Guide1',

@stmt = N'SELECT *

FROM Sales.SalesOrderHeader AS h

INNER JOIN Sales.Customer AS c

ON h.CustomerID = c.CustomerID

INNER JOIN Sales.SalesTerritory AS t

ON c.TerritoryID = t.TerritoryID

WHERE t.CountryRegionCode = @Country_region',

@type = N'OBJECT',

@module_or_batch = N'Sales.GetSalesOrderByCountry',

@params = NULL,

@hints = N'OPTION (OPTIMIZE FOR (@Country_region = N''US''))';

EXEC sp_create_plan_guide

@name = N'Guide1',

@stmt = N'SELECT TOP 1 *

FROM Sales.SalesOrderHeader

ORDER BY OrderDate DESC',

@type = N'SQL',

@module_or_batch = NULL,

@params = NULL,

@hints = N'OPTION (MAXDOP 1)';

http://msdn.microsoft.com/en-us/library/ms179880.aspx

sp_control_plan_guide drops, enables, or disables a plan guide.

EXEC sp_control_plan_guide N'DROP', N'Guide1'

http://msdn.microsoft.com/en-us/library/ms188733.aspx

Does my database contain edition-specific features?

sys.dm_db_persisted_sku_features is used to list all edition-specific features that are enabled in the current database.

SELECT * FROM sys.dm_db_persisted_sku_features

http://msdn.microsoft.com/en-us/library/cc280724.aspx

sp_MsForEachDB

sp_MsForEachDB perform an action on all of the databases.

exec sp_MsForEachDB @command1, @replacechar, @command2, @command3, @whereand, @precommand, @postcommand

@command1 - the command to be executed by sp_MsForEachDB.
@replacechar - a character in the command string that will be replaced with the database name being processed (default '?').
@command2 and @command3 are two additional optional commands that can be run for each database (@command2 will be run after @command1, @command3 will be run after @command2).
@whereand - an additional constraints to help identify the rows in the sysobjects database that will be selected.
@precommand - a command to be run prior to processing any database.
@postcommand - a command to be run after all commands have been processed against all databases.

Examples:

declare @cmd1 varchar(500)

declare @cmd2 varchar(500)

declare @cmd3 varchar(500)

set @cmd1 = 'if ''?'' <> ''tempdb'' print ''*** Processing DB ? ***'''

set @cmd2 = 'if ''?'' <> ''tempdb'' backup database ? to disk=''c:\temp\?.bak'''

set @cmd3 = 'if ''?'' <> ''tempdb'' dbcc checkdb(?)'

exec sp_MSforeachdb @command1=@cmd1,

@command2=@cmd2,

@command3=@cmd3

EXEC sp_MSforeachdb 'USE ?; EXEC sp_spaceused'

sp_MSforeachtable

sp_MSforeachtable perform an action on all of the tables within a database.

exec sp_MSforeachtable @command1, @replacechar, @command2, @command3, @whereand, @precommand, @postcommand

@command1 - the command to be executed by sp_MSforeachtable.
@replacechar - a character in the command string that will be replaced with the table name being processed (default '?').
@command2 and @command3 are two additional optional commands that can be run for each table (@command2 will be run after @command1, @command3 will be run after @command2).
@whereand - an additional constraints to help identify the rows in the sysobjects table that will be selected.
@precommand - a command to be run prior to processing any table
@postcommand - a command to be run after all commands have been processed against all tables

Examples:
declare @cmd1 varchar(500)
set @cmd1 = 'exec sp_Help "?"'
exec sp_MSforeachtable @command1=@cmd1
GO

EXEC sp_MSforeachtable @command1="print '*'; DBCC DBREINDEX ('*', ' ', 80)" ,@replacechar='*'
GO

RANK OVER, DENSE_RANK OVER

--Returns the rank of a result set:

SELECT StamTableId, StamTableStr, colIntA,

RANK() OVER (ORDER BY colIntA) as Rank_over

FROM StamTable

--Returns the rank of rows of a result set, without any gaps in the ranking:

SELECT StamTableId, StamTableStr, colIntA,

DENSE_RANK() OVER (ORDER BY colIntA) as Rank_over

FROM StamTable

--Returns the rank of rows within the partition of a result set, without any gaps in the ranking:

SELECT StamTableId, StamTableStr, colIntA,

RANK() OVER (PARTITION BY StamTableStr ORDER BY StamTableStr, colIntA) as Rank_over

FROM StamTable

RANK():

http://msdn.microsoft.com/en-us/library/ms176102.aspx

DENSE_RANK():

http://msdn.microsoft.com/en-us/library/ms173825.aspx

Get the output of INSERT, DELETE, and UPDATE statements

DELETE

FROM StamTable2

OUTPUT deleted.*

WHERE StamTableId > 11

INSERT INTO StamTable2 (StamTableStr, StamTableStr2)

OUTPUT inserted.*

SELECT StamTableStr, StamTableStr FROM StamTable WHERE StamTableId > 11

UPDATE StamTable2

SET StamTableStr2 = StamTableStr2 + '_UPDATED'

OUTPUT deleted.*, inserted.*

WHERE StamTableId > 11

Note: This OUTPUT doesn't work when there are 'instead of' triggers.

User can not connect remotely to SQL Server

Problem:

An user can not connect remotely to SQL Server, but he can connect in the server machine.

Solution:

Check that TCP/IP is enabled in SQL Server Configuration Manager --> SQL Server Network Configuration --> Protocols for MSSQLSERVER:

sp_change_users_login

sp_change_users_login @Action='update_one', @UserNamePattern='itai', @LoginName='itai'

Maps an existing database user to a SQL Server login.

@Action can be:

Auto_Fix - Links a user entry in the sys.database_principals system catalog view in the current database to a SQL Server login of the same name.
Report - Lists the users and corresponding security identifiers (SID) in the current database that are not linked to any login.
Update_One - Links the specified user in the current database to an existing SQL Server login.

http://msdn.microsoft.com/en-us/library/ms174378(v=sql.90).aspx

Change The Logical Name of SQL Server DB file

ALTER DATABASE DBName

MODIFY FILE (NAME=N'Current_Logic_Name', NEWNAME=N'New_Logic_Name')

Get UTC datetime in MSSQL

-- UTC datetime:

SELECT GETUTCDATE()

-- UTC datetime (long datetime, like SYSDATETIME):

SELECT SYSUTCDATETIME()

Note: in order to get the time zone offset:

http://copypastenet.blogspot.com/2011/08/get-time-zone-offset-in-mssql.html

Get the time zone offset in MSSQL

SELECT SYSDATETIMEOFFSET()

-- return datetime + time zone offset.

Delete multiple objects in one statement

DROP TABLE StamTableItai1, StamTableItai2

DROP PROCEDURE SP1,SP2

DROP FUNCTION F1,F2

sys.dm_db_index_usage_stats - indexes usage

SELECT * FROM sys.dm_db_index_usage_stats

Returns counts of different types of index operations and the time each type of operation was last performed.

http://msdn.microsoft.com/en-us/library/ms188755.aspx

xp_ReadErrorLog - Get records from the SQL Server log

xp_ReadErrorLog [number of the log file], [SQL error logs/Agent logs], [search string 1], [search string 2]

Parameter 1 (int), is the number of the log file you want to read. The default value is 0 for the current log.
Parameter 2 (int), value of 1 reads SQL error logs, value of 2 reads SQL Server Agent logs. The default value is 1.
Parameters 3 and 4 (varchar (255)), are search strings for the log entry. The default value is NULL.

Note for parameter 1: To get the list of archive files, use xp_enumerrorlogs:

http://copypastenet.blogspot.com/2011/08/exec-xpenumerrorlogs-list-of-archive.html

xp_enumerrorlogs - list of archive files

EXEC xp_enumerrorlogs

xp_enumerrorlogs returns a list of archive files with files sizes and dates.

The active file is archive #0.

DB objects code encryption - WITH ENCRYPTION

Purpose:

We want that the users can execute the objects but can't view the code.

Solution:

CREATE Procedure .... WITH ENCRYPTION .....

Example:

CREATE Procedure [dbo].[GetStamTable]

Select * from dbo.StamTable

CREATE Procedure [dbo].[GetStamTableWithEncrypt]

WITH ENCRYPTION

Select * from dbo.StamTable

exec [GetStamTable]

exec [GetStamTableWithEncrypt]

-- same results

sp_helptext [GetStamTable]

-- we will get the SP code

sp_helptext [GetStamTableWithEncrypt]

--we will get an error message: The text for object 'GetStamTableWithEncrypt' is encrypted.

Notes:

1. Pay attention to save the code in order to alter it.....

2. This definition of encryption also affects the profiler, and can make it harder to work with it.

SELECT without FROM clause

SELECT 'Test1' WHERE EXISTS (SELECT 1/0)

SELECT 'Test2' WHERE EXISTS (SELECT *)

Those 2 statements won't raise an Error in SQL SERVER!

Te results will be:

Test1

Test2

Of course that SELECT 1/0 or SELECT * will raise an Error.

In SQL SERVER the FROM clause is optional.

This is why SELECT 1 , SELECT @P and SELECT @P=5 are permitted.

In other database systems (Like Oracle), SELECT statement without FROM clause is not permitted, and there is a dummy table "DUAL" with one row that is used to do such SELECT statements:

SELECT 1 FROM dual , SELECT @v FROM dual.

Another thing is that in EXISTS clause doesn't matter in terms of the syntax or result of the query.

Those 2 facts (SELECT without FROM is allowed and EXISTS doesn't matter in terms of the syntax)

explains the behavior of the 2 statements above.

https://connect.microsoft.com/SQLServer/feedback/details/671475/select-test-where-exists-select#details

SHOWPLAN permission denied in database

Error message:

SHOWPLAN permission denied in database 'DBNAME'

Solution:

Grant permission of SHOWPLAN to the user:

USE DBNAME

GRANT SHOWPLAN TO USERNAME

SQL SERVER error messages - sys.messages

SELECT * FROM sys.messages

sys.messages return the list of the error messages in the system

http://msdn.microsoft.com/en-us/library/ms187382.aspx

List of the severities of the errors:

http://msdn.microsoft.com/en-us/library/ms164086.aspx

dm_exec_query_memory_grants

SELECT * FROM Sys.dm_exec_query_memory_grants

Returns information about the queries that have acquired a memory grant or that still require a memory grant to execute.

http://msdn.microsoft.com/en-us/library/ms365393.aspx

VALUES - Aggregate Over Columns from the same row

-- get the max value per row of few columns of a table

SELECT StamTableStr, (SELECT MAX(val) FROM (VALUES(colIntA),(colIntB)) AS D(val)) AS mx FROM dbo.StamTable

-- Summarize the value of few columns of a table per row

SELECT StamTableStr, (SELECT SUM(val) FROM (VALUES(colIntA),(colIntB)) AS D(val)) AS sm FROM dbo.StamTable

sys.dm_os_sys_memory

SELECT * FROM sys.dm_os_sys_memory

Returns a complete picture of memory at the operating system level, including information about total and available physical memory, total and available page memory, system cache, kernel space and so forth.

http://msdn.microsoft.com/en-us/library/bb510493.aspx

Auditing in SQL Server 2008

Auditing an instance of SQL Server or a SQL Server database involves tracking and logging events that occur on the system.

Auditing declaration and executions steps:

Create a SQL Server Audit object.
Create an Audit Specification (can be at the server or database level).
Turn it on.

-- Create a SQL Server Audit object

CREATE SERVER AUDIT [ItaiAudit]

TO FILE

( FILEPATH = N'D:\FolderA\'

,MAXSIZE = 100 MB

,MAX_ROLLOVER_FILES = 2147483647

,RESERVE_DISK_SPACE = OFF

)

WITH

( QUEUE_DELAY = 1000

,ON_FAILURE = CONTINUE

)

-- Create an Audit Specification (database level)

CREATE DATABASE AUDIT SPECIFICATION [ItaiAuditSpecification]

FOR SERVER AUDIT [ItaiAudit]

ADD (UPDATE ON OBJECT::[dbo].[StamTable] BY [dbo]),

ADD (SELECT ON OBJECT::[dbo].[StamTable2] BY [dbo])

WITH (STATE = OFF)

GO

-- Another example: Create an Audit Specification to get logins (server level)
CREATE SERVER AUDIT SPECIFICATION [AuditSpec_itai_RG]
FOR SERVER AUDIT [Audit_itai_RG]
ADD (FAILED_LOGIN_GROUP),
ADD (SUCCESSFUL_LOGIN_GROUP)
WITH (STATE = ON)
GO

-- Turn it on

ALTER SERVER AUDIT ItaiAudit WITH (STATE = ON)

-- Get auditing data from the file:
SELECT *
FROM fn_get_audit_file('D:\FolderA\Audit*.sqlaudit',default, default) a

Note: Auditing is a SQL 2008 Enterprise version only feature.

msdn: Auditing in SQL Server 2008

http://msdn.microsoft.com/en-us/library/dd392015(v=sql.100).aspx

msdn: Understanding SQL Server Audit

http://msdn.microsoft.com/en-us/library/cc280386(SQL.100).aspx

DB Full Text Catalog deletion

2 steps in order to delete Full Text Catalog:

1. Drop the full-text indexes.

2. Drop the full-text catalog.

In Order to delete all the full-text catalogs of the DB, Run this script:

-- 1. drop fulltext indexes

DECLARE @ExecAlter nvarchar(1000), @ExecDrop nvarchar(1000)

DECLARE CursorFtINDEX CURSOR FORWARD_ONLY FOR

-------------- Start Select --------------------------

SELECT ('ALTER FULLTEXT INDEX ON ' + SCHEMA_NAME(o.schema_id) + '.' + o.name + ' DISABLE'),

('DROP FULLTEXT INDEX ON ' + SCHEMA_NAME(o.schema_id) + '.' + o.name)

FROM sys.fulltext_indexes fti

join sys.objects o ON fti.object_id = o.object_id

order by fti.fulltext_catalog_id

-------------- End Select ----------------------------

OPEN CursorFtINDEX

FETCH NEXT FROM CursorFtINDEX

INTO @ExecAlter, @ExecDrop

WHILE (@@FETCH_STATUS = 0)

BEGIN

exec (@ExecAlter)

exec (@ExecDrop)

--Get next record from cursor

FETCH NEXT FROM CursorFtINDEX

INTO @ExecAlter, @ExecDrop

END

CLOSE CursorFtINDEX

DEALLOCATE CursorFtINDEX

-- 2. drop full text catalogs

DECLARE CursorFT CURSOR FORWARD_ONLY FOR

-------------- Start Select --------------------------

SELECT ('DROP FULLTEXT CATALOG ' + name)

FROM sysfulltextcatalogs

-------------- End Select ----------------------------

OPEN CursorFT

FETCH NEXT FROM CursorFT

INTO @ExecDrop

WHILE (@@FETCH_STATUS = 0)

BEGIN

exec (@ExecDrop)

--Get next record from cursor

FETCH NEXT FROM CursorFT

INTO @ExecDrop

END

CLOSE CursorFT

DEALLOCATE CursorFT

sys.dm_os_windows_info

Returns one row that displays Windows operating system version information.

SELECT * FROM sys.dm_os_windows_info

http://technet.microsoft.com/en-us/library/hh204565.aspx

Note: Available from MSSQL 2008 R2 SP1.

sys.dm_server_services

Returns information about the SQL Server and SQL Server Agent services in the current instance of SQL Server.

SELECT * FROM sys.dm_server_services

http://technet.microsoft.com/en-us/library/hh204542.aspx

Note: Available from MSSQL 2008 R2 SP1.

sys.dm_server_registry

Returns configuration and installation information that is stored in the Windows registry for the current instance of SQL Server

SELECT * FROM sys.dm_server_registry

http://msdn.microsoft.com/en-us/library/hh204561.aspx

Note: Available from MSSQL 2008 R2 SP1.

sys.dm_os_volume_stats

Returns information about the operating system volume

SELECT * FROM sys.dm_os_volumne_stats (database_id, file_id)

-- get data for all of the DBs:

SELECT ...

FROM sys.master_files f

CROSS APPLY sys.dm_os_volume_stats(f.database_id, f.file_id) vs

http://msdn.microsoft.com/en-us/library/hh223223.aspx

Note: Available from MSSQL 2008 R2 SP1.

FORCESEEK and FORCESCAN

FORCESEEK

FORCESEEK [ (index_value(index_column_name [ ,... n ] )) ]

Use the FORCESEEK hint to Specify the query optimizer use only an index seek operation.

SELECT ..... FROM Sales.SalesOrderHeader h

INNER JOIN Sales.SalesOrderDetail AS d WITH (FORCESEEK)

ON h.SalesOrderID = d.SalesOrderID .......

SELECT ..... FROM Sales.SalesOrderHeader h

INNER JOIN Sales.SalesOrderDetail AS d WITH (FORCESEEK (INDEX_NAME ((COLUMN_NAME)))

ON h.SalesOrderID = d.SalesOrderID .......

FORCESCAN

Use the FORCESCAN hint to force the query optimizer to perform a scan operation

SELECT ..... FROM Sales.SalesOrderHeader h

INNER JOIN Sales.SalesOrderDetail AS d WITH (FORCESCAN)

ON h.SalesOrderID = d.SalesOrderID .......

http://msdn.microsoft.com/en-us/library/ms187373.aspx

Labels