Set the Deadlock Victim

This question was raised during our Whirlwind DBA Demos by Pearson Lanka DBAs at SSSLUG but we didn’t have a proper answer. So this faq tell you how you can set priorities for process during the deadlock.

Replication Without Creating a Snapshot

In replication, the standard way of creating a publisher is to create a publication with a snapshot, this article shows how to create a Replication without using a snapshot but with using a backup option.

Partition Columns in a Primary Key

Dinesh Karunarathne has started a new blog and first post is on Partitions Views.

He finished with his post with,

If the partitioned column is part of the PRIMARY KEY, data can directly be inserted in to the view.

I want to elaborate on this point further with a practical example I had.

I had a Order table as I came up with a design to partition order table with Date. This means I have say, three order tables, Orders_2010, Orders_2011 and Order_2012.

So the tables are created and you will notice that you have CLUESTERED PRIMARY KEY for OrderID,OrderDate columns. Traditionally, you would create CLUSTERED PRIMARY KEY only on OrderID.

Next is creating the partition constraints as shown below.

If you use DATEPART function instead of BETWEEN, this will not be possible to use as a partition view.

Each base table has a partitioning column whose key values are enforced by CHECK constraints.

The key ranges of the CHECK constraints in each table do not overlap with the ranges of any other table. Any given value of the partitioning column must map to only one table. The CHECK constraints can only use these operators: BETWEEN, AND, OR, <, <=, >, >=, =.

Next is creating the View.

If you create PRIMARY KEY only on OrderID, you will not be able to insert into the view and you will get the following error.

Msg 4436, Level 16, State 12, Line 2
UNION ALL view 'LogicalPartitioning.dbo.vw_Orders' is not updatable because a partitioning column was not found.

Also, you cannot have IDENTITY property for OrderID. If you use it, you will get the following error.

Msg 4433, Level 16, State 4, Line 2
Cannot INSERT into partitioned view 'LogicalPartitioning.dbo.vw_Orders' because table '[LogicalPartitioning].[dbo].[Orders_2010]' has an IDENTITY constraint.

Same feature but different order, why ?

I got this issue while creating an SQL Server Agent Alert.  When I want to select the database, it came to my notice that databases are not in order. I am not sure what the order is.

Same set of features are available in perfmon but databases are nicely ordered.

This does not apply only to log space used but for many other counters in alert.

Pareto Charts in SSRS

The purpose of a Pareto chart is to highlight the most important amongst a set of factors. For example, in quality control for a manufacturer, a Pareto chart can highlight the most common sources of defects and the highest occurring type of defect.This faq tells you how to create a Pareto charts in SSRS

History of SQL Server

Do you know the history of the SQL Server? Here is a nice little video on the subject.

SSMS BUG

I have seen this bug for a while and in several SQL Server version. Seems like Microsoft doesn’t have plans to fix this,

If you have a database in restoring mode, right click the database just keep the mouse over at the Reports option (NO you don’t have to click it ) you will be getting an error. Good thing is it won’t crash the SSMS.

Yes it is an unhandled exception.

SQL Server Injection

 

I wrote this article to sqlserverstandard magazine which was published in 2005 June (7 years ago, hmmm) . I don’t have the hard copy so every time some one asked, I need to search which will cost me 1/2 – 1 hr. So thought of publishing it here again.

Whirlwind DBA Demos by Pearson Lanka DBAs

I hardly write about my presentations and even I write it will not be more than just a copy and paste from the news letter I am getting from the www.sqlserveruniverse.com . But this time I have some special notes to share.

This idea was tossed by Gogula and then taken over by Pearson Lanka DBAs which have a team of six. So idea was to do six tiny little presentations during 1 hr.

So we decided about topics where are very much common to all of us in day-to-day work. Then every one was given seven minutes to present their topics.

We had two dry runs as this is a new experience for all of us. We were able to tweak our ideas and then cut-short if necessary.

At the live run, we made it happened. All six presentation were good and according to the feedback I received until now ( it is little over 3 hrs after we completed the event) it was a effective session.

It is mainly due to two facts  (I think),

* Since presenters had only seven minutes, we had to pass the message very clearly we selected some what simple but interesting topics.

* Since there are six presenters, audience kept the focus on each presentation.

All in all, it was a success event as far as presenters are concern and I will keep posting any updates I am getting in future.

Another important outcome of this event is we introduced two new speakers. Well it is another Dinesh, Dinesh Karunarathne which makes three Dineshes at SS-SLUG. I kept on asking him to do a presentation but he was able to make an excuse some or the other, but not this time.

Sanjeewa Jayawickrama is the other presenter. But I have to say this, I somewhat sabotage his presentation. I was the “secretary” to all presenters by running their scripts and moving the presentation slides. This is simply to save the time. However, I made few blunders while Sanjeewa presenting. Sorry !!!

We will be doing another round in coming months and hope to improve.

Let us know thoughts as well so that we all can improve.

Design A Hybrid Report in SSRS

 

Do you know what Hybrid Report is? It is some thing like this.

How do you design hybrid reports using SSRS. Here is an answer to this FAQ.

GROUP_CONCAT or LISTAGG

 

Well, this is not some thing SQL Server has. This is a basic function you get in ORACLE or in mySQL.

What it does.

Let us look at this data set. It has Color and ProductNumber where ProductNumber is the business unique key,

What I need is following output.

In  mySQL you can use simple function GROUP_CONCAT

in Oracle you have LISTAGG function

Source :http://oracleabc.com/b/archives/2100

In both cases it is one table scan which means cost is low,

Let’s move to SQL Server.

You don’t have a out-of-box function. However there is a workaround for this. Honestly I don’t know how this is working.

and the query plan is,

In case of SQL Server, you need to use DISTINCT and WHERE IS NOT NULL and more importantly there is two table scans, which means you might not use this for large tables. 

So has Microsoft SQL Server team has reserved this for future versions.

February 2012 meet-up of the SS SLUG

Announcing the February 2012 meet-up of the SQL Server Sri Lanka User Group (SS SLUG). We have a new type of session called the "Whirlwind DBA Demos" lined up for you this month as a treat.

Session #1 Why OLAP? How OLAP. with Gogula G. Aryalingam (MVP) We, as developers work with transactional databases on a day-to-day basis. These databases efficiently store data, and are also great for pulling out data.  But when it comes to hardcore reporting and analysis, we have a problem. One likely solution is OLAP. Join this session to learn why we need OLAP and how we can build and use it. Gogula blogs at: http://dbantics.wordpress.com	Session #2 Whirlwind DBA Demos by the Pearson Lanka DBA Team Whirlwind demos are short demos performed back to back on various topics. Watch the Pearson Lanka DBA Team blow your mind with demos on filtered indexes, recovering databases and analyzing deadlock graphs and more. The team: Sanjeewa Jayawickrama Dinesh Karunarathne Jude Pieries - http://judepieries.blogspot.com Susantha Bathige - http://susanthab.blogspot.com Dinesh Asanka (MVP) - http://dbfriend.blogspot.com Prithiviraj Kulasingham (MVP) - http://preethiviraj.blogspot.com
Where: Microsoft Sri Lanka 11th Floor DHPL Building No. 42 Nawam Mawatha Colombo 2 SRI LANKA When: February 15th (WEDNESDAY), 2012 6:00 PM Onwards Who: Everyone are welcome. Entrance: FREE Map: CLICK HERE FOR MAP More Information: SS SLUG Home Page Hope to see you there... SS SLUG and SQLServerUniverse.com)	January 2012 Meet-up Highlights PICTURES | KALEIDOSCOPE | DOWNLOADS

Rollbacking Truncated Table

 

Question is can you rollback truncated table. If so is there any specific recovery model.

Answer is yes you can rollback truncated table what ever the recovery model you have.

Here is the demo.

CREATE TABLE TblData
(ID INT IDENTITY,
Date Datetime DEFAULT GETDATE()
)

INSERT TblData
DEFAULT VALUES
GO 1000

So we have a table of 1000 records.

BEGIN TRAN

TRUNCATE TABLE TblData

If you do a count table will have 0 records,

Now you issue a ROLLBACK statement and you will be end up 1000 records which means rollback was successful.

How To Drop Users Who Are Owners or Databases and Jobs

When you want to drop users you need to en sure that those users are not owners databases SQL Server agent jobs, otherwise you will be confronted with errors.

This FAQ tells you how to remove those users from databases and Jobs.