What is normalization? Explain the different levels of normalization?
Check out the article Q100139 from Microsoft knowledge base and, of course, much more information available online. Going to be a good idea to get a hold of any RDBMS fundamental
textbook, especially the CJ of the date. Most of the time, will be fine if you can explain to the
third normal form.
What is denormalization and when you go for that?
As its name indicates, denormalization is the reverse process of normalization. Is the controlled introduction of redundancy in the design of databases. Helps improve the query performance as the number of combinations can be reduced.
How do you implement one-to-one, one-to-many and many-to-many, while designing tables?
One to one relationship can be implemented as a single table and rarely as two tables with primary key relations and foreign. one-to-Many are implemented by splitting the data into two tables with primary key relationships and foreign key. Many to many relationships are implemented using a junction table with the keys of both tables forming the composite primary key join table.
It will be a good idea to read a database design basic textbooks.
What is the difference between a primary key and unique key?
Both the main and only key enforce uniqueness of the column defined. But by default primary key creates a clustered index on the column, which creates a unique nonclustered index by default. Another important difference is that primary key does not allow NULLs, but unique key allows NULL only.
What are data types defined by the user and when to go for them?
defined data types allow you to expand the user base SQL Server datatypes by providing a descriptive name and format of the database. Take, for example, the database is a column called Flight_Num that appears in many tables. In all of these pictures should be varchar (8). In this case you can create a data type defined by the user named Flight_num_type of varchar (8) and use it on all tables.
View sp_addtype, sp_droptype in books online.
What kind of data is low and what is the information that can be stored inside a bit column?
bit data type is used to store boolean information like 1 or 0 (true or false). Even the type of SQL Server 6.5 data bits might have a 1 or 0, and there was no support for NULL. But from SQL Server 7. 0 onwards, bit datatype can represent a third state which is NULL.
Define candidate key, alternate key, composite key.
A candidate key is one that can identify each row in a single table. In general, a candidate key becomes the primary key of the table. If the table has more than one candidate key, one of them will become the primary key, and the rest are called alternate keys.
A key formed by the combination of at least two or more columns is called composite key.
What are defaults? Is there a column that by default can not be bound?
A default is a value that will be used by a column, if not given a value to that column while inserting data. IDENTITY columns and timestamp columns can not have defaults bound to them. See CREATE DEFAULT in books online.
What is a transaction and what are ACID properties?
A transaction is a logical unit of work that all steps must be performed or not. ACID stands for atomicity, consistency, isolation, durability. These are the properties of a transaction. For more information and explanation of these properties, see SQL Server books online or any RDBMS fundamentals text book.
Explain different isolation levels
An isolation level determines the degree of isolation of data between concurrent transactions. The default SQL Server isolation level is read committed. These are the other isolation levels (in ascending order of isolation): Read Uncommitted, Read Committed, Repeatable Read, Serializable. See SQL Server books online for an explanation of the isolation levels. Be sure to read about SET TRANSACTION ISOLATION LEVEL, which lets you customize the isolation level at the connection level.
MyIndex CREATE INDEX ON myTable (myColumn)
What kind of index to be created after executing the above statement?
Nonclustered index. An important thing to note: By default a clustered index is created on the primary key, unless otherwise specified.
What is the maximum size of a row?
8060 bytes. Do not be surprised with questions like "What is the maximum number of columns per table. Check out SQL Server books online for the page titled:" Maximum Capacity Specifications ".
Explain Active / Active and Active / Passive cluster configurations
Hopefully you have experience in setting up cluster servers. But if not, at least familiar with the way clustering works and clusterning two configurations Active / Active and Active Passive /. SQL Server books online has enough information on this subject and there is a good white paper available on the Microsoft site.
Explain the SQL Server architecture
This is a very important question and you better be able to answer if you consider yourself a DBA. SQL Server Books Online is the best place to read about the architecture of SQL Server. Read the chapter dedicated to SQL Server Architecture.
What is lock escalation?
Lock escalation is the process of converting a large amount of low level locks (like row locks, page locks) into higher level locks (like table locks). Each block is a memory structure too many locks would mean, more memory being occupied by locks. To prevent this from happening, SQL Server raises many fine-grain locks to fewer coarse-grained locks. Lock escalation threshold can be defined in SQL Server 6.5, but from SQL Server 7.0 onwards is dynamically managed by SQL Server.
What is the difference between DELETE and TRUNCATE TABLE?
Delete table is a logged operation, so the deletion of each row is logged in the transaction, making it slow. TRUNCATE TABLE also deletes all rows from a table, but it will not log the deletion of each row, it logs the deallocation of data pages in the table, making it faster. Of course, TRUNCATE TABLE can be undone.
Explain the storage models of OLAP
Check out MOLAP, ROLAP and HOLAP SQL Server books online for more information.
What are the new features introduced in SQL Server 2000 (or the latest version of SQL Server at the time of the interview)? What has changed between the previous version of SQL Server and the current version?
This question is usually asked to see how their existing knowledge. In general, there is a section at the beginning of the online books titled "What's New" which has all the information. Of course, reading just is not enough, he should have tried those things to better respond to the questions. Also check out the "Backward Compatibility" in books online which talks about the changes that have taken place in the new version.
What are the limitations? Explain the different types of constraints.
Constraints allow the RDBMS enforce the integrity of the database automatically, without having to create triggers, rule or default.
Types of constraints: NOT NULL, CHECK, UNIQUE, PRIMARY KEY, FOREIGN KEY
For an explanation of these constraints see books online for the pages titled: "Limitations" and "CREATE TABLE", "ALTER TABLE"
Whar is an index? What are the types of indexes? How clustered indexes can be created in a table? I can create a separate index on each column of a table. What are the advantages and disadvantages of this approach?
Indexes in SQL Server are similar to indexes in books. They help SQL Server retrieve the data faster.
Indexes are of two types. Clustered index and nonclustered indexes. When craete a clustered index on a table, all rows in the table are stored in the order of the clustered index key. Therefore, there can be only one clustered index per table. No clustered indexes have their own separate storage of the data table storage. Nonclustered indexes are stored as B-tree structures (so do clustered indexes), with leaf-level nodes with the key index and the row locator. The row located could be the RID or the Clustered index key, depending on the absence or presence of clustered index on the table.
If you create an index on each column of a table, which improves query performance because the query optimizer can choose between all the existing indexes to reach an efficient execution plan. At the same t ime, the data modification operations (such as INSERT, UPDATE, DELETE) will become slow, as every time data changes in the table, all indexes must be updated. Another disadvantage is that the indexes need disk space, the indices have, more disk space is used.
What is RAID and what are the different types of RAID configurations?
RAID stands for Redundant Array of Inexpensive Disks, used to provide fault tolerance to database servers. There are six RAID levels 0 and 5 offering different levels of performance, fault tolerance. MSDN has some information about RAID levels and for detailed information, visit the website of the RAID Advisory Board
What are the steps you can take to improve the performance of a poor performing query?
This is a very open question and there could be many reasons behind the poor performance of a query. However, some general issues that you could talk would be: No indexes, table analysis, missing or out of date statistics, blocking, excessive recompiles stored procedures, triggers and procedures without SET NOCOUNT ON, poorly written query with unnecessarily complicated joins, too much normalization, excess usage cursors and temporary tables.
Some of the tools and ways to help you troubleshoot performance problems are SHOWPLAN_ALL ON SET, SET SHOWPLAN_TEXT ON, SET STATISTICS IO ON, SQL Server, Windows NT / 2000 Performance Monitor, the graphical execution plan in Query Analyzer .
Download the white paper on performance tuning SQL Server in the Microsoft Web site. Do not forget to visit SQL-Server-performance.com
What are the steps you should follow if they have a duty to ensure SQL Server?
Again, this is another open question. Here are some things we could talk about: Preferring NT authentication, using server, database and application roles to control access to data, files ensuring physical database using NTFS permissions, using an SA password impossible to guess, restrict physical access to SQL Server, renaming the administrator account on the SQL Server computer, disabling the Guest account, enabling auditing, using multiprotocol encryption, setting up SSL, setting up firewalls, isolating SQL Server from web server, etc.
Read the white paper on SQL Server security from Microsoft website. Also check out my security best practices for SQL Server
What is a deadlock and what is a live lock? How will you go about resolving blocks?
Stalemate is a situation in which two processes, each having a lock on one piece of data, attempts to acquire a lock on the other piece. Each process would wait indefinitely for the other to release the lock, unless one of the user processes is terminated. SQL Server detects deadlocks and terminates a user process.
A livelock is one, which is several times an exclusive lock request denied because a series of overlapping shared locks continue to interfere. SQL Server detects the situation after four denials and refuses further shared locks. A livelock also occurs when read transactions monopolize a table or page, forcing a write transaction to wait indefinitely.
View DEADLOCK_PRIORITY SET and "Minimizing Deadlocks" in SQL Server books online. Also check out the article Q169960 from Microsoft knowledge base.
What is blocking and how to solve their problems?
Blocking occurs when a connection from an application holds a lock and a second connection requires a conflicting lock type. This forces the second connection to wait, blocked on the first.
Read the following topics in SQL Server books online: Understanding and avoiding blocking, efficient coding operations.
Explain CREATE DATABASE syntax
Many of us are accustomed to craeting databases from Enterprise Manager or simply run the command: CREATE dating MyDB. But what if you need to create a database with two filegroups, one on drive C and
other on drive D with log on drive E with an initial size of 600 MB and with a growth factor of 15%? That's why a DBA should be familiar with the CREATE DATABASE syntax. Check out SQL Server books
online for more information.
How to restart SQL Server in single user mode? How to start SQL Server in minimal configuration mode?
SQL Server can be started from the command line, using the SQLSERVR.EXE. This EXE has some very important parameters with which a DBA should know. -M is used to start SQL Server in single user mode
f is used to start SQL Server in minimal mode confuguration. Check out SQL Server books online for more parameters and their explanations.
As part of your job, what are the DBCC commands that are commonly used for the maintenance of databases?
DBCC CHECKDB, DBCC CHECKTABLE CHECKCATALOG DBCC, DBCC CHECKALLOC, SHOWCONTIG DBCC, DBCC SHRINKDATABASE, DBCC SHRINKFILE etc. But there are plenty of DBCC commands which are very useful for DBAs. Check out SQL Server books online for more information.
What are the statistics, in what circumstances are out of date, how to update them?
Statistics determine the selectivity of the indexes. If an indexed column has unique values then the selectivity of the index is more, rather than an index with non-unique values. query optimizer uses indexes to determine whether to choose an index or not while executing a query.
Some situations where you should update statistics:
1) If there is a significant change in the key values in the index
2) If a large amount of data in an indexed column has been added, modified or removed (ie, if the distribution of key values has changed), or the table has been truncated with TRUNCATE TABLE statement and then repopulated,
3) Database is upgraded from an earlier version
Search SQL Server books online for the following commands: UPDATE
STATISTICS SHOW_STATISTICS STATS_DATE, DBCC, CREATE STATISTICS, DROP
STATISTICS sp_autostats, sp_createstats, sp_updatestats
What are the different forms of movement of data and databases between servers and databases in SQL Server?
There are plenty of options available, you select the option, depending on your needs. Some of the choices are: Backup / Restore, dettaching and attach databases, replication, DTS, BCP, logshipping, INSERT ... SELECT, SELECT INTO ... , Creating INSERT scripts to generate data.
Avaialabe explain different types of backups in SQL Server? Given a particular scenario, how to go about choosing a backup plan?
Types of backups you can create in SQL Sever 7.0 + are the database full backup, differential backup database, backup transaction log file group backup. View backup and restore commands in SQL Server books online. Be prepared to write the commands in your interview. Books also has detailed information on the backup / restore architecture and when one should go for a certain type of backup.
Replicaion What is database? What are the different types of replication can be configured in SQL Server?
Replication is the process of copying / moving data between databases on the same or different servers. SQL Server supports the following types of replication scenarios:
* Snapshot replication
* Transactional replication (with immediate updating subscribers, with queued updating subscribers)
* Merge replication
See SQL Server books online for in-depth coverage on replication. Be prepared to explain how different replication agents function, what are the main system tables used in replication, etc.
How to Determine the Service Pack currently installed on SQL Server?
The global variable @ @ Version stores the build number of the sqlservr.exe, which is used to determine the Service Pack installed. To learn more about this process visit SQL Server service packs and versions.
What are cursors? Explain the different types of cursors. What are the disadvantages of cursors? How can you avoid cursors?
Cursors allow row by row prcessing associated results.
Types of cursors: Static, dynamic, forward-only, keyset-driven. See the books online for more information.
Disadvantages of cursors: Each time you retrieve a row from the cursor, the result is a network roundtrip, while a normal SELECT query makes only one rowundtrip, however large the result set. Cursors are also costly because they require more resources and temporary storage (results in more I / S). Farther away, there are restrictions on the SELECT statements that can be used with some types of cursors.
Most times, set based operations can be used instead of cursors. Here's an example:
If you have to give a flat hike to your employees with the following criteria:
Salary between 30000 and higher 40000-5000
Salary between 40000 and higher 55000-7000
Salary between 55000 and higher 65000-9000
In this situation many developers tend to use a cursor, determine each employee's salary and update his salary according to the formula above. But the same can be achieved by multiple update statements or can be combined into a single UPDATE statement as shown below:
UPDATE tbl_emp salary scales set =
CASE WHEN salary between 30,000 and 40,000 THEN salary + 5000
When the salary between 40,000 and 55,000 THEN salary + 7000
When the salary between 55,000 and 65,000 THEN salary + 10000
FIN
Another situation in which developers tend to use cursors: You need to call a stored procedure in a column in a particular row meets certain condition. You do not have to use cursors to do so. This can be achieved using WHILE loop, as long as there is a unique key to identify each row. For examples of using WHILE loop for row by row processing, check the "My Library Codes" on my site or search time.
Write the general syntax of the SELECT statements covering all options.
This is the basic syntax: (Select also purchase books online for advanced syntax).
SELECT select_list
[EN new_table_]
DE table_source
[WHERE search_condition]
[GROUP BY group_by__expression]
[CON search_condition]
[Order__expression ORDER BY [ASC | DESC]]
What is a join and explain different types of combinations?
Combinations are used in queries to explain how different tables are related. Binds also allows you to select data from a table based on data from another table.
Types of joins: INNER JOIN, OUTER JOIN, CROSS JOIN. OUTER JOIN are classified as left outer join, right, and combinations TOTAL EXTERIOR EXTERIOR.
For more information, see pages from books online titled: "Understanding" and "Using combinations."
Can you have a nested transaction?
Yes, very. Check out BEGIN TRAN, COMMIT, ROLLBACK, SAVE TRAN and @ @ TRANCOUNT
What is an extended stored procedure? Can you instantiate a COM object by using T-SQL?
An extended stored procedure is a function in a DLL (written in a programming language like C, C + + using Open Data Services (ODS) API) that can be called from T-SQL, just the way we call normal stored procedures using the EXEC statement. See the books online to learn how to create extended stored procedures and how to add them to SQL Server.
Yes, you can instantiate a COM (written in languages such as Visual Basic, VC + +) object from T-SQL using stored procedure sp_OACreate. Also see books online for sp_OAMethod, sp_OAGetProperty, sp_OASetProperty, sp_OADestroy. For an example of how to create a COM object in VB and call it T-SQL, see 'My code library' of this site.
What is the function of the system to obtain current user ID?
USER_ID (). Also check out other system functions like USER_NAME (),
SYSTEM_USER, SESSION_USER, CURRENT_USER, USER, SUSER_SID (), HOST_NAME ().
What are the triggers? How many triggers that can have on the table? How to use a trigger on demand?
Triggers are special kind of stored procedures that run automatically when an INSERT, UPDATE or DELETE operation takes place on a table.
In SQL Server 6.5 can define only 3 triggers per table, one for INSERT, UPDATE and one for one for DELETE. From SQL Server 7.0 onwards, this restriction is gone, and may create multiple triggers per each action. However, in 7.0 there is no way to control the order in which triggered the fire. In SQL Server 2000 you can specify which trigger fires first or fires last sp_settriggerorder with
Triggers can not be invoked on demand. They are active only when an associated action (INSERT, UPDATE, DELETE) happens on the table on which they are defined.
Triggers are generally used to implement business rules, auditing. Triggers can also be used to extend the referential integrity checks, but wherever possible, use constraints for this purpose, instead of triggers, as constraints are much faster.
Until SQL Server 7.0, triggers fire only after the data modification operation happens. So in a sense, they are called post triggers. However, in SQL Server 2000 can also create pre triggers. Search SQL Server 2000 books online for INSTEAD OF triggers.
Also check out the books online for 'inserted table', 'deleted table and COLUMNS_UPDATED ()
There is a trigger defined for INSERT operations on a table in an OLTP system. The trigger is written to instantiate a COM object and pass the new rows instert that for some custom processing. What do you think of this implementation? Can it be implemented better?
Instantiating COM objects is a slow process and it is already doing within a trigger, it slows down the data insertion process. Same is the case of sending emails from triggers. This scenario can be better implemented by recording all necessary data in a separate table, and has a job that periodically checks this table and necessary.
What is a self join? Explain with an example.
Self-join is like any other combination, except that two instances of the same table is joined in the query. Here's an example: Employees table which contains rows for normal employees and managers. Therefore, to know the managers of all employees, you need a self join.
CREATE TABLE emphasis
(
empid int,
MgrID int
empname char (10)
)
emphasis INSERT SELECT 1.2, 'Vyas'
emphasis INSERT SELECT 2.3, 'Mohamed'
emphasis INSERT SELECT 3, NULL, 'Shobha'
4.2 SELECT INSERT emphasis, "Shridhar '
emphasis INSERT SELECT 5.2, 'Sourabh'
SELECT t1.empname [employees], t2.empname [Administrator]
Of emphasis t1, t2 emphasis
WHERE t1.mgrid = t2.empid
Here's an advanced search using a LEFT OUTER JOIN that even returns the employees without managers (super bosses)
SELECT t1.empname [employees], COALESCE (t2.empname, 'No manager) [Administrator]
FROM t1 emphasis
LEFT OUTER JOIN
emphasis t2
ON
t1.mgrid = t2.empid
0 Comments