Resources

• 1

  Create a primary key on each table you create and unless you are really knowledgeable enough to figure out a better plan, make it the clustered index (note that if you set the primary key in Enterprise Manager it will cluster it by default).

• 2

  Create an index on any column that is a foreign key. If you know it will be unique, set the flag to force the index to be unique.

• 3

  Don’t index anything else (yet).

• 4

  Unless you need a different behaviour, always owner qualify your objects when you reference them in TSQL. Use dbo.sysdatabases instead of just sysdatabases.

• 5

  Use set nocount on at the top of each stored procedure (and set nocount off) at the bottom.

• 6

  Think hard about locking. If you’re not writing banking software, would it matter that you take a chance on a dirty read? You can use the NOLOCK hint, but it’s often easier to use SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED at the top of the procedure, then reset to READ COMMITTED at the bottom.

• 7

  I know you’ve heard it a million times, but only return the columns and the rows you need.

• 8

  Use transactions when appropriate, but allow zero user interaction while the transaction is in progress. I try to do all my transactions inside a stored procedure.

• 9

  Avoid temp tables as much as you can, but if you need a temp table, create it explicitly using Create Table #temp.

• 10

  Avoid NOT IN, instead use a left outer join – even though it’s often easier to visualize the NOT IN.

• 11

  If you insist on using dynamic sql (executing a concatenated string), use named parameters and sp_executesql (rather than EXEC) so you have a chance of reusing the query plan. While it’s simplistic to say that stored procedures are always the right answer, it’s also close enough that you won’t go wrong using them.

• 12

  Get in the habit of profiling your code before and after each change. While you should keep in mind the depth of the change, if you see more than a 10-15% increase in CPU, Reads, or Writes it probably needs to be reviewed.

• 13

  Look for every possible way to reduce the number of round trips to the server. Returning multiple resultsets is one way to do this.

• 14

  Avoid index and join hints.

• 15

  When you’re done coding, set Profiler to monitor statements from your machine only, then run through the application from start to finish once. Take a look at the number of reads and writes, and the number of calls to the server. See anything that looks unusual? It’s not uncommon to see calls to procedures that are no longer used, or to see duplicate calls. Impress your DBA by asking him to review those results with you.

Lorem Ipsum is simply dummy text of the printing and typesetting industry. Lorem Ipsum has been the industry’s standard dummy text ever since the 1500s, when an unknown printer took a galley of type and scrambled it to make a type specimen book. It has survived not only five centuries, but also the leap into electronic typesetting, remaining essentially unchanged. It was popularised in the 1960s with the release of Letraset sheets containing Lorem Ipsum passages, and more recently with desktop publishing software like Aldus PageMaker including versions of Lorem Ipsum.

New features, no matter how attractive they are, are no guarantee that an upgrade is necessary. But SQL Server 2012 is an exception. Using SQL Server makes sense financially, with the benefits outweighing the costs. Not only that, it leads to higher productivity, cost savings and a better working environment for IT professionals in the enterprise.

Increased productivity for systems engineers and database administrators due to time savings, better security and higher data quality.

Increased productivity for application developers due to higher availability, improved workflow, and also due to the improvements incorporated with the new SQL Server.

Savings from service level agreement penalties brought about by data loss or outages. This is also a good way to protect the business’ reputation.

4. Self-service business intelligence and very powerful business intelligence reports.

Click to see all available editions of SQL Server 2012

Top 10 New Features of SQL Server 2012

• 1AlwaysOn Availability Groups
• 2Big Data Support
• 3Columnstore Indexes
• 4User-Defined Server Roles
• 5Enhanced Auditing Features
• 6BI Semantic Model
• 7Sequence Objects
• 8Enhanced PowerShell Support
• 9Distributed Replay
• 10PowerView

• 1

  Know your target audience

• 2

  Understand which platform you want to build on

• 3

  Understand the pricing Model for the app

• 4

  What to look for in a development team

• 5

  Understand the factors that will drive the cost of this development