Ms Sql Server Technology Discussions

[deals2buy]

[b] Primary Key Constraints and Unique Key Constraints[/b]

[b]Primary Key:[/b]

Primary Key enforces uniqueness of the column on which they are defined. Primary Key creates a clustered index on the column. Primary Key does not allow Nulls.

[i]Create table with Primary Key:[/i]

[CODE]
CREATE TABLE Authors (
AuthorID INT NOT NULL PRIMARY KEY,
Name VARCHAR(100) NOT NULL
)
GO
[/CODE]

[i]Alter table with Primary Key:[/i]

[CODE]
ALTER TABLE Authors
ADD CONSTRAINT pk_authors PRIMARY KEY (AuthorID)
GO
[/CODE]

[b]Unique Key:[/b]

Unique Key enforces uniqueness of the column on which they are defined. Unique Key creates a non-clustered index on the column. Unique Key allows only one NULL Value.

[i]Alter table to add unique constraint to column:[/i]

[CODE]
ALTER TABLE Authors ADD CONSTRAINT IX_Authors_Name UNIQUE(Name)
GO
[/CODE]

[deals2buy]

[b] Simple Example of WHILE Loop with BREAK and CONTINUE[/b]

WHILE statement sets a condition for the repeated execution of an SQL statement or statement block. Following is very simple example of WHILE Loop with BREAK and CONTINUE.

[CODE]
USE AdventureWorks;
GO
DECLARE @Flag INT
SET @Flag = 1
WHILE (@Flag < 10)
BEGIN
BEGIN
PRINT @Flag
SET @Flag = @Flag + 1
END
IF(@Flag > 5)
BREAK
ELSE
CONTINUE
END
[/CODE]

WHILE loop can use SELECT queries as well. You can find following example of [url="http://msdn2.microsoft.com/en-us/library/ms178642.aspx"]BOL[/url] very useful.

[CODE]
USE AdventureWorks;
GO
WHILE (
SELECT AVG(ListPrice)
FROM Production.Product) < $300
BEGIN
UPDATE Production.Product
SET ListPrice = ListPrice * 2
SELECT MAX(ListPrice)
FROM Production.Product
IF (
SELECT MAX(ListPrice)
FROM Production.Product) > $500
BREAK
ELSE
CONTINUE
END
PRINT 'Too much for the market to bear';
[/CODE]

[deals2buy]

[b] Import CSV File Into SQL Server Using Bulk Insert – Load Comma Delimited File Into SQL Server[/b]

How to import CSV file into SQL Server? How to load CSV file into SQL Server Database Table? How to load comma delimited file into SQL Server? Let us see the solution in quick steps.

CSV stands for Comma Separated Values, sometimes also called Comma Delimited Values.

Create TestTable

[CODE]
USE TestData
GO
CREATE TABLE CSVTest
(ID INT,
FirstName VARCHAR(40),
LastName VARCHAR(40),
BirthDate SMALLDATETIME)
GO
[/CODE]

Create CSV file in drive C: with name csvtest.txt with following content. The location of the file is C:\csvtest.txt
1,James,Smith,19750101
2,Meggie,Smith,19790122
3,Robert,Smith,20071101
4,Alex,Smith,20040202

[img]http://www.pinaldave.com/bimg/csv1.gif[/img]

Now run following script to load all the data from CSV to database table. If there is any error in any row it will be not inserted but other rows will be inserted.

[CODE]
BULK
INSERT CSVTest
FROM 'c:\csvtest.txt'
WITH
(
FIELDTERMINATOR = ',',
ROWTERMINATOR = '\n'
)
GO
--Check the content of the table.
SELECT *
FROM CSVTest
GO
--Drop the table to clean up database.
DROP TABLE CSVTest
GO

[/CODE]

[img]http://www.pinaldave.com/bimg/csv2.gif[/img]

[deals2buy]

[b] Observation – Effect of Clustered Index over Nonclustered Index[/b]

Let us run the example first. Make sure to to enable Execution Plan (Using CTRL + M) before running comparison queries.

[CODE]
USE [AdventureWorks]
GO
/* */
CREATE TABLE [dbo].[MyTable](
[ID] [int] NOT NULL,
[First] [nchar](10) NULL,
[Second] [nchar](10) NULL
) ON [PRIMARY]
GO
/* Create Sample Table */
INSERT INTO [AdventureWorks].[dbo].[MyTable]
([ID],[First],[Second])
SELECT 1,'First1','Second1'
UNION ALL
SELECT 2,'First2','Second2'
UNION ALL
SELECT 3,'First3','Second3'
UNION ALL
SELECT 4,'First4','Second4'
UNION ALL
SELECT 5,'First5','Second5'
GO
[/CODE]

Now let us create nonclustered index over this table.

[CODE]
/* Create Nonclustered Index over Table */
CREATE NONCLUSTERED INDEX [IX_MyTable_NonClustered]
ON [dbo].[MyTable]
(
[First] ASC,
[Second] ASC
) ON [PRIMARY]
GO
[/CODE]

Run following two queries together.

[CODE]
/* Run following two queries together and observe the
result in by Enabling Actual Execution Plan (CTRL + M)
1st Query will use Table Scan
2nd Query will use Index Seek
*/
SELECT ID
FROM [MyTable]
WHERE First = 'First1' AND Second = 'Second1'
SELECT Second
FROM [MyTable]
WHERE First = 'First1' AND Second = 'Second1'
GO

[/CODE]

[img]http://www.pinaldave.com/bimg/nic.gif[/img]

It is clear from query that index applies to columns on which it is created. In our case as in WHERE condition we have same columns which are used in Index.

Now create Clustered Index over the same table.

[CODE]
/* Create Clustered Index over Table */
CREATE CLUSTERED INDEX [IX_MyTable_Clustered]
ON [dbo].[MyTable]
(
[ID] ASC
) ON [PRIMARY]
GO
[/CODE]

Once again run above two same query and see the execution plan.

[CODE]
/* Run following two queries together and observe the
result in 1st Query will use Index Seek
2nd Query will use Index Seek
*/
SELECT ID
FROM [MyTable]
WHERE First = 'First1' AND Second = 'Second1'
SELECT Second
FROM [MyTable]
WHERE First = 'First1' AND Second = 'Second1'
GO
[/CODE]

[img]http://www.pinaldave.com/bimg/nic1.gif[/img]

Clean up the database by running following script.

[CODE]
/* Clean up */
DROP TABLE [dbo].[MyTable]
GO
[/CODE]

Interesting part of above execution plan is now both queries are using nonclustered index scan. Logically first query should have not used index which is for second query as it was retrieving the column which was not in the nonclustered index. However, it did used the nonclustered index and only difference between our first execution and second execution is that we have created clustered index over the column which is retrieved in the first query.

The question is : [b]The question is why this has happened?[/b]

In summary : A query which is not using nonclustered index to retrieve results used nonclustered index when clustered index created on the column which is retrieved.

The reason for this happening is that every nonclustered index refers to clustered index internally. When clustered index is created on table it reorganizes the table in the physical order of the clustered index. When there is no clustered index created on table at that time all nonclustered index points to data in the table to retrieve the data, however once clustered index is created all the nonclustered indexes are reorganized and they point to clustered index. This effect is creating index seek operation on nonclustered index in our case as column on which clustered index is created is in SELECT clause and WHERE clause contains columns which are used in nonclustered index.

[deals2buy]

[b] Quickest Way to – Kill All Threads – Kill All User Session – Kill All Processes[/b]

[CODE]
USE master;
GO
ALTER DATABASE AdventureWorks
SET SINGLE_USER
WITH ROLLBACK IMMEDIATE;
ALTER DATABASE AdventureWorks
SET MULTI_USER;
GO
[/CODE]

Running above script will give following result.

[i]Nonqualified transactions are being rolled back. Estimated rollback completion: 100%.[/i]

[deals2buy]

[b] Cursor to Kill All Process in Database[/b]

When you run the script please make sure that you run it in different database then the one you want all the processes to be killed.

[CODE]
CREATE TABLE #TmpWho
(spid INT, ecid INT, status VARCHAR(150), loginame VARCHAR(150),
hostname VARCHAR(150), blk INT, dbname VARCHAR(150), cmd VARCHAR(150))
INSERT INTO #TmpWho
EXEC sp_who
DECLARE @spid INT
DECLARE @tString VARCHAR(15)
DECLARE @getspid CURSOR
SET @getspid = CURSOR FOR
SELECT spid
FROM #TmpWho
WHERE dbname = 'mydb'OPEN @getspid
FETCH NEXT FROM @getspid INTO @spid
WHILE @@FETCH_STATUS = 0
BEGIN
SET @tString = 'KILL ' + CAST(@spid AS VARCHAR(5))
EXEC(@tString)
FETCH NEXT FROM @getspid INTO @spid
END
CLOSE @getspid
DEALLOCATE @getspid
DROP TABLE #TmpWho
GO
[/CODE]

[deals2buy]

[b] Introduction to Force Index Query Hints – Index Hint[/b]

[i]“SQL Server query optimizer selects the best execution plan for a query, it is recommended to use query hints by experienced developers and database administrators in case of special circumstances.”[/i]

When any query is ran SQL Server Engine determines which index has to be used. SQL Server makes uses Index which has lowest cost based on performance. Index which is the best for performance is automatically used. There are some instances when Database Developer is best judge of the index used. DBA can direct SQL Server which index to be used to execute query.

[b]Example 1 : SQL Server using default index[/b]

[CODE]
USE AdventureWorks
GO
SELECT *
FROM Person.Contact
GO
[/CODE]

[color=black][img]http://www.pinaldave.com/bimg/indexhint1.gif[/img][/color]
[b]Example 2: SQL Server using forced index[/b]

[CODE]
USE AdventureWorks
GO
SELECT ContactID
FROM Person.Contact WITH (INDEX(AK_Contact_rowguid))
GO
[/CODE]

[color=black][img]http://www.pinaldave.com/bimg/indexhint2.gif[/img][/color]

[b]Example 3: SQL Server using different index for different/same tables[/b]

[CODE]
USE AdventureWorks
GO
SELECT c.ContactID
FROM Person.Contact c
WITH (INDEX(AK_Contact_rowguid))
INNER JOIN Person.Contact pc
WITH (INDEX(PK_Contact_ContactID))
ON c.ContactID = pc.ContactID
GO

[/CODE]

[color=black][img]http://www.pinaldave.com/bimg/indexhint3.gif[/img][/color]

[deals2buy]

[b] Introduction to Force Index Query Hints – Index Hint – Part2[/b]

There is alternate way to do the same using OPTION clause however, as OPTION clause is specified at the end of the query we have to specify which table the index hint is put on.

[b]Example 1: Using Inline Query Hint[/b]

[CODE]
USE AdventureWorks
GO
SELECT c.ContactID
FROM Person.Contact c
WITH (INDEX(AK_Contact_rowguid))
INNER JOIN Person.Contact pc
WITH (INDEX(PK_Contact_ContactID))
ON c.ContactID = pc.ContactID
GO
[/CODE]

[b]Example 2: Using OPTION clause[/b]

[CODE]
USE AdventureWorks
GO
SELECT c.ContactID
FROM Person.Contact c
INNER JOIN Person.Contact pc
ON c.ContactID = pc.ContactID
OPTION (TABLE HINT(c, INDEX (AK_Contact_rowguid)),
TABLE HINT(pc, INDEX (PK_Contact_ContactID)))
GO
[/CODE]

[deals2buy]

[b] SQL Collation and related performance impact, viewing collation in query plans[/b]


Collation settings exist at the Server level, the Database Level and potentially defined at the column level as well. By default if no collation is specified at the column level when creating the table, database collation is assumed

To check the collation of a database:

[CODE]

Select DATABASEPROPERTYEX('TEMPDB','COLLATION')
[/CODE]

[url="http://blogs.msdn.com/cfs-file.ashx/__key/communityserver-blogs-components-weblogfiles/00-00-01-24-03-metablogapi/4377.clip_5F00_image001_5F00_4DB122BB.png"][img]http://blogs.msdn.com/cfs-file.ashx/__key/communityserver-blogs-components-weblogfiles/00-00-01-24-03-metablogapi/3113.clip_5F00_image001_5F00_thumb_5F00_54642C3E.png[/img][/url]

And further to see the collation of a column, we can use the query below

[CODE]
select object_name(object_id) as ObjectName,name As ColName,collation_name
from sys.columns where object_id = object_id('testcollate')
[/CODE]

[url="http://blogs.msdn.com/cfs-file.ashx/__key/communityserver-blogs-components-weblogfiles/00-00-01-24-03-metablogapi/2630.clip_5F00_image0026_5F00_16D6CE75.png"][img]http://blogs.msdn.com/cfs-file.ashx/__key/communityserver-blogs-components-weblogfiles/00-00-01-24-03-metablogapi/7418.clip_5F00_image0026_5F00_thumb_5F00_048E07B3.png[/img][/url]


Now moving on to more of the Performance aspects of the question:
[list]
[*]Same Collation comparison – If the literal or columns being compared are the same collection, we have no problem as we can see below
[/list]
[CODE]
set nocount on
use tempdb
go
drop table testcollate
go
create table testcollate( myid int identity, myname varchar(810))
go
insert into testcollate values(replicate('a',800))
go 10000
insert into testcollate values('Denzil')
go
create index myind on testcollate(myname)
go
set statistics io on
go
select myname from testcollate where myname = 'Denzil'
[/CODE]

[url="http://blogs.msdn.com/cfs-file.ashx/__key/communityserver-blogs-components-weblogfiles/00-00-01-24-03-metablogapi/2046.clip_5F00_image001_5F00_320F2776.png"][img]http://blogs.msdn.com/cfs-file.ashx/__key/communityserver-blogs-components-weblogfiles/00-00-01-24-03-metablogapi/2627.clip_5F00_image001_5F00_thumb_5F00_1F5A2DBF.png[/img][/url]


Table 'testcollate'. Scan count 1, logical reads 5, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

b. If the literal being compared has a different collation or is explicitly converted to a different collation, collation precedence kicks in- [url="http://msdn.microsoft.com/en-US/library/ms179886%28v=SQL.90%29.aspx"]http://msdn.microsoft.com/en-US/library/ms179886(v=SQL.90).aspx[/url]

If the literal has an explicit collation, we will get a plan with a CONVERT/SCAN. The CONVERT_IMPLICIT by itself gives no indication this is due to a Collation difference per say, in fact almost looks like it could be some data type mismatch which it is not and on the constant side, there is a CONVERT given that we were explicitly collating it to a particular collation.
[CODE]
select myname from testcollate
where myname = 'Denzil' collate SQL_Latin1_General_Cp437_CS_AS
[/CODE]



[url="http://blogs.msdn.com/cfs-file.ashx/__key/communityserver-blogs-components-weblogfiles/00-00-01-24-03-metablogapi/6036.clip_5F00_image002_5F00_73A963C2.png"][img]http://blogs.msdn.com/cfs-file.ashx/__key/communityserver-blogs-components-weblogfiles/00-00-01-24-03-metablogapi/4377.clip_5F00_image002_5F00_thumb_5F00_0A6B3BFD.png[/img][/url]

Table 'testcollate'. Scan count 1, logical reads 1240, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

You will have to look at the input/output trees to actually see where the change in collation is happening as that is not exposed in the query plan itself as far as I know. I am using QUERYTRACEON which is an undocumented command in order to demonstrate collation related converts.
[CODE]
select myname from testcollate
where myname = 'Denzil' collate SQL_Latin1_General_Cp437_CS_AS
option (recompile,QueryTraceon 8606)
go
[/CODE]

[xml]
****************************************
*** Input Tree: ***
LogOp_Project QCOL: [tempdb].[dbo].[testcollate].myid QCOL: [tempdb].[dbo].[testcollate].myname
LogOp_Select
LogOp_Get TBL: testcollate testcollate TableID=773577794 TableReferenceID=0 IsRow: COL: IsBaseRow1001
ScaOp_Comp x_cmpEq
[b]ScaOp_Convert varchar collate 520142856[/b],Null,Var,Trim,ML=810
ScaOp_Identifier QCOL: [tempdb].[dbo].[testcollate].myname
ScaOp_Const TI(varchar collate 520142856,Var,Trim,ML=6) XVAR(varchar,Owned,Value=Len,Data = (6,Denzil))
AncOp_PrjList
[/xml]

In order to get the Collation Name of that ID:

[CODE]

select convert(sysname, collationpropertyfromid(520142856, 'name'))
[/CODE]

[url="http://blogs.msdn.com/cfs-file.ashx/__key/communityserver-blogs-components-weblogfiles/00-00-01-24-03-metablogapi/1256.clip_5F00_image003_5F00_25A394FE.png"][img]http://blogs.msdn.com/cfs-file.ashx/__key/communityserver-blogs-components-weblogfiles/00-00-01-24-03-metablogapi/8816.clip_5F00_image003_5F00_thumb_5F00_656D7B83.png[/img][/url]


c. If the Column has different collation than the database would we need to collate the literal to the column itself? Is the constant or literal collated to the collation of the Database or the collation of the column given they both are different?
[CODE]
use tempdb
go
drop table testcollate
go
create table testcollate( myid int identity primary key, myname varchar(810) collate SQL_Latin1_General_Cp437_CS_AS)
go
insert into testcollate values(replicate('a',800))
go 10000
insert into testcollate values('Denzil')
go
create index myind on testcollate(myname)
go
[/CODE]

--As you can see below, the Database collation is different than the Column collation

[CODE]

Select DATABASEPROPERTYEX('TEMPDB','COLLATION') as DBCollation, object_name(object_id) as ObjectName,name As ColName,collation_name as ColumnCollation
from sys.columns where object_id = object_id('testcollate')
[/CODE]

[url="http://blogs.msdn.com/cfs-file.ashx/__key/communityserver-blogs-components-weblogfiles/00-00-01-24-03-metablogapi/7026.clip_5F00_image0044_5F00_27E01DBA.png"][img]http://blogs.msdn.com/cfs-file.ashx/__key/communityserver-blogs-components-weblogfiles/00-00-01-24-03-metablogapi/6036.clip_5F00_image0044_5F00_thumb_5F00_6E5D0DC2.png[/img][/url]
[CODE]

select * from testcollate where myname = 'Denzil'
[/CODE]

We actually get a Seek here, which means the literal here was converted to the collation of the column and not the database.

[url="http://blogs.msdn.com/cfs-file.ashx/__key/communityserver-blogs-components-weblogfiles/00-00-01-24-03-metablogapi/3223.clip_5F00_image0056_5F00_34D9FDCB.png"][img]http://blogs.msdn.com/cfs-file.ashx/__key/communityserver-blogs-components-weblogfiles/00-00-01-24-03-metablogapi/1256.clip_5F00_image0056_5F00_thumb_5F00_6503D93F.png[/img][/url]
[CODE]

select * from testcollate where myname = 'Denzil'
[/CODE]

[xml]
option (recompile,QueryTraceon 8606)
****************************************
*** Input Tree: ***
LogOp_Project QCOL: [tempdb].[dbo].[testcollate].myid QCOL: [tempdb].[dbo].[testcollate].myname
LogOp_Select
LogOp_Get TBL: testcollate testcollate TableID=741577680 TableReferenceID=0 IsRow: COL: IsBaseRow1001
ScaOp_Comp x_cmpEq
ScaOp_Identifier QCOL: [tempdb].[dbo].[testcollate].myname
ScaOp_Const TI(varchar [b]collate 520142856[/b],Var,Trim,ML=6) XVAR(varchar,Owned,Value=Len,Data = (6,Denzil))
AncOp_PrjList


[/xml]

Here you can see the Constant is being collated to the Column collation and not the database collation.

[CODE]select convert(sysname, collationpropertyfromid(520142856, 'name'))[/CODE]

[url="http://blogs.msdn.com/cfs-file.ashx/__key/communityserver-blogs-components-weblogfiles/00-00-01-24-03-metablogapi/5270.clip_5F00_image0037_5F00_524EDF88.png"][img]http://blogs.msdn.com/cfs-file.ashx/__key/communityserver-blogs-components-weblogfiles/00-00-01-24-03-metablogapi/8400.clip_5F00_image0037_5F00_thumb_5F00_400618C6.png[/img][/url]

[deals2buy]

[b] DBCC WRITEPAGE: an introduction[/b]


SQL Server’s undocumented commands, options, and trace flags are sometimes carefully-guarded secrets.

There’s one command in particular, [i]DBCC WRITEPAGE[/i], that has been effectively hidden since it was introduced pre-SQL Server 2000. However, more and more people are learning about it since some of the exam training books publicized trace flag 2588 that allows [i]DBCC HELP (‘?’)[/i] to show all the undocumented DBCC commands, and their syntax (it used to be trace flag 2520, but I changed it for SQL Server 2005 to help obfuscate access to the undocumented DBCCs).

“Microsoft does not support the usage of [i]DBCC WRITEPAGE[/i] and its use would invalidate the support for a customer database on which it is used.”

I present to you [i]DBCC WRITEPAGE[/i] – the most dangerous command you can use in SQL Server.

[i]DBCC WRITEPAGE[/i] allows you alter any byte on any page in any database, as long as you have sysadmin privileges. It also allows you to completely circumvent the buffer pool, in other words you can force page checksum failures.

The purposes of [i]DBCC WRITEPAGE[/i] are:[list=1]
[*]To allow automated testing of [i]DBCC CHECKDB[/i] and repair by the SQL Server team.
[*]To engineer corruptions for demos and testing.
[*]To allow for last-ditch disaster recovery by manually editing a live, corrupt database.
[/list]
I use it for #2 and #3 (and obviously used to do #1). I most recently used it on a live client system in October 2012 after a massive, multi-site SAN failure trashed databases and backups. I do not advise that you attempt #3 unless you’re confident you know what you’re doing and the side-effects on the Storage Engine from the byte(s) that you’re changing.
It is a very dangerous command because:[list=1]
[*]It is an entirely physical change to the page – nothing is logged in the transaction log, and it cannot be rolled back.
[*]You can change any page in any database. For instance, you could use it to modify a page in master so that the instance immediately shuts down and will not start until master is rebuilt and restored.
[*]Anyone with sysadmin privileges can use it and there is no way to stop it.
[*]It breaks the support of your database.
[/list]
You can very easily shoot yourself in the foot very badly playing around with this command. This isn’t hyperbole – it’s just the truth.

The syntax is:

[CODE]
DBCC TRACEON (2588);
GO
DBCC HELP ('WRITEPAGE');
GO
[/CODE]


[CODE]
dbcc WRITEPAGE ({'dbname' | dbid}, fileid, pageid, offset, length, data [, directORbufferpool])
[/CODE]

The parameters mean:[list]
[*]‘dbname’ | dbid : self-explanatory
[*]fileid : file ID containing the page to change
[*]pageid : zero-based page number within that file
[*]offset : zero-based offset in bytes from the start of the page
[*]length : number of bytes to change, from 1 to 8
[*]data : the new data to insert (in hex, in the form ’0xAABBCC’ – example three-byte string)
[*]directORbufferpool : whether to bypass the buffer pool or not (0/1)
[/list]
I’m actually a bit dismayed that someone added the final option to the syntax print-out, as it’s the most dangerous option – and the part I added in SQL Server 2005. If you specify ’1′ for the [i]directORbufferpool[/i] parameter, [i]DBCC WRITEPAGE[/i] does the following:[list]
[*]Checkpoints the database and flushes all its pages out of the buffer pool
[*]Unhooks SQL Server’s FCB (File Control Block) from the data file
[*]Creates its own FCB for the file
[*]Does a direct read of the page into DBCC’s memory
[*]Modifies the page directly
[*]Writes the page directly to disk, bypassing the buffer pool and any page protection generation (i.e. not recalculating the page checksum)
[*]Fixes up the FCBs again
[/list]
So the buffer pool knows nothing about the change to the page – it’s as if the I/O subsystem changed/corrupted the page. If you don’t specify that parameter, or specify ’0′, the change occurs on the page in the buffer pool and the page checksum will be calculated correctly when the page is written to disk (albeit with other corruption issues maybe).

This means that you can force page checksum failures from within SQL Server – great for demos and testing. This is how I create corrupt databases.

To finish off this introductory post I’ll show you how to do just that, using a non-production server please…

[CODE]
CREATE DATABASE [CorruptDB];
GO
USE [CorruptDB];
GO
CREATE TABLE [Test] (
[c1] INT IDENTITY,
[c2] CHAR (8000) DEFAULT 'a');
GO

INSERT INTO [Test] DEFAULT VALUES;
GO
DBCC IND (N'CorruptDB', N'Test', -1);
GO
[/CODE]

[CODE]
PageFID PagePID IAMFID IAMPID ObjectID ...
------- ----------- ------ ----------- ----------- ...
1 154 NULL NULL 101575400 ...
1 153 1 154 101575400 ...
[/CODE]

I’m going to corrupt the data page – page (1:153):

[CODE]
ALTER DATABASE [CorruptDB] SET SINGLE_USER;
GO
DBCC WRITEPAGE (N'CorruptDB', 1, 153, 4000, 1, 0x45, 1);
GO
[/CODE]

Now the database is corrupt and I’ve circumvented the buffer pool so the page checksum is incorrect now – watch…

[CODE]
SELECT * FROM [Test];
GO
[/CODE]

[CODE]
c1 c2
----------- ---------- ...
Msg 824, Level 24, State 2, Line 1
SQL Server detected a logical consistency-based I/O error: incorrect checksum (expected: 0x41fb2e55; actual: 0x41fb4b55). It occurred during a read of page (1:153) in database ID 29 at offset 0x00000000132000 in file 'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\CorruptDB.mdf'. Additional messages in the SQL Server error log or system event log may provide more detail. This is a severe error condition that threatens database integrity and must be corrected immediately. Complete a full database consistency check (DBCC CHECKDB). This error can be caused by many factors; for more information, see SQL Server Books Online.
[/CODE]

Nice eh? Yes, if that’s what you’re trying to do.

In the error log:

[CODE]
EXEC xp_readerrorlog;
GO
[/CODE]

[CODE]
2013-02-05 14:32:56.760 spid55 Starting up database 'CorruptDB'.
2013-02-05 14:36:37.320 spid55 Setting database option SINGLE_USER to ON for database CorruptDB.
2013-02-05 14:36:37.320 spid55 User "APPLECROSS\paul" is modifying bytes 4000 to 4001 of page (1:153) in database "CorruptDB".
2013-02-05 14:37:55.160 spid55 Error: 824, Severity: 24, State: 2.
2013-02-05 14:37:55.160 spid55 SQL Server detected a logical consistency-based I/O error: blah blah blah
[/CODE]

[loveindia]

saturday kuda nee saduvu gola endehe...

[deals2buy]

[b] Tracking page splits using the transaction log[/b]


Before the sqlserver.transaction_log event was added, there was (and still is) the sqlserver.page_split event but that does not distinguish between ‘good’ splits and ‘nasty’ splits either, so some post processing is involved (essentially reading the page referenced in the event to see if it really split or not).

So what’s the answer?
[b] Scanning the log for page splits[/b]

The easiest way to proactively see page splits occurring is to look in the transaction log. Whenever a page splits, an [i]LOP_DELETE_SPLIT[/i] log record is generated so querying the transaction log can let you know what’s going on.

Some simple code to do this is:
[CODE]
SELECT
[AllocUnitName] AS N'Index',
(CASE [Context]
WHEN N'LCX_INDEX_LEAF' THEN N'Nonclustered'
WHEN N'LCX_CLUSTERED' THEN N'Clustered'
ELSE N'Non-Leaf'
END) AS [SplitType],
COUNT (1) AS [SplitCount]
FROM
fn_dblog (NULL, NULL)
WHERE
[Operation] = N'LOP_DELETE_SPLIT'
GROUP BY [AllocUnitName], [Context];
GO
[/CODE]


However, I don’t recommend doing this, for two reasons:[list=1]
[*]Running [i]fn_dblog[/i] will cause read I/Os on the transaction log, which can cause performance issues, especially if you’re running the scanner regularly and it happens to coincide with a log backup, for instance.
[*]Log clearing is disabled while [i]fn_dblog[/i] is running, so on a system with a large amount of log to scan, this could interrupt the ability of the log to clear and cause log growth.
[/list]
If you’re running in the full or bulk-logged recovery model, I recommend scanning your log backups for page splits instead of your actual log. If you’re only running in the simple recovery model, and you *really* want to run the script regularly, you’re going to have to run the script just before each checkpoint operation clears the log. But still, be careful you don’t interrupt the log clearing process.
[b] Scanning a log backup for page splits[/b]

There are two options for this, using the [i]fn_dump_dblog[/i] function I blogged about [url="http://www.sqlskills.com/blogs/paul/using-fn_dblog-fn_dump_dblog-and-restoring-with-stopbeforemark-to-an-lsn/"]here[/url]:[list]
[*]Scanning a log backup on a system other than the production system.
[*]Scanning a log backup on the production system.
[/list]
If you choose to use a system other than the production system, then unless you have a restored copy of the database, you will not be able to get the index name, as [i]fn_dump_dblog[/i] does not give you the name and you will not have the metadata to allow looking up the index name from the allocation unit ID in the log.

So I’ve created two scripts for you, for when the database is and isn’t available on the server where the backup is located. I’ll extend these in future posts.

Have fun!

[b]Scanning a log backup where the database is not available[/b]


[CODE]
SELECT
[AllocUnitId],
(CASE [Context]
WHEN N'LCX_INDEX_LEAF' THEN N'Nonclustered'
WHEN N'LCX_CLUSTERED' THEN N'Clustered'
ELSE N'Non-Leaf'
END) AS [SplitType],
COUNT (1) AS [SplitCount]
FROM
fn_dump_dblog (NULL, NULL, N'DISK', 1, N'C:\SQLskills\SplitTest_log.bck',
DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT)
WHERE
[Operation] = N'LOP_DELETE_SPLIT'
GROUP BY [AllocUnitId], [Context];
GO
[/CODE]


[b]Scanning a log backup where the database is available[/b]

[b][CODE]
SELECT
CAST ([s].[name] AS VARCHAR) + '.' + CAST ([o].[name] AS VARCHAR) + '.' + CAST ([i].[name] AS VARCHAR) AS [Index],
[f].[SplitType],
[f].[SplitCount]
FROM
(SELECT
[AllocUnitId],
(CASE [Context]
WHEN N'LCX_INDEX_LEAF' THEN N'Nonclustered'
WHEN N'LCX_CLUSTERED' THEN N'Clustered'
ELSE N'Non-Leaf'
END) AS [SplitType],
COUNT (1) AS [SplitCount]
FROM
fn_dump_dblog (NULL, NULL, N'DISK', 1, N'C:\SQLskills\SplitTest_log.bck',
DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT)
WHERE
[Operation] = N'LOP_DELETE_SPLIT'
GROUP BY [AllocUnitId], [Context]) f
JOIN sys.system_internals_allocation_units [a]
ON [a].[allocation_unit_id] = [f].[AllocUnitId]
JOIN sys.partitions [p]
ON [p].[partition_id] = [a].[container_id]
JOIN sys.indexes [i]
ON [i].[index_id] = [p].[index_id] AND [i].[object_id] = [p].[object_id]
JOIN sys.objects [o]
ON [o].[object_id] = [p].[object_id]
JOIN sys.schemas [s]
ON [s].[schema_id] = [o].[schema_id];
GO[/b]
[b][/CODE][/b]

[deals2buy]

[quote name='loveindia' timestamp='1360443809' post='1303252907']
saturday kuda nee saduvu gola endehe...
[/quote]

aakali ki chaduvu ki daanam ki oka roju antu undadu

[deals2buy]

[b] SSAS How-To: Create Dynamic Time Calculations To Reuse Over Any Measure[/b]

Rather than making individual time calculations for each measure in an SSAS cube (i.e. YTD, Prior YTD, YTD Variances, etc), one can use a more dynamic approach to time calcs applicable to all measures and save a great amount of dev time and overhead in the process.

[b]1. Create Named Query in DSV:[/b]

[CODE]
SELECT 1 AS DateCalculationKey, ‘Selected Date’ AS DateCalculation
UNION
SELECT 2, ‘YTD’
UNION
SELECT 3, ‘PY YTD’
UNION
SELECT 4, ‘YTD Chg’
UNION
SELECT 5, ‘YTD Chg %’
[/CODE]


[url="http://saldeloera.wordpress.com/2013/02/07/ssas-how-to-create-dynamic-time-calculations-to-reuse-over-any-measure/bstep1_namedset-3/"][img]http://saldeloera.files.wordpress.com/2012/12/bstep1_namedset2.jpg?w=300&h=240[/img][/url]

[b][b]2. Create the dimension and add it to the Dimension Usage tab of the cube (no relations to the fact will exist)[/b][/b]

[b]3. Add the MDX script calculations:[/b]


[CODE]
SCOPE (
[Date].[Calendar Hierarchy].MEMBERS);
[Date Calculations].[Date Calculations].[YTD] =
AGGREGATE
(
{[Date Calculations].[Date Calculations].[Selected Date]} *
PERIODSTODATE
(
[Date].[Calendar Hierarchy].[Calendar Year],
[Date].[Calendar Hierarchy].CURRENTMEMBER
)
);
[Date Calculations].[Date Calculations].[PY YTD] =
AGGREGATE
(
{[Date Calculations].[Date Calculations].[Selected Date]} *
PERIODSTODATE
(
[Date].[Calendar Hierarchy].[Calendar Year],
PARALLELPERIOD
(
[Date].[Calendar Hierarchy].[Calendar Year],
1,
[Date].[Calendar Hierarchy].CURRENTMEMBER
)
)
);
END SCOPE;
SCOPE ([Date].[Calendar Hierarchy].[All]);
[Date Calculations].[Date Calculations].[YTD] = NULL;
[Date Calculations].[Date Calculations].[PY YTD]=NULL;
END SCOPE;
[Date Calculations].[Date Calculations].[YTD Chg]=[Date Calculations].[Date Calculations].[YTD]-
[Date Calculations].[Date Calculations].[PY YTD];
[Date Calculations].[Date Calculations].[YTD Chg %] =
IIF
(
[Date Calculations].[Date Calculations].[PY YTD] = 0,
NULL,
([Date Calculations].[Date Calculations].[YTD] - [Date Calculations].[Date Calculations].[PY YTD]) /
[Date Calculations].[Date Calculations].[PY YTD]
);
FORMAT_STRING([Date Calculations].[Date Calculations].[YTD Chg %]) = “#,0.0%”;
//Create current year and month in scripts:
CREATESET CURRENTCUBE.[Current Year]
AS {(strtomember(“[Date].[Calendar Hierarchy].[Calendar Year].["+ cstr(year(now()))+"]“))}, DISPLAY_FOLDER = ‘Named Sets’ ;
CREATESET CURRENTCUBE.[Current Month] AS
TAIL
(
NONEMPTY
(
[Date].[Calendar Hierarchy].[Month Name Year],
[Measures].[Fact Sales Count]
)
);
[/CODE]

[url="http://saldeloera.wordpress.com/2013/02/07/ssas-how-to-create-dynamic-time-calculations-to-reuse-over-any-measure/bstep2-scripts/"][img]http://saldeloera.files.wordpress.com/2012/12/bstep2-scripts.jpg?w=289&h=300[/img][/url]

[b]The result![/b]
[url="http://saldeloera.wordpress.com/2013/02/07/ssas-how-to-create-dynamic-time-calculations-to-reuse-over-any-measure/bstep2b-cubebrowser/"][img]http://saldeloera.files.wordpress.com/2012/12/bstep2b-cubebrowser.jpg?w=300&h=131[/img][/url]

[deals2buy]

[b] Slicer or Sub-Cube?[/b]

Every developer with SQL background knows how the WHERE clause works. You put some condition in the WHERE clause, such as TheDate = Yesterday (pseudo code), and it will only return data for yesterday.

Not so straightforward in MDX. We should expect more complex behaviors in MDX because of the multi-dimensional nature of the cubes.
But how different it can be.

There are many topics to explore, including why we prefer to call the WHERE clause slicer in MDX, how tuple is constructed, when default member is used, how slicer (WHERE clause if you insist) can be used to limit data, and how axes can be used, how the MDX engine decides when to use slicer, axes, and calculation formula to limit the data, etc..
[b] Sub-Select Can Filter Data Too[/b]

I’ll explore just one aspect of how we can use both the slicer and a sub-cube to limit the data, and where they are the same, and where they might give you different results.
[b] Both Slicer and Sub-Select Produces the Same Result[/b]

Run these two queries (separating by GO), you get the same Internet Sales Amount from both queries, for July 1, 2008.

So our conclusion so far is that slicer and sub-select should give same results.

[url="http://bisherryli.files.wordpress.com/2013/02/image.png"][img]http://bisherryli.files.wordpress.com/2013/02/image_thumb.png?w=448&h=270[/img][/url]

[url="http://bisherryli.files.wordpress.com/2013/02/image1.png"][img]http://bisherryli.files.wordpress.com/2013/02/image_thumb1.png?w=223&h=162[/img][/url]

If you make such statement to some MDX experts, they will tell you that results from using a member in the slicer can be different from using the same member in a sub-select.

So what can be different?
[b] While Sub-Select does not change the query context, the slicer does[/b]

Run this query pair. We are using the [color="#c0504d"]currentmember[/color] function to show what date we are currently at. In MDX’s term, we are checking the current member of the Date hierarchy in the query context (quite mouthful).

[url="http://bisherryli.files.wordpress.com/2013/02/image2.png"][img]http://bisherryli.files.wordpress.com/2013/02/image_thumb2.png?w=363&h=362[/img][/url]

[url="http://bisherryli.files.wordpress.com/2013/02/image3.png"][img]http://bisherryli.files.wordpress.com/2013/02/image_thumb3.png?w=262&h=158[/img][/url]

It turned out that the “current date” from the two queries is not the same. The first one with slicer says we are currently at just one day, July 1, 2008. The second one with the sub-select says we are actually currently at All Periods (all days in the entire Date dimension; the root member of the Date dimension).

Now we know that the query context (where we currently at) is different, depending on where we are putting our member, in the slicer or in the sub-select. The sub-select does nothing to change the query context, while the slicer changes the context according to the tuple (in the above example we only have one member in the tuple) we put in the slicer.

You might already figure out that we need to be careful now about the context.
[b] When Sub-Select and Slicer Might Give Different Results[/b]

Here is an example where your filtered result might not be exactly what you want if you are using the sub-select.

[url="http://bisherryli.files.wordpress.com/2013/02/image4.png"][img]http://bisherryli.files.wordpress.com/2013/02/image_thumb4.png?w=590&h=321[/img][/url]

[url="http://bisherryli.files.wordpress.com/2013/02/image5.png"][img]http://bisherryli.files.wordpress.com/2013/02/image_thumb5.png?w=417&h=284[/img][/url]

[url="http://bisherryli.files.wordpress.com/2013/02/image6.png"][img]http://bisherryli.files.wordpress.com/2013/02/image_thumb6.png?w=339&h=144[/img][/url]

When using the [color="#c0504d"]currentmember[/color] as a filter, You should expect the sub-select will give you the Internet Sales Amount for the entire date range in your Date dimension, not just from one day, July 1, 2008.