Columnstore Index

If all the columns are used to build the index, this become a copy of original table with column values stored differently, column oriented instead of row oriented.

Data in table is treated as readyonly, good for data warehousing analytic queries, not good for everyday OLTP since table with columnstore index can't be updated.

For a query that already uses most of the columns in a table, performance gaining can be limited. in this case, columnstore  index's management overhead (recombining the rows etc) can be worse than the benefits it brings in.

Create an Oracle Linked Server

Using GUI, choose Oracle Provider for OLE DB, product name as OraOLEDB.Oracle(same as oracle provider for OLE DB), data source is the tnsname.

Going to Security and provide user and password in remote login section

In Server options, enable RPC and RPC out. Or for the provider itself, enable the Inprocess.

--this is an automatically generated script by SSMS
USE [master]
GO

/****** Object:  LinkedServer [ORADB1]    Script Date: 13/11/2013 2:47:47 PM ******/
EXEC master.dbo.sp_addlinkedserver @server = N'ORADB1', @srvproduct=N'OraOLEDB.Oracle', @provider=N'OraOLEDB.Oracle', @datasrc=N'ORADB1'
 /* For security reasons the linked server remote logins password is changed with ######## */
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'ORADB1',@useself=N'False',@locallogin=NULL,@rmtuser=N'scott',@rmtpassword='########'

GO

EXEC master.dbo.sp_serveroption @server=N'ORADB1', @optname=N'collation compatible', @optvalue=N'false'
GO

EXEC master.dbo.sp_serveroption @server=N'ORADB1', @optname=N'data access', @optvalue=N'true'
GO

EXEC master.dbo.sp_serveroption @server=N'ORADB1', @optname=N'dist', @optvalue=N'false'
GO

EXEC master.dbo.sp_serveroption @server=N'ORADB1', @optname=N'pub', @optvalue=N'false'
GO

EXEC master.dbo.sp_serveroption @server=N'ORADB1', @optname=N'rpc', @optvalue=N'true'
GO

EXEC master.dbo.sp_serveroption @server=N'ORADB1', @optname=N'rpc out', @optvalue=N'true'
GO

EXEC master.dbo.sp_serveroption @server=N'ORADB1', @optname=N'sub', @optvalue=N'false'
GO

EXEC master.dbo.sp_serveroption @server=N'ORADB1', @optname=N'connect timeout', @optvalue=N'0'
GO

EXEC master.dbo.sp_serveroption @server=N'ORADB1', @optname=N'collation name', @optvalue=null
GO

EXEC master.dbo.sp_serveroption @server=N'ORADB1', @optname=N'lazy schema validation', @optvalue=N'false'
GO

EXEC master.dbo.sp_serveroption @server=N'ORADB1', @optname=N'query timeout', @optvalue=N'0'
GO

EXEC master.dbo.sp_serveroption @server=N'ORADB1', @optname=N'use remote collation', @optvalue=N'true'
GO

EXEC master.dbo.sp_serveroption @server=N'ORADB1', @optname=N'remote proc transaction promotion', @optvalue=N'true'
GO

SQLAgent Properties

To view its properties, use 

EXEC msdb..sp_get_sqlagent_properties

To set a SQLAgent property, use

EXEC msdb.dbo.sp_set_sqlagent_propertie @property=''

An example can be changing SQLAgent's log file directory:

EXEC msdb.dbo.sp_set_sqlagent_properties 

@errorlog_file=N'a dir\SQLAGENT.OUT'

Slow Changing Dimensions

For example, a person's name can be changed due to marriage status changed, a person's address can change due to moving to another place.

For the changed dimensions, there are usually two ways to handle the change.

Method one is to overwrite the values in existing dimension table, for instance, change the person's name directly. The change history is not maintained and since the preexisting facts have a new context, it sometimes may lead to confusion. So this method applies to minor attributes changes. Benefit of this approach is it's easy to group existing facts and new facts into same dimension.

Method two is to create a new record in dimension table. For instance, create a new record with new surrogate key for person's new address. This approach retains all the change history, the pre-existing facts can be viewed as they were (Historic fact context is preserved). But confusion also raise up because duplicated dimensions appear in the dimension table, very often, extra efforts need to be done to combine the slowly changing dimensions back to one dimension in the reports (a fact table for the changes to resolve this problem).

Useful netsh

Recently I was using Oracle on a virtual machine, and I found that I needed to switch between static IP and DHCP frequently. The little knowledge on netsh helped me a lot on releasing me from doing manual configurations.

1. Set it to static IP, as well as restarting oracle services.
net stop OracleServiceORADB1
net stop OracleOraDb11g_home1TNSListener
netsh interface ip set address "Local Area Connection" static 192.168.154.130 255.255.255.0
net start OracleServiceORADB1
net start OracleOraDb11g_home1TNSListener

2. Switch back to DHCP
netsh interface ip set address "Local Area Connection" dhcp

3. Store the current network setting
netsh -c interface dump > c:\netcfg.txt

4. Load the network configurations
netsh -f c:\netcfg.txt

Subquery needs to be aliased in T-SQL

This is not necessary in Oracle.

select decode(c1,'a','not null','null value')
from
(
select null c1 from dual
union
select 'a' from dual
)

In T-SQL, it should be aliased.

select c1
from
(
select null c1 from dual
union
select 'a' from dual
) a

Three Ways to Use CSS

1. External Style Sheet

Ideally applying to entire web site or at least multiple pages. It has to be imported by Link tag in the head section.

<.head>.
<.link rel="stylesheet" type="text/css" href="mystyle.css">.
<./head>.

2. Internal Style Sheet

Defined in head.

<.head>.
<.style>.
hr {color:sienna;}
<./style>.
<./head>

3. Inline Style

Use the style attribute for relevant tag.

<.p style="color:red;margin-left:20px;">.paragraph.<./p>.