Get the Row Count

Get the Row Count using procedure, insted of select count (*) table Name

USE [DBName]
GO
/****** Object: StoredProcedure [dbo].[Sp_RowCount] Script Date: 04/19/2010 17:40:36 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE Proc [dbo].[Sp_RowCount]( @Tname as varchar(100)) As
Declare @SQL Varchar(500)
Set @SQL='Select Object_Name(Id) As [Table Name], Rows From sysindexes Where indid < 2 And Id=' + Str(Object_Id(@Tname)) // the case if u want only count u remove the first colum of (table name).
Exec (@SQL)

Basic Query

SQL SELECT statement has the widest variety of query options, which are used to control the way data is returned.

ORDER BY—A clause that returns the result set in a sorted order based on specified columns.
example:
SELECT * FROM Contacts ORDER BY first_name;
You are free to use ORDER BY with any select statement that might return multiple rows. You can also use it in conjunction with other clauses:

SELECT first_name, last_name FROM Contacts WHERE first_name BETWEEN ‘a’ AND ‘k’ ORDER BY last_name;

When you use DISTINCT, it applies to all requested columns. If you want a list of all the salespeople in your table and the companies they represent but not every sales entry, you can use the following statement. Note that this may return several entries from the same company, etc. DISTINCT applies to the entire requested result set.

DISTINCT—A keyword that returns only unique rows within a result set

SELECT DISTINCT company, last_name, first_name FROM Sales;

COUNT—A function that returns a numeric value which equals the number of rows matching your query

The COUNT function tells you how many rows are in a result set. As with all functions, it accepts one parameter. This basic example will tell you how many rows are in your table:
SELECT COUNT(*) FROM Sales;

You can also use it to count the number of rows in any result set.
SELECT COUNT(*) FROM Sales WHERE net_amount > 100;

AVG—A function that returns the numeric value that equals the average of the numbers in a specified column

AVG returns the average of all the fields in a column with a numeric data type. It accepts one column name as its parameter, and it will return “0” if it's used on a non-numeric column.

SELECT AVG(net_amount) FROM Sales;
SELECT AVG(net_amount) FROM Sales WHERE company LIKE ‘%ABCD Co%’;

SUM—A function that adds the numbers in a specified column
SUM works just like AVG, except it returns the sum of values in all fields in the result set.
SELECT SUM(net_amount) FROM Sales WHERE net_amount > 100;

MIN—A function that returns the lowest non-null value in a column
MIN returns the lowest, non-null value in the specified column. If the column is a numeric data type, the result will be the lowest number. If it's a string data type, it will return the value that comes first alphabetically.
SELECT MIN(net_amount) FROM Sales WHERE last_name = “Smith”;
SELECT MIN(last_name) FROM Sales;

MAX—A function that returns the largest value in a column
MAX works just like MIN, only it returns the highest non-null value. It too can be used on strings or numbers.
SELECT MAX(net_amount) FROM Sales;
SELECT MAX(company) FROM Sales WHERE net_amount > 100;
The MAX function is sometimes used on columns containing an auto-incremented key field to determine what the next entry’s key ID will be. Unless you’re running a nonpublic database, be wary of using this information to insert the next entry, in case another user beats you to the punch.

GROUP BY— Group by keyword is used to give records in same column rows in grouped manner. Group by can be used on one column name and more than one column name in SQL query

Example using the SUM function
For example, you could also use the SUM function to return the name of the department and the total sales (in the associated department). The HAVING clause will filter the results so that only departments with sales greater than $1000 will be returned.

SELECT department, SUM(sales) as "Total sales"
FROM order_details
GROUP BY department
HAVING SUM(sales) > 1000;
Example using the COUNT function

For example, you could use the COUNT function to return the name of the department and the number of employees (in the associated department) that make over $25,000 / year. The HAVING clause will filter the results so that only departments with more than 10 employees will be returned.

SELECT department, COUNT(*) as "Number of employees"
FROM employees
WHERE salary > 25000
GROUP BY department
HAVING COUNT(*) > 10;

Example using the MIN function
For example, you could also use the MIN function to return the name of each department and the minimum salary in the department. The HAVING clause will return only those departments where the starting salary is $35,000.

SELECT department, MIN(salary) as "Lowest salary"
FROM employees
GROUP BY department
HAVING MIN(salary) = 35000;

Example using the MAX function
For example, you could also use the MAX function to return the name of each department and the maximum salary in the department. The HAVING clause will return only those departments whose maximum salary is less than $50,000.

SELECT department, MAX(salary) as "Highest salary"
FROM employees
GROUP BY department
HAVING MAX(salary) < 50000;
Joins
A join in SQL is a clause that allows` merging of records from one or more than one tables in database. The records from the tables are fetched based on some values that are common to each.

SELECT * from Employees e JOIN Salary s ON e.employeeid=s.employeeid;
Inner JoinTwo tables getting join result set display which is matched records only
Explicit type
SELECT *from shooters s INNER JOIN guntypes g ON s.guntype = g.guntype ;
Implicit type
SELECT *from shooters s , guntypes g WHERE s.guntype = g.guntype ;
Cross join
Is also called Cartesian join. Result of joining each row of the table with each row of the other table
Left outer join - unmatched left table records and matched right table records.
SELECT *from shooters s LEFT OUTER JOIN guntypes g ON s.guntype = g.guntype ;
Right outer join – unmatched right table and matched left table
SELECT *from shooters s RIGHT OUTER JOIN guntypes g ON s.guntype = g.guntype ;
Full outer join merged the result fetched from left and right
SELECT *from shooters s FULL OUTER JOIN guntypes g ON s.guntype = g.guntype ;

It is a simple sql join condition which uses the equal sign as the comparison operator.
Equi joins are SQL Outer join and SQL Inner join.
SQL Non Equi Join:
A Non Equi Join is a SQL Join whose condition is established using all comparison operators except the equal (=) operator. Like >=, <=, <, >
SELECT first_name, last_name, subject
FROM student_details
WHERE subject != 'Maths

Keys, Cosntraints, index

A constraint is a property assigned to a column or the set of columns in a table that prevents certain types of inconsistent data values from being placed in the column(s). Constraints are used to enforce the data integrity. This ensures the accuracy and reliability of the data in the database. The following categories of the data integrity exist
Entity Integrity ensures that there are no duplicate rows in a table.
Domain Integrity enforces valid entries for a given column by restricting the type, the format, or the range of possible values.
Referential integrity ensures that rows cannot be deleted, which are used by other records (for example, corresponding data values between tables will be vital).
User-Defined Integrity enforces some specific business rules that do not fall into entity, domain, or referential integrity categories.

A PRIMARY KEY constraint is a unique identifier for a row within a database table. Every table should have a primary key constraint to uniquely identify each row and only one primary key constraint can be created for each table. The primary key constraints are used to enforce entity integrity

CREATE TABLE employee(
EmployeeId INT NOT NULL CONSTRAINT pk_employee PRIMARY KEY (EmployeeId)
,
LName VARCHAR(30) NOT NULL,
FName VARCHAR(30) NOT NULL,
Address VARCHAR(100) NOT NULL,
HireDate DATETIME NOT NULL,
Salary MONEY NOT NULL CONSTRAINT check_sale CHECK (salary > 0)
)
Constraints Enhancements

When you attempt to update or delete a key to which existing foreign keys point. You can control it by using the new ON DELETE and ON UPDATE clauses in the REFERENCES clause of the CREATE TABLE and ALTER TABLE statements. For example, in the previous versions of SQL Server if you wanted to do a cascade delete from the referenced table when the appropriate record in the parent table is deleted, you had to create a trigger which executed on delete of the parent table, but now you can simply specify the ON DELETE clause in the REFERENCES clause.
CREATE TABLE Books (
BookID INT NOT NULL PRIMARY KEY,
AuthorID INT NOT NULL,
BookName VARCHAR(100) NOT NULL,
Price MONEY NOT NULL
)
GO

CREATE TABLE Authors (
AuthorID INT NOT NULL PRIMARY KEY,
Name VARCHAR(100) NOT NULL
)
GO

ALTER TABLE Books
ADD CONSTRAINT fk_author
FOREIGN KEY (AuthorID)
REFERENCES Authors (AuthorID) ON DELETE CASCADE
GO

Constraints are the built-in mechanism for enforcing data integrity. Using constraints is preferred to using triggers, rules, and defaults because built-in integrity features use much less overhead and perform faster than the ones you can create. When you write your own code to realize the same actions the constraints can make you can make some errors, so the constraints are not only faster, but also are more consistent and reliable. So, you should use triggers and rules only when the constraints do not provide all the needed functionality.

Candidate Key - A Candidate Key can be any column or a combination of columns that can qualify as unique key in database. There can be multiple Candidate Keys in one table. Each Candidate Key can qualify as Primary Key.
Primary Key - A Primary Key is a column or a combination of columns that uniquely identify a record. Only one Candidate Key can be Primary Key.



Select a key that does not contain NULL
It may be possible that there are Candidate Keys that presently do not contain value (not null) but technically they can contain null. In this case, they will not qualify for Primary Key. In the following table structure, we can see that even though column [name] does not have any NULL value it does not qualify as it has the potential to contain NULL value in future
A table can have multiple Candidate Keys that are unique as single column or combined multiple columns to the table. They are all candidates for Primary Key. Candidate keys that follow all the three rules - 1) Not Null, 2) Unique Value in Table and 3) Static - are the best candidates for Primary Key. If there are multiple candidate keys that are satisfying the criteria for Primary Key

When you use the multiple-column constraint format, you can create a composite key. A composite key specifies multiple columns for a primary-key or foreign-key constraint.
The next example creates two tables. The first table has a composite key that acts as a primary key, and the second table has a composite key that acts as a foreign key.
CREATE TABLE accounts (
acc_num INTEGER,
acc_type INTEGER,
acc_descr CHAR(20),
PRIMARY KEY (acc_num, acc_type))

CREATE TABLE sub_accounts (
sub_acc INTEGER PRIMARY KEY,
ref_num INTEGER NOT NULL,
ref_type INTEGER NOT NULL,
sub_descr CHAR(20),
FOREIGN KEY (ref_num, ref_type) REFERENCES accounts
(acc_num, acc_type))
Cluster index & Non cluster index
Primary_Key column is a monotonically increasing integer and that the last three columns are VARCHAR columns. Let's also assume that there is a clustered index on the Primary_Key column, and that there are no nonclustered indexes on the table.

Now, let's assume we run the following query:

SELECT Customer_No, Customer_Name, Customer_Address
FROM Table_Name
WHERE Primary_Key = 1001

When the query optimizer analyzes this query, it will know that the Primary_Key column is the key to a clustered index. So the clustered index will (most likely) be used to locate the Primary_Key of 1001 very quickly using a clustered index lookup. In addition, since the other three columns are part of the clustered index, the values for these three columns are immediately available and can be immediately displayed after the query finishes executing. Keep in mind that all the columns in a clustered index are stored at the leaf level of the clustered index, so SQL Server, in this example, does not have to look elsewhere to find the data to be returned.

Non cluster
Now, let's take a look at the following query:

SELECT Customer_No, Customer_Name, Customer_Address
FROM Table_Name
WHERE Customer_No = 'ABC123'

In this case, when this query is analyzed by the query optimizer, there is no index on the column Customer_No. Because of this, SQL Server will perform a clustered index scan to look for the designated record to return. Since this column is not indexed or unique, every row in the table will have to be scanned until the record is found. Once it is found, the rest of the record (because it is a clustered index and each row's data is part of the leaf index), is immediately available and the data is returned.

If we need to perform the above query often, it would be to our advantage to add a nonclustered index to the Customer_No column. This way, when we run a query like the one above, instead of performing a time-consuming clustered index scan, it can use the nonclustered index on Customer_No to perform an index lookup, which is much faster than a clustered table scan. But is this all we need to do to optimize the above query? Actually, we have forgotten something. If we run the query, let's look at it again below, something else has to happen before our data is returned.

SELECT Customer_No, Customer_Name, Customer_Address
FROM Table_Name
WHERE Customer_No = 'ABC123'

When this query runs, it will use the nonclustered index on the Customer_No column to quickly identify the record using an index lookup. But unlike a clustered index, a nonclustered index only contains the data stored in the index, which is, in this case, only the Customer_No column. On the other hand, our query wants to return three columns: Customer_No, Customer_Name, and Customer_Address, not just key Customer_No. Because of this, SQL Server will now have to perform another step before it can return our data. Once it has located the correct row in the nonclustered index, then SQL Server must then look up the values of the other two columns from the clustered index, where this data is stored
Covering indexes, if used judiciously, can be used to speed up many types of commonly run queries. But covering indexes have some limitations. For example, they are limited to a maximum of 16 columns; they have a 900-character maximum width; certain datatypes cannot be included in them; and adding additional columns to an index makes the index wider, which in turn requires more disk I/O to read and write the rows, potentially hurting performance, especially if the table is subject to many INSERTs, UPDATEs, and DELETEs.

"nonkey column nonclustered indexes." This new feature is a variation of the standard covering index we have been talking about up to this point, but with some advantages over covering indexes.

Let's go back to our example query.

SELECT Customer_No, Customer_Name, Customer_Address
FROM Table_Name
WHERE Customer_No = 'ABC123'

Let's assume, as before, that there is a clustered index on Primary_Key. Now, instead of creating a covering index as described above, what we can do is create a nonclustered index on Customer_No, but instead of adding Customer_Name and Customer_Address as additional key columns to the index, we instead add Customer_Name and Customer_Address as nonkey columns to the nonclustered index.

nonkey column nonclustered index over using a covering index. The benefits include:

All data types are supported, except text, ntext, and image. So you have more datatype options than a covering index.
The maximum number of columns is 1,023, not 16 as with covering indexes.
Because the actual index is narrower, the key is more efficient and can offer better performance over a covering index where all of the columns are part of the key.
Yes, these are small differences, but lots of small differences add up and can help boost the performance of your SQL Server. In fact, you may want to review all of your currently existing covering indexes and change them to nonkey column nonclustered indexes to see if your query performance increases. Depending on the circumstances, performance could indeed increase.

Nonkey columns are created using the INCLUDE clause of the CREATE INDEX statement. For example:

CREATE INDEX IX_Table ON Table (Key_Index_Column) INCLUDE (Column1, Column2, Column3)

More space is required to store indexes with nonkey columns. Nonkey column data is stored at both the leaf level of the index and in the table itself.
Larger indexes mean fewer rows can fit on a page, potentially increasing disk I/O.
Index maintenance is increased for data modifications, potentially hurting performance if nonkey columns are large and the database experiences a high level of data modifications.

SQL Paging

Using Stored Procedure Managing Pagination
CREATE PROCEDURE SPName
@PageNum int,
@PageSize int
AS
BEGIN
SET NOCOUNT ON;
WITH OrdersRN
AS
(
SELECT ROW_NUMBER() OVER(ORDER BY createdatetime desc) AS RowNum ,NewsId,Title,createdatetime
FROM bdl_news
)

SELECT * FROM OrdersRN WHERE RowNum BETWEEN ((@PageNum - 1) * @PageSize + 1 ) AND (@PageNum * @PageSize ) ORDER BY createdatetime desc

end

Optimization

# Try to restrict the queries result set by using the WHERE clause.
This can results in good performance benefits, because SQL Server will return to client only particular rows, not all rows from the table(s). This can reduce network traffic and boost the overall performance of the query.

# Try to restrict the queries result set by returning only the particular columns from the table, not all table's columns.
This can results in good performance benefits, because SQL Server will return to client only particular columns, not all table's columns. This can reduce network traffic and boost the overall performance of the query.

# Use views and stored procedures instead of heavy-duty queries.
This can reduce network traffic, because your client will send to server only stored procedure or view name (perhaps with some parameters) instead of large heavy-duty queries text. This can be used to facilitate permission management also, because you can restrict user access to table columns they should not see.


# Try to avoid using SQL Server cursors, whenever possible.
SQL Server cursors can result in some performance degradation in comparison with select statements. Try to use correlated subquery or derived tables, if you need to perform row-by-row operations.


# If you need to return the total table's row count, you can use alternative way instead of SELECT COUNT(*) statement.
Because SELECT COUNT(*) statement make a full table scan to return the total table's row count, it can take very many time for the large table. There is another way to determine the total row count in a table. You can use sysindexes system table, in this case. There is ROWS column in the sysindexes table. This column contains the total row count for each table in your database. So, you can use the following select statement instead of SELECT COUNT(*): SELECT rows FROM sysindexes WHERE id = OBJECT_ID('table_name') AND indid < 2 So, you can improve the speed of such queries in several times.
See this article for more details:
Alternative way to get the table's row count.

# Try to use constraints instead of triggers, whenever possible.
Constraints are much more efficient than triggers and can boost performance. So, you should use constraints instead of triggers, whenever possible.


# Use table variables instead of temporary tables.
Table variables require less locking and logging resources than temporary tables, so table variables should be used whenever possible. The table variables are available in SQL Server 2000 only.


# Try to avoid the HAVING clause, whenever possible.
The HAVING clause is used to restrict the result set returned by the GROUP BY clause. When you use GROUP BY with the HAVING clause, the GROUP BY clause divides the rows into sets of grouped rows and aggregates their values, and then the HAVING clause eliminates undesired aggregated groups. In many cases, you can write your select statement so, that it will contain only WHERE and GROUP BY clauses without HAVING clause. This can improve the performance of your query.

# Try to avoid using the DISTINCT clause, whenever possible.
Because using the DISTINCT clause will result in some performance degradation, you should use this clause only when it is necessary.


# Include SET NOCOUNT ON statement into your stored procedures to stop the message indicating the number of rows affected by a T-SQL statement.
This can reduce network traffic, because your client will not receive the message indicating the number of rows affected by a T-SQL statement.


# Use the select statements with TOP keyword or the SET ROWCOUNT statement, if you need to return only the first n rows.
This can improve performance of your queries, because the smaller result set will be returned. This can also reduce the traffic between the server and the clients.

# Use the FAST number_rows table hint if you need to quickly return 'number_rows' rows.
You can quickly get the n rows and can work with them, when the query continues execution and produces its full result set.


# Try to use UNION ALL statement instead of UNION, whenever possible.
The UNION ALL statement is much faster than UNION, because UNION ALL statement does not look for duplicate rows, and UNION statement does look for duplicate rows, whether or not they exist.

# Do not use optimizer hints in your queries.
Because SQL Server query optimizer is very clever, it is very unlikely that you can optimize your query by using optimizer hints, more often, this will hurt performance.