DATA Types in sql server

Integer Data Type	Bit – 0 or 1	1 byte in memory 0-false 1 true When we want to store Boolean value then use this
	Tiny int	0 to 255 1 byte in memory 1 byte
	Small int	-32,768 to 32,767 2  bytes
	int	-214,748,3648 to 214,748,3647  4 bytes
	Big int	8 bytes in memory
Float Data Type and Real Data TYPE	Float	-1.79E308 to 1.79E308 4 Bytes 4 bytes if the precision is the range of 1 to 24 8 bytes if the precision is in the range of 25 to 53
	Real	-3.4E38 to 3.4E38 4 Bytes
	Decimal Data Types	Decimal(p,s) -10 to the power 38 (-10 ^38to 10^38-1) 1 to  9 5bytes 10 to 19 9 bytes 20 to 28 13 bytes 29 to 38 17 bytes Example Decimal (7,2)->12345.67
Money or Currency Data Types	To hold the currency value it provides with the following types which take a scale of 4 Bytes of Default	Small money  4 bytes Money  8 bytes
Character Data Types	char:   fixed length char data varchar – variable length char data varchar(max) variable length char data Text- variable length char data Use of char: when we require frequently modifying of data in char column then choose char data type Whereas varchar, it will delete that row and update the row in new location	Char,varchar- 8000 bytes Varchar(max), Text- can store upto Maximum 2 GB
		Example Sname varchar(20) – abc(no blank space) Char(10)- abc
UNICODE Character DataType	Nchar-fixed length Unicode char- 8000 bytes Nvarchar-variable length Unicode char-8000 bytes Nvarchar(max)- variable length Unicode char- 2GB Ntext- variable length Unicode char- 2GB
Hierarchy Id	It is used to store the id that represents hierarchy level of the row
Geometry	Storing geometric shapes
Time Stamp	Sql server will automatically update value of that column with current system data and time indicating when the row was last modified Time stamp column can be used to find the recently modified rows in the table
Geography	Storing geographical maps
Sql variant	Any type of data it can store
Unique identifier	Storing globally unique identifier
Binary Data Types	Are used for storing images, audio, videos	Binary-fixed length binary data-8000 bytes Var binary- variable length  binary data- 2gb Image-variable length binary data- 2gb
Date and Time Data Type	Date: 1 jan 0001 to 31 dec 9999- 4 bytes Time :00:00:00  to 23:59:59 ( 6 bytes) Date Time2: 1 jan1753 to 31 dec 9999 ( 8 bytes to 10 bytes) Date Time OffSet : 1 jan 0001 to 31 dec 9999( 10 bytes)
Small Date Time	1 jan 1900 to 6 jun 2079 (4 bytes) Datetime- 1jan1753 to 31dec 9999 (8 bytes)

Grouping Function in sql server.

Grouping function:

This function is used to determine whether or not the value generated for a col by super aggregate is Null.

This function takes colname as argument and returns 1, if the value generated for that column by super aggregate is Null and otherwise return zero.

select case grouping(deptno)

when 1 then 'All Depts'

else

cast(deptno as varchar)

end as Deptno,

case grouping(job)

when 1 then 'All Jobs'

else

job

end as job, sum(Sal) as totalsalary from emp group by deptno, job with cube

Note: no typecast for job because job col is varchar.

set operators in sql server

Union:

It is used to combine the data from two sets.

Example:

display the list of all jobs available in dept 20 and 30

select job from emp where deptno=20

union

select job from emp where DEPTNO=30

with the union set operator you can use the keyword “ALL” to get the output of “UNION” including duplicates.

Example:

select job from emp where deptno=20

union all

select job from emp where DEPTNO=30

Intersect:

It will return only the values that are common in two sets.

Example:

Display list of jobs that are common in the department 20 and 30

select job from emp where deptno=20

intersect

select job from emp where DEPTNO=30

except:

It will return the values that are available in first set but not in second set.

Example:

Display the list of  jobs that are available in dept no 20 , but not in 30

select job from emp where deptno=20

except

select job from emp where DEPTNO=30

Rules to follow:

No of columns selected in all select statement on which you apply set operator must be same.

Data type of corresponding columns in all select statement on which you are applying set operators must be same.

Example:

Find the jobs that are available in dept no  20 and dept no 30 but no in dept no 10

select job from emp where deptno=20

union

select job from emp where DEPTNO=30

except

select job from emp where DEPTNO=10

Find the languages in which no software was developed.

select prof1 from PROGRAMMER

union

select prof1 from PROGRAMMER

except

select developin from SOFTWARE

UNION	JOIN
Accumulates the data	Relates the data
To dissimilar structure can’t be combined with UNION operator	To dissimilar structure can be joined.

An error occurred during local report processing. The definition of the report 'Main Report' is invalid. The report definition is not valid. Details: The report definition has an invalid target namespace 'http://schemas.microsoft.com/sqlserver/reporting/2008/01/reportdefinition' which cannot be upgraded.

When I was running a Report I was getting the following Error.

An error occurred during local report processing. 
The definition of the report 'Main Report' is invalid. 
The report definition is not valid. Details: The report definition has an invalid target namespace 'http://schemas.microsoft.com/sqlserver/reporting/2008/01/reportdefinition' which cannot be upgraded. 

Actually the report was developed in VS 2008 and now, I am trying to access the report in VS2010 then I was getting this Error.

 To solve this,

First go to web.config and modified the following lines.

Before modifying add the following reference.

As follows.

And click on Ok button.

<assemblies>

<add assembly="Microsoft.ReportViewer.WebForms, Version=10.0.0.0, Culture=neutral, PublicKeyToken=B03F5F7F11D50A3A"/>

</assemblies>

<httpHandlers>

<add verb="*"

 path="Reserved.ReportViewerWebControl.axd" type =

 "Microsoft.Reporting.WebForms.HttpHandler,

 Microsoft.ReportViewer.WebForms, Version=10.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a" />

</httpHandlers>

  <handlers>

   <add

   name="ReportViewerWebControlHandler" preCondition="integratedMode"

   verb="*" path="Reserved.ReportViewerWebControl.axd"

   type="Microsoft.Reporting.WebForms.HttpHandler,

  Microsoft.ReportViewer.WebForms, Version=10.0.0.0, Culture=neutral,

  PublicKeyToken=b03f5f7f11d50a3a" />

  </handlers>

And go to the Report page and modified the following lines of code.

<%--<%@ Register Assembly="Microsoft.ReportViewer.WebForms, Version=9.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a" Namespace="Microsoft.Reporting.WebForms" TagPrefix="rsweb" %>--%>

<%@ RegisterAssembly="Microsoft.ReportViewer.WebForms, Version=10.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a"

    Namespace="Microsoft.Reporting.WebForms"TagPrefix="rsweb"%>

Top n clause

Top n clause

It is used to retrieve top ‘n’ no of rows from the result of the select statement.

Syntax:

Select top <n>  */<collist> from <tablename>

[ Where <condition>] [ group by <collist> ] [having <conditions>]

[Order by]

Display top 2 employees among highest salaried employees

select top 2  * from emp order by sal desc

Within the top ‘n’ clause you can use the keyword percent to retrieve only top n% of rows from the result of select statement.

Display top 20% of employees among highest salaried employee

select top 20 percent  * from emp order by sal desc

NOTE:

select top 2 with ties  * from emp order by sal desc

output:

5000.00      10

3000.00      20

3000.00      20

With top n th clause you may use the keyword “with ties” to display all rows from the table that have the same value as last row retrieved by the “topn” clause in the column used by order by.