27 September 2016

Columnstore Indexes SQL 2016 in a nutshell



What is it: A columnstore index is a technology for storing, retrieving and managing data by using a columnar data format, called a columnstore. Uses deltastore to reduce fragmentation of the column segments that improves columnstore compression and performance by storing rows until the number of rows reaches a threshold and are then moved into the columnstore. See Ref# https://msdn.microsoft.com/en-us/library/gg492088.aspx for details.

Performance: The columnstore index is the standard for storing and querying large data warehousing fact tables. It uses column-based data storage and query processing to achieve up to 10x query performance gains in your data warehouse over traditional row-oriented storage, and up to 10x data compression over the uncompressed data size. Beginning with SQL Server 2016, columnstore indexes enable operational analytics, the ability to run performant real-time analytics on a transactional workload. Faster query optimizers for aggregate functions, and Sorting. Aggregate pushdown is supported with or without Group By clause for both clustered columnstore indexes and nonclustered columnstore indexes. Columnstore indexes support read committed snapshot isolation level (RCSI) and snapshot isolation (SI). Columnstore supports index defragmentation by removing deleted rows without the need to explicitly rebuild the index. The ALTER INDEX … REORGANIZE statement will remove deleted rows, based on an internally defined policy, from the columnstore as an online operation. Columnstore indexes can be access on an AlwaysOn readable secondary replica. You can improve performance for operational analytics by offloading analytics queries to an AlwaysOn secondary replica.

Types: Clustered ColumnStore Index (CCI) and Non-Clutsered ColumnStore Index (NCCI)

Combination: Cannot have more than one any type of Columnstore Indexes. CCI can be combined with usual Btree indexes (rowstore based indexes). A usual Clustered index can be converted into CCI also.

Limitations: You cannot use cursors or triggers on a table with a clustered columnstore index. This restriction does not apply to nonclustered columnstore indexes so you can use cursors and triggers on a table with a nonclustered columnstore index but nonclustered columnstore indexes does not save space but do give performance improvements for OLTP. Cannot combined with page/row compression, filesteam or replication. MERGE is disabled when a btree index is defined on a clustered columnstore index. For in-memory tables, a columnstore index must include all the columns; the columnstore index cannot have a filtered condition.

Functional: Support for primary keys and foreign keys by using a btree index to enforce these constraints on a clustered columnstore index. The columnstore index does not have key columns.


Thank you,
Sameer

13 April 2015

Script to create insert statements for each row in the specified table

 
/*
Use this script to create insert statements for each row in the specified table.
Instructions:
1. Set the database you want to script from as normal.
2. change the set @TableName = '' line to be the
table you want to script out.
3. Run the script and copy all the text from the results below
the line with all the dashes (----).
Notes:
   If you get the error message "Invalid object name ''."
   then you either forgot to set the correct database or you spelled
   your table name wrong
Credits:
  Bob Wiechman - Fix for smalldatetime support
  Richard Lesh - correct support of uniqueidentifiers, automatic setting of Identity off/on, add Where clause support, more detail in debug mode.
  Sameer - Fix data integrity issues
*/
declare @TableName sysname
declare @WhereClause  varchar(1024)
declare @IdentityInsert int
declare @ColName sysname
declare @ColType tinyint
declare @ColStatus tinyint
declare @DebugMode bit
declare @ColList nvarchar(4000)
declare @ValList nvarchar(4000)
declare @SQL1 nvarchar(1000)
declare @SQL2 nchar(10)
declare @SQL3 nchar(1000)
set @TableName = 'version'  --  ''
set @WhereClause = ''    -- limit scope of inserts
set @DebugMode = 0    -- set to 1 if you only want a script
set @IdentityInsert = 0                -- set to 1 if you want to force IDENTITY_INSERT statements
set @ColList = ''
set @ValList = ''
set @SQL1 = 'select replace(''insert into ' + @TableName + ' ('
set @SQL2 = ') values ('
set @SQL3 = ')'', ''''''null'''''', ''null'') from ' + @TableName
if @DebugMode = 1 print '-- StmtShell: ' + @sql1 + @sql2 + @sql3
declare csrColumns cursor local fast_forward for
  select c.name, c.xtype, c.status
  from syscolumns c
    inner join sysobjects o
      on o.id = c.id
  where o.name = @TableName
    and o.xtype in ('U', 'S')
  order by ColID
open csrColumns
fetch next from csrColumns into @ColName, @ColType, @ColStatus
while @@fetch_status = 0
begin
  set @ColList = @ColList + ' ' + @ColName
  if @ColType in (173, 104, 106, 62, 56, 60, 108, 59, 52, 122, 48, 165)    -- numeric types (nulls not supported yet)
    set @ValList = @ValList + ' ''+convert(varchar(200),' + @ColName + ')+'''
  else if @ColType in (175, 239, 231, 231, 167)                            -- uid and string types
    set @ValList = @ValList + ' ''''''+isnull(' + @ColName + ',''null'')+'''''''
  else if @ColType in (58, 61)                                             -- dates (nulls not supported yet)
    set @ValList = @ValList + ' ''''''+convert(varchar(200),' + @ColName + ')+'''''''
  else if @ColType = 36                                                    -- uniqueidentfiers (nulls not supported yet)
    set @ValList = @ValList + ' ''''{''+convert(varchar(200),' + @ColName + ')+''}'''''
  if @DebugMode = 1             begin print '-- @ValList: ' + rtrim(@ValList) end
  if (@ColStatus & 0x80) = 0x80 begin set @IdentityInsert = 1 end          -- Check if column has Identity attribute
  fetch next from csrColumns into @ColName, @ColType, @ColStatus
end
close csrColumns
deallocate csrColumns
set @ColList = replace(ltrim(@ColList), ' ', ', ')
set @ValList = replace(ltrim(@ValList), ' ', ', ')
if @IdentityInsert = 1
  print 'set identity_insert ' + @TableName + ' on'
if @DebugMode = 1
  print @SQL1 + @ColList + @SQL2 + @ValList + @SQL3 + ' ' + @WhereClause
else
  exec (@SQL1 + @ColList + @SQL2 + @ValList + @SQL3 + ' ' + @WhereClause)
if @IdentityInsert = 1
  print 'set identity_insert ' + @TableName + ' off'

02 January 2015

Run ASP.NET Website with .NET Framework v1.1 on IIS 8 Windows Server 2012


1) Install IIS with IIS6 meta base compatibilities
2) Install Framework V1.1, SP1 and Security patches.
3) Enable V1.1 in IIS ISAPI and CGI Restrictions.
4) Deploy Site and See if it works. If it didn't work and gives 404 handlers errors goto next step.
5) In web.config add following 1.1 handler mappings just above this tags </configuration>







<system.webServer>
        <handlers>
            <add name="ASPNET-ISAPI-1.1-WebServiceHandlerFactory" path="*.asmx" verb="GET,HEAD,POST,DEBUG" modules="IsapiModule" scriptProcessor="C:\Windows\Microsoft.Net\Framework\v1.1.4322\aspnet_isapi.dll" resourceType="Unspecified" preCondition="classicMode,runtimeVersionv1.1,bitness32" />
            <add name="ASPNET-ISAPI-1.1-SimpleHandlerFactory" path="*.ashx" verb="GET,HEAD,POST,DEBUG" modules="IsapiModule" scriptProcessor="C:\Windows\Microsoft.Net\Framework\v1.1.4322\aspnet_isapi.dll" resourceType="Unspecified" preCondition="classicMode,runtimeVersionv1.1,bitness32" />
            <add name="ASPNET-ISAPI-1.1-HttpRemotingHandlerFactory-soap" path="*.soap" verb="GET,HEAD,POST,DEBUG" modules="IsapiModule" scriptProcessor="C:\Windows\Microsoft.Net\Framework\v1.1.4322\aspnet_isapi.dll" resourceType="Unspecified" preCondition="classicMode,runtimeVersionv1.1,bitness32" />
            <add name="ASPNET-ISAPI-1.1-PageHandlerFactory" path="*.aspx" verb="GET,HEAD,POST,DEBUG" modules="IsapiModule" scriptProcessor="C:\Windows\Microsoft.Net\Framework\v1.1.4322\aspnet_isapi.dll" resourceType="Unspecified" preCondition="classicMode,runtimeVersionv1.1,bitness32" />
            <add name="ASPNET-ISAPI-1.1-HttpRemotingHandlerFactory-rem" path="*.rem" verb="*" modules="IsapiModule" scriptProcessor="C:\Windows\Microsoft.Net\Framework\v1.1.4322\aspnet_isapi.dll" resourceType="Unspecified" preCondition="classicMode,runtimeVersionv1.1,bitness32" />
            <remove name="ASPNET-ISAPI-1.1-AXD" />
            <add name="ASPNET-ISAPI-1.1-AXD" path="*.axd" verb="GET,HEAD,POST,DEBUG" modules="IsapiModule" scriptProcessor="C:\Windows\Microsoft.NET\Framework\v1.1.4322\aspnet_isapi.dll" resourceType="Unspecified" requireAccess="Script" preCondition="classicMode,runtimeVersionv1.1,bitness32" />
        </handlers>
</system.webServer>


Enjoy the running site :)
Note: Make sure to backup your current configs.

Thank you,






26 November 2014

Factors Influencing Consumers Purchase Intentions towards Private Brands

Abstract

The change in market trends, of late, is marked by the growth of ‘brands’ for consumable items. Now, people prefer ‘branded’ products even for household use instead of random selection. Keeping in view the changing trend, this study explores the factors influencing purchase of private brands in Karachi and to understand how those factors affect the purchase intentions of consumers towards private brands.

12 January 2012

How to check if a file exists on remote/local server using php

Linking to a file or displaying an image from remote server is very common practice these days. But problem occurs when the file is not there on the remote server and our page starts showing a broken link. For this problem I have few solutions that will help you in avoiding this issue.

Solution # 1: Using file_exists()

Syntax
file_exists(string $filename)