Posts Tagged ‘sql server’
Difference between Clustered and Non-Clustered Index
Posted by: admin in Database, MS SQL Server, SQL Server 2008 on April 13th, 2010
- Clustered Index
- A clustered index is a special type of index that reorders the way records in the table are physically stored.
- Therefore table can have only one clustered index. The leaf nodes of a clustered index contain the data pages.
- Non-Clustered Index
- A nonclustered index is a special type of index in which the logical order of the index does not match the physical stored order of the rows on disk.
- The leaf node of a nonclustered index does not consist of the data pages. Instead, the leaf nodes contain index rows.
Rebuild Index Script for SQL Server
Posted by: admin in Database, MS SQL Server, SQL Server 2008 on March 12th, 2010
DECLARE @tblName VARCHAR(50)
DECLARE rs CURSOR FOR
SELECT TABLE_NAME FROM information_schema.tables WHERE [TABLE_TYPE] =N’BASE TABLE’
OPEN rs
FETCH NEXT FROM rs INTO @tblName
WHILE @@FETCH_STATUS = 0
BEGIN
DBCC DBREINDEX( @tblName,”,90)
END
CLOSE rs
DEALLOCATE rs
http://www.banmanpro.com/support/filegroupfull.asp
Posted by: admin in Database, MS SQL Server on May 18th, 2009
Try to shrink / back up the database.
Solution:
1. Check if the autogrowth is enable
2. Check if the autoshrink is checked
How to change the table owner in Sql Server
Posted by: admin in Database, MS SQL Server on May 12th, 2009
Use the sp_changeobjectowner stored procedure.
Synstax:
sp_changeobjectowner ‘OldUser.tableName’, ‘NewUser’
Example
In Domain User
-
sp_changeobjectowner '[Domain\Username].[tblTableProperty]', 'dbo'
Local User
-
sp_changeobjectowner '[Username].[tblTableProperty]', 'dbo'
Sql Server 2008 Management Studio Express Installation
Posted by: admin in MS SQL Server on April 23rd, 2009
You have to download and install the following before installing SQL SERVER 2008 Management Studio Express
-
1. <a href="http://www.microsoft.com/downloads/details.aspx?FamilyId=AB99342F-5D1A-413D-8319-81DA479AB0D7&displaylang=en">Microsoft .Net Framework 3.5 SP1</a>
-
2. <a href="http://support.microsoft.com/kb/942288">Windows Installer 4.5</a>
-
3. <a href="http://www.microsoft.com/windowsserver2003/technologies/management/powershell/download.mspx">Windows PowerShell 1.0</a>
Date and Time Formatting
Posted by: admin in MS SQL Server on April 15th, 2009
| Date Format | ||
| Format # | Sample Query (12/30/2009) | Sample Output |
| 1 | select convert(varchar, getdate(), 1) | 12/30/06 |
| 2 | select convert(varchar, getdate(), 2) | 06.12.30 |
| 3 | select convert(varchar, getdate(), 3) | 30/12/06 |
| 4 | select convert(varchar, getdate(), 4) | 30.12.06 |
| 5 | select convert(varchar, getdate(), 5) | 30-12-06 |
| 6 | select convert(varchar, getdate(), 6) | 30 Dec 06 |
| 7 | select convert(varchar, getdate(), 7) | Dec 30, 06 |
| 10 | select convert(varchar, getdate(), 10) | 12-30-06 |
| 11 | select convert(varchar, getdate(), 11) | 06/12/30 |
| 101 | select convert(varchar, getdate(), 101) | 12/30/2006 |
| 102 | select convert(varchar, getdate(), 102) | 2006.12.30 |
| 103 | select convert(varchar, getdate(), 103) | 30/12/2006 |
| 104 | select convert(varchar, getdate(), 104) | 30.12.2006 |
| 105 | select convert(varchar, getdate(), 105) | 30-12-2006 |
| 106 | select convert(varchar, getdate(), 106) | 30 Dec 2006 |
| 107 | select convert(varchar, getdate(), 107) | Dec 30, 2006 |
| 110 | select convert(varchar, getdate(), 110) | 12-30-2006 |
| 111 | select convert(varchar, getdate(), 111) | 2006/12/30 |
| Time Format | ||
| Format # | Sample Query | Sample Output |
| 8 or 108 | select convert(varchar, getdate(), 8 ) | 00:38:54 |
| 9 or 109 | select convert(varchar, getdate(), 9) | Dec 30 2006 12:38:54:840AM |
| 14 or 114 | select convert(varchar, getdate(), 14) | 00:38:54:840 |
You can also format the date or time without dividing characters, as well as concatenate the date and time string:
| Sample Query | Sample Output |
| select replace(convert(varchar, getdate(),101),’/',”) | 12302006 |
| select replace(convert(varchar, getdate(),101),’/',”) + replace(convert(varchar, getdate(),108),’:',”) |
12302006004426 |
How to Allow Explicit Values to be inserted into the identity column of a table
Posted by: admin in MS SQL Server on March 31st, 2009
Allows explicit values to be inserted into the identity column of a table
Syntax
SET IDENTITY_INSERT [ database. [ owner. ] ] { table } { ON | OFF }
Arguments
database
Is the name of the database in which the specified table resides.
owner
Is the name of the table owner.
table
Is the name of a table with an identity column.
Remarks
At any time, only one table in a session can have the IDENTITY_INSERT property set to ON. If a table already has this property set to ON, and a SET IDENTITY_INSERT ON statement is issued for another table, Microsoft® SQL Server™ returns an error message that states SET IDENTITY_INSERT is already ON and reports the table it is set ON for.
If the value inserted is larger than the current identity value for the table, SQL Server automatically uses the new inserted value as the current identity value.
The setting of SET IDENTITY_INSERT is set at execute or run time and not at parse time.
Permissions
Execute permissions default to the sysadmin fixed server role, and the db_owner and db_ddladmin fixed database roles, and the object owner.
Examples
This example creates a table with an identity column and shows how the SET IDENTITY_INSERT setting can be used to fill a gap in the identity values caused by a DELETE statement.
– Create products table.
CREATE TABLE products (id int IDENTITY PRIMARY KEY, product varchar(40))
GO
– Inserting values into products table.
INSERT INTO products (product) VALUES (‘screwdriver’)
INSERT INTO products (product) VALUES (‘hammer’)
INSERT INTO products (product) VALUES (‘saw’)
INSERT INTO products (product) VALUES (‘shovel’)
GO
– Create a gap in the identity values.
DELETE products
WHERE product = ‘saw’
GO
SELECT *
FROM products
GO
– Attempt to insert an explicit ID value of 3;
– should return a warning.
INSERT INTO products (id, product) VALUES(3, ‘garden shovel’)
GO
– SET IDENTITY_INSERT to ON.
SET IDENTITY_INSERT products ON
GO
– Attempt to insert an explicit ID value of 3
INSERT INTO products (id, product) VALUES(3, ‘garden shovel’).
GO
SELECT *
FROM products
GO
– Drop products table.
DROP TABLE products
GO
Source: http://msdn.microsoft.com/en-us/library/aa259221(SQL.80).aspx
Estimating the Size of a Table
Posted by: admin in Database, MS SQL Server on March 22nd, 2009
Estimating the Size of a Table
The following steps can be used to estimate the amount of space required to store the data in a table:
Specify the number of rows present in the table:
Number of rows in the table = Num_Rows
If there are fixed-length and variable-length columns in the table definition, calculate the space that each of these groups of columns occupies within the data row. The size of a column depends on the data type and length specification. For more information, see Data Types.
Number of columns = Num_Cols
Sum of bytes in all fixed-length columns = Fixed_Data_Size
Number of variable-length columns = Num_Variable_Cols
Maximum size of all variable-length columns = Max_Var_Size
If there are fixed-length columns in the table, a portion of the row, known as the null bitmap, is reserved to manage column nullability. Calculate its size:
Null Bitmap (Null_Bitmap) = 2 + (( Num_Cols + 7) / 8 )
Only the integer portion of the above expression should be used; discard any remainder.
If there are variable-length columns in the table, determine how much space is used to store the columns within the row:
Total size of variable-length columns (Variable_Data_Size) = 2 + (Num_Variable_Cols x 2) + Max_Var_Size
If there are no variable-length columns, set Variable_Data_Size to 0.
This formula assumes that all variable-length columns are 100 percent full. If you anticipate that a lower percentage of the variable-length column storage space will be used, you can adjust the result by that percentage to yield a more accurate estimate of the overall table size.
Calculate the row size:
Total row size (Row_Size) = Fixed_Data_Size + Variable_Data_Size + Null_Bitmap +4
The final value of 4 represents the data row header.
Calculate the number of rows per page (8096 free bytes per page):
Number of rows per page (Rows_Per_Page) = ( 8096 ) / (Row_Size + 2)
Because rows do not span pages, the number of rows per page should be rounded down to the nearest whole row.
If a clustered index is to be created on the table, calculate the number of reserved free rows per page, based on the fill factor specified. For more information, see Fill Factor. If no clustered index is to be created, specify Fill_Factor as 100.
Number of free rows per page (Free_Rows_Per_Page) = 8096 x ((100 – Fill_Factor) / 100) / (Row_Size + 2)
The fill factor used in the calculation is an integer value rather than a percentage.
Because rows do not span pages, the number of rows per page should be rounded down to the nearest whole row. As the fill factor grows, more data will be stored on each page and there will be fewer pages.
Calculate the number of pages required to store all the rows:
Number of pages (Num_Pages) = Num_Rows / (Rows_Per_Page – Free_Rows_Per_Page)
The number of pages estimated should be rounded up to the nearest whole page.
Calculate the amount of space required to store the data in a table (8192 total bytes per page):
Table size (bytes) = 8192 x Num_Pages
Source: http://msdn.microsoft.com/en-us/library/aa933068.aspx
How to create vb.net class script from table in T-SQL
Posted by: admin in .NET, Database, MS SQL Server, VB.Net on March 6th, 2009
/*
params:
@dbType – holds the data type defined in the table structure
@fldName – field/column name
@len – storage length of the column if data type is varchar/char etc.
@precision – numeric
@scale – numeric
@newType – equivalent data type in vb.net; ex. varchar -> string
@struct – script
@tablename – table name
*/
DECLARE @dbType VARCHAR(50)
DECLARE @fldName VARCHAR(50)
DECLARe @len smallint
DECLARE @precision smallint
DECLARE @scale smallint
DECLARE @newType VARCHAR(20)
DECLARE @class VARCHAR(MAX)
DECLARE @tablename VARCHAR(20)
DECLARE @tmpVar VARCHAR(MAX)
DECLARE @tmpVarpfx VARCHAR(3)
SET @tablename = ‘tblExchangeDocument’
SET @newType = ”
SET @tmpVar = ”
SET @class = ”–’Public Class ‘ + RIGHT(@tablename, LEN(@tablename) – 3)
SET @tmpVarpfx = ”
DECLARE rs CURSOR FOR
SELECT sys.columns.[name] as fldname, sys.types.name as dbtype, sys.columns.[max_length], sys.columns.[precision], sys.columns.[scale]
FROM sys.columns INNER JOIN sys.types ON sys.columns.system_type_id = sys.types.system_type_id
WHERE object_id = OBJECT_ID(@tablename)
OPEN rs
FETCH NEXT FROM rs
INTO @fldname, @dbType, @len, @precision, @scale
WHILE @@FETCH_STATUS = 0
BEGIN
IF @dbType = ‘varchar’ or @dbType = ‘char’
BEGIN
SET @tmpVarpfx = ‘str’
SET @newType = ‘String’
END
IF @dbType = ‘bigint’
BEGIN
SET @tmpVarpfx = ‘lng’
SET @newType = ‘long’
END
IF @dbType = ‘int’
BEGIN
SET @tmpVarpfx = ‘int’
SET @newType = ‘Integer’
END
IF @dbType = ‘datetime’
BEGIN
SET @tmpVarpfx = ‘dat’
SET @newType = ‘date’
END
SET @tmpVar = @tmpVar + char(13)+ char(9) + ‘Private ‘+ @tmpVarpfx + @fldname + ‘ AS ‘ + @newType
SET @class = @class + char(13) + char(9) + ‘Public Property ‘ + @fldname + ‘() AS ‘ + @newType
SET @class = @class + char(13) + char(9) + char(9) +’GET’
SET @class = @class + char(13) + char(9) + char(9) + char(9) +’return ‘ + @tmpVarpfx + @fldname
SET @class = @class + char(13) + char(9) + char(9) +’END GET’
SET @class = @class + char(13) + char(9) + char(9) +’SET (value AS ‘+ @newType +’)’
SET @class = @class + char(13) + char(9) + char(9) + char(9) + @tmpVarpfx + @fldname + ‘ = ‘ + ‘value’
SET @class = @class + char(13) + char(9) + char(9) +’END SET’
SET @class = @class + char(13) + char(9) + ‘END Property’
FETCH NEXT FROM rs
INTO @fldName, @dbType, @len, @precision, @scale
END
CLOSE rs
DEALLOCATE rs
SET @class = @tmpVar + char(13) + @class
SET @class = ‘Public Class ‘ + RIGHT(@tablename, LEN(@tablename) – 3) + @class
SET @class = @class + char(13) + ‘ End Class’
print @class
How to create a vb.net structure from table using T-SQL script
Posted by: admin in MS SQL Server, VB.Net on March 6th, 2009
/*
params:
@dbType – holds the data type defined in the table structure
@fldName – field/column name
@len – storage length of the column if data type is varchar/char etc.
@precision – numeric
@scale – numeric
@newType – equivalent data type in vb.net; ex. varchar -> string
@struct – script
@tablename – table name
*/
DECLARE @dbType VARCHAR(50)
DECLARE @fldName VARCHAR(50)
DECLARe @len smallint
DECLARE @precision smallint
DECLARE @scale smallint
DECLARE @newType VARCHAR(20)
DECLARE @struct VARCHAR(MAX)
DECLARE @tablename VARCHAR(20)
SET @tablename = ‘tblExchangeDocument’
SET @newType = ”
SET @struct = ‘Public Class ‘ + RIGHT(@tablename, LEN(@tablename) – 3)
DECLARE rs CURSOR FOR
SELECT sys.columns.[name] as fldname, sys.types.name as dbtype, sys.columns.[max_length], sys.columns.[precision], sys.columns.[scale]
FROM sys.columns INNER JOIN sys.types ON sys.columns.system_type_id = sys.types.system_type_id
WHERE object_id = OBJECT_ID(@tablename)
OPEN rs
FETCH NEXT FROM rs
INTO @fldname, @dbType, @len, @precision, @scale
WHILE @@FETCH_STATUS = 0
BEGIN
IF @dbType = ‘varchar’
SET @newType = ‘String’
IF @dbType = ‘bigint’
SET @newType = ‘long’
IF @dbType = ‘int’
SET @newType = ‘Integer’
IF @dbType = ‘datetime’
SET @newType = ‘date’
SET @struct = @struct + ”
SET @struct = @struct + char(13) + char(9) + ‘ Public ‘ + @fldname + ‘ AS ‘ + @newType
FETCH NEXT FROM rs
INTO @fldName, @dbType, @len, @precision, @scale
END
CLOSE rs
DEALLOCATE rs
SET @struct = @struct + char(13) + ‘ End Class’
print @Struct