fn_dblog - display the log file

fn_dblog is an undocumented SQL function that display the log file.

select * from fn_dblog(null, null)

1. The first parameter is the starting log sequence number. NULL = from the first number.
2. The second parameter is the ending log sequence number. NULL = to the last number.

• Read more about Reading Transaction Log File here:

http://blog.sqlauthority.com/2014/10/15/sql-server-beginning-reading-transaction-log-file/

Remote table-valued function calls are not allowed

Error message:

Remote table-valued function calls are not allowed

The reason:

When selects from remote tables (linked servers) - the table must be called with an alias.

--error:

SELECT ... FROM LinkedServer.DBNAME.dbo.Tablename

-- ok:

SELECT ... FROM LinkedServer.DBNAME.dbo.Tablename t

Explanation:

I don't know, and I also don't know if anyone knows.

JOIN on like comparison

declare @tbl table (aaa varchar(50))

declare @src table (aaa varchar(50))

insert into @tbl values ('aaa'), ('aaabcd'), ('bcd'), ('zzzaaa')

insert into @src values ('aaa')

-- select only equel strings (same as t.aaa = s.aaa)

select * from @tbl t join @src s on t.aaa like s.aaa

update @src set aaa = '%' + aaa + '%'

-- select all the records from t that contain s.aaa

select * from @tbl t join @src s on t.aaa like s.aaa

Incorrect syntax near ' '

select * from dbo.MyTable

where ID = 101 

Error message:

Msg 102, Level 15, State 1, Line 2

Incorrect syntax near ' '.

The case:

I wrote this simple query and copy-paste the ID number from e-mail/some chat or something like this.

Cause:

Pay atention - there is a space in the end of the query.

So? it's still not suppose to be a problem....

BUT - this is not realy a space, but "Non-breaking space".

Check it, by select the ascii code of this "Non-breaking space" (copy it from the end of the query and of a regular space:

select ASCII(' '), ASCII(' ')

Solution:

Very simple, as you already can guess - remove this ' '.

DATALENGTH is not like LEN !

Two main differences between DATALENGTH and LEN:
DATALENGTH returns the number of bytes used by an expression.

LEN returns the number of characters contained in an expression.

DATALENGTH supports expressions of any type.

LEN supports only string expressions.

declare @TestLength TABLE 
(
  char1 CHAR(10),
  nchar2 NCHAR(10),
  varchar3 VARCHAR(10),
  nvarchar4 NVARCHAR(10)
) 
INSERT INTO @TestLength VALUES('test', 'test', 'test', 'test')
INSERT INTO @TestLength VALUES('test   ', 'test   ', 'test   ', 'test   ')

SELECT
  LEN(char1) AS len1, DATALENGTH(char1) AS datalength1,
  LEN(nchar2) AS len2, DATALENGTH(nchar2) AS datalength2,
  LEN(varchar3) AS len3, DATALENGTH(varchar3) AS datalength3,
  LEN(nvarchar4) AS len4, DATALENGTH(nvarchar4) AS datalength4
FROM @TestLength

CDC on SQL Server

CDC = Change data capture

CDC is designed to capture insert, update, and delete activity applied to SQL Server tables, and to make the details of the changes available in an easily consumed relational format.

http://technet.microsoft.com/en-us/library/bb522489(v=sql.105).aspx

Check if cdc enabled:

USE [master]

GO

SELECT [name], database_id, is_cdc_enabled FROM sys.databases

GO

Enable cdc on DB:

USE [DB_NAME]

GO

EXEC sys.sp_cdc_enable_db

GO

after enable cdc:

1. New schema cdc will be created
2. New cdc system tables will be created

-- disable cdc:

EXEC sys.sp_cdc_disable_db 

GO

Check which tables of database have been enabled for cdc:

USE [DB_NAME]

GO

SELECT [name], is_tracked_by_cdc FROM sys.tables 

GO

Activate cdc on a table:

USE [DB_NAME]

GO

EXEC sys.sp_cdc_enable_table 

@source_schema = 'SCHEMA_NAME',

@source_name = 'TABLE_NAME',

@role_name = NULL,

@capture_instance = 'CDC_INSTANCE_NAME',

@supports_net_changes = 0,

@index_name = 'PK_OF_TABLE'

• after enable cdc on a table, a new system table will be created: cdc.CDC_INSTANCE_NAME_CT

For example:

EXEC sys.sp_cdc_enable_table 

@source_schema = 'dbo',

@source_name = 'TableItai',

@role_name = NULL,

@capture_instance = 'dbo_TableItai',

@supports_net_changes = 0,

@index_name = 'PK_TableItai'

-- cdc.dbo_TableItai_CT will be created

Disable cdc on a table:

USE [DB_NAME]

GO

EXECUTE sys.sp_cdc_disable_table

    @source_schema = N'SCHEMA_NAME',

    @source_name = N'TABLE_NAME',

    @capture_instance = N'CDC_INSTANCE_NAME';

GO

https://www.simple-talk.com/sql/learn-sql-server/introduction-to-change-data-capture-%28cdc%29-in-sql-server-2008/

-- enable triggers ON DATABASE
IF EXISTS (SELECT * FROM sys.triggers WHERE name = 'DDL_Audit_Trigger')
ENABLE TRIGGER DDL_Audit_Trigger ON DATABASE
GO
IF EXISTS (SELECT * FROM sys.triggers WHERE name = 'TRD_AuditObjects')
ENABLE TRIGGER TRD_AuditObjects ON DATABASE
GO
IF EXISTS (SELECT * FROM sys.triggers WHERE name = 'trg_ProceduresSign')
ENABLE TRIGGER trg_ProceduresSign ON DATABASE

GO

-- disable triggers ON DATABASE
IF EXISTS (SELECT * FROM sys.triggers WHERE name = 'DDL_Audit_Trigger')
DISABLE TRIGGER DDL_Audit_Trigger ON DATABASE
GO
IF EXISTS (SELECT * FROM sys.triggers WHERE name = 'TRD_AuditObjects')
DISABLE TRIGGER TRD_AuditObjects ON DATABASE
GO
IF EXISTS (SELECT * FROM sys.triggers WHERE name = 'trg_ProceduresSign')
DISABLE TRIGGER trg_ProceduresSign ON DATABASE

GO

Different place of casting --> different results

declare @intA int = 2, @intB int = 5

select @intA / @intB * 100.00 -- = ?

select @intA * 100.00 / @intB -- = ?

Why are the results different?

In the first select - the cast to decimal number is done after the first operation - that it int:

2/5 = 0.4 = 0 (int!) * 100.00 = 0.00 (cast the zero to decimal)

In the first select - the cast to decimal is in the first operation, so the other operations will be decimal too:

2 * 100.00 = 200.00 (decimal!) / 5 = 40.00

CTRL + R not working in SSMS 2012

The keyboard combination of CTRL+R show/hide the query results pane.

Sometimes, CTRL + R is not working in SSMS 2012.

Solution:

Go to the menu, Tools --> Options --> Environment --> Keyboard.

On the right, press the "Reset" button.

Hash data is SQL using HASHBYTES

HASHBYTES('<hash algorithm>', <string text or column>)
http://msdn.microsoft.com/en-us/library/ms174415.aspx

<algorithm>::= MD2 | MD4 | MD5 | SHA | SHA1 | SHA2_256 | SHA2_512

Hashing vs Encryption:

http://copypastenet.blogspot.co.il/2014/07/hashing-vs-encryption.html

Hashing vs Encryption

Encryption is a two way process.

Hashing is unidirectional.

In order to find the source value of hashed text, 

we need to hash all the optional strings (for example: hash a column data from a table), and to compare to the hashed string.

outer apply vs cross apply

APPLY causes the right-side query to execute once per result in the left-side query.

CROSS only considers matching rows like INNER JOIN.

OUTER considers all rows in left-side query.

http://stackoverflow.com/questions/6726696/cross-apply-vs-outer-apply-speed-difference

REVERT

REVERT - Switches the execution context back to the caller of the last EXECUTE AS statement.

SELECT SUSER_NAME(), USER_NAME(); -- sa, dbo

EXECUTE AS USER = 'myUser';

SELECT SUSER_NAME(), USER_NAME(); -- myUser, myUser

REVERT;

SELECT SUSER_NAME(), USER_NAME(); -- sa, dbo

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

sp_MSforeachdb - execute a command on all databases in the instance

EXEC sp_MSforeachdb @command

EXEC sp_MSforeachdb 'USE [?]; .....'

The command should include USE [?]; - the ? will be replace by the DBs names.

For example:

DECLARE @command varchar(500) 

SELECT @command = 'USE ? SELECT * FROM sys.objects WHERE xtype = ''U''' 

EXEC sp_MSforeachdb @command

Find executions count of SQL objects

Purpose:

To find how many times objects of some type of SQL objects were executed.

Solution:

This script refer to views - but you can change it to any other type by replacing the call from sys.views to the other relevant sys table.

; WITH QueryExecutions AS

(

SELECT

QueryText =

SUBSTRING

(

SQLTexts.text  ,

QueryStats.statement_start_offset / 2 ,

(

CASE QueryStats.statement_end_offset

WHEN -1 THEN LEN (SQLTexts.text)

ELSE QueryStats.statement_end_offset / 2

END

- QueryStats.statement_start_offset / 2

)

+ 1

) ,

ExecutionCount = QueryStats.execution_count

FROM

sys.dm_exec_query_stats AS QueryStats

CROSS APPLY

sys.dm_exec_sql_text (QueryStats.sql_handle) AS SQLTexts

)

SELECT

ViewSchema = SCHEMA_NAME (Views.schema_id) ,

ViewName = Views.name ,

UsageCount = SUM (ISNULL (QueryExecutions.ExecutionCount , 0))

FROM sys.views AS Views

LEFT OUTER JOIN

QueryExecutions

ON QueryExecutions.QueryText LIKE N'%[. ' + CHAR(9) + CHAR(10) + CHAR(13) + N'\[]' + 

Views.name + N'[ ;' + 

CHAR(9) + CHAR(10) + CHAR(13) + '\]]%' 

ESCAPE N'\' COLLATE DATABASE_DEFAULT

GROUP BY SCHEMA_NAME (Views.schema_id) , Views.name

ORDER BY UsageCount DESC, ViewSchema ASC, ViewName ASC

GO

Indexed Views does not using the index of the view

Problem:

Indexed Views does not using the index of the view...

Solution:

Add WITH (NOEXPAND) to the select (after the view):

FROM dbo.indexedViewName iv WITH (NOEXPAND)

But why the optimizer didn't use the index of the view without this hint?

If a query contains references to columns that are present both in an indexed view and base tables, it can happen....

Note: in order to use NOEXPAND, those settings must set to be ON: 

• ANSI_NULLS
• QUOTED_IDENTIFIER
• ANSI_WARNINGS
• CONCAT_NULL_YIELDS_NULL
• ANSI_PADDING
• ARITHABORT

Cannot schema bind view because name 'TableName' is invalid for schema binding. Names must be in two-part format and an object cannot reference itself.

Error message:

Cannot schema bind view 'dbo.ViewName' because name 'TableName' is invalid for schema binding. Names must be in two-part format and an object cannot reference itself.

Cause and solution:

Call tables with the schema name: dbo.TableName (not only TableName).

It's can happen in indexed views query.

Get the current date (today) at 00:00:00

DECLARE @CurrentDate datetime

SELECT @CurrentDate = DATEADD(dd, 0, DATEDIFF(dd, 0, GETDATE()))

RESTORE HEADERONLY - get the backup header information

RESTORE HEADERONLY FROM DISK = 'D:\tempItai\Itai.bak'

Returns a result set containing all the backup header information for all backup sets on a particular backup device.

This is not RESTORE - only SELECT.

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

In order to find the DB version from the integer in DatabaseVersion column:

http://blogs.msdn.com/b/pranav_rastogi/archive/2012/02/20/mapping-sql-mdf-file-versions-to-sql-product-versions.aspx

Property Owner is not available for Database

Error message:

Property Owner is not available for Database '[...]'. This property may not exist for this object, or may not be retrievable due to insufficient access rights.  (Microsoft.SqlServer.Smo)

Explanation:

For some reason, the owner of the DB was set to 'UNKNOWN'.

Check it using sp_helpdb:

Solution:

Change the DB owner to a valid login, using sp_changedbowner.

USE DBNAME

GO

sp_changedbowner 'sa'

GO

Join to the first row in other table

Join to the first row in other table - using CROSS APPLY:

SELECT aId, aInt, Adress, AbitInt 

FROM dbo.aaa

SELECT bId, DateCreated, aId

FROM dbo.bbb

ORDER BY aId, DateCreated

-- for each row in aaa - join to the earliest row in bbb

SELECT a.aId, a.aInt, a.Adress, a.AbitInt, bb.bId, bb.DateCreated

FROM dbo.aaa a

CROSS APPLY

( SELECT TOP 1 bId, DateCreated, aId

FROM dbo.bbb b

WHERE b.aId = a.aId

ORDER BY aId, DateCreated

) bb

RESOURCE_SEMAPHORE wait type

RESOURCE_SEMAPHORE:

Long explanation: RESOURCE_SEMAPHORE Occurs when a query memory request cannot be granted immediately due to other concurrent queries. High waits and wait times may indicate excessive number of concurrent queries, or excessive memory request amounts.

Short explanation: memory grant pressure.

( Understanding SQL server memory grant: http://blogs.msdn.com/b/sqlqueryprocessing/archive/2010/02/16/understanding-sql-server-memory-grant.aspx )

In order to get more data, use:

select * from sys.dm_exec_query_resource_semaphores

Returns the information about the current query-resource semaphore status.

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

• total_memory_kb - Memory held by the resource semaphore in kilobytes. check it against target_memory_kb or max_target_memory_kb values.
• waiter_count - the number of queries waiting for grants to be satisfied.

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://technet.microsoft.com/en-us/library/ms365393.aspx

SELECT cntr_value AS [Memory Grants Outstanding]
FROM sys.dm_os_performance_counters
WHERE OBJECT_NAME = 'MSSQL$MyInstance:Memory Manager'
AND counter_name = 'Memory Grants Outstanding'

SELECT OBJECT_NAME,cntr_value AS [Memory Grants Pending]
FROM sys.dm_os_performance_counters
WHERE OBJECT_NAME = 'MSSQL$MyInstance:Memory Manager'
AND counter_name = 'Memory Grants Pending'

An useful link regarding RESOURCE_SEMAPHORE:

http://www.sqlserver-dba.com/2011/06/sql-wait-type-resource-semaphore-and-how-to-reduce-it.html

Find the waiting reason of a waiting_task - sys.dm_os_waiting_tasks

SELECT *

FROM sys.dm_os_waiting_tasks

WHERE [session_id] = 71 -- or without filter on a specific session

SET NOEXEC ON

SET NOEXEC ON - it's exactly what is sound like - compiles each query but does not execute it.

Example:

DBCC OPENTRAN - display current open transactions

DBCC OPENTRAN

Display the current open/active transactions that may cause locks.

Get DBCC CHECKDB results - checkdb with tableresults

DBCC checkdb ... with tableresults

Because it's not suggested to run DBCC CHECKDB when the DB is active (in order not to overload the and not disturb users) - it should be good to run it when the DB is inactive, and it's possible to do it in a job and insert the results into a log table:

-- create the log table:

CREATE TABLE [dbo].[checkdbHistory](

[Error] [int] NULL,

[Level] [int] NULL,

[State] [int] NULL,

[MessageText] [varchar](7000) NULL,

[RepairLevel] [int] NULL,

[Status] [int] NULL,

[DbId] [int] NULL,

[Id] [int] NULL,

[IndId] [int] NULL,

[PartitionID] [int] NULL,

[AllocUnitID] [int] NULL,

[File] [int] NULL,

[Page] [int] NULL,

[Slot] [int] NULL,

[RefFile] [int] NULL,

[RefPage] [int] NULL,

[RefSlot] [int] NULL,

[Allocation] [int] NULL,

[TimeStamp] [datetime] NULL DEFAULT (GETDATE())

) ON [PRIMARY]

GO

-- this insertion csn be stored in a job:

INSERT INTO dbo.checkdbHistory 

( [Error], [Level], [State], MessageText, RepairLevel, 

[Status], [DbId], Id, IndId, PartitionId, 

AllocUnitId, [File], Page, Slot, RefFile, 

RefPage, RefSlot, Allocation)

EXEC ('dbcc checkdb(''DB_NAME'') with tableresults')

GO

In order to do this to all of the databases, run the insertion in a cursor.

http://www.mssqltips.com/sqlservertip/2325/capture-and-store-sql-server-database-integrity-history-using-dbcc-checkdb/

SET DEADLOCK_PRIORITY

SET DEADLOCK_PRIORITY - is exactly what it sounds like: Specifies the relative importance that the current session continue processing if it is deadlocked with another session.

SET DEADLOCK_PRIORITY { LOW | NORMAL | HIGH | <numeric-priority> | @deadlock_var | @deadlock_intvar }

<numeric-priority> ::= { -10 | -9 | -8 | … | 0 | … | 8 | 9 | 10 }
LOW maps to -5, NORMAL to 0, and HIGH to 5.

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

Force statistics update - WITH FULLSCAN

SQL Server automatically update statistics according to few rules. 

In order to force statistics update (even when SQL Server rules are not met) - use WITH FULLSCAN.

UPDATE STATISTICS TABLENAME WITH FULLSCAN

Note: sp_autostats doesn't force the statistics update.

Random ordering of query

SELECT .... order by NEWID()

Costs are estimated, lines thickness is actual

This one is important and confusing:

costs are estimated, lines thickness is actual!