This article is half-done without your Comment! *** Please share your thoughts via Comment ***
What type of bitwise operators available in SQL Server?
- & (Bitwise AND)
- ~ (Bitwise NOT)
- | (Bitwise OR)
- ^ (Bitwise Exclusive OR)
What is the difference between UNION and UNION ALL?
UNION:
The UNION command is used to select related information from two tables, much like the JOIN command. However, when using the UNION command all selected columns need to be of the same data type. With UNION, only distinct values are selected.
UNION ALL:
The UNION ALL command is equal to the UNION command, except that UNION ALL selects all values.
The difference between Union and Union all is that Union all will not eliminate duplicate rows, instead it just pulls all rows from all tables fitting your query specifics and combines them into a table.
What is the use of EXCEPT Operator?
Use the EXCEPT Operator to return only rows found in the left query. It returns unique rows from the left query that aren’t in the right query’s results. This query is useful when you’re looking to find rows that are in one set but not another.
What is the use of INTERSECT Operator?
Use an intersect operator to returns rows that are in common between two tables; it returns unique rows from both the left and right query. This query is useful when you want to find results that are in common between two queries.
Can you use LEN() function to calculate the length of TEXT, NText and other Binary data type?
No, we cannot use LEN() function for TEXT, Ntext, BLOB.
We should use DATALENGTH() for calculating length of TEXT, NText, BLOB.
Is it possible to import data directly from T-SQL commands without using SQL Server Integration Services?
Yes – Six commands are available to import data directly in the T-SQL language.
- BCP
- Bulk Insert
- OpenRowSet
- OPENDATASOURCE
- OPENQUERY
- Linked Servers
T-SQL Script to find object dependency in SQL Server.
T-SQL Script to find cross database object dependency in SQL Server.
SQL Script to find Cross Database Object Dependency in SQL Server
What is the FILESTREAM in SQL Server?
FILESTREAM enables SQL Server-based applications to store unstructured data, such as documents and images, on the file system. Applications can leverage the rich streaming APIs and performance of the file system and at the same time maintain transactional consistency between the unstructured data and corresponding structured data.
FILESTREAM integrates the SQL Server Database Engine with an NTFS file system by storing varbinary(max) binary large object (BLOB) data as files on the file system. Transact-SQL statements can insert, update, query, search, and back up FILESTREAM data.
What is the difference between a derived table and a subquery?
A subquery is a SELECT statement that is nested within another statement – that’s why it’s called a subquery, because it’s like having a query within another query . Subqueries are usually used in the WHERE clause as a way to filter out certain rows returned in the result set of the outer query.
A derived table is basically a subquery, except it is always in the FROM clause of a SQL statement. The reason it is called a derived table is because it essentially functions as a table as far as the entire query is concerned.
What is the co-related sub query?
The two queries, in other words the outer and the inner query, are dependent or linked to each other, to generate the final result set.
In a SQL database query, a correlated subquery also known as a synchronized subquery that uses values from the outer query. Because the subquery is evaluated once for each row processed by the outer query, it can be inefficient.
How query executed using co-related sub query?
- First Outer query executes and submit values to the inner query.
- Then, Inner query executes by using value returned by outer-query.
- The condition applied on outer query checked.
What is the difference between a Nested Sub query and Co-related sub query?
Technical difference between Normal Sub-query and Co-related sub-query are:
Looping:
Co-related sub-query loop under main-query; whereas normal sub-query; therefore correlated Subquery executes on each iteration of main query. Whereas in case of Nested-query; Subquery executes first then outer query executes next. Hence, the maximum no. of executes are NXM for correlated subquery and N+M for subquery.
Execution:
Correlated uses feedback from outer query for execution whereas Nested Subquery provides feedback to Outerquery for execution. Hence, Correlated Subquery depends on outer query whereas Nested Sub-query does not.
Performance:
Using Co-related sub-query performance decreases, since, it performs NXM iterations instead of N+M iterations. ¨ Co-related Sub-query Execution.
What are the restrictions of SUB Query?
- A Sub-Query cannot use DISTINCT key if it includes GROUP-BY
- A Sub-Query cannot use COMPUTE and INTO clauses
- A Sub-query can use ORDER-BY if it also have TOP()
- A Sub-query generated view is cannot be Updated.
- A Sub-query cannot includes the columns of type: NTEXT, TEXT, and IMAGE.
- A Sub-query should be compared with using:
- ANY, SOME, or ALL EXISTS or NOT EXISTS
What is the cardinality in SQL?
In SQL (Structured Query Language), the term cardinality refers to the uniqueness of data values contained in a particular column (attribute) of a database table. The lower the cardinality, the more duplicated elements in a column. Thus, a column with the lowest possible cardinality would have the same value for every row. SQL databases use cardinality to help determine the optimal query plan for a given query.
Why are the selectivity and cardinality used in databases?
The selectivity basically is a measure of how much variety there is in the values of a given table column in relation to the total number of rows in a given table.
The cardinality is just part of the formula that is used to calculate the selectivity. Query optimizers use the selectivity to figure out if it is actually worth using an index to find certain rows in a table.
A general principle that is followed is that it is best to use an index when the number of rows that need to be selected is small in relation to the total number of rows. That is what the selectivity helps measure.
What is QUOTED_IDENTIFIER ON/OFF and ANSI_NULL ON/OFF?
SQL SERVER: What is QUOTED_IDENTIFIER ON/OFF and ANSI_NULL ON/OFF
How you can clean buffer or cache for stored procedure?
SQL Server: DBCC to Clean Cache and Clean Buffer for Stored Procedure
Do you know, how to create system stored procedure for make available to all Database?
SQL Server: Make a System Stored Procedure for Available to all Databases
