One of the key challenges that the industry is facing is the unavailability of skilled human resources. Being skilled does not mean having just a stack of certifications, but it also means having an understanding about the internals of the products that you are working on – and using that knowledge to improve the efficiency & productivity at the workplace in turn resulting in better products, better consulting abilities and a happier self.

It is therefore, that after completing graduate or post-graduate school, employees often find themselves in another kind of school. This school is incorporated into the workplace, and provides a unique learning experience – one that allows you to practice things as you learn. This school provides what most organizations call “developer training”, and I am a strong supporter of this arrangement.

Developers v/s Test Engineers

There is this never ending war between developers & test engineers. Hence, let me first clarify what I mean by developer. The way I look at it, a test engineer develops test cases and scenarios and in case of automated test methodologies, they write actual code to realize these test cases. So, they are also developers in my opinion – it’s just that their work product is different.

The Benefits of Developer Trainings

Developer training has a lot of benefits – some tangible, others intangible.

• Training benefits both – employee & employer
  
• Employees who effectively use the trainings provided to them make the workplace more efficient & productive – resulting in a happier employees and efficient teams
  
• Well-trained employees rise to the occasion in times of need and deal with problems independently. Employees in good morale are good for the organization
  
• Training employees is not as costly as hiring new employees

This raises many questions that need to be dealt with. These include, but are not limited to:

• Trainings are expensive, so who pays for them?
  
• What are your duties and liabilities towards your organization for providing you the opportunity to learn?
  
• As an organization, you cannot train all your employees at the same time – so, how do you choose who gets the training in this round, and who doesn’t?
  
• Once your training has been approved, what do you need to do in order to gain the maximum benefit out of it?

Pinal Dave’s Series on Developer Trainings

Pinal Dave, a.k.a. “SQLAuthority” (B|T) tackles multiple aspects of Developer Training and answers the questions mentioned above about in the series he ran last week. Here are direct links to his posts:

1. Developer Training – Important & Significance – http://blog.sqlauthority.com/2012/05/28/developer-training-importance-and-significance-part-1/
   
2. Developer Training – Employee morals & ethics – http://blog.sqlauthority.com/2012/05/29/developer-training-employee-morals-and-ethics-part-2/
   
3. Developer Training – Difficult Questions & Alternate Perspective – http://blog.sqlauthority.com/2012/05/30/developer-training-difficult-questions-and-alternative-perspective-part-3/
   
4. Developer Training – Options for Maximum Benefit – http://blog.sqlauthority.com/2012/05/31/developer-training-various-options-for-maximum-benefit-part-4/
   
5. Developer Training – A conclusive summary – http://blog.sqlauthority.com/2012/06/01/developer-training-a-conclusive-summary-part-5/

I would recommend that you give these posts a very detailed reading, and make maximum use of the vast amounts of Developer trainings available to you. What’s more – Pinal has made some very interesting cartoons to explain the fundamentals of developer training. Enjoy them to the fullest.

Confused by looking at the various options? Here are some quick & easy tips:

It can be overwhelming to look at the various training options available to you, as a developer. So, I thought it best to share some of the methods I use to get my dose of training:

• Take out a couple of minutes from your daily schedule – simply taking out 10 minutes from the lunch break and 5 minutes from the coffee breaks results in 15 minutes of training time!
  
• Join the local user groups! This is perhaps the easiest way to expose yourself to the latest technologies for free
  
• Work with your employer to fund you for paid training opportunities
  
  
  
  • Refer the posts mentioned below from Pinal Dave to learn how to work with your manager for the necessary funding and arrangements
  
  
• Make it a point to visit at least one national or international developer conference/community meet. For developers and IT professionals who work with Microsoft products, the 3-day TechEd is an ideal event to attend
  
• Subscribe to on-line training. Companies like Pluralsight (http://www.pluralsight-training.net/microsoft/) provide on-line training at reasonable costs. You can take these trainings from home, when commuting to/from work – all you need is a laptop, an internet connection and a credit card!
  
• There is a lot to learn from the forums – make sure to regularly follow at least one forum and attempt to answer the questions being asked by those in need. You will learn a lot, and the participants will thank-you for resolving their queries – it’s for the mutual benefit of all
  
• Finally – do not forget to share whatever you have learnt! As Pinal says, always remember that : The beautiful thing about learning is that no one can take it away from you

These are just some of the tips that use. What methods do you use for getting your dose of developer training? Do leave a note as you go.

Until we meet next time,

Be courteous. Drive responsibly.

Often, I have seen developers use code that does not conform to the ANSI standards. The reasons are many, but that is not we are discussing today. What we would discuss is that these non-standard practices need to be stopped, in some cases, by eliminating the feature all-together. The result of this effort is that the code ends up in a lot of rework because the product (in this case, Microsoft SQL Server) ends up either adopting newer standards or discontinuing support of the older, non-standardized code. 

One such coding practice is the use of non-ANSI standard OUTER operators  (*= and =*, for right outer join and left outer join respectively). Such code would typically look like:

USE Northwind

GO

SELECT EmployeeTerritories.EmployeeID,

       Territories.TerritoryID,

       Territories.TerritoryDescription,

       Territories.RegionID

FROM EmployeeTerritories, Territories

WHERE EmployeeTerritories.TerritoryID =* Territories.TerritoryID 

Effectively, the operator *= indicates that all rows from the left table need to be taken and matched with those on the right-hand side table, i.e. it is similar to a LEFT OUTER JOIN. Similarly, the operator =* is the RIGHT OUTER JOIN.

Running such code on any database with a compatibility level of 90 or higher (SQL Server 2005 or higher)  will result in the following error:

Msg 4147, Level 15, State 1, Line 3
The query uses non-ANSI outer join operators (“*=” or “=*”). To run this query without modification, please set the compatibility level for current database to 80, using the SET COMPATIBILITY_LEVEL option of ALTER DATABASE. It is strongly recommended to rewrite the query using ANSI outer join operators (LEFT OUTER JOIN, RIGHT OUTER JOIN). In the future versions of SQL Server, non-ANSI join operators will not be supported even in backward-compatibility modes.

The solution to this is to use the ANSI standard, SQL-92 syntax of LEFT and RIGHT OUTER JOIN. The query above, can therefore be represented in ANSI standard code as:

USE Northwind

GO

SELECT EmployeeTerritories.EmployeeID,

       Territories.TerritoryID,

       Territories.TerritoryDescription,

       Territories.RegionID

FROM EmployeeTerritories

RIGHT OUTER JOIN Territories ON EmployeeTerritories.TerritoryID = Territories.TerritoryID  

If yours is a product that has evolved from the days when Microsoft SQL Server was in it’s infancy, then chances are that fragments of your code still uses such non-ANSI standard code. It would be a good idea to take a couple of days to revisit the code and make it ANSI compliant to avoid further rework.

Until we meet next time,

Be courteous. Drive responsibly.

In any modern day production environment, it is difficult to find an isolated instance of Microsoft SQL Server. Most enterprise class products are interfaced with different, remote systems.

In another scenario, many IT departments use pre-created images to quickly setup development and quality assurance environments. When they change the name of the server after restoring the image, the SQL Server instance name remains unchanged. In these cases, we need to rename the instance of SQL Server.

sp_addserver – Register a SQL Server instance

To register an instance of Microsoft SQL Server as a remote server to any given instance of SQL server, the system stored procedure: sp_addserver needs to be used. This system stored procedure can be used to define the name of the local or remote instance of SQL Server. Typical usage of this system stored procedure is something like the following:

--Check the instances registered with this instance of Microsoft SQL Server
--An id=0 indicates that this is a local instance
sp_helpserver
GO

--Syntax: sp_addserver 'instance name', local, duplicate_ok
--Add a remote server
sp_addserver 'WIN2K8SQL2K8'
GO

--Check the instances registered with this instance of Microsoft SQL Server
--An id=0 indicates that this is a local instance
sp_helpserver
GO

SQL Server 2012 – sp_addserver for remote servers is discontinued

Microsoft SQL Server, had a provision to add remote servers until SQL Server 2008 R2. From Microsoft SQL Server 2012, this functionality is now deprecated, and we now need to add "linked” servers instead. If a user does attempt to use the sp_addserver syntax to add a remote server, system will result in the following error:

Msg 15663, Level 16, State 1, Procedure sp_MSaddserver_internal, Line 60

Feature "sp_addserver" is no longer supported. Replace remote servers by using linked servers.

The solution is to use the sp_addlinkedserver system stored procedure, using which the script would require changes as under:

--Check the instances registered with this instance of Microsoft SQL Server
--An id=0 indicates that this is a local instance
sp_helpserver
GO

--Syntax: sp_addlinkedserver (http://msdn.microsoft.com/en-us/library/ms190479.aspx)
--Add a remote server
sp_addlinkedserver 'WIN2K8SQL2K8','SQL Server'
GO

--Check the instances registered with this instance of Microsoft SQL Server
--An id=0 indicates that this is a local instance
sp_helpserver
GO

Please note that after one uses sp_addserver to rename a local instance of Microsoft SQL Server, a service restart is required for the changes to take effect.

References:

• sp_addserver
• sp_addlinkedserver
• Rename a Computer that Hosts a Stand-Alone Instance of SQL Server

Do leave your feedback before you go. Do you use or have you ever encountered a situation where you used one of these system stored procedures? If you are an IT guy, do you ensure that your deployment strategies rename the instances of SQL Server underneath whenever you rename the host computer? Please let us know.

Until we meet next time,

Be courteous. Drive responsibly.

Every version of SQL Server comes with a lot of optimizations to the database engine, and these changes require changes to the T-SQL syntax. New statements are introduced or modified and existing ones are dropped. Often table and query hints are optimized and modified in a similar fashion. These table and query hints are often used (in many cases, indiscriminately) to “improve the performance” of Microsoft SQL Server.

Let me start outright by saying that till date, I have never encountered the need to use the hint that we are about to discuss today.

Table v/s Query hints

Before going any further, I thought it best to revisit the simple method of identifying which is a table hint and which one is a query hint. For example, take the query below:

~~~–Query for demonstration purposes only!
USE AdventureWorks2012
GO
SELECT *
FROM HumanResources.Employee WITH (NOLOCK) — WITH (NOLOCK) is the table hint
WHERE OrganizationLevel = 3
OPTION (MAXDOP 1) — OPTION (MAXDOP 1) is the query hint~~~

The WITH (NOLOCK) hint is applied to the table/CTE/sub-query, i.e. a data source. Hence the name – table hint.

The OPTION (MAXDOP 1) is applied to the entire query, and hence the name – query hint.

[Edit]: You may also find the following post from Pinal Dave, a.k.a. “SQLAuthority” (B|T), useful in understanding Query Hints: http://blog.sqlauthority.com/2012/05/09/sql-server-quiz-and-video-introduction-to-basics-of-a-query-hint/

FASTFIRSTROW

According to SQL Server Books On Line, the FASTFIRSTROW is a table hint that helps the developer control the behaviour of the optimizer such that the optimizer,

• Optimizes for overall minimum cost (minimum processing time/resources, etc), OR
  
• Optimizes such that the time taken for the first row to be returned is minimized

The FASTFIRSTROW tells the optimizer that it is important to have the first row returned as fast as possible, allowing the application to return results to the users faster. You can see the difference by comparing the actual execution plans produced by the queries below:

~~~–Query for demonstration purposes only!
USE AdventureWorks2008
GO
–Without the table hint
SELECT *
FROM HumanResources.Employee
GO
–With the table hint
SELECT *
FROM HumanResources.Employee WITH (FASTFIRSTROW)
GO~~~

OPTION (FAST n)

Running the above query in SQL Server 2012 results in the following error:

Msg 321, Level 15, State 1, Line 3
“FASTFIRSTROW” is not a recognized table hints option. If it is intended as a parameter to a table-valued function or to the CHANGETABLE function, ensure that your database compatibility mode is set to 90.

The message says it all – FASTFIRSTROW is no longer available, and if you would like to use it, the database compatibility level needs to be set to 90 (i.e.  SQL Server 2005).

The replacement available is not a table hint, but a new query hint – FAST number_rows. This query hint specifies that the query is optimized for fast retrieval of the first number_rows. This is a nonnegative integer. After the first number_rows are returned, the query continues execution and produces its full result set. SQL Server 2012 has given more flexibility to the developer/administrator because the table hint only optimized for the first row, whereas the query hint allows you to specify a custom number of first rows to optimize for.

As a replacement of FASTFIRSTROW, you can use OPTION (FAST 1).

~~~–Query for demonstration purposes only!
USE AdventureWorks2012
GO
–Without the table hint
SELECT *
FROM HumanResources.Employee
GO
–With the table hint
SELECT *
FROM HumanResources.Employee
OPTION (FAST 1);
GO~~~

References:

• Table hints supported by SQL Server 2008 R2: http://msdn.microsoft.com/en-us/library/ms187373(v=sql.105).aspx
  
• Query Hints: http://msdn.microsoft.com/en-us/library/ms181714.aspx
  
• As I was learning about this table hint, I came across the following post, which I thought might make interesting reading: http://sqlblog.com/blogs/kalen_delaney/archive/2008/12/02/using-a-nonclustered-index-to-avoid-a-sort.aspx


• http://blog.sqlauthority.com/2012/05/09/sql-server-quiz-and-video-introduction-to-basics-of-a-query-hint/

Until we meet next time,

Be courteous. Drive responsibly.

Every version of SQL Server comes with a lot of optimizations to the database engine, and these changes require changes to the T-SQL syntax. New statements are introduced or modified and existing ones are dropped. Often table and query hints are optimized and modified in a similar fashion. These table and query hints are often used (in many cases, indiscriminately) to “improve the performance” of Microsoft SQL Server.

Let me start outright by saying that till date, I have never encountered the need to use the hint that we are about to discuss today.

Table v/s Query hints

Before going any further, I thought it best to revisit the simple method of identifying which is a table hint and which one is a query hint. For example, take the query below:

--Query for demonstration purposes only!
USE AdventureWorks2012
GO
SELECT *
FROM HumanResources.Employee WITH (NOLOCK) -- WITH (NOLOCK) is the table hint
WHERE OrganizationLevel = 3
OPTION (MAXDOP 1)                          -- OPTION (MAXDOP 1) is the query hint

The WITH (NOLOCK) hint is applied to the table/CTE/sub-query, i.e. a data source. Hence the name – table hint.

The OPTION (MAXDOP 1) is applied to the entire query, and hence the name – query hint.

[Edit]: You may also find the following post from Pinal Dave, a.k.a. “SQLAuthority” (B|T), useful in understanding Query Hints: http://blog.sqlauthority.com/2012/05/09/sql-server-quiz-and-video-introduction-to-basics-of-a-query-hint/

FASTFIRSTROW

According to SQL Server Books On Line, the FASTFIRSTROW is a table hint that helps the developer control the behaviour of the optimizer such that the optimizer,

• Optimizes for overall minimum cost (minimum processing time/resources, etc), OR
  
• Optimizes such that the time taken for the first row to be returned is minimized

The FASTFIRSTROW tells the optimizer that it is important to have the first row returned as fast as possible, allowing the application to return results to the users faster. You can see the difference by comparing the actual execution plans produced by the queries below:

--Query for demonstration purposes only!
USE AdventureWorks2008
GO
--Without the table hint
SELECT *
FROM HumanResources.Employee 
GO
--With the table hint
SELECT *
FROM HumanResources.Employee WITH (FASTFIRSTROW)
GO

OPTION (FAST n)

Running the above query in SQL Server 2012 results in the following error:

Msg 321, Level 15, State 1, Line 3
“FASTFIRSTROW” is not a recognized table hints option. If it is intended as a parameter to a table-valued function or to the CHANGETABLE function, ensure that your database compatibility mode is set to 90.

The message says it all – FASTFIRSTROW is no longer available, and if you would like to use it, the database compatibility level needs to be set to 90 (i.e.  SQL Server 2005).

The replacement available is not a table hint, but a new query hint – FAST number_rows. This query hint specifies that the query is optimized for fast retrieval of the first number_rows. This is a nonnegative integer. After the first number_rows are returned, the query continues execution and produces its full result set. SQL Server 2012 has given more flexibility to the developer/administrator because the table hint only optimized for the first row, whereas the query hint allows you to specify a custom number of first rows to optimize for.

As a replacement of FASTFIRSTROW, you can use OPTION (FAST 1).

--Query for demonstration purposes only!
USE AdventureWorks2012
GO
--Without the table hint
SELECT *
FROM HumanResources.Employee 
GO
--With the table hint
SELECT *
FROM HumanResources.Employee
OPTION (FAST 1);
GO

References:

• Table hints supported by SQL Server 2008 R2: http://msdn.microsoft.com/en-us/library/ms187373(v=sql.105).aspx
  
• Query Hints: http://msdn.microsoft.com/en-us/library/ms181714.aspx
  
• As I was learning about this table hint, I came across the following post, which I thought might make interesting reading: http://sqlblog.com/blogs/kalen_delaney/archive/2008/12/02/using-a-nonclustered-index-to-avoid-a-sort.aspx


• http://blog.sqlauthority.com/2012/05/09/sql-server-quiz-and-video-introduction-to-basics-of-a-query-hint/

Until we meet next time,

Be courteous. Drive responsibly.