Monday, May 20, 2013

SQL Server 2012 New Features - Contained Database

One of the problems in the existing database model is data being left behind when an application is moved from one instance to another.  Some data connected with an application, (for example, login information and agent job information) is currently stored within the instance instead of inside the database and When you move a non-contained database application from one instance to another instance of SQL Server, this data is left behind.

Later on, DBA's need to identify the data which was left behind and move it with the application to the new instance of SQL Server. This process can be time consuming and difficult.

This is where Contained Databases will be really useful.

Separates the database application from the management of SQL Server

Users can connect to the database without authenticating a login at the Database Engine level

Applications within contained databases can be isolated from other databases from the instance of SQL Server, on which they reside, simplifying development and management

SQL Server 2012 New Features - Pagination

Pagination in SQL SERVER 2012 is now very easy, previously ROW_NUMBER(), TOP, Order BY clauses need to be used to get similar output.

Using OFFSET and FETCH is bit faster than using ROW_NUMBER(), TOP, Order BY clauses and very simple to use for Ad-Hoc paging.

OFFSET: Specifies the number of rows to skip before it starts to return rows from the query expression. The value can be an integer constant or expression that is greater than or equal to zero.

FETCH: Specifies the number of rows to return after the OFFSET clause has been processed. The value can be an integer constant or expression that is greater than or equal to one.

Saturday, May 18, 2013

SQL Server 2012 New Features - Sequence Objects

Sequence objects in SQL Server 2012 are similar to the ones in ORACLE and DB2

Allows to generate numeric values specified at the time of creation of sequence

They are similar to the IDENTITY Column with a difference being IDENTITY column is table specific while sequence objects are table independent.

Sequence objects are much faster in generating numbers than IDENTITY

.NET User Group Meeting - Data Centric Applications using LightSwitch 2012

Going to present on Data Centric Applications using LightSwitch 2012 at the .NET User Group next month. Drop in and enjoy the session.

http://www.meetup.com/WelliDotNet/events/118974032/

Need to build a nice front end for a new or legacy database with minimum amount of code? Want to build an application that can be deployed as Windows or Web application?

LightSwitch, Microsoft's Rapid Application Development (RAD) environment for developing data centric Line of Business (LOB) applications. LightSwitch allows you to connect to various data sources (SQL Server, Oracle, OData feeds, SharePoint lists etc) and create front-ends using Silverlight or HTML5 client.

This session will take you through the architecture, scenarios on when to use and not to use, Silverlight and HTML5 front end demos and Deployment of LightSwitch applications.

Wednesday, June 19 2013

Saturday, May 11, 2013

The SQL Server instance that you are trying to connect does not include an installation of Data Quality Services.

The SQL Server instance that you are trying to connect does not include an installation of Data Quality Services. To finalize the installation of Data Quality Services (DQS) please run the DQS installer script.

This error occurred when trying to connect to the SQL2012 instance using DQS Client. Make sure to run the Data Quality Server Installer under Microsoft SQL SERVER 2012 > Data Quality Services and then try connecting to the SQL2012 instance again.

SQL 2012 New Features - Column Store Indexes

Stores data in columns instead of storing in rows

Enables accessing data in easy and fast manner.

Improves processing time of Data Warehouse (DW) queries by hundreds to thousands times

To improve query performance, you need to build a column store index on the fact tables in a DW.

CREATE COLUMNSTORE INDEX [Index_Name] on [table_name] (Column_Name)

Monday, May 06, 2013

Linked SQL Server database giving “inconsistent metadata” error

When I query the table via a Linked server, I get the following error.

Inconsistent Metadata

The workaround for this is to use OPENQUERY.

SELECT * FROM OPENQUERY(LinkedServerName, 'SELECT * FROM DBName.Schema.Table')

Analysis Services - A connection cannot be made. Ensure the server is running

Analysis Services Project - A connection cannot be made. Ensure the server is running.

Right click on the project in solution explorer and click on properties and select deployment on the left hand side and change the server name to the Analysis server name. (default server name is localhost)