What are Constraints or Define Constraints ?
Generally we use Data Types to limit the kind of Data in a Column. For example, if we declare any column with data type INT then ONLY Integer data can be inserted into the column. Constraint will help us to limit the Values we are passing into a column or a table. In simple Constraints are nothing but Rules or Conditions applied on columns or tables to restrict the data.
Different types of Constraints ?
There are THREE Types of Constraints.
1.Domain
2.Entity
3.Referential
Domain has the following constraints types -
1.Not Null
2.Check
Entity has the following constraint types -
1.Primary Key
2.Unique Key
Referential has the following constraint types -
1.Foreign Key
•What is the difference between Primary Key and Unique Key ?
Both the Primary Key(PK) and Unique Key(UK) are meant to provide Uniqueness to the Column on which they are defined. PFB the major differences between these two.
1.By default PK defines Clustered Index in the column where as UK defines Non Clustered Index.
2.PK doesn’t allow NULL Value where as UK allow ONLY ONE NULL.
3.You can have only one PK per table where as UK can be more than one per table.
4.PK can be used in Foreign Key relationships where as UK cannot be used.
•What is the difference between Delete and Truncate ?
Both Delete and Truncate commands are meant to remove rows from a table. There are many differences between these two and pfb the same.
1.Truncate is Faster where as Delete is Slow process.
2.Truncate doesn’t log where as Delete logs an entry for every record deleted in Transaction Log.
3.We can rollback the Deleted data where as Truncated data cannot be rolled back.
4.Truncate resets the Identity column where as Delete doesn’t.
5.We can have WHERE Clause for delete where as for Truncate we cannot have WHERE Clause.
6.Delete Activates TRIGGER where as TRUNCATE Cannot.
7.Truncate is a DDL statement where as Delete is DML statement.
•What are Indexes or Indices ?
An Index in SQL is similar to the Index in a book. Index of a book makes the reader to go to the desired page or topic easily and Index in SQL helps in retrieving the data faster from database. An Index is a seperate physical data structure that enables queries to pull the data fast. Indexes or Indices are used to improve the performance of a query.
•Types of Indices in SQL ?
There are TWO types of Indices in SQL server.
1.Clustered
2.Non Clustered
•How many Clustered and Non Clustered Indexes can be defined for a table ?
Clustered – 1
Non Clustered – 999
MSDN reference – Click Here
•What is Transaction in SQL Server ?
Transaction groups a set of T-Sql Statements into a single execution unit. Each transaction begins with a specific task and ends when all the tasks in the group successfully complete. If any of the tasks fails, the transaction fails. Therefore, atransaction has only two results: success or failure. Incomplete steps result in the failure of the transaction.by programmers to group together read and write operations. In Simple Either FULL or NULL i.e either all the statements executes successfully or all the execution will be rolled back.
•Types of Transactions ?
There are TWO forms of Transactions.
1.Implicit – Specifies any Single Insert,Update or Delete statement as Transaction Unit. No need to specify Explicitly.
2.Explicit – A group of T-Sql statements with the beginning and ending marked with Begin Transaction,Commit and RollBack. PFB an Example for Explicit transactions.
BEGIN TRANSACTION
Update Employee Set Emp_ID = 54321 where Emp_ID = 12345
If(@@Error <>0)
ROLLBACK
Update LEave_Details Set Emp_ID = 54321 where Emp_ID = 12345
If(@@Error <>0)
ROLLBACK
COMMIT
In the above example we are trying to update an EMPLOYEE ID from 12345 to 54321 in both the master table “Employee” and Transaction table “Leave_Details”. In this case either BOTH the tables will be updated with new EMPID or NONE.
•What is the Max size and Max number of columns for a row in a table ?
Size – 8060 Bytes
Columns – 1024
•What is Normalization and Explain different normal forms.
Database normalization is a process of data design and organization which applies to data structures based on rules that help building relational databases.
1. Organizing data to minimize redundancy.
2. 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.
1NF: Eliminate Repeating Groups
Each set of related attributes should be in separate table, and give each table a primary key. Each field contains at most one value from its attribute domain.
2NF: Eliminate Redundant Data
1. Table must be in 1NF.
2. All fields are dependent on the whole of the primary key, or a relation is in 2NF if it is in 1NF and every non-key attribute is fully dependent on each candidate key of the relation. 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
1. The table must be in 2NF.
2. Transitive dependencies must be eliminated. All attributes must rely only on the primary 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.
BCNF: Boyce‐Codd Normal Form
for every one of its non-trivial functional dependencies X → Y, X is a superkey—that is, X is either a candidate key or a superset thereof. 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.
For example, if you can have two phone numbers values and two email address values, then you should not have them in the same table.
5NF: Isolate Semantically Related Multiple Relationships
A 4NF table is said to be in the 5NF if and only if every join dependency in it is implied by the candidate keys. There may be practical constrains on information that justify separating logically related many‐to‐many relationships.
•What is Denormalization ?
For optimizing the performance of a database by adding redundant data or by grouping data is called de-normalization.
It is sometimes necessary because current DBMSs implement the relational model poorly.
In some cases, de-normalization helps cover up the inefficiencies inherent in relational database software. A relational normalized database imposes a heavy access load over physical storage of data even if it is well tuned for high performance.
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.
•Query to Pull ONLY duplicate records from table ?
There are many ways of doing the same and let me explain one here. We can acheive this by using the keywords GROUP and HAVING. The following query will extract duplicate records from a specific column of a particular table.
Select specificColumn
FROM particluarTable
GROUP BY specificColumn
HAVING COUNT(*) > 1
This will list all the records that are repeated in the column specified by “specificColumn” of a “particlarTable”.
•Types of Joins in SQL SERVER ?
There are 3 types of joins in Sql server.
1.Inner Join
2.Outer Join
3.Cross Join
Outer join again classified into 3 types.
1.Right Outer Join
2.Left Outer Join
3.Full Outer Join.
•What is Table Expressions in Sql Server ?
Table Expressions are subqueries that are used where a TABLE is Expected. There are TWO types of table Expressions.
1.Derived tables
2.Common Table Expressions.
•What is Derived Table ?
Derived tables are table expression which appears in FROM Clause of a Query. PFB an example of the same.
select * from (Select Month(date) as Month,Year(Date) as Year from table1) AS Table2
In the above query the subquery in FROM Clause “(Select Month(date) as Month,Year(Date) as Year from table1) ” is called Derived Table.
•What is CTE or Common Table Expression ?
Common table expression (CTE) is a temporary named result set that you can reference within a
SELECT, INSERT, UPDATE, or DELETE statement. You can also use a CTE in a CREATE VIEW statement, as part of the view’s SELECT query. In addition, as of SQL Server 2008, you can add a CTE to the new MERGE statement. There are TWO types of CTEs in Sql Server -
1.Recursive
2.Non Recursive
•Difference between SmallDateTime and DateTime datatypes in Sql server ?
Both the data types are meant to specify date and time but these two has slight differences and pfb the same.
1.DateTime occupies 4 Bytes of data where as SmallDateTime occupies only 2 Bytes.
2.DateTime ranges from 01/01/1753 to 12/31/9999 where as SmallDateTime ranges from 01/01/1900 to 06/06/2079.
•What is SQL_VARIANT Datatype ?
The SQL_VARIANT data type can be used to store values of various data types at the same time, such as numeric values, strings, and date values. (The only types of values that cannot be stored are TIMESTAMP values.) Each value of an SQL_VARIANT column has two parts: the data value and the information that describes the value. (This information contains all properties of the actual data type of the value, such as length, scale, and precision.)
•What is Temporary table ?
A temporary table is a database object that is temporarily stored and managed by the database system. There are two types of Temp tables.
1.Local
2.Global
•What are the differences between Local Temp table and Global Temp table ?
Before going to the differences, let’s see the similarities.
1.Both are stored in tempdb database.
2.Both will be cleared once the connection,which is used to create the table, is closed.
3.Both are meant to store data temporarily.
PFB the differences between these two.
1.Local temp table is prefixed with # where as Global temp table with ##.
2.Local temp table is valid for the current connection i.e the connection where it is created where as Global temp table is valid for all the connection.
3. Local temp table cannot be shared between multiple users where as Global temp table can be shared.
•Whar are the differences between Temp table and Table variable ?
This is very routine question in interviews. Let’s see the major differences between these two.
1.Table variables are Transaction neutral where as Temp tables are Transaction bound. For example if we declare and load data into a temp table and table variable in a transaction and if the transaction is ROLLEDBACK, still the table variable will have the data loaded where as Temp table will not be available as the transaction is rolled back.
2.Temporary Tables are real tables so you can do things like CREATE INDEXes, etc. If you have large amounts of data for which accessing by index will be faster then temporary tables are a good option.
3.Table variables don’t participate in transactions, logging or locking. This means they’re faster as they don’t require the overhead.
4.You can create a temp table using SELECT INTO, which can be quicker to write (good for ad-hoc querying) and may allow you to deal with changing datatypes over time, since you don’t need to define your temp table structure upfront.
•What is the difference between Char,Varchar and nVarchar datatypes ?
char[(n)] - Fixed-length non-Unicode character data with length of n bytes. n must be a value from 1 through 8,000. Storage size is n bytes. The SQL-92 synonym for char is character.
varchar[(n)] - Variable-length non-Unicode character data with length of n bytes. n must be a value from 1 through 8,000. Storage size is the actual length in bytes of the data entered, not n bytes. The data entered can be 0 characters in length. The SQL-92 synonyms for varchar are char varying or character varying.
nvarchar(n) - Variable-length Unicode character data of n characters. n must be a value from 1 through 4,000. Storage size, in bytes, is two times the number of characters entered. The data entered can be 0 characters in length. The SQL-92 synonyms for nvarchar are national char varying and national character varying.
•What is the difference between STUFF and REPLACE functions in Sql server ?
The Stuff function is used to replace characters in a string. This function can be used to delete a certain length of the string and replace it with a new string.
Syntax – STUFF (string_expression, start, length, replacement_characters)
Ex - SELECT STUFF(‘I am a bad boy’,8,3,’good’)
Output – “I am a good boy”
REPLACE function replaces all occurrences of the second given string expression in the first string expression with a third expression.
Syntax - REPLACE (String, StringToReplace, StringTobeReplaced)
Ex – REPLACE(“Roopesh”,”pe”,”ep”)
Output – “Rooepsh” – You can see PE is replaced with EP in the output.
•What are Magic Tables ?
Sometimes we need to know about the data which is being inserted/deleted by triggers in database. Whenever a trigger fires in response to the INSERT, DELETE, or UPDATE statement, two special tables are created. These are the inserted and the deleted tables. They are also referred to as the magic tables. These are the conceptual tables and are similar in structure to the table on which trigger is defined (the trigger table).
The inserted table contains a copy of all records that are inserted in the trigger table.
The deleted table contains all records that have been deleted from deleted from the trigger table.
Whenever any updation takes place, the trigger uses both the inserted and deleted tables.
•Explain about RANK,ROW_NUMBER and DENSE_RANK in Sql server ?
Found a very interesting explanation for the same in the url Click Here . PFB the content of the same here.
Lets take 1 simple example to understand the difference between 3.
First lets create some sample data :
– create table
CREATE TABLE Salaries
(
Names VARCHAR(1),
SalarY INT
)
GO
– insert data
INSERT INTO Salaries SELECT
‘A’,5000 UNION ALL SELECT
‘B’,5000 UNION ALL SELECT
‘C’,3000 UNION ALL SELECT
‘D’,4000 UNION ALL SELECT
‘E’,6000 UNION ALL SELECT
‘F’,10000
GO
– Test the data
SELECT Names, Salary
FROM Salaries
Now lets query the table to get the salaries of all employees with their salary in descending order.
For that I’ll write a query like this :
SELECT names
, salary
,row_number () OVER (ORDER BY salary DESC) as ROW_NUMBER
,rank () OVER (ORDER BY salary DESC) as RANK
,dense_rank () OVER (ORDER BY salary DESC) as DENSE_RANK
FROM salaries
No comments:
Post a Comment