SqlServer Begin's

So the usual story, the query works well for small data sets but does not work optimally for large data. What are my options ?      Recompile the view/function/procedure Update states Add a optimize @Variable for unknown Look for missing indexes Rewrite the query The low hanging fruits Generally, the top two items would allow you to get over the hump but without a optimize for hint you can’t get over the parameter sniffing problem and without the index you would keep running into the same problem over and over again. Long term Rewrite the query to avoid the the index and if you get really lucky it may be possible to avoid the optimize for hint. How i went about solving the problem The fat line indicated where the problem existed, but what would it take to resolve the  issue ? The table (let’s call it DetailTransactionLineItem just because it held detail to detail table  ) had only fraction of the data for the @Pratmerter Us...

   What a felling, After weeks of pondering and been pestered by Teresa Brooks i have started the machine learning course by Andrew Ng. just finished installing Octave on my ubantu VM.  Don't have much to notes to share at the moment but few links that was helpful when setting up the VM Setting up the VM and Installing Ubantu  https://www.youtube.com/watch?v=QkJmahizwO4 To install Octave on Ubantu http://linuxpitstop.com/install-gnu-octave-on-ubuntu-15-04/ Now lets hope i will make some honest effort to submit some of the course assignments

So we are moving to Windows server core. What do you know, the SQL DBA's life has become bit more complicated and the SQL DBA's need few more tool added to their belt. Just wanted to share my first  .ps1 . The objective of the script was to identify the space usage of the server participating in HADR replication. Few things to Note  - The user executing the script should have permission to read data off the "Sys.dm_hadr_availability_replica_cluster_nodes" DMV - Should have a  folder "ServerDetails" in the desktop or should have the permission to create the folder under the ps1 user context.  - A text file will be created in the "ServerDetails" folder which will be automatically created. I am sure there will be more versions of this PS1 in the next few weeks. But for the moment, i want to share my success :)  Cheers yo  #--=============================================================================== #-- Input to g...

It is mentioned in the SQL world that you can’t insert a value into timestamp/rowversion column. But you actually can, I was surprised to find there was no record of this workaround on the internet. That said, I looked no more than 15-20 minutes. This was tested on " SQL Server Edition : 2012 Enterprise". The problem with timestamp is that it doesn’t accept an explicit value to a table Ex.   Create table #Customer ( ID int ( identity(1,1) , Name nvarchar(100), Address nvarchar(1000), LastestVersion  timestamp ) Insert into #Customer (Name , Address , LastestVersion) Select top 500 Name , Address , LastestVersion From  Actual_Customer The above will error out complaining that a explicit value can’t be added to the #Customer table due to rowversion. However if you define the table on the fly with “SELECT INTO”, SQL server is tricked into believing the timestamp column is another jack datatype. SELECT top 500 Name , Address , LastestVersion I...

It's been some time since i added anything to the blog and a lot has happened in the last few months. I have run into many number of challenging stuff at Xero and spread my self to learn new things. As a start i want to share a situation where I used a dacpac to compare the differences of a database schema's. - This involves of creating the two dacpacs for the different databases - Comparing the two dacpacs and generating a report to know the exact differences - Generate a script that would have all the changes How to generate a dacbpac The easiest way to create a dacpac for a database is through management studio ( right click on the databae --> task --> Extract data-tier-application). This will work under most cases but will error out when the database has difffrent settings. ie. if CDC is enabled To work around this blocker, you need to use command line to send the extra parameters. Bellow is the command used to generate the dacpac. "%ProgramFiles...