Differences between Delete and Truncate Commands

DELETE	TRUNCATE
1. It is DML command.	1. It is DDL command.
2. Deletes rows for Temporary	2. Deletes rows for permanent
3. Supports to delete rows on    conditional basis	3. No conditional deletion
4. When all rows are deleted,identity column value still    persist. Inorder toreset identity we need to use DBCC command	4. When all rows are deleted, identity col         value will be set to seed automatically.
5. It is slow in execution,since it consults log file to store  each deleted row	5. It is fast in execution since it does not consult log.

Thanks

GSV

Differences between Mysql and SQLserver

MySQL does not support Triggers, SQL Server does.
MySQL does not support User Defined Functions, SQL Server does.
MySQL does not have Cursor Support, SQL Server does.
MySQL does not support Stored Procedures, SQL Server does.
MySQL does not have FULL JOIN capabilities, SQL Server does.
MySQL has limited Import/Export capabilities, SQL Server has full support for Import/Export.
MySQL has limited Transaction Support while SQL Server offers full Transaction Support.

Thanks

GSV

Sql Server Default Databases

By default we will used to have five databases they are

Master Database:

1.The Master database is the heart of SQL Server.
2, It basically records all the system level information.
3,Every instance of SQL Server will have an independent Master database;
4. The system and user databases related information such as name and location for user and system database are captured in Master database.
5.The Master database basically consists of two physical files, namely master.mdf (data file) and mastlog.ldf (log file).
6.The database id is 1

Model Database:

1.The Model database is basically used as a template when creating databases in SQL Server.
2.The database id is 3

MSDB Database:

1.SQL Server Agent uses MSDB database to store information related to the configuration of SQL Server Agent Jobs, Job schedules, Alerts, Operators etc.
2. MSDB also stores information related to configuration of Service Broker, Log Shipping, database backups and restore information, Maintenance Plan Configuration, Configuration of Database Mail.
3. This database also stores SSIS packages created in SQL Server 2005 & 2008.
4. The database id is 4.

TempDB Database:

1.The TempDB is the only database in SQL Server which is recreated every time when SQL Server restarts.
2.It basically stores all the temporary objects such as temporary tables, global temporary tables, temporary stored procedures, cursors, table variables,Triggers.
3.The database id is 2
.
Resource Database:
1.The Resource database is a read only, hidden system database that contains all the SQL Server system objects such as sys.objects which are physically available only in the Resource database, even though they logically appear in the SYS schema of every database.
2, Resource Database does not contain any user data or any user metadata.
3. The database id is 32767.

Thanks
GSV

Difference between SqlServer 2000/2005/2008

SQL Server 2000	SQL Server 205	SQL serevr 2012
1.Query Analyser and Enterprise manager are separate.	1.Both are combined as SSMS(Sql Server management Studio).	1.Both are combined as SSMS(Sql Server management Studio).
2.No XML datatype is used.	2.XML datatype is introduced.	2.XML datatype is used.
3.We can create maximum of 65,535 databases.	3.We can create 2(pow(20))-1 databases.	3.We can create 2(pow(20))-1 databases.
4.Nil	4.Exception Handling	4.Exception Handling
5.Nil	5.Varchar(Max) data type	5.Varchar(Max) data type
6.Nil	6.DDL Triggers	6.DDL Triggers
7.Nil	7.DataBase Mirroring	7.DataBase Mirroring
8.Nil	8.RowNumber function for paging	8.RowNumber function for paging
9.Nil	9.Table fragmentation	9.Table fragmentation
10.Nil	10.Full Text Search	10.Full Text Search
11.Nil	11.Bulk Copy Update	11.Bulk Copy Update
12.Nil	12.Cant encrypt	12.Can encrypt the entire database introduced in 2008.
13.cant compress the tables and indexes.	13.Can Compress tables and indexes.(Introduced in 2005 SP2)	13.Can compress tables and indexes.
14.Datetime datatype is used for both date and time.	14.Datetime is used for both date and time.	14.Date and time are seperately used for date and time datatype,geospatial and timestamp with internal timezone is used
15.No varchar(max) or varbinary(max) is available.	15.Varchar(max) and varbinary(max) is used.	15.Varchar(max) and varbinary(max) is used.
16.No table datatype is included.	16.No table datatype is included.	16.Table datatype introduced.
17.No SSIS is included.	17.SSIS is started using.	17.SSIS avails in this version.
18.CMS is not available.	18.CMS is not available.	18.Central Management Server(CMS) is Introduced.
19.PBM is not available.	19.PBM is not available.	19.Policy based management(PBM) server is Introduced.

Thanks

GSV

String Functions in T-SQL

String functions Perform operations on a string (char or varchar) input value and return a string or numeric value.

String functions let us extract various portions of character strings, change the case of strings, concatenate and reverse strings and perform many other types of manipulations.

String manipulations are an inherent part of any programming language. In transactional systems you might have to format the string accepted on a user-interface screen; in reporting situations you might need to concatenate or compare strings.

Transact-SQL includes numerous functions as shown below that are fairly simple to use.

String Functions:

1.ASCII

Returns the ASCII code value of the leftmost character of a character expression. This function is commonly used for comparing characters without knowing whether they're in upper or lower case. 

Return type is integer.

Syntax: ASCII(Character_Expression)

Examples:

NOTE:  We can provide valid expression also to ASCII( ). i.e ascii(1+5) is valid.

2. CHAR( )

Converts an int ASCII code to a character.The integer expression range from 0 through 255. NULL is returned if the integer expression is not in this range

Syntax:               CHAR( integer_expression )

Examples:

3.LOWER( )

Returns a character expression after converting uppercase character data to lowercase.The character_expression is an expression of character or binary data. character_expression can be a constant, variable, or column. character_expression must be of a data type that is implicitly convertible to varchar. Otherwise, use CAST to explicitly convert character_expression.

Syntax:                           LOWER (character_expression)

Example:

4.UPPER()

SQL Server 2012 Course Content

SQL Server Training Course Prerequisite

• No Prior Experience is Presumed.

SQL Server Training Course Duration

• 30 Working days, daily one and half hours

SQL Server Training Course Overview(Course Content/Syllabus)

Introduction To DBMS

• File Management System And Its Drawbacks
• Database Management System (DBMS) and Data Models

·         Physical Data Models

·         Logical Data Models

·         Hierarchical Data Model (HDBMS)

·         Network Data Model (NDBMS)

·         Relational Data Model (RDBMS)

·         Object Data Model (ODBMS)

·         Object Relational Data Model (ORDBMS)

• Conceptual Data Models

·         Entity – Relationship (E-R) Model

Introduction To SQL Server 2012

• Advantages and Drawbacks Of SQL Server Compared To Oracle And DB2

·         Connecting To Server

·         Server Type

·         Server Name

·         Authentication Modes

·         Sql Server Authentication Mode

·         Windows Authentication Mode

·         Login and Password

·         Sql Server Management Studio and Tools In Management Studio

·         Object Explorer

·         Object Explorer Details

·         Query Editor

TSQL (Transact Structured Query Language)

Introduction To TSQL

• History and Features of TSQL
• Types Of TSQL Commands

·         Data Definition Language (DDL)

·         Data Manipulation Language (DML)

·         Data Query Language (DQL)

·         Data Control Language (DCL)

·         Transaction Control Language (TCL)

·         Database

·         Creating Database

·         Altering Database

·         Deleting Database

·         Constrains

·         Procedural Integrity Constraints

·         Declarative Integrity Constraints

·         Not Null, Unique, Default and Check constraints

·         Primary Key and Referential Integrity or foreign key constraints

·         Data Types In TSQL

·         Table

·         Creating Table

·         Altering Table

·         Deleting Table

Data Manipulation Language

• Insert

·         Identity

·         Creating A Table From Another Table

·         Inserting Rows From One Table To Another

·         Update

·         Computed Columns

·         Delete

·         Truncate

·         Differences Between Delete and Truncate

Data Query Language (DQL)

• Select
• Where clause
• Order By Clause
• Distinct Keyword
• Isnull() function
• Column aliases
• Predicates

·         Between … And

·         In

·         Like

·         Is Null

Built In Functions

• Scalar Functions

·         Numeric Functions

·         Character Functions

·         Conversion Functions

·         Date Functions

·         Aggregate Functions

·         Convenient Aggregate Functions

·         Statistical Aggregate Functions

·         Group By and Having Clauses

·         Super Aggregates

·         Over(partition by …) Clause

·         Ranking Functions

·         Common Table Expressions (CTE)

Top n Clause

Set Operators

• Union
• Intersect
• Except

Joins

• Inner Join

·         Equi Join

·         Natural Join

·         Non-Equi Join

·         Self Join

·         Outer Join

·         Left Outer Join

·         Right Outer Join

·         Full Outer Join

·         Cross Join

Sub Queries

• Single Row Sub Queries
• Multi Row Sub Queries

·         Any or Some

·         ALL

·         Nested Sub Queries

·         Co-Related Sub Queries

·         Exists and Not Exists

Indexes

• Clustered Index
• NonClustered Index
• Create , Alter and Drop Indexes
• Using Indexes

Security

• Login Creation

·         SQL Server Authenticated Login

·         Windows Authenticated Login

·         User Creation

·         Granting Permissions

·         Revoking Permissions

·         Roles

Views

• Purpose Of Views
• Creating , Altering and Dropping Indexes
• Simple and Complex Views
• Encryption and Schema Binding Options in creating views

Transaction Management

• Introduction
• Begin Transaction
• Commit Transaction
• Rollback Transaction
• Save Transaction
• Role Of Log File In Transaction Management
• Implicit Transactions

TSQL Programming

• Drawbacks Of TSQL that leads to TSQL Programming
• Introduction To TSQL Programming
• Control statements In TSQL Programming

·         Conditional Control Statements

·         If

·         Case

• Looping Control Statements

·         While

Cursors

• Working With Cursors
• Types Of Cursors

·         Forward_Only and Scroll Cursors

·         Static, Dynamic and Keyset Cursors

·         Local and Global Cursors

Stored Sub Programs

• Advantages Of Stored Sub Programs compared to Independent SQL Statements
• Stored Procedures

·         Creating , Altering and Dropping

·         Optional Parameters

·         Input and Output Parameters

·         Permissions on Stored Procedures

• User Defined Functions

·         Creating, Altering and Dropping

·         Types Of User Defined Functions

·         Scalar Functions

·         Table Valued Functions

·         Inline Table Valued Functions

·         Multi Statement Table Valued Functions

·         Permissions On User Defined Functions

• Triggers

·         Purpose of Triggers

·         Differences Between Stored Procedures and User Defined Functions and Triggers

·         Creating, Altering and Dropping Triggers

·         Magic Tables

·         Instead Of Triggers

• Exception Handling

·         Implementing Exception Handling

·         Adding and removing User Defined Error Messages To And From SQL Server Error Messages List

·         Raising Exceptions Manual

Backup and Restore Of Database

Attach and Detach of Database
Normalization

Thanks

GSV