Windows Family vs Windows Server Family

We've been using Microsoft Windows since our childhood and we know there are several kinds of editions and versions in the Windows family.

The operating systems of Windows family are classified into two types,

• Client Operating Systems.
• Network Operating Systems.

Client Operating Systems

The following operating systems come under Client Operating Systems,

• Disk Operating System (DOS)
• Windows 95
• Windows 98
• Windows 2000
• Windows XP (includes XP1, XP2, XP3)
• Windows Vista
• Windows 7 (includes Home Basic, Home Premium, Professional, Ultimate etc.)
• Windows 8 (includes Windows 8.1)

These are designed for delivering standard consumer experience. They can be installed on a standalone computer as well as in a network infrastructure. They can work only as clients and cannot offer any services to other clients if they are installed in a network infrastructure. They are referred as lightweight operating systems and can run even with a minimal hardware configuration. Their license costs are less when compared to that of Network Operating Systems. They are designed with more graphics for the end users.

Network Operating Systems

The following Windows operating systems come under Network Operating Systems,

• Windows Server 2000
• Windows Server 2003
• Windows Server 2003 R2
• Windows Server 2008
• Windows Server 2008 R2
• Windows Server 2012
• Windows Server 2012 R2

These are designed only to be installed in a network infrastructure. Once installed in a network they can run and provide services to the clients. The difference between Network and Client operating systems are, Active Directory Services can be installed on network operating systems. The Active Directory Services authenticate all the clients in a Windows Domain Network and provide services to them. Through Active Directory, they define security policies and other authorities to all the clients present in that domain. These operating systems need high end hardware configuration like more number of processors, high amount of physical memory. Unlike Client Operating Systems they have minimal graphics in their user interface. These operating systems concentrate on background process mostly to ensure better services to the clients. Their license costs are more than that of client operating systems. These are used in professional production environments.

Delete Connection History in SSMS 2012

Connection History in SSMS 2012 is stored as a .bin file in the following location,

%appdata%\Microsoft\SQL Server Management Studio\11.0

Copy and paste the above path in RUN dialog box and open it. There you can find a file with name SqlStudio and of format .bin. Just delete that file and then open SSMS. Then you will find no connection history.

Case Sensitive Search in SQL Server

We all know that SQL is a case insensitive language. Yes, it is true. But we think that even in searching the data in database is also case insensitive. But that's not true. There lies a feature called COLLATION which contradicts our assumption.

Collation comes into action when we use ORDER BY clause. There are several collations which define language and sorting according to the alphabet of that language. The default collation we see in the English version of SQL Server on the English version of Microsoft Windows is Latin collation. As English is derived from Latin, the sorting of the English records in the database is performed according to the English alphabet. This Latin collation of two types, case sensitive and case insensitive. The default collation taken by SQL Server during its installation is Case Insensitive Latin collation. If case sensitive Latin collation is selected then that doesn't allow case insensitive searching of the records.

Let's see it practically...

USE AdventureWorks2012

GO

CREATE TABLE SENSITIVE_SEARCH (NAME VARCHAR(100))

GO

INSERT INTO SENSITIVE_SEARCH VALUES ('YASHWANTH'),('yashwanth'),('Yashwanth'),('yasHwanth')

GO

SELECT * FROM SENSITIVE_SEARCH

Now, I've created a table and inserted the same name in different styles.

Now let's do a search with a predicate,

SELECT * FROM SENSITIVE_SEARCH WHERE NAME = 'YASHWANTH'

See the result. It's same as above. Though you change the style of the predicate column value the result lies same.

To achieve case sensitive search, you need to change the collation. Collation can be changed at instance level, database level and column level. In this case, only for this table we need to achieve case sensitive search. So, changing the column's collation is enough.

Before changing the collation, let's know how to know collation at different levels...

1) To know the collation of the server through Object Explorer, right click on the instance name and select Properties.

Through T-SQL, collation can be known as,

SELECT SERVERPROPERTY('collation')

2) To know the collation of the database through Object Explorer, right click on the database and select Properties. Under Maintenance section, collation appears.

Through T-SQL, it can be known as,

SELECT DATABASEPROPERTYEX('AdventureWorks2012','collation')

or connect to the database and run this command

SELECT collation_name FROM sys.databases WHERE name = 'AdventureWorks2012'

3) To know the table collation through Object Explorer, right click on the Table and select Properties. Under Extended Properties, collation appears.

4) Collation of a column can be known through Object Explorer by right clicking on the column and selecting Properties.

Through T-SQL, it can be known as,

SELECT collation_name FROM sys.columns WHERE object_id = OBJECT_ID('SENSITIVE_SEARCH') AND name = 'NAME'

Now we need to change the collation of the column NAME in the table SENSITIVE_SEARCH. To facilitate case sensitive search, we need to choose a suitable collation. To know the available collations supported by SQL Server, run the following query.

SELECT * FROM sys.fn_helpcollations() WHERE name LIKE 'SQL%'

There are some characteristics to be noted while selecting a collation,

_CS - Case Sensitive

_CI - Case Insensitive

_AS - Accent Sensitive

_AI - Accent Insensitive

_KS - Kana Sensitive

_WS - Width Sensitive

In our present case, we require the following collation,

SQL_Latin1_General_CP1_CS_AS

To change the collation of our table's column to the above collation run the following command,

ALTER TABLE SENSITIVE_SEARCH ALTER COLUMN NAME VARCHAR(100) COLLATE SQL_Latin1_General_CP1_CS_AS

Now case sensitive search is enabled for the column NAME.

SELECT * FROM SENSITIVE_SEARCH WHERE NAME = 'YASHWANTH'

GO

SELECT * FROM SENSITIVE_SEARCH WHERE NAME = 'Yashwanth'

To know more about collations in SQL Server, follow the official documentation of Microsoft.

http://msdn.microsoft.com/en-us/library/ms143726.aspx