Database Engine
12.2K views | +0 today
Follow
 
Scooped by Irawan Soetomo
onto Database Engine
Scoop.it!

Indexed (materialized) views

Indexed (materialized) views | Database Engine | Scoop.it

Even if it sounds almost the same as the regular views, indexed views are completely different animals. That type of the views are not only about the abstraction but more about performance. When you create the indexed view, SQL Server “materializes” the data in the view into physical table so instead of doing complex joins, aggregates, etc, it can queries the data from that “materialized” table. Obviously it’s faster and more efficient.

 

Last thing I’d like to show is performance implications. Let’s insert the new order.


As you can see, it introduces nice performance hit because of the view support. Similar to the indexes – you have to pay the price of view maintenance for the benefit of performance improvements. Is it worth to do in your system? Hard to say especially if you have heavy loaded OLTP system. For Data Warehouse/Reporting/OLAP systems it could greatly benefit you. Another thing to keep in mind – indexed views shine when you use them with the aggregates.

No comment yet.
Database Engine
My collected tips on MS-SQL
Curated by Irawan Soetomo
Your new post is loading...
Your new post is loading...
Scooped by Irawan Soetomo
Scoop.it!

10 SQL tricks that you didn’t think were possible

10 SQL tricks that you didn’t think were possible | Database Engine | Scoop.it

This article will bring you 10 SQL tricks that many of you might not have thought were possible.

No comment yet.
Scooped by Irawan Soetomo
Scoop.it!

Getting normally-distributed random numbers

Getting normally-distributed random numbers | Database Engine | Scoop.it

When you are creating test data, you will, at some stage, hit the problem of simulating a distribution. There are plenty of different distributions. In the real world, variables such as height, rainfall, or examination results conform to a Normal or Gaussian distribution, where the mean is the median, and variables are more likely to have their values close to the median. The natural world is full of examples of variables that conform to the normal distribution, and exhibit the easily-recognised bell-curve when graphed by frequency. This is because the final distribution is the sum of a number of factors that are subject to chance variation.

Irawan Soetomos insight:
select (((rand(checksum(newid())) * 2 - 1) + (rand(checksum(newid())) * 2 - 1) + (rand(checksum(newid())) * 2 - 1)) * 0.17 + 0.50) from Tally where ID < 1000 order by 1

select rand(checksum(newid())) from Tally where ID < 1000 order by 1
No comment yet.
Scooped by Irawan Soetomo
Scoop.it!

SQL Server Agent – Skip Job Processing when Database is in Read-Only mode

SQL Server Agent – Skip Job Processing when Database is in Read-Only mode | Database Engine | Scoop.it

One of our scheduled jobs has been failing on a regular basis. When ran manually it works, but in the middle of the night at 3 AM, it claims to have ran successfully, but our numbers do not add up.

Irawan Soetomos insight:
-- exec only if db is rw
if databasepropertyex('DB_NAME', 'Updateability') = 'READ_WRITE'
exec p_SPName 'param'
No comment yet.
Scooped by Irawan Soetomo
Scoop.it!

Columnstore Indexes in SQL Server 2012

Columnstore Indexes in SQL Server 2012 | Database Engine | Scoop.it

The columnstore index in SQL Server 2012 stores columns instead of rows, and is designed to speed up analytical processing and data-warehouse queries. Whilst columnstore indexes certainly do that effectively, they are not a universal panacea since there are a number of limitations on them. When used appropriately, they can reduce disk I/O and use memory more efficiently.

Irawan Soetomos insight:
No comment yet.
Scooped by Irawan Soetomo
Scoop.it!

Calculate running total / running balance

SELECT TID, amt, RunningTotal = SUM(amt) OVER (ORDER BY TID ROWS UNBOUNDED PRECEDING) FROM dbo.Transactions ORDER BY TID;
No comment yet.
Scooped by Irawan Soetomo
Scoop.it!

Introducing SQL Server In-Memory OLTP

In-Memory OLTP, aka Hekaton, originally shipped with 2014, and although it certainly helped the performance of certain types of workload, it then had certain restrictions that impeded its widespread adoption.With SQL Server 2016, there is more support for In-Memory OLTP and a more seamless integration with SQL Server's Database Engine. It is time to consider whether In-Memory OLTP can help those pinch-points in your data throughput, explains Artemakis Artemiou.

No comment yet.
Scooped by Irawan Soetomo
Scoop.it!

SQL Server performance monitoring: dealing with deadlocks

SQL Server performance monitoring: dealing with deadlocks | Database Engine | Scoop.it

Not sure what to monitor on SQL Server? SQL Server MVP Grant Fritchey explains how SQL Server performance monitoring can help you find and fix deadlocks.

 
Irawan Soetomos insight:
 
No comment yet.
Scooped by Irawan Soetomo
Scoop.it!

What is SQL Server AlwaysOn?

SQL Server AlwaysOn is a popular term mentioned in various sources, but what does SQL Server AlwaysOn really mean? This tip will explain the term SQL Server AlwaysOn and its two main technologies.

Irawan Soetomos insight:
No comment yet.
Scooped by Irawan Soetomo
Scoop.it!

Find all SQL Stored Procedures with QUOTED_IDENTIFIER set to OFF

UPDATE failed because the following SET options have incorrect settings: 'QUOTED_IDENTIFIER'. Verify that SET options are correct for use with indexed views and/or indexes on computed columns and/or filtered indexes and/or query notifications and/or XML data type methods and/or spatial index operations.

SELECT SCHEMA_NAME(s.schema_id) + '.' + s.name AS name, s.create_date, s.modify_date, OBJECTPROPERTY(s.object_id,'ExecIsQuotedIdentOn') AS IsQuotedIdentOn FROM sys.objects s WHERE s.type IN ('P','TR','V','IF','FN','TF') AND OBJECTPROPERTY(s.object_id,'ExecIsQuotedIdentOn') = 0 ORDER BY SCHEMA_NAME(s.schema_id) + '.' + s.name DESC
 
Irawan Soetomos insight:
Get List of Computed Columns in Database Table https://stackoverflow.com/a/1484157/54908

SELECT * FROM sys.columns WHERE is_computed = 1
 
 
No comment yet.
Scooped by Irawan Soetomo
Scoop.it!

Forecasting with SQL

Forecasting with SQL | Database Engine | Scoop.it

So, if you are ever given the task of creating a time series forecast, with T-SQL as your tool, you will probably start by using one of the most common forecast models: simple linear regression with trend and seasonality. This forecast method is also known as the Holt-Winter’s model and its application will be the subject of this article. One take away is the demonstration of forecasting, though another useful aspect is the illustration of the math to calculate a trend line for a chart.

No comment yet.
Scooped by Irawan Soetomo
Scoop.it!

Query to Find Duplicate Indexes – Script to Find Redundant Indexes

I was recently delivering session on Performance Tuning subject. I was asking if there is any harm having duplicate indexes. Of course, duplicate indexes are nothing but overhead on the database system. Database system has to maintain two sets of indexes when it has to do update, delete, insert on the table which has duplicate indexes. There is also a possibility that indexes are overlapped.

Irawan Soetomos insight:
No comment yet.
Scooped by Irawan Soetomo
Scoop.it!

OFFSET and FETCH

Now, I’m not saying that this was previously not possible in SQL Server. There are solutions to this problem in other versions of the product in the form of temp tables, ROW_NUMBER() and TOP but I prefer OFFSET and FETCH to the others – it’s just simple!
No comment yet.
Scooped by Irawan Soetomo
Scoop.it!

Why does it take so long for SQL Management Studio to connect?

* Open Internet Explorer 
* Go to Tools -> Internet option 
* Open the "Advanced" tab 
* Uncheck "Check for server certificate revocation (requires restart)"
Irawan Soetomos insight:
No comment yet.
Scooped by Irawan Soetomo
Scoop.it!

Match only entire words with LIKE?

So 'awesome document' LIKE '%doc%' is true, because doc is a sub-string. But, I want it to be false while 'awesome doc' or 'doc awesome' or 'awesome doc awesome' should be true. How can I do with with a like?
Irawan Soetomos insight:
'#'+[MyColumn]+'#' like '%[^a-z0-9]doc[^a-z0-9]%'
No comment yet.
Scooped by Irawan Soetomo
Scoop.it!

Prevent multiple users from running the same SQL Server stored procedure at the same time

There is a stored procedure that must be run by only one user at a time. How do to I prevent multiple users from running the stored procedure at the same time? In this tip we look at using sp_getapplock and sp_releaseapplock.

Irawan Soetomos insight:
No comment yet.
Scooped by Irawan Soetomo
Scoop.it!

SQL Server Statistics Questions We Were Too Shy to Ask

SQL Server Statistics Questions We Were Too Shy to Ask | Database Engine | Scoop.it

If you need to optimise SQL Server performance, it pays to understand SQL Server Statistics. Grant Fritchey answers some frequently-asked questions about SQL Server Statistics: the ones we somehow feel silly asking in public, and think twice about doing so.

Irawan Soetomos insight:
Share your insight
No comment yet.
Scooped by Irawan Soetomo
Scoop.it!

Log Activity with sp_whoisactive

Create a new job and plop the below code into the job step, modifying the first 3 variables as needed. The code will create the logging table if it doesn’t exist, the clustered index if it doesn’t exist, log current activity and purge older data based on the @retention variable.

No comment yet.
Scooped by Irawan Soetomo
Scoop.it!

Calculating business days and holidays

A common scenario you may have encountered is the need to calculate the first business day after a given date. There are quite a few ugly ways to solve this, including cursors, but there are also some pretty neat ways to approach the problem, and as a bonus, you’ll learn about recursion and the new LEAD(), LAG() functions and accumulation in T-SQL.

No comment yet.
Scooped by Irawan Soetomo
Scoop.it!

T-SQL commands performance comparison - NOT IN vs NOT EXISTS vs LEFT JOIN vs EXCEPT

T-SQL commands performance comparison - NOT IN vs NOT EXISTS vs LEFT JOIN vs EXCEPT | Database Engine | Scoop.it

We conclude, first, that using the NOT EXISTS or the LEFT JOIN commands are the best choice from all performance aspects. We tried also to add an index on the joining column on both tables, where the query that uses the EXCEPT command enhanced clearly and showed a better performance, besides the NOT EXISTS command that still the best choice overall.

 
No comment yet.
Scooped by Irawan Soetomo
Scoop.it!

SQL Server Statistics: Problems and Solutions

SQL Server Statistics: Problems and Solutions | Database Engine | Scoop.it

SQL Server Statistics assist the query optimiser to calculate the best way of running the query. Holger describes every common way that things can go wrong with statistics, and how to put matters right.

No comment yet.
Scooped by Irawan Soetomo
Scoop.it!

A Common Misconception about MAXDOP

A Common Misconception about MAXDOP | Database Engine | Scoop.it

Out of the box, SQL Server is (and has been) able to take advantage of multiple processors/cores without any effort on behalf of administrators. In fact, for any query with a cost of greater than 5, SQL Server will (by default) use a parallel—meaning that it’ll look into breaking up work against multiple threads of execution.

No comment yet.
Scooped by Irawan Soetomo
Scoop.it!

List all triggers in database

SELECT [so].[name] AS [trigger_name], USER_NAME([so].[uid]) AS [trigger_owner], USER_NAME([so2].[uid]) AS [table_schema], OBJECT_NAME([so].[parent_obj]) AS [table_name], OBJECTPROPERTY( [so].[id], 'ExecIsUpdateTrigger') AS [isupdate], OBJECTPROPERTY( [so].[id], 'ExecIsDeleteTrigger') AS [isdelete], OBJECTPROPERTY( [so].[id], 'ExecIsInsertTrigger') AS [isinsert], OBJECTPROPERTY( [so].[id], 'ExecIsAfterTrigger') AS [isafter], OBJECTPROPERTY( [so].[id], 'ExecIsInsteadOfTrigger') AS [isinsteadof], OBJECTPROPERTY([so].[id], 'ExecIsTriggerDisabled') AS [disabled] FROM sysobjects AS [so] INNER JOIN sysobjects AS so2 ON so.parent_obj = so2.Id WHERE [so].[type] = 'TR'
No comment yet.
Scooped by Irawan Soetomo
Scoop.it!

DMV to gather history on queries run

SELECT deqs.last_execution_time AS [Time], dest.TEXT AS [Query], deqs.total_elapsed_time/1000000 as Total_Execution_time, deqs.total_worker_time/1000000 as CPU_time FROM sys.dm_exec_query_stats AS deqs CROSS APPLY sys.dm_exec_sql_text(deqs.sql_handle) AS dest ORDER BY deqs.last_execution_time DESC

No comment yet.
Scooped by Irawan Soetomo
Scoop.it!

Window Function Examples

Window (or Windowing) functions are a great way to get different perspectives on a set of data without having to make repeat calls to the server for that data. For example, we can gather the sum of a column and display it side-by-side with the detail-level data, such that “SalesAmount” and “SUM(SalesAmount)” can appear in the same row. We can also do analytical functions like PERCENT_RANK and ranking functions like ROW_NUMBER, all without altering the granularity of the result set or making additional trips to get the same source data again and again.

No comment yet.
Scooped by Irawan Soetomo
Scoop.it!

Features Supported by the Editions of SQL Server 2014

This topic provides details of features supported by the different editions of SQL Server 2014.
Irawan Soetomos insight:
No comment yet.