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
For previous part of this article
Discover Method Part 1
Hi I am working with SQL Server Database and BI (SSAS, SSRS,SSIS) since last 8 years with some of worlds large databases. Cheers Amish Shah
Discover Method Part 1
DISCOVER METHODS
They are very rich source for getting meta data information for the server.
Lets look for more detail on DISCOVER METHODS
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
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.
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.
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.
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.
How to get comma separated list of members for Dimension Attribute
Q:- I want a comma separated list of all members of attribute
How can I get it?
How can I get it?
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
http://www.powerpivot.com/
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
http://www.powerpivot.com/
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.
DRILLTHROUGH - Analysis Service
DRILLTHROUGH [MAXROWS Unsigned_Integer]
select mdx statement
Return set of attributes and measures
select mdx statement
Return set of attributes and measures
How to create subcube in analysis service
It restricts cubes space to selected member in the select expression for create subcube statement
CREATE SUBCUBE
CREATE SUBCUBE
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.
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
AddCalculatedMembers
By default when we create calculated members we can not see them while resolving member of a set.
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.
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.
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
Forceseek
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.
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.
ALL SQL Q&A
Hi Friends
Well, this is a very popular section. I am getting number of queries and I try my best to give solution here.
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.
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.
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
Preallocate
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.
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
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.
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.
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.
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
CREATE TABLE test (
CREATE 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.
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.
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.
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
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.
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
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
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.
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.
Now SQL Server 2008 support insert script with data also.
How to do running sum using CTE - SQL Server
Many time we need to do runing sum of value.
In SQL 2oo5 we can do it with use of CTE
In SQL 2oo5 we can do it with use of CTE
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.
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 Columns Part 2
In last article we look into what is sparse columns and how to use it. You can visit Part 1 from here
Sparse Column Part 1
Sparse Column Part 1
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.
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.
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.
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
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.
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
SYSDATETIME
Provide current date and time in datetime2 datatype
lets get some overview of it
SYSDATETIME
Provide current date and time in datetime2 datatype
Multi Select - Browsing Problem. SQL Server Reporting Service
Bug in Reporting Service
In parameters option for Multi Select is available from SQL 2005 Reporting Service.
In parameters option for Multi Select is available from SQL 2005 Reporting Service.
Except Function - MDX Analysis Service 2005
SQL Server Analysis Service 2005
Except in MDX
Usage:-
Except (Set1, Set2, ALL)
Except in MDX
Usage:-
Except (Set1, Set2, ALL)
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.
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
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.
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.
We can also use cube and rollup function with grouping sets.
Subscribe to:
Posts (Atom)
Popular Posts
-
sp_addlinkedsrvlogin To modify or add new login for linked server
-
Recently we had hardware and performance issues on SQL Server,with our client , so while monitoring the system we also looked at error ... -
Recently I got a problem where Builtin\Administrators Account was deleted . How can we recreate it? Here is a solution for this Run th... -
As we all are aware with the variables in SSIS, which is used for holding custom values and assign to various properties of objects at run...
-
Recently I got a mail for following problem While the client tries to open registered server window in Mbanagement Studio he gets follow... -
Various way to calculate running total from All period to currentmember As MDX is a very vast and dynamic language we can write same quer...
-
Many time we need to do runing sum of value. In SQL 2oo5 we can do it with use of CTE -
sp_purge_jobhistory We can use this procedure to delete old history for jobs.
-
Index are very important part for any database. We need to regularly monitor index and maintain index for better performance of the system... -
For every Developer and DBA index maintenance is very important task. Since the changing ...