How do I check size of SQL Servere Database

We can check the size of a MSSQL database by running a query through the SQL Web Admin using the command ‘sp_helpfile’.

Retriving data as an XML from SQL Server

With SQL Server we can generate XML output using different methods. Using TSQL keyword FOR XML along with AUTO, RAW, PATH and EXPLICIT we could generate almost any XML structure that we might need.
Following sample is using XML Explicit option for retriving data.

SELECT 1 AS Tag,



NULL AS Parent,



NULL AS 'root!1!',



NULL AS 'Schedule!2!ID!Element',



NULL AS 'Schedule!2!ScheduledDate!Element',



NULL AS 'Schedule!2!ResourceID!Element'



UNION



SELECT 2 AS Tag, 1 AS Parent,



null,RS.ID, RS.ScheduledDate,RS.ResourceID from dbo.tblTest



For

XML EXPLICIT





Sample Result generated will be in this format:

<root>

<Schedule> <ID>69</ID> <

<ScheduledDate>2011-05-08T00:00:00</ScheduledDate> <ResourceID>2</ResourceID>

</Schedule>



<Schedule><ID>70</ID><

<ScheduledDate>2011-07-08T00:00:00</ScheduledDate><ResourceID>23</ResourceID>

</Schedule>

</root>

Implementing Referential Integrity and Cascading Actions in SQL Server

Implementing Referential Integrity and Cascading Actions in SQL Server

http://msdn.microsoft.com/en-us/library/aa902684(v=sql.80).aspx

SQL SERVER - How to get the last generated value of an identity column

SCOPE_IDENTITY Returns the last IDENTITY value inserted into an IDENTITY column in the same scope(a stored procedure, trigger, function, or batch).


Example:

Insert Employees(FirstName, LastName) values ('Sharma','Bhupesh')

select @@identity Employees
SCOPE_IDENTITY, IDENT_CURRENT, and @@IDENTITY are similar functions in that they return values inserted into IDENTITY columns

SQL SERVER - UDF - Get Name as per specific format

Following Sql function can be used to get Name as per specific format

--This function uses table employee of pubs database which comes with sql server



CREATE FUNCTION [dbo].fnEmployeeName(@ID varchar(15), @Format int = 1) RETURNS varchar(30) AS

/****** fnEmployeeName******/

-- Return Employee Name as per the required format

BEGIN
DECLARE @FIRSTNM varchar(15), @LASTNM varchar(15), @MIDDLENM varchar(15)
SELECT @FIRSTNM = fname, @LASTNM = lname, @MIDDLENM = minit FROM employee WHERE emp_id = @ID
DECLARE @Value varchar(103) IF @@ROWCOUNT > 0
begin
IF @Format=1 SET @Value = LTRIM(IsNull(@LASTNM,'') + IsNull(' ' + @FIRSTNM,'') + IsNull(' ' + @MIDDLENM,''))
Else
SET @Value = LTRIM(IsNull(@FIRSTNM,'') + IsNull(' ' + @LASTNM,'') + IsNull(' ' + @MIDDLENM,'')) END
RETURN @Value
END

SQL SERVER - How to serach stored procedure for a specific text

Search in stored procedures can be performed through INFORMATION_SCHEMA.ROUTINES view, or syscomments. Let say if you want to search for keyword 'power' in your stored procedure. try following:
1. Search using INFORMATION_SCHEMA.ROUTINES view


SELECT ROUTINE_NAME, ROUTINE_DEFINITION FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_DEFINITION LIKE '%power%' AND ROUTINE_TYPE='PROCEDURE'
2. Search using syscomments


SELECT OBJECT_NAME(id) FROM syscomments WHERE [text] LIKE '%power%' AND OBJECTPROPERTY(id, 'IsProcedure') = 1 GROUP BY OBJECT_NAME(id)
BUT you may not get correct result if your stored procedure is very large(>8kb) since whole text of procedure is not being returned. You will not see this problem in sql2005 as in sql2005 there are function like OBJECT_DEFINITION , which returns the whole text of the procedure.


SELECT Name FROM sys.procedures WHERE OBJECT_DEFINITION(object_id) LIKE '%power%'

SELECT OBJECT_NAME(object_id) FROM sys.sql_modules WHERE Definition LIKE '%power%' AND OBJECTPROPERTY(object_id, 'IsProcedure') = 1
SELECT ROUTINE_NAME FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_DEFINITION LIKE '%powerr%' AND ROUTINE_TYPE = 'PROCEDURE'


Still its not 100% perfect. There are 3rd party tools available to do this i.e.Gplex Database (www.gplexdb.com)

SQL Server 2005-When a stored procedure, table or view was last modified

In SQL Server 2005 we can find out when table, view or stored procedure was last modified. This functionality was not available in previous versions of SQL server

--Store procedure last modified
SELECT * From information_schema.routines
--Stored modified within last 7 days

select DATEDIFF(d,LAST_ALTERED,GetDate()),* from information_schema.routines WHERE DATEDIFF(d,LAST_ALTERED,GetDate())<=7

--table last modified
SELECT [name], create_date, modify_date FROM sys.tables
--View last modified

SELECT [name], create_date,modify_date FROM sys.views

Retreive only Alpha numeric values in TSQL

Suppose we have a column in a database which containse numeric, alpha-numeric or alpha . Following query can be be used to retrieve only alphanumeric fields:

Sample:

Select * from tblUser Where Name like '%[0-9]%'

Delete dupplicate rows from a table when there is no primary key

Suppose we have a table tblStock with field StcokID which has following values:
GOOG
BA
GOOG
YAHOO
GOOG


Following query can be used to remove duplicate rows in this case:


DELETE TOP (SELECT COUNT(*) -1 FROM tblStock WHERE StockID = 'GOOG')
FROM tblStock  WHERE StockID = 'GOOG'

SQL stored Proc sample

Following Stored Proc sample is talen from MSDN

USE Northwind
GO
DROP PROCEDURE OrderSummary
GO
CREATE PROCEDURE OrderSummary @MaxQuantity INT OUTPUT AS
-- SELECT to return a result set summarizing
-- employee sales.
SELECT Ord.EmployeeID, SummSales = SUM(OrDet.UnitPrice * OrDet.Quantity)
FROM Orders AS Ord
     JOIN [Order Details] AS OrDet ON (Ord.OrderID = OrDet.OrderID)
GROUP BY Ord.EmployeeID
ORDER BY Ord.EmployeeID

-- SELECT to fill the output parameter with the
-- maximum quantity from Order Details.
SELECT @MaxQuantity = MAX(Quantity) FROM [Order Details]

-- Return the number of all items ordered.
RETURN (SELECT SUM(Quantity) FROM [Order Details])
GO

-- Test the stored procedure.

-- DECLARE variables to hold the return code
-- and output parameter.
DECLARE @OrderSum INT
DECLARE @LargestOrder INT

-- Execute the procedure, which returns
-- the result set from the first SELECT.
EXEC @OrderSum = OrderSummary @MaxQuantity = @LargestOrder OUTPUT

-- Use the return code and output parameter.
PRINT 'The size of the largest single order was: ' +
                 CONVERT(CHAR(6), @LargestOrder)
PRINT 'The sum of the quantities ordered was: ' +
                 CONVERT(CHAR(6), @OrderSum)
GO

The output from running this sample is:

EmployeeID  SummSales                  
----------- -------------------------- 
1           202,143.71                 
2           177,749.26                 
3           213,051.30                 
4           250,187.45                 
5           75,567.75                  
6           78,198.10                  
7           141,295.99                 
8           133,301.03                 
9           82,964.00                  
The size of the largest single order was: 130 
The sum of the quantities ordered was: 51317

SQL SERVER- SQL Statement to perform PIVOT functionality in SQL 2000

PIVOT and UNPIVOT are built in functions in SQL 2005 but in sql2000 we can use case statement with group by clause to Achieve same thing
Example:

SELECT

Name,

MAX(CASE WHEN Title = 'Visit 1' then DateResult else null END) AS 'Visit1',

MAX(CASE WHEN Title = 'Visit 2' then DateResult else null END) AS 'Visit2'

FROM PersonSchedule

group

by Name

Recompiling Stored Procedures

If we edit a stored procedure, it is recompiled the very first time it is executed. So the program using this Stored Procedure may see delay during this period (as it is being recompiled).

But we can force SQL Server to recompile Stored Procedure by using WITH RECOMPILE option

Example:exec usp_TestProcedure WITH RECOMPILE

SQL: CharToNum

*
* If a character fields is used as a number field, it will not sort properly as

* "200" comes before "5" with string sorting. This function converts all values

* such that values that are entirely numeric will sort correctly and before

* values that contain other characters.

*

*/

CREATE FUNCTION CharToNumber(@MyValue varchar(15)) RETURNS char(15) AS

BEGIN

IF ISNUMERIC(@MyValue) = 1

begin

DECLARE @MyValLength int

SET @MyValLength = len(@MyValue)



IF @MyValLength > 15

SET @MyValue = left(@MyValue,15)

ELSE IF @MyValLength < 15

SET @MyValue = space(15-@MyValLength) + @MyValue

END



RETURN @MyValue

END

GO

Database Mirroring fact for developers

Connection string should include FailOver parameter

Server = myServerAddress;database=My Database;Integrated Security=SSPI;Failover Partner=Failover Server

Note: The Failover Partner keyword is not supported by .NET Framework version 1.0 or 1.1 , so if you application is built in VisualStudio 2003 database Mirroring will not work. True

SQLServer-UDF-FormatDateTime

Following function can be used in SQL to get DateTime in specific format
create FUNCTION dbo.FormatDateTime
( @dt DATETIME,
@format VARCHAR(16)

)

RETURNS VARCHAR(64)

AS
BEGIN
DECLARE @dtVC VARCHAR(64)
SELECT @dtVC = CASE @format

WHEN 'LONGDATE' THEN

DATENAME(dw, @dt)
+ ',' + SPACE(1) + DATENAME(m, @dt)
+ SPACE(1) + CAST(DAY(@dt) AS VARCHAR(2)) + ',' + SPACE(1) + CAST(YEAR(@dt) AS CHAR(4))

WHEN 'LONGDATEANDTIME' THEN


DATENAME(dw, @dt)
+ ',' + SPACE(1) + DATENAME(m, @dt)
+ SPACE(1) + CAST(DAY(@dt) AS VARCHAR(2))
+ ',' + SPACE(1) + CAST(YEAR(@dt) AS CHAR(4))
+ SPACE(1) + RIGHT(CONVERT(CHAR(20), @dt
- CONVERT(DATETIME, CONVERT(CHAR(8), @dt, 112)), 22), 11)

WHEN 'SHORTDATE' THEN

LEFT(CONVERT(CHAR(19), @dt, 0), 11)

WHEN 'SHORTDATEANDTIME' THEN


REPLACE(REPLACE(CONVERT(CHAR(19), @dt, 0),
'AM', ' AM'), 'PM', ' PM')

WHEN 'UNIXTIMESTAMP' THEN

CAST(DATEDIFF(SECOND, '19700101', @dt) AS VARCHAR(64))

WHEN 'YYYYMMDD' THEN

CONVERT(CHAR(8), @dt, 112)

WHEN 'YYYY-MM-DD' THEN

CONVERT(CHAR(10), @dt, 23)

WHEN 'YYMMDD' THEN

CONVERT(VARCHAR(8), @dt, 12)

WHEN 'YY-MM-DD' THEN


STUFF(STUFF(CONVERT(VARCHAR(8), @dt, 12), 5
, 0, '-'), 3, 0, '-')

WHEN 'MMDDYY' THEN

REPLACE(CONVERT(CHAR(8), @dt, 10), '-', SPACE(0))

WHEN 'MM-DD-YY' THEN

CONVERT(CHAR(8), @dt, 10)

WHEN 'MM/DD/YY' THEN

CONVERT(CHAR(8), @dt, 1)

WHEN 'MM/DD/YYYY' THEN

CONVERT(CHAR(10), @dt, 101)

WHEN 'DDMMYY' THEN

REPLACE(CONVERT(CHAR(8), @dt, 3), '/', SPACE(0))

WHEN 'DD-MM-YY' THEN

REPLACE(CONVERT(CHAR(8), @dt, 3), '/', '-')

WHEN 'DD/MM/YY' THEN

CONVERT(CHAR(8), @dt, 3)

WHEN 'DD/MM/YYYY' THEN

CONVERT(CHAR(10), @dt, 103)

WHEN 'HH:MM:SS 24' THEN

CONVERT(CHAR(8), @dt, 8)

WHEN 'HH:MM 24' THEN

LEFT(CONVERT(VARCHAR(8), @dt, 8), 5)

WHEN 'HH:MM:SS 12' THEN

LTRIM(RIGHT(CONVERT(VARCHAR(20), @dt, 22), 11))

WHEN 'HH:MM 12' THEN

LTRIM(SUBSTRING(CONVERT(
VARCHAR(20), @dt, 22), 10, 5)
+ RIGHT(CONVERT(VARCHAR(20), @dt, 22), 3))

ELSE

'Invalid format specified'

END
RETURN @dtVC

END

Using dynamic Queries

//Example of dynamic query in SQL Server, following example use SQL Server pubs database

DECLARE @SQLSTRING VarChar(15)

SET @SQLSTRING = 'titleauthor'

SELECT @SQLString ='select au_id from '+ @SQLSTRING

EXEC (@SQLString)

How to retrieve return value of stored procedure in Query Analyzer

Following lines of code explains how to retrieve return value of stored procedure from Query Analyzer

Here I am assuming that our sp is called:usp_SelTest

declare @i int

exec @i =usp_SelTest @Parameter1 = 1
select @i as 'return value'

SQL SERVER - UDF - Get Name as per specific format

Following Sql function can be used to get Name as per specific format

--This function uses table employee of pubs database which comes with sql server


CREATE FUNCTION [dbo].fnEmployeeName(@ID varchar(15), @Format int = 1) RETURNS varchar(30) AS

/****** fnEmployeeName******/

-- Return Employee Name as per the required format

BEGIN
DECLARE @FIRSTNM varchar(15), @LASTNM varchar(15), @MIDDLENM varchar(15)
SELECT @FIRSTNM = fname, @LASTNM = lname, @MIDDLENM = minit FROM employee WHERE emp_id = @ID
DECLARE @Value varchar(103) IF @@ROWCOUNT > 0
begin
IF @Format=1 SET @Value = LTRIM(IsNull(@LASTNM,'') + IsNull(' ' + @FIRSTNM,'') + IsNull(' ' + @MIDDLENM,''))
Else
SET @Value = LTRIM(IsNull(@FIRSTNM,'') + IsNull(' ' + @LASTNM,'') + IsNull(' ' + @MIDDLENM,'')) END
RETURN @Value
END

SQL injection

SQL injection is a technique that exploits a security vulnerability occurring in the database layer of an application. The vulnerability is present when user input is either incorrectly filtered for string literal escape characters embedded in SQL statements or user input is not strongly typed and thereby unexpectedly executed. It is in fact an instance of a more general class of vulnerabilities that can occur whenever one programming or scripting language is embedded inside another. SQL injection is a subset of Code injection.


Example:

SELECT * From tblStock WHERE Ticker = 'Enter Stock Ticker here'

However, assume that the user enters the following:

GOOG'; drop table tblStock-- ....in this case tblStock can be dropped

Best coding practise to prevent SQL Injection is as follow:

1. Validate all User Input

* Never build Transacent statement directlt from User Input

* When working with XML document, validate all data with its schema as sson as it is entered

2. Use parameterized Query