Optimized way to get count of rows from a table

 

To get no. of rows from a table we usually use query

Select count(*) from table_name

But this query reads complete table to return no. of

Optimized way for performing this query is

Select rows from sysindexes where id= Object_Id(‘table_name’)

Technorati Tags:

Popular posts from this blog

Cross Apply Incorrect syntax near '.'

Recently I came across an issue in Sql Server 2005, while running CROSS APPLY. The error says that Incorrect syntax near '.'. I tried to figure out all syntax that I could apply but it did not resolve the issue. Actually we need to set sp_dbcmptlevel level to 90. There are good blog posts for this here and here , which can save your valuable time.
Read more

SQL Server 2005 - Showing ResultSet in a Single Row OR in a comma separated string format.

Scenario:- Suppose You have a table abc (Sal_Id int ,Emp_Id int). The Following Query is fired on the table select Emp_Id FROM abc WHERE Sal_Id=1 Query gives result with following rows 1 2 4 Your requirement is to get these values in a single row. This can be achieved by making use of COALESCE in SQL Server. The above Query can be modified as follows to fetch the rows in required format. DECLARE @EmpList varchar(100) SELECT @EmpList = COALESCE(@EmpList,'') + CAST(Emp_Id AS VARCHAR(3)) FROM abc WHERE Sal_Id=1 select @EmpList This will fetch result as 124 If you want result as a Comma Separated string, change COALESCE function as COALESCE(@EmpList,',' ''). You will get result as 1,2,4
Read more

Alert : can not set credentials for principal sa

  While editing credentials of database user, you may get this error in Sql Server 2008 management studio. It is just a checkbox that needs to be checked to resolve this issue. But this may save your few minutes finding the issue cause. Here is a great link for resolving this issue – http://weblogs.asp.net/atlaszhu/archive/2009/08/08/alert-cannot-set-a-credential-for-principal-sa-when-trying-to-modify-sa-properties-through-sql-management-studio-2008.aspx Technorati Tags: sql server , login
Read more