Click here to get free chapters (PDF) in the mailbox
SQL SERVER – 2008 – Interview Questions and Answers Complete List Download
1) General Questions of SQL SERVER
What is RDBMS?
Relational Data Base Management Systems (RDBMS) are database management systems that maintain data records and indices in tables. Relationships may be created and maintained across and among the data and tables. In a relational database, relationships between data items are expressed by means of tables. Interdependencies among these tables are expressed by data values rather than by pointers. This allows a high degree of data independence. An RDBMS has the capability to recombine the data items from different files, providing powerful tools for data usage. (Read More Here)
What are the properties of the Relational tables?
Relational tables have six properties:
- Values are atomic.
- Column values are of the same kind.
- Each row is unique.
- The sequence of columns is insignificant.
- The sequence of rows is insignificant.
- Each column must have a unique name.
What is Normalization?
Database normalization is a data design and organization process applied to data structures based on rules that help building relational databases. In relational database design, the process of organizing data to minimize redundancy is called normalization. Normalization usually involves dividing a database into two or more tables and defining relationships between the tables. The objective is to isolate data so that additions, deletions, and modifications of a field can be made in just one table and then propagated through the rest of the database via the defined relationships.
What are different normalization forms?
1NF: Eliminate Repeating Groups
Make a separate table for each set of related attributes, and give each table a primary key. Each field contains at most one value from its attribute domain.
2NF: Eliminate Redundant Data
If an attribute depends on only part of a multi-valued key, remove it to a separate table.
3NF: Eliminate Columns Not Dependent On Key
If attributes do not contribute to a description of the key, remove them to a separate table. All attributes must be directly dependent on the primary key. (Read More Here)
BCNF: Boyce-Codd Normal Form
If there are non-trivial dependencies between candidate key attributes, separate them out into distinct tables.
4NF: Isolate Independent Multiple Relationships
No table may contain two or more 1:n or n:m relationships that are not directly related.
5NF: Isolate Semantically Related Multiple Relationships
There may be practical constrains on information that justify separating logically related many-to-many relationships.
ONF: Optimal Normal Form
A model limited to only simple (elemental) facts, as expressed in Object Role Model notation.
DKNF: Domain-Key Normal Form
A model free from all modification anomalies is said to be in DKNF.
Remember, these normalization guidelines are cumulative. For a database to be in 3NF, it must first fulfill all the criteria of a 2NF and 1NF database.
What is De-normalization?
De-normalization is the process of attempting to optimize the performance of a database by adding redundant data. It is sometimes necessary because current DBMSs implement the relational model poorly. A true relational DBMS would allow for a fully normalized database at the logical level, while providing physical storage of data that is tuned for high performance. De-normalization is a technique to move from higher to lower normal forms of database modeling in order to speed up database access.
What is Stored Procedure?
A stored procedure is a named group of SQL statements that have been previously created and stored in the server database. Stored procedures accept input parameters so that a single procedure can be used over the network by several clients using different input data. And when the procedure is modified, all clients automatically get the new version. Stored procedures reduce network traffic and improve performance. Stored procedures can be used to help ensure the integrity of the database.
e.g. sp_helpdb
, sp_renamedb
, sp_depends
etc.
What is Trigger?
A trigger is a SQL procedure that initiates an action when an event (INSERT, DELETE or UPDATE) occurs. Triggers are stored in and managed by the DBMS. Triggers are used to maintain the referential integrity of data by changing the data in a systematic fashion. A trigger cannot be called or executed; DBMS automatically fires the trigger as a result of a data modification to the associated table. Triggers can be viewed as similar to stored procedures in that both consist of procedural logic that is stored at the database level. Stored procedures, however, are not event-drive and are not attached to a specific table as triggers are. Stored procedures are explicitly executed by invoking a CALL to the procedure while triggers are implicitly executed. In addition, triggers can also execute stored procedures.
Nested Trigger: A trigger can also contain INSERT, UPDATE and DELETE logic within itself, so when the trigger is fired because of data modification it can also cause another data modification, thereby firing another trigger. A trigger that contains data modification logic within itself is called a nested trigger. (Read More Here)
What is View?
A simple view can be thought of as a subset of a table. It can be used for retrieving data, as well as updating or deleting rows. Rows updated or deleted in the view are updated or deleted in the table the view was created with. It should also be noted that as data in the original table changes, so does data in the view, as views are the way to look at part of the original table. The results of using a view are not permanently stored in the database. The data accessed through a view is actually constructed using standard T-SQL select command and can come from one to many different base tables or even other views.
What is Index?
An index is a physical structure containing pointers to the data. Indices are created in an existing table to locate rows more quickly and efficiently. It is possible to create an index on one or more columns of a table, and each index is given a name. The users cannot see the indexes; they are just used to speed up queries. Effective indexes are one of the best ways to improve performance in a database application. A table scan happens when there is no index available to help a query. In a table scan SQL Server examines every row in the table to satisfy the query results. Table scans are sometimes unavoidable, but on large tables, scans have a terrific impact on performance.
What is a Linked Server?
Linked Servers is a concept in SQL Server by which we can add other SQL Server to a Group and query both the SQL Server dbs using T-SQL Statements. With a linked server, you can create very clean, easy to follow, SQL statements that allow remote data to be retrieved, joined and combined with local data. Stored Procedure sp_addlinkedserver
, sp_addlinkedsrvlogin
will be used add new Linked Server. (Read More Here)
© Copyright 2000-2009 Pinal Dave. All Rights Reserved. SQLAuthority.com
Reference : Pinal Dave (https://blog.sqlauthority.com)
237 Comments. Leave new
hi,
I am new in SQL, I am using SQL server 2005, I have one problem, the problem is the SA user password i have to reset after every 2-3 days. I donot want to reset after every few days. Anybody can help me regarding this.
Thanks
Sunil jangid
[Edited phone number]
Simply awesome………………No words to praise you !!
Your Parents should proud of you.God bless…..
hi,
Please let me know, how to fix this problem. I am very fresher in SQL Server. because of this problem my developer is not able to connect with database. when i reset SA password. developer can easly connect with database.but i cannot reset password everytime. anybody can help me reagarding this.
Sunil Jangid
Hello Sunil,
Is the password of “sa” user expiring repeatedly?
If that is the issue then, please check the in the Properties page of “sa” login uncheck the following two checkboxes:
Enforce Password policy
Enforce Password expiration
Regards,
Pinal Dave
Thank you for SQL server 2008 Interview book i really need it and may i know how to take backup of database with Stored procedure with data also?
Backup database your_db to disk='file_path'
Sir, i have completed my BCA and i have decided to join the Dot net course. How is dot net course related to Sql server 2008 or DBA. what are the chances of getting the job if i simultaneously complete both the courses. Please help me regarding this matter. I am really confused
it is very nice and use full blog…. Thanks lots..
Hi Pinal,
I wanted to weather the INDEX of a table is removed from a table when we restore the databse.
If it is removed then how do I REINDEX it again ?
Please Help.
When you restore the database entire database get changed to restored database
Pinal
You have done a great Job. Keep your good work going.
We hardly see people like you in this self centric world.
Kudos to your great work
Warm regards,
Ramesh Krishnamurthy
I want display row to column and column to row in sql Table by sql-query.
Look for PIVOT operator in SQL Server help file
I want display row to column and column to row in sql Table by sql-query.
As Exampel:
Table:
——————————-
Name Roll
AAAA 111
BBBB 222
——————————-
I want to display in Following Format by SQL_QUERY
Result Table:
Name AAAA AAAA
Roll 111 222
————————–
Hi Pinal.
Im working as junior level DBA in a production environment.Can i have a good article regarding performance tuning of SQL server.Also i want to know the importance of Dynamic management Views in performance of the sql server.I would like to appriciate the website and you as your efforts are worth than anything for us.Thanks in advance.
Hi Pinal,
Thanks so much for the lovely article that you have posted. I am actually preparing for the MCTS in sql server 2005 and want your help and guidence. Please guide me through the process of preparation and also please tell me how will it benefit me in my current role in the company as a Dotnet developer .
Respected sir,
my self zubair khan pursuing mca from ignou and working in cie Ltd company in last 3 years.as a Asstt IT executive.i also have knowledge of sql server7.0 and 2000.i want to make my speciliazation in database.i read ur interview question and answer it enough for IT company.i have T-sql programming skill. plz advice me sir
Hi,
i want to retrieve data from a table for a particular cell value, can anyone give me the answer…i dont knw column name and row number….
Can you post some sample data with expected result?
realy ur que. and ans. is very good
Hi dave,
I am having problems with learning the concepts of normalisation. Each time I find very difficult to normalise the given fields. Can you please share your experience on this. Or can you guide to some link where the normalisation is explained in detail with examples.
Thanks & Best regards,
kannan. R
I find it easier to think about normalization in the following way than to repeat mantras like “no redundant data”.
Everything I work with are objects. Objects are serialized as records in the database tables. Object has properties. Properties are columns in the database. Object has relationships to other object. Relationships are indicated (and forced) with foreign keys in the database.
Lastly, every object must be identifiable from other objects of same type.
Take for instance object: human. Humans are identified by SSN (but don’t use SSN as primary key in the database!). SSN is one of the properties also. Other properties are like gender, name and age. Now you got already one table defined:
CREATE TABLE Human(id INT PRIMARY KEY, SSN VARCHAR(20), Gender VARCHAR(6), Name VARCHAR(100), Age INT)
GO
INSERT INTO Human SELECT 1, ‘xxxxxx-xxx’, ‘Male’, ‘Marko Parkkola’, 33
GO
Humans usually live somewhere, let’s say in a house. House has properties like address. Now there’s another table:
CREATE TABLE House(id INT PRIMARY KEY, Address VARCHAR(100))
GO
INSERT INTO House SELECT 1, ‘Tampere, Finland’
GO
INSERT INTO House SELECT 1, ‘Keitele, Finland’
GO
Now we have house but who to put our human in there? Simple, we need a relationship table that tells where human lives. But human can possess many houses and live only in one at the time? Alright, let’s put a property there which tells which kind of relationship human has to the house:
CREATE TABLE Human_House(id INT PRIMARY KEY, HumanID INT NOT NULL, HouseID INT NOT NULL, RelationshipType VARCHAR(100))
GO
ALTER TABLE Human_Resource ADD CONSTRAINT FK_HH_Human FOREIGN KEY (HumanID) REFERENCES Human(id)
GO
ALTER TABLE Human_Resource ADD CONSTRAINT FK_HH_House FOREIGN KEY (HouseID) REFERENCES House(id)
GO
INSERT INTO Human_House SELECT 1, 1, 1, ‘Occupies’
INSERT INTO Human_House SELECT 1, 1, 2, ‘Rents’
Now examples are pretty lousy. Sorry about that. But I hope this helps, at least a bit.
hi a i manish
i know much better about sql
thanks!
hi ,
i like this forum.
hi
without using sql profiler how we monitor sql server.
It depends what you want to monitor. You can use Performance Monitor (Control Panel -> Administrative Tools -> Performace Monitor) to monitor at least something.
Hello Pinal sir,
great collection , i liked …
I am php developer and using mysql database(total 11 months exp . in mumbai).
but i am not satisfly with this .
so i want to change this to only database field.
and i have confusion where to start to become dba and in which database technology (oracle, sql server , ibm db2)
so kindly help me
thanks in advance