Sunday January 21st 2018

Categories

Insider

Archives

‘SQL, BI, IT news’ Archives

Indexed Views

Indexed Views

A normal database view is just a named SELECT statement that can be used from other SELECT statements. These views have no particular impact on performance. Beginning with SQL Server 2000, you could create one or more indexes on a view so long as the view satisfies the certain criteria. Please see the Books Online Article “Creating Indexed Views [...]

RANKING DATA (Transact-SQL)

RANKING DATA (Transact-SQL)

I would like to discuss the Rank FUNCTIONS in SQL Server introduced in SQL Server 2008. T-SQL has 4 FUNCTIONS FOR RANKING DATA - ROW_NUMBER, RANK, DENSE_RANK and NTILE. 1- ROW_NUMBER Row_Number function assigns a number from 1 to n based on a user-specified sorting order. Row_Number does not account for ties with in result set, so if you have [...]

Combining data sets UNION, EXCEPT and INTERSECT

Combining data sets UNION, EXCEPT and INTERSECT

SQL Server provides several operators that provide you with the ability to combine or compare the results from multiple Select statements. The UNION operator has been available from very first version of SQL server to provide ability to combine the result sets from multiple queries. On the other hand the Except and INTERSECT operators were [...]

Compute by Summary function

Compute by Summary function

One of the limitation of Group by clause is that the result set contains only summary data, not the original detail records. Management usually want to see original detail records, subtotals and grand totals all together. You can use the COMPUTE BY just to generate a quick prototype to confirm results, Transact SQL doesn’t really have extensive [...]

Check Constraints

Check Constraints

A Data integrity feature. A check constraints specifies a condition that is checked every time an attempt is made to modify the contents of the table to modify data such delete, insert, update. If the check condition remains True after the modification, it is allowed, otherwise disallowed. Create Table Offices (Office Integer Not Null, City [...]

SQL: EXISTS CONDITION

SQL: EXISTS CONDITION

SQL: EXISTS CONDITION This SQL tutorial explains how to use the SQL EXISTS condition with syntax and examples. DESCRIPTION The SQL EXISTS condition is used in combination with a subquery and is considered to be met, if the subquery returns at least one row. It can be used in a SELECT, INSERT, UPDATE, or DELETE statement. Read MoreRead [...]

What are Derived tables and common table expressions in sql server

What are Derived tables and common table expressions in sql server

Very good tutorial, Really clear and easy to follow explanations on Derived tables and common table expressions. This was the first of your tutorials I have seen and I am now planning to start right from the beginning with No.1. Thanks!

Improve SQL Server performance using profiler and tuning advisor

Improve SQL Server performance using profiler and tuning advisor

An Outstanding explanation to Improve SQL Server performance. This video first starts with a simple explanation of what is a SQL server profiler and performance tuning wizard. After completing the basic concepts it then demonstrates a simple example using two tables by capturing the work load and understanding the recommendations given by the [...]

The HAVING Clause in SQL

The HAVING Clause in SQL

The HAVING clause was added to SQL because the WHERE keyword could not be used with aggregate functions. In the below example we want to select all the employees who have orders greater than 10 SELECT Employees.LastName, COUNT(Orders.OrderID) AS NumberOfOrders FROM (Orders INNER JOIN Employees ON [...]

Correlated subquery

Correlated subquery

In a SQL database query, a correlated subquery (also known as a synchronized subquery) is a subquery (a query nested inside another query) that uses values from the outer query. The subquery is evaluated once for each row processed by the outer query. Here is an example for a typical correlated subquery. In this example we are finding the list of [...]

 Page 2 of 8 « 1  2  3  4  5 » ...  Last »