Sql Server - Collation

How to know the collation of a database?

Execute the following sql statement by changing the DataBase_Name.


SELECT DATABASEPROPERTYEX( 'DataBase_Name' , 'Collation' ) AS DatabaseCollation

How to know the collation of Sql Server?


SELECT SERVERPROPERTY('Collation') AS ServerCollation


Job Sites and Consultants Information

Job Sites

http://www.dice.com/
http://www.simplyhired.com/
http://www.careerbuilder.com/
http://www.jobdhundo.com/ (Seattle Area Job Site)
http://www.careercup.com/
http://www.monster.com/

Consultant / HR Information

iGate
mrudul.godavarthi@igate.com
Mrudul.godavarthi@mastech.com
Direct: 412-894-8001

ExcellData

Tanuta.Singh@excell.com
Neha.Jha@excell.com
Sangeeta.Halepet@excell.com

Aditi

anuradhab@aditi.com

yashodhrar@aditi.com

SolutionIQ

Alycia

Abeck@solutionsiq.com

Adaequare

Gokul

gksaride@adaequare.com

Daniel M. Lee
IT Recruitment
Rydek Professional Staffing
Tel 310-641-9800 x22
TOLL FREE: (888)222-2934 X 22
Cell: 310-892-2125
FAX: 310-641-9804
DLEE@RYDEK.COM
WWW.RYDEK.COM

Nancy Wojack
Technical Staffing Consultant
VisionIT Inc.
313.664.5661 - Direct
313.664.5650 - Main
313.664.5652 - Fax
Nwojack@visionitinc.com
http://www.visionitinc.com

Prateek Gattani
IDC Technologies Inc.
Campbell, CA
Tel: (408) 891-2184
Fax: (408) 608-6088
Email: pat@idctechnologies.com

Kelli Upton
Bradson Technology Professionals
425-456-8900 office
253-350-5491 cell
kupton@bradsontech.com
www.bradsontech.com

Nina Schindler Work: (408) 739-4900 Ext. 123 Fax: (866) 996-9977
Maxonic 1230 Midas Way Suite 220 Sunnyvale, CA 94085-4068 www.maxonic.com
nina@maxonic.com

sreelu@swinsoft.com

SQL Server -- Shortcuts

SQL Server Management Studio:

• Ctrl + Alt + G -- View Registered Servers
• Ctrl + Alt + T -- View Template Explorer -- This is helpfull when creating objects using sql script.
• Ctrl + R -- Toggle Result Pane (Message Window) -- This will help to display more space while writing Sql script.
• F8 -- To open Object Explorer

SQL SERVER 2005: Partitions

Two Type of Partitions:
1. Horizontal Partition
Saving rows into different tables based on data range.
2. Vertical Partition
Splitting the table into smaller tables (columns). Scattering the columns into multiple tables

Process to implement Horizontal Partition:

• Create partition function
• Create partition scheme
• Create the table/index

SQL Server -- Acronym

• DMX -- Data Mining Extension
• DSV -- Data Source View
• MDF -- Master Data File -- Primary data file for the database in SQL Server.
• MDX -- Multidimensional Expressions
• NDF -- -- Secondary data file for the database.
• OLAP -- Online Analytical Processing
• OLTP -- Online Transaction Processing
• RDL -- Report Definition Language
• SAC -- Surface Area Configuration -- Used to manage Sql Service / features by SQL Server Administrators
• SMDL -- Semantic Data Modeling Language
• T-SQL -- Transact SQL -- Microsoft Proprietary Language for writing SQL statements.
• XMLA --

Different ways to move database from one server to another server

There are different ways to move database from one server to another server

1. Detach/Attach
2. Backup/Restore
3. Copy database Wizard
4. Import/Export
5. Manual script

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