SQL Tidbits Jun 2010

June 27th

  • You can not create Indexes on columns which are of datatype Text, ntext, image etc.

June 26th

  • A view can have a maximum of 1024 columns.

June 25th

  • DBCC TRACESTATUS (-1) displays the Trace flags that are currently on.

June 24th

  • Page level restore can be performed on databases which have either bulk-logged or full recovery model.

June 23rd

  • It is recommended that the drives where SQL Server databases are placed should be a NTFS drives.

June 22nd

  • Notification Services are discontinued / deprecated in SQL Server 2008.

June 21st

  • A user who needs to only read the data from tables in a database can be assigned with db_datareader database role.

June 20th

  • Sysadmin Fixed Server role is the highest possible server role that can be assigned to a user.

June 19th

  • Replication is one of th ways the tables inside Data Warehouse can be populated with.

June 18th

  • When logging to SQL Server remember that user name is not case sensitive where as the password is case sensitive.

June 17th

  • Clustered index can be created on a column which contains duplicate records.

June 16th

  • SQL Server provides different ways of encrypting data, one of them is Transparent Data Encryption and the other one is Cell level Encryption.

June 15th

  • A Common Table Expression can be used instead of dervied tables and ensures that CTE’s can be reused within the same SQL Server stored procedure / query.

June 14th

  • Between condition in a where clause, includes both the values used as the lower and higher range; example, age between 4 and 14 includes all records with age starting at 4 through 14.

June 13th

  • By default, Order By clause sorts the data in ascending order, to change the data order to descending, you need to use DESC after the column name.

June 12th

  • When submitting a workload in Database Engine Tuning Advisor, you can specify an end time for the workload.

June 11th

  • A Profiler Trace can be saved in a trace file or as a SQL Server table.

June 10th

  • For optimum performance, the number of TempDB files should be equal to the number of prcessorss on the machine.

June 9th

  • It is always suggested to set autogrowth option to true for all database files.

June 8th

  • Only Non-Clustered Indexes can have included columns, clustered Indexes do not allow Included columns.

 June 7th

  • There can be multiple data and log files for a single database.

June 6th

  • Database Mirroring is considered as Hot Standby where as Log Shipping is considered as Warm Standby.

June 5th

  • Filegroups are not applicable to log files, they are only applicable for data files..

June 4th

  • Introduced in SQL Server 2008, multiple records can be inserted in table using a single INSERT statement.

June 3rd

  • Text based execution plans might not be supported in the future releases of SQL Server.

June 2nd

  • Execution plan can be viewed in 3 different formats ( as of this day), they are Text, Graphical and XML based execution plans.

June 1st

  • Side-by-Side Upgrades gives more work for the admin, takes more time to Upgrade and requires additional Server to host the SQL Server or additional Instance besides the existing instance.