For SQL Server Training and Consultancy , Please visit :  https://sqlserverprofessional.wordpress.com/

SQL Server Professional offers  Consultancy and   Hands-On  Master Training in the areas of SQL Server Development and Administration . Team has Practical Production experience of more than 20 Years in SQL Server tools and  members are certified in SQL Server Development and Administration.

Email : sqlservertraining@outlook.com

Mob: +91-9036916045 ( CEO : M A Srinivas, Bangalore, India)

Corporate houses/COMPANIES can directly deal with us to have Quality Training and Save Training and  Consultancy Costs bypassing  middle agents

About 10 to 20 % of  Revenue GENERATED is donated to charitable institution as corporate social responsibility

NOMINAL TRAINING FEES FOR FRESHERS , JOB SEEKING YOUTHS

Services in Consultancy : Auditing of Servers, Best Practices Implementation, Optimize Installation and Configuration of H/W, O/S, Database Engine, Code Reviews, Performance Optimization, Trouble Shooting, Error Fixing, Debugging, Documentation , Feature Implementation,  Tips and Tricks  etc.

Services in Training :  SQL Server Training in Development and Administration (DBA).
All Trainings are hands-on , practical business oriented use cases at different  levels for beginners (100), Experience up to 5 years ( 200), More than 5 years (300)
Administration :

•  H/W , O/S, Engine Configuration
• Auditing of existing Server,
• User Authorization and Authentication (Login, Database user, Role) , Access ( Grant, Revoke, Deny),
• Optimum Placing of files (mdf,ldf), tempdb placement, Use of appropriate RAID, database file size optimization ( initial size, incremental size),
• Policy based management, auditing
• Index de-fragmentation ( reorganize,  rebuild)
• Backup and Restore, Report tools
• Central management Server, Registered Servers
• High Availability
• Clustering, Always-on,  Replication, Mirroring, ,Log Shipping.
• Performance ( Profiler, Extended Events, Performance Monitor)

Development :

• Normalization, Entities, Attributes, Data Modeling ( Logical and Physical),
• Table Creation, Constraints( Check, Unique, Default, Foreign Key) , Appropriate data types,
• Indexes (Clustered, Non Clustered, Filtered, Include, Columnar)
• T-SQL Development :  Select, Joins (Inner, Outer, Full, Cross) , GROUP BY,ORDER BY,UNION,INTERSECT,EXCEPT,CROSS APPLY,OUTER APPLY
• Predicates ( Sargable & NON Sargable), Conditional (IF .. ELSE, CASE), Looping ( While)
• Locks ( Blocking and Deadlocks), Transaction Isolation levels ,
• RANKING Functions , CDC, Change Tracking, Auditing, XML, Execution plan analysis
• Tracing and Extended Events
• Optimized Code Development delivering right code first time and all the time ,
• Memory OLTP, Columnar Index, Temporal Tables, Row level  Security, Encrypt Database, Query Cost, Query Statistics, Stretch Database
• DOs and DONT’s, Tips and Tricks SSMS configuration and optimization

Faculty Certifications

Microsoft Certified – Querying Microsoft SQL Server 2012  : 70-461

Microsoft Certified – Administering Microsoft SQL Server 2012 : 70-462

Contact: 
Email : sqlservertraining@outlook.com
Mob: +91-9036916045 ( CEO : M A Srinivas, Bangalore, India)

•  Experience in SQL Server in production environment as developer, DBA, Database   Architect , Trainer from 1996
• SQL Server  Consultant, Corporate Trainer in SQL Server Administration and Development.
•  Managed  More than 80 servers and DB Sizes of 2 TB and above
•  Conducted  more than 4 lakh Code Reviews, Re-Written Stored Procedures , Rearranged indexes with performance improvement of more than 90%
• Conducted  Server Auditing, Configuration, Optimization, Troubleshooting, Feature Implementation, Disaster Recovery,  Backup and Restore ,High Availability
• Conducted Corporate training in SQL Server ( All Versions) in Development and Administration (DBA)
• Corporate Trainings conducted in

SQL Server  2016  / 2014 / 2012 / 2008 R2 / 2008 / 2005

• Administration (DBA)
• Development
• Performance Tuning and Troubleshooting
• High Availability( Clustering, Always ON , Mirroring, Replication, Log Shipping)
• Conducted more than 250 Trainings both internal and external

 Consultancy

• Setting up Infrastructure, Configuration
• Disaster Recovery, Performance Optimization, Trouble Shooting
• Auditing, High Availability, Security, Maintenance ,Monitoring
• Development
• Documentation

  Main Clients : 
IBM, HCL, Envestnet, Oracle, XLhealth, Variant, Pelsoftlabs, Digital Harbor , First American ,NIIT, ARM, EXCELITY,RBS, Dubai and U S Clients etc.

FORMAT ( Transact-SQL)

 

Brief Description:      Returns a value formatted with the specified format and optional culture

Syntax:                       FORMAT ( value, format [, culture ] )

Availability:               All Versions of SQL Server 2012

Details:           Use the FORMAT function for locale-aware formatting of date/time and number values as strings. For general type conversions, continue to use CAST or CONVERT.

In the case of errors other than a culture that is not valid, such as a format that is not valid, FORMAT returns a null.

FORMAT relies on the presence of .the .NET Framework Common Language Runtime (CLR).

This function will not be remoted since it depends on the presence of the CLR. Remoting a function that requires the CLR would cause an error on the remote server.

Acceptable data types for the value argument are listed in the following table, together with their .NET Framework mapping information:

Category	Type	.NET type
Numeric	bigint	Int64
Numeric	int	Int32
Numeric	smallint	Int16
Numeric	tinyint	Byte
Numeric	decimal	SqlDecimal
Numeric	numeric	SqlDecimal
Numeric	float	Double
Numeric	real	Single
Numeric	smallmoney	Decimal
Numeric	money	Decimal
Date and Time	date	DateTime
Date and Time	time	TimeSpan
Date and Time	datetime	DateTime
Date and Time	smalldatetime	DateTime
Date and Time	datetime2	DateTime
Date and Time	datetimeoffset	DateTimeOffset

 

Examples:

1.   

 

declare @d datetime = getdate();

select format( @d, 'dd/MM/yyyy', 'en-US' ) as fomatteddate

 

output

---------------

fomatteddate

01/04/2012

 

  2.

declare @a int = 1234

select format(@a, '0000000000') AS foramt_a

go

 

output

-----------

foramt_a

0000001234

 

 

Pros:  

 

·         Formatting Numeric values with prefix zeros

·         Formatting Dates on different cultures

CHOOSE ( Transact-SQL)

 

Brief Description:      Returns the item at the specified index from a list of values

Syntax:                       CHOOSE ( index, val_1, val_2 [, val_n ] )

Availability:               All Versions of SQL Server 2012

Details:                       CHOOSE acts like an index into an array, where the array is composed of the arguments that follow the index argument. The index argument determines which of the following values will be returned

 

Examples:

1.        

declare @a int = 1

select choose( @a,'Jan','Feb','Mar','Apr','May','Jun','Jul','Aug','Sep','Oct','Nov','Dec') as selectedmonth

 

output

------------

selectedmonth

Jan

 

set @a = 6

select choose( @a,'Jan','Feb','Mar','Apr','May','Jun','Jul','Aug','Sep','Oct','Nov','Dec') as selectedmonth

go

 

output

------------

selectedmonth

Jun

------------------------------------------------------------------------------------

2.

 

Following settings will all return null as output for out of bound index values

 

declare @a int

select choose( @a,'Jan','Feb','Mar','Apr','May','Jun','Jul','Aug','Sep','Oct','Nov','Dec') as selectedmonth

go

 

declare @a int = 0

select choose( @a,'Jan','Feb','Mar','Apr','May','Jun','Jul','Aug','Sep','Oct','Nov','Dec') as selectedmonth

go

declare @a int = 13

select choose( @a,'Jan','Feb','Mar','Apr','May','Jun','Jul','Aug','Sep','Oct','Nov','Dec') as selectedmonth

go

 

declare @a int = -1

select choose( @a,'Jan','Feb','Mar','Apr','May','Jun','Jul','Aug','Sep','Oct','Nov','Dec') as selectedmonth

go

 

 

 

output

------------

selectedmonth

NULL

 

 

 

 

Pros:  

 

·         Short hand expression for array of values

·         Conversion from Access to SQL Server is straight forward 

 

Cons:  

 

·         Index value NULL need to be handled

·         Out of bound values return NULL  

IIF ( Transact–SQL)

Brief Description:      Returns one of two values, depending on whether the Boolean expression evaluates to true or false

Syntax:                       IIF ( boolean_expression, true_value, false_value )

Availability:               All Versions of SQL Server 2012

Details:                       IIF is a shorthand way for writing a CASE statement. It evaluates the Boolean expression passed as the first argument, and then returns either of the other two arguments based on the result of the evaluation. That is, the true_value is returned if the Boolean expression is true, and the false_value is returned if the Boolean expression is false or unknown. true_value and false_value can be of any type

Examples:

1.

declare @a int = 1, @b int = 2, @c int = 3, @d int = 4, @e int = 5, @f int = 6, @g int = 7

select iif ( (iif (@b > @a,@c,@d) ) < @e, @f, @g) as iifresult

go

 

iifresult

6

----------------------------------------------------------------------------------------

2. 

-- Assigning constant NULLs to true_vale , false_value  will result in error

declare @a int = 1, @b int = 2

select iif ( @a < @b , null,null)  as iifresult

go

 

Msg 8133, Level 16, State 1, Line 3

At least one of the result expressions in a CASE specification must be an expression other than the NULL constant.

-----------------------------------------------------------------------------------

 

3.

 

-- Assigning variables with NULLs to true_vale , false_value, output is NULL 

declare @a int = 1, @b int = 2, @c int = null, @d int = null

select @c = null, @d = null

 

select iif ( @a < @b , @c,@d ) as iifresult

go

 

iifresult

NULL

 

-----------------------------------------------------------------------------------

 

 

Pros:  

 

·         Shorthand for CASE when two conditions are involved

 

Cons:  

 

·         Assigning  NULL constant to true  or false value result in error

·         Assigning NULL values through variables to true or false value results in NULL output