Difference between HAVING and WHERE Clause
1.Where Clause can be used other than Select statement also
2.Where applies to each and single row
3.In where clause the data that fetched from memory according to condition
4.Where is used before GROUP BY clause
Ex:Using Condition for the data in the memory.
1.Having is used only with the SELECT statement.
2.Having applies to summarized rows (summarized with GROUP BY)
3.In having the completed data firstly fetched and then separated according to condition.
4.HAVING clause is used to impose condition on GROUP Function and is used after GROUP BY clause in the query
Ex: when using the avg function and then filter the data like ava(Sales)>0
Having works like Where clause with out Group By Clause
Difference between Identity column and Primary Key?
Answer: Identity Column:
1.Identity column is auto incremented
2.Incremented numeric values only
3.Only one Identity column in table
4.All identity column is an primary Key
5.Values cannot be updated
1.Primary Key value will be entered by the user.
2.Can be created more than one column (composite primary key).
3.All primary key is not an identity column.
4.Can be update the value
5.Can be refer by other table as a foreign key
Difference between DELETE and TRUNCATE ?
1. DELETE is a DML Command.
2. DELETE statement is executed using a row lock, each row in the table is locked for deletion.
3. We can specify filters in where clause
4. It deletes specified data if where condition exists.
5. Delete activates a trigger because the operation are logged individually.
6. Slower than truncate because, it keeps logs.
7. Rollback is possible.
8. To use Delete you need DELETE permission on the table.
9. Delete can be used with indexed views
1. TRUNCATE is a DDL command.
2. TRUNCATE TABLE always locks the table and page but not each row.
3. Cannot use Where Condition.
4. It Removes all the data.
5. TRUNCATE TABLE cannot activate a trigger because the operation does not log individual row deletions.
6. Faster in performance wise, because it doesn't keep any logs.
7. Rollback is not possible.
8. To use Truncate on a table you need at least ALTER permission on the table.
9. Truncate cannot be used with indexed views
DELETE and TRUNCATE both can be rolled back when used with TRANSACTION.
If Transaction is done, means COMMITED, then we can not rollback TRUNCATE command, but we can still rollback DELETE command from LOG files, as DELETE write records them in Log file in case it is needed to rollback in future from LOG files.
Difference between VARCHAR and NVARCHAR?
1.Storage: 8 bit
2.Abbreviation: Variable -Length Character String
3.Accepts only English character
4.Doesn't supports other language symbols
5.Runs faster than NVARCHAR as consumes less memory
6.Use this when you develop the application for only local purpose
1.Storage: 16 bit
3.Accepts both English character and non-English symbols
4.supports other language symbols
5.Runs slower than VARCHAR as consumes less memory
6.Use this when you use your application globally
Difference between char and varchar data types in Sql Server?
1.Fixed length memory storage
2.CHAR takes up 1 byte per character
3.Use Char when the data entries in a column are expected to be the same size
Declare test Char(100);
Then "test" occupies 100 bytes first four bytes with values and rest with blank data.
1.Variable length memory storage(Changeable)
2.VARCHAR takes up 1 byte per character, + 2 bytes to hold length information
3.varchar when the data entries in a column are expected to vary considerably in size.
Declare test VarChar(100);
Then "test" occupies only 4+2=6 bytes. first four bytes for value and other two bytes for variable length information.
1.When Using the fixed length data's in column like phone number, use Char
2.When using the variable length data's in column like address use VarChar
Difference between Primary Key and Foreign Key?
1-Primary key is a set of one or more fields/columns of a table that uniquely identify a record in database table.
2-It can't accept null, duplicate values. Only one Candidate Key can be Primary Key.
3-By default, Primary key is clustered index and data in the database table is physically organized in the sequence of clustered index.
4-We can have only one Primary key in a table.
5-DEFINE- DeptID PRIMARY KEY
6-We can generated ID automatically with the help of Auto Increment field. Primary key supports Auto Increment value.
1-Foreign key is a field in the table that is primary key in another table.
2-Foreign key can accept multiple null value.
3-Foreign key do not automatically create an index, clustered or non-clustered. You can manually create an index on foreign key.
4-We can have more than one foreign key in a table.
5-DEFINE- DeptID int FOREIGN KEY REFERENCES Department(DeptID)
6-Poreign key not supports Auto Increment value.
1-Unique key uniquely identify a record in the table.
2-Unique key can accept only one null value.
3-By default, Unique key is a unique non-clustered index.
4-We can have more than one unique key in a table.
5-DEFINE- DeptID UNIQUE KEY
6-Unique key not supports Auto Increment value.
1-super key is the combination of fields by which the row is uniquely identified.
2-A Super Key identifies uniquely rows/tuples in a table/relation of a database.
3-If you add any other Column/Attribute to a Primary Key then it become a Super Key, like EmployeeID + EmployeeName is a Super Key.
4-Primary key, Unique key, Alternate key are subset of Super Keys.
1) A Alternate key is a key that can be work as a primary key. Basically it is a candidate key that currently is not primary key.
Example: In below diagram RollNo and EnrollNo becomes Alternate Keys when we define ID as Primary Key.
1) Composite Key is a combination of more than one fields/columns of a table. It can be a Candidate key, Primary key.
Minimal superkey (Candidate key):
1-Minimal super key is call candidate key, A minimal superkey is the minimum number of columns that can be used to uniquely identify a single row.
2-Yes, a table can have multiple minimal superkeys.
3-A candidate key is the most minimal subset of fields that uniquely identifies a tuple(rows).
4-There can be multiple Candidate Keys in one table. Each Candidate Key can work as Primary Key.
5-Example: In below diagram ID, RollNo and EnrollNo are Candidate Keys since all these three fields can be work as Primary Key.
Primary & candidate key:
1) Both Primary and Candidate keys can uniquely identify records in a table on database.
2) Both Primary and Candidate keys are has constraints UNIQUE and NOT NULL.
3) Primary key or Candidate keys can be either single column or combination of multiple columns in a table.
Diff Primary & candidate Key
CREATE TABLE [dbo].[parent] (
[id] [int] IDENTITY NOT NULL,
[name] [varchar](250) NOT NULL,
CONSTRAINT [PK_dbo__parent] PRIMARY KEY NONCLUSTERED ([id])
CREATE TABLE [dbo].[child] (
[id] [int] IDENTITY NOT NULL, [parent_id] [int] NULL,
[name] [varchar](250) NOT NULL,
CONSTRAINT [PK_dbo__child] PRIMARY KEY NONCLUSTERED ([id]),
CONSTRAINT [FK_dbo__child__dbo__parent] FOREIGN KEY ([parent_id]) REFERENCES [dbo].[parent]([id])
Index In Sql?
an index is a data structure (most commonly a B- tree) that stores the values for a specific column in a table. An index is created on a column of a table. So, the key points to remember are that an index consists of column values from one table, and that those values are stored in a data structure.
Index is a database object, which can be created on one or more columns (16 Max column combination for SQL Server).
Indexes allow the database application to find data fast; without reading the whole table.
An index can be created in a table to find data more quickly and efficiently.
The users cannot see the indexes, they are just used to speed up searches/queries.
The index will improve the performance of data retrieval and adds some overhead on data modification such as create, delete and modify
Indexes should not be used on small tables.
Tables that have frequent, large batch update or insert operations.
Indexes should not be used on columns that contain a high number of NULL values.
Columns that are frequently manipulated should not be indexed.
CREATE INDEX index_name ON table_name (column_name);
CREATE UNIQUE INDEX index_name ON table_name (column_name);
CREATE INDEX index_name ON table_name (column_name1, column_name2);
CREATE UNIQUE INDEX i1 ON t1 (col1 DESC, col2 ASC, col3 DESC);
-- Create a non-clustered index
CREATE INDEX fyi_links_created ON fyi_links (created);
-- display all index on table
EXEC SP_HELP DROP INDEX index_name;
ALTER TABLE table_name ADD INDEX (column1), ADD UNIQUE INDEX (Colum2);
ALTER TABLE T1 DROP INDEX B, DROP INDEX C;
DROP INDEX B ON T1; DROP INDEX C ON T1;
CREATE TABLE (
id NUMBER NOT NULL,
CONSTRAINT pk PRIMARY KEY NONCLUSTERED (id) )
Difference between Clustered and Nonclustered Indexes in SQL?
1) Clustered Index physically store & sort all rows while Nonclustered Index doesn`t (store in logical way).
2) In SQL one table can only have one Clustered Index but there is no such restriction on NonClustered Index.
3) In many relational databases Clustered Index is automatically created on primary key creation.
4) Always create Indexes on columns which is frequently used in SELECT query because there is huge performance difference between indexed and non indexed column in SQL queries.
5) Non Cluster Index is fast then Cluster index.
6) A non-clustered index is useful for columns that have some repeated values. Say for example, AccountType column of a bank database may have 10 million rows
7) A clustered index (SQL Server, MySQL/InnoDB) is a table stored in an index B-Tree structure. There is no second data structure (heap-table) for the table.
8) A non-clustered index is an index that refers to another data structure containing further table columns.
9) Accessing table data via a secondary index (index on a clustered index) is slower than a similar query on a heap-table.
What’s referential integrity?
Referential integrity is a relational database concept in which multiple tables share a relationship based on the data stored in the tables, and that relationship must remain consistent.
Difference between Stored Procedure and Function in SQL Server?
Stored Procedures are pre-compile objects which are compiled for first time and its compiled format is saved which executes (compiled code) whenever it is called. But Function is compiled and executed every time when it is called. For more about stored procedure and function refer the articles Different types of Stored Procedure and Different types of Function.
1-Function must return a value but in Stored Procedure it is optional( Procedure can return zero or n values).
2-Functions can have only input parameters for it whereas Procedures can have input/output parameters .
3-Functions can be called from Procedure whereas Procedures cannot be called from Function.
1-Procedure allows SELECT as well as DML(INSERT/UPDATE/DELETE) statement in it whereas Function allows only SELECT statement in it.
2-Procedures can not be utilized in a SELECT statement whereas Function can be embedded in a SELECT statement.
3-Stored Procedures cannot be used in the SQL statements anywhere in the WHERE/HAVING/SELECT section whereas Function can be.
4-Functions that return tables can be treated as another rowset. This can be used in JOINs with other tables.
5-Inline Function can be though of as views that take parameters and can be used in JOINs and other Rowset operations.
6-Exception can be handled by try-catch block in a Procedure whereas try-catch block cannot be used in a Function.
7-We can go for Transaction Management in Procedure whereas we can`t go in Function.