Format money/decimal with commas

SQL format money/decimal with commas:
Sometimes you want to have your money fields properly formatted with commas like this: 13,243,543.57
You can use the CONVERT function and give a value between 0 and 2 to the style and the format will be displayed based on that
Below is an example:
DECLARE @v MONEY
SELECT @v = 1322323.6666

SELECT CONVERT(VARCHAR,@v,0)  –1322323.67
Rounded [...]

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 [...]

HOW TO USE ROLLUP and CUBE

HOW TO USE CUBE:
The CUBE operator generates a result set that is a multidimensional cube. A multidimensional cube is an expansion of fact data, or data that records individual events. The expansion is based on columns that the user wants to analyze. These columns are called dimensions. The cube is a result set containing [...]

SQL running serial generating tips

SQL running serial generating tips:
Traditionally developers and Database administrators used temporary tables and co-related sub-queries to generate calculated row numbers in a query. Now SQL Server 2005 provides a function, which replaces all of the additional resources we used to generate row numbers.
Returns the sequential number of a row within a partition of a result [...]

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 [...]

Case sensitive comparisons in SQL

Case sensitive comparisons in SQL Server
Some time we need to compare some data in binary see the examples:
SELECT *  FROM TABLE1 WITH (NOLOCK) WHERE convert(varbinary(255), PASS) = convert(varbinary(255), ‘RAGHU’)
Normal comparison:
SELECT *  FROM TABLE1 WITH (NOLOCK) WHERE convert(varbinary(255), PASS) = convert(varbinary(255), ‘Raghu’) AND PASS= ‘TEST’
Uuse the BINARY_CHECKSUM function:

SELECT *  FROM TABLE1 WITH (NOLOCK) WHERE BINARY_CHECKSUM(answer) = [...]

Sub Query Update

Sub Query Update:
if you want to run update query in join or by comparing other table then here is the example.
UPDATE Table1
SET Price = Price * 2
WHERE ProductID IN
(SELECT ID
FROM Table2
WHERE ID= 51);
GO
Here is an equivalent UPDATE statement using a join:
USE [...]

BEGIN TRANSACTION (Transact-SQL)

BEGIN TRANSACTION represents a point at which the data referenced by a connection is logically and physically consistent. If errors are encountered, all data modifications made after the BEGIN TRANSACTION can be rolled back to return the data to this known state of consistency. Each transaction lasts until either it completes without errors and COMMIT [...]

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 [...]

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 [...]

What is BCP Utility in SQL server?

Introduction BCP Utility:
The bcp utility bulk copies data between an instance of Microsoft SQL Server 2005 and a data file in a user-specified format. The bcp utility can be used to import large numbers of new rows into SQL Server tables or to export data out of tables into [...]

Tips And Tricks For Advanced MS SQL Server Developers

Tips And Tricks For Advanced MS SQL Server Developers:

Use “TRUNCATE TABLE” statement instead of “DELETE” clause if you want to delete all rows from a table. It is much faster then “DELETE” statement without any conditions. “TRUNCATE TABLE” frees all the space occupied by that table’s data and indexes, without logging [...]

Database Settings Optimization Tips

Database Settings Optimization Tips

You can turn off the ‘auto create statistics’ database option.When this database option is set to true, statistics are automatically created on columns used in a predicate. By default, this database option is set to true. Because auto creation statistics results in some performance degradation, you can turn off this database option [...]

SQL Server Settings Optimization Tips

SQL Server Settings Optimization Tips:

You can increase the ‘min memory per query’ option to improve the performance of queries that use hashing or sorting operations, if your SQL Server has a lot of memory available and there are many queries running concurrently on the server.
The SQL Server will automatically allocate, at a minimum, the [...]

Basic Definitions

Definition of a Database
A database is a collection of related information, accessed and managed by its DBMS. After experimenting with hierarchical and networked DBMSs during the 1970’s, the IT industry became dominated by relational DBMSs (Or Object-Relational Database Management System) such as Informix database, Oracle, Sybase, and, later on, Microsoft SQL Server and the like.
In [...]

Indexing Service

What is Indexing Service?
Indexing Service is a base service for Microsoft® Windows® 2000 or later that extracts content from files and constructs an indexed catalog to facilitate efficient and rapid searching.
Indexing Service can extract both text and property information from files on the local host and on remote, networked hosts. The files can be simply [...]

SQL Performance Tuning using Indexes

SQL Performance Tuning using Indexes
This article looks at general guidelines to creating effective indexes using short keys, distinct keys, covering indexes and clustered indexes.

Effective indexes are one of the best ways to improve performance in a database application. Without an index, the SQL Server engine is like a reader trying to find a word in [...]

CREATE PARTITION FUNCTION

CREATE PARTITION FUNCTION (Transact-SQL)

Creates a function in the current database that maps the rows of a table or index into partitions based on the values of a specified column. Using CREATE PARTITION FUNCTION is the first step in creating a partitioned table or index.

Syntax

CREATE PARTITION FUNCTION partition_function_name ( input_parameter_type )
AS RANGE [ LEFT | RIGHT [...]

Partitioning the Data in a Table

Is it ever good database design practice (for speed sake, etc.) to essentially make copies of tables to hold a certain group of data?
For example, I have come across a database table that stores information for a housing subdivision; ie. lot number, lot size, lot price, etc. And the database to which this table belongs [...]

Backup/Restore Optimization Tips

Backup/Restore Optimization Tips:

Try to perform backup to the local hard disk first, and copy backup file(s) to the tape later.
When you perform backup, some SQL Server commands cannot be made, for example: during backup you cannot run ALTER DATABASE statement with either the ADD FILE or REMOVE FILE options, you cannot shrink database, you cannot [...]