Technical Articles on Microsoft SQL Server

What is a Stored Procedure in SQL Server?
The name for a batch of Transact-SQL or CLR code that is stored within SQL Server and can be called directly by applications or within other programming constructs.
Stored Procedure Syntax
A stored procedure is a set of one or more SQL statements that are stored together in database. To create a stored procedure use CREATE PROCEDURE statement. To use the stored procedure you send a request for it to be executed. When server recieves the request, it executes the stored procedure.
Clauses in SQL Server
SQL Server provides with the following clauses that can be used in the SELECT statements: WHERE, GROUP BY, HAVING, ORDER BY.
Constraints in SQL Server
A constraint is a property assigned to a column or the set of columns in a table that prevents certain types of inconsistent data values from being placed in the column(s).
IDENTITY property in SQL Server
The IDENTITY property makes generating unique values easy. IDENTITY isn't a datatype. It is a column property
Pre-Defined Datatypes in SQL Server
In a Database, each column, local variable, expression, and parameter has a related data type. A data type is an attribute that specifies the type of data that the object can hold: integer data, character data, monetary data, data and time data, binary strings, and so on.
User-Defined Data Types in SQL Server
In a Database, each column, local variable, expression, and parameter has a related data type. A data type is an attribute that specifies the type of data that the object can hold: integer data, character data, monetary data, data and time data, binary strings, and so on.
User Defined Functions
User Defined Functions are compact pieces of Transact SQL code, which can accept parameters, and return either a value, or a table. Data transformation and reference value retrieval are common uses for functions.
Default databases in SQL Server
SQL Server consists of six databases by default.
How to create View in SQL Server?
In this article I am going to describe what is view and how to create views in SQL Server 2005 database. A view is a virtual table that consists of columns from one or more tables.
Triggers
A trigger is also similar to a procedure but, which doesn't require to be called. It will be fired when we perform any DML Operation on the table on which the trigger is dependent.
Guidelines to use INDEX in SQL Server
Choosing the correct columns and types for an index is an important step in creating an effective index. In this article, we will talk about two main points, namely short index keys and selective indexes.
Advantages & drawbacks of INDEX
The database engine can use indexes to boost performance in a number of different queries. Sometimes these performance improvements are dramatic. An important feature of SQL Server 2000 is a component known as the query optimizer.
How an INDEX Works ?
The SQL Server engine uses an index in much the same way a reader uses a book index. For example, one way to find all references to CHERUKURI statements in a SQL book would be to begin on page one and scan each page of the book.
@@ Keywords in SQL Server
SQL Server @@ Keywords (Transact-SQL)
Logical Operators in SQL Server
Logical operators test for the truth of some condition. Logical operators, like comparison operators, return a Boolean data type with a value of TRUE, FALSE, or UNKNOWN.
What are Magic Tables in SQL Server?
Whenever a trigger fires in response to the INSERT,DELETE,or UPDATE statement,two special tables are created.These are the inserted and the deleted tables.They are also referred to as the magic tables.
How can you increase SQL performance?
Every index increases the time takes to perform INSERTS, UPDATES, and DELETES, so the number of indexes should not be too much. Try to use maximum 4-5 indexes on one table, not more.
How to Write Stored Procedures effectively
At processing time, T-SQL works well with a set. Therefore, avoid cursors wherever possible; even if it means using two or three steps or maybe even a temporary table.
What are Ranking Functions in SQL Server
SQL Server 2005 provides you easy and efficient ways for different types of ranking requirements. There are four ranking functions in SQL Server 2005: RANK, DENSE_RANK, ROW_NUMBER, NTILE.
How to Find nth Record in a table?
There may be ways to slove this problem, and it found easy in this soluntion.
How to configure SQL Server 2005 to allow remote connections
Sqlcmd: Error: Microsoft SQL Native Client: 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.
CURSORS in SQL Server
We can’t move through each individual record one by one in this situation inside a stored procedure. Therefore, whenever we find some situation where we need to process each record individually we can use cursors.
Add leading zeros to number
Formatting numbers to add leading zeros can be done in SQL Server. It is just simple. Lets create a new table and see how it works
Differences between SQL Server temporary tables and table variables
There are three major theoretical differences between temporary tables And table variables
Retrieving Dynamic XML from SQL Server using FOR XML
To allow the retrieval of data in the XML format from SQL Server, the FOR XML command has been added to the T-SQL syntax. Using the FOR XML command with a sequel query allows the results to be generated as XML.
SQL Server DateTime Formatting
This articles describes the most popular style codes that are available for use when converting between a DateTime and a character representation. Each example uses date, 16 November 1982.
How to read data from XML string
This article describes how to read data from xml string to sql table.
How to save images in SQL Server from backend
OPENROWSET supports bulk operations through a built-in BULK provider that enables data from a file to be read and returned as a rowset.
What is SQL Injection
SQL injection is yet another common vulnerability that is the result of lax input validation. SQL injection is an attack on the site itself—in particular its database.
Database Mail in SQL Server
Configuration component has two sub components. One is the Database Mail account, which contains information such as the SMTP server login, Email account, Login and password for SMTP mail.
Performance Tuning
You can improve your SQL Server application performance by optimizing the queries you use. The following sections outline techniques you can use to optimize query performance.
Logical Design and Physical Design of Database
Logical design, Defining business entities, attributes for each entity, and relationships among entities.
How To Delete Duplicate Records in SQL Server
How to find and delete Duplicate Records from Table in SQL Server
New Data Types in SQL Server 2008
This articles briefs about the New Data Types introduced in SQL Server 2008.
SQL Server Object Types in sys.objects
SQL Server Object Types in sys.objects
Split Function in SQL Server
Split Function is a user defined table valued function which accepts two parameters, one is string to split and other one is delimiter to split the first string.
LOB Data Types FILESTREAM Data Type
The FILESTREAM data type combines the performance of accessing LOBs directly from the NTFS file system with the referential integrity and direct access through the SQL Server relational database engine. It can be used for both binary and text data, and it supports files up to the size of the disk volume.
STUFF AND FOR XML PATH for String Concatenation
We can use XmlPath to concatenate column data into single row. Stuff is used to remove the first character after string concatenation.