SQL Tidbits May 2010

May 31st

  • In-place upgrades requires less SQL Server resources when compared to Side-by-Side upgrades.

May 30th

  • In-place upgrades are difficult to rollback and require longer tme to restore to a previous state of SQL Server.

May 29th

  • In-place upgrades are easier and quicker to perform.

May 28th

  • System tables in SQL Server 2000 such as sysprocesses, sysdatabases etc are supported in SQL Server 2008 as catalog views for backward compatibility, but they might be discontinued from the future versions.

May 27th

  • DBCC SQLPERF(WAITSTATS,CLEAR) clears the wait statistics and starts a new count from that point.

May 26th

  • DBCC SQLPERF(WAITSTATS) command displays the wait statistics for that server.

May 25th

  • It is a good practise to have the auto file growth set to a certain fixed size instead of a % value.

May 24th

  • When Autogrowth is enabled on database files, the file growth can be set to grow by a certain percentage or a fixed amount of size (in MB).

May 23rd

  • DBCC SQLPERF (LOGSPACE) command displays the list of databases, their Log file size in MB and the % of Log space used.

May 22nd

  • Database partitioning supports a maximum of 1000 partitions per database.

May 21st

  • SSIS packages can be deployed either in MSDB or in File System.

May 20th

  • Shrinking of database should be carefully planned since it can potentially cause fragmentation.

May 19th

  • Default constraint on a table will effective on those inserts that happen after the constraint is created / modified.

May 18th

  • Oracle database can be configured as Publisher in Replication in SQL Server.

May 17th

  • Publisher and Distributor in a Replication configuration can exist either on the same SQL Server Instance or on different Instances.

May 16th

  • Configuring Distributor is the first step in setting up Replication.

May 15th

  • A subscription in Replication can be either a Push Subscription or a Pull Subscription

May 14th

  • In SQL Server 2008 failover cluster, SQL Server has to be installed on each of the nodes participating in the SQL Server Cluster individually (separately).

May 13th

  • In SQL Server 2005 failover cluster, SQL Server can be installed from one of the nodes and the installation is simultaneously performed on all the nodes participating in the SQL Server Cluster.

May 12th

  • Page level compression compresses a table more than a row level compression enabled on the same table.

May 11th

  • Filtered Index is one of the new type if Index introduced in SQL Server 2008.

May 10th

  • A Primary Key can be created as a Non Clustered by specifying Primary Key Non Clustered, else it would be a Clustered Primary Key.

May 9th

  • The number of instances that can be installed in SQL Server Standard, Workgroup and other editions (except Enterprise Edition) is 16 instances per machine.

May 8th

  • There are primarily 2 kinds of upgrades that can be performed in SQL Server; In-Place upgrade and Side-by-Side upgrade.

May 7th

  • Analysis Server backups can be password protected using the backup wizard.

May 6th

  • ReportServer Database is not primarily for the storage of the reporting data, it contains the details about reports deployed on the Reporting Server.

May 5th

  • 2 new databases are available when Reporting Server is installed they are ReportServer and ReportServerTempDB.

May 4th

  • Database Compression in SQL Server 2008 comes with 2 modes / types; Row compression and Page compression.

May 3rd

  • By default IIS is not installed on Windows Server 2003 or 2008, it has to be installed manually.

May 2nd

  • Reporting Server Installation option is enabled only when the Internet Information Services (IIS) is installed.

May 1st

  • Business Intelligence Development Studio is the primary tool used in SQL Server BI.