SQLServerCentral - www.sqlservercentral.com

A community of more than 1,600,000 database professionals and growing

Featured Contents

Featured Script

The Voice of the DBA

Open Source the MCM

It's over. There's no more MCM program or certification from Microsoft. The last MCM test was given last year and no upgrades are planned for SQL Server 2012 or SQL Server 2014. That's a little sad, though there are quite a few of our SQL Server professionals that can still proudly wear the MCM title for the rest of their careers.

The MCM tests were designed differently than all the other MCP type tests, requiring more thought and deduction, as well as practical skills. The lab in particular was daunting to many of the MCMs, most of whom would tell you about the difficulties in getting through the scenarios in the limited time alloted. All of the people I've talked to found the challenge refreshing and also informative, enabling them to learn a few things about their knowledge, even from the problems they didn't complete.

Since that chapter in Microsoft Learning is complete, and the tasks likely out of date, I'd ask that Microsoft Learning release the questions and scenarios to the world as an open source project. Unlike the other certifications and exams, these questions aren't going to be re-used anytime soon and the knowledge could help many people learn to build better solutions.

This would be a great move, allowing many DBAs to challenge themselves with the questions and scenarios in practice labs. The types of scenarios could be used in interviews for new employees, either as they are written or modified for a particular environment. Professionals using the SQL Server platform would get an idea of not only the broad level of knowledge that MCMs have, but they'd also have a way to test themselves and direct their own learning to become better rounded SQL Server developers and administrators.

I doubt it would happen, but I'd think Microsoft could help the community, generate some goodwill, and help improve the overall quality of people working on their platform.

Steve Jones from SQLServerCentral.com

Join the debate, and respond to today's editorial on the forums


The Voice of the DBA Podcast

Listen to the MP3 Audio ( 2.4MB) podcast or subscribe to the feed at iTunes and LibSyn. feed

The Voice of the DBA podcast features music by Everyday Jones. No relation, but I stumbled on to them and really like the music. Support this great duo at www.everydayjones.com.

Everyday Jones

Follow Steve Jones on Twitter to find links and database related items and announcements.

ADVERTISEMENT
SQL Source Control

Get your SQL Server database under version control now!

Version control is standard for applications, but databases haven’t caught up. So how can you bring database development up to speed? Why should you start? Find out…

.NET Memory Management

Ricky Leeks on Learning .NET Memory Management

Pick up all six free articles in one free download. Find out what Ricky has to teach you about garbage collection, memory management gotchas, and more. Download the article pack free.

SQL Toolbelt

16 essential SQL Server tools

In one installer, the award-winning SQL Toolbelt contains everything you need to work with SQL Server. "The SQL Toolbelt provides tools that database developers as well as DBAs should not live without." William Van Orden. Download a free trial.

Featured Contents

 

Stairway to Database Source Control Level 1: Setting Up For Database Source Control

Dave Green from SQLServerCentral.com

The first level of this stairway introduces the basics of source control, some common technologies and technologies, and demonstrates how to start versioning a SQL Server database. More »


 

Free eBook: Troubleshooting SQL Server: A Guide for the Accidental DBA

Press Release from Red-Gate

Three SQL Server MVPs (Jonathan Kehayias, Ted Krueger and Gail Shaw) provide fascinating insight into the most common SQL Server problems, why they occur, and how they can be diagnosed using tools such as Performance Monitor, Dynamic Management Views and server-side tracing. The focus is on practical solutions for removing root causes of these problems, rather than "papering over the cracks". More »


 

Database Normalization Basics

Additional Articles from SimpleTalk

The task of Database Normalization doesn't have to be painful, especially if you follow Old Mother Celko's Normalization Heuristics. More »


 

From the SQLServerCentral Blogs - MDX # 39 – Find Default Measure using MDX Query

Sherry Li's BI Corner from SQLServerCentral Blogs

In Chapter 1 of our book, MDX with SSAS 2012, we’ve devoted a section “Setting a default member of a... More »


 

From the SQLServerCentral Blogs - FileTable–Adding a file to a folder

Steve Jones from SQLServerCentral Blogs

At my Filestream/Filetable talk yesterday at SQL Intersection, someone asked me about programmatically adding a file to a Filetable. Easy... More »

Question of the Day

Today's Question (by Steve Jones):

What is the amount of free disk space required for your durable memory-optimized tables?

Think you know the answer? Click here, and find out if you are right.


We keep track of your score to give you bragging rights against your peers.
This question is worth 1 point in this category: SQL Server 2014.

We'd love to give you credit for your own question and answer.
To submit a QOTD, simply log in to the Contribution Center.

ADVERTISEMENT

Microsoft SQL Server 2012 High-Performance T-SQL Using Window Functions

Optimize your queries—and obtain simple and elegant solutions to a variety of problems—using window functions in Transact-SQL. Led by T-SQL expert Itzik Ben-Gan, you’ll learn how to apply calculations against sets of rows in a flexible, clear, and efficient manner. Ideal whether you’re a database administrator or developer, this practical guide demonstrates ways to use more than a dozen T-SQL querying solutions to address common business tasks. Get your copy from Amazon today.

Yesterday's Question of the Day

Yesterday's Question (by Steve Jones):

I have created a certificate for the login/user Sales1 as follows:

CREATE CERTIFICATE Sales1Cert
 AUTHORIZATION Sales1 WITH SUBJECT = 'Salesperson1 certificate'

I have also created a symmetric key that has been used to encrypt some sales data as follows:

CREATE SYMMETRIC KEY SalesSymKey
 WITH ALGORITHM = AES_128
 ENCRYPTION BY CERTIFICATE Sales1Cert

I now want to give Sales2 access to decrypt the data encrypted by the SalesSymKey symmetric key, but I don't want them to have access to other keys that are encrypted with the certificate Sales1Cert. What two things should I do?

Answer:

  • Run "CREATE CERTIFICATE Sales2Cert AUTHORIZATION Sales2 WITH SUBJECT = 'Salesperson2 certificate'"
  • Run "ALTER SYMMETRIC KEY SalesSymKey ADD ENCRYPTION BY CERTIFICATE Sales2Cert"

Explanation:

A single certificate can be used to encrypt and secure multiple other keys. A single key can also be secured by multiple certificates. Therefore the solution is to add a certificate for user Sales2 with 

CREATE CERTIFICATE Sales2Cert

AUTHORIZATION Sales2 WITH SUBJECT = 'Salesperson2 certificate'

Then add the encryption to the Symmetric key using this certificate with:

ALTER SYMMETRIC KEY SalesSymKey
 ADD ENCRYPTION BY CERTIFICATE Sales2Cert

This way both users can use their own certificate to access data secured by SalesSymKey, but they would not be able to access data secured by other keys.

Ref: Yet another column encryption demo - http://blogs.msdn.com/b/lcris/archive/2005/12/16/504692.aspx

ALTER SYMMETRIC KEY - http://technet.microsoft.com/en-us/library/ms189440.aspx


» Discuss this question and answer on the forums

Featured Script

Restore script genrator for moving dbs

Anoop from SQLServerCentral.com

Run the script from query analyser on the source server. Remember to change query results to text and also remove include column headers option from query options. If filepaths are different on source and destination just do a find and replace on file path.  Copy the output of query and run it on destination and you are done

More »

Database Pros Who Need Your Help

Here's a few of the new posts today on the forums. To see more, visit the forums.

SQL Server 2014 : Development - SQL Server 2014

Watch out: change for line numbers in SSMS 2014 - There is a change in SSMS 2014 how line numbers are displayed in error messages. Instead of displaying the raw...

convert decimal to int in case condition - i have a table exam_setup CREATE TABLE exam_setup ( setup_id INT, sub_id INT, assignment decimal(4,1), attendance decimal(4,1), INT_1 decimal(4,1), INT_2 decimal(4,1) ) table values are, sub_id = 75, assignment = 25.0 attendance = 15.5 INT_1 =0.0 INT_2...

Microsoft SQL triggers on columns - Dear All I have one table namely consumer with approx 50 columns. I have created one same table with audit prefix including...


SQL Server 2012 : SQL 2012 - General

Constraint Vs Trigger - Hi, I am facing a question which I cannot really have an answer without having a second opinion. My application have a...

Trigger and commit - Dear All I have a update trigger. In this trigger I need to insert few records in 3 tables. If error...

Cannot Stop File Growth on Data File - We have a large 'History' database that is currently about 4.5TB, with most of that in a datafile that is...

PK - Hi All, In what situations we can create primary key on a table? I mean what is the minimum no of...

Script to trace FK between differenct DBs, is it possible. - Hi, I have this script running to list all FK relationship but it's only for single db, anybody tried to...

SSRS report using the new HierarchyID datatype - Hi, I am using the HierarchyID datatype for my requirement to manage the hierarchy structure. I found it very useful in...

Help with Join removing NULLS that need to be displayed - I have a table named item that looks like this: PartNumber Description I.DateCreated 1A1XXX ALPHA1 1/1/2014 2A2XXX ALPHA2 1/5/2014 3A3XXX ALPHA3 1/12/2014 1B1XXX BRAVO1 1/22/2014 2B2XXX BRAVO2...

Database monitoring - Currently there are various teams accessing the database. For costing reasons, we need to track usage. Is there an efficient way...

DB Chaining and Ownership chains - Hi all I am getting the following error when trying out DB chaining and Ownership chains Msg 916, Level 14, State 1,...

Profiling Parameters - Hi I was trying to find the parameters of a query I suspect to be causing unnecessary logical read and eventually...

File and Filegroup backup components greyed out - Simple Recovery Model - Hi, I am looking at the file / filegroup level backup and recovery options within SQL Server and I'm struggling with the...

Optimizing Query - Hi, Script attached which needs to be optimized. Request background: 1) Add a new record with top priority Related Requirement: 1) Modify the priority of...

SSIS import - The column delimiter for column "COL1" was not found. End Error Error: 2014-04-14 17:08:09.95 Code: 0xC0202092 Source: Load PEFC MTD...

SQL Server Maximum Write Performance - Hi SQL Gurus, I want to know if we are already getting the fastest SqlSever Write Performance for our application. We created...

Partial Match / Duplicates - Hi, I'm trying to figure out values that match partially or are duplicates, but it is supposed to be a row...

Database size from Backup file - Hello Guys, Can anyone please help me in finding the database size from the backup file. I have SQL 2012 backup file,...

Availability Group Failover Stops Working After First Failure. - Hello- I've setup a two node Cluster Server (non-shared storage) with a file sharing witness. I'm testing some of the different...

Retrieve binary file from SQL Server - Hi All, I have been trying to store binary file in a folder from the SQL Server. Here is the code I...

Webservices Deployment - Hi, Please some one let me know how to deploy web services on local machine. I created self certificate in IIS...

Can't execute SSIS package from remote machine - OS: Windows 2012, standard, 64-bit SQL: 2012 + SP1 + CU2 = 11.0.3339 We recently decided to "break apart" our BI environment. We used to...


SQL Server 2012 : SQL Server 2012 - T-SQL

Replacing UNION in TSQL 2012 - Gents, I have been looking for the most optimal way to replace UNIONs all over the internet and I can't...

Simple JOiN - I have 3 Tables; User, UserType, Permissions - Common Field between User and UserType is UserTypeID Common Field between UserType and Permissions...

Get original order quantities and dates, and find any changes or additions to quantities in subsequent order version(s) - I am due to work on this problem, but I have a lot of trouble trying to figure out how...


SQL Server 2008 : SQL Server 2008 - General

Drop Identity column from temp table - Hi I'm selecting * from my perm table into my temp table(SELECT * temp INTO FROM perm), and it will also select the...

Error installing MS SQL Server R2 Enterprise (x64) - Hi Everyone, Recently I attempted to upgrade from MS SQL Server to MS SQL Server R2. However I have encountered an error,...

Connecting other sql server database from sql query - Hi , I am writing stored procedure in sql server called SERVER1 and inside same procedure i want to access another...

Cannot resolve the collation conflict between "Latin1_General_CI_AI" and "SQL_Latin1_General_CP1_CI_AS" in the equal to operation. - how to reslove this error Msg 468, Level 16, State 9, Line 7 Cannot resolve the collation conflict between "Latin1_General_CI_AI" and "SQL_Latin1_General_CP1_CI_AS"...

Looking For Set Based Solution to RBAR Like Problem with Comparison Of Data in Audit Table - I've included the DDL to construct the audit table I am working with. Understand that this audit table nor the...

What does an ALTER COLUMN actually do at run time? - We ALTER'ed a table - increasing the size (numeric 12 to numeric 17). It took over 40 minutes to run on...

SQL Server Wait Time Clarification - I recently collected the wait times on one of our servers. We know we have a disk issue and it...

querying Login details.......... - Hi all, what is the best way to query all login details like A is 'sysadmin', B is 'public', C is...

SSIS Package - HOW TO USE - I am new to SQL, I created a table on a destination database and I am trying to transfer data...

Insert INTO Select - Hi I have this problem to solve. I have to get any table as input parameter, then create a temp table...

How to save maintenance plan as a SSIS package in SQL 2008 Enterprise - Hi Experts, Wanted to know how to save maintenance plan as a SSIS package in SQL 2008 Enterprise, so that I...

Aggregating data by Ranking and grouping - Hi There, I need to aggregate data and display it by ranking. I have a table called learning which has the...

the problem in use of data type float - Hi my problem is: DECLARE @Price FLOAT SET @Price=15000000 PRINT @p1 result: 1.5e+007 please help tanks

Table Fragmentation - When i run the DBCC SHOWCONTIG() on my database it is displaying the attached list , is the tables got fragemented?...

Bulk Insert Issue - Hi, I am trying to import CSV files into my table I have three columns in my table. TableName : Keys key nvarchar(50) English nvarchar(4000) Spanish nvarchar(4000) My...

Random fill. - Two tables, First table has one numeric field from 1 to n (consequtive). (thousands of rows). And one or more columns to hold...

Query optimization help - I have one simple query to optimize but somehow after creating couple of index this query is still having more...

SQl Server R2 Enterprise Evaluation link anyone? - Hi, Anyone got a link or is hosting the older 2008 R2 enterprise evaluation version, I need to load up an...


SQL Server 2008 : T-SQL (SS2K8)

Getting heirarchy - I'm struggling to prepare a recursive t-sql for the below scenario User table user id (int) active (bit) Reporting table userid (int) reporting_userid (int) Assume I have...

Performance related question - asa

Pulling incorrect records using date range in where clause - Hi All, I've been experiencing difficulty with pulling records using a where clause date range. I'm using this: select * from dbo.ACCTING_TRANSACTION_hISTORY where ath_postype...

Problem with SQL statement pulling out incorrect data - Hi there folks I'm not at all adept at using T-SQL, and I've got a problem with a script that I've...

Help in writing SQL averages query - Hello All , I need some expert help in writing the sql query for the below scenario. I have a table in...

Merging pseudo-duplicate records - Here's an interesting sql challenge that I haven't encountered before. We have a data warehouse staging database in which we...

SQL Help requested to Get Daily and Weekly OT - Hello Everyone, I need to pull a report from a table which gives me daily totals, I'm asked to get regular...

Get the Start and end date based on a criteria. - Hi All, I have a scenario where i need to get the starting and ending date time based on the crieteria....

2008 r2 t-sql call stored procedure with 3 parameters - In t-sql 2008 r2 I need execute a stored procedure called StudentData and pass 3 parameter values to the stored...

Selecting Data by Date for last five days AND avoiding weekend dates - What I am trying to do: Obtain attendance percentages for schools for the last five days. The outcome would look...


SQL Server 2008 : SQL Server Newbies

Converting CDbl to SQL - Hi, Hopefully someone can help. i have the following description field in access: CDbl(1/([NUM_STOP]-1)) AS [TL Equivilent] Is there any way that i...


SQL Server 2008 : Security (SS2K8)

SysAdmin server role and Active Directory Groups - I've been struggling to find best practices information or any advice regarding Active Directory Groups that provide group members with...


SQL Server 2008 : SQL Server 2008 High Availability

Standby & NoRecovery - Hello, We have a standby database, which is a manual, log-shipped copy of live, which we report and query against. This...

Triggers Not Found at Subscriber End - Dear, I have configured snapshot replication in my database. Some of my tables have triggers. But at the subscriber end, I...


SQL Server 2008 : SQL Server 2008 Administration

How To Optimize a Query in SQL2008 - Hi All, Am Using SQL2008, I want to Optimize a Query,Where can i check the Recommendations like whether i have to create...

change datasource of server in [sys].[servers] - We will shutting down an older sql server and standing up a newer 2008 version using the same server name...

SQL Server 2008 R2 in hung state. Need help in analyzing the log. - 2014-04-20 20:02:08.51 spid5s SQL Server has encountered 28 occurrence(s) of I/O requests taking longer than 15 seconds to complete on...

SP redundant call - Hi Experts, How to Check the stored procedures to find redundant calls within it? Thanks in Advance.


SQL Server 2008 : SQL Server 2008 Performance Tuning

Need to generate Report from a Busy Database - Hi All, We have a production database which is busy throughout the day and need to generate report from the same....

Optimization not enough - Hi, In my execution plan i can see some warning like: 1. "Type conversion in expression (CONVERT_IMPLICIT(varchar(40),[PAIntel].[ActivationDateTime],0)) may affect "CardinalityEstimate" in query...


Cloud Computing : SQL Azure - Development

Add Azure SQL Database to Azure Private Network - Does anyone know if this is possible? I can find no resource, Microsoft or third-party which give advice how this...


SQL Server 2005 : Business Intelligence

Data Driven Subscription for SSRS report using SSAS as backend - I have a SSRS 2005 report that compares data between the two servers T36 and T38. The data is stored...


SQL Server 2005 : Development

Extracting data from XML is slow and filling tempdb transaction log - I am trying to extract data from an XML column within a table on SQL Server 2005. The table as...


SQL Server 2005 : SQL Server 2005 General Discussion

Error saving Excel document in varbinary(max) field - Hello, We have a web site in asp.net where users can upload and save various documents. These are being stored in an...


SQL Server 2005 : SS2K5 Replication

Log Reader Agent Job Issue on Remote Distributor - From a Dev machine, I have successfully set up transactional replication using SQL Server 2005 as a Publisher and 2012...


SQL Server 2005 : SQL Server 2005 Integration Services

SSIS, "Class Not Registered" error...deployment or permissions issue? - I'm getting an error with an SSIS package that reads from an Access DB on a network share and writes...


SQL Server 2005 : T-SQL (SS2K5)

Execute a select statement continually - for instance every 5 minutes - This is probably easy, but I can't find it mentioned.... Anyway, I need to connect to our SQL estate via a...

Calculate XIRR in SQL Server - Hello All, Is there any way to calculate XIRR in SQL server using T-SQL. Kindly suggest. Thankx


SQL Server 2005 : SQL Server Newbies

Query database by date and send results to table - I'm a bit of a SQL newb but have been tasked with coming up with some code to retrieve values...


Reporting Services : Reporting Services

SSRS Grouping - Hi, I have a requirement in SSRS as shown in the attachment. Please suggest me how can i achieve this. ...

SQL/SRSS Newbie Assistance Needed :) - Hi all: Ok, I'm somewhat new to SQL/SRSS and could really use some help in getting started on something I"m tasked...

ssrs text at last page of the report - In my report i am using one tablix and one text box. Text box always bottom of the last page,...

Report Builder 3.0 Exclusion Query based on Parameter and Field Values - Hi, I am creating a report which has a Full and Partial Parameter. If the Partial parameter is selected I want...


Reporting Services : Reporting Services 2005 Development

Change Shared Datasource Password - Hi All, I have created nearly 50 shared DataSource , which is supported for nearly 300 RDLs. The Password credential for...


Reporting Services : Reporting Services 2008 Administration

Security for Linked report - We have a master report "AP Aging" under "Europe" folder. In Security "GAE-G-AccountingReportServicesW1" AD group is added. "GAE-G-AccountingReportServicesW1" includes users Olga, Christian,...

Report Model Changes - How do I the DiscourageGrouping attribute for a field? - I have a field 'comments' that is returning an error when I drag and drop it to report builder that...


Reporting Services : Reporting Services 2008 Development

Expand/Collapse rows with extra columns being added. - I have a report with collapsible/expanding rows. The problem I have is that I want the summary report to have...

How to convert nvarchar to date data type? - I have to convert nvarchar to date data type. and fetch the week number of that particular month from the...


Programming : General

No Value Given For One or More Required Parameter oledb to Excel - In order to avoid the 255 character limitation in Excel and SSIS I have written a VB.Net SSIS script to...


Programming : Powershell

SQL Server Detach database - Hi I am a SQL Server DBA and have never used Powershell before. I'm trying to detach a database using Powershell,...


Data Warehousing : Integration Services

Any possibilities to save the message tab information in SSIS - Hi All, I have a package to execute the .SQl file in a folder. Is there any possibility to save message...

The given key was not present in the dictionary. SSIS XML SOURCE - Hi folks Just looking for suggestions really. I've got to load multiple XML files into a database. I've managed (finally) to...


Data Warehousing : Strategies and Ideas

Global BI Architecture - Im in the process of starting to think about building a Data warehouse for out BI needs in the UK....


Data Warehousing : Analysis Services

Attaching SSAS databases - Unfortunately, we recently had our development server die on us. :crazy: Fortunately, we were able to get the data from...


Database Design : Design Ideas and Questions

HIPAA Compliant Architecture - Hi, I am developing a Medical Billing Software and should be HIPAA complaint. My current architecture is using seperate database for...


SQLServerCentral.com : Anything that is NOT about SQL!

Today's Random Word! - HI When you woke up today, or logged-onto Opera Forums, you may have had a dream, a thought, a scene...

Are the posted questions getting worse? - Is it me, or are the posted questions getting worse these days? I just read a post by someone apparently in...


SQLServerCentral.com : Articles Requested

Permissions in a database - There are scripts for this, but I would like a short tutorial piece that shows how to query for all...

Database Role Membership - I am looking for a short piece that shows how to check if a user is a member of a...

Server Role Membership - I am looking for a short peice that shows how to check if a login is a member of a...

Basic piece on statistics for queries - How statistics are used by the query optimized, how they are stored, some good maintenance practices for users.

Duplicate Indexes - The problems with having duplicate indexes, how to find them and how to remove them.


SQL Server 7,2000 : Strategies

Batch Delete is Slow :angry: - Batch Delete is Slow I am deleting some 200000 records from 5000000 rows. set rowcount 10000 while begin Delete from mytable where mytime<=123456 if...


SQL Server 7,2000 : SQL Server Agent

Start SQL Agent job using stored procedure - Hi, I'm trying to execute a stored procedure which will start a SQL Agent Job but I got "The SELECT permission...


Career : Employers and Employees

DBA, Data Scientist, Database Engineer. What's the difference? - I'm more interested in your general opinion. I'm not aware of any consensus on this subject but maybe I'm wrong?

This email has been sent to {user_email}. To be removed from this list, please click here.
If you have any problems leaving the list, please contact the webmaster@sqlservercentral.com.
This newsletter was sent to you because you signed up at SQLServerCentral.com.
Feel free to forward this to any colleagues that you think might be interested.
If you have received this email from a colleague, you can register to receive it here.
This transmission is ©2014 Red Gate Software Ltd, Newnham House, Cambridge Business Park, Cambridge, CB4 0WZ, United Kingdom. All rights reserved.
Contact: webmaster@sqlservercentral.com