Tag Archives: SQL Server 2014

Back From Vacation Syndrome and SSMS Shortcuts

Was just back to United States from a long vacation and started to work. The first two days were filled with replying to all pending emails and settling up issues reported while I was away. Slowly was out of jet lag and my routine work life started. My client did gave me a new requirement to build a report. Came to my cubicle, opened SQL Server Management Studio (SSMS) and started building the query for it. With me being away from work for few weeks, I thought I might have forgotten the tables, databases and relationships which exists between different database objects.

To my surprise, I didn’t forget any and I painted the whole picture of the report from different tables in my mind when my client gave the requirement. While building the query I noticed something strange, I have been stumbling with keyboard shortcuts I use with SSMS and just took a breath and yelled at myself (How could I?). Shortcuts are cool feature with SSMS and I love using them. I know I should get into my notes and brush up my shortcuts.

This time I wanted to know why did I forget them and started googling about it. The one word answer is Brain Plasticity. The ability of our brain to be flexible and adapt with new changes is called Brain Plasticity and our brain survives by forgetting too. The article I read quoted human brain doesn’t need to forget intentionally and any changes in the outside environment has an effect on our brain and it will make us forget to store new events. Think of it in this way, If I were to think about SQL and databases at my vacation it might have gone terrible right?, luckily I wasn’t and my brain was on vacation mode and now am back to work it will slowly store my work and daily life related data and events and might forget vacation mode and it keeps working!!!Amazing is our brain!!!

I just called it as Back from Vacation Syndrome:)

Here are the shortcuts I brushed up from my notes and would like every SQL Developer to be aware of it and forget only when they are on vacation:P

Ctrl + U  - To Change database connection

Ctrl + F6 – Toggle between query windows

Ctrl + F5 – To parse the code

Ctrl + R – To Toggle Result Pane

Ctrl K + Ctrl C – To Comment the code

Ctrl K + Ctrl U – To Un Comment the code

F8 – To view Object Explorer

Shift + Alt + Enter – To view in Full Screen

Ctrl + Shift + U – Converts the selected code to Upper case

Ctrl  + Shift + D – Converts the selected code to Lower case

Ctrl + Shift + Home – Selects the code from current cursor location to the beginning of query

Ctrl + Shift + End – Select the code from current cursor location till the end

Share with me if you find some more shortcuts are essential.

#JunaithHaja

Download and Install Adventure Works 2014

Microsoft has launched Adventure Works 2014 database for its SQL Server 2014 version. Until 2012 Microsoft provided sample databases in format of mdf and ldf file downloads, a developer will download and attach the mdf and ldf file to install Adventure works 2012. It has changed the style completely with 2014, do watch the video to know about it.

Script:

USE [master]

RESTORE DATABASE AdventureWorks2014

FROM disk= ‘C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\Backup\AdventureWorks2014.bak’

WITH MOVE ‘AdventureWorks2014_data’

TO ‘C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\AdventureWorks2014.mdf’,

MOVE ‘AdventureWorks2014_Log’

TO ‘C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\AdventureWorks2014.ldf’

,REPLACE

References and Dowloads: https://msftdbprodsamples.codeplex.com/releases/view/125550

 

UPDLOCK, HOLDLOCK AND NOLOCK in SQL Server 2014

I got this question in an interview where the interviewer asked me to Define any three locks I used in my career and their practical purpose.

I answered him, I have used UPDLOCK,HOLDLOCK AND NOLOCK in my career and started elaborating as follows,

UPDLOCKS are associated with DML statements like Update. It is a shared lock and needs to be converted to an Exclusive lock before the original operation occurs. Also the lock occurs at row level. When a user executes an Update statement it assigns a Shared lock on all the scanned rows in the table, when it finds the row to be updated it converts it to an Exclusive lock and update operation occurs.

HOLDLOCKS are used at table level it prevents the table from being updated by any other DML transactions like an insert or an update. HOLDLOCKS are preferable to use in situations where we need only COMMITTED READS.

NOLOCKS are opposite of HOLDLOCKS and when they are issued at a table level, it allows the user to access/read the data from a table whilst some update/ insert operations are running at the back. NOLOCKS may produce UNCOMMITTED READS and are preferred to use at the Production Environments so that the real time update is not hindered.

The Interviewer asked, How do you see the current locks in a Server?

I told him I will use the Stored proc SP_LOCK

Then he asked me how will you kill the locks?

I answered, I will use KILL Session_ID command.

Then he asked me, Is there a report you can see the status of the Locks?

I answered him, There is one in the SSMS at database level, when you right click the database and view Reports.

He asked me, Whats its name?

I told him, I can’t think the name on top of my head but will be able to tell it when I open SSMS.

He said, Its “Top Transactions By Lock Count”

I was like Gosh!

#JunaithHaja

Memory Consumption Report in SQL Server 2014

One day I had an weird error related to memory saying “There is insufficient system memory  to run the query“. I know  two things for sure when this error happened, First is this issue should be something related to the Systems Memory.Second is all data regarding memory should be residing in the sys tables.As a matter of fact I don’t want to dig into sys tables and write queries and want to view it as a report. Was researching across SQL Server Management Studio and came across this Standard Memory Consumption report which helped me on that day.

Its a server level report and you Right Click at the Server level–>Reports –> Standard Reports–>Memory Consumption.

Memory Consumption in Sql Server 2014

The report looks like,

Memory Consumption / Usage Report in SQL Server 2014

Had to clear one of the memory components to fix the issue.

Hope it helps!!

#JunaithHaja

Configure Delayed Transaction Durability in SQL Server 2014

After reading my previous article on Delayed Transaction Durability,one of my Twitter follower posted me  this question. How do we configure our server to work with Delayed Transaction Durability? Does it comes by default?

First thing to note, Delayed Transaction Durability can be applied only at Database level and not at Server level. Here is how you configure Delayed Transaction Durability in a SQL Server 2014.

Step 1: Open your SQL Server 2014 in SSMS and Right click the database you want to set this feature and open the Properties window.

Step 2: Go to Options and Set the Delayed Durability property as Allowed under Miscellaneous section.

Delayed Transaction Durability in SQL Server 2014

Now you are all set:)

Coming to his second question, By default Delayed Transaction Durability comes Disabled with SQL Server 2014 and we can set the property as “Allowed”, “Forced” or “Disabled”.

Hope it Helps!!

#JunaithHaja

 

Why you should upgrade to SQL Server 2014:

I shared my blog with my brother Zulfi Haja, he is a SQL Server Consultant too and works for an investment bank in London,UK. Having reviewed the top 5 features of SQL Server 2014, He shot me an email saying, Bro the effort you put on summarizing the top 5 features was worth reading and it’s a neat approach, would definitely recommend it to my colleagues, but I am still not convinced for an upgrade to SQL Server 2014. Can you help?

I thought only a Tech Evangelist from Microsoft will be able to answer him. I took the question from him with a little hesitation and replied back saying, “I don’t know how long will it take but surely will get back to you once I find a convincing answer”. He said, All the best!!

Was looking out for users experience with Enterprise 2014 version across my network circle and over the blogs, it was of no use. In the beginning of May I joined a local SQL Server User group and they invited me for their monthly meeting. To my surprise there was a presentation about In Memory OLTP and SQL Server 2014 by a Product Specialist from Microsoft. I said to myself, I found the guy and this Product Specialist and Tech Evangelist should be the one to answer my question. His presentation was very convincing enough and I found the answer from his talk. After the presentation the Q&A session was open and I asked him the same question and he gave me the expected answer from presentation. It is SQL Server 2014 can now support Tier1 application.

Let me elaborate this, if you have hanged out with a developer crowd from different domains they have this pre destined notion with them where they will give you an analogy, Oracle is like a business class of a flight and major critical applications are built using it and SQL Server is like an economy class where middle tiered companies uses it. In fact, of all my projects 60-70% of them will be the front end applications supported by Oracle and we had the data replicated to us in SQL Server environments for our reporting and analysis purposes. With SQL Server 2014 that pre destined notion is no more and as per my conversation with the Tech Evangelist, SQL Server 2014 will be good enough to support Tier1 applications and compete with Oracle supported systems because of its new DB engine design. Also he hinted out saying, if NASDAQ uses SQL Server why not your applications?

So Oracle folks better be aware of it :)

#JunaithHaja

Subversion in Visual Studio 2013 using TortoiseSVN and AnkhSVN:

I would highly recommend a developer to understand the concept of Subversion before accessing any projects stored in a repository.

Subversion is a generic term and developers uses this technology to store their source code, .proj, .soln,web pages and other project related files in a repository. Developers working in a team will be able to download the files and upload the files to the repository with any code related changes thereby providing a single point of access. Subversion belongs to Software Configuration and Change Management field. Different subversion products are available in the market like ApacheSVN, TortoiseSVN, etc. We would use TortoiseSVN for our below illustration.

TortoiseSVN is an open source subversion product developed by Collabnet. We create our repository using TortoiseSVN software. We use the AnkhSVN plugin to access the repository through Visual Studio. In other words, AnkhSVN is an add-on used to integrate Microsoft Visual Studio and Tortoise SVN.

First download and install the latest version of TortoiseSVN 1.8.7 and AnkhSVN 2.5.124751. The installation is straight forward process.

You can verify the installation of TortoiseSVN by right clicking your mouse on any Windows Explorer and you will be able to see options for SVNCheckout and TortoiseSVN in it. The AnkhSVN installation can be verified by opening the Visual Studio and click File, you should see an option for Subversion.

In most real time scenarios a repository will be already existing, so let’s jump on How to access an existing repository.

Copy a Read only Solution from Repository:

If you want to get a read only copy of a solution file,     

Step 1: Create an empty folder at your desired location. Let’s call it SQLCASTReadCopy.

Step 2: Right Click the folder, Select TortoiseSVN and Click Export

Export a Project File from Repository using TortoiseSVN

Step 3: Enter the location of the Solution/Project file in the URL Repository drop down   and the location of the above created folder SQLCASTReadCopy in the Export Directory field and click OK, you will have a copy downloaded from the repository to the above folder.

Export an SSIS Solution from Repository using TortoiseSVN

Now you can access the downloaded copy from your local machine and you will not be able to write any changes to the repository.

Copy a Read and Write Solution from Repository:

Step 1: Create an empty folder at your desired location and call it SQLCASTWriteCopy

Step 2: Open the Visual Studio 2013, Click File -> Open -> Open from Subversion

Step 3: Enter the location of the solution/project file at the repository in the From field of the dialog box and enter the location of above created local folder location SQLCASTWriteCopy from Step1 in the To field and Click OK.

Now you will have a read/write copy downloaded to the above location.

Your changes can be written to the repository by clicking COMMIT and UPDATE in the Pending Changes window of Visual Studio 2013.

In any of the above process if the dialog box prompts for your NTlogin and password, enter it and it will validate with the Active Directory.

Hope it Helps:-)

 #JunaithHaja

Delete last two characters of a Column in SQL Server 2014:

One of my colleague asked me this question, Is there a way to generalize a function to remove last two (n) characters from a column if the column doesn’t has a fixed length.  Yes it can be as below

Syntax: LEFT (ColumnName,LEN(ColumnName) – n)

The function calculates the length of the string and removes last n characters using LEFT() function.

Here is the kicker I wanted to share with you, Is it possible to flip the above function using RIGHT() to remove first n characters from a Column(string), yes it is possible:-)

Syntax: RIGHT (ColumnName,LEN(ColumnName) – n)

I made up a sample data using AdventureWorks - Transaction table in my SQL Server 2014 version. Imagine you have the TransactionID and ProductID concatenated as TranProdID column. Removing the last two characters will give you the TransactionID and removing the first three characters will give you the ProductID.

The query can be written as

SELECT  TranProdID, LEFT(TranProdID,LEN(TranProdID)-2) as TransactionID,                       RIGHT(TranProdID,LEN(TranProdID)-3) as ProductID                     FROM [SQLCAST2014].[Production].[Transaction]

Here is the snippet from my machine,

Remove Last Two Characters from a Column

Connect Any Database Permission in SQL Server 2014:

Microsoft has introduced three new permissions namely Connect Any Database, Impersonate Any Login and Select All User Securables in its SQL Server 2014 version. The CONNECT ANY DATABASE permission seemed more interesting to me from a developer perspective as we all have come across situation if we had this permission we could have avoided a sad face:-(. In our daily life we work across different servers and databases but sad reality is that the developer doesn’t has permission to access all databases across every server he works. Classic example is a developer working for marketing team having access only for a Marketing database and may not have access to Finance database. So what happens if he accidentally hits the Finance database? A dialog box opens with a message saying “The database is not accessible” this caused a sad face in me many times. The CONNECT ANY DATABASE permission is the solution for it by which it will allow the developer to connect any database in the server and will not have him access to the underlying DB objects thereby preventing the dialog box.

Let’s try demonstrating it.

I created a SQL Server Authentication login for this demonstration and name it as SqlCastTest. Its default permissions can be viewed by Right Clicking the Login and opening its Properties window under tab Securables. The Effective permissions could be seen like below.

Default Permission

Let’s try opening the database SQLCAST2014 through this login in object explorer and it will pop an error message as follows.

Error Message in SQL Server 2014

 

Let’s give CONNECT TO ANY DATABASE permission to our SqlCastTest login by executing the below script

USE [MASTER]

GO

GRANT CONNECT ANY DATABASE TO [SqlCastTest]

GO

Now let’s try connecting to the same database and it will let us connect to it as follows.

Connection Established through Connect Any Database Permission

 

To verify our drill let’s check the new permission available at Securables tab in Login,

Connect Any Database Permission Check at Login Level

See the CONNECT ANY DATABASE is effective and associated with our test login like below.Keep in mind, CONNECT ANY DATABASE will not allow access to the DB objects and it’s only a connect permission to the existing and future databases in the server.

Hope it helps:-)

#JunaithHaja