Browsing articles tagged with " SQL Server 2012"

Pour Some Data On Me

Apr 5, 2013   //   by Karen Lopez   //   Blog, Data, Fun, Snark, SQL Server  //  No Comments



Today’s SQL Server 2012 Anniversary question is:

I thought the image I created above might inspire you.

Follow @SQLServer on Twitter and answer their daily questions to win fame and prizes.


Pour Some Sugar On Me

Good luck!

Judging, Hiring, Prizes and Leeches… #bethenext

Jul 10, 2012   //   by Karen Lopez   //   Blog, Database, Fun, Professional Development, SQL Server  //  5 Comments

If you’ve ever been on the interviewer side of a interview, you know how tough it is to make judgements about someone and their work in a very short, unnatural situation and time frame.  I’ve been invited to participate in something even more exciting…a reality show Be The Next Microsoft Employee.  This web-based reality show is a contest run by Microsoft Learning for hiring a new SQL Server professional.  Four contestants, Alex, Chris, Michael and Stacy, compete in a series of challenges given to them by Buck Woody (@buckwoody | blog ) and guest judges like me.  Like all reality shows, these challenges aren’t going to be mundane, sit-in-a-quiet-cube-and-take-a-few-hours-to-plan-your-solution efforts.  I hear some even involve spiders, water, catapults and leeches.  Okay, that might just have been Buck scaring people again. Or not.

We will be judging victims candidates on the quality, completeness, professionalism and technical correctness of their solutions.  That part is just like in real life…except for cameras, producers and an entire professional film crew. And leeches, I think.  Just to make all more fun.

Anyway, the trailer for the reality show is up now.  Episodes will start going up on the Microsoft Learning website on 24 July. 


You Can Win, Too

Microsoft Learning is also running a prize contest for viewers.  You can win a free training class from New Horizons and certification exam vouchers from Prometric. This contest is open to nearly everyone, so not just US residents.  Register for a chance to increase your SQL Server skills. 


Stay tuned here for more exciting details, plus behind-the-scenes posts and photos. Seeing the contestants, knowing the judges…this is going to be one heck of a ride…

Grab this Free eBook on What’s New in SQL Server 2012 (288 pages)

Mar 19, 2012   //   by Karen Lopez   //   Blog, Database, SQL Server  //  No Comments


Ross Mistry (site | @RossMistry) and Stacia Misner (blog |@StaciaMisner) have written Introducing SQL Server 2012, a 288 page eBook and it’s free for download.  It’s currently available only as a PDF, but according to the publisher, .mobi and EPUB version will be available by 23 March. 

Ross covers the newest features and enhancements on the database administration side while Stacia describes what’s new with business intelligence, data quality, master data, analysis services, integration services and reporting services.

Once you get the book, you’re going to want to get your hands on the pre-release (RTM) version of SQL Server 2012.

So head over to , download the RTM of SQL Server 2012  and get started.  You should also check out the demos from the SQL Server 2012 Virtual Launch site for overviews of some of the newest features. 


Get Your Data Lovin’ On: SQL Server 2012 Virtual Launch Event

Mar 6, 2012   //   by Karen Lopez   //   Blog, Events, Professional Development, SQL Server  //  No Comments


On Wednesday, 7 March Microsoft is holding a virtual launch event for the newest version of Microsoft SQL Server to be released on 1 April.  You want to be there.  Yes, even you data architects, business analysts, project managers, enterprise architects and other IT professionals working in the upper rows of the Zachman Framework.  Why?  Because there are many new features in SQL Server 2012 made just for you:

  • Master Data Services
  • Data Quality Services
  • BI Semantics
  • Self Serve BI
  • Hadoop integration
  • New Data Tools
  • New datatypes, database objects and other design features like ColumnStore Indexes.

MVP Lovin’

You’ll have access to SQL Server MVPs who will be standing by to take your questions and chat about the new features we are most excited about. I’ll be working at these Topic Pods:

  • Application Development, Beyond Relational, Connectivity at 6 PM ET / 3 PM PT
  • Business Intelligence & Enterprise Information Management at 9 PM ET / 6 PM PT

If you register for the event, you can stop by and chat with some awesome SQL Server MVPs and me about some of the newest features of SQL Server. 

Prize Love

Unlike that other launch that is happening tomorrow, at the SQL Server Launch you can earn some really great prizes: cash gift cards, SQL Server Gear, and Xboxes.  So the more you watch, the more you engage, the more you can win.  Cash, Swag and Gaming.  What more could you want?

Lovin’ Just for You

While I think non-DBA types can get a lot out of the overviews of all the new features, I think developer and  data professionals will find these agenda items the most applicable:

Blazing-Fast Performance: A Technical Best Practices Tour with ColumnStore Index
Go behind the scenes with SQL Server engineers for a quick technical tour of the ColumnStore Index feature and an introduction to best practices for achieving astonishing performance with your ColumnStore Indexes.

Blazing-Fast Performance: Explore FileTables, Full-Text Search and Semantic Search for Faster, More Meaningful Results Across Unstructured Data
80% or more of the data produced and stored are so called unstructured documents and are often not stored in the database, but often need to be managed in conjunction with relational data. In order to facilitate the storage and management of unstructured data, SQL Server has evolved from storing blobs to providing a sophisticated integration with the Windows Filesystem and provides rich services over such data to provide an interesting and compelling application development experience. This presentation introduces the new unstructured data processing capabilities of SQL Server 2012 that provide full Windows application-compatible file management over files stored in SQL Server as well as the services to unlock the information in such data such as the extensions and improvements to Full-Text Search and the introduction of semantic similarity search.

Organizational Compliance: Using the New Security Features and Audit Enhancements to Increase Compliance Policies
Introduction to new security features in SQL Server 2012 such as User-Defined Server Roles and enhanced Auditing to increase organization compliance.

Peace of Mind: Quick Tour Through the New SQL Server Migration Assistant
In this session we will review SSMA modules that automate assessment, schema conversion, data migration and testing of Oracle and Sybase migrations to SQL Server 2012.

Unlocking the Value of Big Data with Microsoft Business Intelligence
Klout is a ground-breaking company that extracts signals from social networks to determine which people are most influential. Hadoop is a core part of how Klout captures and processes over 3 billion signals daily, however translating and analyzing these signals into meaningful information requires a scalable and cost effective business intelligence solution. That’s where Microsoft SQL Server 2012 comes in, delivering “speed of thought” ad hoc queries, with response times of under 10 seconds on 35 billion rows of data. Learn more about Big Data and BI best practices in this session.

Rapid Data Exploration: Empowering End User Insight Through Rapid Data Exploration and Visualization with Power View
Introduction to Power View, the new thin, self-service ad hoc reporting tool. In this session we will highlight its main features, scenarios, show a demo, and discuss its relationship to other products in the BI stack (SharePoint, Excel).

Rapid Data Exploration: Powerful Self-Service Analysis with PowerPivot
Get a taste of what’s new in PowerPivot, the self-service BI experience that enables information workers to build their personal and team BI applications within the familiar environment of Excel: This session will provide a quick introduction to PowerPivot, followed by demos highlighting the top 5 new modeling features delivered with the release of SQL Server 2012.

Managed Self-Service BI: End User Created, IT Managed
Introduction to the BI continuum – IWs using the familiar tools like Excel, IT retaining control and governance of the workbooks. We will show how IT can manage the system in SharePoint, and how self-service BI solutions transition to managed corporate BI solutions.

Credible, Consistent Data: Going Under the Hood with the New BI Semantic Model
Introduction to the BISM, position tabular and multidimensional types of models. We will show how easy it is to create a model in PowerPivot, show how Power View is leveraging it, and highlight how easy is it to go from an individual workbook to the enterprise IT governed BI solution.

Credible, Consistent Data: Improving Data Quality with the New Data Quality Services
No matter how brilliant your BI tools are, it’s garbage-in-garbage-out. This session will give an overview on the new Data Quality Services product in SQL Server 2012. Come watch this important scenario to learn how to position, sell, and demo the new DQS product.

Credible, Consistent Data: Empowering IW / Data Experts to Efficiently Manage Important Data with the New Master Data Services Add-In for Excel
Introduction to the improvements and new functionality in the SQL Server 2012 release, empowering IW to curate the organization’s important data, maintaining it accurate and relevant through its lifecycle, via easy model creation and validation.

Credible, Consistent Data: Introducing New Team-based Data Integration with Integration Services
There are plenty of new features for SQL Server Integration Services (SSIS) in SQL Server 2012 that make it a very compelling release for your customers. This session will cover some of the highlights of the release, including the new Change Data Capture functionality. We will demonstrate how to bring together key components needed to build an effective ETL framework using SSIS. This reusable framework will show off the new functionality and productivity enhancements for existing users, and present a compelling story for customers who have never seen or used SSIS before. We will cover the end-to-end lifecycle of an SSIS solution, including how to maintain and manage the framework once it has been deployed into production.

Scalable Analytics & Data Warehousing: Fast Track for SQL Server 2012
Fast Track for SQL Server 2012 has significant improvements, leveraging exciting new enhancements in SQL Server 2012 like the new ColumnStore Index feature. You will also see how Fast Track partners are working with the SQL Server team to transform the latest enhancements in server and storage technology into highly optimized data warehouse Reference Architectures.

Big Data Analytics with Apache Hadoop and SQL Server 2012
In this session we will explore Apache Hadoop on Windows Server and Azure and powerful insights we can generate integrating with SQL Server.

There are more sessions, so be sure to check them all out.  I hope you register and learn how you can Love Your Data with SQL Server 2012.  See you there.

New in SQL Server 2012 SEQUENCEs: Why They Aren’t Just for Surrogate Keys

Feb 16, 2012   //   by Karen Lopez   //   Blog, Data, Data Modeling, Database, DLBlog, SQL Server  //  5 Comments

imageStarting with SQL Server 2012, database designers will have the ability to use SEQUENCEs to generate numbers, such as for populating surrogate keys (primary Keys with no meaning). Sounds like the IDENTITY property, right?  It’s not quite the same.  In SQL Server (and other DBMSs), the IDENTITY property is a characteristic of a column in a table.  A database designer can set this property up to automatically generate a value in that column and specify certain features such as what number to start with and what number to increment by.  These properties are set for a specific column and only one column in a table can have this identity property.  That’s why IDENTITY is almost always used as a surrogate primary key on a table.

To create a column with the IDENTITY property in SQL Server 2012:

    [ database_name . [ schema_name ] . | schema_name . ] table_name 
    [ AS FileTable ]
    ( { <column_definition> | <computed_column_definition> 
        | <column_set_definition> | [ <table_constraint> ] [ ,...n ] } )
    [ ON { partition_scheme_name ( partition_column_name ) | filegroup 
        | "default" } ] 
    [ { TEXTIMAGE_ON { filegroup | "default" } ] 
    [ FILESTREAM_ON { partition_scheme_name | filegroup 
        | "default" } ]
    [ WITH ( <table_option> [ ,...n ] ) ]
[ ; ]
<column_definition> ::=
column_name <data_type>
    [ COLLATE collation_name ] 
    [ NULL | NOT NULL ]
        [ CONSTRAINT constraint_name ] DEFAULT constant_expression ] 
      | [ IDENTITY [ ( seed ,increment ) ] [ NOT FOR REPLICATION ] 
    [ ROWGUIDCOL ] [ <column_constraint> [ ...n ] ] 
    [ SPARSE ]

I’ve highlighted the syntax that applies the IDENTITY to a column.  SQL Server will know which column has the identity property (and you can only have one of these columns in the same table).  Notice that there are only three options for an IDENTITY.

To create a SQUENCE in SQL Server 2012:

CREATE SEQUENCE [schema_name . ] sequence_name
    [ AS [ built_in_integer_type | user-defined_integer_type ] ]
    [ START WITH <constant> ]
    [ INCREMENT BY <constant> ]
    [ { MINVALUE [ <constant> ] } | { NO MINVALUE } ]
    [ { MAXVALUE [ <constant> ] } | { NO MAXVALUE } ]
    [ CYCLE | { NO CYCLE } ]
    [ { CACHE [ <constant> ] } | { NO CACHE } ]
    [ ; ]

Notice that there is no TABLE or COLUMN referenced in that TSQL.  You are just creating a SEQUENCE.  SQL Server will have no idea what you are going to use it for or where it will be used.  In fact, you could create a SEQUENCE and never use it. Notice there are several more options for a SEQUENCE.

SEQUENCEs are separate objects in a database and can be used to populate more than one column even across several tables.  Because they aren’t bound to a table in any way, they can be used in ways that IDENTITY columns can’t be used.  For  instance:

  • One SEQUENCE can be used across more than one table. For instance, a Control Number or Document Number on several tables.  I’ve seen this used in other DBMSs to generate document numbers across subtypes, say Purchase Orders, Shipping Notices, Return to Vendor Numbers, etc.  There’s a requirement that they be unique across all types of documents.  This could not be done with IDENTITY.
  • Using a SEQUENCE allows tables to have more than one auto-generated number in multiple columns. Think of Order Number and Cancellation Number, both on the Order table. You can’t do that with IDENTITY.
  • Since sequences are generated completely outside a table, you can chose to store the resulting value in a different format, say VARCHAR or CHAR. Or, heaven forbid, you might want to append data to it or do other kinds of evil to it before storing it.
  • Since sequences are generated completely outside a table, you can store the results in a NULLable column.

I think the confusion sets in because we tend to think of auto-generated number as useful only as surrogate keys. But we have real data needs for generating numbers other than just row identifiers, even if that’s the primary use for this. In fact, one could have a table with a PK that uses IDENTITY and an unlimited number of columns that use a SEQUENCE to set their values.  Of course, SEQUENCEs can be used for surrogate primary keys, too.  The existence of SEQUENCEs gives us another option over IDENTITY.

So SEQUENCE is a feature that just generates numbers based on some characteristics. A designer has many ways to use the number once it is generated and tables are not dependent on that method to get that number, nor do they even “know” about how that value was provided.

This separation of the COLUMN from how the data is populated is a feature of using SEQUENCEs. How one uses this separation depends on the technical and business requirements for the data.

Get Hands On with SQL Server 2012 – Virtual Labs

Feb 3, 2012   //   by Karen Lopez   //   Blog, Data, Database, Professional Development, SQL Server  //  1 Comment

Want to get your hands on SQL Server 2012 right now?  You can be up and running without needing a server or to install SQL Server locally.  Just visit SQL Server Virtual Labs (use IE)  and choose a lab to get started.  The labs run in a virtual machine, so these aren’t just slides and a demo.  This is real, hands-on working with the tools.  Here’s your chance to get up to speed on SQL Server before the other 99%. Smile.

You can get real experience with new SQL Server 2012 features such as:

  • AlwaysOn, SQL Server’s new High Availability feature.  I have to say this is fun to play with.  Pull the plug and watch SQL Server gracefully fail over.  Like nothing happened at all.  Monitor the status of all the AlwaysOn components.  Very cool stuff here.
  • Master Data Services, SQL Server’s new feature for managing reference and master data.
  • Data Quality Services, a new feature for helping you Love Your Data even more.
  • Sparklines and Data bars, new data visualization features.
  • Columnstore Indexes, SQL Server’s new feature to make queries just fly.
  • Spatial and Location Services for mashing your data up with location based services.




While you are running the VM set up, register for the SQL Server 2012 Launch events coming in March in the US.  I hope to make it to one or two of those.

Subscribe via E-mail

Use the link below to receive posts via e-mail. Unsubscribe at any time. Subscribe to by Email