Using FullText search with FileTables in SQL Server 2012

In my previous post sometime back I talked about FILETABLES, [link]. This is a new feature introduced in SQL Server 2012, which is built on top of FILESTREAM. I also talked about how we can use them to store files and retrieve the information from them.   Here, in this post we will see how we can search…

1

SQL Server 2012 (a.k.a Denali) – New feature | FileTables

new kid on the blog… this is my first post on MSDN Blogs after joining Microsoft very recently (Jan-2012). I also used to blog on my personal bog site i.e. http://sqlwithmanoj.com about SQL Server (TSQL) and I’ll start my 1st blog post here by referring to my latest article.   SQL Server 2012 (a.k.a Denali) is yet…

4

DYNAMIC PIVOT

My previous PIVOT UNPIVOT post describes to organize data in a cross tab fashion or to transpose columns into rows and vice-versa. This is fine only when you know how many columns you need. But when columns vary according to the query then how could you apply them on your scripts. The only way is to store…


ISNULL vs COALESCE

ISNULL & COALESCE with some common features makes them equivalent, but some features makes them work and behave differently, shown below.- Similarity:Both can be use to build/create a CSV list as shown below: USE [AdventureWorks] GO DECLARE @csv VARCHAR(2000) SELECT @csv = ISNULL(@csv + ‘, ‘, ”) + FirstName FROM Person.Contact WHERE ContactID <= 10…


Combine multiple ROWS into Comma Separated String… and vice-versa

There are times when you want to combine values from multiple rows of a single column into a CSV (Comma Separates Value) String. Like combining multiple orders of each customer into one single row, or multiple Cities against each State as shown below. It was a bit difficult to do in SQL Server 2000 and…


Table variables are not stored in memory, but in tempdb

Table-Variables just like Temp-Tables also gets stored in tempdb. This is a misconception among people and online forums that Table-Variables are memory variables or maintained in memory. To prove this I executed following code: CREATE TABLE #tempTab (j INT) INSERT INTO #tempTab VALUES (1) SELECT * FROM #tempTab SELECT TABLE_NAME FROM tempdb.INFORMATION_SCHEMA.TABLES GO DECLARE @tabVar TABLE…


Temporary Tables vs Table Variables

Every time there is a lot of debate on #Temporary Tables & Table Variables. And everyone cites his/her own definition and examples. Most of them are bogus and conflicting. So I searched online and compiled a list of enough definition & points that are valid and tested: Temporary Tables:   1. create table #T (…)  …