DBA Diaries

Thoughts and experiences of a DBA working with SQL Server and MySQL

SQL Server 2012 certification

Posted on Written by 4 Comments

sql server 2012 certificationSo SQL Server 2012 has gone RTM and I am considering studying for a SQL Server 2012 certification.

At the time of writing this post, the exams are not yet available but are due in June 2012 with the beta exams happening in March 2012.

For this release, in order to attain a SQL Server 2012 certification you must pass five exams but if you are already SQL Server 2008 MCITP certified, then you need take only three to upgrade.

The information on the Microsoft site seems to indicate that the new qualifications will not be called MCITP. It refers to them as “Professional Level Certifications” There are two categories “Data Platform” and “Business Intelligence” of which three of the exams are the same, with the last two making the difference.

So somewhat different to the previous version whereby, you could go down three different paths to become either a MCITP Database Administrator, Database Developer or a Business Intelligence Developer for SQL Server 2008 and each one had only two exams to pass.

The lists are as follows.

“Data Platform”

  • Exam 70-461: Querying Microsoft SQL Server 2012
  • Exam 70-462: Administering Microsoft SQL Server 2012 Databases
  • Exam 70-463: Implementing a Data Warehouse with Microsoft SQL Server 2012
  • Exam 70-464: Developing Microsoft SQL Server 2012 Databases
  • Exam 70-465: Designing Database Solutions for Microsoft SQL Server 2012

“Business Intelligence”

  • Exam 70-461: Querying Microsoft SQL Server 2012
  • Exam 70-462: Administering Microsoft SQL Server 2012 Databases
  • Exam 70-463: Implementing a Data Warehouse with Microsoft SQL Server 2012
  • Exam 70-466: Implementing Data Models and Reports with Microsoft SQL Server 2012
  • Exam 70-467: Designing Business Intelligence Solutions with Microsoft SQL Server 2012

If you want an outline of what you will be expected to study for each exam, then you can view the Microsoft site and click into each exam for a breakdown. The link is here – SQL Server 2012 certification

My first thought when I read this and compared it with the SQL Server 2008 certification exams was why so many when you consider that in order to be qualified in SQL 2008, you had to pass one exam to get accredited with an MCTS and one more for the MCITP qualification. There are now five exams for one qualification.

When you consider that the options have been reduced in terms of what you can qualify as, then the number of exams involved seems logical because there is more cross over with the new qualifications compared with the old ones.

Of course this does mean that if you going for a SQL Server 2012 certification and you are not already certified, then you have a lot of work to do to get there.

For those upgrading the path is a little easier but the others will have to sit the five exams. At the time of writing this post I could not find anywhere which mentioned what you achieve after passing an exam. With the MCITP program, you attained an MCTS, a great incentive to sit an exam knowing that you had a chance of coming away with something at the end. Hopefully this will be the case with the SQL Server 2012 certification program.

If you want to go for both the Data Platform and Business Intelligence certs then it looks like you will have to sit all seven exams.

The professional level certs will also require re-certification every three years.

SQL Server 2012 certification benefits

  • Helps to make your CV stand out from the rest and could help get you an interview.
  • Demonstrates that you have spent time studying the product in depth.
  • Helps prove you are an expert in the field, although I would always say that there is no substitute for experience.
  • It’s a great way to learn about the features of the product.
  • Can help towards a promotion or get you more leverage for that pay increase you are after.

What costs are involved?

There will be a wealth of books available and Microsoft amongst others will usually supply dedicated reference material for at least some of the exams which you can purchase.

For each exam you sit, there will be a fee. Exams can be booked through Prometric and you can get an up to date price list from them on their site.

Consider using Books Online because it is a comprehensive reference tool and should provide a wealth of material which will be of use and could save you buying books.

Try and convince your employer to help contribute towards the costs illustrating the benefits that a SQL Server 2012 certification can bring to the business.

How I would study for SQL Server 2012 certification

I can’t tell you what method works best as I simply don’t know. Everyone is different and if you have studied for a sql server certification in the past, you will probably already have a method and routine which works.

This may sound obvious but organization will be key. I have to come up with a plan and stick to it. My life outside of work needs to have more structure if I am to achieve my goals and be ready to sit the exams.

Inside of work I like to be organized. Outside of work, I am more relaxed about things and I tend to “go with the flow”. I’ve got to change that. I want to draw up a schedule whereby each week, I have a plan to follow. I’m going to have to treat this as a project, break it down into chunks of study and tick them off when I have completed them.

As with any project, goal posts may have to be moved to accommodate change. I might have a week when I have had to work more hours in the office to get things done. This will set me back but I have to ensure that I get those study hours in somewhere else in order to catch-up. So sacrifices may have to be made.

There will be many other DBA’s out there who have already studied for a MCTS or MCITP. They may have their own blogs or they may contribute to discussions on forums. I will look for any advice that they can provide.

At the time of writing this post, the books which are due to be released on the exams are not yet available. However it doesn’t stop me from learning about the product. I am using the SQL Server 2012 RC0 download but SQL Server RTM is also now available to download on a 6 month evaluation if you wanted to use that.

I highly recommend that you create yourself a virtual environment in which to play in. Windows 2008 R2 is also available on a evaluation basis so if you have the capacity, set up some virtual machines, install one of the SQL Server versions I have mentioned and start learning using the Microsoft exam outlines and SQL Server 2012 Books Online to support your training.

For virtual clients, I use VirtualBox from Oracle and below are the links for Windows 2008 R2 and SQL Server 2012 RTM

SQL Server 2012 RTM Evaluation

Windows 2008 R2 Evaluation

And finally…

I’m still undecided. I am definitely tempted to sit the SQL Server 2012 certification exams. I know they will be of great benefit in my career and the feeling of achievement in becoming a recognized SQL Server professional will I am sure be a great one.

Update…

You can read more on this topic in this post

New T-SQL features in SQL Server 2012 – CREATE SEQUENCE

Posted on Written by Leave a Comment

Continuing on from new T-SQL features of SQL Server 2012, I wanted to explain about the new CREATE SEQUENCE feature.

What is CREATE SEQUENCE for?

A common feature in table design is to place an auto incrementing number on a field in the form of an IDENTITY column. So this is an easy way of maintaining a sequential number on a table.

What if you wanted to create a database wide identity? Prior to SQL 2012, you might choose to do it by having a table sitting there in the middle of it all with a numeric field which gets updated by some function every time a new identity is created. CREATE SEQUENCE takes away this overhead.

You can create a sequence either using SQL Server Management Studio or using T-SQL. In Management Studio, find the “Sequences” folder under “Programmability” beneath the database you want to add a sequence for.

create sequence

Here you can configure the sequence according to your design.

To do the same thing in T-SQL, the syntax is:

CREATE SEQUENCE MyDemoSequence
START WITH 1
INCREMENT BY 1;

If you choose to do this using T-SQL then by default a sequence is created as a BIGINT datatype unless you specify otherwise, for example:

CREATE SEQUENCE MyDemoSequence AS SMALLINT
START WITH 1
INCREMENT BY 1;

Note that START, INCREMENT, MINVALUE and MAXVALUE must be configured within the boundaries of the data type. For example, you couldn’t specify a negative START value for a TINYINT data type.

To use CREATE SEQUENCE

To get the next value in the sequence use the NEXT VALUE FOR

SELECT NEXT VALUE FOR MyDemoSequence;

If you were inserting to a table, it would like something like this:

INSERT INTO YourTable(ID, Name)
VALUES(NEXT VALUE FOR MyDemoSequence, 'Your name');

So what else is there to know about CREATE SEQUENCE?

CYCLE and NO CYCLE tells the sequence to cycle back round to the minimum value when the last value has been allocated in the sequence. By default, this is set to NO CYCLE.

CACHE or NO CACHE, designed for performance benefits in reducing IO requests for new numbers out of a sequence. A cache value can be specified and the numbers up to the maximum cache value are loaded up into memory until the cache is exceeded and a new set of numbers is required.

For example, you might create a sequence with a cache of 20. When a value is needed from the sequence, the minimum value in the sequence up to and including the CACHE value are loaded into memory. The CACHE value of 20 is written to the system table sys.sequences and when 20 gets used and 21 is requested, then a fresh set of numbers is allocated to the cache (21-40) with 40 being written to the sys.sequences table as the CACHE value.

Here’s an example of a sequence created using CYCLE and CACHE

CREATE SEQUENCE MyDemoSequence AS INT
START WITH 1
INCREMENT BY 1
CACHE 20
CYCLE;

Lets have a quick look at the sys.sequences table

SELECT cache_size, is_cached, current_value
FROM sys.sequences
WHERE name = 'MyDemoSequence';

Returns

cache_size  is_cached current_value
----------- --------- -------------
20          1         1

(1 row(s) affected)

 

New T-SQL features in SQL Server 2012 – Conversion Functions

Posted on Written by Leave a Comment

SQL Server 2012 (“Denali”) is nearly here and with all new releases, there are always new features to know about.

I am very interested to find out more about the product and my next few posts will talk about some of the new T-SQL features of SQL Server 2012.

This first post will cover the new conversion functions in SQL Server 2012.

I am using the latest download from Microsoft – SQL Server 2012 RC0 for my research.

TRY_CONVERT()

Should the function fail then NULL is returned. The following all return the same output bar the last statement which fails and returns NULL.

SET NOCOUNT ON;
SELECT TRY_CONVERT(datetime, '2012/01/31');
SELECT TRY_CONVERT(datetime, '2012-01-31');
SELECT TRY_CONVERT(datetime, '20120131');
SELECT TRY_CONVERT(datetime, '31-12-2012');

Returns

-----------------------
2012-01-31 00:00:00.000

There is an optional integer expression called “style” which is like CONVERT and actually shares the same integer values for styling the output.

SET NOCOUNT ON;
SELECT TRY_CONVERT(datetime, '20120131', 103);

Returns

-----------------------
2012-01-31 00:00:00.000

PARSE()

Accepts a nvarchar input up to 4000 chars and attempts to evaluate the input based on the value and datatype specified for the parse. The following examples return the same output.

SET NOCOUNT ON;
SELECT PARSE('Tuesday, 31 January 2012, 21:00' AS datetime USING 'en-GB');
SELECT PARSE('Tuesday, 31 January 2012, 9PM' AS datetime USING 'en-GB');
SELECT PARSE('Tuesday 31 January 2012 9PM' AS datetime USING 'en-GB');
SELECT PARSE('Tuesday 31 Jan 2012 9PM' AS datetime USING 'en-GB');
SELECT PARSE('Tuesday 31 Jan 12 9PM' AS datetime USING 'en-GB');

Returns

-----------------------
2012-01-31 21:00:00.000

The part which reads USING ‘en-GB’ is the culture specified. If no culture is specified then the language of the current session gets used.

Should the parse fail, then an error is produced.

TRY_PARSE()

Just like TRY_CONVERT(), TRY_PARSE() will return NULL if the TRY fails to parse the input. So trying to parse a string as a date would fail and return NULL. Again, you can specify USING to try a specific culture. The following returns NULL.

SET NOCOUNT ON;
SELECT TRY_PARSE('This is a date' AS datetime USING 'en-GB');

Just like TRY_CONVERT(), it’s useful for testing output, for example:

SET NOCOUNT ON;
SELECT CASE WHEN TRY_PARSE('This is a date' AS datetime USING 'en-GB') IS NULL THEN
'Parse failed'
ELSE
'Parse succeeded'
END;

Returns

---------------
Parse failed

Next SQL Server 2012 post – CREATE SEQUENCE

 

This site uses cookies. We assume you are happy with cookies but click the link if you are not. Close