SQL Server System Databases

SQL Server System Databases:

Master

• Purpose - Core system database to manage the SQL Server instance. In SQL Server 2005, the Master database is the logical repository for the system objects residing in the sys schema. In SQL Server 2000 and previous editions of SQL Server, the Master database physically stored all of the system objects.
• Prominent Functionality
  • Per instance configurations
  • Databases residing on the instance
  • Files for each database
  • Logins
  • Linked\Remote servers
  • Endpoints
• Additional Information
• The first database in the SQL Server startup process
• In SQL Server 2005, needs to reside in the same directory as the Resource database

• Resource
  • Purpose - The Resource database is responsible for physically storing all of the SQL Server 2005 system objects. This database has been created to improve the upgrade and rollback of SQL Server system objects with the ability to overwrite only this database.
  • Prominent Functionality
    • System object definition
  • Additional Information
    • Introduced in SQL Server 2005 to help manage the upgrade and rollback of system objects
    • Prior to SQL Server 2005 the system related data was stored in the master database
    • Read-only database that is not accessible via the SQL Server 2005 tool set
    • The database ID for the Resource database is 32767
    • The Resource database does not have an entry in master.sys.databases
• TempDB
  • Purpose - Temporary database to store temporary tables (#temptable or ##temptale), table variables, cursors, work tables, row versioning, create or rebuild indexes sorted in TempDB, etc. Each time the SQL Server instance is restarted all objects in this database are destroyed, so permanent objects cannot be created in this database.
  • Prominent Functionality
    • Manage temporary objects listed in the purpose above
  • Additional Information
    • Properly Sizing the TempDB Database
    • Each time a SQL Server instance is rebooted, the TempDB database is reset to its original state
• Model
  • Purpose - Template database for all user defined databases
  • Prominent Functionality
    • Objects
    • Columns
    • Users
  • Additional Information
    • User defined tables, stored procedures, user defined data types, etc can be created in the Model database and will exist in all future user defined databases
    • The database configurations such as the recovery model for the Model database are applied to future user defined databases
• MSDB
  • Purpose - Primary database to manage the SQL Server Agent configurations
  • Prominent Functionality
    • SQL Server Agent Jobs, Operators and Alerts
    • DTS Package storage in SQL Server 7.0 and 2000
    • SSIS Package storage in SQL Server 2005
  • Additional Information
    • Provides some of the configurations for the SQL Server Agent service
    • For the SQL Server 2005 Express edition installations, even though the SQL Server Agent service does not exist, the instance still has the MSDB database
    • Missing SQL Server Agent History
• Distribution
  • Purpose - Primary data to support SQL Server replication
  • Prominent Functionality
    • Database responsible for the replication meta data
    • Supports the data for transaction replication between the publisher and subscriber(s)
• ReportServer
  • Purpose - Primary database for Reporting Services to store the meta data and object definitions
  • Prominent Functionality
    • Reports security
    • Job schedules and running jobs
    • Report notifications
    • Report execution history
• ReportServerTempDB
• Purpose - Temporary storage for Reporting Services
• Prominent Functionality
  • Session information
  • Cache

System Databases Do's and Don'ts

• Data Access - Based on the version of SQL Server query only the recommended objects. In general the system database objects are being deprecated to a set of views, so be sure all of your scripts are accessing the right objects. If not, you are going to have a big project in the future to convert all of your scripts.
• Changing Objects - Do not change system objects. In SQL Server 2005 all of the database objects have been moved to the Resource database which stores the definition for the system objects and can be updated via new SQL Server releases independent of the data.
• New Objects - Creating objects in the system databases is not recommended. If you have objects that are needed for the instance i.e. administrative items, just create a separate DBA database to store these objects.
• Sneaking a Peak - Up to this point, all of the T-SQL code for the tables, views, stored procedures, functions, etc. has been clear text. So you can review the objects and learn from the techniques used by Microsoft.
• Dropping Objects - The most prominent reason to drop system objects are for specific types of lock downs and auditing in particular industries. Although some of those practices are well documented, be sure you understand the ramifications related to administering and developing applications once those restrictions are in place.
• Security - Do not forget about the Public role and Guest user, they are the conduit for users to access the system objects. So that should answer the question of how people (logins\users) can access the objects based on the object owner or schema, depending on the SQL Server version.
• Backups - Be sure to have a consistent backup process for your system databases. Including the system databases with your user defined databases might be the best approach if a disaster occurs.
• Scope - Each SQL Server instance (including the Express Edition) has its own set of SQL Server system databases. As such, if a single Windows server has multiple SQL Server instances installed, a change to one system database only impacts the single instance, not all instances on the Windows server.

Next Steps

• Building your knowledge about the system databases is important to have a better understanding of how SQL Server works internally.
• Next time you see a script referencing a system object, dig a little deeper to learn about the object to expand your understanding of the script and how SQL Server works.
• Although system databases are databases, heed caution when working with them due to the potential impact the change could have across the instance.

When not to use the sa password in SQL Server applications

Where are the places that I should not use the sa login:

• Embedded in management tools or application development tools
• Hard coded in application connection strings
• DTS or SSIS connection objects
• UDL files
• Batch files
• SQLCMD, OSQL or ISQL T-SQL scripts
• In casual conversation
• On sticky notes on your desk or monitor

Alternatives to using the sa login

Depending on how the sa login and password is being used in your environment, dictates what steps can be taken to limit the sa usage. Once you have that information, then you can make a decision on how to migrate from using the sa credentials to other alternatives outlined below.

• Windows authentication to SQL Server with a Windows domain account
• Setup application logins that are assigned the needed rights in order for the application to operate properly
• Convert the code to execute in Jobs without having to authenticate externally to SQL Server
• Use the trusted connection switches for the connection strings or applications

Next Steps

• Assess the usage of the sa login in your environment to determine if the usage is acceptable or needs to be corrected to improve this portion of SQL Server security.
• Once you understand where the sa login is used, determine if the sa login is truly needed. See what alternatives can be put into place to limit the sa login usage and better secure the sa credentials.
• Be sure to properly test your applications to understand the needed rights and validate that the applications work properly.
• Secure the sa password to prevent this login from being used in future applications just because the password is known.
• Work with your team to understand the security needs as new applications are built.

When was the last time your SQL Server database was restored

DECLARE @dbname sysname, @days int

SET @dbname = NULL --substitute for whatever database name you want

SET @days = -30 --previous number of days, script will default to 30

SELECT

rsh.destination_database_name AS [Database],

rsh.user_name AS [Restored By],

CASE WHEN rsh.restore_type = 'D' THEN 'Database'

WHEN rsh.restore_type = 'F' THEN 'File'

WHEN rsh.restore_type = 'G' THEN 'Filegroup'

WHEN rsh.restore_type = 'I' THEN 'Differential'

WHEN rsh.restore_type = 'L' THEN 'Log'

WHEN rsh.restore_type = 'V' THEN 'Verifyonly'

WHEN rsh.restore_type = 'R' THEN 'Revert'

ELSE rsh.restore_type

END AS [Restore Type],

rsh.restore_date AS [Restore Started],

bmf.physical_device_name AS [Restored From],

rf.destination_phys_name AS [Restored To]

FROM msdb.dbo.restorehistory rsh

INNER JOIN msdb.dbo.backupset bs ON rsh.backup_set_id = bs.backup_set_id

INNER JOIN msdb.dbo.restorefile rf ON rsh.restore_history_id = rf.restore_history_id

INNER JOIN msdb.dbo.backupmediafamily bmf ON bmf.media_set_id = bs.media_set_id

WHERE rsh.restore_date >= DATEADD(dd, ISNULL(@days, -30), GETDATE()) --want to search for previous days

AND destination_database_name = ISNULL(@dbname, destination_database_name) --if no dbname, then return all

ORDER BY rsh.restore_history_id DESC

GO

Simple script to backup all SQL Server databases

DECLARE @Name VARCHAR(50)--database name

DECLARE @Path VARCHAR(256) -- path for backup files

DECLARE @FileName VARCHAR(256)--filename for backup

DECLARE @FileDate VARCHAR(20) -- Used for file name

SET @Path ='H:\backuptest\'

SELECT @FileDATE =CONVERT(VARCHAR(20), GETDATE(),112)

DECLARE db_Cursor CURSOR FOR

SELECT name FROM master.dbo.sysdatabases WHERE name NOT IN ('master','model','msdb','tempdb')

OPEN db_Cursor

FETCH NEXT FROM db_Cursor INTO @Name

WHILE @@FETCH_STATUS =0

BEGIN

SET @FileName =@Path + @Name + '_' + @FileDate +'.BAK'

BACKUP DATABASE @Name TO DISK =@FileName

FETCH NEXT FROM db_Cursor INTO @Name

END

CLOSE db_Cursor

DEALLOCATE db_Cursor

What is Identity Columns in Sql. What are its advantages & disadvantages?

In MSSQL there is a feature called a Identity which automatically generates a new sequential no for new record. This is a very good features because once it’s applied on any column developer needs not to be worry about maintaining numbers. SQL automatically fetches a new id & inserts into table.

This features is a panic only when some transaction is running & during execution some error occurs & transaction rolls back or if delete command is fired on system instead of Truncate command. In these scenarios identity sequence doesn’t rollback to its original count & starts from the very next value which system was trying to generate last time.

What is Normalization? What are the Different Types of Normalization?

It is set of rules that have been established to aid in the design of tables that are meant to be connected through relationships. This set of rules is known as Normalization.

Benefits of Normalizing your database include:

• Avoiding repetitive entries
• Reducing required storage space
• Preventing the need to restructure existing tables to accommodate new data
• Increased speed and flexibility of queries, sorts, and summaries

Note: During an interview, people expect to answer a maximum of three normal forms and that's what is expected practically. Actually you can normalize database to fifth normal form. But believe this book, answering three normal forms will put you in a decent shape during an interview.

The three normal forms as follows:

First Normal Form

For a table to be in first normal form, data must be broken up into the smallest units possible. In addition to breaking data up into the smallest meaningful values, tables in first normal form should not contain repetitions groups of fields.

Figure 1: Repeating groups example

In the above example, city1 and city2 are repeating. In order for these tables to be in First normal form, you have to modify the table structure as follows. Also note that the Customer Name is now broken down to first name and last name (First normal form data should be broken down to the smallest unit).

Figure 2: Customer table normalized to first normal form

Second Normal Form

The second normal form states that each field in a multiple field primary key table must be directly related to the entire primary key. In other words, each non-key field should be a fact about all the fields in the primary key.

In the above table of customer, city is not linked to any primary field.

Figure 3: Normalized customer table.

Figure 4: City is now shifted to a different master table.

That takes our database to a second normal form.

Third Normal Form

A non-key field should not depend on another Non-key field. The field Total is dependent on Unit price and qty.

Figure 5: Fill third normal form

So now the Total field is removed and is the multiplication of Unit price * Qty.

What is Denormalization?

Denormalization is the process of putting one fact in numerous places (it is vice-versa of normalization). Only one valid reason exists for denormalizing a relational design - to enhance performance. The sacrifice to performance is that you increase redundancy in a database.

Can you Explain Fourth Normal Form?

Note: Whenever the interviewer is trying to go above the third normal form, there can be two reasons, ego or to fail you. Three normal forms are really enough, practically anything more than that is an overdose.

In fourth normal form, it should not contain two or more independent multi-valued facts about an entity and it should satisfy “Third Normal form”.

So let us try to see what multi-valued facts are. If there are two or more many-to-many relationship in one entity and they tend to come to one place, it is termed as “multi-valued facts”.

Figure 6: Multi-valued facts

In the above table, you can see that there are two many-to-many relationships between Supplier / Product and “Supplier / Location (or in short multi-valued facts). In order for the above example to satisfy the fourth normal form, both the many-to-many relationships should go in different tables.

Figure 7: Normalized to Fourth Normal form.

Can you Explain Fifth Normal Form?

Note: UUUHHH if you get this question after joining the company, do ask him if he himself really uses it?

Fifth normal form deals with reconstructing information from smaller pieces of information. These smaller pieces of information can be maintained with less redundancy.

Example: Dealers sell Product which can be manufactured by various Companies. Dealers in order to sell theProduct should be registered with the Company. So these three entities have a mutual relationship within them.

Figure 8: Not in Fifth Normal Form.

The above table shows some sample data. If you observe closely, a single record is created using lot of small information. For instance: JM Associate can sell sweets under the following two conditions:

• JM Associate should be an authorized dealer of Cadbury
• Sweets should be manufactured by Cadbury company

These two smaller bits of information form one record of the above given table. So in order for the above information to be “Fifth Normal Form” all the smaller information should be in three different places. Below is the complete fifth normal form of the database.

Figure 9: Complete Fifth Normal Form

What is the Difference between Fourth and Fifth normal form?

Note: There is a huge similarity between Fourth and Fifth normal form, i.e. they address the problem of “Multi-Valued facts”.

“Fifth normal form” multi-valued facts are interlinked and “Fourth normal form” values are independent. For instance in the above two questions Supplier/Product and Supplier/Location are not linked. While in fifth form, theDealer/Product/Companies are completely linked.

Have you Heard about Sixth Normal Form?

Note: Arrrrggghhh yes there exists a sixth normal form also. But note guys you can skip this statement. Just in case you want to impress the interviewer...

If you want a relational system in conjunction with time, you use sixth normal form. At this moment SQL Server does not support it directly.

What is the Difference between SQL SERVER 2000 and 2005?

Following are some major differences between the two versions:

• (PG) The most significant change is the .NET integration with SQL SERVER 2005. Stored procedures, user-defined functions, triggers, aggregates, and user-defined types can now be written using your own favorite .NET language (VB.NET, C#, J# etc.). This support was not there in SQL SERVER 2000 where the only language was T-SQL. In SQL 2005, you have support for two languages T-SQL and .NET.
• (PG) SQL SERVER 2005 has reporting services for reports which is a newly added feature and does not exist for SQL SERVER 2000. It was a separate installation for SQL Server 2000.
• (PG) SQL SERVER 2005 has introduced two new data types varbinary (max) and XML. If you remember in SQL SERVER 2000, we had image and text data types. Problem with image and text data types is that they assign the same amount of storage irrespective of what the actual data size is. This problem is solved usingvarbinary (max) which acts depending on amount of data. One more new data type is included XML which enables you to store XML documents and does schema verification. In SQL SERVER 2000, developers usedvarchar or text data type and all validation had to be done programmatically.
• (PG) SQL SERVER 2005 can now process direct incoming HTTP request without IIS Web server. In addition, stored procedure invocation is enabled using the SOAP protocol.
• (PG) Asynchronous mechanism is introduced using server events. In Server event model the server posts an event to the SQL Broker service, later the client can come and retrieve the status by querying the broker.
• For huge databases, SQLSERVER has provided a cool feature called “Data partitioning”. In data partitioning, you break a single database object such as a table or an index into multiple pieces. But for the client application accessing the single database object, “partitioning” is transparent.
• In SQL SERVER 2000, if you rebuilt clustered indexes even the non-clustered indexes where rebuilt. But in SQL SERVER 2005 building the clustered indexes does not build the non-clustered indexes.
• Bulk data uploading in SQL SERVER 2000 was done using BCP (Bulk copy program’s) format files. Now in SQL SERVER 2005 bulk, data uploading uses XML file format.
• In SQL SERVER 2000 there were maximum 16 instances, but in 2005 you can have up to 50 instances.
• SERVER 2005 has support of “Multiple Active Result Sets” also called as “MARS”. In previous versions of SQL SERVER 2000 in one connection, you could only have one result set. Now in one SQL connection, you can query and have multiple results set.
• In previous versions of SQL SERVER 2000, system catalog was stored in the master database. In SQL SERVER 2005, it’s stored in a resource database which is stored as sys object. You cannot access the sys object directly as in the older version we were accessing the master database.
• This is one of the hardware benefits which SQL SERVER 2005 has over SQSERVER 2000 – support of hyper threading. WINDOWS 2003 supports hyper threading; SQL SERVER 2005 can take advantage of the feature unlike SQL SERVER 2000 which did not support hyper threading.
  Note: Hyper threading is a technology developed by INTEL which creates two logical processors on a single physical hardware processor.
• SMO will be used for SQL Server Management.
• AMO (Analysis Management Objects) to manage Analysis Services servers, data sources, cubes, dimensions, measures, and data mining models. You can mapm AMO in old SQL SERVER with DSO (Decision Support Objects).
• Replication is now managed by RMO (Replication Management Objects).
  Note: SMO, AMO and RMO are all using .NET Framework.
• SQL SERVER 2005 uses current user execution context to check rights rather than ownership link chain, which was done in SQL SERVER 2000.
  Note: There is a question on this later see for execution context questions.
• In previous versions of SQL SERVER the schema and the user name was same, but in current, the schema is separated from the user. Now the user owns schema.
  Note: There are questions on this, refer “Schema” later.
  Note: Ok below are some GUI changes.
• Query analyzer is now replaced by query editor.
• Business Intelligence development studio will be used to create Business intelligence solutions.
• OSQL and ISQL command line utility is replaced by SQLCMD utility.
• SQL SERVER Enterprise manager is now replaced by SQL SERVER Management studio.
• SERVER Manager which was running in system tray is now replaced by SQL Computer manager.
• Database mirror concept is supported in SQL SERVER 2005, which was not present in SQL SERVER 2000.
• In SQL SERVER 2005 Indexes can be rebuilt online when the database is in actual production. If you look back in SQL SERVER 2000, you cannot do insert, update, and delete operations when you are building indexes.
• (PG) Other than Serializable, Repeatable Read, Read Committed, and Read Uncommitted isolation levels, there is one more new isolation level “Snapshot Isolation level”.
  Note: We will see “Snapshot Isolation level” in detail in the coming questions.

Summarizing: The major significant difference between SQL SERVER 2000 and SQL SERVER 2005 is in terms of support of .NET Integration, Snap shot isolation level, Native XML support, handling HTTP request, Web service support and Data partitioning. You do not have to really say all the above points during an interview. A sweet summary and you will rock.

What is a Trigger in SQL Server?

A Trigger is a database object that is attached to a table. In many aspects it is similar to a stored procedure. As a matter of fact, triggers are often referred to as a "special kind of stored procedure." The main difference between a trigger and a stored procedure is that the former is attached to a table and is only fired when an INSERT, UPDATE or DELETE occurs.

What are the two types of Triggers in SQL Server?

1. After Triggers : Fired after Insert, Update and Delete operations on a table.

2. Instead of Triggers: Fired instead of Insert, Update and Delete operations on a table.

What are the special tables used by Triggers in SQL Server?

Triggers make use of two special tables called inserted and deleted. The inserted table contains the data referenced in an INSERT before it is actually committed to the database. The deleted table contains the data in the underlying table referenced in a DELETE before it is actually removed from the database. When an UPDATE is issued both tables are used. More specifically, the new data referenced in the UPDATE statement is contained in inserted table and the data that is being updated is contained in deleted table.

Give a real time example for triggers usage?

It is recomended to avoid triggers in a real time environment. There is one scenario I can think of why you may want to use triggers in a real time environment. Let us use an example to understand this.

I have 2 tables, tblPerson and tblGender as shown below. GenderId is the foriegn key in tblPerson table.

Now create a view based on the above 2 tables as shown below.


Select * from vWPersons will give us the result as shown below.
Now update the view the following query. This will change the Gender Text to Female in tblGender table for Id = 1. This is not what we have expected.

Update tblGender Set Gender='Female' where Id=1


The base tables are updated incorrectly. So, Select * from vWPersons will give us the result as shown below.

To update the base tables correctly, you can create an INSTEAD OF trigger on the view as shown below.

Now run the query below which will update the underlying base tables correctly.
Update vWPersons Set Gender='Female' Where Id=1

Select * from vWPersons, will show the correct result set as shown below. The INSTEAD OF trigger has correctly updated the GenderId in tblPerson table.
So, Instead of triggers can be used to facilitate updating Views that are based on multiple base tables.

What is the difference between a User Defined Function (UDF) and a Stored Procedure (SP) in SQL Server

1. Stored Procedure support deffered name resolution where as functions do not support deffered name resolution.

2. User Defined Function can be used in a select statement where as you cannot use a stored procedure in a select statement.

3. UDF's cannot return Image, Text where as a StoredProcedure can return any datatype.

4. In general User Defined Functions are used for computations where as Stored Procedures are used for performing business logic.

5. UDF should return a value where as Stored Procedure need not.

6. User Defined Functions accept lesser number of input parameters than Stored Procedures. UDF can have upto 1023 input parameters where as a Stored Procedure can have upto 21000 input parameters.

7. Temporary Tables can not be used in a UDF where as a StoredProcedure can use Temporary Tables.

8. UDF can not Execute Dynamic SQL where as a Stored Procedure can execute Dynamic SQL.

9. User Defined Function does not support error handling where as Stored Procedure support error handling. RAISEERROR or @@ERROR are not allowed in UDFs.

web user controls

What are ASP.NET Custom controls?

Custom controls extend the tools available to Web developers. Using custom controls, you can encapsulate key aspects of the visual interface and program logic that you want to reuse throughout your application, or throughout your organization.

What are the 3 types of custom controls in ASP.NET?
Microsoft Visual Studio .NET provides three types of custom control for use on Web forms.
1. Web user controls
These combine existing server and HTML controls by using the Visual Studio .NET Designer to create functional units that encapsulate some aspect of the user interface. User controls reside in content files, which must be included in the project in which the controls are used.

2. Composite custom controls
These create new controls from existing server and HTML controls. Although similar to user controls, composite controls are created in code rather than visually, and therefore they can be compiled into an assembly (.dll), which can be shared between multiple applications and used from the Toolbox in Visual Studio .NET.

3. Rendered custom controls
These create entirely new controls by rendering HTML directly rather than using composition. These controls are compiled and can be used from the Toolbox, just like composite controls, but you must write extra code to handle tasks that are performed automatically in composite controls.

What are the limitations of user controls in ASP.NET?
As the user controls are not compiled into assemblies, they have the following limitations:
1. A copy of the control must exist in each Web application project in which the control is used.
2. User controls can’t be loaded in the Visual Studio .NET Toolbox; instead, you must create them by dragging the control from Solution Explorer to the Web form.
3. User control code is initialized after the Web form loads, which means that user control property values are not updated until after the Web form’s Load event.

What are the steps to follow for creating and using a user control in a Web application?
1. Add a Web user control page (.ascx) to your project.
2. Draw the visual interface of the control in the designer.
3. Write code to create the control’s properties, methods, and events.
4. Use the control on a Web form by dragging it from Solution Explorer to the Web form on which you want to include it.
5. Use the control from a Web form’s code by declaring the control at the module level and then using the control’s methods, properties, and events as needed within the Web form.

How do you identify user controls?
User controls are identified by their .ascx file extensions.

What is the base class from which user controls derive?
User controls derive from System.Web.UI.UserControl base class. This base class provides the base set of properties and methods you use to create the control.

What are the steps to follow to create properties and methods for the user control that you can use from a Web form?
To create properties and methods for the user control that you can use from a Web form, follow these steps:
1. Create the public property or method that you want to make available on the containing Web form.
2. Write code to respond to events that occur for the controls contained within the user control. These event procedures do the bulk of the work for the user control.
3. If the property or method needs to retain a setting between page displays, write code to save and restore settings from the control’s ViewState.

What happens when you drag a user control from solution explorer and drop it on a web form?
When you drag a user control from solution explorer and drop it on a web form, Visual Studio .NET generates a @Register directive and HTML tags to create the control on the Web form.

Kill All Active Connections To A Database.

USE master
GO

SET NOCOUNT ON
DECLARE @DBName varchar(50)
DECLARE @spidstr varchar(8000)
DECLARE @ConnKilled smallint
SET @ConnKilled=0
SET @spidstr = ''

Set @DBName = 'DB_NAME'
IF db_id(@DBName) < 4
BEGIN
PRINT 'Connections to system databases cannot be killed'
RETURN
END
SELECT @spidstr=coalesce(@spidstr,',' )+'kill '+convert(varchar, spid)+ '; '
FROM master..sysprocesses WHERE dbid=db_id(@DBName)

IF LEN(@spidstr) > 0
BEGIN
EXEC(@spidstr)
SELECT @ConnKilled = COUNT(1)
FROM master..sysprocesses WHERE dbid=db_id(@DBName)
END