Welcome in Dinesh Patel Blog

Find installed R Packages in SQL/R Process sp_execute_external_script @language = N'R', @script = N'OutputDataSet <- data.frame(installed.packages())' Install Additional R Packages https://msdn.microsoft.com/en-in/library/mt591989.aspx  Revolution R Enterprise inside SQL Server EXECUTE   sp_execute_external_script @language = N'R' ,@script = N' salesData <- InputDataSet fit <- lm(SalesAmount ~ Year + Quarter, data = as.data.frame(salesData)) data2017 <- data.frame(Year=2017, Quarter=1:4) sales2017 <- predict(fit, newdata=data2017) style <- c(rep(1,14), rep(2,4)) salesForecast <- data.frame(data2017, sales2017) library(sqldf) OutputDataSet <- sqldf("select Quarter, Year, SalesAmount from salesData UNION select Quarter, Year, sales2017 from salesForecast") ' ,@parallel = 1 ,@input_data_1 = N'select d.CalendarQuarter Quarter, CalendarYear Year, sum(f.SalesAmount) SalesAm...

COMPRESS and DECOMPRESS functions can be used as shown below: SELECT COMPRESS ('Compress and Decompress Function SQL Server') select CAST(  DECOMPRESS( COMPRESS ('Compress and Decompress Function SQL Server') )  AS NVARCHAR(MAX) ) FROM dbo.Product

--Get List of Last Full Backup or No Backup ;WITH LAST_FULL_BACKUP_LIST AS ( SELECT SYSDBLIST.name AS Name, MAX(BUSETS.backup_finish_date) AS Last_Backup_Finish_DateTime FROM MASTER.sys.databases AS SYSDBLIST LEFT OUTER JOIN msdb.dbo.backupset AS BUSETS ON SYSDBLIST.name = BUSETS.database_name WHERE SYSDBLIST.name<>'TempDB' AND BUSETS.[type] ='D' OR BUSETS.[type] IS NULL GROUP BY SYSDBLIST.name ) , --Get List of Last Differential Backup LAST_DIFFERENTIAL_BACKUP_LIST AS ( SELECT SYSDBLIST.name AS Name, MAX(BUSETS.backup_finish_date) AS Last_Backup_Finish_DateTime FROM MASTER.sys.databases AS SYSDBLIST LEFT OUTER JOIN msdb.dbo.backupset AS BUSETS ON SYSDBLIST.name = BUSETS.database_name WHERE SYSDBLIST.name<>'TempDB' AND BUSETS.[type] ='I' GROUP BY SYSDBLIST.name ) , --Get List of Last Log Backup LOG_BACKUP_LIST AS ( SELECT SYSDBLIST.name AS Name, BUSETS.backup_finish_date AS Backup_F...

Columnstore Indexes ·          In-memory, compressed data in pages based on columns instead of rows Types of Columnstore Index •        Clustered Columnstore Indexes –       SQL Server 2014 Enterprise, Developer, and Evaluation Edition Only –       Includes all columns in the table –       Only index on the table –       Optimizes storage for compression and performance –       Updatable CREATE CLUSTERED COLUMNSTORE INDEX [IX_CS_FactProductInventory] ON dbo. FactProductInventory; •        Non-Clustered Columnstore Indexes –       Includes some or all columns in the table –       Can be combined with other indexes –       Read-Only CREATE NONCLUSTERED...

Buffer Pool Extension •        Extends buffer cache to non-volatile storage •        Improves performance for read-heavy OLTP workloads •        SSD devices are often more cost-effective than adding physical memory •        Simple configuration with no changes to existing applications. ALTER SERVER CONFIGURATION SET BUFFER POOL EXTENSION ON     (FILENAME = 'F:\SSDCACHE\Example.BPE', SIZE = 60 GB); GO Returning configuration buffer pool extension information SELECT path, file_id, state, state_description, current_size_in_kb FROM sys.dm_os_buffer_pool_extension_configuration; Returning the number of cached pages in the buffer pool extension file SELECT COUNT(*) AS cached_pages_count FROM sys.dm_os_buffer_descriptors WHERE is_in_bpool_extension <> 0; Setting process affinity The examples in this section show ...

Memory-Optimized Tables •        Defined as C structs, compiled into DLLs, and loaded into memory •        Can be persisted as filestreams, or non-durable •        Do not apply any locking semantics •        Can be indexed using hash indexes •        Can co-exist with disk-based tables •        Can be queried using Transact-SQL •        Cannot include some data types, including text, image, and nvarchar(max) •        Do not support identity columns or foreign key constraints Memory-Optimized Table Scenarios •        Optimistic concurrency optimizes latch-bound workloads: –       Multiple concurrent transactions modify large numbers of rows –   ...

Use the CREATE PROCEDURE statement ·          NATIVE_COMPILATION option ·          SCHEMABINDING option ·          EXECUTE AS option ·          BEGIN ATOMIC clause (isolation level and language) CREATE PROCEDURE   dbo.DeleteCustomer   @CustomerID   INT WITH NATIVE_COMPILATION, SCHEMABINDING, EXECUTE AS OWNER AS BEGIN ATOMIC WITH     (TRANSACTION ISOLATION LEVEL = SNAPSHOT;      LANGUAGE = 'us_English')   DELETE   dbo.OpenOrders   WHERE   CustomerID   = @CustomerID   DELETE   dbo.Customer   WHERE   CustomerID   = @CustomerID END;

Top Five New Features in SQL Server 2012 Microsoft recently released the first Community Technical Preview (CTP) of the newest version of SQL Server, code-named Denali.  Three years after the release of SQL Server 2008, this new version of the popular database platform promises several exciting new features. 1. SQL Server AlwaysOn Technology The new High Availability and Disaster Recovery (HADR) features of Denali will offer a vast improvement over the database mirroring capabilities of SQL Server 2008.  AlwaysOn allows you to create availability groups of databases that can be failed over simultaneously.  This is especially important when you wish to fail over interdependent applications that rely upon more than one database.  AlwaysOn also allows you to create active secondary servers, making use of the redundant databases for part of your read workload. 2. Ad Hoc Query Paging The ad hoc query paging enhancements to TSQL add the OFFSET an...