Common MSSQL Interview Question for Developers-Part 1/2

Database is a collection of data, stored and retrieved digitally from remote or local computer system

Structured Query Language (SQL) is a programming language for accessing and manipulating Relational Database Management Systems (RDBMSs). SQL is widely used in popular RDBMSs such as SQL Server, Oracle, and MySQL

RDBMS or Relational Database Management Systems are database management systems that maintain data in the form of tables and relationships.

By default, SQL Server runs on port 1433

                  Constraints are used to specify rules concerning data in SQL. They can be applied to single or multiple columns in Tables, during creating or altering table.

• Not Null
• Check
• Default
• Index
• Unique
• Primary Key
• Foreign Key

Not Null Constructor Restrict null values in Column

Check Constraints verify all values in a field satisfy a condition.

E.g. Check (age>18)

      Default constraints assign default value to the field incase value is not provided

     Unique Constraints ensure only unique value is added to the field.

     Index Constraints provide fast retrieval of data if field id used in where condition

Primary Key Constraints provide a unique identification for the row.

Provide a referential integrity to row in another table

  View is a virtual table created based on a result of SQL query, it uses no memory space as data is generated when called and not stored anywhere.

     Materialized or Indexed views are also virtual tables created based on the result of a select query, but the data is saved on disk for easy availability. means memory is used to store data and is faster than normal view.

      Offset is used to skip some rows in the select query result 

e.g.  select * from employee order by [join date] desc offset 5 rows

--this query will bring all employees list except last five joiners

                  Fetch specifies the number of rows to be returned after offset is applied.

e.g.  select * from employee order by [join date] desc offset 5 rows fetch 10 rows only

--this query will bring only 10 employees list after excluding last five joiners

Select basicsalary from tblEmployeemaster  order by basicsalary desc offsel 4 rows fetch 1 row only;

      Over clause is a window function in SQL Server and is added to Aggregate functions in the Query, and Over function can use partition to determine condition for window

Dynamic SQL is an sql statement that is constructed and executed  at runtime,  means the statements are created at runtime as string and then executed using EXEC or sp_executesql

E.g. declare @sqlstring varchar(1000)

       Set  @sqlstring=’select * from employees’;

       Exec (@sqlstring);

     Common Table Expression is a temporary result set which you can query (insert, update or delete) , it help you to store result set from complex queries and reuse them. It is defined using WITH statement

WITH CTE_NAME(columns..)

As (

 Actual query…….

) Select* from  CTE_NAME