Code Simplified – Viral Sarvaiya

Code Simplified – Viral Sarvaiya, Web Developer Friends, dot net Developer, Sql Server Developer

Posts Tagged ‘sql server 2005’

Difference between count(*) and count(column_name)

Posted by Viral Sarvaiya on September 13, 2010


what is count(*), count(column_name) and count(distinct column_name) ?

suppose we have employee table and having 100 records.

you all know what is count(*) in sql, this all function return same result if there is no record null in column.

select count(*) from employee
select count(emp_name) from employee
select count(distinct emp_name) from employee

that return the total employee from the employee table,

answer of this 3 query is 100.

but what happen if some records approx 20 of the emp_name is null?

now this 3 query give same answer?

no.

select count(*) from employee

return 100

select count(emp_name) from employee

return 80

select count(distinct emp_name) from employee

return 80

count(*) count the null records while count(column_name) omits the null value so it return 80(100 – 20) record.

hope you are clear with the count(*) count(column_name) and distinct column_name).

thank you.

Posted in Sql Server | Tagged: , , , , , | 1 Comment »

Common Table Expression(CTE) in Sql server 2005

Posted by Viral Sarvaiya on April 15, 2010


Introduction:

The common table expression is one of the new features in sql server 2005. It can be used instead of temp table or table variables in the stored procedures in the circumstances. Let’s see CTE with some example queries.

Background:

Most of the developers while writing the stored procedures they create the temp tables or table variables. They need some table to store the temporary results in order to manipulate the data in the other tables based on this temp result.

The temp variables will be stored on the tempdb and it needs to be deleted in the tempdb database.

The table variable is best when compare with the temp tables. Because the table variable initially will be there in the memory for the certain limit of size and if the size increase then it will be moved to the temp database. However the scope of the table variable is only up to that program. When compare with table variable the CTE is best. It just store the result set like normal view.

For more details refer following link

http://blog.crowe.co.nz/blog/archive/2007/09/06/Microsoft-SQL-Server-2005—CTE-Example-of-a-simple.aspx

http://www.c-sharpcorner.com/UploadFile/skumaar_mca/CTE200509142009094057AM/CTE2005.aspx

http://msdn.microsoft.com/en-us/library/ms190766.aspx

http://msdn.microsoft.com/en-us/library/ms186243.aspx

Posted in Sql Server | Tagged: , , , , | Leave a Comment »

about the LINQ.

Posted by Viral Sarvaiya on July 24, 2009


Introduction

This article provides an introduction to employing LINQ to SQL within a Windows Forms application. The article will address the incorporation of LINQ to SQL into a WinForms project, how to use LINQ to SQL to select, insert, update, and delete data, and how to use LINQ to SQL to execute stored procedures. Select query examples will demonstrate ordering, filtering, aggregation, returning typed lists, returning single objects and values, and how to query across entity sets (which are essentially related tables associated by foreign keys).

For more detail click here.

Posted in ASP.NET, Sql Server | Tagged: , , | Leave a Comment »

 
%d bloggers like this: