34) Explain “CHECK Constraint” in Sql Server?
“CHECK Constraint” will be used when the column value has to be restricted within a limit. This constraint can be set in the column level.
SELECT DISTINCT column_name From table
Trigger is used for initiating any action when the operation like Insert/Update/Delete is occurred on an object.
37) Explain Common Table Expression (CTE) in Sql Server?
CTEs are used to make the query easier. Whenever there are too many joins are being used in a query then we can use CTEs to make it simple. For example
SELECT * FROM test
38) Why to use “IN” clause in Sql Server?
“IN” clause is used to specify multiple values in WHERE clause. For example
SELECT * FROM Employees WHERE City IN (‘bangalore’,’Kochin’)
39) What does man by SQL Wildcard Characters in Sql Server?
WildCard Characters are used with “LIKE” operator in Sql Server. Wildcards are used for data retrieval process from the table. Some of the wildcards are
- “-“ - This is used for substituting a single character.
- “%” - This is used for substituting zero or more characters.
- [listofchars] – Ranges of characters for matching.
40) Explain “NOT NULL Constraint” in Sql Server?
“NOT NULL Constraint” is used in a column to make sure the value in the column is not null. If this constraint has not set then by default columns will accept NULL values too.
41) Explain “@@ROWCOUNT” and “@@ERROR” in Sql Server?
- @@ROWCOUNT - Used to return the number of rows affected in the table due to last statement.
- @@ERROR – Used to return the error code which is occurred due to last SQL statement. ‘0’ means there are no errors.
42) Why to use Cursor in Sql Server?
Cursor is used in case of row traversal. This can be considered as a pointer pointing to one row at a time in the list of rows. Cursors can be used for retrieval, removal or addition of records in a table.
43) Why to use Sub Query in Sql Server and List out types of Sub Queries?
Sub Queries are queries within a query. The parent or outer query is being called as main query and the inner query is called as inner query or sub query. Different types of Sub Queries are
- Correlated - It is not an independent subquery. It is an inner query which is referred by outer query.
- Non Correlated - It is an independent subquery. It can be executed even without outer query.
44) What are user defined functions (UDFs) in Sql Server?
User Defined functions are being used to handle complex queries.
There are two types of user defined functions –
- Scalar – This type of functions are used for returning single scalar value.
- Table Valued – This type of function are used for returning a table which has list of rows. Sql supports datatype called table which is used here for returning a table.