Collation Conflict - 3

I posted a blog post about Collation Conflict some time back. With the discussions on Temp tables and table variables we realized that we have an option with table variable as work around for the above problem.

However, we have much cleaner option in SQL Server 2012 by using contained database option.

Let us create the Contained database.

CREATE DATABASE [Danish]
CONTAINMENT = PARTIAL
COLLATE Danish_Norwegian_CI_AS
GO

Then let me run the previous script which failed in standard databases.

USE Danish
GO

CREATE TABLETable1
(IDINT IDENTITY,
NameVARCHAR(50)
)

INSERT INTOTable1
(NAME)
VALUES
('A'),
('B'),
('C')

CREATE TABLE#Temp1
(MaxIDNAME VARCHAR(50)
)

INSERT INTO#Temp1
SELECTMAX(Name)FROMTABLE1

SELECT*FROMTABLE1
WHERENAMEIN
(SELECTMAxIDNAME from#Temp1)

This time we don’t have any errors.

Actually, temp table  is created in the tempdb but will not have an issue as in the standard databases.

REPAIR_FAST another flop

I posted about REPAIR_FAST around few days back. I got a feed back from Glen Joseph.

But, this requires the DB to be in Single User Mode ( am i correct ? ) these days you cannot do this in many Production environments.         

I just tried out it.

Yes Glen you are absolutely right!!!

REBUILD_FAST option in DBCC CHECKDB

DBCC CHECKDB is somewhat famous for DBA to run in case of database corruptions.

This is the syntax for it.

So incase of a corruption, you can have either of three parameters, REPAIR_FAST, REPAIR_REBUILD and REPAIR_ALLOW_DATA_LOSS. Out of these parameters let us focus on REPAIR_FAST. This is the documentation for REPAIR_FAST in SQL Server 2000

Performs minor, nontime-consuming repair actions such as repairing extra keys in nonclustered indexes. These repairs can be done quickly and without risk of data loss.

However, do you know that this is no more with SQL Server 2005 onwards.

This is the documentation for the REPAIR_FAST.

Maintains syntax for backward compatibility only. No repair actions are performed.

Keep sending your comments.!!!