Wednesday, 28 October 2015

SQL Server Management Studio is Free now


For many years, it is ambiguous as no Microsoft license expert is sure that SSMS should be licensed if it is installed on a separate server other than database engine. But we know it should be licensed after reading their document again and again.
Now excited news is confirmed that SSMS 2016 is free to install on any computers,

https://msdn.microsoft.com/en-us/library/mt238290.aspx

Rank or distinct, which one you like to use




first impression, distinct is lighter, rank is more expensive though it is new and cool. likes to see some document explain how each works. execution plan shows  somewhat though.
The story:
I got a query from someone to run on my oracle server(realize the rank is used in SQL server 2012 too), it basically likes to export the number of users who logged in during a month against his database table auditevent(every login has a record),  so count only once if a user logged in more than once.
he is using rank over and count the rank=1 so gives distinct value. his uses 2.697s, I  replaced it with distinct clause which uses 2.60s, the time runs 0.097 second less. I thought should be much more less.
select count(userid) from (select USERID ,
             RANK() OVER (PARTITION BY USERID ORDER BY LOGGEDTIME DESC,rownum) Rank_Line     
            from CONCERTOAUDIT.AUDITEVENT
            where ...)
where Rank_Line=1
replaced with
 select count( distinct USERID) 
        from
        (
            select USERID    
            from CONCERTOAUDIT.AUDITEVENT
            where..)



Monday, 26 October 2015

After played Chess for nearly 5 years, Lily is participating the World Youth Chess Championship 2015 in Greece.  Despite she has no coach, no practice currently, still hope she could get a better result than the past. Good luck Lily


Thursday, 15 October 2015

SQL Server 2012 Policy Management - Script icon besides Database Engine in SSMS

SQL Server 2012 Policy Management - looks like a script icon in SSMS when the policy failed.

I implemented a policy on my production server to check if all databases are in full recovery model.
the 3 steps are:

  1.  create condition , choose Database as facet, filter Recovery Model = Full 
  2.  Create policy, against all users database by database_id>4 and use the above condition, schedule a job runs weekly. 
  3.  enable it and evaluate. I noticed a few databases were highlighted not being Full recovery model, but it is required by the application as simple. I left it and will update the condition later, then forgot it. 

 The next week, I open this server is SSMS, I found a Script icon beside the server, panic, what is wrong with my SQL instance? google it, didn't find it, soon later, notice the script icon is the policy checker. Cool stuff.