Welcome to our blog series on SQL Server! In this post, we will discuss some fundamental concepts and new features of SQL Server 2008. Whether you are a beginner or an experienced DBA, this article will provide you with valuable insights into the world of SQL Server.

Understanding the Basic Functions of Databases

SQL Server consists of several databases, each serving a specific purpose. Let’s take a look at some of the key databases:

• Master Database: The master database holds information for all the databases located on the SQL Server instance. It is essential for the functioning of the SQL Server engine.
• Msdb Database: The msdb database stores information related to database backups, SQL Agent information, DTS packages, SQL Server jobs, and some replication information.
• Tempdb: The tempdb holds temporary objects such as global and local temporary tables and stored procedures.
• Model Database: The model database serves as a template for creating new user databases in the SQL Server instance.
• Resource Database: The resource database is a read-only database that contains all the system objects included in SQL Server. It does not contain user data or user metadata.

Maximum Number of Indexes per Table

In SQL Server, the number of indexes that can be created on a table is limited. The limit varies depending on the version of SQL Server:

• For SQL Server 2005: 1 Clustered Index + 249 Nonclustered Index = 250 Indexes.
• For SQL Server 2008: 1 Clustered Index + 999 Nonclustered Index = 1000 Indexes.

New Features of SQL Server 2008 Management Studio

SQL Server 2008 introduced several new features and functionalities in the Management Studio (SSMS) based on the feedback from DBAs. Let’s explore a few of these features:

• IntelliSense for Query Editing: IntelliSense helps in writing queries by providing suggestions and completing keywords. It eliminates the need to remember syntax or browse online references.
• Multi Server Query: SSMS 2008 allows running a query on different servers from a single query editor window. This feature is useful for managing multiple servers efficiently.
• Query Editor Regions: When working with large T-SQL code, it can become confusing. Query Editor Regions allow organizing code into sections defined by GO commands or BEGIN-END blocks, making it easier to navigate and understand.
• Object Explorer Enhancements: The Object Explorer Detail now includes an Object Search feature, allowing you to search for specific objects within the database. Additionally, new wizards assist in various tasks, and data from the Object Explorer can be easily copied to Excel.
• Activity Monitors: The Activity Monitors provide valuable information about server performance, including graphs for Processor Time, Waiting Tasks, Database I/O, and Batch Requests/Sec. The “Recent Expensive Queries” tab helps identify slow-running queries for performance optimization.

We hope this overview of SQL Server concepts and features has been informative. Stay tuned for more articles in our SQL Server series, where we will dive deeper into various topics and provide practical examples.