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

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

Analyze and Fix Index Fragmentation in SQL Server 2008

Analyze and Fix Index Fragmentation in SQL Server 2008
It is very common that over time SQL Server tables and indexes tend to become fragmented. The fragmentation generally happens when data within the underlying tables on which an index exists is modified. The data modification basically can be an insert, update or a delete operation. The [...]

How to Cluster SQL Server 2005

How to Cluster SQL Server 2005
Believe it or not, the procedure to install a SQL Server 2005 instance onto a cluster is one of the easiest parts of getting your SQL Server 2005 cluster up and running. The SQL Server 2005 setup program is used for the install and does the hard work for you. [...]

SQL Tuning or SQL Optimization

SQL Tuning or SQL Optimization
Sql Statements are used to retrieve data from the database. We can get same results by writing different sql queries. But use of the best query is important when performance is considered. So you need to sql query tuning based on the requirement. Here is the list of queries which we [...]

SQL Subquery

SQL Subquery
Subquery or Inner query or Nested query is a query in a query. A subquery is usually added in the WHERE Clause of the sql statement. Most of the time, a subquery is used when you know how to search for a value using a SELECT statement, but do not know the exact value.
Subqueries [...]

Using Stored Procedures

Most of us, the database programmers, have used Stored Procedures. May be not all of us knows about why we use them. This article is for those who have used/never used stored procedures, and are yet to understand why everyone suggests using them in your Database.
Stored Procedures – What are they?
Stored procedure is a [...]

SQL Server Date Formats CAST and CONVERT

Date Formats CAST and CONVERT (Transact-SQL)
Converts an expression of one data type to another.
Topic link icon Transact-SQL Syntax Conventions
Syntax for CAST:
CAST ( expression AS data_type [ (length ) ])
Syntax for CONVERT:
CONVERT ( data_type [ ( length ) ] , expression [ , style ] )
SQL Server Date Formats

Default

SELECT CONVERT(VARCHAR(20), GETDATE(), 100)

Jan 1 2005 1:29PM 1

USA

SELECT [...]

SQL Statement OR Syntax

SQL Quick Reference:

SQL Statement
Syntax

AND / OR
SELECT column_name(s)
FROM table_name
WHERE condition
AND|OR condition

ALTER TABLE
ALTER TABLE table_name
ADD column_name datatypeor
ALTER TABLE table_name
DROP COLUMN column_name

AS (alias)
SELECT column_name AS column_alias
FROM table_nameor
SELECT column_name
FROM table_name  AS table_alias

BETWEEN
SELECT column_name(s)
FROM table_name
WHERE column_name
BETWEEN value1 AND value2

CREATE DATABASE
CREATE DATABASE database_name

CREATE TABLE
CREATE TABLE table_name
(
column_name1 data_type,
column_name2 data_type,
column_name2 data_type,
…
)

CREATE INDEX
CREATE INDEX index_name
ON table_name (column_name)or
CREATE UNIQUE INDEX index_name
ON table_name (column_name)

CREATE VIEW
CREATE VIEW [...]

SQL Server 2005 Failover Clustering White Paper

SQL Server 2005 Failover Clustering White Paper
Brief Description:
Comprehensive document about implementing failover clustering for SQL Server 2005 and Analysis Services.
Overview:
This white paper is intended for a technical audience and not technical decision makers. It complements the existing documentation around planning, implementing, and administering of a failover cluster that can be found in Microsoft SQL Server [...]

Roles of DBA

A database administrator (DBA) is a person who is responsible for the environmental aspects of a database. In general, these include basic:

Recoverability – Creating and testing Backups
Integrity – Verifying or helping to verify data integrity
Security – Defining and/or implementing access controls to the data
Availability – Ensuring maximum uptime
Performance – Ensuring maximum performance
Development and testing support [...]

Easy step to install SQL Server 2000

Installing SQL Server 2000
loadTOCNode(2, ’summary’);
To Install SQL Server 2000 Basic Local Installation
loadTOCNode(3, ’summary’);

Insert the Microsoft SQL Server 2000 compact disc in your CD-ROM drive (if the compact disc does not run automatically, double-click Autorun.exe in the root directory of the compact disc), select SQL Server 2000 Components, and then select Install Database Server. Setup prepares [...]

Introduction to SQL

SQL is a standard language for accessing and manipulating databases.
What is SQL?

SQL stands for Structured Query Language
SQL lets you access and manipulate databases
SQL is an ANSI (American National Standards Institute) standard

What Can SQL do?

SQL can execute queries against a database
SQL can retrieve data from a database
SQL can insert records [...]