Posts Tagged ‘ sqlserver 2005 ’

Windows Authentication in SQL Server 2005

Few days earlier, I came to learn some very basic login stuffs while working on Windows Authentication Mode of SQL Server. My task was to disable the Windows Authentication Mode. This is really very simple but it may irritate you if you are not informed of following facts.

First of all, you can’t disable or drop [BUILTIN/Administrators] or [ComputerName/UserName] Windows Credentials if you are logged in to SQL Server using this account.
Continue reading

Advertisements

APPLY Table Operator in SQL Server 2005

APPLY Table Operator is available in SQL Server 2005 and later editions. This evaluates the right hand-side table expression to left hand-side table expression. Other way, it correlates each row of left-hand side table expression to right hand-side table expression.

Let’s try to make it clear by coding. You can use NorthWind, AdventureWorks or download the database here to run those scripts.
Continue reading

MySQL “LIMIT” Equivalent in SQL Server 2005

First of all, there is no direct replacement of MySQL “LIMIT” in SQL Server. Let’s have a look at syntax for “LIMIT” in MySQL Server.

SELECT *
FROM [TABLE NAME]
ORDER BY [ORDER BY COLUMN NAME]
LIMIT 10 OFFSET 100

This query will select 101 to 110 th rows of data in MySQL. In SQL Server “TOP” can be used to select fixed number of rows from a Table or View.
Continue reading

Padding in SQL Server 2005

SQL Server built-in function RIGHT can be used to format output with leading zeros or other character. It is used with or without REPLICATE function. However, RIGHT function expects string type argument not numeric. If you are in a situation that you need to use numeric value for padding, just convert it to string.

The following are the simplest use of RIGHT with and without REPLCATE.


SELECT RIGHT(‘000000’+ ‘1’, 6)
SELECT RIGHT(REPLICATE(‘000000’,6)+ ‘1’,6)


Here is the output.



This GetApplicantId function generates a formatted ApplicantId using @ApplicationDate, @PositionCode, @ApplicantIdentity.


CREATE FUNCTION GetApplicantId(@ApplicationDate datetime, @PositionCode int,@ApplicantIdentity int )
RETURNS varchar(20)
AS
BEGIN
DECLARE @ApplicantId varchar(20);
SELECT @ApplicantId =
right(‘0000’ + CONVERT(varchar(4),@PositionCode),4) + CONVERT(varchar,@ApplicationDate,112)
SET @ApplicantId = @ApplicantId + ‘-‘
SELECT @ApplicantId = @ApplicantId + right(‘000000’ + CONVERT (varchar(7),@ApplicantIdentity),7)
RETURN @ApplicantId
END


You can use it in query or elsewhere in your application.


SELECT dbo.GetApplicantId(GETDATE(),1,1) as ApplicantId


Thanks
A Rahim Khan

Database BackUp & Restore Commands in SQL Server 2005

Sometimes, Database backup and restore using SQL Command is proved handy.

First of all, taking backup of any specific database to your desired location is very easy using this simple SQL command.

Moreover, you can retrieve logical name from Database backup file.

Finally, this code snippet shows how to restore database and save database files in default location or even in your desired location.

Thanks
A Rahim Khan