TIP - To fix orphan uers

Orphan Users

When a database is moved from one server to another "Orphan users" is one of those little things that tend to create problem in smooth transition. 

There could be 2 reasons because of which Orphan users might get created

• Associated Login for the user itself isn't present on the server where database has been moved 
• Mismatch between SIDs of USER of the database and the LOGIN of the server

When the database user looses it's association (SID mismatch) with Login it becomes orphan and this uncoupling leads to LOGIN loosing its privileges on that database.

In this tip we will try to build a script to address second reason mentioned above

sp_change_users_login 'report' - lists the orphan users from the databases

master.sys.server_principals / master.dbo.syslogins - lists the logins present on the server. 

Interview Question - To populate a table without specifying values in the INSERT statement

This one should be quick and most of must be aware of it. 

Following question was asked to one of my friend in an interview for the post of Sr. Software Engineer

How would you populate following table with 5 records without specifying values in INSERT statement ?

The table structure was as follows

IF OBJECT_ID('tempdb..#default') IS NOT NULL
DROP TABLE #default

CREATE TABLE #default
(
id INT IDENTITY(1,1),
software VARCHAR(20) DEFAULT('microsoft'),
osType VARCHAR(10)
)

GO

Interview Question - What will be the output of .... ? LEN() function

This blog is about what happens when we do not pay attention to Microsoft documentation ... 

Recently, I had a simple question fielded to me which made me look STUPID.. Well being stupid is my right as a human being :) but making me look one is a different thing altogether ... I hope you guys are with me on this :) .... Here comes the bazoooooooooka 

What will be the output of following SQL statements ?

• SELECT LEN(' ') as 'single Tab' 
• SELECT LEN('    ') as 'multiple spaces' 
• SELECT LEN('    ') as 'tab followed by 3 spaces' 
• SELECT LEN(' ') as '3 spaces followed by tab' 
• SELECT LEN('   a') as '3 spaces followed by char' 
• SELECT LEN(' ') as 'space tab space' 
• SELECT LEN(' a ') as 'space char space' 

Simple isn't it ....

Well if you guess the output of each of these correct then BRAVO, if not then you'll know how does it feel when simple thing such as this stumps you..

Query - to script the steps to move user database files around

Many times we don't pay attention while creating a user database and end up creating database files where they are not supposed to be.. in such scenarios often we find ourselves moving file across drives so as to arrange them to match our settings (or liking's ?) 

To move files of the online user database, you have to perform certain steps in the specific order and it is documented quite nicely in MSDN

This is what we do

• Take DB offline
• Move files
• Modify old file location to the new one
• bring database online

The following script generates the statements for the  steps mentioned above.. Please note that on my server I wanted to move files for the databases with a name starting from test.. you will have to change the WHERE clause so that the scripts are generated for the databases of your choice

Query - to find specific column across tables and across database

Here is the situation my friend came up with -

list all the databases and tables with containing one specific column

he required this info because his project was under migration and they were looking for this kind of information .. The query shared here can be modified to find Most commonly used column name across databases

Query does 2 things

• Loops over databases having test at the start of their name
• Finds out the column across tables using Information_schema.Columns

Query - to find objects containing specific word

Sometime back we did one exercise where our objective was to find out the objects which don't work any more .. 

for example suppose we had created a view v_tab with a simple select statement fetching abc column from XYZ table .. later on say after 3-4 months that abc column got renamed to lmn but the view v_tab referring to table column as abc never got updated so that view now became obsolete .. You see what happened there and mind you this happens quite often when attention is not paid while deploying new changes to the database objects.. 

we used following approach to address this

Query - to peek into Maintenance plan History

Today, it's about maintenance plan and it's history

Often we run into situations where jobs executing maintenance plan does not show actual error that caused the failure. And more often than not in such scenarios browsing through maintenance plans history works like a charm.. 

Recently a friend of mine returning from his long vacation and ran into similar situation where his sql agent job didn't provide me sufficient information about the root cause of failure. And to make matter worse they had limitation put on the agent history retention which did not help either. Thankfully he had error notification setup which helped him listing out the names of the job that had failed.

Given below is what I wrote to help him out .. there might be easier way than this so fill me in if you happen to know about it 

Interview Question - Write the SQL script to delete/Truncate data from all the tables of the specific database

Recently, I came across this question where candidate had to write a query to empty all the tables from the specific database. Prima facie it feels like a cakewalk for the candidate but believe me it's not. Why not ? Because one must consider the relationships between the tables while writing the query for this question. It could be multilevel hierarchy that one needs to identify before even thinking about deleting the data from the child tables.

So the question gets divided into 2 parts 

• Identify the relationship hierarchy
• Start deleting the data from the bottom i.e from child tables to the parent tables

Now the real question, is it really necessary to identify the hierarchy between tables ? Isn't there any other way to perform this ? 

Query - To list the jobs and their steps from where SSIS packages are getting executed- SQL 2008

Recently, I was asked to write a SQL query to fetch the information of the enabled jobs and their steps where SSIS package (stored in SQL server) is getting called directly.

As most of us know information related to jobs and other SQL agent related stuff is stored in the MSDB database in SQL server. There are quite a few tables which comes handy while trying to fetch the job details and honestly, I did not know where do I look for the required information. While browsing for the data I got to know about the following tables which provides all the necessary information 

• sysjobsteps - Stores the job step details 
• sysjobs - Stores the information related to jobs

Note - I noticed that when the package (stored in SQL server) is called from the job step, "command" column in the sysjobsteps starts with "/SQL". Following query is based on this observation only and I could be wrong in assuming that hence please feel to correct me in case I'm wrong. Also if there exists an easier way to find the job-package details please let me know..  

SQL Script

Tip - Query to list the accounts running the SQL services

It will be quick this time around ..

Have you guys ever encountered a situation when you badly had to know the account name under which some remote SQL server and SQL server Agent services are running. 

Ahh you did, great.. So what did you do ? went to SQL box and found out it using services.msc or SQL config manager ?  alright Good. 

Let me make situation a bit more challenging ..

What if you can not remote into SQL BOX because of limited access? Now what will you do ?Pretty frustrating isn't it. Just to know the account name for the SQL service you have to raise a ticket with IT and wait for them to respond. 

But what if I tell you, when you can query that server fine why bother jumping onto the server itself? Yes,it can be achieved using simple SQL statement. Isn't that awesome? if you ask me.. YES, it is.

Starting SQL 2008 R2  MICROSOFT has solved this problem for us and presented 

• dm_server_services - The dynamic management view to report status information about the services

Well how about that.. let us see it in action 

MS-CRM - Query to list the Activity record distribution across Business Units in the CRM database

The MS-CRM contains many entities of type Activity. Few of the most widely used activities are PhoneCall, Email, Appointment etc..

When the activity record is created in MS-CRM, it internally loads 3 tables with the provided information

1. Base table for the Activity. Ex. - AppointmentBase
2. ActivityPointerBase
3. ActivityPartyBase

ActivityPointerBase table contains one record for each activity record that is created in the MS-CRM. While ActivityPartyBase table hols the information of the parties (participants) for particular Activity record.

Following tables are used in the query to find the distribution of the activities across 

Interview Question - What is the difference between Stored Procedures and Functions ?

A very common question, usually asked when the candidate is applying for SQL developers position. 

I have listed few of the differences that I can think of right now. 

Please feel free to let me know 

• In case you know any additional differences
• You think the difference listed below are not correct

Interview Question - How to get the count of rows for each table of the particular database ? How.....

This is simple yet important question which may feature when someone is interviewing for SQL developer position.  

The question goes like this 
"How to get the count of rows for each table of the particular database ? How many ways you can think of to fetch the details ?"

Again second part of the question made it interesting because now interviewer wants to understand your knowledge about different ways of Looping in SQL server ?

One obvious answer for this question would be using CURSOR but I'll leave that to you to write instead I'll try to use in-build looping mechanism that comes handy in this situation.

Interview Question - How to generate values from 1 to 1000 ? without using .....

This question comes across as a straight forward question but can prove lethal to gauge the turnaround time. It also checks the ability of the person to reach the destination when virtually all the doors are closed.

Well the complete question is

" How to generate values from 1 to 1000 without using WHILE loop and Cursor ? and Using single SELECT or block of code"

The question became interesting with the last part because most of us would have thought about using loop/cursor but that is forbidden.. and we also have to use single SELECT or block of code .. 

Okay.. What does that mean ? Is it some kind of a clue to answer this question ? 

MS-CRM - Query to list the TimeZones for the Active Users

In MS-CRM database as we know end user can set the timezone of his choice for himself / herself. And after that all the data that end user sees on CRM UI would be displayed in the timezone that he/she has opted for. 

Now, the question is where does CRM stores the TIMEZONE related information? hence comes the TimeZoneDefinitionBase table to rescue. 

It holds the info such as 

• TimeZoneCode - Unique Integer value representing each timezone
• UserInterfaceName - Displays info in-terms of UTC +/- Hrs
• StatndardName - Gives away the global standard name for the TimeZone that everyone can understand

Interview Question - What will be output for the query ?

Today's question deal with somewhat neglected part of the SQL server. I say Neglected because it is very rarely used.

What will be the output for the following query 

DECLARE @TEST TABLE
(
COL1 SMALLINT,
COL2 SMALLINT
)


INSERT INTO @TEST
SELECT 13,76

SELECT 
COL1 & COL2  AS [&]
,COL1 | COL2 as [|]
,COL1 ^ COL2 AS [^]
,~COL1 AS [~]
,~COL2 AS [~2]
,~ COL1 | COL2 & COL1 ^ COL2 AS Precedence
FROM @TEST

Tip - IN clause other way around

How to do you write a query to fetch the records from a table where particular value is present in 2 different columns or in either of them ? 

Phewww.. pretty simple isn't it ?

Let us give it a shot...

DECLARE @TAB TABLE
(
ID SMALLINT,
NUM SMALLINT
)

INSERT INTO @TAB
SELECT 1,1 UNION ALL
SELECT 2,1 UNION ALL
SELECT 3,3 UNION ALL
SELECT 1,4

With reference to the question raised @ the top we have to write a query to find the records containing 1 in either of the columns from @TAB or featuring in both of them.. 

Interview Question - What is the difference between Table Variable and Temp Table ?

Few days back, one of my colleague asked me about the difference between Table variable and Temp table. Although I did manage to tell him few difference that got him going but that answer did not satisfy me. So to settle the nerve I ended up reading about them and got to know few more differences between table variable and temp table. 

Then it occurred to me that it's good candidate for the blog entry as this question fielded quite often during screening process. 

Below given screenshot shows the difference between the Table variable and Temp table

NULL with NOT IN () caluse

Most of you must be aware of the fact that NULL value causes more problem than anything else database world. Well here is an example which may or already have ruined few minutes @m some point in your career.

NULL means nothing then Why on the earth should we care about it ? well it is one of the most important thing that DB developer should care or you'd be doomed for unexpected results.

Let's see one example of NULL causing few of us headache.. 

Interview Question - What is the difference between TRUNCATE and DELETE ?

I am not sure why this question is fielded in the interviews but over the last couple of years I have seen/heard it enough times to be made as a blog entry..

The question is simple - What is the difference between TRUNCATE and DELETE ? 

Many of you must be aware of the most of the differences that are listed below but it might turn into worth reading if you find something new