List tables with Identity Insert on | off
CREATE TABLE #temp
DECLARE @sql nvarchar(2000)
SET @sql = ‘select objectproperty(object_ID(”?”) ,”TableHasIdentity”),”?”’
INSERT INTO #temp
SELECT CASE Has_Identity WHEN 0 THEN ‘NO’ WHEN 1 THEN ‘YES’ END AS Has_Idenity, Table_Name
DROP TABLE #temp
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
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.
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.
- Find the size of one row : 4+30+30+50+15+2+10 = 141 bytes
- Find the number of rows that will fit on a single page:
- Remember the size of the page is 8096 bytes (without the page header size).
- So; size of page / size of one row = number of rows that fit on one page
- Divide the total number of rows that you think we will have in the table by the result of step 2.
- Assuming that we will have 1,000,000 records in our persons table:
- We are rounding it up to a nearest integer because any partial row is going to be moved to a new page.
- This is our number of pages : 17544
- Now multiply the results from step 3 above by 8192 (including page header size) to get the size estimate in bytes; and we get:
We always calculate the max size of the row in the table.
8096/ 141 = 57.4184 round it down to 57 as we have to contain every row in one page.
1000000/57 = 17543.8596 round it up to 17544.
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.
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,
WHEN t.is_Disabled = 1 THEN ‘Yes’
END AS IS_Trigger_Disabled,
WHEN t.is_instead_of_trigger = 1 THEN ‘Yes’
END AS Is_Instead_of_Trigger,
FROM sys.triggers t,
WHERE t.object_id = ste.object_id
AND sta.object_ID = t.parent_ID