SQL Tidbits Jan 2010

January 31st 2010

  • When a Primary Key is defined on a table, it creates a Clustered Index on that table.

January 30th 2010

  • Date and Time are 2 individual data types introduced in SQL Server 2008.

January 29th 2010

  • Triggers in SQL Server can exist at 2 levels; Server Level and Database level.

January 28th 2010

  • SELECT @@VERSION displays the SQL Server version information along with the Operating System information.

January 27th 2010

  • SQL Server 2008 R2 is announced to be released in May 2010.

January 26th 2010

  • A Delete statement can contain a where clause whereas a Truncate statement cannot.

January 25th 2010

  • TempDB is recreated at each restart of the SQL Server.

January 24th 2010

  • Online Indexing is available only in Enterprise Edition.

January 23rd 2010

  • There can be a maximum of 1000 partitions on a table.

January 22nd 2010

  • The Default port under which SQL Server listens is 1433.

January 21st 2010

  • Backupset table in MSDB contains the backup information of all the database backups of a SQL Instance.

January 20th 2010

  • Database snapshots can be created using T-SQL commands only, it cannot be performed using SSMS.

January 19th 2010

  • SQL Server Replication can be used to copy objects and data pertaining to User Databases only, system databases cannot be replicated.

January 18th 2010

  • A Database can have any of the 3 Recovery models; Simple, Full or Bulk-Logged Recovery Model.

January 17th 2010

  • A new system database called mssqlsystemresource also called as resource database is introduced from SQL Server 2005.

January 16th 2010

  • Database Mirroring is a new High Availability feature introduced SQL Server starting from version 2005.

January 15th 2010

  • There are two types of Indexes; Clustered Index and Non-clustered Index.

January 14th 2010

  • Primary Key is required for Transactional Publication in Replication.

January 13th 2010

  • Indexed views are supported only on Microsoft® SQL Server Enterprise Edition.

January 12th 2010

  • A Heap table or Heap is a table without a clustered Index.

January 11th 2010

  • Simple recovery model does not guarantee a short transaction log files, the transaction log file in simple recovery model will grow until it can allocate the single largest transaction.

January 10th 2010

  • Oracle Publication is supported only in Enterprise Edition.

January 9th 2010

  • Differential backups are cumulative meaning the latest differential backup contains the previous differential backups of the Base Full backup.

January 8th 2010

  • SQL Server has types 2 types of authentication modes, Windows Authentication Mode and SQL Server and Windows Authentication Mode also referred to as Mixed Mode.

January 7th 2010

  • Transaction Log backups can be performed on a database which has recovery model set to Full or Bulk-Logged.

January 6th 2010

  • Starting from SQL Server 2005, Failover Clustering also supports for SQL Server Analysis Services.

January 5th 2010

  • Data Transformation Services have been replaced by SQL Server Integration Services from SQL Server 2005.

January 4th 2010

  • Fill factor value of an Index determines to what level an Index page needs to be full when created or rebuilt.

January 3rd 2010

  • Developer Edition of SQL Server has all the features available in Enterprise Edition, but it is not meant production environment.

January 2nd 2010

  • Common Table Expression (CTE) is one of the new T-SQL enhancements in SQL Server 2005.

January 1st 2010

  • Multiple Instances of SQL Server is supported from SQL Server 2000.