In every version of SQL Server new T-SQL features are introduced. These are new T-SQL features of SQL Server 2012.
Sample databases dedicated to SQL Server 2012 RC0 are released. AdventureWorks2008R2_Data.mdf and AdventureWorksDWDenali_Data.mdf are those files. You need to attach these mdf files to your SQL Server instance.
Download these sample data files from here.
New to the Express family, LocalDB is a lightweight version of Express that has all its programmability features, yet runs in user mode and has a fast, zero-configuration installation and short list of pre-requisites. Use this if you need a simple way to create and work with databases from code. It can be bundled with Visual Studio, other Database Development tools, or embedded with an application that needs local databases.
Dropping database from SQL Server is not a major task. However, it can be painful if that database is being used at time of dropping it. Read this FAQ from www.sql-server-performance.com
sp_who2 is function you can use to identify who (Login) is running at what (DBName, command) from where (SPID & Host) as you can see in the following image.
If you look closely, you will see that there are two SPID columns with same values. By looking at them, they are same So let us see the coding of this.
So you would see that SPID column is convert(char(5),spid) so why on earth same column twice.
Just see the comment after second SPID.
Handy extra for right-scrolling users.
Do you agree. I am not 
PowerPivot and Power View Lifecycle and BI Semantic Model (BISM) Overview
AlwaysOn SQL 2012
http://channel9.msdn.com/Blogs/mwilmot/SQLShorts-AlwaysOn-SQL-2012
AlwaysOn Availability Groups for SQL 2012
http://channel9.msdn.com/posts/SQLShorts--AlwaysOn-Availability-Groups-for-SQL-2012
SQL 2012 Apollo Column Store
http://channel9.msdn.com/posts/SQLShorts-SQL-2012-Apollo-Column-Store
I was a speaker at TechInsights 2011 which was held in Malaysia at Monash University Kuala Lampur.
Well, this is my first appearance out side Sri Lanka and was a great experience.
I did two sessions 1. CDC for SCD and 2. Resource Governor. To be honest, first session was not much participated second one was far better.
Here are few photos of the sessions.
Here are Presentations and Samples
SQL Server 2012 Release Candidate 0 is now available for download. SQL Server 2012 RC0 (previously code-named Denali) includes several new capabilities (most of which are related to the release’s new features) that weren’t available in SQL Server Denali CTP3, including the following:
For more information about the upcoming release of SQL Server, see Microsoft’s SQL Server 2012 page. You can download SQL Server 2012 RC0 here.
Let’s start with an example.
You will see that MiddleName column has NULL values. If you want to combine all four columns and display them in one column you will write following query.
Result is,
You will realize the obvious problems from the above result set. To avoid this what you should is, use the ISNULL function.
Though the above will solve your problem, as a developer you need to keep an eye on nullability of the column.
With Denali, you have a new function called CONCAT.
And the result is,
which is what we were looking for.
Next question is, how about the performance.
To answer that I will display query execution plans for two queries, one with CONCAT and the other with ISNULL function.
So there is no additional cost with the new function.
I am sure you would have used CONVERT function many times.
If you see the above data set, last date value is an invalid date. If you are integrating with legacy systems, you will these types of records.
They you might need to convert these into a date time value. Naturally, you will use, CONVERT function.
But it will fail.
In Denali or SQL Server 2012 (as per CTP3) you have new function called TRY_CONVERT.
With this new function, you won’t get the error as well as you will be able to find out the errors as well.
Following query will return all the rows where you have an invalid date value.
You can use this for other data types as well.
Download the code from here
Microsoft SQL Server Sustained Engineering group is pleased to announce the release of SQL Server 2008 Service Pack 3 (SP3). Both the Service Pack and Feature Pack updates are available for download on the Microsoft Download Center. As part of our continued commitment to software excellence for our customers, this upgrade is free and doesn’t require an additional service contract.
Download it from here
Replication Agent will fail when attempting to alter a column in the publisher which is part of an index.
Here is the faq of mine.
මෙය මවිසින් ලියන ලද දෙවැනි ලිපියයි.
මෑතකදී, විශ්ව විද්යාලයක සිසුන් පිරිසක් තම පාඨමාලවේ පර්යේෂන වියාපෘතියකට ශ්රි ලංකා පොලීසිය සදහා ලැබෙන සුළු අපරාධ විශ්ලේශණය කිරීම තෝරා ගෙන තිබුනි. ඒ සදහා ඔවුන් සිංහලෙන් දත්ත ගබඩා කිරිමට තෝරා ගෙන තිබිනි. එයට මූලික හේතුව වී තිබුනේ, ලැබෙන දත්ත භාවිතා කරන අයට ඉංග්රීසි භාවිතයට වඩා සිංහල පහසු නිසාය. බොහෝ ශ්රි ලංකාවේ රාජ්ය ආයතයන් හි මෙම තත්වය ඇති බැවින්, ඒ පිළිබදව කිසියම් විස්තරයක් කිරීම මෙම ලිපියෙහි අරමුණයි.
When you create a database, by default data file and log file will be added to the database. For logical name it will be, Databasename_log. Let us say you want to change it to Database_Log (where L is in upper case).
ALTER DATABASE [TailLog]
MODIFY FILE (NAME=N'TailLog_log', NEWNAME=N'TailLog_Log')
GO
Then you will get an following error.
Msg 1828, Level 16, State 3, Line 1
The logical file name "TailLog_Log" is already in use. Choose a different name.
Let us try to do this from the SSMS.
So basically you are not allow to do this.
What you have to do is, first set it to another name.
ALTER DATABASE [TailLog]
MODIFY FILE (NAME=N'TailLog_log', NEWNAME=N'TailLog_LogTemp')
GO
The file name 'TailLog_LogTemp' has been set.
Then change it back to TailLog_Log so that you can achieve your target.
ALTER DATABASE [TailLog]
MODIFY FILE (NAME=N'TailLog_LogTemp', NEWNAME=N'TailLog_Log')
GO
Reports in SSMS are very helpful when monitoring SQL Server. But today I encounter an issue with one of the reports, which is Disk Usage Report.
Above is the Transaction Log space usage report taken for same database but for different times. Actually, second one was after taking a log backup. After seeing this report my immediate reaction was log backup has not cleared the log. So I ran another log backup and results are similar. But when you analyze these graphs carefully you would see that in the first graph, Green is Used and in the second Green is Unused and Blue is vice-versa. (Oops).
I did few more research into this to find out the tipping point and this is happening after 50% percent. See the image below.
Now it is 50-50 and next one is,
You can clearly see the change. I verified this is the same behavior in SQL Server 2008 R2 as well.
You may have used many different Identity functions like SCOPE_IDENTITY(), @@IDENTITY etc. but have you used $IDENTITY function.
Let us say you have a table where identity property is set in one of the columns.
if you run the following query,
Following is the result,
This means, you don’t have to explicitly mention the identity column instead you can use $IDENTITY. You can use this for all aggregations ( AVG ($IDENTITY), MAX($IDENTITY) etc) as well as in grouping columns.
මෙය මා විසින් ලියන ලද පළමු සිංහල blog සටහනයි. බොහෝ කලක පටන් පැවති අදහසක් "දැවන්ත" කාර්යයකින් පසු මෙසේ සටහනක් බවට පත්විය. මේ වෙනුවෙන් මට සහාය වු, Fiqriට මගේ ස්තුතිය.
පූර්ණ කාලීන දත්ත මූලික කළමනාකරුවන් (DBA) අවශ්ය ඇයි? (si-LK)
This was my first presentation in public outside Microsoft technologies. Presentation was on MongoDB which Susantha and myslef are doing quite a bit of R&D lately.
Our presentation went well and had few questions from the audience which was just over 1000.
Entire event was covered by adaderana
You can view all the pictures at facebook and samples are available in my skydrive.
Did you have a requirement of getting last date of the month by passing any date. I can remember I had this requirement during SQL Server 2000 days, in a HR system.
If you search for this in Google followings are the results.
There are around 2,350,000 results!. In these results, there you might they have used DATEDIFF and DATEADD functions.
With Denali CTP3 (not sure whether this was there in CTP1) new function called EOMonth is included. Now it is simply matter of passing date.
You can see the a syntax in the following image. Point to note here is there is no execution plan differences between this version and the previous. So only difference will be the usability,
SQL Server Management Studio is an essential tool for DBAs and developers. But are you using it effectively? In this article, I will try examine a few tips and techniques which could well boost your efficiency using SSMS.
Read the entire article here
SQL Server 2008 R2 SP1 release was not much highlighted since Denali CTP3 is making all the headlines.
Microsoft SQL Server 2008 R2 Service Pack 1 delivers hotfix solutions from SQL Server 2008 CU 1 to CU 6 in addition to resolving to customer requests with key security and supportability issues. SQL Server 2008 R2 SP1 has also seen 8% less bugs requiring resolution within the Service Pack scope when compared with SQL Server 2008 SP1.
Related Links:
· Download SQL Server 2008 R2 SP1
· Download SQL Server 2008 R2 SP1 Express
· Download SQL Server 2008 R2 SP1 Feature Packs
· Knowledge Base Article for Microsoft SQL Server 2008 R2 SP1
Much anticipated Denali CTP3 is now available for download from Microsoft. This is the third Community Technology Preview of codenamed Denali.
According to Microsoft, SQL Server 2012 codenamed Denali will enable customers to:
“•Deliver required uptime and data protection with AlwaysOn
•Gain breakthrough & predictable performance with Project Apollo
•Help enable security and compliance with new User-defined Roles and Default Schema for Groups
•Enable rapid data discovery for deeper insights across the organization with Project Crescent
•Ensure more credible, consistent data with SSIS improvements, a Master Data Services add-in for Excel, and new Data Quality Services
•Optimize IT and developer productivity across server and cloud with Data-tier Application Component (DAC) parity with SQL Azure and SQL Server Developer Tools code name “Juneau” for a unified dev experience across database, BI, and cloud functions.”
Though there is no official yet, it is believe that this version of SQL Server will be named as 2012 not as 2011 as said some time back. Nice way of postponing the release!
SQL Server 2012 Codenamed Denali CTP3 is available for download here.
In the development cycle, it is needless to less we all in some part of our carrier, have injected bugs in different scale. However, when we were told it is a bug, I am sure most of you try to fix it or prioritize it to fix it.
But can you imagine, if there is a bug in SQL Server for more than 5 years, ranging three SQL Server Products (SQL Server 2005, SQL Server 2008 and R2).
This bug is in CTE.
Now CTE was introduced primarily to support recursive queries.
Let’s say I have a table and data like this.
So I have a hierarchy as following.
Let’s assume that, you need to retrieve, only two levels starting from Richard, here is the query you should use.
so number of levels are handled by , MAXRECURSION = 2 option. In this of course, you will get the result but also and ERROR!!!,
yes, error says,
Msg 530, Level 16, State 1, Line 1
The statement terminated. The maximum recursion 2 has been exhausted before statement completion.
You can say that since it giving the correct result, what is the big deal about this. But the problem here is, since it is a Level 16 error, if you are using this inside of your code, your code will throw an exception.
You have a workaround which I have given in my sample code at the end of this post.
Now this error first appeared in SQL Server 2005 and Microsoft has make sure that they don’t fix this so you can see the same bug in SQL Server 2008 and in R2. I got to know that this was initially reported in 2002 when SQL Server 2005 CTP was released. So it is nine years now.
To confirm the stuff, I checked this in Denali CTP1. Guess what, still this issue in the backlog.
Download sample code from here
If you are altering a column with default column, you need to drop it. but the problem will be if you created a default constraint without explicitly specifying constraint name. Here is a faq from me.
xp_msver is a extended stored procedure, to get some valuable information in SQL Server.
EXEC master.dbo.xp_msver
Will return,
Most important parameters that I can think of is, ProductVersion (not a big deal since you can get this from ServerProperty function), ProcessorCount, Physical Memory.
Next question is, why you would need this?
Let’s say, you need to set the Maximum Degree of the Parallelism (MAXDOP) as a function of number of Processor count. For example, some DBAs want to have MAXDOP to, ProcessorCount – 2.
Similarly you can set the memory allocation as well.
Download relevant script from here
Due to various reasons, your procedures will fail after time out period elapsed.
But with the above generic error message, it is difficult to find out which procedure was failed.
Obvious way to find out timing out procedures are to debug your code. However, sometimes you might not be able to create this issue in your environments, since time-out error might depend on the environment setting or environment data.
So let us create a procedure which will time out.
Since this proc is having a delay for 20 hrs, it will obviously will fail with time out.
Let us call this procedure from C# application.
Though profiler can be used to capture this, running profiler may decrease your system performance. Microsoft introduced Extended Events to SQL Server, calming it is a light weight monitoring
In extended event, there is an event type called Event pairing, shows incomplete events. There are some events which are pairs such as lock acquired and lock released and from this event type we can capture incomplete events.
In our scenario, paired events are sp_statement_starting and sp_statement_completed and in case of a timing out, sp_statement_Completed event will not be completed.
You can create a extended event like following/
Then we can capture the event by running following query.
This result will give you
This means, though there are 11127 matched events, there is one orphan event which is what we are after.
If you expand the XML, it will give you the database id and the object id from which can find the culprit.
Interesting thing here is, sql_text is shown as Unable to retrieve SQL text, but if you execute this proc in SSMS, you will see the relevant proc in this XML it self.
