Difference between char and nvarchar / char and varchar data-type?
Nov 23rd
char[(n)] – Fixed-length non-Unicode character data with length of n bytes. n must be a value from 1 through 8,000. Storage size is n bytes. The SQL-92 synonym for char is character. nvarchar(n) – Variable-length Unicode character data of n characters. n must be a value from 1 through 4,000. Storage size, in bytes, is two times the number of characters entered. The data entered can be 0 characters in length. The SQL-92 synonyms for nvarchar are national char varying and national character varying. varchar[(n)] – Variable-length non-Unicode character data with length of n bytes. n must be a value More >
What is sorting and what is the difference between sorting & clustered indexes?
Nov 23rd
- The ORDER BY clause sorts query results by one or more columns up to 8,060 bytes. This will happen by the time when we retrieve data from database. Clustered indexes physically sorting data, while inserting/updating the table.
DATA TYPES
What are the data types in SQL
Bigint Binary bit char cursor Datetime Decimal float image int Money Nchar ntext nvarchar real Smalldatetime Smallint smallmoney text timestamp Tinyint Varbinary Varchar uniqueidentifierDifference between Index defrag and Index rebuild?
Nov 23rd
When you create an index in the database, the index information used by queries is stored in index pages. The sequential index pages are chained together by pointers from one page to the next. When changes are made to the data that affect the index, the information in the index can become scattered in the database. Rebuilding an index reorganizes the storage of the index data (and table data in the case of a clustered index) to remove fragmentation. This can improve disk performance by reducing the number of page reads required to obtain the requested data DBCC INDEXDEFRAG – More >
What is Index Tuning?
Nov 23rd
One of the hardest tasks facing database administrators is the selection of appropriate columns for non-clustered indexes. You should consider creating non-clustered indexes on any columns that are frequently referenced in the WHERE clauses of SQL statements. Other good candidates are columns referenced by JOIN and GROUP BY operations.
You may wish to also consider creating non-clustered indexes that cover all of the columns used by certain frequently issued queries. These queries are referred to as “covered queries” and experience excellent performance gains. Index Tuning is the process of finding appropriate column for non-clustered indexes. SQL Server provides a wonderful facility More >
Given a scenario that I have a 10 Clustered Index in a Table to all their 10 Columns. What are the advantages and disadvantages?
Nov 23rd
Only 1 clustered index is possible
What is Disadvantage of index?
Nov 23rd
Every index increases the time in takes to perform INSERTS, UPDATES and DELETES, so the number of indexes should not be very much.
Explain about Clustered and non clustered index? How to choose between a Clustered Index and a Non-Clustered Index?
Nov 23rd
There are clustered and nonclustered indexes. A clustered index is a special type of index that reorders the way records in the table are physically stored. Therefore table can have only one clustered index. The leaf nodes of a clustered index contain the data pages. A nonclustered index is a special type of index in which the logical order of the index does not match the physical stored order of the rows on disk. The leaf nodes of a nonclustered index does not consist of the data pages. Instead, the leaf nodes contain index rows. Consider using a clustered index More >
What is Index? It’s purpose?
Nov 22nd
