/*
Below code is for SQL 2008
*/
SELECT 'EXEC sp_HelpText '+name, create_date, modify_date,type
FROM sys.objects
WHERE type in ('P','FN','V','TF')
AND DATEDIFF(D,modify_date, GETDATE()) < 10 --How old the object can be ?
ORDER BY modify_date
Showing posts with label Sql Server. Show all posts
Showing posts with label Sql Server. Show all posts
Tuesday, October 4, 2011
Find Modified Procedures,Functions,Views In SQL Server
Saturday, August 1, 2009
Ranking Functions
Use Of Ranking Functions
Like Row_Number() , Rank() , Dense_Rank()
Select
Row_Number() Over ( Order By Percentage Desc) as Sr_No,
Rank() Over (Order By Percentage Desc) as Rank,
Dense_Rank() Over (Order By Percentage Desc) as Dense_Rank,
Student_Name,
Percentage
From Students
Sr_No Rank Dense_Rank Student_Name Percentage
1 1 1 Ankur 80.00
2 2 2 Vinod 78.00
3 2 2 Mahmad 78.00
4 4 3 Amol 70.00
5 4 3 Varun 70.00
6 6 4 Rajesh 69.00
7 6 4 Bharat 69.00
8 8 5 Parixit 35.00
Like Row_Number() , Rank() , Dense_Rank()
Select
Row_Number() Over ( Order By Percentage Desc) as Sr_No,
Rank() Over (Order By Percentage Desc) as Rank,
Dense_Rank() Over (Order By Percentage Desc) as Dense_Rank,
Student_Name,
Percentage
From Students
Sr_No Rank Dense_Rank Student_Name Percentage
1 1 1 Ankur 80.00
2 2 2 Vinod 78.00
3 2 2 Mahmad 78.00
4 4 3 Amol 70.00
5 4 3 Varun 70.00
6 6 4 Rajesh 69.00
7 6 4 Bharat 69.00
8 8 5 Parixit 35.00
Tuesday, July 28, 2009
Recursive Stored Procedure
Create PROCEDURE Factorial
@Number as int,
@Factorial as int OUTPUT
AS
Begin
DECLARE @Input int
DECLARE @Output int
IF @Number != 1
BEGIN
set @Input = @Number - 1
EXEC Factorial @Input, @Output OUTPUT
SELECT @Factorial = @Number * @Output
END
ELSE
BEGIN
SELECT @Factorial = 1
END
RETURN
End
@Number as int,
@Factorial as int OUTPUT
AS
Begin
DECLARE @Input int
DECLARE @Output int
IF @Number != 1
BEGIN
set @Input = @Number - 1
EXEC Factorial @Input, @Output OUTPUT
SELECT @Factorial = @Number * @Output
END
ELSE
BEGIN
SELECT @Factorial = 1
END
RETURN
End
Monday, July 27, 2009
Generate Serial Number In SQL Server 2005
select Row_Number() over (order by Emp_Id) as SrNo ,Emp_Id,Emp_Name
from mstEmployee
from mstEmployee
Thursday, May 7, 2009
Candidate Key
A Candidate Key is a column or group of columns that uniquely describe every row in a table.
Friday, April 10, 2009
Magic table in sql server
When we are inserting or deleting any data from table in transaction a temporary table
(inserted , deleted) is created and will be deleted when we call commit tran or rollback tran, the
table is called magic table.
(inserted , deleted) is created and will be deleted when we call commit tran or rollback tran, the
table is called magic table.
Thursday, April 2, 2009
Friday, March 13, 2009
Concatenate Rows Into A String Variable
DECLARE @Course varchar(Max)
SELECT @Course = COALESCE(@Course + ' , ', '') + Course_Name
FROM Course
where Course_Name like 'B%'
print @Course
SELECT @Course = COALESCE(@Course + ' , ', '') + Course_Name
FROM Course
where Course_Name like 'B%'
print @Course
Monday, February 23, 2009
Sync Foreign Keys In Sql Server 2005
List all foreigh keys in sql 2005
SELECT f.name AS ForeignKey,
OBJECT_NAME(f.parent_object_id) AS TableName,
COL_NAME(fc.parent_object_id,
fc.parent_column_id) AS ColumnName,
OBJECT_NAME (f.referenced_object_id) AS ReferenceTableName,
COL_NAME(fc.referenced_object_id,
fc.referenced_column_id) AS ReferenceColumnName
FROM sys.foreign_keys AS f
INNER JOIN sys.foreign_key_columns AS fc
ON f.OBJECT_ID = fc.constraint_object_id
Order By f.name , OBJECT_NAME(f.parent_object_id),fc.constraint_column_id
Sync foreigh key in sql 2005
BEGIN TRAN
BEGIN TRY
set nocount on
Declare @idoc int
set @Error_Code=0
set @Error_Desc=''
Declare @ConstraintTable Table
(
Sr_No Int Identity(1,1),
ForeignKey Varchar(Max),
TableName Varchar(Max),
ColumnName Varchar(Max),
ReferenceTableName Varchar(Max),
ReferenceColumnName Varchar(Max)
)
exec sp_xml_preparedocument @idoc output , @ConstraintsXML
Insert Into @ConstraintTable
Select ForeignKey,
TableName,
ColumnName,
ReferenceTableName,
ReferenceColumnName
From OpenXML(@idoc,'/NewDataSet/Table',2)
With (
ForeignKey Varchar(Max),
TableName Varchar(Max),
ColumnName Varchar(Max),
ReferenceTableName Varchar(Max),
ReferenceColumnName Varchar(Max)
)
Order By ForeignKey,TableName
exec sp_xml_removedocument @idoc
Declare @ForeignKey Varchar(Max)
Declare @TableName Varchar(Max)
Declare @ColumnName Varchar(Max)
Declare @ReferenceTableName Varchar(Max)
Declare @ReferenceColumnName Varchar(Max)
Declare @CTableName Varchar(Max)
Declare @CColumnName Varchar(Max)
Declare @CReferenceTableName Varchar(Max)
Declare @CReferenceColumnName Varchar(Max)
Declare @SrNo Int
Declare @ForeignKeyString Varchar(Max)
Declare @Rows Int
Declare @CommandStr Varchar(Max)
Set @SrNo = 1
Set @ForeignKeyString = ''
Select @Rows = Count(*) From @ConstraintTable
--Select * From @ConstraintTable
Set @CTableName = ''
Set @CColumnName = ''
Set @CReferenceTableName = ''
Set @CReferenceColumnName = ''
Select * From @ConstraintTable
While @SrNo < = @Rows + 1
Begin
Select @ForeignKey = ForeignKey,
@TableName = TableName,
@ColumnName = ColumnName,
@ReferenceTableName = ReferenceTableName,
@ReferenceColumnName = ReferenceColumnName
From @ConstraintTable
Where Sr_No = @SrNo
--print @ForeignKey
--print @SrNo
IF (@ForeignKeyString = @ForeignKey) And @SrNo <= @Rows
Begin --Same Key Append Column Names
set @CColumnName = @CColumnName + ',' + @ColumnName
set @CReferenceColumnName = @CReferenceColumnName + ',' + @ReferenceColumnName
End --Same Key Append Column Names
Else
Begin --Another Foreigh Key Make new command
If Len(@ForeignKeyString)>0
Begin
Set @CommandStr = 'If Exists( select * FROM sys.foreign_keys Where Name=' + CHAR(39) + @ForeignKeyString + Char(39) +') ALTER TABLE '
Set @CommandStr = @CommandStr + @CTableName + ' DROP CONSTRAINT [' + @ForeignKeyString +']'
--print @CommandStr
exec (@CommandStr)
Set @CommandStr = ' ALTER TABLE ' + @CTableName
Set @CommandStr = @CommandStr + ' WITH NOCHECK ADD CONSTRAINT [' + @ForeignKeyString + ']'
Set @CommandStr = @CommandStr + ' FOREIGN KEY (' + @CColumnName + ') '
Set @CommandStr = @CommandStr + ' REFERENCES ' + @CReferenceTableName + '(' + @CReferenceColumnName + ')'
--print @CommandStr
exec (@CommandStr)
End
Set @ForeignKeyString = @ForeignKey
Set @CTableName = @TableName
Set @CColumnName = @ColumnName
Set @CReferenceTableName = @ReferenceTableName
Set @CReferenceColumnName = @ReferenceColumnName
End --Another Foreigh Key Make new command
Set @SrNo = @SrNo + 1
End
COMMIT TRAN
print 'Sync Successfull !'
END TRY
BEGIN CATCH
ROLLBACK TRAN
Select @Error_Code = cast(ERROR_NUMBER() as varchar(5))
Select @Error_Desc = @CommandStr + char(13) + CAST(ERROR_LINE() AS VARCHAR(4000)) + 'Ö' + CAST(ERROR_MESSAGE() as VARCHAR(4000)) /*+ 'll'+ @query*/
print @Error_Code
print @Error_Desc
END CATCH
SELECT f.name AS ForeignKey,
OBJECT_NAME(f.parent_object_id) AS TableName,
COL_NAME(fc.parent_object_id,
fc.parent_column_id) AS ColumnName,
OBJECT_NAME (f.referenced_object_id) AS ReferenceTableName,
COL_NAME(fc.referenced_object_id,
fc.referenced_column_id) AS ReferenceColumnName
FROM sys.foreign_keys AS f
INNER JOIN sys.foreign_key_columns AS fc
ON f.OBJECT_ID = fc.constraint_object_id
Order By f.name , OBJECT_NAME(f.parent_object_id),fc.constraint_column_id
Sync foreigh key in sql 2005
BEGIN TRAN
BEGIN TRY
set nocount on
Declare @idoc int
set @Error_Code=0
set @Error_Desc=''
Declare @ConstraintTable Table
(
Sr_No Int Identity(1,1),
ForeignKey Varchar(Max),
TableName Varchar(Max),
ColumnName Varchar(Max),
ReferenceTableName Varchar(Max),
ReferenceColumnName Varchar(Max)
)
exec sp_xml_preparedocument @idoc output , @ConstraintsXML
Insert Into @ConstraintTable
Select ForeignKey,
TableName,
ColumnName,
ReferenceTableName,
ReferenceColumnName
From OpenXML(@idoc,'/NewDataSet/Table',2)
With (
ForeignKey Varchar(Max),
TableName Varchar(Max),
ColumnName Varchar(Max),
ReferenceTableName Varchar(Max),
ReferenceColumnName Varchar(Max)
)
Order By ForeignKey,TableName
exec sp_xml_removedocument @idoc
Declare @ForeignKey Varchar(Max)
Declare @TableName Varchar(Max)
Declare @ColumnName Varchar(Max)
Declare @ReferenceTableName Varchar(Max)
Declare @ReferenceColumnName Varchar(Max)
Declare @CTableName Varchar(Max)
Declare @CColumnName Varchar(Max)
Declare @CReferenceTableName Varchar(Max)
Declare @CReferenceColumnName Varchar(Max)
Declare @SrNo Int
Declare @ForeignKeyString Varchar(Max)
Declare @Rows Int
Declare @CommandStr Varchar(Max)
Set @SrNo = 1
Set @ForeignKeyString = ''
Select @Rows = Count(*) From @ConstraintTable
--Select * From @ConstraintTable
Set @CTableName = ''
Set @CColumnName = ''
Set @CReferenceTableName = ''
Set @CReferenceColumnName = ''
Select * From @ConstraintTable
While @SrNo < = @Rows + 1
Begin
Select @ForeignKey = ForeignKey,
@TableName = TableName,
@ColumnName = ColumnName,
@ReferenceTableName = ReferenceTableName,
@ReferenceColumnName = ReferenceColumnName
From @ConstraintTable
Where Sr_No = @SrNo
--print @ForeignKey
--print @SrNo
IF (@ForeignKeyString = @ForeignKey) And @SrNo <= @Rows
Begin --Same Key Append Column Names
set @CColumnName = @CColumnName + ',' + @ColumnName
set @CReferenceColumnName = @CReferenceColumnName + ',' + @ReferenceColumnName
End --Same Key Append Column Names
Else
Begin --Another Foreigh Key Make new command
If Len(@ForeignKeyString)>0
Begin
Set @CommandStr = 'If Exists( select * FROM sys.foreign_keys Where Name=' + CHAR(39) + @ForeignKeyString + Char(39) +') ALTER TABLE '
Set @CommandStr = @CommandStr + @CTableName + ' DROP CONSTRAINT [' + @ForeignKeyString +']'
--print @CommandStr
exec (@CommandStr)
Set @CommandStr = ' ALTER TABLE ' + @CTableName
Set @CommandStr = @CommandStr + ' WITH NOCHECK ADD CONSTRAINT [' + @ForeignKeyString + ']'
Set @CommandStr = @CommandStr + ' FOREIGN KEY (' + @CColumnName + ') '
Set @CommandStr = @CommandStr + ' REFERENCES ' + @CReferenceTableName + '(' + @CReferenceColumnName + ')'
--print @CommandStr
exec (@CommandStr)
End
Set @ForeignKeyString = @ForeignKey
Set @CTableName = @TableName
Set @CColumnName = @ColumnName
Set @CReferenceTableName = @ReferenceTableName
Set @CReferenceColumnName = @ReferenceColumnName
End --Another Foreigh Key Make new command
Set @SrNo = @SrNo + 1
End
COMMIT TRAN
print 'Sync Successfull !'
END TRY
BEGIN CATCH
ROLLBACK TRAN
Select @Error_Code = cast(ERROR_NUMBER() as varchar(5))
Select @Error_Desc = @CommandStr + char(13) + CAST(ERROR_LINE() AS VARCHAR(4000)) + 'Ö' + CAST(ERROR_MESSAGE() as VARCHAR(4000)) /*+ 'll'+ @query*/
print @Error_Code
print @Error_Desc
END CATCH
Tuesday, December 30, 2008
Stored Procedure
Syntax to create Stored Procedure.
CREATE PROCEDURE
-- Add the parameters for the stored procedure here
<@Param1, sysname, @p1> = ,
<@Param2, sysname, @p2> =
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for procedure here
SELECT <@Param1, sysname, @p1>, <@Param2, sysname, @p2>
END
Example :
Suppose there is a table StudentMast Having three fields Roll,Name,Address. I want to make an SP(i.e stored procedure) which will take one parameter and finds records which have name = the parameter.
CREATE PROCEDURE Find_Student_By_Name
@Name Varchar(50) ---This is a parameter
AS
BEGIN
Select *
From StudentMast
Where Name=@Name
END
CREATE PROCEDURE
-- Add the parameters for the stored procedure here
<@Param1, sysname, @p1>
<@Param2, sysname, @p2>
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for procedure here
SELECT <@Param1, sysname, @p1>, <@Param2, sysname, @p2>
END
Example :
Suppose there is a table StudentMast Having three fields Roll,Name,Address. I want to make an SP(i.e stored procedure) which will take one parameter and finds records which have name = the parameter.
CREATE PROCEDURE Find_Student_By_Name
@Name Varchar(50) ---This is a parameter
AS
BEGIN
Select *
From StudentMast
Where Name=@Name
END
Wednesday, October 8, 2008
Error Handling In Sql Server
Error_Number()
Returns the error number of the error that caused the CATCH block of a TRY…CATCH construct to be run in Sql Server. Return type of this function is int.
Error_Line()
Returns the line number at which an error occurred in Sql Server Stored Procedure..
Error_Message()
Returns the message text of the error that occured in Sql Server Stored Procedure.
Example :
BEGIN TRY
SELECT 1/0; --Divide by zero error
END TRY
BEGIN CATCH
SELECT ERROR_NUMBER() AS ErrNumber;
SELECT ERROR_LINE() AS ErrLine;
SELECT ERROR_MESSAGE() AS ErrorMessage;
END CATCH
Returns the error number of the error that caused the CATCH block of a TRY…CATCH construct to be run in Sql Server. Return type of this function is int.
Error_Line()
Returns the line number at which an error occurred in Sql Server Stored Procedure..
Error_Message()
Returns the message text of the error that occured in Sql Server Stored Procedure.
Example :
BEGIN TRY
SELECT 1/0; --Divide by zero error
END TRY
BEGIN CATCH
SELECT ERROR_NUMBER() AS ErrNumber;
SELECT ERROR_LINE() AS ErrLine;
SELECT ERROR_MESSAGE() AS ErrorMessage;
END CATCH
Friday, October 3, 2008
Difference Between Varchar And NVarchar
The difference between the two is that Nvarchar is used to store uNicode data, which is used to store multilingual data in your database tables. Other languages like chinese have an extended set of character codes that needs to be saved and this datatype allows for this extension.
If your database will not be storing multilingual data you should use the varchar datatype instead of nvarchar datatype. Because nvarchar takes twice as much space as varchar.
If your database will not be storing multilingual data you should use the varchar datatype instead of nvarchar datatype. Because nvarchar takes twice as much space as varchar.
Monday, September 1, 2008
@@identity
After an INSERT, SELECT INTO, or bulk copy statement is completed, @@IDENTITY contains the last identity value that is generated by the statement. If the statement did not affect any tables with identity columns, @@IDENTITY returns NULL. If multiple rows are inserted, generating multiple identity values, @@IDENTITY returns the last identity value generated
Friday, August 29, 2008
Create Trigger To Log DDL Events
CREATE TRIGGER [trgLogDDLEvent] ON DATABASE
FOR DDL_DATABASE_LEVEL_EVENTS
AS
set nocount on
DECLARE @data XML
SET @data = EVENTDATA()
IF @data.value('(/EVENT_INSTANCE/EventType)[1]', 'varchar(100)')
<> 'CREATE_STATISTICS'
INSERT INTO DdlLog..ChangeLog
(
EventType,
ObjectName,
ObjectType,
tsql
)
VALUES (
@data.value('(/EVENT_INSTANCE/EventType)[1]',
'varchar(100)'),
@data.value('(/EVENT_INSTANCE/ObjectName)[1]',
'varchar(100)'),
@data.value('(/EVENT_INSTANCE/ObjectType)[1]',
'varchar(100)'),
@data.value('(/EVENT_INSTANCE/TSQLCommand)[1]',
'varchar(max)')
) ;
ENABLE TRIGGER [trgLogDDLEvent] ON DATABASE
FOR DDL_DATABASE_LEVEL_EVENTS
AS
set nocount on
DECLARE @data XML
SET @data = EVENTDATA()
IF @data.value('(/EVENT_INSTANCE/EventType)[1]', 'varchar(100)')
<> 'CREATE_STATISTICS'
INSERT INTO DdlLog..ChangeLog
(
EventType,
ObjectName,
ObjectType,
tsql
)
VALUES (
@data.value('(/EVENT_INSTANCE/EventType)[1]',
'varchar(100)'),
@data.value('(/EVENT_INSTANCE/ObjectName)[1]',
'varchar(100)'),
@data.value('(/EVENT_INSTANCE/ObjectType)[1]',
'varchar(100)'),
@data.value('(/EVENT_INSTANCE/TSQLCommand)[1]',
'varchar(max)')
) ;
ENABLE TRIGGER [trgLogDDLEvent] ON DATABASE
Wednesday, August 20, 2008
Trigger
A trigger is a special kind of stored procedure that automatically executes when an event occurs in the database server.
There are two (2) types of triggers in Sql Server 2005 DDL (Data Definition Language) and DML (Data Manipulation Language).
DDL is new in Sql Server 2005.
Syntax to create trigger:
CREATE TRIGGER trigger_name
ON { ALL SERVER | DATABASE }
[ WITH [ ,...n ] ]
{ FOR | AFTER } { event_type | event_group } [ ,...n ]
AS { sql_statement [ ; ] [ ...n ] | EXTERNAL NAME < method specifier > [ ; ] }
When you want to fire the trigger on table level you have to use DML triggers. DML triggers can be on INSERT,UPDATE,DELETE command. While DDL triggers can be used when you want to execute at the time of table,procedure,trigger,function's events like creation , alteration , drop.
There are two (2) types of triggers in Sql Server 2005 DDL (Data Definition Language) and DML (Data Manipulation Language).
DDL is new in Sql Server 2005.
Syntax to create trigger:
CREATE TRIGGER trigger_name
ON { ALL SERVER | DATABASE }
[ WITH
{ FOR | AFTER } { event_type | event_group } [ ,...n ]
AS { sql_statement [ ; ] [ ...n ] | EXTERNAL NAME < method specifier > [ ; ] }
When you want to fire the trigger on table level you have to use DML triggers. DML triggers can be on INSERT,UPDATE,DELETE command. While DDL triggers can be used when you want to execute at the time of table,procedure,trigger,function's events like creation , alteration , drop.
Stored Procedure
Syntax to create Stored Procedure.
CREATE PROCEDURE
-- Add the parameters for the stored procedure here
<@Param1, sysname, @p1> = ,
<@Param2, sysname, @p2> =
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for procedure here
SELECT <@Param1, sysname, @p1>, <@Param2, sysname, @p2>
END
Example :
Suppose there is a table StudentMast Having three fields Roll,Name,Address. I want to make an SP(i.e stored procedure) which will take one parameter and finds records which have name = the parameter.
CREATE PROCEDURE Find_Student_By_Name
@Name Varchar(50) ---This is a parameter
AS
BEGIN
Select *
From StudentMast
Where Name=@Name
END
CREATE PROCEDURE
-- Add the parameters for the stored procedure here
<@Param1, sysname, @p1>
<@Param2, sysname, @p2>
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for procedure here
SELECT <@Param1, sysname, @p1>, <@Param2, sysname, @p2>
END
Example :
Suppose there is a table StudentMast Having three fields Roll,Name,Address. I want to make an SP(i.e stored procedure) which will take one parameter and finds records which have name = the parameter.
CREATE PROCEDURE Find_Student_By_Name
@Name Varchar(50) ---This is a parameter
AS
BEGIN
Select *
From StudentMast
Where Name=@Name
END
Data Types In SQL Server 2005
Type From To
bigint -9,223,372,036,854,775,808 9,223,372,036,854,775,807
int -2,147,483,648 2,147,483,647
smallint -32,768 32,767
tinyint 0 255
bit 0 1
decimal -10^38 +1 10^38 –1
numeric -10^38 +1 10^38 –1
money -922,337,203,685,477.5808 +922,337,203,685,477.5807
smallmoney -214,748.3648 +214,748.3647
Approximate numerics (Numbers With Decimal Point)
Type From To
float -1.79E + 308 1.79E + 308
real -3.40E + 38 3.40E + 38
datetime and smalldatetime ( SQL Date And Time)
Type From To
datetime Jan 1, 1753 Dec 31, 9999
smalldatetime Jan 1, 1900 Jun 6, 2079
Character Strings
Type Description
char Fixed-length non-Unicode character data with a maximum
length of 8,000 characters.
varchar Variable-length non-Unicode data with a maximum of 8,000
characters.
varchar(max) Variable-length non-Unicode data with a maximum length
of 231 characters (In SQL Server 2005 only).
text Variable-length non-Unicode data with a maximum length
of 2,147,483,647 characters.
Unicode Character Strings (In computing, Unicode is an industry standard allowing computers to consistently represent and manipulate text expressed in most of the world\\\’s writing systems.)
Type Description
nchar Fixed-length Unicode data with a maximum length of 4,000
characters.
nvarchar Variable-length Unicode data with a maximum length of
4,000 characters.
nvarchar(max) Variable-length Unicode data with a maximum length of 230
characters (SQL Server 2005 only).
ntext Variable-length Unicode data with a maximum length of
1,073,741,823 characters.
Binary Strings(Binary Bits i.e 0,1)
Type Description
binary Fixed-length binary data with a maximum length of 8,000
bytes.
varbinary Variable-length binary data with a maximum length of
8,000 bytes.
varbinary(max) Variable-length binary data with a maximum length of 231
bytes (SQL Server 2005 only).
image Variable-length binary data with a maximum length of
2,147,483,647 bytes.
Other Data Types
1) sql_variant: Stores values of various SQL Server-supported data
types, except text, ntext, and timestamp.
2) timestamp: Stores a database-wide unique number that gets updated
every time a row gets updated.
3) uniqueidentifier: Stores a globally unique identifier (GUID).
4) xml: Stores XML data.
5) cursor: A reference to a cursor.
6) table: Stores a result set for later processing.
bigint -9,223,372,036,854,775,808 9,223,372,036,854,775,807
int -2,147,483,648 2,147,483,647
smallint -32,768 32,767
tinyint 0 255
bit 0 1
decimal -10^38 +1 10^38 –1
numeric -10^38 +1 10^38 –1
money -922,337,203,685,477.5808 +922,337,203,685,477.5807
smallmoney -214,748.3648 +214,748.3647
Approximate numerics (Numbers With Decimal Point)
Type From To
float -1.79E + 308 1.79E + 308
real -3.40E + 38 3.40E + 38
datetime and smalldatetime ( SQL Date And Time)
Type From To
datetime Jan 1, 1753 Dec 31, 9999
smalldatetime Jan 1, 1900 Jun 6, 2079
Character Strings
Type Description
char Fixed-length non-Unicode character data with a maximum
length of 8,000 characters.
varchar Variable-length non-Unicode data with a maximum of 8,000
characters.
varchar(max) Variable-length non-Unicode data with a maximum length
of 231 characters (In SQL Server 2005 only).
text Variable-length non-Unicode data with a maximum length
of 2,147,483,647 characters.
Unicode Character Strings (In computing, Unicode is an industry standard allowing computers to consistently represent and manipulate text expressed in most of the world\\\’s writing systems.)
Type Description
nchar Fixed-length Unicode data with a maximum length of 4,000
characters.
nvarchar Variable-length Unicode data with a maximum length of
4,000 characters.
nvarchar(max) Variable-length Unicode data with a maximum length of 230
characters (SQL Server 2005 only).
ntext Variable-length Unicode data with a maximum length of
1,073,741,823 characters.
Binary Strings(Binary Bits i.e 0,1)
Type Description
binary Fixed-length binary data with a maximum length of 8,000
bytes.
varbinary Variable-length binary data with a maximum length of
8,000 bytes.
varbinary(max) Variable-length binary data with a maximum length of 231
bytes (SQL Server 2005 only).
image Variable-length binary data with a maximum length of
2,147,483,647 bytes.
Other Data Types
1) sql_variant: Stores values of various SQL Server-supported data
types, except text, ntext, and timestamp.
2) timestamp: Stores a database-wide unique number that gets updated
every time a row gets updated.
3) uniqueidentifier: Stores a globally unique identifier (GUID).
4) xml: Stores XML data.
5) cursor: A reference to a cursor.
6) table: Stores a result set for later processing.
What is SQL Injection? How to prevent it?
SQL Injection is a technique used to exploit web sites by altering backend SQL statements by manipulating application input. It can happen when any application developer accepts the input of text box in SQL statement as it is.
Here is the example how on can achieve SQL injection.
Suppose a web site has a user authentication form without handling any input. Supplying the input to SQL Query as it is.
A mischievous user knows the user id but don’t know password gives user name and password as below.
User Id = mahmad
Password = abc' OR 'x'='x
So the query by this input will be like given below:
Select *
From UserTable
Where UserId='mahmad'
And Password= 'abs' OR 'x'='x';
This wrong password will work 100% without knowing password and your system is cracked.
How to prevent SQL Injection in this situation?
Solution is simple just replace single quote (') with two single quotes ('').
Here is the example how on can achieve SQL injection.
Suppose a web site has a user authentication form without handling any input. Supplying the input to SQL Query as it is.
A mischievous user knows the user id but don’t know password gives user name and password as below.
User Id = mahmad
Password = abc' OR 'x'='x
So the query by this input will be like given below:
Select *
From UserTable
Where UserId='mahmad'
And Password= 'abs' OR 'x'='x';
This wrong password will work 100% without knowing password and your system is cracked.
How to prevent SQL Injection in this situation?
Solution is simple just replace single quote (') with two single quotes ('').
Escape Sequence (’) In Sql Server 2005,QUOTENAME
Suppose in sql server there is one variable
@String Varchar(max)
Now in this variable you want to store a string like this
Select * From Student_Table Where std_Name Like 'Mah%'
Suppose you are making statement as given below
Set @String='Select * From Student_Table Where std_Name Like 'Mah%''
But this will give you error.
To solve this problem you can use QUOTENAME function of SQL like given below.
Set @String='Select * From Student_Table Where std_Name Like ' + QUOTENAME('Mah%',Char(39))
Where Char(39) is ( ' ) character.
@String Varchar(max)
Now in this variable you want to store a string like this
Select * From Student_Table Where std_Name Like 'Mah%'
Suppose you are making statement as given below
Set @String='Select * From Student_Table Where std_Name Like 'Mah%''
But this will give you error.
To solve this problem you can use QUOTENAME function of SQL like given below.
Set @String='Select * From Student_Table Where std_Name Like ' + QUOTENAME('Mah%',Char(39))
Where Char(39) is ( ' ) character.
SQL Server 2005 Shortcut Keys
Below I am giving generally used shortcut keys in SQL Server 2005 :
Cancel a query. ALT+BREAK
Connections: Connect. CTRL+O
Connections: Disconnect. CTRL+F4
Connections: Disconnect and close child window. CTRL+F4
Database object information. ALT+F1 (i.e Structure Of Table etc. )
Equivalent to sp_help)
Editing: Clear the active Editor pane. CTRL+SHIFT+DEL
Editing: Comment out code. CTRL+SHIFT+C
Editing: Decrease indent. SHIFT+TAB
Editing: Increase indent. TAB
Editing: Delete through the end of a line in the Editor CTRL+DEL
Editing: Find. CTRL+F
Editing: Go to a line number. CTRL+G
Editing: Make selection lowercase. CTRL+SHIFT+L
Editing: Make selection uppercase. CTRL+SHIFT+U
Editing: Remove comments. CTRL+SHIFT+R
Editing: Replace. CTRL+H
Execute a query. CTRL+E , F5
Help for the selected TSQL statement. SHIFT+F1
Navigation: Switch between query and result panes. F6
Navigation: Switch panes. Shift+F6
New Query window. CTRL+N
Object Browser (To show/hide). F8
Object Search. F4
Parse the query for checking syntax. CTRL+F5
Print. CTRL+P
Results: Display results in grid format. CTRL+D
Results: Display results in text format. CTRL+T
Results: Save results to file. CTRL+SHIFT+F
Results: Show Results pane (toggle). CTRL+R
Save. CTRL+S
Tuning: Index Tuning Wizard. CTRL+I
Change / Select Database. CTRL+U
Cancel a query. ALT+BREAK
Connections: Connect. CTRL+O
Connections: Disconnect. CTRL+F4
Connections: Disconnect and close child window. CTRL+F4
Database object information. ALT+F1 (i.e Structure Of Table etc. )
Equivalent to sp_help)
Editing: Clear the active Editor pane. CTRL+SHIFT+DEL
Editing: Comment out code. CTRL+SHIFT+C
Editing: Decrease indent. SHIFT+TAB
Editing: Increase indent. TAB
Editing: Delete through the end of a line in the Editor CTRL+DEL
Editing: Find. CTRL+F
Editing: Go to a line number. CTRL+G
Editing: Make selection lowercase. CTRL+SHIFT+L
Editing: Make selection uppercase. CTRL+SHIFT+U
Editing: Remove comments. CTRL+SHIFT+R
Editing: Replace. CTRL+H
Execute a query. CTRL+E , F5
Help for the selected TSQL statement. SHIFT+F1
Navigation: Switch between query and result panes. F6
Navigation: Switch panes. Shift+F6
New Query window. CTRL+N
Object Browser (To show/hide). F8
Object Search. F4
Parse the query for checking syntax. CTRL+F5
Print. CTRL+P
Results: Display results in grid format. CTRL+D
Results: Display results in text format. CTRL+T
Results: Save results to file. CTRL+SHIFT+F
Results: Show Results pane (toggle). CTRL+R
Save. CTRL+S
Tuning: Index Tuning Wizard. CTRL+I
Change / Select Database. CTRL+U
Subscribe to:
Posts (Atom)