Categories
Database MS SQL Server SQL

Case Sensitive Search in SQL Server

Mysql mostly provide a Case sensitive search because of its default collate configuration. However SQL server use different collate and hence by default it is not case sensitive. It is good to not having case sensitive search by default, as except password compare you most often doesn’t need case sensitive search. Do you? well I never really need that, anyways. Those who wonder how they can get case sensitive search should look this good article

http://blog.sqlauthority.com/2007/04/30/case-sensitive-sql-query-search/

It simply case to change the collate value of column for which you want to get case sensitivity, by defining COLLATE Latin1_General_CS_AS to field declaration.

You can check existing collate of each field using 

EXEC sp_help [tablename]
Alter table [tablename] Alter Column Password Varchar(32) COLLATE Latin1_General_CS_AS

However why are you wondering for case sensitive for password field? aren’t you doing MD5 or other conversion on your password for security already ? :). 

Categories
Database MS SQL Server SQL

Reseed Auto Increment Value in SQL Server

You can reseed the Auto increment field to desire number by using following SQL statement [I think it is specific for SQL Server only] other Database have their own methods.

DBCC CHECKIDENT (<TableName>, RESEED, 0)

Works great for me on Sql Server 2008

Categories
Links

WordPress on SQL Server

http://wordpress.visitmix.com/ enables you to use wordpress software with SQL server instead of MySQL. Not sure if it is a good move or bad as SQL server are expensive to get and how many BIG fish are actually going to use it ? But this is good technical Advancement. Keep it up guys.

Categories
.NET SQL

Pagination Query for SQL Server

In recent past, we got few problem when we need to do Pagination with SQL Server. I specially got frustrated when I saw that MySQL server has “limit” word that does pagination work in MySQL in best known manner for me, BUT microsoft didn’t consider that option yet. But today I found a method for SQL server that does the pagination in similar way as MySQL limit works. I am not very sure if it runs on SQL Server 2000 or earlier but it runs on SQL Server 2005. The magic word here is “ROW_NUMBER()” function that provides a Serial No. kind of Numbering to each record. So to do paging all you need to do is, make Query calling ROW_NUMBER() and you are done.

Here is sample of such query

SELECT * FROM
    (SELECT ROW_NUMBER()
        OVER (ORDER BY EmployeeName) AS Row,
        EmployeeId, EmployeeName, Salary
    FROM Employees) AS EMP
WHERE Row BETWEEN 2 AND 4

I took this sample and knowledge from a great resources for .NET developer. Website URL is http://www.openwinforms.com/row_number_to_sql_select.html

Hope you like the solution. Any alternative is welcome.