Feed on
Posts
Comments

Archive for the ‘SQL Index’ Category

SQL SERVER - 2008 - Find If Index is Being Used in Database

Posted in Author Pinal, SQL, SQL Authority, SQL DMV, SQL Index, SQL Performance, SQL Query, SQL Scripts, SQL Server, SQL Tips and Tricks, T SQL, Technology on October 3, 2008 | 5 Comments »

It is very often I get query that how to find if any index is being used in database or not. If any database has many indexes and not all indexes are used it can adversely affect performance. If number of index is higher it reduces the INSERT / UPDATE / DELETE operation but increase [...]

Read Full Post »

SQL SERVER - Guidelines and Coding Standards Complete List Download

Posted in Author Pinal, Best Practices, DBA, Data Warehousing, Database, SQL, SQL Authority, SQL Coding Standards, SQL Constraint and Keys, SQL Cursor, SQL Data Storage, SQL Documentation, SQL Download, SQL Function, SQL Index, SQL Joins, SQL Performance, SQL Query, SQL Scripts, SQL Security, SQL Server, SQL Stored Procedure, SQL Tips and Tricks, SQL Trigger, SQL Utility, SQLAuthority, T SQL, Technology on September 25, 2008 | 3 Comments »

SQL SERVER - Guidelines and Coding Standards complete List Download
Coding standards and guidelines are very important for any developer on the path of successful career. A coding standard is a set of guidelines, rules and regulations on how to write code. Coding standards should be flexible enough or should take care of the situation where [...]

Read Full Post »

SQL SERVER - Guidelines and Coding Standards Part - 1

Posted in Author Pinal, Best Practices, Database, SQL, SQL Authority, SQL Coding Standards, SQL Constraint and Keys, SQL Documentation, SQL Index, SQL Joins, SQL Performance, SQL Query, SQL Scripts, SQL Server, SQL Stored Procedure, SQL Tips and Tricks, Software Development, T SQL, Technology on September 23, 2008 | 13 Comments »

Use “Pascal” notation for SQL server Objects Like Tables, Views, Stored Procedures. Also tables and views should have ending “s”.
Example:
UserDetails
Emails

If you have big subset of table group than it makes sense to give prefix for this table group. Prefix should be separated by _.
Example:
Page_ UserDetails
Page_ Emails

Use following naming convention for Stored [...]

Read Full Post »

SQL SERVER - 2008 - Interview Questions and Answers Complete List Download

Posted in Author Pinal, Database, SQL, SQL Authority, SQL Constraint and Keys, SQL Cursor, SQL Data Storage, SQL DateTime, SQL Documentation, SQL Download, SQL Error Messages, SQL Function, SQL Index, SQL Interview Questions and Answers, SQL Joins, SQL Performance, SQL Query, SQL Scripts, SQL Security, SQL Server, SQL Server DBCC, SQL Stored Procedure, SQL Tips and Tricks, SQL Trigger, SQL Utility, SQL XML, SQLAuthority, T SQL, Technology on September 20, 2008 | 12 Comments »

Download SQL Server 2008 Interview Questions and Answers Complete List
Interview is very important event for any person. A good interview leads to good career if candidate is willing to learn. I always enjoy interview questions and answers series. This is my very humble attempt to write SQL Server 2008 interview questions and answers. SQL Server [...]

Read Full Post »

SQL SERVER - 2008 - Interview Questions and Answers - Part 8

Posted in Author Pinal, Data Warehousing, Database, Main, SQL, SQL Authority, SQL Data Storage, SQL Function, SQL Index, SQL Interview Questions and Answers, SQL Query, SQL Scripts, SQL Server, SQL Server DBCC, SQL Tips and Tricks, SQL Utility, SQLAuthority, T SQL, Technology on September 19, 2008 | 1 Comment »

SQL SERVER - 2008 - Interview Questions and Answers Complete List Download
What is Data Compression?
In SQL SERVE 2008 Data Compression comes in two flavors:

Row Compression
Page Compression

Row Compression
Row compression changes the format of physical storage of data. It minimize the metadata (column information, length, offsets etc) associated with each record. Numeric data types and [...]

Read Full Post »

SQL SERVER - 2008 - Interview Questions and Answers - Part 6

Posted in Author Pinal, Database, Main, SQL, SQL Authority, SQL Data Storage, SQL DateTime, SQL Function, SQL Index, SQL Interview Questions and Answers, SQL Query, SQL Scripts, SQL Server, SQL Tips and Tricks, SQL Utility, SQLAuthority, T SQL, Technology on September 17, 2008 | No Comments »

SQL SERVER - 2008 - Interview Questions and Answers Complete List Download
3) Questions of SQL SERVER 2008
What are the basic functions for master, msdb, model, tempdb and resource databases?
The master database holds information for all databases located on the SQL Server instance and is theglue that holds the engine together. Because SQL Server cannot start [...]

Read Full Post »

SQL SERVER - 2008 - Interview Questions and Answers - Part 4

Posted in Author Pinal, Database, SQL, SQL Authority, SQL Constraint and Keys, SQL Function, SQL Index, SQL Interview Questions and Answers, SQL Query, SQL Scripts, SQL Server, SQL Stored Procedure, SQL Tips and Tricks, SQL Utility, SQLAuthority, T SQL, Technology on September 15, 2008 | 4 Comments »

SQL SERVER - 2008 - Interview Questions and Answers Complete List Download
1) General Questions of SQL SERVER
Which command using Query Analyzer will give you the version of SQL server and operating system?
SELECT SERVERPROPERTY (‘productversion’), SERVERPROPERTY (‘productlevel’), SERVERPROPERTY (‘edition’)
What is SQL Server Agent?
SQL Server agent plays an important role in the day-to-day tasks of a [...]

Read Full Post »

SQL SERVER - 2008 - Interview Questions and Answers - Part 3

Posted in Author Pinal, Database, Main, SQL, SQL Authority, SQL Constraint and Keys, SQL Index, SQL Interview Questions and Answers, SQL Query, SQL Scripts, SQL Server, SQL Tips and Tricks, SQL Utility, SQLAuthority, T SQL, Technology on September 14, 2008 | 3 Comments »

SQL SERVER - 2008 - Interview Questions and Answers Complete List Download
1) General Questions of SQL SERVER
2) Common Questions Asked
Which TCP/IP port does SQL Server run on? How can it be changed?
SQL Server runs on port 1433. It can be changed from the Network Utility TCP/IP properties -> Port number, both on client [...]

Read Full Post »

SQL SERVER - 2008 - Interview Questions and Answers - Part 2

Posted in Author Pinal, Data Warehousing, SQL, SQL Authority, SQL Constraint and Keys, SQL Cursor, SQL Function, SQL Index, SQL Interview Questions and Answers, SQL Joins, SQL Query, SQL Scripts, SQL Server, SQL Stored Procedure, SQL Tips and Tricks, SQLAuthority, T SQL, Technology on September 13, 2008 | 9 Comments »

SQL SERVER - 2008 - Interview Questions and Answers Complete List Download
1) General Questions of SQL SERVER
What is Cursor?
Cursor is a database object used by applications to manipulate data in a set on a row-by-row basis, instead of the typical SQL commands that operate on all the rows in the set at one time.
In [...]

Read Full Post »

SQL SERVER - 2008 - Interview Questions and Answers - Part 1

Posted in Author Pinal, Database, Main, SQL, SQL Authority, SQL Constraint and Keys, SQL Index, SQL Interview Questions and Answers, SQL Query, SQL Scripts, SQL Server, SQL Stored Procedure, SQL Tips and Tricks, SQL Trigger, SQLAuthority, T SQL, Technology on September 12, 2008 | 9 Comments »

SQL SERVER - 2008 - Interview Questions and Answers Complete List Download
1) General Questions of SQL SERVER
What is RDBMS?
Relational Data Base Management Systems (RDBMS) are database management systems that maintain data records and indices in tables. Relationships may be created and maintained across and among the data and tables. In a relational database, relationships [...]

Read Full Post »

SQL SERVER - Explanation about Usage of Unique Index and Unique Constraint

Posted in Author Pinal, DBA, Database, SQL, SQL Authority, SQL Index, SQL Performance, SQL Query, SQL Server, SQL Tips and Tricks, T SQL, Technology on September 7, 2008 | 10 Comments »

I enjoy reading questions from blog readers and answering them. One of the another SQL enthusiastic is Imran who also regulalry answer questions of users on this community blog. Recently he has answered in detail about when to use Unique Index and when to use Unique Constraint.
Cristiano asked following questions :
i need to know how [...]

Read Full Post »

SQL SERVER - Find Primary Key Using SQL Server Management Studio

Posted in Author Pinal, SQL, SQL Authority, SQL Constraint and Keys, SQL Index, SQL Query, SQL Scripts, SQL Server, SQL Tips and Tricks, T SQL, Technology on September 6, 2008 | 2 Comments »

Imran Mohammed is great SQL Expert and always eager to help community members. He enjoys answering question and solving problems of other community fellows. His answers are always detailed and trustworthy. Today we will see interesting question from Prasant and excellent answer from Imran Mohammed.
Question from Prasant:
Hi,
I want to drop the primary key on one [...]

Read Full Post »

SQL SERVER - 2008 - Creating Full Text Catalog and Full Text Search

Posted in Author Pinal, SQL, SQL Authority, SQL Index, SQL Query, SQL Scripts, SQL Server, SQL Tips and Tricks, SQL Utility, T SQL, Technology, tagged on September 5, 2008 | 8 Comments »

Full Text Index helps to perform complex queries against character data.  These queries can include word or phrase searching. We can create a full-text index on a table or indexed view in a database. Only one full-text index is allowed per table or indexed view. The index can contain up to 1024 columns. Software developer [...]

Read Full Post »

SQL SERVER - 2008 - Introduction to Filtered Index - Improve performance with Filtered Index

Posted in Author Pinal, SQL, SQL Authority, SQL Index, SQL Performance, SQL Query, SQL Scripts, SQL Server, SQL Tips and Tricks, T SQL, Technology on September 1, 2008 | 1 Comment »

Filtered Index is a new feature in SQL SERVER 2008. Filtered Index is used to index a portion of rows in a table that means it applies filter on INDEX which improves query performance, reduce index maintenance costs, and reduce index storage costs compared with full-table indexes.
When we see an Index created with some WHERE [...]

Read Full Post »

SQL SERVER - 2008 - Introduction to Online Indexing Operation

Posted in Author Pinal, SQL, SQL Authority, SQL Index, SQL Performance, SQL Query, SQL Scripts, SQL Server, SQL Tips and Tricks, T SQL, Technology on August 15, 2008 | 1 Comment »

When index is created or recreated it usually decreases performance of database. Either SQL takes long time for response or it does not response at all as transactions are blocked. When new table or database goes live it is not possible to find out exactly how many indexes are needed. After [...]

Read Full Post »

SQL SERVER - Effect of Order of Join In Query

Posted in Author Pinal, SQL, SQL Authority, SQL Index, SQL Joins, SQL Query, SQL Scripts, SQL Server, SQL Tips and Tricks, T SQL, Technology on August 2, 2008 | 3 Comments »

Let us try to understand this subject with example.
We will use Adventurworks database for this purpose. Table which we will be using are HumanResources.Employee (290 rows), HumanResources.EmployeeDepartmentHistory (296 rows) and HumanResources.Department (16 rows).
We will be running following two queries and observe the output. In the resultset the order of first column (EmployeeID) is different in [...]

Read Full Post »

SQL SERVER - Find Current Identity of Table

Posted in Author Pinal, SQL, SQL Authority, SQL Index, SQL Query, SQL Scripts, SQL Server, SQL Server DBCC, SQL Tips and Tricks, T SQL, Technology on June 20, 2008 | 4 Comments »

Many times we need to know what is the current identity of the column. I have found one of my developer using aggregated function MAX() to find the current identity.
USE AdventureWorks
GO
SELECT MAX(AddressID)
FROM Person.Address
GO

However, I prefer following DBCC command to figure out current identity.
USE AdventureWorks
GO
DBCC CHECKIDENT (’Person.Address’)
GO

Reference : Pinal Dave (http://www.SQLAuthority.com)

Read Full Post »

SQL SERVER - Order Of Column In Index

Posted in Author Pinal, SQL, SQL Authority, SQL Index, SQL Performance, SQL Query, SQL Scripts, SQL Server, SQL Tips and Tricks, T SQL, Technology on June 2, 2008 | 2 Comments »

I just found one of my Jr. DBA to create many indexes with lots of column in it. After talking with him I found out that he really does not understand how really Index works. He was under impression that if he has more columns in one index, that index has higher chance of getting [...]

Read Full Post »

SQL SERVER - 2005 - Twelve Tips For Optimizing Sql Server 2005 Query Performance

Posted in Author Pinal, Best Practices, Database, SQL, SQL Authority, SQL Coding Standards, SQL Constraint and Keys, SQL Cursor, SQL Function, SQL Index, SQL Joins, SQL Performance, SQL Query, SQL Server, SQL Stored Procedure, SQL Tips and Tricks, SQL Trigger, T SQL, Technology on May 21, 2008 | 12 Comments »

I recently came across very nice article about optimization tips for SQL Server 2005. Here is the list of those 12 tips.
Twelve Tips For Optimizing Sql Server 2005 Query Performance
1. Turn on the execution plan, and statistics
2. Use Clustered Indexes
3. Use Indexed Views
4. Use Covering Indexes
5. Keep your clustered index small.
6. Avoid cursors
7. Archive old [...]

Read Full Post »

SQL SERVER - Download FAQ Sheet - SQL Server in One Page

Posted in Author Pinal, Best Practices, DBA, Database, SQL, SQL Add-On, SQL Authority, SQL Constraint and Keys, SQL Documentation, SQL Download, SQL Function, SQL Index, SQL Joins, SQL Query, SQL Server, SQL Tips and Tricks, SQL Trigger, SQL Utility, Software Development, T SQL, Technology on April 28, 2008 | 26 Comments »

One of the most popular request I have received on this blog is to create one page which list all the SQL Server FAQs. SQL Server technology is very broad as well very deep. This is my humble attempt to list few of the daily used details in one page. Let me know your opinion [...]

Read Full Post »

Older Posts »