Gola's MS Tech

Tuesday, June 30, 2009

Date function that determines date range based on weekday

Example select dbo.DAYSEARCH('monday',2,-1,getdate())

CREATE FUNCTION dbo.DAYSEARCH(@day_name VARCHAR(9), @step_count INT, @direction smallint,@dt DATETIME)
RETURNS datetime
AS
BEGIN
/*
Returns a date based upon criteria to find a specific day-of-week
for a specific number of "steps" forward or backward.
For instance, "last Wednesday" or "two Thursdays from today".
@day_name = day of week to find ie. Monday, Tuesday...
@step_count = number of iterations back for a specific day:
--------> "1 Last Monday " = 1
--------> "3 Thursdays from now" = 3
@direction:
--------> -1 if Past
--------> 1 if Future
*/

--declare @day_name VARCHAR(9), @step_count INT, @direction smallint

DECLARE @daysearch datetime
DECLARE @counter smallint
DECLARE @hits smallint
DECLARE @day_name_calc VARCHAR(9)

SELECT @counter = @direction
SELECT @hits = 0

WHILE @hits < @step_count
BEGIN
SELECT @day_name_calc = DATENAME(weekday , DATEADD(d, @counter, @dt))

IF @day_name_calc = @day_name
BEGIN
SELECT @hits = @hits + 1
SELECT @daysearch = DATEADD(d, @counter, @dt)
END

SELECT @counter = (@counter + (1 * @direction))
END
RETURN @daysearch
END

Tuesday, April 28, 2009

JavaScript check uncheck with ASP.Net GridView Control

<%@ Page Language="C#" AutoEventWireup="true" CodeFile="CSharp.aspx.cs" Inherits="_Default" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml" >
<head id="Head1" runat="server">
<title>Scrollable Grid</title>
<style type ="text/css" >
.header
{
background-color:Green;
}
</style>
<script type = "text/javascript">
function Check_Click(objRef)
{
//Get the Row based on checkbox
var row = objRef.parentNode.parentNode;
if(objRef.checked)
{
//If checked change color to Aqua
row.style.backgroundColor = "aqua";
}
else
{
//If not checked change back to original color
if(row.rowIndex % 2 == 0)
{
//Alternating Row Color
row.style.backgroundColor = "#C2D69B";
}
else
{
row.style.backgroundColor = "white";
}
}

//Get the reference of GridView
var GridView = row.parentNode;

//Get all input elements in Gridview
var inputList = GridView.getElementsByTagName("input");

for (var i=0;i<inputList.length;i++)
{
//The First element is the Header Checkbox
var headerCheckBox = inputList[0];

//Based on all or none checkboxes
//are checked check/uncheck Header Checkbox
var checked = true;
if(inputList[i].type == "checkbox" && inputList[i] != headerCheckBox)
{
if(!inputList[i].checked)
{
checked = false;
break;
}
}
}
headerCheckBox.checked = checked;

}
</script>
<script type = "text/javascript">
function checkAll(objRef)
{
var GridView = objRef.parentNode.parentNode.parentNode;
var inputList = GridView.getElementsByTagName("input");
for (var i=0;i<inputList.length;i++)
{
//Get the Cell To find out ColumnIndex
var row = inputList[i].parentNode.parentNode;
if(inputList[i].type == "checkbox" && objRef != inputList[i])
{
if (objRef.checked)
{
//If the header checkbox is checked
//check all checkboxes
//and highlight all rows
row.style.backgroundColor = "aqua";
inputList[i].checked=true;
}
else
{
//If the header checkbox is checked
//uncheck all checkboxes
//and change rowcolor back to original
if(row.rowIndex % 2 == 0)
{
//Alternating Row Color
row.style.backgroundColor = "#C2D69B";
}
else
{
row.style.backgroundColor = "white";
}
inputList[i].checked=false;
}
}
}
}
</script>
<script type = "text/javascript">
function MouseEvents(objRef, evt)
{
var checkbox = objRef.getElementsByTagName("input")[0];
if (evt.type == "mouseover")
{
objRef.style.backgroundColor = "orange";
}
else
{
if (checkbox.checked)
{
objRef.style.backgroundColor = "aqua";
}
else if(evt.type == "mouseout")
{
if(objRef.rowIndex % 2 == 0)
{
//Alternating Row Color
objRef.style.backgroundColor = "#C2D69B";
}
else
{
objRef.style.backgroundColor = "white";
}
}
}
}
</script>
</head>
<body>
<form id="form1" runat="server">
<asp:GridView ID="GridView1" runat="server" HeaderStyle-CssClass = "header"
AutoGenerateColumns = "false" Font-Names = "Arial" OnRowDataBound = "RowDataBound"
Font-Size = "11pt" AlternatingRowStyle-BackColor = "#C2D69B" >
<Columns>
<asp:TemplateField>
<HeaderTemplate>
<asp:CheckBox ID="checkAll" runat="server" onclick = "checkAll(this);" />
</HeaderTemplate>
<ItemTemplate>
<asp:CheckBox ID="CheckBox1" runat="server" onclick = "Check_Click(this)" />
</ItemTemplate>
</asp:TemplateField>
<asp:BoundField ItemStyle-Width="150px" DataField="CustomerID" HeaderText="CustomerID" />
<asp:BoundField ItemStyle-Width="150px" DataField="City" HeaderText="City" />
<asp:BoundField ItemStyle-Width="150px" DataField="Country" HeaderText="Country"/>
<asp:BoundField ItemStyle-Width="150px" DataField="PostalCode" HeaderText= "PostalCode"/>
</Columns>
</asp:GridView>
</form>
</body>
</html>

for more information please visit : http://www.aspsnippets.com/post/2009/03/13/Using-JavaScript-with-ASPNet-GridView-Control.aspx

Thursday, January 15, 2009

How to find all the stored procedures that reference a specific object ?

Usually, people tries to find all the stored procedures that reference a specific object. This object could be any table, view or even any text that is placed in the procedure, In all such cases this post will be really of much help to all of them.

SQL SERVER 2000

Let's say you are searching for 'objectName' in all your stored procedures then all you have to do is :

SELECT ROUTINE_NAME, ROUTINE_DEFINITION FROM
INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_DEFINITION LIKE '%objectName%'
AND ROUTINE_TYPE='PROCEDURE'

Another way to perform a search is through the system table syscomments:

SELECT OBJECT_NAME(id) FROM syscomments
WHERE [text] LIKE '%objectName%'
AND OBJECTPROPERTY(id, 'IsProcedure') = 1
GROUP BY OBJECT_NAME(id)

Now, why to use GROUP BY? Well, there is a curious distribution of the procedure text in system tables if the procedure is greater than 8KB. So, the above makes sure that any procedure name is only returned once, even if multiple rows in or syscomments draw a match. But, that begs the question, what happens when the text you are looking for crosses the boundary between rows? Here is a method to create a simple stored procedure that will do this, by placing the search term (in this case, 'objectName') at around character 7997 in the procedure. This will force the procedure to span more than one row in syscomments, and will break the word 'objectName' up across rows.

Run the following query in Query Analyzer, with results to text (CTRL+T):

SET NOCOUNT ON
SELECT 'SELECT '''+REPLICATE('x', 7936)+'objectName' SELECT REPLICATE('x', 500)+''''

This will yield two results. Copy them and inject them here:

CREATE PROCEDURE dbo.x
AS
BEGIN
SET NOCOUNT ON
<<>>
END
GO

Now, try and find this stored procedure in INFORMATION_SCHEMA.ROUTINES or syscomments using the same search filter as above. The former will be useless, since only the first 8000 characters are stored here. The latter will be a little more useful, but initially, will return 0 results because the word 'objectName' is broken up across rows, and does not appear in a way that LIKE can easily find it. So, we will have to take a slightly more aggressive approach to make sure we find this procedure. Your need to do this, by the way, will depend partly on your desire for thoroughness, but more so on the ratio of stored procedures you have that are greater than 8KB. In all the systems that I manage, I don't have more than a handful that approach this size, so this isn't something I reach for very often. Maybe for you it will be more useful. First off, to demonstrate a little better (e.g. by having more than one procedure that exceeds 8KB), let's create a second procedure just like above. Let's call it dbo.y, but this time remove the word 'objectName' from the middle of the SELECT line.

CREATE PROCEDURE dbo.y
AS
BEGIN
SET NOCOUNT ON
<<>>
END
GO

Basically, what we're going to do next is loop through a cursor, for all procedures that exceed 8KB. We can get this list as follows:

SELECT OBJECT_NAME(id)
FROM syscomments
WHERE OBJECTPROPERTY(id, 'IsProcedure') = 1
GROUP BY OBJECT_NAME(id)
HAVING COUNT(*) > 1

We'll need to create a work table to hold the results as we loop through the procedure, and we'll need to use UPDATETEXT to append each row with the new 8000-or-less chunk of the stored procedure code.

-- create temp table
CREATE TABLE #temp ( Proc_id INT, Proc_Name SYSNAME, Definition NTEXT )
-- get the names of the procedures that meet our criteria
INSERT #temp(Proc_id, Proc_Name)
SELECT id, OBJECT_NAME(id)
FROM syscomments
WHERE OBJECTPROPERTY(id, 'IsProcedure') = 1
GROUP BY id, OBJECT_NAME(id)
HAVING COUNT(*) > 1

-- initialize the NTEXT column so there is a pointer
UPDATE #temp SET Definition = ' '
-- declare local variables
DECLARE @txtPval binary(16), @txtPidx INT, @curName SYSNAME, @curtext NVARCHAR(4000)

-- set up a cursor, we need to be sure this is in the correct order
-- from syscomments (which orders the 8KB chunks by colid)

DECLARE c CURSOR
LOCAL FORWARD_ONLY STATIC READ_ONLY FOR
SELECT OBJECT_NAME(id), text
FROM syscomments s
INNER JOIN #temp t
ON s.id = t.Proc_id
ORDER BY id, colid OPEN c FETCH NEXT FROM c INTO @curName, @curtext

-- start the loop
WHILE (@@FETCH_STATUS = 0)
BEGIN
-- get the pointer for the current procedure name / colid

SELECT @txtPval = TEXTPTR(Definition)
FROM #temp
WHERE Proc_Name = @curName

-- find out where to append the #temp table's value

SELECT @txtPidx = DATALENGTH(Definition)/2
FROM #temp
WHERE Proc_Name = @curName
-- apply the append of the current 8KB chunk
UPDATETEXT #temp.definition @txtPval @txtPidx 0 @curtext
FETCH NEXT FROM c INTO @curName, @curtext
END
-- check what was produced
SELECT Proc_Name, Definition, DATALENGTH(Definition)/2
FROM #temp
-- check our filter
SELECT Proc_Name, Definition
FROM #temp
WHERE definition LIKE '%objectName%'
-- clean up
DROP TABLE #temp
CLOSE c
DEALLOCATE c

SQL SERVER 2005
In SQL Server 2005 there are new functions like OBJECT_DEFINITION, which returns the whole text of the procedure. Also, there is a new catalog view, sys.sql_modules, which also holds the entire text, and INFORMATION_SCHEMA.ROUTINES has been updated so that the ROUTINE_DEFINITION column also contains the full text of the procedure. So, any of the following queries will work to perform this search in SQL Server 2005:

SELECT Name FROM sys.procedures
WHERE OBJECT_DEFINITION(object_id)
LIKE '%objectName%'

SELECT OBJECT_NAME(object_id) FROM sys.sql_modules
WHERE Definition LIKE '%objectName%'
AND OBJECTPROPERTY(object_id, 'IsProcedure') = 1

SELECT ROUTINE_NAME FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_DEFINITION LIKE '%objectName%'
AND ROUTINE_TYPE = 'PROCEDURE'

Thursday, September 11, 2008

Differences between SQL Server temporary tables and table variables

Temporary tables are created in tempdb. The name "temporary" is slightly misleading, for even though the tables are instantiated in tempdb, they are backed by physical disk and are even logged into the transaction log. They act like regular tables in that you can query their data via SELECT queries and modify their data via UPDATE, INSERT, and DELETE statements. If created inside a stored procedure they are destroyed upon completion of the stored procedure. Furthermore, the scope of any particular temporary table is the session in which it is created; meaning it is only visible to the current user. Multiple users could create a temp table named #TableX and any queries run simultaneously would not affect one another - they would remain autonomous transactions and the tables would remain autonomous objects. You may notice that my sample temporary table name started with a "#" sign. This is the identifier for SQL Server that it is dealing with a temporary table.

Table Variables

The syntax for creating table variables is quite similar to creating either regular or temporary tables. The only differences involve a naming convention unique to variables in general, and the need to declare the table variable as you would any other local variable in Transact SQL

table variables have certain clear limitations. Table variables can not have Non-Clustered Indexes
You can not create constraints in table variables
You can not create default values on table variable columns
Statistics can not be created against table variables

Similarities with temporary tables include:

Instantiated in tempdb
Clustered indexes can be created on table variables and temporary tables
Both are logged in the transaction log
Just as with temp and regular tables, users can perform all Data Modification Language (DML) queries against a table variable: SELECT, INSERT, UPDATE, and DELETE.

Deleting Data in SQL Server with TRUNCATE vs DELETE commands

Question
There are two main keywords used for deleting data from a table: TRUNCATE and DELETE. Although each achieves the same result, the methods employed for each vastly differ. There are advantages, limitations, and consequences of each that you should consider when deciding which method to use.

Answer

Deleting Data Using TRUNCATE TABLE

TRUNCATE TABLE is a statement that quickly deletes all records in a table by deallocating the data pages used by the table. This reduces the resource overhead of logging the deletions, as well as the number of locks acquired; however, it bypasses the transaction log, and the only record of the truncation in the transaction logs is the page deallocation. Records removed by the TRUNCATE TABLE statement cannot be restored. You cannot specify a WHERE clause in a TRUNCATE TABLE statement-it is all or nothing. The advantage to using TRUNCATE TABLE is that in addition to removing all rows from the table it resets the IDENTITY back to the SEED, and the deallocated pages are returned to the system for use in other areas.

In addition, TRUNCATE TABLE statements cannot be used for tables involved in replication or log shipping, since both depend on the transaction log to keep remote databases consistent.
TRUNCATE TABLE cannot be used when a foreign key references the table to be truncated, since TRUNCATE statements do not fire triggers. This could result in inconsistent data because ON DELETE/UPDATE triggers would not fire. If all table rows need to be deleted and there is a foreign key referencing the table, you must drop the index and recreate it. If a TRUNCATE TABLE statement is issued against a table that has foreign key references, the following error is returned:

Deleting Data Using DELETE FROM Statement

DELETE TABLE statements delete rows one at a time, logging each row in the transaction log, as well as maintaining log sequence number (LSN) information. Although this consumes more database resources and locks, these transactions can be rolled back if necessary. You can also specify a WHERE clause to narrow down the rows to be deleted. When you delete a large number of rows using a DELETE FROM statement, the table may hang on to the empty pages requiring manual release using DBCC SHRINKDATABASE (db_name).
When large tables require that all records be deleted and TRUNCATE TABLE cannot be used, the following statements can be used to achieve the same result as TRUNCATE TABLE:

DELETE from "table_name"
DBCC CHECKIDENT("table_name", RESEED, "reseed_value")

Script to create commands to disable, enable, drop and recreate Foreign Key constraints in SQL Server

TO READ MORE...

Problem
Foreign keys (FK) are designed to maintain referential integrity within your database. When used properly FKs allow you to be sure that your data is intact and there are no orphaned records. On the flipside of using FKs to maintain referential integrity, they also become an issue when you need to change table structures or temporarily modify data that might violate the foreign key constraint. Other tips have been written that show you how to identify your FKs and why you should use them, but what is the best approach for manipulating FK constraints to make structure or data changes?

Solution
As mentioned already other tips have been written that show you how to find your foreign keys within your database and why you should use foreign keys. You can refer to these tips for this information:

The Importance of Foreign Keys
Identify all of your foreign keys in a database

--------------------------------------------------------------------------------

Below is a script that can be used to find all foreign keys that reference the primary table that you wish to work with. In this script you provide the table name and the schema name (object owner). The script will then return a list of statements that can be copied and pasted into a query window to make these changes.

The script also takes three different parameter values depending on the action you want to take:

DISABLE - this will create the command to disable all FK constraints that reference the table you are working with
ENABLE - this will create the command to enable all FK constraints that reference the table you are working with
DROP - this will create a command to drop all FK constraints and create a command to create all FK constraints that reference the table are working with
The values below use a table in the AdventureWorks database, so you can just copy and paste this code and run this sample against that database.

-- Enable, Disable, Drop and Recreate FKs based on Primary Key table
-- Written 2007-11-18
-- Edgewood Solutions / MSSQLTips.com
-- Works for SQL Server 2005

SET NOCOUNT ON

DECLARE @operation VARCHAR(10)
DECLARE @tableName sysname
DECLARE @schemaName sysname

SET @operation = 'DROP' --ENABLE, DISABLE, DROP
SET @tableName = 'SpecialOfferProduct'
SET @schemaName = 'Sales'

DECLARE @cmd NVARCHAR(1000)

DECLARE
@FK_NAME sysname,
@FK_OBJECTID INT,
@FK_DISABLED INT,
@FK_NOT_FOR_REPLICATION INT,
@DELETE_RULE smallint,
@UPDATE_RULE smallint,
@FKTABLE_NAME sysname,
@FKTABLE_OWNER sysname,
@PKTABLE_NAME sysname,
@PKTABLE_OWNER sysname,
@FKCOLUMN_NAME sysname,
@PKCOLUMN_NAME sysname,
@CONSTRAINT_COLID INT

DECLARE cursor_fkeys CURSOR FOR
SELECT Fk.name,
Fk.OBJECT_ID,
Fk.is_disabled,
Fk.is_not_for_replication,
Fk.delete_referential_action,
Fk.update_referential_action,
OBJECT_NAME(Fk.parent_object_id) AS Fk_table_name,
schema_name(Fk.schema_id) AS Fk_table_schema,
TbR.name AS Pk_table_name,
schema_name(TbR.schema_id) Pk_table_schema
FROM sys.foreign_keys Fk LEFT OUTER JOIN
sys.tables TbR ON TbR.OBJECT_ID = Fk.referenced_object_id --inner join
WHERE TbR.name = @tableName
AND schema_name(TbR.schema_id) = @schemaName

OPEN cursor_fkeys

FETCH NEXT FROM cursor_fkeys
INTO @FK_NAME,@FK_OBJECTID,
@FK_DISABLED,
@FK_NOT_FOR_REPLICATION,
@DELETE_RULE,
@UPDATE_RULE,
@FKTABLE_NAME,
@FKTABLE_OWNER,
@PKTABLE_NAME,
@PKTABLE_OWNER

WHILE @@FETCH_STATUS = 0
BEGIN

-- create statement for enabling FK
IF @operation = 'ENABLE'
BEGIN
SET @cmd = 'ALTER TABLE [' + @FKTABLE_OWNER + '].[' + @FKTABLE_NAME
+ '] CHECK CONSTRAINT [' + @FK_NAME + ']'

PRINT @cmd
END

-- create statement for disabling FK
IF @operation = 'DISABLE'
BEGIN
SET @cmd = 'ALTER TABLE [' + @FKTABLE_OWNER + '].[' + @FKTABLE_NAME
+ '] NOCHECK CONSTRAINT [' + @FK_NAME + ']'

PRINT @cmd
END

-- create statement for dropping FK and also for recreating FK
IF @operation = 'DROP'
BEGIN

-- drop statement
SET @cmd = 'ALTER TABLE [' + @FKTABLE_OWNER + '].[' + @FKTABLE_NAME
+ '] DROP CONSTRAINT [' + @FK_NAME + ']'

PRINT @cmd

-- create process
DECLARE @FKCOLUMNS VARCHAR(1000), @PKCOLUMNS VARCHAR(1000), @COUNTER INT

-- create cursor to get FK columns
DECLARE cursor_fkeyCols CURSOR FOR
SELECT COL_NAME(Fk.parent_object_id, Fk_Cl.parent_column_id) AS Fk_col_name,
COL_NAME(Fk.referenced_object_id, Fk_Cl.referenced_column_id) AS Pk_col_name
FROM sys.foreign_keys Fk LEFT OUTER JOIN
sys.tables TbR ON TbR.OBJECT_ID = Fk.referenced_object_id INNER JOIN
sys.foreign_key_columns Fk_Cl ON Fk_Cl.constraint_object_id = Fk.OBJECT_ID
WHERE TbR.name = @tableName
AND schema_name(TbR.schema_id) = @schemaName
AND Fk_Cl.constraint_object_id = @FK_OBJECTID -- added 6/12/2008
ORDER BY Fk_Cl.constraint_column_id

OPEN cursor_fkeyCols

FETCH NEXT FROM cursor_fkeyCols INTO @FKCOLUMN_NAME,@PKCOLUMN_NAME

SET @COUNTER = 1
SET @FKCOLUMNS = ''
SET @PKCOLUMNS = ''

WHILE @@FETCH_STATUS = 0
BEGIN

IF @COUNTER > 1
BEGIN
SET @FKCOLUMNS = @FKCOLUMNS + ','
SET @PKCOLUMNS = @PKCOLUMNS + ','
END

SET @FKCOLUMNS = @FKCOLUMNS + '[' + @FKCOLUMN_NAME + ']'
SET @PKCOLUMNS = @PKCOLUMNS + '[' + @PKCOLUMN_NAME + ']'

SET @COUNTER = @COUNTER + 1

FETCH NEXT FROM cursor_fkeyCols INTO @FKCOLUMN_NAME,@PKCOLUMN_NAME
END

CLOSE cursor_fkeyCols
DEALLOCATE cursor_fkeyCols

-- generate create FK statement
SET @cmd = 'ALTER TABLE [' + @FKTABLE_OWNER + '].[' + @FKTABLE_NAME + '] WITH ' +
CASE @FK_DISABLED
WHEN 0 THEN ' CHECK '
WHEN 1 THEN ' NOCHECK '
END + ' ADD CONSTRAINT [' + @FK_NAME
+ '] FOREIGN KEY (' + @FKCOLUMNS
+ ') REFERENCES [' + @PKTABLE_OWNER + '].[' + @PKTABLE_NAME + '] ('
+ @PKCOLUMNS + ') ON UPDATE ' +
CASE @UPDATE_RULE
WHEN 0 THEN ' NO ACTION '
WHEN 1 THEN ' CASCADE '
WHEN 2 THEN ' SET_NULL '
END + ' ON DELETE ' +
CASE @DELETE_RULE
WHEN 0 THEN ' NO ACTION '
WHEN 1 THEN ' CASCADE '
WHEN 2 THEN ' SET_NULL '
END + '' +
CASE @FK_NOT_FOR_REPLICATION
WHEN 0 THEN ''
WHEN 1 THEN ' NOT FOR REPLICATION '
END

PRINT @cmd

END

FETCH NEXT FROM cursor_fkeys
INTO @FK_NAME,@FK_OBJECTID,
@FK_DISABLED,
@FK_NOT_FOR_REPLICATION,
@DELETE_RULE,
@UPDATE_RULE,
@FKTABLE_NAME,
@FKTABLE_OWNER,
@PKTABLE_NAME,
@PKTABLE_OWNER
END

CLOSE cursor_fkeys
DEALLOCATE cursor_fkeys

How to create a case-sensitive instance of SQL Server 2000 and How can I make my SQL queries case sensitive?

How to create a case-sensitive instance of SQL Server 2000 and How can I make my SQL queries case sensitive?

1. How to create a case-sensitive instance of SQL Server 2000
To create a case-sensitive instance of SQL Server 2000 follow the bellow steps at the time of installation of SQL Server Setup:

1.1 Run SQL Server Setup to install SQL Server 2000 Components, select Install Database Server, and then click Next at the Welcome screen of the SQL Server Installation Wizard.

1.2 In Computer Name dialog box, Local Computer is the default option and the local computer name appears in the edit box. Click Next.

1.3 In the Installation Selection dialog box, click click Create a new instance of SQL Server, or install Client Tools, and then click Next.

1.4 Follow the directions on the User Information and related screens.

1.5 In the Installation Definition dialog box, click Server and Client Tools, and then click Next.

1.6 In the Instance Name dialog box:
To create a case-sensitive default instance, accept the Default check box and click Next.

1.7 To create a case-sensitive named instance, clear the Default check box and type an instance name.
In the Setup Type dialog box, click Custom, and click Next.

1.8 In the Select Components, Services Accounts, and Authentication Mode dialog boxes, change or accept the default settings, and then click Next.

1.9 Security Note When possible, use Windows Authentication.

1.10 In the Collation Settings dialog box, you have two options:
To make a Windows Locale collation case-sensitive, select Collation designator and then select the correct collation designator from the list. Clear the Binary check box, and then select the Case-sensitive check box.

1.11 To make a SQL collation case-sensitive, select SQL Collations, and then select the correct collation name.
For more information about collation options, click Help. When you finish setting the options, click Next.

1.12 In subsequent dialog boxes, change or accept the default settings, and then click Next.

1.13 When you are finished specifying options, click Next in the Start Copying Files dialog box.

1.14 In the Choose Licensing Mode dialog box, make selections according to your license agreement, and click Continue to begin the installation.

1.15 Click Help for information about licensing, or see your system administrator.

2. How can I make my SQL queries case sensitive?

If you installed SQL Server with the default collation options, you might find that the following queries return the same results:

CREATE TABLE mytable
(
mycolumn VARCHAR(10)
)
GO

SET NOCOUNT ON

INSERT mytable VALUES('Case')
GO

SELECT mycolumn FROM mytable WHERE mycolumn='Case'
SELECT mycolumn FROM mytable WHERE mycolumn='caSE'
SELECT mycolumn FROM mytable WHERE mycolumn='case'

You can alter your query by forcing collation at the column level:

SELECT myColumn FROM myTable
WHERE myColumn COLLATE Latin1_General_CS_AS = 'caSE'

SELECT myColumn FROM myTable
WHERE myColumn COLLATE Latin1_General_CS_AS = 'case'

SELECT myColumn FROM myTable
WHERE myColumn COLLATE Latin1_General_CS_AS = 'Case'

-- if myColumn has an index, you will likely benefit by adding
-- AND myColumn = 'case'

If you want to do this in a more global way, instead of modifying each individual query, you can force the collation at the database level, or at the column level, using the ALTER DATABASE and ALTER TABLE commands, respectively. You can see the current collation level on the properties tab of the database server, through Enterprise Manager (if you're going to change this setting, MAKE NOTE OF THIS VALUE):

And you can see the description from running the following query:

SELECT DATABASEPROPERTYEX('', 'Collation')

As changing this setting can impact applications and SQL queries, I would isolate this test first. In SQL Server 2000, you can easily run an ALTER TABLE statement to change the sort order of a specific column, forcing it to be case sensitive. First, execute the following query to determine what you need to change it back to:

EXEC sp_help 'mytable'

The second recordset should contain the following information, in a default scenario:

Column_Name Collation
----------- ----------------------------------------------
mycolumn SQL_Latin1_General_CP1_CI_AS

Whatever the 'Collation' column returns, you now know what you need to change it back to after you make the following change, which will force case sensitivity:

ALTER TABLE mytable
ALTER COLUMN mycolumn VARCHAR(10)
COLLATE Latin1_General_CS_AS
GO

SELECT mycolumn FROM mytable WHERE mycolumn='Case'
SELECT mycolumn FROM mytable WHERE mycolumn='caSE'
SELECT mycolumn FROM mytable WHERE mycolumn='case'

If this screws things up, you can change it back, simply by issuing a new ALTER TABLE statement (be sure to replace my COLLATE identifier with the one you found previously):

ALTER TABLE mytable
ALTER COLUMN mycolumn VARCHAR(10)
COLLATE SQL_Latin1_General_CP1_CI_AS

If you are stuck with SQL Server 7.0, you can try this workaround, which might be a little more of a performance hit (you should only get a result for the FIRST match):

SELECT mycolumn FROM mytable WHERE
mycolumn = 'case' AND
CAST(mycolumn AS VARBINARY(10)) = CAST('Case' AS VARBINARY(10))

SELECT mycolumn FROM mytable WHERE
mycolumn = 'case' AND
CAST(mycolumn AS VARBINARY(10)) = CAST('caSE' AS VARBINARY(10))

SELECT mycolumn FROM mytable WHERE
mycolumn = 'case' AND
CAST(mycolumn AS VARBINARY(10)) = CAST('case' AS VARBINARY(10))

-- if myColumn has an index, you will likely benefit by adding
-- AND myColumn = 'case'