SQL Server 2014 Free eBook

Microsoft is offering a Free eBook on SQL Server 2014.  Here is the link to download PDF file

Download Book: http://aka.ms/684751pdf

Happy Learning !!!

Harsh Shah

Get the list of tables which does not have Indexes

Here is the quickest way to know the list tables in SQL Server which doesn't have Indexes.

This will be very useful when we have large number of tables in our SQL Server database.


SELECT DISTINCT so.object_id,
                so.NAME
FROM   sys.indexes si
       INNER JOIN sys.objects SO
               ON SO.object_id = si.object_id
WHERE  SO.type = 'U'
       AND si.index_id = 0 


I am sure  there are many other ways to get the same list.. this is one of them.

Try Microsoft Products for Free in Virtual Lab Environment

Everthought of trying Microsoft Product for Free before installing it ? Or just wanted to learn about a new feature Microsoft recently introduced i.e. even before installing it?

Like this there are various reasons we wanted to try or to just learn something new.

If your answer is yes to above questions, then Microsoft is offering a complete Virtual Environment - with Hands-On-Lab for testing various product features which you can't do it considering various limitations like - Can't install on a production Environment, Don't have enough infrastructure ready to setup the data etc.. etc..

Microsoft is offering HOL (Hands On Lab) for various products and you can check it out here

https://technet.microsoft.com/en-us/virtuallabs?id=J+avMPtWlXk

There are system requirements needs to be followed to run the Hands on Lab which you can find it here

Check System Requirement :

https://vlabs.holsystems.com/vlabs/SystemRequirements.aspx

Hands on Lab

 

 

How to set Auto Commit OFF in SQL Server or Disable Auto Commit in SQL Server

Developers moving from Oracle to SQL Server often face issues with commit transactions. Because Oracle doesn't do AUTO COMMIT but SQL Server does and here developer faces the issues. Sometimes during testing we delete rows which we shouldn't be and can't ROLLBACK as these transactions in SQL Server are auto committed by default.


So the question is can we set this off ? Yes, we can and here is how you can do that.


There are two ways to achieve this.

You can  disable AUTO COMMIT at database level as shown below.

Option 1

1. Open SQL Server Management Studio
2. Right Click on Server  and Select  Properties
3. You will now be in Server Properties
4. Go to Connections page and check the options as shown below.

Option 2

1. Connect to Database Instance where you want to set Auto Commit off.
2. Go to SQLQuery window
3. Execute below command

SET IMPLICIT_TRANSACTIONS ON;

Once this is executed , you can use ROLLBACK to undo the changes or COMMIT to save the changes.