Prepare for your 70-461 exam?

Recently I passed the 70-461 exam and I must say this blog post helped me preparing for it:
Passed 70-461 Exam : Querying Microsoft SQL Server 2012

Here by this post I want to provide my readers some information and knowledge about this Exam, so that they can plan well for this and look forward to take up this exam.

–> Exam Highlights:

– There were total 54 Questions
– Total time to attempt the paper was 3 Hrs.
– Total Marks: 1000
– Passing Marks: 700
– The Questions in the Exam were:
1. Mostly Objective Type.
2. Some were Subjective: where you’ve to write code, like SELECT statement and/or View Definition, etc.
3. And very few were Multiple Choice.
4. A few Question were where you’ve to pick up the appropriate code snippet and arrange them in correct sequence.

–> The Exam is divided into 4 modules:
1. Create Database Objects
2. Work with Data
3. Modify Data
4. Troubleshoot & Optimize



Want to find out what will be tested during the exam? Manoj posted a detailed break-down with all the subjects in the exam:
Passed 70-461 Exam : Querying Microsoft SQL Server 2012





SQL Server Data Tools (SSDT) Project Templates – Visual Studio 2012 (BI)

Thanks to the Dutch Data Dude

If you installed SQL 2012 you have probably noticed that you development environment for Integration Services, Analysis Services and Reporting Services is still hosted in a Visual Studio 2010 shell (SQL Server Data Tools). However, with a free download you can get the Microsoft Business Intelligence Project Templates for SSIS, SSAS and SSRS in your Visual Studio 2012 installation. All you need to do is to download and install it.

Microsoft SQL Server Data Tools – Business Intelligence for Visual Studio 2012





How to determine the maximum length / value for each column in your database?

You might want to use this query (found it here) and made a few modifications. It’s an older query, but this may point you in the right direction.

DECLARE @Results table
(
ID uniqueidentifier,
SchemaName varchar(250),
TableName varchar(250),
ColumnName varchar(250),
DataType varchar(250),
MaxLength varchar(250),
NumericPrecision varchar(250),
DatetimePrecision varchar(250),
IsNullable varchar(250),
Longest varchar(250),
SQLText varchar(250)
)

INSERT INTO @Results
SELECT
NEWID(),
t.TABLE_SCHEMA,
t.TABLE_NAME,
COLUMN_NAME,
DATA_TYPE,
CASE
WHEN DATA_TYPE NOT IN ('nvarchar','varchar') THEN 'N/A'
WHEN CHARACTER_MAXIMUM_LENGTH = -1 THEN 'Max'
ELSE CAST(CHARACTER_MAXIMUM_LENGTH AS varchar)
END,
NUMERIC_PRECISION,
DATETIME_PRECISION,
IS_NULLABLE,
'NA',
'SELECT Max(Len(' + COLUMN_NAME + ')) FROM ' + t.TABLE_SCHEMA + '.' + t.TABLE_NAME
FROM INFORMATION_SCHEMA.COLUMNS c
INNER JOIN INFORMATION_SCHEMA.TABLES T
ON t.TABLE_NAME = c.TABLE_NAME
WHERE t.TABLE_TYPE = 'BASE TABLE'

DECLARE @id varchar(36)
DECLARE @sql varchar(200)
DECLARE @receiver table(theCount int)

DECLARE length_cursor CURSOR
FOR SELECT ID, SQLText FROM @results WHERE MaxLength != 'NA'
OPEN length_cursor
FETCH NEXT FROM length_cursor
INTO @id, @sql
WHILE @@FETCH_STATUS = 0
BEGIN
INSERT INTO @receiver (theCount)
exec(@sql)

UPDATE @results
SET Longest = (SELECT theCount FROM @receiver)
WHERE ID = @id

DELETE FROM @receiver

FETCH NEXT FROM length_cursor
INTO @id, @sql
END
CLOSE length_cursor
DEALLOCATE length_cursor

SELECT
SchemaName,
TableName,
ColumnName,
DataType,
MaxLength,
NumericPrecision,
DatetimePrecision,
IsNullable,
Longest
FROM
@Results





How to create a Table based on a View with the same column names and data types?

I found myself manually creating tables with identical column names and data types based on view-definitions…which felt a bit dump. As always, Google is my best friend. Found out that it is actually quite simple, by using the INTO clause. Of course I’ve used it before to store data in Temp-tables (#), but never thought about the possibility to use it for normal tables. 🙂

Let say you’ve created a view like this one:

CREATE VIEW dbo.vw_Resultset
AS
(
SELECT
CAST(1 AS INT) AS RecordId
,CAST(123 AS DECIMAL(9,2)) AS Amount
,CAST('NormalText' AS VARCHAR(20)) AS NormalText
,CAST(N'UnicodeText' AS NVARCHAR(50)) AS UnicodeText
,CAST(GETDATE() AS DATETIME) AS CurrentDateTime
,CAST(GETDATE() AS DATETIMEOFFSET) AS CurrentDateTimeOffset
)

How it looks in the physical world:

vw_Resultset

Ok, it’s time to create a new table with an identical structure (column names and data types):

SELECT *
INTO dbo.tbl_Resultset
FROM dbo.vw_Resultset

The result:

tbl_Resultset

Drop a comment if you haven’t thought about this option before, so I don’t feel alone! Feeling dump right now…I’ll now focus on a few, more advanced, problem solving / designing work 😀