Steps to Access a Database which is in Inaccess mode

Steps to Remove Database Inaccess Mode

select databaseproperty('irisdb_admin','isShutdown')

alter database irisdb_admin set offline

alter database irisdb_admin set online

Find table being locked in SQL Server 2008/2005

Find table being locked in SQL Server 2008/2005

SQL Scripts to find Locked table in SQL Server 2005/2008

Script for the list of tables which are being locked :

select object_name(resource_associated_entity_id) as 'TableName',*
     from sys.dm_tran_locks where resource_type = 'OBJECT'
     and resource_database_id = DB_ID()

Script for list of tables with schema mode change lock :

select object_name(resource_associated_entity_id) as 'TableName',*
     from sys.dm_tran_locks where resource_type = 'OBJECT'
     and resource_database_id = DB_ID()
     and request_mode = 'Sch-M'

Snapshot vs Log Shipping vs Mirroring vs Replication vs Failover Clustering

--->Snapshot : A Database Snapshot is a read-only, static view of a database (the source database). Multiple snapshots can exist on a source database and can always reside on the same server instance as the database. Each database snapshot is consistent, in terms of transactions, with the source database as of the moment of the snapshot’s creation.

--->Log Shipping : Log Shipping is an old technique available since SQL SERVER 2000. Here the transactional log (ldf) is transferred periodically to the standby server. If the active server goes down, the stand by server can be brought up by restoring all shipped logs.

--->Mirroring : Mirroring which was introduced with 2005 edition, works on top of Log Shipping. Main difference is the uptime for the standby server is quite less in mirroring. Standby server automatically becomes active in this case (through help of a broker server which is called as Witness in SQL SERVER parlance), without having to restore logs. Additional advantages of Mirroring include support at .NET Framework level plus some new features like Auto Page Recovery introduced with SQL SERVER 2008.

--->Replication : Replication is the process of copying data between two databases on the same server or different servers on the same database. This is one of the methods to maintain the redundant database site for disaster recovery purpose. Replication methodology maintains copies of main database from the primary server on the secondary sever, both of the databases will be in synchronization state and this technology is used to provide high availability.

--->Snapshot Replication: Snapshot replication is initiated at firs by all types of replication to initialize subscribers. In this method of replication, entire data will be captured and sent to the subscriber. What happens in this replication is, all the changes that occur in the publisher will be captured and then later sent to the subscriber when the snapshot agent runs again. Snapshot agent and distribution agents are used in the snapshot replication.
--->Merge Replication: Merge replication is the process of distributing data from publisher to the subscriber. In the merge replication, updates are allowed even when subscriber or publisher are connected or disconnected. If they are disconnected, the updates will be applied when both get connected. Merge agent and snapshot agent is used in merge replication. Merge replication is the most difficult replication method to implement and manage.
--->Transactional Replication: In transactional replication, all changes made in the publisher are captured and stored in the distribution database, and then these changes are transferred to the subscriber database. The best situation to use transactional replication is when data changes frequently and data is transferred to the subscriber immediately as in almost real time scenario.
--->Peer-Peer Replication: Peer-Peer replication is built on the foundation of the transactional replication. This replication method is introduced in SQL Server 2005. This replication is available only in SQL Server Enterprise Edition. Peer-to-Peer replication provides high availability solution by providing multiple copies of databases across multiple instances.

---> Failover Clustering : Failover Clustering is a high availability option only (unlike others above which can be used for disaster recovery as well) used with clustering technology provided by hardware + OS. Here the data / databases don’t belong to either of servers, and in fact reside on shared external storage like SAN. Advantages of a SAN storage is large efficient hot pluggable disk storage. You might see DR options like Mirroring used quite frequently with failover clustering. Here’s a good article on adding geo redundancy to a failover cluster setup.

SQL SERVER – Best Practices for Query Optimization

--> Table should have primary key
--> Table should have minimum of one clustered index
--> Table should have appropriate amount of non-clustered index
--> Non-clustered index should be created on columns of table based on query which is running
--> Following priority order should be followed when any index is created
a) WHERE clause
b) JOIN clause
c) ORDER BY clause
d) SELECT clause
--> Do not to use Views or replace views with original source table
--> Triggers should not be used if possible, incorporate the logic of trigger in stored procedure
--> Remove any adhoc queries and use Stored Procedure instead
--> Check if there is atleast 30% HHD is empty – it improves the performance a bit
--> If possible move the logic of UDF to SP as well
--> Remove * from SELECT and use columns which are only necessary in code
--> Remove any unnecessary joins from table
--> If there is cursor used in query, see if there is any other way to avoid the usage of this (either by SELECT … INTO or INSERT … INTO, etc)

Style of using @variable and IN operator

Style of Using @variable and IN Operator

Using Of @variable and IN Operator in single statment

create table test (companyid int, title varchar(100), description varchar(100), keywords varchar(100))

insert into test values(1,'company a','company a','aa')

insert into test values(1,'company b','company b','bb')

insert into test values(1,'company c','company c','cc')

insert into test values(1,'company d','company d','dd')



declare @keywords varchar(100)

set @keywords = 'aa,bb,cc,dd';



WITH CTE AS (

SELECT

CAST('' + REPLACE(@keywords, ',', '') + '' AS XML) AS Keywords

)



select * from test where keywords in (

SELECT RTRIM(LTRIM(ExtractedCompanyCode.X.value('.', 'VARCHAR(256)'))) AS Keywords

FROM CTE

CROSS APPLY Keywords.nodes('//I') ExtractedCompanyCode(X)

)

Common Table Expressions (CTE) :-

Common Tabel Expressions (CTE)

Common Table Expressions (CTEs) were one of the most exciting features to be introduced with SQL

Server 2005

A CTE is a "temporary result set" that exists only within the scope of a single SQL statement. It

allows access to functionality within that single SQL statement that was previously only available

through use of functions, temp tables, cursors, and so on...

CTE basics:-

The concept behind a CTE is simplicity itself. Consider the following statement:

with MyCTE(x)

as

(select x='hello')

select x from MyCTE

This defines a CTE called MyCTE. In brackets after the as keyword is the query that defines the CTE.

The subsequent query references our CTE, in this case simply returning the string "hello".

Like a derived table, a CTE lasts only for the duration of a query but, in contrast to a derived

table, a CTE can be referenced multiple times in the same query. So, we now we have a way of

calculating percentages and performing arithmetic using aggregates without repeating queries or using

a temp table:

with MyCTE(x)

as

(select top 10 x = id from sysobjects)

select x, maxx = (select max(x) from MyCTE), pct =100.0 * x / (select sum(x) from MyCTE)

from MyCTE

This returns (on my system):

	x           maxx         pct 

	4           2137058649   2.515723270440

	5           2137058649   3.144654088050

	7           2137058649   4.402515723270

	8           2137058649   5.031446540880

	13          2137058649   8.176100628930

	15          2137058649   9.433962264150

	25          2137058649   15.723270440251

	26          2137058649   16.352201257861

	27          2137058649   16.981132075471

	29          2137058649   18.238993710691

CTE and recursion:-

More interesting, in my opinion, is the use of recursion with CTEs. The table defined in the CTE

can be referenced in the CTE itself to give a recursive expression, using union all:

with MyCTE(x)

as
(

select x = convert(varchar(8000),'hello')

union all

select x + 'a' from MyCTE where len(x) < 100

)

select x from MyCTE

order by x

The query:

select x = convert(varchar(8000),'hello')

is called the anchor member. This is executed in the first pass and will populate the CTE with the

result, in this case hello. This initial CTE is repeatedly executed until the complete result set is

returned. The next entry:

select x + 'a' from MyCTE where len(x) < 100

is a recursive member as it references the CTE, MyCTE. The recursive member is executed with the

anchor member output to give helloa. The next pass takes helloa as input and returns helloaa, and so

on so that we arrive at a CTE populated with rows as follows:

hello

helloa

helloaa

helloaaa

helloaaaa

….........

MDX : MDX Tutorial, MDX Query, MDX Scrip, MDX Select Query.....

Hi friends,

I have seen developers searching for a last minute MDX query / script
to create a calculated member on the cube. Many start in an organized fashion
with a start from some simple MDX tutorial, and learning tuples,
namedsets, axis, MDX Select Query Syntax etc... But still after that,
many queries remain unsolved, and the real answer comes after a
hands-on experience of some real-time project when one really starts
applying functions, tweaking or nesting queries and parameters

Hope this helps all the last minute MDX requirements of those developers
who don't have time to spend in learning all the syntax, structure and
theory behing MDX syntax just to solve a little query or requirement
on a cube in production or last-minute patch.

MDX Various Queries

How do I find the bottom 10 customers with the lowest sales in 2003 that were not null?

SELECT { [Measures].[Internet Sales Amount] } ON COLUMNS , 

		BOTTOMCOUNT( NONEMPTY(DESCENDANTS( [Customer].[Customer Geography].[All Customers] 

		, [Customer].[Customer Geography].[Customer] ) 

		, ( [Measures].[Internet Sales Amount] ) ) 

		, 10 , ( [Measures].[Internet Sales Amount] ) ) ON ROWS 

	FROM [Adventure Works] WHERE ( [Date].[Calendar].[Calendar Year].&[2003] )

How in MDX query can I get top 3 sales years based on order quantity?

SELECT {[Measures].[Reseller Order Quantity]} ON 0

		, TopCount([Date].[Calendar].[Calendar Year].Members

		,3, [Measures].[Reseller Order Quantity]) ON 1 

	FROM [Adventure Works]

How do you extract first tuple from the set?

SELECT {{[Date].[Calendar].[Calendar Year].Members}.Item(0)} ON 0 

	FROM [Adventure Works]

How do you get Last month in the time dimension

SELECT ClosingPeriod([Date].[Calendar].[Month], [Date].[Calendar].DefaultMember) ON 0 

	FROM [Sales Summary] 

	WHERE ([Measures].[Sales Amount])

Need a MDX query that returns list of months from start of year up to specified month.

SELECT YTD([Date].[Calendar].[Month].&[2003]&[8]) ON 0 

	FROM [Sales Summary];

I Need an MDX statement to show the first day of the last month in the cube

SELECT OpeningPeriod([Date].[Calendar].[Date] 

		, ClosingPeriod([Date].[Calendar].[Month], [Date].[Calendar].DefaultMember) ) ON 0 

	FROM [Sales Summary]

How in the report can I order date dimension members in descending order?

SELECT {[Measures].[Reseller Order Quantity]} ON 0 

		, ORDER(Tail([Date].[Calendar].[Calendar Year].Members, 3) 

		, [Date].[Calendar].CurrentMember.Member_Key, DESC ) ON 1 

	FROM [Adventure Works]

I Need an MDX statement to get the last Month loaded into a cube SELECT ClosingPeriod([Date].[Calendar].[Month],

SELECT ClosingPeriod([Date].[Calendar].[Month], [Date].[Calendar].DefaultMember) ON 0 

	FROM [Sales Summary]

I Need an MDX statement to get the first month of the last year loaded into a cube

SELECT OpeningPeriod([Date].[Calendar].[Month] 

		, ClosingPeriod([Date].[Calendar].[Calendar Year], [Date].[Calendar].DefaultMember) ) ON 0 

	FROM [Sales Summary]

How do you write MDX query that uses execution date/time as a parameter?

SELECT {[Measures].[Internet Order Count]} ON 0 

		, {StrToMember("[Date].[Date].[" + Format(now(), "MMMM dd, yyyy") + "]") } ON 1 

	FROM [Direct Sales]

Need MDX Query to get latest months and previous years same months data

SELECT {ClosingPeriod([Date].[Calendar].[Month], [Date].[Calendar].DefaultMember) 

		, ParallelPeriod([Date].[Calendar].[Calendar Year] , 1 

		, ClosingPeriod([Date].[Calendar].[Month], [Date].[Calendar].DefaultMember) ) } ON 0 

	FROM [Sales Summary]

I need an MDX query to show year level data for all years except the last one, and month level data for the last year.

SELECT {NULL: ClosingPeriod([Date].[Calendar].[Calendar Year]

		, [Date].[Calendar].DefaultMember).PrevMember 

		, DESCENDANTS(ClosingPeriod([Date].[Calendar].[Calendar Year]

		, [Date].[Calendar].DefaultMember) , [Date].[Calendar].[Month]) } ON 0 

	FROM [Sales Summary]

How do you extract first member of the tuple?

SELECT {([Date].[Calendar].[Calendar Year].&[2003] 

		, [Customer].[Customer Geography].[Country].&[Canada]).Item(0) } ON 0 

	FROM [Adventure Works]

How can I compare members from different dimensions that have the same key values?

SELECT {[Measures].[Internet Order Count]} ON 0 

		, FILTER( NonEmptyCrossJoin( [Ship Date].[Date].Children, [Delivery Date].[Date].Children )

		, [Ship Date].[Date].CurrentMember.Properties('Key') = [Delivery Date].[Date].Properties('Key') ) ON 1 

	FROM [Adventure Works]

How can I get attribute key with MDX?

	WITH MEMBER Measures.ProductKey as [Product].[Product Categories].Currentmember.Member_Key 

	SELECT {Measures.ProductKey} ON 0, [Product].[Product Categories].Members on 1 

	FROM [Adventure Works]

How do you compare dimension level name to specific value?

WITH MEMBER [Measures].[TimeName] AS IIF([Date].[Calendar].Level IS [Date].[Calendar].[Calendar Quarter],'Qtr','Not Qtr') 

	SELECT [Measures].[TimeName] ON 0 

	FROM [Sales Summary] 

	WHERE ([Date].[Calendar].[Calendar Quarter].&[2004]&[3])

How do I calculate sales for 12 Month to date in MDX?

WITH MEMBER [Measures].[Last 12 Mth Order Count] AS 

		SUM( ClosingPeriod([Date].[Calendar].[Month], [Date].[Calendar].[All Periods]).Lag(12) : ClosingPeriod([Date].[Calendar].[Month]

		, [Date].[Calendar].[All Periods]) 

		, [Measures].[Order Count]) 

	SELECT [Measures].[Last 12 Mth Order Count] ON 0 

	FROM [Adventure Works]

How do I group dimension members dynamically in MDX?

WITH MEMBER [Product].[Category].[Case Result 1] AS ' Aggregate(Filter([Product].[Category].[All].children, [Product].[Category].currentmember.Properties("Key") < "3"))' 

	     MEMBER [Product].[Category].[Case Result 2] AS ' Aggregate(Filter([Product].[Category].[All].children, [Product].[Category].currentmember.Properties("Key") = "3"))' 

	     MEMBER [Product].[Category].[Case Result 3] AS ' Aggregate(Filter([Product].[Category].[All].children, [Product].[Category].currentmember.Properties("Key") > "3"))' 

	SELECT NON EMPTY {[Measures].[Order Count] } ON COLUMNS 

		, {[Product].[Category].[Case Result 1],[Product].[Category].[Case Result 2],[Product].[Category].[Case Result 3] } ON ROWS 

	FROM [Adventure Works]

How do you write MDX query that returns measure ratio to parent value?

WITH MEMBER [Measures].[Order Count Ratio To Parent] AS IIF( ([Measures].[Order Count], [Date].[Calendar].CurrentMember.Parent) = 0 

		, NULL , [Measures].[Order Count] / ([Measures].[Order Count], [Date].[Calendar].CurrentMember.Parent) ) 

		, FORMAT_STRING = "Percent" 

	SELECT {[Measures].[Order Count], [Measures].[Order Count Ratio To Parent]} ON 0 

		, {DESCENDANTS([Date].[Calendar].[All Periods], 1), [Date].[Calendar].[All Periods] } ON 1 

	FROM [Adventure Works]

MDX query to get sales by product line for specific period plus number of months with non empty sales.

WITH Member [Measures].[Months With Above Zero Sales] AS COUNT( DESCENDANTS({[Date].[Calendar].[Calendar Year].&[2003]: [Date].[Calendar].[Calendar Year].&[2004]} 

		, [Date].[Calendar].[Month]) * [Measures].[Sales Amount] , ExcludeEmpty ) 

	SELECT {[Measures].[Sales Amount]

		, [Measures].[Months With Above Zero Sales]} ON 0 

		, [Product].[Product Model Lines].[Product Line].Members on 1 

	FROM [Adventure Works] WHERE ([Date].[Calendar].[Calendar Year].&[2003]: [Date].[Calendar].[Calendar Year].&[2004])

How can I get Last (Previous) Year to Date (YTD) values?

WITH MEMBER [Measures].[Current YTD] AS SUM(YTD([Date].[Calendar].CurrentMember)

			, [Measures].[Internet Order Quantity]) 

	     MEMBER [Measures].[Last YTD] AS SUM(YTD(ParallelPeriod([Date].[Calendar].[Calendar Year] , 1 

		, [Date].[Calendar].CurrentMember)) , 

	   	    [Measures].[Internet Order Quantity] ) 

	SELECT {[Measures].[Current YTD] , [Measures].[Last YTD] } ON 0 

	FROM [Adventure Works] WHERE ([Date].[Calendar].[Date].[March 22, 2004])

How do you calculate monthly average of a year?

WITH MEMBER [Measures].[AvgVal] AS Avg( Descendants([Date].[Calendar].[Calendar Year].&[2004] 

			, [Date].[Calendar].[Month]), [Measures].[Internet Order Count] ) 

	SELECT {[Measures].[AvgVal]} ON 0 

	FROM [Adventure Works] 

	WHERE ([Product].[Product Model Lines].[Model Name].&[Classic Vest])

How do you calculate monthly average of a year including empty months?

WITH MEMBER [Measures].[AvgVal] AS Avg( Descendants([Date].[Calendar].[Calendar Year].&[2004] 

		, [Date].[Calendar].[Month]) 

		, CoalesceEmpty([Measures].[Internet Order Count], 0) ) 

	SELECT {[Measures].[AvgVal]} ON 0 

	FROM [Adventure Works] 

	WHERE ([Product].[Product Model Lines].[Model Name].&[Classic Vest])

Scope MDX

Can you use the Filter function in a SCOPE statement?

SCOPE ( Filter([Date].[Calendar].[Month].MEMBERS, [Date].[Calendar].Properties("Month of Year") = "January") ); 

	Measures.[Amount] = 10; 

	END SCOPE;

Can you create a SCOPE based on the Member property?

SCOPE ( Filter([Date].[Calendar].[Month].MEMBERS , [Date].[Calendar].Properties("Month of Year") = "January") ); 

	Measures.[Amount] = 10; 

	END SCOPE;

How do you specify SCOPE for a Dimension to include all members before a certain member

	SCOPE(NULL:[Date].[Calendar].[Calendar Quarter].&[2003]&[3]); This = 100; 

	END SCOPE;

How do you specify SCOPE for a Dimension to include all members after a certain member

SCOPE([Date].[Calendar].[Calendar Quarter].&[2003]&[3]: NULL); This = 100; 

	END SCOPE;

How Can I define SCOPE in MDX for all measures in one measure group?

SCOPE(MeasureGroupMeasures("Sales Orders")); This = ...; 

	END SCOPE;

How Can I define SCOPE in MDX for all but one measures in one measure group?

SCOPE(MeasureGroupMeasures("Internet Sales") - {[Measures].[Internet Tax Amount]}); 

	This = ...; 	

	END SCOPE;		

How Can I define SCOPE in MDX for all measures in multiple measure groups?

SCOPE({MeasureGroupMeasures("Internet Sales"), MeasureGroupMeasures("Internet Orders")

	     , MeasureGroupMeasures("Internet Customers") }); This = ...; 

	END SCOPE;