Intellisense update/refresh on schema changes - SQL Server

The intellisense in SQL Server 2008 is a great feature but sometimes you might get into trouble with it. As an example, If you make any change like creating a new table or column name change etc, while SSMS is open those changes are not reflected in current or even new query editor window in that instance of SSMS.

If you close the instance and launch SSMS again then you can see the changes. So for every change you don't want to close and re-open. So here is the tip, Just refresh your local cache then it will work.

You can refresh you local cache by going to Edit->IntelliSense->Refresh Local Cache or simple using short cut (CTRL+Shift+R)

Reference : Vijaya Kadiyala (www.DotNetVJ.com)

Get the records nearest to GETDATE() in SQL Server

In this post i would like to show you different ways to find the rows nearest to the GetDate() in SQL Server.

Method 1, is based on calculating the difference interims of days between GetDate() and HireDate and then take the absolute number using ABS. Order these results in ascending (ASC)order by using ORDER BY Clauase. Finally, filter the results using TOP 1 WITH TIES. WITH TIES is used to select all the records when there is a tie.

Method 1:
=======

SELECT TOP 1 WITH TIES
BusinessEntityID,
LoginID,
HireDate,
GETDATE() AS [Current_Date]
FROM HumanResources.Employee
ORDER BY ABS(DATEDIFF(DAY,GETDATE(),HireDate));

Method 2, is based on using Group Functions. Where MAX function is used to take the recent date and based on this date extracted all the other information.

Method 2:
=======

SELECT
BusinessEntityID,
LoginID,
HireDate
FROM HumanResources.Employee
WHERE HireDate = (SELECT MAX(HireDate) FROM HumanResources.Employee)

Method 3, is based on Analytical Functions in SQL Server. Where RANK function is used to
along with the OVER BY to calculate the difference between GetDate and HireDate. This logic is same as in Method 1.

Method 3:
=======

SELECT V.* FROM
(SELECT
BusinessEntityID,
LoginID,
HireDate,
RANK() OVER (ORDER BY ABS(DATEDIFF(DAY,GETDATE(),HireDate))) AS RecID
FROM HumanResources.Employee ) V
WHERE V.RecID = 1

Please let me know if you have any other ways to wirte these queries.....

Reference : Vijaya Kadiyala (http://www.dotnetvj.com/)

DROP INDEX - Must specify the table name and index name for the DROP INDEX statement

In this post i would like to show you, how to drop an index in SQL Server.

If you came from different database backgrounds like Oracle or DB2, then you will issue the following statement and expect to execute without errors.

DROP INDEX INDEX_NAME;

This will not work in SQL Server. You need to specify the on which table this index is defined.
When you execute the above statement you will get below error:

Msg 159, Level 15, State 1, Line 1
Must specify the table name and index name for the DROP INDEX statement.

The right way to drop an index is:

DROP INDEX INDEX_NAME ON TABLE_NAME;

Related Articles:
Clustered Index in SQL Server
Non Clustered Index in SQL Server
Covering Index in SQL Server
Filtered indexes in SQL Server 2008

Reference: Vijaya Kadiyala (www.DotNetVJ.com)

When to use fully qualified names in SQL Server

In Sql Server each and every object name has four parts.
1. Server Name
2. Database Name
3. Owner Name
4. Object Name

If you quality Object Name with Server Name, Database Name and Owner then it is referred as Fully Qualified Name.

Some people believes that if you fully qualify the object name it will improve the performance. But its not true. Depends on the need you need to make a right choice. As an example if your application is using one/single database always then you can write queries as shown below:

SELECT * FROM HumanResources.Shift

Sometimes owner name is also referred as Schema. If you take AdventureWorks2008 database, then there are various schemas like HumanResources, Sales, Production, Person etc.

If you have multiple databases and your queries always joins two or more tables from two or more different databases then it will be good if you qualify with database name as shown below:

SELECT * FROM
AdventureWorks2008.HumanResources.Shift

SELECT * FROM
VJ_LCL.dbo.emp

VJ_LCL is my local database and the schema owner of emp table is dbo. Similarly AdventureWorks2008 is database and HumanResources is the schema name and Shift is the table name. Rememeber, object name has to be unique within each schema.

On the other hand, if you have queries that has joins between two or more tables from two or more different servers then you need to fully qualify the object name as show below:

SELECT * FROM
VJMSSQLServer.AdventureWorks2008.HumanResources.Shift

If you fully qualify each and every object even when you have single database then you might get into following problems:

1) If you are planning to change the database, your code will break
2) If you are planning to change the server or deploying it on another server then your code will break.

Reference: Vijaya Kadiyala (http://DotNetVJ.com)

Getting the Server Name in SQL Server using @@SERVERNAME

In this post i would like to give you a simple tip on how to get the Server Name of the SQL server.

In order to get the SQL Server - Server name you need to use Global Variable as shown below.

SELECT @@SERVERNAME AS Server_Name

Get Current date and time in SQL Server using CURRENT_TIMESTAMP, GETDATE(), SYSDATETIME()

In this post i would like to discuss with you about displaying current date and time in SQL Server.

CURRENT_TIMESTAMP is ANSI compliant. This value is based on the operating system where the SQL Server instance is running. This function is nondeterministic function.


GETDATE() is inherited from sybase.



SYSDATETIME(), this value is based on the operating system where the SQL Server instance is running. This function is nondeterministic function. The return type of this function is datetime2. So this is more accurate than any other time based function.


In addition to this there is another way to display the current date and time.


The {fn Now()} is an ODBC canonical function which can be used in T-SQL. There is no performance difference between any of these functions.


SELECT SYSDATETIME() AS "SYSDATETIME",
CURRENT_TIMESTAMP AS "CURRENT_TIMESTAMP",
GETDATE() AS "GETDATE",
{fn NOW()} AS "fn_now()"