Skip to content

Month name from month number

December 13, 2010
At times, we have to get the month name and all that we have is a month number, here is a quick smart way to get the name of the month corresponding to the month number:
DECLARE @month int
SET @month = 4
SELECT DateName(mm,DATEADD(mm,@month,-1))  Month
To get the list of all the months spelled our along with their corresponding month numbers:
SELECT Number + 1 as [Month Number],
DateName(mm,DATEADD(mm,Number,0)) as [Month Name]
FROM master..spt_values
WHERE Type = ‘P’ and Number < 12

August 18, 2010

List tables with Identity Insert on | off

CREATE TABLE #temp
(
Has_Identity nvarchar(max),
Table_Name nvarchar(200)
)

DECLARE @sql nvarchar(2000)
SET @sql = ‘select  objectproperty(object_ID(”?”)  ,”TableHasIdentity”),”?”’
INSERT INTO #temp
EXEC sp_MSforeachtable
@sql
SELECT CASE Has_Identity WHEN 0 THEN ‘NO’ WHEN 1 THEN ‘YES’ END AS Has_Idenity, Table_Name
FROM   #temp
DROP TABLE #temp

Date time in sql 2008

December 29, 2009
tags:

The new Date and TIME datatypes do not support the + and  operators with integers. They also do not support casting to DATE Time from Integer.
This means that

DECLARE @dt date
SET @dt=GETDATE()
SET @dt=@dt+1
SELECT @dt

does not work and it gives the following  error:
“Msg 206, Level 16, State 2, Line 3
Operand type clash: date is incompatible with int

One has to strictly use the DateDiff() function for all the date additions and substractions.
So if you are currently working on sql 2005 and plan to migrate to sql 2008, it would be a good idea to follow the best practices.

A day at work

September 17, 2009

At one of the clients, a sql server express box had crashed. Before the crash, sql server express was previously installed on that box, the IT dept took insanely long time to fix the machine and after they got the machine back, sql server service could not start.
Was getting following error:

“The log scan number (97:224:1) passed to log scan in database ‘model’ is not valid. This error may indicate data corruption or that the log file (.ldf) does not match the data file (.mdf)”

Which meant that the Model database log file was either corrupt or was inconsistent.
The solution to this problem was to rebuild the log for model database. To do this I had to start SQL server together with -c option, the – m option and the trace flag 3608.
Trace Flag 3608 prevents SQL server from recovering any database except the master database.
But when I tried to start sql server using start sqlservr.exe -c -T3608
Got the following error:

“Your SQL server is either corrupt or has been tampered with (Error getting instance ID from name). Please uninstall then re-run setup to correct the problem.”

In short it was all screwed up. The IT dept had done something with the registry settings. Also I think the machine had been renamed. Before reinstalling sql server, I wanted to try this: http://support.microsoft.com/default.aspx?scid=kb;en-us;290301.

But time was of essence and I had the data and log files for the database in question also had the latest backup so decided to go ahead with the reinstall.
(I would just like to mention that this was a machine at a very remote location in some village with very poor connectivity.)

The saga doesn’t end here, when I started the installation, after completing half of the installation got the following error:

“SQL Server Setup could not connect to the database service for server configuration. The error was: [Microsoft][SQL Native Client]Encryption not supported on the client. Refer to server error logs and setup logs for more information. For details on how to view setup logs, see “How to View Setup Log Files” in SQL Server Books Online.”

I had already uninstalled sql server from the machine. After some research, the problem boiled down to the SQL Native Client, which  I had not removed from the machine. Many times even after uninstalling native client people face this error. One work around to this problem is to install sql native client and import the registry keys. Here is a detailed discussion of the same.
My case was simpler, I just had to remove the native client and voila! Installation went very smooth. After that it was just a question of re attaching the data files and we were back in business.

Predict table size

August 24, 2009

Person Table

Column Name

Data Type

PersonID

Int

LastName

Nvarchar(30)

FirstName

Nvarchar(30)

Address

Nvarchar(50)

City

Varchar(15)

State

Char(2)

Zip

Char(10)

  1. Find the size of one row : 4+30+30+50+15+2+10 = 141 bytes
  2. We always calculate the max size of the row in the table.

  3. Find the number of rows that will fit on a single page:
    1. Remember the size of the page is 8096 bytes (without the page header size).
    2. So; size of page / size of one row = number of rows that fit on one page
  4. 8096/ 141 = 57.4184 round it down to 57 as we have to contain every row in one page.

  5. Divide the total number of rows that you think we will have in the table by the result of step 2.
    1. Assuming that we will have 1,000,000 records in our persons table:
    1. We are rounding it up to a nearest integer because any partial row is going to be moved to a new page.
    1. This is our number of pages : 17544
  6. 1000000/57 = 17543.8596 round it up to 17544.

  7. Now multiply the results from step 3 above by 8192 (including page header size) to get the size estimate in bytes; and we get:
  8. 17544 * 8192=143720448 bytes

    To get the size in MB:

    143720448 /(1024*1024) = 137.0625 MB

    So for a person table with 1000,000 records the size estimate is 137 MB.

Get All triggers on a table

June 11, 2009

Many times when we try to debug a piece of code, the erractic code is in the triggers.
Here is a simple query to find all the triggers present in the database:

SELECT sta.name AS Table_Name,
t.name Trigger_Name,
ste.Type_desc Trigger_Type,
CASE
WHEN t.is_Disabled = 1 THEN ‘Yes’
ELSE ‘No’
END AS IS_Trigger_Disabled,
CASE
WHEN t.is_instead_of_trigger = 1 THEN ‘Yes’
ELSE ‘No’
END AS Is_Instead_of_Trigger,
t.Create_Date,
t.Modify_Date
FROM   sys.triggers t,
sys.trigger_events ste,
sys.tables sta
WHERE  t.object_id = ste.object_id
AND  sta.object_ID = t.parent_ID

I AM MCTS NOW !!

June 3, 2009

A bit late to post, but I am happy to say that I am a Microsoft Certified Technology Specialist!
Cleared 70-431 on 21st March 09.

Follow

Get every new post delivered to your Inbox.