What are unique identifiers in SQL Server?
The columns which are created as a data type unique identifier, contains the hexadecimal value which populates automatically. This value is used for segregating the data in rows where there is a chance of duplicate rows in data.
The Newid () is the function used for getting this value. It’s a hexadecimal value and the NewID function always returns a unique value.
What is check constraint in SQL SERVER?
Check constraint is the constraint on the column where you can restrict the value in columns as per data type or some numeric value.
Limitations of check constrains is that constraint don’t apply on null value i.e. if you are inserting the null value in column that will get inserted in the column where you have put some check constraint.
For check constraint there should be at least one row in the table on which constrain can be specified.
Delete operation doesn’t s validates check constraint.
What is default constraint?
Default constraint is the constraint use to put default value in column if you are not inserting any values in it.
Two types of default constraint exists ANSI standard default constraint and bound default constraint.
What is Null Constraint?
Null constraint doesn’t allow any null value in column. It restricts inserting null value in columns. You need to insert some value in this column. It’s useful in the cases when there are few fields which are mandatory in your application it will be a check at the database level
What is Identity Column in Table?
When you declare some column as Identity. Incremental value automatically populates in the column when ever new row inserts in the table .You can decide the incremental value as per requirement generally it‘s 1.
This Identity property of column can also disable whenever required and value can be updated manually.
Syntax to check current Identity of the table
DBCC CHECKIDENT (TABLE NAME).
Can we change identity key values for a table or reset the identity key value?
Yes we can do this by using reseed identity function in SQL Server and providing the new value.
DBCC CHECKINDENT (Table Name, reseed, new identity value).
What are the different types of data types in SQL Server?
There are different types of data types used in SQL Server
1.Int
2.Small int
3.Big int
4.Tiny int
5.Float
6.Double
7.Decimal
8.Money
9.Char
10.Varchar
11.Nvarchar
12.Nchar
13.Varbinary
14.Varbinary max
15.Small date time
16.Date time
17.Ntext
18.Numeric
19.Sql variant
20.Blob.
21.Glob
22.Unique identifier
23.Nvarchar max
What is the use of different types of integer data types?
Tiny int, SmallInt, Int and Bigint are the different types of integer data types. The difference is for size range.
What is times stamp data type is SQL Server?
Timestamp data type used for row versioning in SQL Server table. Its value automatically changes when there is any update on that particular row.
While declaring the create syntax You need not to provide any name to column just specify the data type as time stamp it will take column as timestamp by default.
You can also use row version data type in place of timestamp but for row version you have to provide column name at the time of table declaration.
What is select statement in TSQL?
It’s an elementary questions but the beginners can go through face this kind of questions i.e. fresher’s having no experience.
Select statement is the first keyword in any query. It used to retrieve the one or more columns from different tables. Basic syntax of a query select column name from table.
What is the Join in SQL Server? What are the different types of join?
Joins are used to merge the data between two tables according to the different scenario. There are different types of join. You can read out in detail in my one of the earlier posts.
What is the view in SQL server?
View is object created with the combination of different table and it contains the columns which are required. This is a de normalized representation of database. It doesn’t contain the data physically.
What is the basic different in joins in SQL Server 2000 and 2005?
In SQL Server the tales are joined by using join key word but earlier it was a symbolic joining. Check below example.
In 2005 it was like: Select A1.Name, B1.Address from A inner join B on A.EID=B.EID
In 2000 it was like: Select A1.Name, B1.Address from A, B where A.EID=B.EID
Explain Where Clause?
Where Clause is used to provide filter condition in a query i.e. to segregate the records on the basis of filter criteria.
What is common table expression (CTE)?
CTE is a new feature introduced in SQL Server 2005. CTE is a virtual view which exists at runtime. It can be used for the logic which need to be used other than the current scenario. CTE is major is used for handling the hierarchical and recursive data.
What is difference between CTE and View?
CTE is a not a physical object and View is a physical object.
What is the table variable?
Table variable is the variable used to store data in table form. It like temporary table but it scopes remains till the execution of the query only. It a variable so no index can be created on it.
Table variable declaration is just like other variables
Declare @table table (column data type, Column2 data type)
Temporary table is the just like the physical table but it exist in temp Db. and its scope remains till the transaction is open. The temp table starts with # symbol.
Temporary table syntax is just like physical table.
Create #table table (column1 data type, column2 data type)
Difference between temporary table and table variable?What is the temporary table?
It’s like physical table creates with the create table syntax while table variable is a variable creates with declare variable syntax.
What is the global temporary table?
Global temporary tables are the table which is created one transaction and can be accessed in other transaction till the earlier transaction is open.The ## is used or global temprory tale alias.
What are the updated views?
Views through which data can be updated. For doing multiple operation while updating
What is the union all clause?
Union all clause is used to merger two queries which have same no of columns in there select statement.
Select A1,A2 from table1
Union all
Select t1,t2 from table2
Result set will contain entire data from both the tables.
What is union Clause?
Union clause work same as union all clause it returns different rows in result set
Difference between union all and union clause?
Union all can return duplicate values while union clause filters duplicate rows from both the clauses.
What is stored procedure in sql server?
Stored procedure is a batch of SQL statement which is returned in order to perform a certain task.
Is nesting possible in stored procedure? If yes how many number of times?
Yes and nesting upto 32 level
What is the User defined functions?
User defined functions are the objects which used to for perform some particular task or retrieve some particular data. Function always returns some values.
What are the difference types of UDF?
Udf are of two type’s single value return function and tabular function. Tabular function returns record set while single value function returns only a single value.
What are difference between UDF and stored procedure?
Udf returns a value and sp generates record set or output parameter. SP can be used for ddl and dml statement while udf cannot be used for the same.
What is the cursor in SQL server?
Cursors are used to hold the data temporarily for processing for some logic. It stores the data from table and process one by one each record.
What are the different types of cursor in SQL Server?
When to use cursor in SQL Server?
Cursor is required when there is any operation required on data by traversing one by one.
What is a trigger in SQL Server?
Triggers are the objects which used to perform the
What are the different types of triggers in SQL Server?
After and Instead of Triggers are the two types of triggers in sql server.
What is the Ranking function in SQL Server?
Ranking functions were introduced in sql server 2005 and the function can be used for getting the rank.
Ranking functions are four types.
1.Row number
2.Rank
3.Dense Rank
4.Ntile
What is top operator in SQL Server?
Top operator is used in SQL server to get some specified no of rows from the result set.
What is table sample in SQL Server?
Table sample is used for getting the sample of rows from a result set. This sample is in the form of percentage.
Ex Select table sample (10%) from table A
What is the delete command?
Delete command is used to delete the data from the table. It deletes the data one by one.
What is truncate command?
Truncate command is used to delete the data from the table.
Difference between delete and truncate?
Data deleted with truncate command cannot be roll back one the transaction is committed. While data deletion with delete command can be roll back even if the transaction is committed. I.e. for truncate command transaction logs are not maintained while for delete command transaction logs are maintained as delete operation performs row by row.
What are dynamic queries?
Dynamic Queries are the query in which logic can be change at run time. These queries are created by using the variables.
What is distinct clause? How it works?
Distinct clause is used to filter unique records in the result set. If you are putting distinct on more than one column it will provide data for distinct row.
What is isolation level in SQL Server?
Isolation levels are used to handle transaction in SQL Server. I.e. to implement concurrency across the transactions.
What is default isolation Level for SQL Server?
Read committed is the default isolation for SQL Server.
What are indexes in SQL Server?
Indexes are used to search the data in the database in well-mannered approach. Data can be searched by user without going into entire database.
What are the different types of indexes in SQL Server?
Indexes are two types
1.Clustered index
2.Non Clustered Index.
How many columns can be used with roll up or with cube command?
10 columns
How many clustered index can be created on the table?
Only one clustered index can be created.
How many columns can be selected in a select statement?
4096 columns can be selected.
How many columns can be selected in an insert statement?
4096 columns can be inserted in an insert statement.
What level of nesting is possible for stored procedure?
32 levels on nesting is possible for SP.
What level of nesting is possible for sub Queries?
32 level of nesting.
What level of nesting is possible for Triggers?
32 level of nesting.
How many non-clustered indexes can be created on a table?
999 non clustered indexes can be created on table.
How many parameters can be provided to a stored procedure?
2100 parameters can be provided to a sp.
How many parameters can be provided to a UDF?
2100 parameters can be provided to an udf.
How many columns can be taken in update statement?
4096 columns can be updated in an update statement.
What is NO COUNT used for?
It’s used to check whether the sql statement executed successfully or not. When no count is on than this message will not return and while it off then sql server returns the message.
What is SET ANSI NULL ON used for?
It specifies the null values for sql 92 standards.it determines the null value for true or false statements.
What is arithabort used for?
It stop executing the query while there is an over flow or divide by zero error.
What is SET ROW COUNT used for?
Set row count stop processing the query after the specified no of rows. You can limit update up to a specified no.
What is SET ANSI padding?
It controls the way columns stores the value which are shorter than the defined size of the columns.
What is SET ANSI WARNING on?
When on it display the warning if there Is any null value for operations such as group by, order by max etc. and also if there is an arithmetic over flow. When off its displays no warning and also truncates the data to fit in the size of the column.
What is SET NO EXEC?
Compiles query but doesn’t executes it.
Write a query to find the nth minimum and maximum
For Minimum
Select * From table t1 Where
(n-1) = (Select Count(Distinct(t2.column)) From table t2 Where
t2.column < t1.column)
For maximum
Select * From table t1 Where
(n-1) = (Select Count(Distinct(t2.column)) From table t2 Where
t2.column > t1.column)
Write a query to get the last record of the table.
select top 1 * from table e1 order by id desc
Write a query to get the no rows of a table without using count or any other clause.
There is the view sys.partitions is the view which contains the information about the rows in the table. By providing the object id of table you can get rows for table.
Write the create syntax for the procedure.
Create Procedure PSample (p1 int ,p2int ) AS
Begin
Select * from table
End
Write the Create syntax for Views.
Create View Emap
AS
selectFirstName,MiddleName,lastname from xyz
Write one example for self-join clause.
You can chek full example on this link
What is the Limit of small date time function?
Max limit for small date time is 2079 and Min limit is 1900. More than this limit will end up in error.
What is the @@error?
@@Error stores the error details when there is any error occurs while updating the data. It can be used in procedures to get the error details.
What is the @@raise error?
@@Raiseerror to print the error message.
What is the @@row count?
Rowcount is used to check how many no of rows updated in the last transaction.
What is Date Diff function?
Datediff function is used to get difference between two dates. Difference can be in mm sec day month’s year and other date part.
What is Date Add Function?
Dateadd function is used to add dates. It can used to add days months or year in a date.
What is date part function?
Date part is used to get the part from a date time value.
What is coalesce function in SQL Server?
Coalesce is used as case statement. It checks expression and provide the value for the expression which is true at first.
Can we insert null value in primary column?
Nopes we can’t insert null values in primary column.
Can we create clustered index on null value column?
Yes we can create clustered index on null value column but it’s not recommended.
What are the different types of transaction errors?
- Dirty Ready
- Non Repeatable Reads
- Phantom reads
What are the table hint and how we can provide it on table?
Table hints are the hints which can be provided in the query to force locking or index etc. which you want to sql server to run when it executes at
What is No lock?
When No lock hint is provided on the table data can be read even table is used in other transaction. Applying no lock your queries will not acquires shared locks on table.
What is the nesting level of triggers?
A trigger nesting is possible for up to 32 levels.
What happens if a transaction is failed in nested transaction, then other outer transactions will execute or not?
If any transaction fails or rollback in the nested transaction results in roll back of all transaction.
How to enforce index on a query?
You can use index hints and can force sql server to use that index while running the query.
What is stuff function in SQL Server?
Stuff function replaces the string at specified index in a string.
STUFF(Mainstring, positionfromwherereplacementstart, numberofcharcterreplaced, stringnewtobereplaced)
What is replace function in SQL Server?
Replace function replaces the search string for every occurrences of search string in main string.
REPLACE (mainstring,stringneedtobesearched,stringneedtobereplaced)
What is SQL Server Reporting Services?
SSRS is reporting tool which comes with Microsoft SQL Server. It’s a part of BI suite which SQL Server incorporates with it.
SSRS is free with Microsoft SQL Server and used to create reports for client purpose. With reporting service reports can be created in different format such as tabular, matrix.