- In-place upgrades requires less SQL Server resources when compared to Side-by-Side upgrades.
- In-place upgrades are difficult to rollback and require longer tme to restore to a previous state of SQL Server.
- In-place upgrades are easier and quicker to perform.
- 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.
- DBCC SQLPERF(WAITSTATS,CLEAR) clears the wait statistics and starts a new count from that point.
- DBCC SQLPERF(WAITSTATS) command displays the wait statistics for that server.
- It is a good practise to have the auto file growth set to a certain fixed size instead of a % value.
- 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).
- DBCC SQLPERF (LOGSPACE) command displays the list of databases, their Log file size in MB and the % of Log space used.
- Database partitioning supports a maximum of 1000 partitions per database.
- SSIS packages can be deployed either in MSDB or in File System.
- Shrinking of database should be carefully planned since it can potentially cause fragmentation.
- Default constraint on a table will effective on those inserts that happen after the constraint is created / modified.
- Oracle database can be configured as Publisher in Replication in SQL Server.
- Publisher and Distributor in a Replication configuration can exist either on the same SQL Server Instance or on different Instances.
- Configuring Distributor is the first step in setting up Replication.
- A subscription in Replication can be either a Push Subscription or a Pull Subscription
- 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).
- 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.
- Page level compression compresses a table more than a row level compression enabled on the same table.
- Filtered Index is one of the new type if Index introduced in SQL Server 2008.
- A Primary Key can be created as a Non Clustered by specifying Primary Key Non Clustered, else it would be a Clustered Primary Key.
- The number of instances that can be installed in SQL Server Standard, Workgroup and other editions (except Enterprise Edition) is 16 instances per machine.
- There are primarily 2 kinds of upgrades that can be performed in SQL Server; In-Place upgrade and Side-by-Side upgrade.
- Analysis Server backups can be password protected using the backup wizard.
- ReportServer Database is not primarily for the storage of the reporting data, it contains the details about reports deployed on the Reporting Server.
- 2 new databases are available when Reporting Server is installed they are ReportServer and ReportServerTempDB.
- Database Compression in SQL Server 2008 comes with 2 modes / types; Row compression and Page compression.
- By default IIS is not installed on Windows Server 2003 or 2008, it has to be installed manually.
- Reporting Server Installation option is enabled only when the Internet Information Services (IIS) is installed.
- Business Intelligence Development Studio is the primary tool used in SQL Server BI.