cursors in sql server

A cursor is a set of rows together with a pointer that identifies a current row.

In other word, Cursor is a database object used by applications to manipulate data in a set on a row-by-row basis, its like recordset in the ASP and visual basic.

SQL Server is very good at handling sets of data. For example, you can use a single UPDATE statement to update many rows of data. There are times when you want to loop through a series of rows a perform processing for each row. In this case you can use a cursor.

The basic syntax of a cursor is:

DECLARE @AuthorID char(11)
 
DECLARE c1 CURSOR READ_ONLY
FOR
SELECT au_id
FROM authors

OPEN c1

FETCH NEXT FROM c1
INTO @AuthorID

WHILE @@FETCH_STATUS = 0
BEGIN

 PRINT @AuthorID

 FETCH NEXT FROM c1
 INTO @AuthorID

END

CLOSE c1
DEALLOCATE c1

The DECLARE CURSOR statement defines the SELECT statement that forms the basis of the cursor. You can do just about anything here that you can do in a SELECT statement. The OPEN statement statement executes the SELECT statement and populates the result set. The FETCH statement returns a row from the result set into the variable. You can select multiple columns and return them into multiple variables. The variable @@FETCH_STATUS is used to determine if there are any more rows. It will contain 0 as long as there are more rows. We use a WHILE loop to move through each row of the result set.
The READ_ONLY clause is important in the code sample above.  That dramatically improves the performance of the cursor.

In this example, I just print the contents of the variable. You can execute any type of statement you wish here. In a recent script I wrote I used a cursor to move through the rows in a table and call a stored procedure for each row passing it the primary key. Given that cursors are not very fast and calling a stored procedure for each row in a table is also very slow, my script was a resource hog. However, the stored procedure I was calling was written by the software vendor and was a very easy solution to my problem. In this case, I might have something like this:

EXEC spUpdateAuthor (@AuthorID)

instead of my Print statement. The CLOSE statement releases the row set and the DEALLOCATE statement releases the resources associated with a cursor.
If you are going to update the rows as you go through them, you can use the UPDATE clause when you declare a cursor. You'll also have to remove the READ_ONLY clause from above.

DECLARE c1 CURSOR FOR
SELECT au_id, au_lname
FROM authors
FOR UPDATE OF au_lname

You can code your UPDATE statement to update the current row in the cursor like this

UPDATE authors
SET au_lname = UPPER(Smith)
WHERE CURRENT OF c1

That covers the basics of cursors. You can check Books Online for more detailed information.

How to Create duplicate table in sql server 2005,2008

The "SELECT INTO" technique will create a table with the same columns but doesn't reproduce the keys, constraints, and defaults.


This query is used to create a dupliate table with same columns and data

Code:

select * into newtable from oldtable

How to Change Login Mode In SQL Server 2005

In Sql server you can login in two mode:

1. Windows Authentication

2. Sql Server Authentication

But you can only access SQL Server in both way by default if you only have selected both the login option when you install the SQL server.

But, If you forgot to select both the option while installing the SQL server, you can edit the option in following way:

1. Right Click on Server instances and view the Properties

2. Form the Property window and list at the left select the “Security” tab:

3. Select the option indicating both the login mode and Click OK.

4. Cheers!! 

MSSQL Server Tips and Tricks

The following are a wide range of tips and tricks I have collected over the years working with various versions or Microsoft's SQL Server. I always enjoy finding little snippets of code which are both simple and powerful. Why write complicated routines when one or two lines of code can do it?
Although you may very well find the following code useful, I don't really want small posts like this from detracting from the overall purpose of my blog, which is to help me develop my writing skills. I am therefore going to create a section on this bloggie called my Code Notebook which will hopefully serve two purposes:

1. It will provide me with an area to note down and share pieces of code which I am proud of creating and,
2. It will keep the main focus of the bloggie on improving my writing.

So without further ado, I present to you my SQL Server Tips and Tricks...

Date Only

Quite a useful function for producing or saving only the date part of the smalldatetime datatype. This can be useful if you only need to store the date or you need to group some results by dateonly.

Select
Convert([smalldatetime],floor(Convert([float],getdate(),(0))),(0))

Produce a Row or Table Checksum

This can be useful for checking if any of the contents of a row or table has changed without saving the entire contents of row or table.

Table Checksum
Select CheckSum_Agg(Binary_CheckSum(*)) From Table With (NOLOCK)
Row Checksum
Select CheckSum_Agg(Binary_CheckSum(*)) From Table Where Column = Value

Reindex an entire database

EXEC [sp_MSforeachtable] @command1 = "RAISERROR('DBCC DBREINDEX(''?'') ...',10,1) WITH NOWAIT DBCC DBREINDEX('?')"

Count Character in a String

This very simple statement allows you to count how many times a certain character appears in a string.

Select Len('///xxx') - Len(Replace('///xxx', '/', ''))

Single & Multi User Mode

By default, a database is set to Multi User Mode, allowing more than one connection to connect to the database at a time. However, it may prove useful at times, for example database maintenance, to restrict the database to only 1 connection. This allows that 1 user to carry out work on the database without any locks.

ALTER DATABASE MyDatabase SET SINGLE_USER

ALTER DATABASE MyDatabase SET MULTI_USER

Smart Date Ranges

Find records which date falls somewhere inside the current week.

where dateadd( week, datediff( week, 0, TransDate ), 0 ) =
dateadd( week, datediff( week, 0, getdate() ), 0 )

Find records which date occurred last week.

where dateadd( week, datediff( week, 0, TransDate ), 0 ) =
dateadd( week, datediff( week, 0, getdate() ) - 1, 0 )

Returns the date for the beginning of the current week.

select dateadd( week, datediff( week, 0, getdate() ), 0 )

Returns the date for the beginning of last week.

select dateadd( week, datediff( week, 0, getdate() ) - 1, 0 )

Drop all Database Connections

Although this could be a very dangerous script to run, it is very useful when you need to do some maintenance on a database and there are some rouge connections open stopping you from doing so. The script will run through all users currently connection to the specified database and kill their connection.

Use Master
Go
Declare @dbname sysname
Set @dbname = 'name of database you want to drop connections from'
Declare @spid int
Select @spid = min(spid) from master.dbo.sysprocesses
where dbid = db_id(@dbname)
While @spid Is Not Null
Begin
        Execute ('Kill ' + @spid)
        Select @spid = min(spid) from master.dbo.sysprocesses
        where dbid = db_id(@dbname) and spid > @spid
End

If anyone know  more tips or tricks please share them with me...

SqlQuery to delete all procedures and tables

To delete all tables with a single query

sp_MSforeachtable "DROP TABLE ? PRINT '? dropped' "


==============================================


To delete all procedures from sqlserver

CREATE Procedure [dbo].[DeleteAllProcedures]
As
      declare @procName varchar(500)
      declare cur cursor
            for select [name] from sys.objects where type = 'p'
      open cur
      fetch next from cur into @procName
      while @@fetch_status = 0
      begin
            if @procName <> 'DeleteAllProcedures'
                  exec('drop procedure ' + @procName)
                  fetch next from cur into @procName
      end
      close cur
      deallocate cur
--Go
      --Grant Execute On dbo.DeleteAllProcedures To Public
--Go

SQL SERVER – Database Coding Standards and Guidelines – Part 2

SQL Server Database Coding Standards and Guidelines – Part 2

Coding

• Optimize queries using the tools provided by SQL Server⁵
• Do not use SELECT *
• Return multiple result sets from one stored procedure to avoid trips from the application server to SQL server
• Avoid unnecessary use of temporary tables
  • Use ‘Derived tables’ or CTE (Common Table Expressions) wherever possible, as they perform better⁶
• Avoid using <> as a comparison operator
  • Use ID IN(1,3,4,5) instead of ID <> 2
• Use SET NOCOUNT ON at the beginning of stored procedures⁷
• Do not use cursors or application loops to do inserts⁸
  • Instead, use INSERT INTO
• Fully qualify tables and column names in JOINs
• Fully qualify all stored procedure and table references in stored procedures.
• Do not define default values for parameters.
  • If a default is needed, the front end will supply the value.
• Do not use the RECOMPILE option for stored procedures.
• Place all DECLARE statements before any other code in the procedure.
• Do not use column numbers in the ORDER BY clause.
• Do not use GOTO.
• Check the global variable @@ERROR immediately after executing a data manipulation statement (like INSERT/UPDATE/DELETE), so that you can rollback the transaction if an error occurs
  • Or use TRY/CATCH
• Do basic validations in the front-end itself during data entry
• Off-load tasks, like string manipulations, concatenations, row numbering, case conversions, type conversions etc., to the front-end applications if these operations are going to consume more CPU cycles on the database server
• Always use a column list in your INSERT statements.
  • This helps avoid problems when the table structure changes (like adding or dropping a column).
• Minimize the use of NULLs, as they often confuse front-end applications, unless the applications are coded intelligently to eliminate NULLs or convert the NULLs into some other form.
  • Any expression that deals with NULL results in a NULL output.
  • The ISNULL and COALESCE functions are helpful in dealing with NULL values.
• Do not use the identitycol or rowguidcol.
• Avoid the use of cross joins, if possible.
• When executing an UPDATE or DELETE statement, use the primary key in the WHERE condition, if possible. This reduces error possibilities.
• Avoid using TEXT or NTEXT datatypes for storing large textual data.⁹
  • Use the maximum allowed characters of VARCHAR instead
• Avoid dynamic SQL statements as much as possible.¹⁰
• Access tables in the same order in your stored procedures and triggers consistently.¹¹
• Do not call functions repeatedly within your stored procedures, triggers, functions and batches.¹²
• Default constraints must be defined at the column level.
• Avoid wild-card characters at the beginning of a word while searching using the LIKE keyword, as these results in an index scan, which defeats the purpose of an index.
• Define all constraints, other than defaults, at the table level.
• When a result set is not needed, use syntax that does not return a result set.¹³
• Avoid rules, database level defaults that must be bound or user-defined data types. While these are legitimate database constructs, opt for constraints and column defaults to hold the database consistent for development and conversion coding.
• Constraints that apply to more than one column must be defined at the table level.
• Use the CHAR data type for a column only when the column is non-nullable.¹⁴
• Do not use white space in identifiers.
• The RETURN statement is meant for returning the execution status only, but not data.

Reference:
5) Use the graphical execution plan in Query Analyzer or SHOWPLAN_TEXT or SHOWPLAN_ALL commands to analyze your queries. Make sure your queries do an “Index seek” instead of an “Index scan” or a “Table scan.” A table scan or an index scan is a highly undesirable and should be avoided where possible.
6) Consider the following query to find the second highest offer price from the Items table:

SELECT MAX(Price)
    FROM Products
    WHERE ID IN
    (
    SELECT TOP 2 ID
        FROM Products
        ORDER BY Price DESC
    )
            

The same query can be re-written using a derived table, as shown below, and it performs generally twice as fast as the above query:

SELECT MAX(Price)
    FROM
    (
    SELECT TOP 2 Price
        FROM Products
        ORDER BY Price DESC
    )
            

7) This suppresses messages like ‘(1 row(s) affected)’ after executing INSERT, UPDATE, DELETE and SELECT statements. Performance is improved due to the reduction of network traffic.
8) Try to avoid server side cursors as much as possible. Always stick to a ‘set-based approach’ instead of a ‘procedural approach’ for accessing and manipulating data. Cursors can often be avoided by using SELECT statements instead. If a cursor is unavoidable, use a WHILE loop instead. For a WHILE loop to replace a cursor, however, you need a column (primary key or unique key) to identify each row uniquely.
9) You cannot directly write or update text data using the INSERT or UPDATE statements. Instead, you have to use special statements like READTEXT, WRITETEXT and UPDATETEXT. So, if you don’t have to store more than 8KB of text, use the CHAR(8000) or VARCHAR(8000) datatype instead.
10) Dynamic SQL tends to be slower than static SQL, as SQL Server must generate an execution plan at runtime. IF and CASE statements come in handy to avoid dynamic SQL.
11) This helps to avoid deadlocks. Other things to keep in mind to avoid deadlocks are:

• Keep transactions as short as possible.
• Touch the minimum amount of data possible during a transaction.
• Never wait for user input in the middle of a transaction.
• Do not use higher level locking hints or restrictive isolation levels unless they are absolutely needed.

12) You might need the length of a string variable in many places of your procedure, but don’t call the LEN function whenever it’s needed. Instead, call the LEN function once and store the result in a variable for later use.
13)

IF EXISTS (
    SELECT 1
        FROM Products
        WHERE ID = 50)
            

Instead Of:

IF EXISTS (
    SELECT COUNT(ID)
        FROM Products
        WHERE ID = 50)
            

14) CHAR(100), when NULL, will consume 100 bytes, resulting in space wastage. Preferably, use VARCHAR(100) in this situation. Variable-length columns have very little processing overhead compared with fixed-length columns.
Complete Series of Database Coding Standards and Guidelines
SQL SERVER Database Coding Standards and Guidelines – Introduction
SQL SERVER – Database Coding Standards and Guidelines – Part 1
SQL SERVER – Database Coding Standards and Guidelines – Part 2
SQL SERVER Database Coding Standards and Guidelines Complete List Download
Reference : Pinal Dave (http://blog.SQLAuthority.com)

SQL SERVER – database-coding-standards-and-guidelines-part-1

SQL Server Database Coding Standards and Guidelines – Part 1

Naming

Tables: Rules: Pascal notation; end with an ‘s’

• Examples: Products, Customers
• Group related table names¹

Stored Procs: Rules: sp<App Name>_[<Group Name >_]<Action><table/logical instance>

• Examples: spOrders_GetNewOrders, spProducts_UpdateProduct

Triggers: Rules: TR_<TableName>_<action>

• Examples: TR_Orders_UpdateProducts
• Notes: The use of triggers is discouraged

Indexes: Rules: IX_<TableName>_<columns separated by _>

• Examples: IX_Products_ProductID

Primary Keys: Rules: PK_<TableName>

• Examples: PK_Products

Foreign Keys: Rules: FK_<TableName1>_<TableName2>

• Example: FK_Products_Orderss

Defaults: Rules: DF_<TableName>_<ColumnName>

• Example: DF_Products_Quantity

Columns: If a column references another table’s column, name it <table name>ID

• Example: The Customers table has an ID column
• The Orders table should have a CustomerID column

General Rules:

• Do not use spaces in the name of database objects
  • Do not use SQL keywords as the name of database objects
  • In cases where this is necessary, surround the
• object name with brackets, such as [Year]
• Do not prefix stored procedures with ‘sp_’²
• Prefix table names with the owner name³

Structure

• Each table must have a primary key
  • In most cases it should be an IDENTITY column named ID
• Normalize data to third normal form
  • Do not compromise on performance to reach third normal form. Sometimes, a little de-normalization results in better performance.
• Do not use TEXT as a data type; use the maximum allowed characters of VARCHAR instead
• In VARCHAR data columns, do not default to NULL; use an empty string instead
• Columns with default values should not allow NULLs
• As much as possible, create stored procedures on the same database as the main tables they will be accessing

Formatting

• Use upper case for all SQL keywords
  • SELECT, INSERT, UPDATE, WHERE, AND, OR, LIKE, etc.
• Indent code to improve readability
• Comment code blocks that are not easily understandable
  • Use single-line comment markers(–)
  • Reserve multi-line comments (/*.. ..*/) for blocking out sections of code
• Use single quote characters to delimit strings.
  • Nest single quotes to express a single quote or apostrophe within a string
    • For example, SET @sExample = ‘SQL”s Authority’
• Use parentheses to increase readability
  • WHERE (color=’red’ AND (size = 1 OR size = 2))
• Use BEGIN..END blocks only when multiple statements are present within a conditional code segment.
• Use one blank line to separate code sections.
• Use spaces so that expressions read like sentences.
  • fillfactor = 25, not fillfactor=25
• Format JOIN operations using indents
  • Also, use ANSI Joins instead of old style joins⁴
• Place SET statements before any executing code in the procedure.

Reference:
1) Group related table names:

Products_USA
Products_India
Products_Mexico

2) The prefix sp_ is reserved for system stored procedures that ship with SQL Server. Whenever SQL Server encounters a procedure name starting with sp_, it first tries to locate the procedure in the master database, then it looks for any qualifiers (database, owner) provided, then it tries dbo as the owner. Time spent locating the stored procedure can be saved by avoiding the “sp_” prefix.

3) This improves readability and avoids unnecessary confusion. Microsoft SQL Server Books Online states that qualifying table names with owner names helps in execution plan reuse, further boosting performance.

4)
False code:
SELECT *
FROM Table1, Table2
WHERE Table1.d = Table2.c

True code:
SELECT *
FROM Table1
INNER JOIN Table2 ON Table1.d = Table2.c

Complete Series of Database Coding Standards and Guidelines
SQL SERVER Database Coding Standards and Guidelines – Introduction
SQL SERVER – Database Coding Standards and Guidelines – Part 1
SQL SERVER – Database Coding Standards and Guidelines – Part 2
SQL SERVER Database Coding Standards and Guidelines Complete List Download
Reference : Pinal Dave (http://blog.SQLAuthority.com)

SQL Query Analyzer Keyboard Shortcuts

This table displays the keyboard shortcuts available in SQL Query Analyzer.

Activity	Shortcut
Bookmarks: Clear all bookmarks.	CTRL-SHIFT-F2
Bookmarks: Insert or remove a bookmark (toggle).	CTRL+F2
Bookmarks: Move to next bookmark.	F2
Bookmarks: Move to previous bookmark.	SHIFT+F2
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
Editing: Clear the active Editor pane.	CTRL+SHIFT+DEL
Editing: Comment out code.	CTRL+SHIFT+C
Editing: Copy. You can also use CTRL+INSERT.	CTRL+C
Editing: Cut. You can also use SHIFT+DEL.	CTRL+X
Editing: Decrease indent.	SHIFT+TAB
Editing: Delete through the end of a line in the Editor pane.	CTRL+DEL
Editing: Find.	CTRL+F
Editing: Go to a line number.	CTRL+G
Editing: Increase indent.	TAB
Editing: Make selection lowercase.	CTRL+SHIFT+L
Editing: Make selection uppercase.	CTRL+SHIFT+U
Editing: Paste. You can also use SHIFT+INSERT.	CTRL+V
Editing: Remove comments.	CTRL+SHIFT+R
Editing: Repeat last search or find next.	F3
Editing: Replace.	CTRL+H
Editing: Select all.	CTRL+A
Editing: Undo.	CTRL+Z
Execute a query. You can also use CTRL+E (for backward compatibility).	F5
Help for SQL Query Analyzer.	F1
Help for the selected Transact-SQL statement.	SHIFT+F1
Navigation: Switch between query and result panes.	F6
Navigation: Switch panes.	Shift+F6
Navigation: Window Selector.	CTRL+W
New Query window.	CTRL+N
Object Browser (show/hide).	F8
Object Search.	F4
Parse the query and check syntax.	CTRL+F5
Print.	CTRL+P
Results: Display results in grid format.	CTRL+D
Results: Display results in text format.	CTRL+T
Results: Move the splitter.	CTRL+B
Results: Save results to file.	CTRL+SHIFT+F
Results: Show Results pane (toggle).	CTRL+R
Save.	CTRL+S
Templates: Insert a template.	CTRL+SHIFT+INSERT
Templates: Replace template parameters.	CTRL+SHIFT+M
Tuning: Display estimated execution plan.	CTRL+L
Tuning: Display execution plan (toggle ON/OFF).	CTRL+K
Tuning: Index Tuning Wizard.	CTRL+I
Tuning: Show client statistics	CTRL+SHIFT+S
Tuning: Show server trace.	CTRL+SHIFT+T
Use database.	CTRL+U