sql server database design interview questions

Last updated: 10/5/2010 9:19 PM
category	topic	links
Database design interview question	What is your approach on designing a Database?	#DB design approaches
	Gather the requirements à create a conceptual model (use case) àCreate a logical model (ER Modeling) from this conceptual model àimplement the physical model (choose RDBMS)(data types, constraints, indexes etc...) from the logical modelà create a prototype of your application and test the model.
Database design interview question	What are the advantages and disadvantages of Normalization	#is my database normalized
	Advantages: reduce the redundancy(duplicate) ,avoid null values, avoid update anomalies(refer update anomalies) Disadvantages: too many joins required to produce reports.
Database design interview question	When will you go for Normalization or De-normalization	#Tradeoff matrix
	It depends. A tradeoff between no. of updates vs. reports will guide till what level the database should be normalized or de-normalized.
Database design interview question	What is your approach on normalizing the database to improve the performance of the database
	Refer above questions answer.
Database design interview question	Have you experienced any problems or failures in database design?
	·        Assigning too large data types, thinking that the length of the data would grow in future. E.g.:. Customer_first_name varchar (90) – in general this will not be 90, but if the designer doesn’t have any idea on the realistic values then this leads to occupying more pages for small data. ·        Not doing any normalization or improper normalization: when designing the database focusing on meeting the requirements, sometimes the designer might ignore to normalize the database and especially doing the tradeoff between No. of Insert/Update/Deletes vs. reports. Tip: validate the designing through having a standard checklist helps to make sure that the design is up to the mark. ·        Don’t keep the audit data in the same table, look at the below table where the table has the audit data itself where you can’t maintain any constraints (fk, unique). Requires maintaining the integrity at the coding level where it leads to complex SQL statements instead of simple queries. Name Age Status Date Robert 30 Updated 01-jan-1999 5 AM Robert 31 Inserted 01-jan-1999 5 AM Robert 31 Deleted 01-jan-1999 5 AM o   Resolution: create a view with a SQL statement to produce only the active data and use these views in your front-end application. Rest of all the tables will be act as audit tables. ·        Don’t design the database by assumptions, all the assumptions must be clarified with all the stakeholders before finalizing the database design. ·        Freeze the scope of the project before finalizing the database design ·        I had seen many projects that the front-end developers are mostly complaining about the database that the design is not well and good and leads to the poor performance. This is in fact true and below is the most possible scenarios in most of the projects which makes the design not up to the mark for performance. o   Not following any trade-off matrix before making any decisions on changing the database design during the every build – This is the most common problem as most of the project requires some changes and the designer/developer has to be very careful before making any change on the schema, he has to consider the impact of the change WRT time, cost, scope and has to think about how to mitigate as well.
Database design interview question	What is the difference between Primary Key and Unique Key	#heap table
	A unique key can also be a primary key and can involve in joins. Differences are: when you create a primary key by default it will create a clustered index whereas for the unique key by default it will create non clustered index. A table will contains only one primary key in a table, but can contains more than one unique key. Primary key won’t allow any null value, but unique key allows only one null.
Database design interview question	Can a foreign key allow null value?
	Yes.(also refer cardinality)
Database design interview question	Mention few best practices to design a database
	·        Select the right design tool to design the database, there are many tools available in the market and the most popular one is Erwin, but make sure that Erwin ( not sure about the current version)  itself has some problems in it and you must know about these before using the Erwin. §  One of the most challenging task when working with Erwin is during making any changes , Erwin generates the scripts which contains the drop and recreate statements which has to be analyzed first before executing directly on the development database which might make the database inconsistent if the script fails to execute at the middle. §  Creates a non-clustered index by default on the primary key, so make sure there won’t be any heap tables created by Erwin. §  When you modify the table in Erwin which has any comments defined then the script generated by Erwin will not contains any of these comments. §  Finally make sure its 100% compatible with the version of the SQL Server you are using. ·        Define the scope of your project and finalize the database design before moving to the development as any change in the schema leads to the major changes in the application development. ·        Conform the logical design with the requirements and get the sign-off ·        Tip: It’s possible to confirm the design of the database as per requirements through the prototype of the application. ·        follow a proper naming convention ·        try to define the data types as short as possible ( refer SQL Server Page 8 KB) ·        Don’t use the text data type unless its highly required as querying the data inside a text data type sometime requires to build a full text catalog ·	·
Database design interview question	What is the difference between Primary Key and Candidate Key
	Candidate key: A single column or set of columns that can uniquely identifies the row in a table. a candidate key can acts as a primary key and you can define only candidate key as a primary key. Primary Key: A key or set of keys that can uniquely identifies the row in a table. E.g.:  sometimes when you declare a surrogate key (column with identity property) as a primary key then the actual business primary key will acts as candidate key.
Database design interview question	What is cardinality
	It’s a property to specify the number of instances of an entity that can be existed in each side of the relationship. E.g.:. One-One or null, One-Many or null
Database design interview question	What is Functional Dependency?
	It’s a special relationship among columns in a table .A set of attributes X in table functionally dependent on set of attributes Y on the same table where the values in attributes Y  identifies the values X. E.g.:. Employee Address, Employee phone functionally depends on Employee Name.
Database design interview question	What is Tuple
	Row in a table
Database design interview question	What is a Schema?
	Table, columns and the relations between the tables. Note: the concept of schema in SQL Server 2005 & 2008 is different from the concept of schema in relational theory where the SQL Server 2005 & 2008 refers to the logical container of database objects.
Database design interview question	what is the difference between DBMS and RDBMS	# SQL Server and Codd Rules
	Rather than differentiating between the DBMS and RDBMS in detail, the fact is all the leading database software available in the market are DBMS. So what is DBMS: software to manage the database (collection of data) through insert/update/delete / retrieval of data through a systematic and structured way (SQL) and manages the hierarchial, network, relational, object oriented databases. To define any DBMS as RDBMS it has to satisfy all the 12 rules defined by E.F Codd. There is an assumption that if the system has any relational databases then it’s an RDBMS which is not really true. For e.g.: MS Access is not an RDBMS though the recent version maintains the relationships through primary and foreign keys.
	Is SQL Server is DBMS or RDBMS
	Yes, but not 100%. As per E.F Codd rule 0, to define any system as an RDBMS it has to satisfy all the 12 rules. Most of the leading softwares in the market are violating the Rule-9: Logical Data Independence.
Database design interview question	can you create 2 tables with the same name
	yes , in a different schemas
SQL server 2008 Database design interview question	Can you enforce the naming convention in SQL Server to have all the tables should follow as defined?
	Yes, through policy based management. Open SSMS àManagement àPolicy Management àFacets à right-click Table, and then click New Condition à type Table starts with tbl_ * In the Facet box, confirm that Table is selected. * In Expression area, in the Field box, select @Name, in the Operator box select LIKE, and in the Value type "tbl_%" * Optionally, you can type a description of the condition, by clicking on the Description tab.
Database design interview question	what are the new data types in SQL Server 2005
Database design interview question	What are the new data types in SQL Server 2008
Database design interview question	what is sparse column and its pros and cons
	As per BOL “columns reduce the space requirements for null values at the cost of more overhead to retrieve non-null values” So, when the column value is NULL for any row in the table, the values require no storage, but when it has any value then it takes few more bytes than its actual size. It’s better to go for this only when the column has lot many null values. Refer BOL for the limitations and estimated space.
Database design interview question	How will you implement a tree structure (one employee reports to another who is an employee of the organization itself) in design? What are the challenges in managing this type of tables?	refer the dev-tree struct-qries
	TBL_EMPLOYEES ID Int ( primary key , identity 1,1) emp_id char(9) , unique key emp_name varchar(30) rep_to int (foreign key references emp_id designation varchar(30) when you enter the data it looks like the below : ID emp_id emp_name rep_to designation 1 A1 bill gates chairman 2 A2 steve CEO 3 B1 craig 1 vice president 4 B2 Jeff raikes 2 president Challenges: populating data from the text files is bit challenging. If you try to insert the new data and update the existing data from the below text file into this table then you need to write at least one cursor. emp_id emp_name reports designation M1 tuchen marketing manager M2 Jim alchen M1 co-president I2 David Campbel I1 infra-manager I1 Paul Flessner 1 sr.vice president Note : above data captured for example from www.directionsonmicrosoft.com/sample/DOMIS/orgchart/sample/outlineorgchart.html www.directionsonmicrosoft.com/sample/DOMIS/orgchart/sample/orgchart.html
Database design interview question	what is the disadvantage of having a foreign key
	There is no any disadvantage in particular and in fact it supports the referential integrity which is the major advantage. But internally when there is a foreign key means there is a overhead on the SQL Server system : Before delete a master record: the engine will look at the existence of the child record in foreign key table internally. before insert or updating a primary key a child record : the engine will look into the master record in the primary key table conclusion : so it’s always better to create a clustered index on the primary key and a non-clustered index on a foreign key
	why there should be a clustered index on every primary key and non-clustered index on every foreign key
	Refer above question.
	give an example for the generalization and summarization
	what is a surrogate key and how you will implement surrogate key in SQL Server
	what are the advantages and disadvantage of having Identity Keys in SQL Server basis on what you will define a identity key
	which NF the below table “customers” is in ID(unique) Name Address telephone age 1 Albert 123, cb street,new jersey, usa 001999999 50 which NF the below table “customers” is in ID(unique) Name street apt city country 1 Albert cb street 123 new jersey USA
	what is normalization define 1-NF, 2-NF,3NF
	what is a Heap Table and when will you make heap tables
	what is collation and how will you define a collation at column level
	give an example for 1-1 relationship
	give an example for 1- many relationship
Database design interview question	Is your database is DBMS?
	this is a very basic and a funny question J Your database is not same as the database software (Oracle/SQL Server). Its incorrect to say that my database is an DBMS
	what is the difference between and when to choose what: char varchar varchar nvarchar text varchar(max)
	complete create table syntax
	performance tuning considerations
	security considerations
	Index – fill factor – pad index – pagesplit