Download Microsoft Sql Server 2008 CTP

You can download Sql Server 2008 beta from the following location

https://connect.microsoft.com/SQLServer/content/content.aspx?ContentID=5395

For more information on Sql Server 2008, visit the following link:

http://www.microsoft.com/sql/prodinfo/futureversion/default.mspx

MS SQL Server 2008 Reporting Services

1. Build reports from different datasources like Sql Server, Oracle, DB2, SAP
2. Business users can create their own reports (ad-hoc reports)
3. Reporting Services Configuration Manager to manage the report configurations.
4. Render reports to different formats (HTML, PDF, XML, Doc, Excel)
5. Integrate reports with SharePoint Server.

SQL SERVER: Index Types

They are two types of indexes
1. Clustered Index

• Only one clustered index can be created on a table
• The physical storage of data in a table depends on the Clustered Index.
• A view with unique clustered index is called Indexed View

2. Non-Clustered Index

• 249 non-clustered indexes can be created on a single table

You need "Control" or "Alter" permission on that table to create a index.

How to display "ALL" or Country name at the top

There are many scenarios where you need to display "ALL" or "Select ALL" or a particular value at the top in a drop down list. Most of the people hard code the values in the UI. You can display this value from the backend also.

For example if you want to display the country "USA" at the top and the rest of countries in sort order you can use the following statement. Assuming COUNTRY information is stored in Country table.

SELECT "USA" AS CountryName, 0 AS SortColumn
UNION
SELECT CountryName, 1 AS SortColumn FROM Country ORDER BY SortColumn, CountryName

or

SELECT CountryName FROM Country ORDER BY (CASE WHEN CountryName='USA' THEN CHAR(0) ELSE CountryName End)

You can use this to display the product names, category names.....

SQL SERVER 2008: New data types

1. DATE/TIME Data Types - SQL Server 2008 introduces new date and time data types:

• DATE – a date only type
• TIME – a time only type
• DATETIMEOFFSET – a time zone-aware date/time type
• DATETIME2 – a date/time type with larger fractional seconds and year range than the existing DATETIME typeThe new data types enable applications to have separate data and time types, while providing large data ranges or user-defined precision for time values.

How to know what are the tables\views present in SQL Server database

You can always view the tables and views using SQL Server Management Studio/ Enterprise Manager. If we want to list all the tables’ information we can use the following system level stored procedure.

• To list all the tables/views information in a database. Execute the following command.
  EXEC SP_TABLES
• To list only table names for dbo user. Execute the following command.
  EXEC SP_TABLES @Table_Type = ‘TABLE’, @Table_Owner = ‘dbo’
• You can use the other parameters like @table_name = '%' for further filter the table names

We can also use the following select statement to list all the tables information:

• From SysObjects table
  SELECT Name AS TableName FROM sysObjects WHERE xtype='u' ORDER BY TableName
• From Information_Schema.Tables
SELECT TABLE_NAME FROM Information_Schema.Tables WHERE TABLE_TYPE='BASE TABLE' ORDER BY Table_Name

Difference between Delete and Truncate operation in MS SQL Server

Delete:

• Used to remove all rows or specified rows based on where condition.
• Logs in Transaction Log while removing each row. Transaction Log is more in delete operation.
• We can Rollback the delete operation
• Raise Triggers if present.
• Delete is a DML(Data Manipulation Language) command

Truncate:

• Truncate removes all rows in a table.
• Truncate logs the erased page information in the Transaction Log. Transaction log size is small.
• Truncate resets the seed value(initializes ) for the identity column
• Truncate is faster than delete operation.
• Triggers are not raised by Truncate operation.
• Can Rollback the Truncate operation.
• We cannot use Truncate on a table with Foreign Key

How to know MS SQL Server Version

There are different ways to know the SQL Server Version.

You can use the following sql statements to know the Sql Server Version:

SELECT @@VERSION AS SQLVERSION /*Gives the complete information of sql edition and version*/

SELECT SERVERPROPERTY('ProductVersion')AS SqlVersion, SERVERPROPERTY('Edition')AS SqlEdition, SERVERPROPERTY('ServerName')AS SQLServerName

SELECT 'Microsoft SQL Server ' + CAST(SERVERPROPERTY('EDITION')AS VARCHAR(100)) + ' ' + CAST(SERVERPROPERTY('PRODUCTLEVEL')AS VARCHAR(50)) AS SqlServerVersion

You can use the other values to know more information using SERVERPROPERTY function.

You can also use the following stored procedures to know the Sql Server version Information.

EXEC xp_msver
EXEC sp_msGetVersion