Getting row count and table size in Microsoft Azure SQL database

Azure SQL database in SQL Management Studio does not provide Disk Usage Report, here is a query to view the row count and page size by table.

Query:

SELECT

       t.name,

       SUM(s.row_count) AS [Row Count],

       (

             SELECT   

                   SUM(reserved_page_count) * 8.0 / 1024 AS [Size in MB]

             FROM sys.dm_db_partition_stats, sys.objects

             WHERE sys.dm_db_partition_stats.object_id = sys.objects.object_id

             AND sys.objects.object_id = t.object_id

             GROUP BY sys.objects.name

       ) AS [Size in MB]

FROM sys.tables t

       INNER JOIN sys.dm_db_partition_stats s ON t.object_id = s.object_id

             AND t.type_desc = 'USER_TABLE'

             AND t.name NOT LIKE '%dss%'

             AND s.index_id = 1

GROUP BY t.object_id, t.name

ORDER BY t.name

Output:

Creating RamDisk in Mac OS X

Tested with Mac OS X version 10.9 Mavericks

Steps:

1. Create an AppleScript, e.g. /Users/eddiecjc/Documents/Scripts/RamDisk.scpt

   set isTmpDiskRunning to "false"
   set isRamdiskCreated to "false"
   tell application "Finder"
    if not (exists POSIX file "/Volumes/Ramdisk") then
     -- Method 1, may have problem with system hibernate.
     -- Creating RamDisk, with size 4GB (4 * 1024 * 1024 * 2)
     do shell script "diskutil erasevolume HFS+ RamDisk `hdiutil attach -nomount ram://$((4*1024*1024*2))`"
     
     -- Method 2, using Open Source software TpmDisk.
     --do shell script "
     --  open -a /Applications/TmpDisk.app --args -name=RamDisk -size=4096
     -- "
     --set isTmpDiskRunning to "true"
    end if
    
    repeat until isRamdiskCreated = "true"
     if exists POSIX file "/Volumes/Ramdisk" then
      set isRamdiskCreated to "true"
     else
      display alert "Ramdisk is NOT created.
   Retry in 5 seconds..." as critical buttons {"I will wait"} giving up after 5
     end if
    end repeat
   end tell
   
   -- Create Folders
   set FolderPath to POSIX file "/Volumes/Ramdisk" as alias
   tell application "Finder"
    try
     make new folder at FolderPath with properties {name:"Library Caches"}
     make new folder at FolderPath with properties {name:"Downloads"}
     make new folder at FolderPath with properties {name:"Temp"}
     make new folder at (POSIX file "/Volumes/Ramdisk/Temp" as alias) with properties {name:"Screenshots"}
     
     -- Set up symbolic link
     --sudo rm -r ~/Library/Caches
     --ln -s /Volumes/RamDisk/Library\ Caches ~/Library/Caches
     --sudo rm -r ~/Downloads
     --ln -s /Volumes/RamDisk/Downloads ~/Downloads
     
     -- Change screenshots default location
     -- defaults write com.apple.screencapture location /Volumes/Ramdisk/Temp/Screenshots
     -- killall SystemUIServer
    end try
   end tell
   
   if isTmpDiskRunning = "true" then
    -- Kill the process, so it does not take up the space from the task bar.
    set app_name to "TmpDisk"
    do shell script "killall " & app_name
   end if
   
   -- 1. Export this to type Application RamDisk.app.
   -- 2. Mark as Run-Only
   -- 3. Mark the file as executable with chmod +x .
   -- 4. Add RamDisk.app to Login Items in the Users & Groups. 
   

2. Export the script to type Application, e.g. /Users/eddiecjc/Documents/Scripts/RamDisk.app, and mark it as Run-Only
3. In Terminal, mark the file as executable, e.g. chmod +x /Users/eddiecjc/Documents/Scripts/RamDisk.app
4. Add the script to run during start up
   1. Open System Preferences
   2. Go to Users & Groups
   3. Select the current user
   4. Go to Login Items tab
   5. Click on "+" and navigate to the RamDisk.app
   6. Repeat for other users that would need a RamDisk
      
5. Run RamDisk.app to set up the RamDisk as a preparation for the remaining steps.
6. Move stuff to Ramdisk:
   • Move ~/Library/Caches to Ramdisk

     sudo rm -r ~/Library/Caches
     ln -s /Volumes/RamDisk/Library\ Caches ~/Library/Caches
     

   • Move Downloads to Ramdisk

     sudo rm -r ~/Downloads
     ln -s /Volumes/RamDisk/Downloads ~/Downloads

   • Move screenshot default saving location to Ramdisk

     defaults write com.apple.screencapture location /Volumes/Ramdisk/Temp/Screenshots
     killall SystemUIServer

     Reference: http://simonemccallum.com/2013/08/11/how-to-change-the-default-location-for-your-mac-os-x-screenshots/
   • Change any default download location for apps (e.g. Chrome)
   • Change the unarchiver default working directory.
7. If using the built-in command rather than TmpDisk, for laptops, change the hibernation mode, to prevent Ramdisk being ejected after hibernation.
1. Find out the current hibernation mode:

   pmset -g | grep hibernatemode

   Default is 3 for laptops, indicating contents in the Ram will be written to the hard drive before going to hibernate mode.
2. Change it to 0, so battery will still power the Ram (drawback is that if the battery is drained or power is lost, then resuming from hibernation would fail):

   sudo pmset -a hibernatemode 0

3. Clean the disk space used previously for the hibernation:

   cd /var/vm
   sudo rm sleepimage

   Reference: http://blog.roodo.com/jason1204/archives/25384258.html and http://etherealmind.com/osx-hibernate-mode/
8. Restart the Mac, Ramdisk should be created after log in.

Note: Mac OS X comes with built-in command to create virtual disks using ram, as described above, but there is a problem of Ramdisk disappearing after the hibernation. To get around this issue, there is an Open Source ramdisk tool called TmpDisk, download and install TmpDisk from http://inkscribbles.com/apps/tmpdisk/. Comment out method 1 in the script and uncomment to use method 2 instead.

MSSQL Hack: be able to execute sys.sp_addrolemember within a transaction

sys.sp_addrolemember is a MS SQL Server system stored procedure that cannot be executed within a user-defined transaction.
The work around is to terminate/commit any outer transaction (only in scenarios where the caller does not have any change *prior to calling sp_addrolemember*, that may need rolling back if something fails *after calling sp_addrolemember*, as this might affect the caller to lose the capability of rolling back unwanted changes).

SET IMPLICIT_TRANSACTIONS OFF -- make sure server does not start any implicit transaction 
WHILE XACT_STATE() = 1 -- the transaction is committable
  COMMIT TRAN -- Try committing any outer transaction, in order for 

EXEC sp_addrolemember
  @rolename = 'rolename',
  @membername = 'db_user_name'

Comparing between different collations

In MSSQL 2005, when comparing string (nvarchar, varchar, etc..) between different collations e.g.
SELECT * FROM sys.sql_logins
WHERE NAME NOT IN (
SELECT login_name FROM RESOURCE
)

SQL Server would throw an error due to different collations set for the columns compared:
Msg 468, Level 16, State 9, Line 1
Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CS_AS" and "SQL_Latin1_General_CP1_CI_AS" in the equal to operation.

So, to be able to compare between different collations, COLLATEE DATABASE_DEFAULT can be used, which would convert the column(s) to the collation the current database is using, and the new query looks like:
SELECT * FROM sys.sql_logins
WHERE NAME COLLATE DATABASE_DEFAULT NOT IN (
SELECT login_name COLLATE DATABASE_DEFAULT FROM RESOURCE
)

In addition, if the collation for one of the columns being compared is known, all the other columns can be converted to the same collation:
SELECT * FROM sys.sql_logins
WHERE NAME COLLATE SQL_Latin1_General_CP1_CS_AS NOT IN (
SELECT login_name FROM RESOURCE
)

Set up PostgreSQL as a linked server in Microsoft SQL Server 32-bit on a Windows Server 2003 64-bit

Environment

• Machine 1
  IP: 10.2.29.183
  OS: Windows Vista Business SP1
  DB: PostgreSQL 8.3, port: 5432
  
  
• Machine 2
  IP: 10.2.29.18
  OS: Windows Server 2003 Enterprise x64 Edition SP2
  DB: Microsoft SQL Server X86 Standard 9.00.1399.06, port: 4900
  

Procedures

• Machine 1
  
  
  
  1. Make sure port 5432 is opened in the firewall.
     
  2. Make sure Machine 2 is granted access to PostgreSQL. The access config file is at C:\Program Files\PostgreSQL\8.3\data\pg_hba.conf in this case. host all all 10.2.29.18/32 md5 is added to the end of the file.
     
  
  
• Machine 2
  
  
  
  1. Install 64-Bit OLEDB Provider for ODBC (MSDASQL), the installer file is WindowsServer2003.WindowsXP-KB948459-v2-x64-ENU.exe in this case.
     
  2. Install 32-bit PostgreSQL ODBC driver, choose the appropriate PostgreSQL version matching the setup (in this case psqlodbc_08_03_xxxx.zip).
     
  3. In SQL Server Management Studio, create a linked server using the following command:
     
EXEC sp_addlinkedserver
@server = '10.2.29.183',
@srvproduct = 'PostgreSQL',
@provider = 'MSDASQL',
@provstr = 'Driver=PostgreSQL ANSI;uid=postgres;Server=10.2.29.183;Port=5432;database=my_db_name;pwd=my_password';

EXEC sp_addlinkedsrvlogin
@rmtsrvname = '10.2.29.183',
@useself = 'false',
@rmtuser = 'postgres',
@rmtpassword = 'my_password';

     
  4. To test the connection:
     
SELECT *
FROM OPENQUERY([10.2.29.183], 'SELECT * FROM information_schema.tables')

     
  
  

References

• "Using 32 bit ODBC driver in x64?". Available: http://forums.techarena.in/windows-x64-edition/698703.htm [Accessed: May 06, 2009].
  
• Postgres OnLine Journal, "SETTING UP POSTGRESQL AS A LINKED SERVER IN MICROSOFT SQL SERVER 64-BIT". Available: http://www.postgresonline.com/journal/index.php?/archives/46-Setting-up-PostgreSQL-as-a-Linked-Server-in-Microsoft-SQL-Server-64-bit.html [Accessed: May 06, 2009].