Archive for the ‘SQL Server’ category

Case Sensitive SQL

September 27, 2013

In SQL Server you can use COLLATE Latin1_General_CS_AS in your SQL to perform case sensitive queries.  However, if you need to do case sensitive queries you may want to consider altering the column so that it is case sensitive.  If that is not an option, here is how to use SQL.

How to select case sensitive:

select *
  from TableName
where FieldName = ‘xYz COLLATE Latin1_General_CS_AS

How to group by using case sensitive:

select FieldName COLLATE Latin1_General_CS_AS, count(*)
  from TableName
group by FieldName COLLATE Latin1_General_CS_AS

Advertisements

How to Stop a query from Auto Committing in Microsoft SQL Server Management Studio.

August 23, 2010

By default Microsoft SQL Server Management Studio Query (Query Analyzer) auto commits leaving no option to rollback.  I had been very use to this and preferred it this way, however, recently I had been working in an environment with different tools where explicit commit was the preferred way and so now I want the flexibility of both in SQL Server as well.

One option is to turn on implicit transactions at the beginning of your query using  “set implicit_transactions on”.  When this is on, you will still see all of the updated or inserted row counts when executing your query, but you will have the option to rollback or commit.

Another way is to turn on implicit transactions by default within SQL Server Management Studio.

  1. Tools –> Options
  2. Expand Query Execution –> SQL Server
  3. Select ANSI
  4. Either Check SET IMPLICIT_TRANSACTIONS or SET ANSI_DEFAULTS

JUST BECAREFUL.  Don’t leave a transaction open because it may end up blocking other users.  Always make sure to commit or rollback.

MSDN Ref – http://msdn.microsoft.com/en-us/library/ms187807.aspx

SQL Server 2005 – Moving System Databases

June 7, 2008

I have recently had SQL Server 2005 setup by a web host, however, they kept the system databases on the C: drive, which in my book is a no no.  I am not a DBA so I don’t know everything but my main reasons for moving these databases are:

1) For performance, you want your data(.mdf) and log(.ldf) files on separate drives.
2) Due to limited space on the C: drive, these files need room to grow.  Especially tempdb.
3) Keeping the house clean.  It is nice to have all the server files that need to be backed up in a single spot. (i.e. web site, email, database, etc.)

Moving these databases is not a trivial task.  There does appear to be a little bit of work that could be error prone if done manually.  So, it was very nice to stumble across an article by Vince Iacoboni who describes the process and provides a script to make the change.  The great thing about Vince is he is active in the discussion board regarding his article and script.

Moving the SQL 2005 System Databases