NULLIF (Transact-SQL)

How to use NULLIF (Transact-SQL)

NULLIF will Return a null value if the two specified expressions are equal. If you want to compare to fields and then this will work for you like :

Run these examples and you can see how to use it on your way.
Select NULLIF('aa','aa')
Select isnull( NULLIF('aa','aa'),'right')

MSDN is comparing NULLIF and CASE

To show the similarity between NULLIF and CASE, the following queries evaluate whether the values in the MakeFlag and FinishedGoodsFlag columns are the same. The first query uses NULLIF. The second query uses the CASE expression.


USE AdventureWorks;
GO
SELECT ProductID, MakeFlag, FinishedGoodsFlag,
NULLIF(MakeFlag,FinishedGoodsFlag)AS 'Null if Equal'
FROM Production.Product
WHERE ProductID < 10;
GO

SELECT ProductID, MakeFlag, FinishedGoodsFlag,'Null if Equal' =
CASE
WHEN MakeFlag = FinishedGoodsFlag THEN NULL
ELSE MakeFlag
END
FROM Production.Product
WHERE ProductID < 10;
GO

So NULLIF and ISNULL functions can help you on different way.

Advertisements

Difference with (nolock) vs (nolock) as a SQL Table Hint

Difference with (nolock) vs (nolock) as a SQL Table Hint:

select sum(amount) from reward_expired (nolock)

difference was between using the table hint (nolock) and the table hint with (nolock).
Look at MSDN under SQL 2005,

“In SQL Server 2005, with some exceptions, table hints are supported in the FROM clause only when the hints are specified with the WITH keyword. Table hints also must be specified with parentheses.

The following table hints are allowed with and without the WITH keyword: NOLOCK, READUNCOMMITTED, UPDLOCK, REPEATABLEREAD, SERIALIZABLE, READCOMMITTED, FASTFIRSTROW, TABLOCK, TABLOCKX, PAGLOCK, ROWLOCK, NOWAIT, READPAST, XLOCK, and NOEXPAND. When these table hints are specified without the WITH keyword, the hints should be specified alone.”

Micorosft plan to remove the ability to use just (nolock), and to future proof all SQL, users should write all table hints using the with keyword.

Omitting the WITH keyword is a deprecated feature: This feature will be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature.

Table Hints (Transact-SQL)

Table Hints (Transact-SQL)

Table hints override the default behavior of the query optimizer for the duration of the data manipulation language (DML) statement by specifying a locking method, one or more indexes, a query processing operation such as a table scan or index seek, or other options.

WITH ( <table_hint> [ [ , ]...n ] )

<table_hint> ::=
[ NOEXPAND ] {
    INDEX ( index_value [ ,...n ] ) | INDEX = ( index_value )
  | FASTFIRSTROW
  | FORCESEEK
  | HOLDLOCK
  | NOLOCK
  | NOWAIT
  | PAGLOCK
  | READCOMMITTED
  | READCOMMITTEDLOCK
  | READPAST
  | READUNCOMMITTED
  | REPEATABLEREAD
  | ROWLOCK
  | SERIALIZABLE
  | TABLOCK
  | TABLOCKX
  | UPDLOCK
  | XLOCK
} 

<table_hint_limited> ::=
{
    KEEPIDENTITY
  | KEEPDEFAULTS
  | FASTFIRSTROW
  | HOLDLOCK
  | IGNORE_CONSTRAINTS
  | IGNORE_TRIGGERS
  | NOWAIT
  | PAGLOCK
  | READCOMMITTED
  | READCOMMITTEDLOCK
  | READPAST
  | REPEATABLEREAD
  | ROWLOCK
  | SERIALIZABLE
  | TABLOCK
  | TABLOCKX
  | UPDLOCK
  | XLOCK
}

The following table hints are allowed with and without the WITH
keyword: NOLOCK, READUNCOMMITTED, UPDLOCK, REPEATABLEREAD,
SERIALIZABLE, READCOMMITTED, FASTFIRSTROW, TABLOCK, TABLOCKX, PAGLOCK,
ROWLOCK, NOWAIT, READPAST, XLOCK, and NOEXPAND. When these table hints
are specified without the WITH keyword, the hints should be specified
alone.

For example:

Select * FROM table1 (TABLOCK)

Select * FROM table1 WITH (TABLOCK, INDEX(myindex))

Reducing SQL Server Deadlocks

Deadlocking and SQL best practices:

Deadlocking refers to the condition in which one resource is waiting on the action of a second, while that second action is waiting on the first. This is different from being blocked, or having to wait for a resource. Using the locks above, if a transaction had a shared lock, then you issued a delete on those same records held by the first lock, you would not be deadlocked. Instead, you would be blocked. When the shared lock was released, your delete statement would complete. Blocking implies some performance hit, but the transaction will complete. It simply has to wait for something else to finish first. A deadlock on the other hand, means there is no way to finish. Your transaction is stuck in a loop with some other transaction. At this point, the database system will usually pick one transaction to be killed so the other can complete.

Here are some tips on how to avoid deadlocking on your SQL Server:

  • Esure the database design is properly normalized.
  • Have the application access server objects in the same order each time.
  • During transactions, don’t allow any user input. Collect it before the transaction begins.
  • Avoid cursors.
  • Keep transactions as short as possible. One way to help accomplish this is to reduce the number of round trips between your application and SQL Server by using stored procedures or keeping transactions with a single batch. Another way of reducing the time a transaction takes to complete is to make sure you are not performing the same reads over and over again. If your application does need to read the same data more than once, cache it by storing it in a variable or an array, and then re-reading it from there, not from SQL Server.
  • Reduce lock time. Try to develop your application so that it grabs locks at the latest possible time, and then releases them at the very earliest time.
  • If appropriate, reduce lock escalation by using the ROWLOCK or PAGLOCK.
  • Consider using the NOLOCK hint to prevent locking if the data being locked is not modified often.
  • If appropriate, use as low of an isolation level as possible for the user connection running the transaction.
  • Consider using bound connections
  • Remember to SET NOCOUNT ON at the beginning of your SQL bataches, stored procedures, triggers to avoid network traffic. This will also reduct the chances of error on linked server.
  • Index should be created on highly selective columns, which are used in JOINS, WHERE and ORDER BY clause.
  • Use Column name in ORDER BY clause instead of numbers.
  • Do not use TEXT or NTEXT if possible. In SQL Server 2005 use VARCHAR(MAX) or NVARCHAR(MAX).
  • Do not use SELECT *, use proper column names to decrease network traffic and fewer locks on table.

Use SQL Server Profiler to identify the cause of a deadlock. A deadlock occurs when there is a cyclic dependency between two or more threads, or processes, for some set of resources within SQL Server. Using SQL Server Profiler, you can create a trace that records, replays, and displays deadlock events for analysis.

To trace deadlock events, add the Deadlock graph event class to a trace. This event class populates the TextData data column in the trace with XML data about the process and objects that are involved in the deadlock. SQL Server Profiler can extract the XML document to a deadlock XML (.xdl) file which you can view later in SQL Server Management Studio. You can configure SQL Server Profiler to extract Deadlock graph events to a single file that contains all Deadlock graph events, or to separate files. This extraction can be done in any of the following ways:

  • At trace configuration time, using the Events Extraction Settings tab. Note that this tab does not appear until you select the Deadlock graph event on the Events Selection tab.
  • Using the Extract SQL Server Events option on the File menu.
  • Individual events can also be extracted and saved by right-clicking a specific event and choosing Extract Event Data.

Enable remote connection SQL Server

Error Description ::

An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: Named Pipes Provider, error: 40 – Could not open a connection to SQL Server)

Enable remote connections for SQL Server 2005 Express or SQL Server 2005 Developer Edition

You must enable remote connections for each instance of SQL Server 2005 that you want to connect to from a remote computer. To do this, follow these steps:

  1. Click Start, point to Programs, point to Microsoft SQL Server 2005, point to Configuration Tools, and then click SQL Server Surface Area Configuration.
  2. On the SQL Server 2005 Surface Area Configuration page, click Surface Area Configuration for Services and Connections.
  3. On the Surface Area Configuration for Services and Connections page, expand Database Engine, click Remote Connections, click Local and remote connections, click the appropriate protocol to enable for your environment, and then click Apply.Note Click OK when you receive the following message:
    Changes to Connection Settings will not take effect until you restart the Database Engine service.
  4. On the Surface Area Configuration for Services and Connections page, expand Database Engine, click Service, click Stop, wait until the MSSQLSERVER service stops, and then click Start to restart the MSSQLSERVER service.

Enable the SQL Server Browser service:

  1. Click Start, point to Programs, point to Microsoft SQL Server 2005, point to Configuration Tools, and then click SQL Server Surface Area Configuration.
  2. On the SQL Server 2005 Surface Area Configuration page, click Surface Area Configuration for Services and Connections.
  3. On the Surface Area Configuration for Services and Connections page, click SQL Server Browser, click Automatic for Startup type, and then click Apply.Note When you click the Automatic option, the SQL Server Browser service starts automatically every time that you start Microsoft Windows.
  4. Click Start, and then click OK.

Create exceptions in Windows Firewall:

  1. Click Start, point to Programs, point to Microsoft SQL Server 2005, point to Configuration Tools, and then click SQL Server Configuration Manager.
  2. In SQL Server Configuration Manager, click the SQL Server Browser service in the right pane, right-click the instance name in the main window, and then click Properties.
  3. On the SQL Server Browser Properties page, click the Advanced tab, locate the instance ID in the property list, and then click OK.

To open Windows Firewall, click Start, click Run, type firewall.cpl, and then click OK

Create an exception for SQL Server 2005 in Windows Firewall

To create an exception for SQL Server 2005 in Windows Firewall, follow these steps:

  1. In Windows Firewall, click the Exceptions tab, and then click Add Program.
  2. In the Add a Program window, click Browse.
  3. Click the C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLBinnsqlservr.exe executable program, click Open, and then click OK.Note The path may be different depending on where SQL Server 2005 is installed. MSSQL.1 is a placeholder for the instance ID that you obtained in step 3 of the previous procedure.
  4. Repeat steps 1 through 3 for each instance of SQL Server 2005 that needs an exception.

Create an exception for the SQL Server Browser service in Windows Firewall

To create an exception for the SQL Server Browser service in Windows Firewall, follow these steps:

  1. In Windows Firewall, click the Exceptions tab, and then click Add Program.
  2. In the Add a Program window, click Browse.
  3. Click the C:Program FilesMicrosoft SQL Server90Sharedsqlbrowser.exe executable program, click Open, and then click OK.

SQL RAND Function

SQL RAND Function

The SQL RAND() function is used to generate some random numbers at run time. Here is the syntax:

Syntax

RAND ( [ seed ] )

Repetitive calls of RAND() with the same seed value return the same results.

For one connection, if RAND() is called with a specified seed value, all subsequent calls of RAND() produce results based on the seeded RAND() call. For example, the following query will always return the same sequence of numbers.

SELECT RAND(100), RAND(), RAND()

Examples:

The following example produces four different random numbers that are generated by the RAND function.

DECLARE @counter smallint
SET @counter = 1
WHILE @counter < 5
BEGIN
SELECT RAND() Random_Number
SET @counter = @counter + 1
END
GO

The following example returns random float numbers based on three different seed values.

CREATE TABLE Random (Seed1 float, Seed5 float, Seed10 float)
INSERT INTO Random Values (RAND(1), RAND(5), RAND(10))
SELECT * FROM Random

The RAND function is a pseudorandom number generator that operates in a manner similar to the C run-time library rand function. If no seed is provided, the system generates its own variable seed numbers. If you call RAND with a seed value, you must use variable seed values to generate random numbers. If you call RAND multiple times with the same seed value, it returns the same generated value. The following script returns the same value for the calls to RAND because they all use the same seed value:

SELECT RAND(159784)
SELECT RAND(159784)
SELECT RAND(159784)

A common way to generate random numbers from RAND is to include something relatively variable as the seed value, such as adding several parts of a GETDATE:

SELECT RAND( (DATEPART(mm, GETDATE()) * 100000 )
+ (DATEPART(ss, GETDATE()) * 1000 )
+ DATEPART(ms, GETDATE()) )

When you use an algorithm based on GETDATE to generate seed values, RAND can still generate duplicate values if the calls to RAND are made within the interval of the smallest datepart used in the algorithm. This is especially likely when the calls to RAND are included in a single batch. Multiple calls to RAND in a single batch can be executed within the same millisecond. This is the smallest increment of DATEPART. In this case, incorporate a value based on something other than time to generate the seed values.

How to use Bulk Insert

Introduction Bulk Insert:

The Bulk Insert task provides the quickest way to copy large amounts of data into a SQL Server table or view. For example, suppose your company stores its million-row product list on a mainframe system, but the company’s e-commerce system uses SQL Server 2005 to populate Web pages. You must update the SQL Server product table nightly with the master product list from the mainframe. To update the table, you save the product list in a tab-delimited format and use the Bulk Insert task to copy the data directly into the SQL Server table.

To ensure high-speed data copying, transformations cannot be performed on the data while it is moving from the source file to the table or view.

You can configure the Bulk Insert task in the following ways:

  • Specify the OLE DB connection manager to connect to the destination SQL Server database and the table or view into which data is inserted.
  • Specify the File or Flat File connection manager to access the source file and provide information about the source data file, such as the code page and file type.
  • Define the format used by the Bulk Insert task, either by using a format file or by defining the column and row delimiters of the source data. If using a format file, specify the File connection manager to access the format file.
  • Specify actions to perform on the destination table or view when inserting the data. The options include whether to check constraints, enable identity inserts, keep nulls, fire triggers, or lock the table.
  • Provide information about the batch of data to insert, such as the batch size, the first and last row from the file to insert, the number of insert errors that can occur before the task stops inserting rows, and the names of the columns that will be sorted.

If the Bulk Insert task uses a Flat File connection manager to access the source file, the task does not use the format specified in the Flat File connection manager. Instead, the Bulk Insert task uses either the format specified in a format file, or the values of the RowDelimiter and ColumnDelimiter properties of the task.

BULK INSERT (Transact-SQL)

BULK INSERT
   [ database_name . [ schema_name ] . | schema_name . ] [ table_name | view_name ]
      FROM 'data_file'
     [ WITH
    (
   [ [ , ] BATCHSIZE = batch_size ]
   [ [ , ] CHECK_CONSTRAINTS ]
   [ [ , ] CODEPAGE = { 'ACP' | 'OEM' | 'RAW' | 'code_page' } ]
   [ [ , ] DATAFILETYPE  =
      { 'char' | 'native'| 'widechar' | 'widenative' } ]
   [ [ , ] FIELDTERMINATOR = 'field_terminator' ]
   [ [ , ] FIRSTROW  =first_row ]
   [ [ , ] FIRE_TRIGGERS ]
   [ [ , ] FORMATFILE = 'format_file_path' ]
   [ [ , ] KEEPIDENTITY ]
   [ [ , ] KEEPNULLS ]
   [ [ , ] KILOBYTES_PER_BATCH =kilobytes_per_batch ]
   [ [ , ] LASTROW = last_row ]
   [ [ , ] MAXERRORS = max_errors ]
   [ [ , ] ORDER ( { column [ ASC | DESC ] } [ ,...n ] ) ]
   [ [ , ] ROWS_PER_BATCH = rows_per_batch ]
   [ [ , ] ROWTERMINATOR = 'row_terminator' ]
   [ [ , ] TABLOCK ]
   [ [ , ] ERRORFILE = 'file_name' ]
    )]

Examples

A. Using pipes to import data from a file:

This example imports order detail information into the AdventureWorks.Sales.SalesOrderDetail table from the specified data file by using a pipe (|) as the field terminator and |n as the row terminator.

BULK INSERT AdventureWorks.Sales.SalesOrderDetail     
FROM 'f:orderslineitem.txt'     
WITH         
(           
FIELDTERMINATOR =' |',           
ROWTERMINATOR =' |n'        
)

B. Using the FIRE_TRIGGERS argument

This example specifies the FIRE_TRIGGERS argument.

BULK INSERT AdventureWorks.Sales.SalesOrderDetail     
FROM 'f:orderslineitem.txt'     
WITH       
(          
FIELDTERMINATOR =' |',          
ROWTERMINATOR = ':n',          
FIRE_TRIGGERS        )

C. Using line feed as a row terminator

This example loads a file that uses the line feed as a row terminator such as a UNIX output:

DECLARE @bulk_cmd varchar(1000)  
SET @bulk_cmd = 'BULK INSERT AdventureWorks.Sales.SalesOrderDetail  
FROM ''<drive>:<path><filename>''   
WITH (ROWTERMINATOR = '''+CHAR(10)+''')'  
EXEC(@bulk_cmd)