Feeds:
Posts
Comments

Archive for the ‘Tips & Tricks’ Category

SQL Server assigns each object (tables, view, stored procedure, functions etc) a unique ID and that ID we call as an Object_ID. In simple words, we can say that it creates uniqueness between two objects.
In this article, we will learn how it works, what is the next counter, etc.

What will be the first object_ID in the new database ?
If you create a new object in the database, the first ID will always be 2073058421 in SQL SERVER 2005 and 245575913 in SQL SERVER 2012.
The difference between both versions is due to some new internal tables introduced in SQL SERVER 2012 and got the new ID 2073058421 (filestream_tombstone_2073058421) once you create a database in 2012.

How to get this Object_ID from SQL ?
You just need to use a function OBJECT_ID, to retrieve object ID of any object. For example :

Select Object_ID('test_table');

What will be the object_ID increment counter for user defined objects ?
It will add 16000057 + Last user defined object_ID and will give you a new ID.

What will be the object_ID increment counter for system objects ?
It varies from 1 onwards.

If we drop an object and create the same object, does SQL assign the same object_ID ?
No, SQL will assign a new ID every time.

Why some object IDs are in negative and some IDs are in positive ?

select * from sys.all_objects

If you execute above query, you can see Some IDs are in positive and some are in negative. All user defined objects are assigned a positive object ID plus system tables. Apart from these objects, the rest of the system objects are assigned negative object IDs.

All the above facts and figures can vary in different versions of SQL. If you know something new about Object_ID do share with me.

Read Full Post »

I was working on statistical reports and I had to display whether the given year was leap year or not in a field. So, I developed few ways using SQL SERVER 2012 functions to detect whether leap year or not.

Given below are the multiple ways to detect leap year:

Method 1 :
In method 1, I made a date (2012-02-28) using DATEFROMPARTS function then used EOMONTH function to find the last date (2012-02-29) of February. Then to find the last day (29), used DAY function and after that used IIF to check whether it is 29 or not, to detect if its a leap year or not.

DECLARE @Year INT =2012
SELECT IIF(DAY(EOMONTH(DATEFROMPARTS(@Year,2,28)))=29 ,'YES','NO') AS [LEAP YEAR]
--RESULT

LEAP YEAR
———
YES

(1 row(s) affected)
Method 2 :
In method 2, I simply made 2 dates using DATEFROMPARTS function. First is the 1st date of the year and 2nd date is the last date of the year and then used DATEDIFF function to find total no of days in a year and finally used IIF function to check if it is 366 or 365 days. If 366, then leap year and if 365 then it is not.

DECLARE @Year INT =2012
SELECT IIF(DATEDIFF(DAY,DATEFROMPARTS(@Year,1,1), DATEFROMPARTS(@year,12,31))+1=366 ,'YES','NO') AS [LEAP YEAR]
--RESULT

LEAP YEAR
———
YES

(1 row(s) affected)
Method 3 :
In method 3, I first made a date(2012-02-28) using DATEFROMPARTS function then added one day to it. After addition I took the day and checked it using IIF function. If it is 29, then leap year, else, it is not.

DECLARE @Year INT =2012
SELECT IIF(DAY(DATEADD(DAY,1,DATEFROMPARTS(@Year,2,28)))=29 ,'YES','NO') AS [LEAP YEAR]
--RESULT

LEAP YEAR
———
YES

(1 row(s) affected)

Method 4 :
In method 4, I made 2 dates (2012-02-28, 2012-03-01) using DATEFROMPARTS function and then found the date difference using DATEDIFF function, and finally used IIF function to check the difference. If it is 2 days, means the year is leap, else not.

DECLARE @Year INT =2012
SELECT IIF(DATEDIFF(DAY,DATEFROMPARTS(@Year,2,28), DATEFROMPARTS(@year,3,01))=2 ,'YES','NO') AS [LEAP YEAR]
--RESULT

LEAP YEAR
———
YES

(1 row(s) affected)

Note : Try @Year as 2013 and all the result set will return that 2013 is not a leap year using any above methods.

Let me know if you know a better method to detect leap year.

Read Full Post »

Web browser has been launched in sql server since SQL Server 2005. And it is available in Menu (View >> Other windows >> Web Browser). And this sql server browser uses the same internet explorer setting on windows server except home page. Whatever home page you setup in windows internet explorer, it will not impact in SQL server web browser settings.

This functionality is same in all sql server from SQL server 2005 and above. In addition to this, SQL Server 2012 provided a functionality to set up internet explorer setting from SQL Server as well and you can setup separate home page for SQL SERVER browser also.

Given below is the path to configure this setting in SQL Server 2012.
Tools >> Options >> Environment >> Web browser

ie_settings1.1

Read Full Post »

SQL server 2012 introduced a new feature namely “Surrounded With” that can reduce the development time and increase the accuracy level in SQL SERVER.

In the previous version of SQL server (2005 & 2008), if we had to do a customization/development in any procedure/function and add few conditions in it, we had to first insert the condition with BEGIN in a certain place and then had to insert an END at an appropriate place.
If both were placed wrongly, then script would not work properly.

The solution is now available in SQL Server 2012 and it is very simple.
Let me explain it Step by Step.

Step 1 :

Write the code where you need to place “If condition , While condition or Begin End” .

Step 2 :

Select the code and right click and select “Surround With”

Step 3 :

Select the appropriate condition either it is “If condition”,“while condition” or “Begin End”.

Step 4 :

SQL server automatically generate the appropriate code in appropriate place, Now just write the condition and run the code.

Let me know if this technique is helpful for you.

Read Full Post »

« Newer Posts