A SELECT statement with TOP clause options

TOP clause can limits the numbers of rows return by the SELECT query .There are three options used with TOP clause in the SELECT Query:

1. TOP with number of rows
2. TOP with percentage of rows
3. TOP with ties

So let's see these examples to demonstrate the options:

USE AdventureWorks
GO

-- To return only 2 rows from the result set 
SELECT TOP 2 Name , ProductNumber ,ListPrice
FROM Production.Product
ORDER BY ListPrice

--TO return only the 2% from the result set 
SELECT TOP 2 PERCENT Name , ProductNumber ,ListPrice
FROM Production.Product
ORDER BY ListPrice

-- TO return only 2 rows from the result set 
-- plus : extra rows will returned if their values
-- (Based on ORDER BY column) match the vlaue of last row
SELECT TOP 2 WITH TIES Name , ProductNumber ,ListPrice
FROM Production.Product
ORDER BY ListPrice DESC

Attach MDF file in case of missing LDF file

In case you have a database with .mdf file and without having the .ldf file of a database so to restore your database or reattach it again do the following:
First open SSMS and right click in Database folder in object explorer and choose Attach….

Then click on Add button to add .mdf file of a database you need to attach it as the following

Note the Message column of .ldf file shows (Not Found) so select this row and then remove it as the following

Then press OK to reattach your database with new .ldf file.
Or use the following command to attach database file with only .mdf file:

USE [master]
GO
CREATE DATABASE [TestDb] ON 
( FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10.SQL2008\MSSQL\DATA\TestDb.mdf' )
 FOR ATTACH
GO

and you will get the following result:
File activation failure. The physical file name "C:\Program Files\Microsoft SQL Server\MSSQL10.SQL2008\MSSQL\DATA\TestDb_log.ldf" may be incorrect.

New log file 'C:\Program Files\Microsoft SQL Server\MSSQL10.SQL2008\MSSQL\DATA\TestDb_log.LDF' was created.

SQL Server - Case Sensivity and Accent sensivity determined by a Collation

Sql server 2005/2008 support Unicode characters so you can present your data in any language so for example if your Database collation is (a Latin) and you want to store Arabic language words you need to define a Unicode data type like(nchar,nvarchar,ntext) to stored your data properly and to avoid the question marks problem (???) except if your database collation is Arabic and in this case you does not need to define your columns as a Unicode type you can use the type like(char,varchar,text) to store Arabic language because in this case the collation is responsible to display your character language.
Note:
SQL Server 2005/2008 allows you to make collation choices at the server, database, column, and expression level.
Case Sensivity and accent sensivity determined by a Collation so let's see how we can define the case sensitive and accent sensitive by using collation for two main languages Arabic and English.

--This example show the Case sensitive for English Language
DECLARE @Name1 varchar(25) ='SQL SERVER' 
       ,@Name2 varchar(25) ='sql server' 

--Here do not consider the Case sensitive        
IF(@Name1 COLLATE SQL_Latin1_General_Cp1_CI_AS = @Name2 COLLATE SQL_Latin1_General_Cp1_CI_AS)
    RAISERROR('They are %s',10,1,'the same')
ELSE
    RAISERROR('They are %s',10,0,'not the same')

--Here do consider the Case sensitive
IF(@Name1 COLLATE SQL_Latin1_General_Cp1_CS_AS = @Name2 COLLATE SQL_Latin1_General_Cp1_CS_AS)
    RAISERROR('They are %s',10,1,'the same')
ELSE
    RAISERROR('They are %s',10,0,'not the same')

the result of the above script as follwoing:
They are the same
They are not the same

--This example show the Accent Sensitive for Arabic language
DECLARE @Name3 nvarchar(25) ='أ' 
       ,@Name4 nvarchar(25) ='آ' -- try to change it to ؤ – ئ – إ –ء-

--Here do not consider the Accent Sensitivity       
IF(@Name3 COLLATE Arabic_CS_AI= @Name4 COLLATE Arabic_CS_AI)
    RAISERROR('They are %s',10,1,'the same')
ELSE
    RAISERROR('They are %s',10,0,'not the same')

--Here do consider the Accent Sensitivity
IF(@Name3 COLLATE Arabic_CS_AS= @Name4 COLLATE Arabic_CS_AS)
    RAISERROR('They are %s',10,1,'the same')
ELSE
    RAISERROR('They are %s',10,0,'not the same')

the result of the above script as follwoing:
They are the same
They are not the same

SQL Server and short tips – 3

• Sql server Express 2005/2008 utilizes max 1 GB for CPU as well as for Ram and 4 GB for max database size.
• statistics about SQL Server 2008 :
• Max Data storage per database (524272 TB)
• Max Concurrent User Connection (32767)
• Max Databases per server (32767)
• Max tables per database (2147483647)
• Max columns per table (1024)
• To hide the system database in Object Explorer: SSMS >Tools>Options>Hide system objects in Object Explorer then restart Sql Server.
• Keep in your administrative tools, Activity Monitor can offer to you the Recent Expensive queries.
• To see how many pages ,extents and the extent Switches in your database tables use the following command: DBCC SHOWCONTIG
• Case sensitivity in SQL Server:

--SQL Server 2005/2008 allows you to make collation choices 
--at the server, database, column, and expression levels
--Case Sensivity determined by Collation
--Oracle, by default is case sensitive whereas 
--SQL Server installations by default are case-insensitive

DECLARE @Name1 varchar(25) ='SQL SERVER' 
       ,@Name2 varchar(25) ='sql server' 

--Here do not consider the Case Sensitivity       
IF(@Name1 COLLATE SQL_Latin1_General_Cp1_CI_AS= @Name2 COLLATE SQL_Latin1_General_Cp1_CI_AS)
    RAISERROR('They are %s',10,1,'the same')
ELSE
    RAISERROR('They are %s',10,0,'not the same')

--Here do consider the Case Sensitivity
IF(@Name1 COLLATE SQL_Latin1_General_Cp1_CS_AS= @Name2 COLLATE SQL_Latin1_General_Cp1_CS_AS)
    RAISERROR('They are %s',10,1,'the same')
ELSE
    RAISERROR('They are %s',10,0,'not the same')   
-------------------------------------------------------------------- 
DECLARE @Name3 nvarchar(25) ='أ' 
       ,@Name4 nvarchar(25) ='إ' --try to change it to ء – آ - ؤ -ئ

--Here do not consider the Accent Sensitivity       
IF(@Name3 COLLATE Arabic_CI_AI= @Name4 COLLATE Arabic_CI_AI)
    RAISERROR('They are %s',10,1,'the same')
ELSE
    RAISERROR('They are %s',10,0,'not the same')

--Here do consider the Accent Sensitivity
IF(@Name3 COLLATE Arabic_CS_AS= @Name4 COLLATE Arabic_CS_AS)
    RAISERROR('They are %s',10,1,'the same')
ELSE
    RAISERROR('They are %s',10,0,'not the same')

Cannot resolve collation conflict for column 1 in SELECT statement

My friend has faced this problem against some queries so the cause of this problem as described in above title related to the Collation.

He has a table with columns with different collation at Column level for example a Table called "Students" with "FirstName" and "LastName" columns and each of these columns has a different collation so the "FirstName with SQL_Latin1_General_CP1_CI_AI collation " and "LastName with Arabic collation" and when he write the following query he got an error:

SELECT FirstName + ' ' +LastName
FROM dbo.Students

The solution for this problem is easy so rewrite the above query as following:

SELECT FirstName + ' ' + LastName  COLLATE SQL_Latin1_General_CP1_CI_AI LastName
FROM dbo.Students 

As well as if you try to write a query as the following:

SELECT * FROM dbo.Students 
WHERE FirstName = LastName

You will get the following error:

Msg 468, Level 16, State 9, Line 1
Cannot resolve the collation conflict between "Arabic_CI_AS" and "SQL_Latin1_General_CP1_CI_AI" in the equal to operation

To fix it, rewrite the above query as following:

SELECT * FROM dbo.Students 
WHERE FirstName = LastName COLLATE SQL_Latin1_General_CP1_CI_AI

SQL Server and short tips – 2

• Sqlcmd utility comes in replacement of osql utility (with SQL 2000 version) and isql utility (with SQL 7.0 version) but still you can run osql with SQL Server 2005/2008.
• Sqlcmd used to reach the sql server instance from outside of SSMS or GUI for repetitive tasks or automate the execution of SQL scripts.
• To see all commands of sqlcmd use the following: sqlcmd /?
• You can access the instance of SQL Server as Administrator and only one connection allowed for troubleshoot problems even if the server does not responses to any client connections.

Let's see this example which passes a variable from the sqlcmd to sql script file

1. Create in c:\script.sql file
2. Write in this file the following command:

USE AdventureWorks
GO
SELECT $(ColumnName) FROM $(TableName)
GO

3. Open cmd window then run this command

sqlcmd -S .\sql2008 -E -i c:\script.sql -v ColumnName="ProductNumber" TableName="Production.Product"

And then press enter.

• You can shut down your instance (stop the instance service) by using the following command:

After connection has made, write the following:

Shutdown with nowait

• And last tip is a Security in Sql 2005/2008 is inherited in Hierarchical scope.