Microsoft SQL Server 2005/2008

Calculate Execution Time of the Query

2011-03-15T21:57:00.000-07:00

Method 1:

DECLARE @StartTime datetime
,@EndTime datetime

SELECT @StartTime=GETDATE()

SELECT * FROM tblmastertable ORDER BY Insertion_time DESC

SELECT @EndTime=GETDATE()

SELECT DATEDIFF(ms,@StartTime,@EndTime) AS [Duration in millisecs]

Method 2:

SET STATISTICS TIME ON

SELECT * FROM tblmastertable ORDER BY Insertion_time DESC

SET STATISTICS TIME OFF

Default Update_Time on Every update on table

2011-03-15T02:30:00.000-07:00

CREATE TABLE [tblBatch]
(
Batch_id int identity(1, 1) Primary key,
Batch_Name nvarchar(255),
Create_Time datetime NOT NUll default CURRENT_TIMESTAMP,
Update_Time datetime Not Null default CURRENT_TIMESTAMP
)

By default Create_Time and Update_Time will have CURRENT_TIMESTAMP

ALTER TRIGGER trgBatch_Modify_Time
ON tblBatch
AFTER UPDATE
AS
BEGIN
UPDATE tblBatch
SET Update_Time = GETDATE()
WHERE Batch_ID = (SELECT Batch_ID From inserted)
END

Above define trigger will enable to update update_Time column with current date

Table Valued Parameters in SQL Server 2008

2011-02-24T21:14:00.000-08:00

SQL Server 2008 introduces the ability to pass a table data type into stored procedures and functions. The table parameter feature can greatly ease the development process because you no longer need to worry about constructing and parsing XML data

Practical Example of the Table Valued Parameter

-- Creating table tblProductHistory to hold data of Products

IF OBJECT_ID('tblProductHistory')>0
DROP TABLE tblProductHistory;
GO
CREATE TABLE [dbo].[tblProductHistory]
(
[Product_ID] [int] IDENTITY(1,1) NOT NULL PRIMARY KEY,
[Product_Name] [varchar](250) NULL,
[Intro_Date] [datetime] NULL,
[Sale_Price] [money] NULL
)
GO

-- Creating Type in Database engine to hold datatime as variable

CREATE TYPE ProductHistoryTableType AS TABLE
(
[Product_Name] [varchar](250) NULL,
[Into_Date] [datetime] NULL,
[Sale_Price] [money] NULL
)
GO

-- Procedure will use the TableType define above as parameter

ALTER PROCEDURE proc_InsertExportProducts
(
@TableVariable ProductHistoryTableType READONLY
)
AS
BEGIN

INSERT INTO [tblProductHistory]
(
Product_Name, Intro_Date, Sale_Price
)
SELECT
Product_Name, Into_Date, Sale_Price
FROM
@TableVariable
WHERE
Product_Name Like '%Export%' -- All product with Export text in their name

END
GO

-- Adding some test data in variable of Type 'ProductHistoryTableType'

DECLARE @DataTable AS ProductHistoryTableType

DECLARE @i SMALLINT
SET @i = 1

WHILE (@i <=1000)
BEGIN

INSERT INTO @DataTable(Product_Name, Into_Date, Sale_Price)
VALUES ('Semi-Connectors', DATEADD(mm, @i, '3/11/1997'), DATEPART(ms, GETDATE()) + (@i + 57))

INSERT INTO @DataTable(Product_Name, Into_Date, Sale_Price)
VALUES('Export-SChips', DATEADD(mm, @i, '3/11/1956'), DATEPART(ms, GETDATE()) + (@i + 13))

INSERT INTO @DataTable(Product_Name, Into_Date, Sale_Price)
VALUES('Export-Diodes', DATEADD(mm, @i, '3/11/1975'), DATEPART(ms, GETDATE()) + (@i + 29))

SET @i = @i + 1
END

-- Execute the stored procedure with table variable - passing @DataTable

EXECUTE proc_InsertExportProducts
@TableVariable = @DataTable

-- Resultant of the procedure in the main table

SELECT * FROM [tblProductHistory]

How to calculate running total in Sql Server

2011-02-21T22:59:00.000-08:00

Example 1:

SELECT SalesOrderID,
OrderDate = CONVERT(date, OrderDate),
O.TotalDue,
(SELECT sum(TotalDue)
FROM AdventureWorks2008.Sales.SalesOrderHeader
WHERE SalesOrderID <= O.SalesOrderID) 'Running Total'FROM AdventureWorks2008.Sales.SalesOrderHeader OGO

/* results

SalesOrderID OrderDate TotalDue Running Total
43659 2001-07-01 27231.5495 27231.5495
43660 2001-07-01 1716.1794 28947.7289
43661 2001-07-01 43561.4424 72509.1713
43662 2001-07-01 38331.9613 110841.1326
43663 2001-07-01 556.2026 111397.3352

*/

Example 2:

CREATE TABLE tblAccounts
(
Trans_ID int IDENTITY(1,1),
Trans_Date datetime,
Balance float
)
GO

insert into tblAccounts(Trans_Date,Balance) values ('1/1/2000',100)
insert into tblAccounts(Trans_Date,Balance) values ('1/2/2000',101)
insert into tblAccounts(Trans_Date,Balance) values ('1/3/2000',102)
insert into tblAccounts(Trans_Date,Balance) values ('1/4/2000',103)
insert into tblAccounts(Trans_Date,Balance) values ('1/5/2000',104)
insert into tblAccounts(Trans_Date,Balance) values ('1/6/2000',105)
insert into tblAccounts(Trans_Date,Balance) values ('1/7/2000',106)
insert into tblAccounts(Trans_Date,Balance) values ('1/8/2000',107)
insert into tblAccounts(Trans_Date,Balance) values ('1/9/2000',108)
insert into tblAccounts(Trans_Date,Balance) values ('1/10/2000',109)
insert into tblAccounts(Trans_Date,Balance) values ('1/11/2000',200)
insert into tblAccounts(Trans_Date,Balance) values ('1/12/2000',201)
insert into tblAccounts(Trans_Date,Balance) values ('1/13/2000',202)
insert into tblAccounts(Trans_Date,Balance) values ('1/14/2000',203)
insert into tblAccounts(Trans_Date,Balance) values ('1/15/2000',204)
insert into tblAccounts(Trans_Date,Balance) values ('1/16/2000',205)
insert into tblAccounts(Trans_Date,Balance) values ('1/17/2000',206)
insert into tblAccounts(Trans_Date,Balance) values ('1/18/2000',207)
insert into tblAccounts(Trans_Date,Balance) values ('1/19/2000',208)
insert into tblAccounts(Trans_Date,Balance) values ('1/20/2000',209)
GO

-- Appling Cross Join on tblAccounts

SELECT A.Trans_ID AS ID,
B.Trans_ID AS BID,
B.Balance
FROM tblAccounts A CROSS JOIN
tblAccounts B
WHERE B.Trans_ID BETWEEN A.Trans_ID-0 AND A.Trans_ID
AND A.Trans_ID>0

-- Getting running total by using cross-join

SELECT TRANS_ID, SUM(Balance) As Running_total

FROM (SELECT A.Trans_ID AS TRANS_ID,
B.Trans_ID AS BID,
B.Balance
FROM tblAccounts A CROSS JOIN
tblAccounts B
WHERE B.Trans_ID BETWEEN A.Trans_ID-0 AND A.Trans_ID
AND A.Trans_ID>0) K

GROUP BY Trans_ID
ORDER BY TRANS_ID

Custom Paging Data Procedure

2011-02-08T04:33:00.000-08:00

CREATE PROCEDURE procGetCustomPagingData
@PageIndex INT = 1
,@PageSize INT = 10
,@RecordCount INT OUTPUT
AS
BEGIN
SET NOCOUNT ON;
SELECT ROW_NUMBER() OVER
(
ORDER BY [ID] ASC
)AS RowNumber
,[Sales_Accounts]
,[Other_Income]
,[Total_Revenue_A]
,[NetProfit]
INTO #Results
FROM [tblmastertable]

SELECT @RecordCount = COUNT(*)
FROM #Results

SELECT * FROM #Results
WHERE RowNumber BETWEEN(@PageIndex -1) * @PageSize + 1 AND(((@PageIndex -1) * @PageSize + 1) + @PageSize) - 1

DROP TABLE #Results
END

Difference between Rollup and Cube

2010-04-18T22:15:00.000-07:00

You can use the CUBE and ROLLUP operators to generate summary information in a query. A CUBE operator generates a result set that shows the aggregates for all combinations of values in the selected columns. A ROLLUP operator generates a result set showing the aggregates for a hierarchy of values in the selected columns. Both the CUBE and ROLLUP operators return data in relational form.

The CUBE operator generates a multidimensional cube result set. A multidimensional cube is an expansion of fact data, or the data that records individual events.
This expansion is based on columns that the user wants to analyze. These columns are called dimensions. A cube is a result set that contains a cross tabulation of all the possible combinations of the dimensions.

The CUBE operator is specified in the GROUP BY clause of a SELECT statement. The select list contains the dimension columns and aggregate function expressions. The GROUP BY specifies the dimension columns by using the WITH CUBE keywords.
The result set contains all possible combinations of the values in the dimension columns, together with the aggregate values from the underlying rows that match that combination of dimension values.

The ROLLUP operator is useful in generating reports that contain aggregate values. The ROLLUP operator generates a result set that is similar to the result set generated by the CUBE operator.

However, the difference between the CUBE and ROLLUP operator is that the CUBE generates a result set that shows the aggregates for all combinations of values in the selected columns. By contrast, the ROLLUP operator returns only the specific result set.
The ROLLUP operator generates a result set that shows the aggregates for a hierarchy of values in the selected columns. Also, the ROLLUP operator provides only one level of summarization, for example, the cumulative running sum in a table.

Trigger for Deletion of Child Records w.r.t. to Parent Table

2009-09-22T05:36:00.000-07:00

Parent table: tblProduct PK Product_code

Child table 1: tblCategory FK Product_code

Child table2: tblDelivery FK Prodcut_code

Create TRIGGER [Delete_After_tblProduct]

ON [dbo].[tblProduct]

AFTER DELETE

AS

BEGIN

Begin try

declare @product_code bigint;

select @product_code=project_code from deleted;

delete from tblcategory where product_code=@product_code;

delete from tblDelivery where product_code=@product_code;

end try

begin catch

end catch

END

Temporary Stored Procedures

2009-07-13T23:30:00.000-07:00

SQL Server provides the ability to create private and global temporary stored procedures.
Temporary stored procedures are analogous to temporary tables in that they can be
created with the # and ## prefixes added to the procedure name. The # prefix denotes a
local temporary stored procedure; ## denotes a global temporary stored procedure. A local
temporary stored procedure can be executed only by the connection that created it, and
the procedure is automatically deleted when the connection is closed. A global temporary stored procedure can be accessed by multiple connections and exists until the connection
used by the user who created the procedure is closed and any currently executing versions
of the procedure by any other connections are completed.
If a stored procedure not prefixed with # or ## is created directly in the tempdb database,
the stored procedure exists until SQL Server is shut down. Procedures created directly in
tempdb continue to exist even after the creating connection is terminated.
Temporary stored procedures are provided for backward compatibility with earlier
versions of SQL Server that did not support the reuse of execution plans for T-SQL statements
or batches. Applications connecting to SQL Server 2000 and higher should use the
sp_executesql system stored procedure instead of temporary stored procedures.

What is SQL Injection

2009-07-06T23:38:00.000-07:00

SQL Injection happens when a developer accepts user input that is directly placed into a SQL Statement and doesn't properly filter out dangerous characters. This can allow an attacker to not only steal data from your database, but also modify and delete it. Certain SQL Servers such as Microsoft SQL Server contain Stored and Extended Procedures (database server functions). If an attacker can obtain access to these Procedures it may be possible to compromise the entire machine. Attackers commonly insert single qoutes into a URL's query string, or into a forms input field to test for SQL Injection. If an attacker receives an error message like the one below there is a good chance that the application is vulnerable to SQL Injection.

SQL injection attacks take advantage of code that does not filter input that is being entered directly into a form. Susceptible applications are applications that take direct user input and then generate dynamic SQL that is executed via back-end code. For example say you have a logon form that accepts a user name and password. Once authenticated against the database, the application then sets a session value, or some other token for allowing the user to access the protected data.
Take a logon form for example, here you have two basic form elements, a textbox for accepting a user name, and a password box for the password.

Dim SQL As String = "SELECT Count(*) FROM Users WHERE UserName = '" & _
username.text & "' AND Password = '" & password.text & "'"
Dim thisCommand As SQLCommand = New SQLCommand(SQL, Connection)
Dim thisCount As Integer = thisCommand.ExecuteScalar()
In the previous code block it executes the built SQL script directly, if count is greater than one, then you know the values entered in for the user name and password were the ones matching the database.
Now with that code in the previous example, suppose someone entered the following string into your username text box:
' or 0=0 --
The apostrophe will close the username value being sent to the SQL query, then pass another argument to the SQL query, after the last argument it then comments out the rest of the query using the "--". Since the second argument they entered into your texbox is an "or" statement, the first check on the user name doesn't matter, and since 0 is always going to equal 0 the script will execute successfully and return a positive logon. Guess what? Your intruder now has access to your application.
Ok so maybe they can logon into your application, but what else can they do? Let's take another example of SQL injection, as in the previous example of using the apostrophe to terminate the value, and proceed on to another argument, lets do this, but using something that can really ruin your application's data and day:
'; drop table users --
Definitely something that can ruin your day. Of course this type of an attack you'll probably notice pretty quick. Other SQL commands can then be entered to determine your database's structure, and return all user names and passwords from the database. You make it even easier for the attacker if you do not provide some ambiguous error message and provide the error message returned from .NET. This error message can provide critical information they need to determine what to enter in your form in order to obtain information.
SQL Injection Prevention
One method of preventing SQL injection is to avoid the use of dynamically generated SQL in your code. By using parameterized queries and stored procedures, you then make it impossible for SQL injection to occur against your application. For example, the previous SQL query could have been done in the following way in order to avoid the attack demonstrated in the example:
Dim thisCommand As SQLCommand = New SQLCommand("SELECT Count(*) " & _
"FROM Users WHERE UserName = @username AND Password = @password", Connection)
thisCommand.Parameters.Add ("@username", SqlDbType.VarChar).Value = username
thisCommand.Parameters.Add ("@password", SqlDbType.VarChar).Value = password
Dim thisCount As Integer = thisCommand.ExecuteScalar()
By passing parameters you avoid many types of SQL injection attacks, and even better method of securing your database access is to use stored procedures. Stored procedures can secure your database by restricting objects within the database to specific accounts, and permitting the accounts to just execute stored procedures. Your code then does all database access using this one account that only has access to execute stored procedures. You do not provide this account any other permissions, such as write, which would allow an attacker to enter in SQL statement to executed against your database. Any interaction to your database would have to be done using a stored procedure which you wrote and is in the database itself, which is usually inaccessible to a perimeter network or DMZ.
So if you wanted to do the authentication via a stored procedure, it may look like the following:
Dim thisCommand As SQLCommand = New SqlCommand ("proc_CheckLogon", Connection)
thisCommand.CommandType = CommandType.StoredProcedure
thisCommand.Parameters.Add ("@username", SqlDbType.VarChar).Value = username
thisCommand.Parameters.Add ("@password", SqlDbType.VarChar).Value = password
thisCommand.Parameters.Add ("@return", SqlDbType.Int).Direction = ParameterDirection.ReturnValue
Dim thisCount As Integer = thisCommand.ExecuteScalar()

Finally, ensure you provide very little information to the user when an error does occur. If there is database access failure, make sure you don't dump out the entire error message. Always try to provide the least amount of information possible to the users. Besides, do you want them to start helping you to debug your code? If not, why provide them with debugging information?
By following these tips for your database access you're on your way to preventing unwanted eyes from viewing your data.

Call Views in Store procedure

2009-05-07T03:09:00.001-07:00

Step1) Create a view with name “EmpTblView” as follows:

create view EmpTblView
as
select EName,Sal
from EmpTbl

Step2) Create procedure which calls the above View as follows:

create procedure EmpTblSP
as
begin
select * from EmpTblView
end

Step3) now run the stored procedure as follows:
exec EmpTblSP

Views in SQL Server

2009-05-07T03:08:00.000-07:00

Purpose of Views

A view is a virtual table. A view serves as a security mechanism. If we have several tables in a database and we want to view only specific columns from specific tables we can go for views. Using view we can avoid the complex queries.

Views advantages

1.Change behavior without changing code. You can alter your application at runtime without worrying about breaking code or making a PHP syntax error. (Note: since views that are stored in the database do not have revision control, changes made like this cannot simply be undone... be warned)

2.Many options for presentation. Different view types mean that you can change the presentation of the data you fetch.

3.Black box. You don't need to write or read any PHP code to get started with Views.

4.Data safety. Malicious users can do a lot of damage without proper data safety testing. Views goes through the trouble to scrub all data it presents, so you can feel assured that as long as the module providing the data properly implements the Views API, its data will be safe.

5.Open architecture. If you are a developer, you can write to the Views API and add features, presentation styles and behaviors to Views in custom modules. These are then reusable in other views that you later create.

6.Community benefit. You will be the recipient of an enormous amount of Views code that is being contributed and improved by the community. There will be Views features available tomorrow that weren't here today. There will be people fixing Views bugs while you sleep. This won't be the case for your custom code.

7.Reuse views. Build a basic view that does something useful, export it, and then use it on the next site you build.

8.Tight integration with CCK, Voting API, and many other modules that you might already be using. Every contrib module can potentially extend Views, so make sure and take inventory of what modules you plan to use when evaluating Views on its own.

9.Panels integration. Panels 2 is especially cool, and the Views integration is spectacular. Do some research here before you decide against Views.

10.Programmatic handling of views. You can instantiate views objects in PHP code and manipulate them in many ways. The Views API provides several points of access to the views object, so if you are really unhappy with what you can achieve with the Views UI, you can grab control of the object itself.

11.Documentation. There is more documentation about Views than there is about your custom code.

12.Page, menu and block integration. Views gives you page views and block views, and lets you instantiate menu items as well.

13.Views 2. Super cool and way more flexible than Views 1. Research Views 2 before deciding against Views.

14.Exposed filters: Views gives you a lot of control over creating form elements to filter your view. Writing FAPI code is bulky and verbose. Nothing against FAPI, but creating all those dropdown selects, autocomplete text fields and checkboxes is a lot of work. You can still use formalter to get to the exposed filters submission and validation handlers, so you don't lose any control by going with Views here.

Restrictions of View

We cannot create a trigger or an index on a view. The only way to change the sort of the result set in a view is to use ORDER BY clauses in your select statement that runs against the view. A view contain up to 250 columns.

We cannot use the UNION operators within a CREATE VIEW statement. But we can use the UNION operator to merge two views into a single result set.

Modification statements (INSERT, UPDATE, DELETE) are allowed on multi table views if the statement affects only one base table at a time.