Chittibabu: Sql Queries and Information

Showing posts with label Sql Queries and Information. Show all posts

Thursday, August 4, 2011

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.

Thursday, July 21, 2011

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!!

Tuesday, July 12, 2011

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

Tuesday, June 21, 2011

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

Wednesday, February 2, 2011

Advantages of Stored Procedures

Using stored procedures provides many advantages over executing large and complex SQL
batches from client applications. The following are some of them:
. Modular programming—Subroutines and functions are often used in ordinary 3GL
and 4GL languages (such as C, C++, and Microsoft Visual Basic) to break code into
smaller, more manageable pieces. The same advantages are achieved when using
stored procedures, with the difference that the stored procedure is stored in SQL
Server and can be called by any client application.
. Restricted, function-based access to tables—Someone can have access to execute a
stored procedure without having permissions to operate directly on the underlying
tables.
. Reduced network traffic—Stored procedures can consist of many individual SQL
statements but can be executed with a single statement. This allows you to reduce
the number and size of calls from the client to the server.
. Faster execution—Stored procedures’ query plans are kept in memory after the first
execution. The code doesn’t have to be reparsed and reoptimized on subsequent
executions.
. Enforced consistency—If users modify data only through stored procedures, problems
that often result from ad hoc modifications (such as omitting a crucial WHERE
clause) are eliminated.
. Reduced operator and programmer errors—Because less information is being
passed, complex tasks can be executed more easily, with less likelihood of
SQL errors.
. Automating complex or sensitive transactions—If all modifications of certain
tables take place in stored procedures, you can guarantee the data integrity on those
tables.

---------------------------------------------------------------------------------

Stored procedures are compiled and are the fastest possible
means of executing a batch or query.

Executing the processing at the server instead of the desktop
greatly reduces network traffic.

Stored procedures offer modularity and are an easy means of deploying features and
code changes. If the front-end application calls a stored procedure to perform some
processing, modifying a stored procedure in a single location upgrades all users.

Stored procedures can be an important component in database security. If all user
access goes through stored procedures, direct access to the tables can be denied and
all access to the data can be controlled.

Difference between outer join and inner join

Inner join:

We use this when we compare two colums from two different table .Based on equality or non equality, we retrieve the rows matched.
eg.

Select emp.empid , order.orderid
from emp Innerjoin order
on Emp.empid=order.empid

This example gives all the rows from emp,order tables where the empid's in both the tables are same.

Outer Join:

There are three types of outer joins namely:
Left Outer Join---For retreiving all the columns from the first table irrespective of the column match.
Right Outer Join---For retreiving all the columns from the second table irrespective of the column match
Full Outer Join---For retreiving all the columns from both the tables irrespective of column match.

Eg.

If we have two tables named stud1,stud2 with the following data

Stud1: id Name stud2: id Name
1 xxx 1 aaa
2 yyy 2 bbb
3 zzz 4 ccc
4 www 6 ddd
When we use Left Outer Join we get the output as:
1 aaa
2 bbb
3
4 ccc

When we use Right Outer Join we get the output as:
1 aaa
2 bbb
4 ccc
ddd

When we use Full Outer Join we get the output as:
1 aaa
2 bbb
3
4 ccc
ddd

Tuesday, February 1, 2011

Difference between @@IDENTITY, SCOPE_IDENTITY, IDENT_CURRENT

Introduction

In most of our application scenario we need to get
latest inserted row information through SQL query. And for that we have multiple
options like
All three functions return last-generated identity values.
However, the scope and session on which last is defined in each of
these functions differ.

Compare

@@IDENTITY :
It returns the last identity value generated for any table in
the current session, across all scopes.
Let I explain this, suppose we create an insert trigger on
table which insert a row in another table with generate an identity column then
@@IDENTITY returns that identity record which is created by
trigger.
SCOPE_IDENTITY :
It returns the last identity value generated for any table in
the current session and the current scope.
Let I explain this, suppose we create an insert trigger on
table which insert a row in another table with generate an identity column then
SCOPE_IDENTITY result is not affected but if a trigger or a user defined
function is effected on the same table that produced the value returns that
identity record then SCOPE_IDENTITY returns that identity record which is
created by trigger or a user defined function.
IDENT_CURRENT :
It returns the last identity value generated for a specific
table in any session and any scope.
In other words we can say it is not affected by scope and
session, it is only depend on a particular table and return that table related
identity value which is generated in any session or scope.

SQL Query

I am explaining the above process with help of some sample query, hope it helps-

CREATE TABLE Parent(id int IDENTITY);

CREATE TABLE Child(id int IDENTITY(100,1));

GO

CREATE TRIGGER Parentins ON Parent FOR INSERT

AS

BEGIN

   INSERT Child DEFAULT VALUES

END;

GO

--End of trigger definition

SELECT id FROM Parent;
--id is empty.

SELECT id FROM Child;
--ID is empty.
 

--Do the following in Session 1
INSERT Parent DEFAULT VALUES;
SELECT @@IDENTITY;
/*Returns the value 100. This was inserted by the trigger.*/

SELECT SCOPE_IDENTITY();
/* Returns the value 1. This was inserted by the

INSERT statement two statements before this query.*/

SELECT IDENT_CURRENT('Child');

/* Returns value inserted into Child, that is in the trigger.*/

SELECT IDENT_CURRENT('Parent');

/* Returns value inserted into Parent. This was the INSERT statement four statements before this query.*/

-- Do the following in Session 2.

SELECT @@IDENTITY;

/* Returns NULL because there has been no INSERT action

up to this point in this session.*/

SELECT SCOPE_IDENTITY();

/* Returns NULL because there has been no INSERT action

up to this point in this scope in this session.*/

SELECT IDENT_CURRENT('Child');

/* Returns the last value inserted into Child.*/

View post:

Built-in Functions - System Functions

System functions perform operations and return information about values, objects, and settings in an instance of SQL Server. The large majority of system functions are nondeterministic.

CASE Function

CASE is typically classified as a system function; however, it could also be considered as a statement. There are two general uses of the CASE function. The first one is used to replace occurrences of one value with other values, as specified by the programmer. Syntax for this flavor of CASE is as follows:

1.SELECT column_name = CASE WHEN column_name = 'a' THEN 'b' ELSE 'c' END

For instance, the following query attempts to specify the salary level for each job title within Adventure Works DW database:

SELECT DISTINCT TITLE,
                SALARYRANGE = CASE
                                WHEN TITLE LIKE 'Chief%' THEN 'unlimited'
                                WHEN TITLE LIKE '%manager%' THEN '100K to 250K'
                                WHEN TITLE LIKE '%assistant%' THEN '20K to 40K'
                                WHEN TITLE LIKE '%supervisor%' THEN '50K to 65K'
                                WHEN TITLE LIKE '%technician%' THEN '30K to 60K'
                                WHEN TITLE LIKE 'vice president%' THEN '250K to 500K'
                                ELSE 'unknown'
                              END
FROM   DIMEMPLOYEE

Results (abbreviated):

Title                                              SalaryRange  
-------------------------------------------------- ------------  
Accountant                                         unknown 
Accounts Manager                                   100K to 250K  
Assistant to the Chief Financial Officer           20K to 40K  
Buyer                                              unknown 
Chief Executive Officer                            unlimited  
Chief Financial Officer                            unlimited  
Document Control Assistant                         20K to 40K  
Document Control Manager                           100K to 250K  
Engineering Manager                                100K to 250K  

The other variation of CASE, which is sometimes referred to as the searched CASE, evaluates a Boolean expression and returns different values accordingly. For instance, we could use the searched CASE to categorize the top internet customers within Adventure Works DW database as follows:

1.SELECT CAST(FirstName + ', ' + LastName AS VARCHAR(35)) AS FullName, CustomerCategory = CASE WHEN SUM(SalesAmount) < 12000 THEN 'SILVER' WHEN SUM(SalesAmount) BETWEEN 12000 AND 13250 THEN 'GOLD' WHEN SUM(SalesAmount) BETWEEN 13250 AND 15000 THEN 'PLATINUM' ELSE 'CREAM OF THE CROP' END, SUM(SalesAMount) AS TotalOrders FROM dimCustomer a INNER JOIN FactInternetSales b ON a.CustomerKey = b.CustomerKey GROUP BY FirstName + ', ' + LastName HAVING SUM(SalesAmount) >=11000 ORDER BY 3 DESC

Results:

FullName                            CustomerCategory  TotalOrders  
----------------------------------- ----------------- ---------------------  
Jordan, Turner                      CREAM OF THE CROP 15999.0996  
Willie, Xu                          PLATINUM          13490.0596  
Nichole, Nara                       PLATINUM          13295.38  
Kaitlyn, Henderson                  PLATINUM          13294.27  
Margaret, He                        PLATINUM          13269.27  
Randall, Dominguez                  PLATINUM          13265.99  
Adriana, Gonzalez                   GOLD              13242.70  
Rosa, Hu                            GOLD              13215.65  
Brandi, Gill                        GOLD              13195.64  
Brad, She                           GOLD              13173.19  
Francisco, Sara                     GOLD              13164.64  
Maurice, Shan                       GOLD              12909.6682  
Janet, Munoz                        GOLD              12489.1696  
Lisa, Cai                           SILVER            11469.1882  
Franklin, Xu                        SILVER            11284.9707  
Lacey, Zheng                        SILVER            11248.4582  
Larry, Munoz                        SILVER            11068.0082

COALESCE Function

The COALESCE function returns the first value from a supplied list that is not NULL. COALESCE is a powerful tool if you are returning numerous values to a user and want to substitute occurrences of NULL with values from a different column or with an expression. The syntax is:

1.COALESCE(expression1, expression2, expressionN)

All expressions must have compatible data types. For instance, you can't coalesce DATETIME and INTEGER, but you could coalesce VARCHAR(20) and CHAR(20).
For example, the following query will examine the parent account key column for each account within DimAccount table; if the value is NULL the query will return "none" as the parent account:

1.SELECT a.AccountDescription, COALESCE(b.AccountDescription, 'None') AS ParentAccount FROM dimAccount a LEFT JOIN dimAccount b ON a.ParentAccountKey = b.AccountKey

Results (abbreviated):

AccountDescription                                 ParentAccount  
-------------------------------------------------- --------------------------------------  
Balance Sheet                                      None 
Assets                                             Balance Sheet 
Current Assets                                     Assets  
Cash                                               Current Assets  
Receivables                                        Current Assets  
Trade Receivables                                  Receivables  
Other Receivables                                  Receivables  
Net Income                                         None 
Operating Profit                                   Net Income  
Gross Margin                                       Operating Profit  
Net Sales                                          Gross Margin  
Gross Sales                                        Net Sales  
Intercompany Sales                                 Gross Sales  
Returns and Adjustments                            Net Sales  
Discounts                                          Net Sales  
Total Cost of Sales                                Gross Margin  
Statistical Accounts                               None 
Headcount                                          Statistical Accounts  
Current Installments of Long-term Debt             Current Liabilities  
Trade Sales                                        Gross Sales

COALESCE function is equivalent to the CASE function if it checks each expression for NULL values and returns the non-null expression, as in:

1.SELECT coalesce_equivalent = CASE WHEN (expression1 IS NOT NULL) THEN expression1 WHEN (expression2 IS NOT NULL) THEN expression2 ELSE NULL END

ISNULL Function

The ISNULL deterministic function is similar to COALESCE, but accepts only two parameters. The first parameter will be checked, and if NULL value is found, it will be replaced with the second parameter. Furthermore, ISNULL requires that both parameters have the same (not just compatible) data type. For example, we can return 'none' if the parent account of the given account is NULL:

1.SELECT a.AccountDescription, ISNULL(b.AccountDescription, 'None') AS ParentAccount FROM dimAccount a LEFT JOIN dimAccount b ON a.ParentAccountKey = b.AccountKey

Results would be identical to those results obtained with the COALESCE function.

NULLIF Function

The NULLIF deterministic function returns a NULL value if the two parameters it accepts are equivalent. NULLIF can be thought of as an opposite of ISNULL; for instance, we could substitute a NULL for number of employees if we find that number of employees for a particular reseller is 10:

1.SELECT NumberEmployees, NULLIF(NumberEmployees, 10) AS manipulated_number_of_employees FROM dimReseller WHERE NumberEmployees IN (10, 11)

Results (abbreviated):

NumberEmployees manipulated_number_of_employees  
--------------- -------------------------------  
10              NULL 
11              11  
11              11  
10              NULL 
10              NULL 
10              NULL 
11              11  
11              11  
10              NULL

GETANSINULL Function

The GETANSINULL function provides a quick way of checking whether column nullability is determined according to the ANSI 92 standard. The function returns 1 if ANSI null standard is used for column nullability, otherwise zero is returned. This function takes a single argument of database name. If database name isn't specified the setting is returned for the current database. For example:

1.SELECT GETANSINULL('AdventureWorksDW')

Results:

1.------

2.1

CAST and CONVERT Function

The CAST and CONVERT functions are very similar: Both translate a value from one data type to another. Although their performance is also similar, their syntax and potential usage is slightly different. The syntax is as follows:

1.CAST(expression AS new_data_type) CONVERT(new_data_type, expression, [style])

The expression must already have a data type that is translatable into the new_data_type. For instance, you can't convert an alphanumeric string into an integer.
Note: CONVERT has an optional parameter: style. This parameter is allowed only for cases when working with date and time values. SQL Server supports numerous formats for presenting date and time values; the style parameter is used to specify such format.
For example, suppose you want to retrieve dates from the dimTime table without returning the time portion. Either CAST or CONVERT function can be used as follows:

1.SELECT TOP 1 CAST(FullDateAlternateKey AS VARCHAR(12)) FROM DimTime

2.SELECT TOP 1 CONVERT(VARCHAR(12), FullDateAlternateKey, 109) FROM DimTime

Both return the same results:

1.------------

2.Jul 1 2001

If you needed to return a date value in which month, day, and year are separated by dashes you could use the CONVERT function with the style 110, as follows:

1.SELECT TOP 1 CONVERT(VARCHAR, FullDateAlternateKey, 110) FROM DimTime

Results:

1.------------------------------

2.07-01-2001

@@IDENTITY, IDENTITY, SCOPE_IDENTITY, IDENT_CURRENT(), and NEWID() Function

The @@IDENTITY, IDENTITY, SCOPE_IDENTITY, IDENT_CURRENT(), and NEWID() functions deal with IDENTITIY values or globally unique identifiers. SQL Server 2005 also supports the NEWSEQUENTIALID() function for default constraints of columns with the UNIQUEIDENTIFIER data type.
The NEWID() function could be used if you want to provide a default value for a column with the UNIQUEIDENTIFIER data type. This function can also be used to generate a new GUID in Transact-SQL. Columns with UNIQUEIDENTIFIER data type are often used as primary keys. Values generated by NEWID() function are impossible to predict and are not ordered, which can cause performance issues. For example, the following script creates a table and populates using NEWID() function for UNIQUEIDENTIFIER data type column. Note that resulting GUIDS are NOT sequential:

CREATE TABLE test (  test_column UNIQUEIDENTIFIER NOT NULL DEFAULT NEWID()  )   
INSERT test DEFAULT VALUES 
INSERT test DEFAULT VALUES 
INSERT test DEFAULT VALUES 
INSERT test DEFAULT VALUES   
SELECT * FROM test

Results:

test_column  
------------------------------------  
B7C4B585-7DDC-41FA-8AED-8EDD9F8BA483  
6E88893F-D913-43E0-9503-7A7BE0B9FAF2  
352E9644-07CC-4324-9FCE-AD61DAD4001A  
848F80F9-4E20-4969-A331-436E669819A8

Fortunately SQL Server 2005 supports the NEWSEQUENTIALID() function that generates a sequential GUIDS which tend to perform better than unordered GUID. The NEWSEQUENTIALID() function can only be used in a DEFAULT constraint expression. Since values generated by NEWSEQUENTIALID() are ordered one can predict the value that will be generated next; therefore this function shouldn't be exposed to the users if data security is of concern. For example, the following script creates a table and populates it with sequential values for UNIQUEIDENTIFIER data type column. Note that resulting GUIDS are sequential:

CREATE TABLE test (  test_column UNIQUEIDENTIFIER NOT NULL DEFAULT NEWSEQUENTIALID()  )   
INSERT test DEFAULT VALUES 
INSERT test DEFAULT VALUES 
INSERT test DEFAULT VALUES 
INSERT test DEFAULT VALUES   
SELECT * FROM test

Results:

test_column  
------------------------------------  
00700F04-4AB7-DA11-804B-006073E94311  
01700F04-4AB7-DA11-804B-006073E94311  
02700F04-4AB7-DA11-804B-006073E94311  
03700F04-4AB7-DA11-804B-006073E94311

The IDENTITY function has limited use; in rare cases, when you use SELECT INTO syntax you can supply identity values for the newly created table using the IDENTITY function. For instance, suppose we want to add an identity column to the sales table (within a temporary table). We could use the following statement to copy all rows from sales table into #new_sales and add an identity column, all in one shot:

1.SELECT IDENTITY(INT, 1,1) AS sales_key, * INTO #new_sales FROM sales

The other three IDENTITY-related functions deserve more attention. You will often need to populate multiple related tables, perhaps within a single transaction. For instance, you could be populating the order and order_details tables in one transaction. If the order table has an identity column, you'll have to look up the identity value just inserted into the order table before you can add a related record in order_details. The @@IDENTITY, SCOPE_IDENTITY(), and IDENT_CURRENT() functions help you look up the last identity value inserted, but their behavior is slightly different from each other, as follows:

@@IDENTITY returns the last IDENTITY value inserted on the current connection. Suppose that you have an INSERT trigger on the order table that populates the audit_trail table, which also has an IDENTITY column. In such a case, the @@IDENTITY function will return the last identity value inserted, which would be the identity inserted in the audit_trail table instead of the identity value added to the order table. Therefore, if you try populating order_details with a value returned by the @@IDENTITY function, your data integrity will be compromised.
The IDENT_CURRENT() function accepts a table name as the parameter and returns the last identity value generated in that table by any connection. If you were trying to populate order_details with the last identity value inserted into the order table, then IDENT_CURRENT('order') could work if you were the only user of the system; however, another user might have added a row to the order table a few milliseconds after you added the row to the same table. Therefore, using IDENT_CURRENT() in a multi-user system might also compromise your data integrity.
The SCOPE_IDENTITY() function takes no parameters and returns the last identity value inserted within the current scope. So if an INSERT statement populating the order table executes a trigger and adds a row to the audit_trail table, SCOPE_IDENTITY() will return the last value added to the order table, whereas @@IDENTITY will return the last value added to audit_trail.

ISDATE Function

The ISDATE function determines whether the parameter passed is of a date and time data type. This function returns a BIT value, as in the following example:

1.SELECT ISDATE('february 31, 2009') AS 'february 39, 2009', ISDATE('January 1, 2009') AS '1/1/2009'

Results:

february 39, 2009 1/1/2009  
----------------- -----------  
0                 1

ISNUMERIC Function

The ISNUMERIC deterministic function determines whether the parameter passed is of a numeric data type. This function returns a BIT value, as in the following example:

1.SELECT ISNUMERIC('abc') AS 'abc', ISNUMERIC('123.45') AS '123.45'

Results:

abc         123.45  
----------- -----------  
0           1

CURRENT_TIMESTAMP Function

The CURRENT_TIMESTAMP function works exactly the same way as GETDATE: It returns current date and time. For example:

1.SELECT CURRENT_TIMESTAMP

Results:

1.-----------------------

2.2006-03-19 16:24:57.827

DATALENGTH Function

The DATALENGTH deterministic function is similar to the LEN function, which returns the length of a particular string expression. DATALENGTH returns the number of bytes used to represent an expression of any data type. For example, the following query returns the data length for currency which is stored as NCHAR(3) data type:

1.SELECT TOP 1 DATALENGTH(CurrencyAlternateKey) , CurrencyALternateKey FROM dimCurrency

Results:

CurrencyALternateKey  
----------- --------------------  
6           AED

@@TRANCOUNT Function

The @@TRANCOUNT function returns the number of open transactions on a particular connection. You can check the value of @@TRANCOUNT to troubleshoot blocking issues. @@TRANCOUNT can also be used for error handling; if @@TRANCOUNT returns anything other than 0, something must have gone wrong and you have uncommitted transactions on the current connection. This function does not take any parameters.

XACT_STATE Function

The XACT_STATE function is new with SQL Server 2005. It is similar to @@TRANCOUNT since it determines whether there are any uncommitted transactions on the current connection. The XACT_STATE function does not accept any parameters. Unlike @@TRANCOUNT the XACT_STATE function can also determine if the uncommitted transaction has been classified as an uncomittable transaction. This function can return one of the following values:

1 The session has an active transaction. The transaction can be committed.
0 The session has NO active transactions.
(-1) The session has an active transaction; an error has occurred which classifies the active transaction as uncommittable. The transaction cannot be committed; neither can the session request rolling back to a savepoint. Rather the entire transaction must be rolled back. After the transaction has been rolled back the session can initiate a new transaction.

You can effectively use XACT_STATE function for error handling, as shown below:

1.IF (XACT_STATE()) = -1 BEGIN PRINT 'The transaction is in an uncommittable state. Rolling back transaction.' ROLLBACK TRANSACTION; END

@@ERROR Function

The @@ERROR function returns the number of the last error encountered on the current connection. If there are no errors, @@ERROR returns 0. The @@ERROR function is used for error handling. With SQL Server 2000 and previous releases checking @@ERROR was the only way to diagnose and troubleshoot errors. SQL Server 2005 introduces TRY / CATCH syntax for error handling as well as several new functions: ERROR_LINE, ERROR_NUMBER, ERROR_SEVERITY, ERROR_STATE, and ERROR_MESSAGE. Note that @@ERROR returns the error number returned by the last executed statement, so it's important to catch the error immediately after it occurs. For example, the following query catches the error because it checks for error immediately after the statement that encountered the error:

1.SELECT 1 / 0 SELECT 'error number is: ' + CAST(@@ERROR AS VARCHAR)

Results:

-----------  
Msg 8134, Level 16, State 1, Line 1  Divide by zero error encountered.      
-----------------------------------------------  
error number is: 8134

However, the next example does NOT catch the error because it checks the @@ERROR function value too late, after a statement that completes successfully:

SELECT 1 / 0  
SELECT 'this is a successful statement. it resets @@ERROR to zero!' 
SELECT 'error number is: ' + CAST(@@ERROR AS VARCHAR)

Results:

-----------  
Msg 8134, Level 16, State 1, Line 1  Divide by zero error encountered.      
-----------------------------------------------------------  
this is a successful statement. it resets @@ERROR to zero!     
-----------------------------------------------  
error number is: 0

@@ROWCOUNT Function

The @@ROWCOUNT function returns the number of rows affected by the last query. This function can be used effectively to find out whether the number of rows modified is the same as what you intended to modify. If your query was supposed to update 15 rows but @@ROWCOUNT returns 10 then something must have gone wrong. This function is often used for error handling. Much like with @@ERROR it's important to get @@ROWCOUNT value immediately after the statement you want to examine. For example, the following query erroneously reports that total number of affected rows is one, even though your main query returned 10 rows, as desired:

SELECT TOP 10 * FROM dimCustomer  
SELECT 'this is a successfull statement. it resets @@ROWCOUNT to one!' 
SELECT 'number of rows affected is: ' + CAST(@@ROWCOUNT AS VARCHAR)

The ROWCOUNT_BIG() function does the same thing as @@ROWCOUNT, but returns a BIGINT data type instead of an INT data type.
Note: It's easy to confuse the functionality of ROWCOUNT and @@ROWCOUNT. The former advises SQL Server to affect only a specified number of rows (similar to the TOP keyword); the latter simply counts the number of rows affected, as shown here:

/* first limit the output to 2 rows */ 
SET ROWCOUNT 2    
/* this query will affect only 2 rows */ 
SELECT EnglishProductSubcategoryName FROM DimProductSubCategory    
/* now use functions to count the number of  affected rows */ 
SELECT @@ROWCOUNT AS '@@rowcount_output',   ROWCOUNT_BIG() AS 'rowcount_big_output'

Results:

EnglishProductSubcategoryName  
--------------------------------------------------  
Mountain Bikes  Road Bikes    
@@rowcount_output rowcount_big_output  
----------------- --------------------  
2                 2

APP_NAME() Function

The APP_NAME() function returns a string with the name of the application that initiated the database connection. APP_NAME() can be helpful if you're troubleshooting a connection and want to know which app initiated the offending process. For example:

1.SELECT APP_NAME()

Results:

1.-------------------------------------------------

2.Microsoft SQL Server Management Studio - Query

UPDATE() Function

The UPDATE() function is only available within INSERT or UPDATE triggers and determines whether a value of a single column has been modified. You can use this function to execute certain logic within a trigger only if the value of a particular column has changed. The function accepts column name as the only parameter. For example, the following query rolls back the transaction within trigger if the value of AccountCodeAlternateKey column has been changed:

1.IF UPDATE(AccountCodeAlternateKey) BEGIN RAISERROR('key cannot be updated', 16, 1) ROLLBACK END

COLUMNS_UPDATED() Function

The COLUMNS_UPDATED() function works similarly to UPDATE() except it checks for multiple columns being updated by the same statement. Like UPDATE() function the COLUMNS_UPDATED() is only available within insert and update triggers. This function returns a bit pattern with VARBINARY data type showing which columns have been affected by the INSERT or UPDATE statement that invoked the trigger. The function does not accept any parameters. For example, the following query checks whether columns 2, 3 and 4 have been modified:

1.IF (COLUMNS_UPDATED() & 14) = 14 BEGIN SELECT 'columns 2, 3, and 4 have been changed' END

ERROR_LINE Function

The ERROR_LINE function returns the line number at which the error occurred which caused the CATCH block of TRY / CATCH logic to execute. This function can be very useful in determining the statement that caused the error and troubleshooting the code module (stored procedure) that encountered the error. The function does not accept any parameters. For example, the following query returns the line number where the error occurred:

BEGIN TRY
  SELECT 'empty string'
 
  SELECT 1 / 0
END TRY
 
BEGIN CATCH
  SELECT 'the error occurred at line ' + CAST(ERROR_LINE() AS VARCHAR)
END CATCH

Results:

------------  
empty string      
-----------      
--------------------------------------------------------  
the error occurred at line 3

ERROR_MESSAGE Function

The ERROR_MESSAGE function returns the text of the error which caused the CATCH block of TRY / CATCH logic to execute. This function can be very useful in determining the statement that caused the error and troubleshooting the code module (stored procedure) that encountered the error. The function does not accept any parameters. For example, the following query returns the error text:

BEGIN TRY  
SELECT 1 / 0  
END TRY    
BEGIN CATCH  
SELECT 'the error was: ' + ERROR_MESSAGE() 
END CATCH

Results:

-----------      
--------------------------------------------------  
the error was: Divide by zero error encountered.

ERROR_NUMBER Function

The ERROR_NUMBER function returns the number of the error which caused the CATCH block of TRY / CATCH logic to execute. This function can be very useful in determining the statement that caused the error and troubleshooting the code module (stored procedure) that encountered the error. The function does not accept any parameters. For example, the following query returns the error number:

BEGIN TRY  
SELECT 1 / 0  
END TRY    
BEGIN CATCH  
SELECT 'the error number was: ' + CAST(ERROR_NUMBER() AS VARCHAR) 
END CATCH

Results:

1.----------- --------------------------------------------------

2.the error number was: 8134

ERROR_PROCEDURE Function

The ERROR_PROCEDURE function returns the name of the stored procedure or trigger that encountered the error. This function does not accept any parameters and can be effectively called from CATCH block. For example, the following query creates a stored procedure that intentionally causes divide by zero error. Next the procedure is executed and the name of the erroneous stored procedure is returned:

CREATE PROCEDURE my_test_proc  AS   
SELECT 1 / 0  
GO   
BEGIN TRY  
EXEC my_test_proc  
END TRY    
BEGIN CATCH  
SELECT 'the erroneous procedure was: ' + ERROR_PROCEDURE() 
END CATCH

Results:

1.------------------------------------------

2.the erroneous procedure was: my_test_proc

ERROR_SEVERITY Function

The ERROR_SEVERITY function returns the severity of the error which caused the CATCH block of TRY / CATCH logic to execute. The function does not accept any parameters. For example, the following query returns the error severity:

BEGIN TRY  
SELECT 1 / 0  
END TRY    
BEGIN CATCH  
SELECT 'the error severity was: ' + CAST(ERROR_SEVERITY() AS VARCHAR) 
END CATCH

Results:

1.------------------------------------------------------

2.the error severity was: 16

ERROR_STATE Function

The ERROR_STATE function returns the state of the error which caused the CATCH block of TRY / CATCH logic to execute. The function does not accept any parameters. For example, the following query returns the error state:

BEGIN TRY  
SELECT 1 / 0  
END TRY    
BEGIN CATCH  
SELECT 'the error state was: ' + CAST(ERROR_STATE() AS VARCHAR) 
END CATCH

Results:

1.------------------------------------------------------

2.the error state was: 1

Additional System Functions Function

The following table provides a quick reference for what the rest of the system functions do:

System Function	Parameters	Description and Example
STATS_DATE	Table_id, index_id	Determining the last time statistics were updated. Example: view source print ? `1.SELECT` `TOP` `1` `STATS_DATE(object_id, index_id)` `FROM` `sys.indexes` Results: view source print ? `1.-----------------------` `2.2005-10-14 01:36:26.140`
COLLATIONPROPERTY	Collation_name, property	Determining the value of a certain collation property. Example: view source print ? `1.SELECT` `COLLATIONPROPERTY('SQL_Latin1_General_CP1_CI_AS',` `'codepage')` Results: view source print ? `1.----` `1252`
FN_HELPCOLLATIONS	None	Returns the list of supported collations. Example: view source print ? `1.SELECT` `*` `FROM` `FN_HELPCOLLATIONS()`
FN_SERVERSHAREDDRIVES	None	Returns the list of shared drives in clustered servers. Example: view source print ? `1.SELECT` `*` `FROM` `FN_SERVERSHAREDDRIVES()`
FN_VIRTUALFILESTATS	Database_id, file_id	Determining I/O stats for a certain database or log file. Example: view source print ? `1.SELECT` `*` `FROM` `FN_VIRTUALFILESTATS(10, 1)`
FORMATMESSAGE	Message number, parameter value	Constructing an error message using an existing message in sysmessages table. Example: view source print ? `1.sp_addmessage` `50002, 16,` `'terrible error on %s'` `2.GO` `3.DECLARE` `@var1` `VARCHAR(100)` `4.SELECT` `@var1` `=` `FORMATMESSAGE(50002,` `'Table1')` `5.SELECT` `@var1` Results: view source print ? `1.--------------------------` `terrible error` `on` `Table1`
SERVERPROPERTY	Property name	Returns a server property value. The following example returns the edition of the current instance: view source print ? `1.SELECT` `SERVERPROPERTY('edition')` Results: view source print ? `1.-----------------` `Developer Edition`
SESSIONPROPERTY	Option	Getting a session value for various session options configured using SET statements. The following query returns ANSI_NULLS setting: view source print ? `1.SELECT` `SESSIONPROPERTY('ANSI_NULLS')` Results: view source print ? `1.--` `1`
PARSENAME	Object_name, object_piece	Returning server, owner, database or name portion of the specified object. Object piece can take the following values: 1 - object name 2 - schema name 3 - database name 4 - server name Example: view source print ? `1.SELECT` `PARSENAME('dbo.DimAccount', 2)` Results: view source print ? `1.-----` `Dbo`

Pages

Thursday, August 4, 2011

Thursday, July 21, 2011

Tuesday, July 12, 2011

Tuesday, June 21, 2011

SQL Server Database Coding Standards and Guidelines – Part 2

SQL Server Database Coding Standards and Guidelines – Part 1

Wednesday, February 2, 2011

Tuesday, February 1, 2011

Introduction

Compare

SQL Query

Contents

CASE Function

COALESCE Function

ISNULL Function

NULLIF Function

GETANSINULL Function

CAST and CONVERT Function

@@IDENTITY, IDENTITY, SCOPE_IDENTITY, IDENT_CURRENT(), and NEWID() Function

ISDATE Function

ISNUMERIC Function

CURRENT_TIMESTAMP Function

DATALENGTH Function

@@TRANCOUNT Function

XACT_STATE Function

@@ERROR Function

@@ROWCOUNT Function

APP_NAME() Function

UPDATE() Function

COLUMNS_UPDATED() Function

ERROR_LINE Function

ERROR_MESSAGE Function

ERROR_NUMBER Function

ERROR_PROCEDURE Function

ERROR_SEVERITY Function

ERROR_STATE Function

Additional System Functions Function

How this blog is usefull?

Search This Blog

Feed

About Me

Labels