Discover Method Part 2

Lets look at some more Discover Commands . Many of this commands are not documented at BOL .

For previous part of this article
Discover Method Part 1

Discover Method Part 1

They are very rich source for getting meta data information for the server.
Lets look for more detail on DISCOVER METHODS

Last Nonempty Date/ Last Transaction Date

I freqeuntly got question on to get last non empty date or last date when transaction happened.

Here we will look at two example on how to get it from Adventure Works cube

DMVs in Analysis Service -- Part 3 (MDSCHEMA_MEMBERS)

Till now we have used MDX query to get hierarchical members in a hierarchy, But after DMVs its now possible to get this values using DMV MDSCHEMA_MEMBERS
It will be a fun to use DMV to get value. Lets more explore in it.

DMVs in Analysis Service -- Part 2 (MDSCHEMA)

As we all know that AS 2008 has come with many new features
I am using SQL Server since last 8 years and DMVs are great improvement in SQL Server 2005
Now exciting thing is that this DMVs are also in Analysis Service
Till now its not possible to view metadata in Analysis Service , but after 2008 its possible.
We will explore this DMVs in coming articles.

DMVs in Analysis Service -- Part 1 (DBSCHEMA)

As we all know that AS 2008 has come with many new features
I am using SQL Server since last 8 years and DMVs are great improvement in SQL Server 2005
Now exciting thing is that this DMVs are also in Analysis Service
Till now its not possible to view metadata in Analysis Service , but after 2008 its possible.
We will explore this DMVs in coming articles.

Gemini Renamed as PowerPivot

hi friends
Who are working with analysis service must be aware of Gemini.Its addon for Excel 2010 for users to handle large amount of data and analysis them , a self service BI
This project is now renamed as PowerPivot
We can get more information over here

Clear Cache and bug in Clearing Cache for partition

We can Clear Cache by using ClearCache command in XML for Analysis (XMLA)

for a specified Database, Cube, MeasureGroup ,dimension or partition.

How to create subcube in analysis service

It restricts cubes space to selected member in the select expression for create subcube statement


DistinctCount -- Analysis Service

Another Way for DistinctCount

As we all know that DistinctCount is a challenge for SSAS. Since its not an additive measure
AS needs additional efforts to get distinct count.

AddCalculatedMembers - MDX


By default when we create calculated members we can not see them while resolving member of a set.

IgnoreUnrelatedDimension Property - Analysis Service

Q:- In my fact table some dimension are related to some fact tables. For that dimension it shows same value for all attributes. I dont want to show values for this dimension.

IsAggregatable Property - Analysis Service

Q:- I want to hide All in my dimension. How can I do it? Is it good to do it?

Ans:- In Analysis Service we generally see all dimension has All member. This is because of IsAggregatable property of the attribute. You can set its value to false, so that it will not show All member.

How to manage partition

Partition are useful function for managing large data in database. I have posted an article on how to create partition. Now we will look into how to manage partitions.

First we will create partition as given in my past article.

Problem with multilingual character in SSAS

Q:- I have multilingual keywords in my table. It contains keywords chinese/japanese/arabic and many other languages. When I create a dimension I cant see that characters , it shows box and question mark.

Forceseek- Table Hint SQL 2oo8


It forces optimizer to use index seek only. Sometimes optimizer does not user proper plan and use index scan which cause high reads on the system. We can use forceseek here to force otpimizer to use index seek which can give better performance.

How to set ToolTip/Description for Reports

Many time we want to set ToolTip for our reports. It can give helpful information for the field in Reports. Also if we set description for Report it can give handful information for Reports. Lets see how we can set it.


Hi Friends

Well, this is a very popular section. I am getting number of queries and I try my best to give solution here.

SQL server basics/job interview questions Part 2

From Simon:-

Hi I want to get collation of my server. How can I get it?

Advanced Property SQL Server Reporting Service

Advanced Properties in SQL Server Reporting Service.

If you are using SQL Server Reporting Service 2008 then you can get Advanced property from Management Studio itself. Connect to Reporting Service in Management studio, Click on server node for properties then click on advanced.

Index Suggestion by Query Execution Plan - SQL Server 2008

I hope many of us we have start to use SQL Server 2oo8. SQL Server 2oo8 comes with many silent features. We all are familiar of execution plan of a query. We are getting visual display of query execution and cost of each action. Its very useful to understand how query will be executed.

How to get tablesize- SQL Server

Many times we need to get Size of tables and rows for each table for maintenance purpose.
Here I have given a script which will give
size information for each table using sp_spaceused procedure.

How to get last sales date/sales amount for each product -Analysis Service

How to get last date/last sales amount for each product when product was sold.

We are looking at a sample which will show last sales date and slaes amount for each product. Similarly we can get first date when product was sold using this function. We can use LastNonEmpty function but its not available except developers edition.

Like- SQL Server

Recently I got a query on how to use like clause. As we all know we can use to find word with pattern matching.
Here we will look a sample for it.

Memory properties in Analysis Service

It will allocated specified amount of memory to analysis service.By specifying preallocate Analysis Service uses large memory pages and we can not swap this to page file. It can give better performance to Analysis Service.

Except command -SQL Server 2005

Except command can be used when we want rows from table1 which are not in table2.
This command is supported in SQL 2005 and later versions.
Let see an example for this

Interesect Command(SQL Server 2005)

One of new feature of SQL Server 2005 is except and intersect commands.
Lets look how it can be useful.

How to get log file size

Recently one guy asked me how to get logfile size and free space in the log file.
We can get this information from dbcc sqlperf command.

Cahced Plans - Using System Tables

SQL Server stores query plan in cache for each query run on the server in sys.dm_exec_cached_plans system table. We can get information of query text, query plan , memory used, and count of query execution.

How to find missing indexes- SQL Server

As a developer its required to have proper index on tables for better performance.
But while number of person querying a table its not possible to track and analyze all queries.
In this case we need some metadata for all queries which shows us which queries does not have index and creating index can improve x% of performance.

Table Value Constructors

Table Value Constructors for Insert Statement

TABLE test (

id INT,
name VARCHAR(100))

SQL Formatter

SQL Formatter

Recently I come across a beautifull tool SQL Fromatter Tool SQL Pertty Printer. A wonderful tool for us who are just living on SQL. As we all know properly formatted SQL can be better readable and understandable than unformatted query.

How to get Identity Columns details

Many times developers need to know identity columns related information from tables.
After SQL Server 2005 , its easy for us to get this info with much rich informations.

How to get rowcount/size for eachtable.

Many times we want to get number of rows and spaceused by each table.
We can use sp_spaceused for this.
But when we want this info for all tables than we need some simple way.

SQL 2008 SP1 released

MS has released SP1 for SQL Server 2008.

To download click here

Also MS has released cumulative update of SP1.

To download click here

Filtered indexes Part2 - SQL Server 2008

To visit part 1 click here

Some benefit of filtered indexes
1. It has less performance hit. Since only data qualifying in the where condition are used , less data inserted/updated/deleted in index pages.

Filtered index part -1 , SQL 2008

Along with sparse columns SQL Server 2008 comes with two more advanced features

1.Filtered indexes
2.Filtered statistics

SQL server basics/job interview questions Part 3


From rezwan_marshall
Hi Amish- have questions.When upgrade to sql server sp3 1) Do I need to disconnect applications prior to sp3 installation and connect them back after? 2) What generic tests or (cursory patching test) can be done on sql server and the applications to verify that the upgrade was successful ? Thanks in advance -Marshall
6:21 PM

How to get backup history details

Many times we need to get backup details.
Below procedure which I am using to get backup info. It might be helpful to you guys also.

SQL Server 2008:- Insert Script with DATA

If we want to generate Insert Script with data values, it was not possible before SQL Server 2008.
Now SQL Server 2008 support insert script with data also.

Sparse Column - Part 3 Column Sets

Column Sets

SQL 2008 provides column_set for all sparse columns which can be used as a single set for all sparse columns. It can be used to display values or to update/insert values also.

Sparse column Part 1

Sparse ColumnsMSSQL 2008 has come with sparse columns,which is a solution for space taken by null columns. It is used when column has majority of null values. It actually takes no value for storing null values but it takes some more space for non null values.

Job Monitoring Part -3

In my past article we looked on how to monitor jobs using TSQL using procedure sp_get_composite_job_info
Now we will look some more parameters for it.

Job Monitoring Part - 2

In my past article we looked on how to monitor jobs using TSQL using procedure sp_get_composite_job_info
Now we will look some more parameters for it.

How to move tempdb

As a DBA sometimes we have to move our tempdb to other drive due to I/O or space issue.

Here we will look into how to move tempdb
Since tempdb is recreated every time we start server we dont have to move files physically.

How to know when SQL Server was started?

Many times we want to know when SQL Server was last started.
For this here is a simple way

Job Monitoring Part - 1 Get execution / enabled /disabled status

As a DBA we we have to regular play with jobs. Monitor jobs is a important activity for any DBA.

Here I am going to discuss an undocumented proc SP_GET_COMPOSITE_JOB_INFO which can be a great help for all of us.

New DateTime function in SQL Server 2008

SQL Server 2008 comes with some new datetime function ,
lets get some overview of it


Provide current date and time in datetime2 datatype

User Defined Table Types :- SQL Server 2008

We sometimes need to use table variable in our query.
Every time in the query we have to define the table variable.

Functions deprected after SQL Server 2008

Deprecated Option in SQL Server 2008

Some features are deprecated from next version of SQL Server 2008, here are some of the commons used functions which are deprecated and their replacements.
Look at BOL for more info

Grouping Sets- Cube and Rollup

Last article we show about grouping sets.

As we know that grouping set is union of different result sets generated by group by clause. Here in group by clause we can use also cube and rollup functions.

Grouping sets in SQL Server 2008

Its a new feature in SQL Server 2008. It generate a result of union all of group by result of different set of columns.
We can also use cube and rollup function with grouping sets.

Popular Posts