What is the purpose of
an Index?
An index is used when we need a bookmark for our table. They are just used to speed up searches/queries. A table can only have one Clustered index and up to 249 Non-Clustered Indexes.
Clustered Index:
A clustered index physically sorts the data in a table. The value of a clustered index is a key-value pair, where key is the index key and value is the actual value.
If you create a primary key in a table, a clustered index is created by default.
Non- Clustered Index:
A non-clustered index sorts the data logically but not physically. The value of a non-clustered index is not the data but a pointer to the data page. So, we can say a non-clustered index is dependent on the clustered index. In the absence of clustered index, it refers a physical location in the Heap for value.
What is Difference between Stored procedure and function?
• Function has a return type but stored procedure doesn't have a return type.
• Stored Procedure supports IN/OUT/IN-OUT Parameters while function supports only IN parameters.
• Stored procedure can contain all the DML (Select, update, insert, delete) statements but function can contain only select statement.
• Function can be called from a stored procedure but stored procedure cannot be executed from a function.
• For Exception Handling, the stored procedure can contain try---catch block but Function doesn't support try---catch block.
• Function can be called in a SELECT statement not a stored procedure.
What are the different types of joins you know?
The different type of joins I know are:
Inner Join, Outer Join and Self Join.
I. Inner Join/Join:
The First table and second table are matched row by row. The result set contains only the matching records from both the tables, unmatched rows are ignored. If the 2 tables have no matching records, then it returns NULL.
II. Outer Join:
Outer join is of 3 types, such as:
Left outer Join, Right outer Join, and Full outer Join.
Left outer Join:
This join returns all the rows from the left table along with the matching rows from the right table. If there are no columns matching in the right table, it returns NULL values.
Right outer Join:
This join returns all the rows from the right table along with the matching rows from the left table. If there are no columns matching in the left table, it returns NULL values.
Full outer Join:
null value when there is no It returns all the rows from both the table when the conditions are met and returns match.
III. Self-Join:
Joining the table itself called self-join. Self-join is used to retrieve the records having some relation or similarity with other records in the same table. Here we need to use aliases for the table to join 2 different column of the same table.
What is the maximum number of non-clustered index that can be created by for a table?
249
What is the difference between "Having" and "Where" clause?
An index is used when we need a bookmark for our table. They are just used to speed up searches/queries. A table can only have one Clustered index and up to 249 Non-Clustered Indexes.
Clustered Index:
A clustered index physically sorts the data in a table. The value of a clustered index is a key-value pair, where key is the index key and value is the actual value.
If you create a primary key in a table, a clustered index is created by default.
Non- Clustered Index:
A non-clustered index sorts the data logically but not physically. The value of a non-clustered index is not the data but a pointer to the data page. So, we can say a non-clustered index is dependent on the clustered index. In the absence of clustered index, it refers a physical location in the Heap for value.
What is Difference between Stored procedure and function?
• Function has a return type but stored procedure doesn't have a return type.
• Stored Procedure supports IN/OUT/IN-OUT Parameters while function supports only IN parameters.
• Stored procedure can contain all the DML (Select, update, insert, delete) statements but function can contain only select statement.
• Function can be called from a stored procedure but stored procedure cannot be executed from a function.
• For Exception Handling, the stored procedure can contain try---catch block but Function doesn't support try---catch block.
• Function can be called in a SELECT statement not a stored procedure.
What are the different types of joins you know?
The different type of joins I know are:
Inner Join, Outer Join and Self Join.
I. Inner Join/Join:
The First table and second table are matched row by row. The result set contains only the matching records from both the tables, unmatched rows are ignored. If the 2 tables have no matching records, then it returns NULL.
II. Outer Join:
Outer join is of 3 types, such as:
Left outer Join, Right outer Join, and Full outer Join.
Left outer Join:
This join returns all the rows from the left table along with the matching rows from the right table. If there are no columns matching in the right table, it returns NULL values.
Right outer Join:
This join returns all the rows from the right table along with the matching rows from the left table. If there are no columns matching in the left table, it returns NULL values.
Full outer Join:
null value when there is no It returns all the rows from both the table when the conditions are met and returns match.
III. Self-Join:
Joining the table itself called self-join. Self-join is used to retrieve the records having some relation or similarity with other records in the same table. Here we need to use aliases for the table to join 2 different column of the same table.
What is the maximum number of non-clustered index that can be created by for a table?
249
What is the difference between "Having" and "Where" clause?
• Having clause is a search condition like Where clause but is used only for an aggregate (Avg, Sum, etc.) or Group By statement.
• Having can be used only in a Select statement unlike Where clause which can be used in any DML Statement.
What is the Difference
between Primary Key and Unique Key?
I. A table can have only one primary key column but it can have more than one unique key columns. But the combination of the columns must be having a unique value.
II. Primary key cannot have a NULL value but a Unique Key column can have only one NULL value.
III. By default, primary key creates a clustered Index whereas unique key creates a non-clustered Index.
Can a foreign key reference a non-primary key?
I. A table can have only one primary key column but it can have more than one unique key columns. But the combination of the columns must be having a unique value.
II. Primary key cannot have a NULL value but a Unique Key column can have only one NULL value.
III. By default, primary key creates a clustered Index whereas unique key creates a non-clustered Index.
Can a foreign key reference a non-primary key?
Yes, a foreign key can
actually reference a key that is not the primary key of a table. But, a foreign
key must reference a unique key.
What is the Difference
between delete and truncate?
I. Delete command delete the rows from a table row by row but truncate command delete all the rows from a table at one time.
ii. TRUNCATE is much faster than DELETE.
iii. You can’t rollback in TRUNCATE but in DELETE you can rollback. TRUNCATE removes the record permanently.
iv. TRUNCATE is a DDL command whereas DELETE is a DML command.
V. In case of TRUNCATE, Trigger doesn't get fired. But in DML commands like DELETE .Trigger get fired.
vi. You can’t use conditions (WHERE clause) in TRUNCATE. But in DELETE you can write conditions using WHERE clause.
What is the Difference between Union and Union All?
The only difference between UNION and UNION ALL is the fact Union removes all the duplicate rows between 2 tables whereas Union All returns all the values from both tables.
Performance wise Union All is faster than Union as it requires some extra effort to eliminate the extra rows.
Union command is basically used to return all related rows between 2 tables.
What is different type of sub queries?
I. Delete command delete the rows from a table row by row but truncate command delete all the rows from a table at one time.
ii. TRUNCATE is much faster than DELETE.
iii. You can’t rollback in TRUNCATE but in DELETE you can rollback. TRUNCATE removes the record permanently.
iv. TRUNCATE is a DDL command whereas DELETE is a DML command.
V. In case of TRUNCATE, Trigger doesn't get fired. But in DML commands like DELETE .Trigger get fired.
vi. You can’t use conditions (WHERE clause) in TRUNCATE. But in DELETE you can write conditions using WHERE clause.
What is the Difference between Union and Union All?
The only difference between UNION and UNION ALL is the fact Union removes all the duplicate rows between 2 tables whereas Union All returns all the values from both tables.
Performance wise Union All is faster than Union as it requires some extra effort to eliminate the extra rows.
Union command is basically used to return all related rows between 2 tables.
What is different type of sub queries?
A query nested inside
another query is called a sub query. The different types of sub queries are:
I. Single row sub query: Returns zero or one row.
ii. Multiple row sub query: Returns one or more rows.
iii. Multiple column sub query: Returns one or more columns.
iv. Correlated sub queries: Reference one or more columns in the outer SQL statement. The sub query is known as a correlated sub query because the sub query is related to the outer SQL statement.
v. Nested sub queries: Sub queries are placed within another sub query.
I. Single row sub query: Returns zero or one row.
ii. Multiple row sub query: Returns one or more rows.
iii. Multiple column sub query: Returns one or more columns.
iv. Correlated sub queries: Reference one or more columns in the outer SQL statement. The sub query is known as a correlated sub query because the sub query is related to the outer SQL statement.
v. Nested sub queries: Sub queries are placed within another sub query.
What is SQL Server?
Microsoft SQL Server is a Relational Database Management System (RSBMS) produced by Microsoft. It's primary query language Transact-SQL, an implementation of the ANSI/ISO standard Structured Query Language (SQL) used by both Microsoft and Sybase.
What is the difference between VARCHAR and NVARCHAR?
VARCHAR is a variable length string of ASCII characters, which take one Byte of space. Whereas, NVARCHAR is a variable length string of UNICODE characters, which take two Bytes of space.
b) The maximum size of VARCHAR columns is 8,000 characters. Whereas, the maximum size of NVARCHAR columns is 4,000 characters.
c) NVARCHAR requires 1 byte to represent a character. Whereas, VARCHAR requires 2 bytes to represent a character.
c) NVARCHAR supports wider range of characters than VARCHAR.
How to query the database to get all the Table names?
SELECT * FROM information_schema.tables where Table_type='BASE TABLE';
Or
SELECT * FROM sysobjects WHERE type='u';
How to query the database to get all the Stored Procedure names?
SELECT * FROM sysobjects WHERE type='p';
How to query all the column names of a table?
SELECT * FROM syscolumns WHERE object_name(syscolumns.id)='Tblzone';
How to query all the column names, data types and length of a table?
SELECT syscolumns.name "Column Name", systypes.name "Type", syscolumns.length "Length" FROM syscolumns, systypes WHERE object_name(syscolumns.id)='TblStudent' AND systypes.usertype=syscolumns.usertype;
Or
SELECT * FROM INFORMATION_SCHEMA.columns where table_name='TblSupportCall'
How to select all the current databases in SqlServer?
SELECT name FROM master..sysdatabases
Or
SELECT CATALOG_NAME AS DataBaseName FROM INFORMATION_SCHEMA.SCHEMATA
Or
EXEC sp_databases
Or
EXEC sp_MSForEachDB 'SELECT ''?'' AS DatabaseName'
Or
EXEC sp_MSForEachDB 'Print ''?'''
Write and SQL query to retrieve the 2nd highest salary from the EMP table.
SELECT TOP 1 salary
FROM (
SELECT DISTINCT TOP 2 salary
FROM employee
ORDER BY salary DESC) a
ORDER BY salary
Or
SELECT MIN(mark)
FROM student
WHERE mark IN(
SELECT TOP 3 mark
FROM student
ORDER BY mark DESC)
Write a query to get the name of the employee who is getting the highest salary from an employee table?
SELECT EmpName FROM Emp
WHERE Salary =(SELECT MAX(Salary) FROM Emp)
When do you get this error message "String or Binary data would be truncated"?
This error message appears when you try to insert a string with more characters than the column can maximal accommodate.
Which TCP/IP port does SQL Server run on?
SQL Server runs on port 1433 but we can also change it for better security.
What is SQL Inner Join?
Inner Join is used to retrieve only matching data from two or more table.
Microsoft SQL Server is a Relational Database Management System (RSBMS) produced by Microsoft. It's primary query language Transact-SQL, an implementation of the ANSI/ISO standard Structured Query Language (SQL) used by both Microsoft and Sybase.
What is the difference between VARCHAR and NVARCHAR?
VARCHAR is a variable length string of ASCII characters, which take one Byte of space. Whereas, NVARCHAR is a variable length string of UNICODE characters, which take two Bytes of space.
b) The maximum size of VARCHAR columns is 8,000 characters. Whereas, the maximum size of NVARCHAR columns is 4,000 characters.
c) NVARCHAR requires 1 byte to represent a character. Whereas, VARCHAR requires 2 bytes to represent a character.
c) NVARCHAR supports wider range of characters than VARCHAR.
How to query the database to get all the Table names?
SELECT * FROM information_schema.tables where Table_type='BASE TABLE';
Or
SELECT * FROM sysobjects WHERE type='u';
How to query the database to get all the Stored Procedure names?
SELECT * FROM sysobjects WHERE type='p';
How to query all the column names of a table?
SELECT * FROM syscolumns WHERE object_name(syscolumns.id)='Tblzone';
How to query all the column names, data types and length of a table?
SELECT syscolumns.name "Column Name", systypes.name "Type", syscolumns.length "Length" FROM syscolumns, systypes WHERE object_name(syscolumns.id)='TblStudent' AND systypes.usertype=syscolumns.usertype;
Or
SELECT * FROM INFORMATION_SCHEMA.columns where table_name='TblSupportCall'
How to select all the current databases in SqlServer?
SELECT name FROM master..sysdatabases
Or
SELECT CATALOG_NAME AS DataBaseName FROM INFORMATION_SCHEMA.SCHEMATA
Or
EXEC sp_databases
Or
EXEC sp_MSForEachDB 'SELECT ''?'' AS DatabaseName'
Or
EXEC sp_MSForEachDB 'Print ''?'''
Write and SQL query to retrieve the 2nd highest salary from the EMP table.
SELECT TOP 1 salary
FROM (
SELECT DISTINCT TOP 2 salary
FROM employee
ORDER BY salary DESC) a
ORDER BY salary
Or
SELECT MIN(mark)
FROM student
WHERE mark IN(
SELECT TOP 3 mark
FROM student
ORDER BY mark DESC)
Write a query to get the name of the employee who is getting the highest salary from an employee table?
SELECT EmpName FROM Emp
WHERE Salary =(SELECT MAX(Salary) FROM Emp)
When do you get this error message "String or Binary data would be truncated"?
This error message appears when you try to insert a string with more characters than the column can maximal accommodate.
Which TCP/IP port does SQL Server run on?
SQL Server runs on port 1433 but we can also change it for better security.
What is SQL Inner Join?
Inner Join is used to retrieve only matching data from two or more table.
What is SQL Left Outer Join?
It retrieves all the records from left most table (irrespective of the condition specified) and retrieves only matching records from the right most table and assigns NULL for unmatched fields.
What is SQL Right Outer Join?
It retrieves all the records from right most table (irrespective of the condition specified) and retrieves only matching records from the left most table and assigns NULL for unmatched fields. (Opposite to LEFT OUTER JOIN).
What is SQL Full Outer Join?
It retrieves records from both the table (irrespective of the condition) and for unmatched fields it assigns NULL.
Which TCP/IP port does SQL Server run on?
SQL Server runs on port 1433 but we can also change it for better security.
From where can you change the default port?
From the Network Utility TCP/IP properties. Port number. Both on client and the server.
Can you tell me the difference between DELETE & TRUNCATE commands?
Delete command removes the rows from a table based on the condition that we provide with a WHERE clause. Truncate will actually remove all the rows from a table and there will be no data in the table after we run the truncate command.
Can we use truncate command on a table which is referenced by FOREIGN KEY?
No. We cannot use truncate command on a table with Foreign Key because of referential integrity.
What is the use of DBCC commands?
DBCC stands for database consistency checker. We use these commands to check the consistency of the databases, i.e. maintenance, validation task and status checks.
Can you give me some DBCC command options?
DBCC CHECKDB - Ensures that tables in the DB and the indexes are correctly linked and DBCC CHECKALLOC - To check that all pages in a DB are correctly allocated. DBCC SQLPERF - It gives report on current usage of transaction log in percentage. DBCC CHECKFILEGROUP - Checks all tables file group for any damage.
What command do we use to rename a DB?
sp_renamedb 'oldname' , 'newname'
Well sometimes sp_reanmedb may not work you know because if someone is using the DB it will not accept this command so what do you think you can do in such cases?
In such cases we can first bring to DB to single user using sp_dboptions and then we can rename that DB and then we can rerun the sp_dboptions command to remove the single user mode.
What is the difference between a HAVING CLAUSE and a WHERE CLAUSE?
Having Clause is basically used only with the GROUP BY function in a query. WHERE Clause is applied to each row before they are part of the GROUP BY function in a query.
What do you mean by COLLATION?
Collation is basically the sort order. There are three types of sort order Dictionary case sensitive, Dictionary - case insensitive and binary.
0 comments:
Post a Comment