Database Engine
6.9K views | +10 today
Follow
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!

How to Recover SQL Database Data Deleted by Accident

How to Recover SQL Database Data Deleted by Accident | Database Engine | Scoop.it

With a good SQL Server disaster recovery strategy, data recovery is not a problem. You have a reliable full database backup with valid data, a full database backup and subsequent differential database backups, or a full database backup and a chain of transaction log backups. But not all situations are ideal. Here we’ll address some sub-optimal scenarios, where you can still successfully recover data.

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

How to setup alerts and email operator notifications

How to setup alerts and email operator notifications | Database Engine | Scoop.it

Setting up an alert to e-mail an operator with a message is a multiple step process. You can setup database mail, define an operator and an alert, but still no e-mail is being sent. So how do you send an alert to an operator?

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

Porter Stemming Algorithm

The Porter stemming algorithm (or ‘Porter stemmer’) is a process for removing the commoner morphological and inflexional endings from words in English. Its main use is as part of a term normalisation process that is usually done when setting up Information Retrieval systems.
more...
No comment yet.
Scooped by Irawan Soetomo
Scoop.it!

Tables having Identity Columns

select SchemaName = s.name, TableName = t.name from sys.schemas as s inner join sys.tables as t on s.schema_id = t.schema_id inner join sys.columns as c on t.object_id = c.object_id inner join sys.identity_columns as ic on c.object_id = ic.object_id and c.column_id = ic.column_id group by s.name, t.name order by s.name, t.name;
Irawan Soetomo's insight:
more...
No comment yet.
Scooped by Irawan Soetomo
Scoop.it!

RDBMS in the Cloud: Deploying SQL Server on AWS

Amazon Web Services (AWS) is a flexible, cost-effective, easy-to-use cloud computing platform. Relational database management systems, or RDBMS, are widely deployed within the Amazon cloud. In this whitepaper, we help you understand how to deploy SQL Server databases on AWS. You can run SQL Server databases on Amazon Relational Database Service (Amazon RDS) or Amazon Elastic Compute Cloud (Amazon EC2).

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

Columnstore Indexes Explained

Columnstore Indexes Explained | Database Engine | Scoop.it

Columnstore indexes were first released with SQL server 2012 and have now gained a shiny new polish with the release of SQL server 2014. MSDN claims an increase of performance of up to 10 times can be gained by using column store indexes. But what are they and how are they better?

Irawan Soetomo's insight:
more...
No comment yet.
Scooped by Irawan Soetomo
Scoop.it!

Natively Compiled Stored Procedures

Natively Compiled Stored Procedures | Database Engine | Scoop.it
In SQL Server, the classic stored procedures that we know and love are interpretive. This means that when a stored procedure is executed for the first time, the SQL Server relational engine first compiles it, and then executes it. By contrast, when we create a natively compiled stored procedure, SQL Server compiles it into machine code immediately, and stores it in a DLL.

As such, it's typically a good idea to save in-memory OLTP, as well as in-memory stored procedures for applications where speed and efficiency are of the utmost importance. However, for those tables and procedures that you can move to in-memory, you will often see a huge improvement in performance over standard disk-based stored procedures.
more...
No comment yet.
Scooped by Irawan Soetomo
Scoop.it!

Unique Indexes Are Code; Non-Unique Indexes Are Data

There are two kinds of database indexes: unique and non-unique. Unique indexes and non-unique indexes are different. They are as different as night and day. They are as different as men and women. They are as different as code and data. In fact, unique indexes are code and non-unique indexes are data, and that's how they should be treated.
more...
No comment yet.
Scooped by Irawan Soetomo
Scoop.it!

Difference Between SET QUOTED_IDENTIFIER ON and OFF setting


In this article we will discuss on the difference between SET QUOTED_IDENTIFIER ON and SET QUOTED_IDENTIFIER OFF. Please go through the article SET QUOTED_IDENTIFIER ON/OFF Setting in Sql Server to have detailed information on this setting. As a better practice we should always use SET QUOTED_IDENTIFIERS ON setting.


Irawan Soetomo's insight:
SET QUOTED_IDENTIFIER must be ON when you are creating or changing indexes on computed columns or indexed views. If SET QUOTED_IDENTIFIER is OFF, CREATE, UPDATE, INSERT, and DELETE statements on tables with indexes on computed columns or indexed views will fail. For more information about required SET option settings with indexed views and indexes on computed columns, see "Considerations When You Use the SET Statements" in SET Statements (Transact-SQL). https://msdn.microsoft.com/en-us/library/ms174393.aspx
more...
No comment yet.
Scooped by Irawan Soetomo
Scoop.it!

Changing the default backup folder

Changing the default backup folder | Database Engine | Scoop.it

The directories for the default data files, log files and backups are stored in the system registry. With SSMS you had the ability to change the backup location, but this was removed from SSMS for SQL 2005 and was later put back into SSMS with a later release of SQL Server.

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

SQL Server High Availability Solutions

SQL Server High Availability Solutions | Database Engine | Scoop.it

It is quite tough to decide what kind of high availability solution is available on the table and which one can fit all your production server requirements in terms of licenses, hardware cost, maintenance and professional services. In this blog post, I would like to explain every single SQL Server high availability solution that is currently feasible with the related pros and cos.

Irawan Soetomo's insight:
Share your insight
more...
No comment yet.
Scooped by Irawan Soetomo
Scoop.it!

Is it possible to Pass recordset to LIKE?

SELECT t1.* FROM Table1 AS t1 where exists(select t2.NamefromTable2 AS t2 where t1.Name like '%'+t2.Name+'%')

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

Indexed Views: The Basics

Indexed Views: The Basics | Database Engine | Scoop.it

When we add a unique clustered index to a view, we 'materialize' it. In other words, the 'virtual table' persists to disk, with its own page structure, and we can treat it just like a normal table. Any aggregations defined by the indexed view are now pre-computed, and any joins pre-joined, so the engine no longer has to do this work at execution time. SQL Server creates statistics for the indexed view, different from those of the underlying tables, to optimize cardinality estimations.

 

A well-crafted indexed view can write fewer pages to disk than the underlying tables, meaning fewer pages queries need to read fewer pages to return results. This means faster, more efficient queries. Use the techniques and tips in this article to ensure your views are optimal!

Irawan Soetomo's insight:

http://www.brentozar.com/archive/2013/11/what-you-can-and-cant-do-with-indexed-views/

 

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

Super fast splitter

declare 
@String varchar(8000)='Dog,Cat,House,Car', 
@Delimiter varchar(50) = ','

;
with 

cte1(N) As (Select 1 From (Values(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) N(N)), 

cte2(N) As (Select Top (IsNull(DataLength(@String),0)) Row_Number() over (Order By (Select NULL)) From (Select N=1 From cte1 a, cte1 b, cte1 c, cte1 d) A ), 

cte3(N) As (Select 1 Union All Select t.N+DataLength(@Delimiter) From cte2 t Where Substring(@String,t.N,DataLength(@Delimiter)) = @Delimiter), 

cte4(N,L) As (Select S.N,IsNull(NullIf(CharIndex(@Delimiter,@String,s.N),0)-S.N,8000) From cte3 S) 

Select Key_PS = Row_Number() over (Order By A.N) ,Key_Value = Substring(@String, A.N, A.L) ,Key_Pos = A.N From cte4 A

-- XML

Select Key_PS = Row_Number() over (Order By (Select null)), Key_Value = LTrim(RTrim(B.i.value('(./text())[1]', 'varchar(max)'))) From (Select x = Cast(''+ Replace(@String,@Delimiter,'')+'' as xml).query('.')) as A Cross Apply x.nodes('x') AS B(i)
more...
No comment yet.
Scooped by Irawan Soetomo
Scoop.it!

Update varbinary(MAX) field in SQLServer 2012 Lost Last 4 bits

SqlServer added half-byte at the beginning of the pattern so that it represent whole number of bytes.

select 0x1, 0x104 --> 0x01 and 0x0104
more...
No comment yet.
Scooped by Irawan Soetomo
Scoop.it!

Stored procedures and multiple result sets

- There’s a type xml in T-SQL. This type allows storing XML documents and accessing their nodes. 

- Objects of this type can be either converted to nvarchar() or constructed from it. 

- Objects of this type can be passed to stored procedures as output parameters. 

- You can easily generate XML from select queries.

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

Tables having Computed Columns

select sysobjects.name as TableName, syscolumns.name as ColumnName from syscolumns inner join sysobjects on syscolumns.id = sysobjects.id and sysobjects.xtype = 'U' where syscolumns.iscomputed = 1
Irawan Soetomo's insight:
more...
No comment yet.
Scooped by Irawan Soetomo
Scoop.it!

Log Shipping vs. Mirroring vs. Replication

Log Shipping vs. Mirroring vs. Replication | Database Engine | Scoop.it

Log Shipping:: It automatically sends transaction log backups from one database (Known as the primary database) to a database (Known as the Secondary database) on another server. An optional third server, known as the monitor server, records the history and status of backup and restore operations. The monitor server can raise alerts if these operations fail to occur as scheduled.


Mirroring:: Database mirroring is a primarily software solution for increasing database availability. It maintains two copies of a single database that must reside on different server instances of SQL Server Database Engine.


Replication:: It is a set of technologies for copying and distributing data and database objects from one database to another and then synchronizing between databases to maintain consistency. Using replication, you can distribute data to different locations and to remote or mobile users over local and wide area networks, dial-up connections, wireless connections, and the Internet.

Irawan Soetomo's insight:
more...
No comment yet.
Scooped by Irawan Soetomo
Scoop.it!

Implementing SQL Server In-Memory OLTP

Implementing SQL Server In-Memory OLTP | Database Engine | Scoop.it

How do you go about transferring a disk-based workload to the respective memory-optimized design? How do you process memory-optimized tables? How important for performance are natively-compiled Stored Procedures? Artemakis Artemiou comes up with a step-by-step guide to implementing an in-memory OLTP solution.

Irawan Soetomo's insight:
more...
No comment yet.
Scooped by Irawan Soetomo
Scoop.it!

Split strings the right way - or the next best way

Split strings the right way - or the next best way | Database Engine | Scoop.it

Aaron Bertrand (@AaronBertrand) of SQL Sentry explores some common approaches people use to split strings, complete with performance analysis.

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

Download and Install Adventure Works 2014 Sample Databases

Every new version of SQL Server should have its own Adventureworks database. The reason is that SQL Server comes up with new features with every version and most of the new features need a new dataset sample to demonstrate the capabilities of the features. This is the why every version of SQL Server has its own AdventureWorks database.

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

Conditional Order By

Conditional Order By | Database Engine | Scoop.it

A common scenario in many client-server applications is allowing the end user to dictate the sort order of results. Some people want to see the lowest priced items first, some want to see the newest items first, and some want to see them alphabetically. This is a complex thing to achieve in Transact-SQL because you can't just throw @SortColumn and @SortDirection into a straight T-SQL query.

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

A Beginner's Guide to the OUTPUT Clause

A Beginner's Guide to the OUTPUT Clause | Database Engine | Scoop.it

T-SQL supports the OUTPUT clause after the inception of SQL server 2005 and later editions. We can use the OUTPUT clause with DML statements (INSERT, DELETE, UPDATE) to return information from modified rows.

We primarily use the OUTPUT clause for auditing and archiving modified rows. In this tutorial, we will walk through the use of the OUTPUT clause with different DML statements and examples. First, we will create a table, dbo.Songs, and populate it with some data.

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

SQL Data Compare: Compare And Synchronize SQL Server Database Contents

SQL Data Compare: Compare And Synchronize SQL Server Database Contents | Database Engine | Scoop.it

SQL Data Compare is the industry-standard tool for comparing and deploying SQL Server database contents.

You can work with live databases, backups, or SQL scripts in source control. Damaged or missing data can be restored to a single row, without the need for a full database recovery.

 

SQL Data Compare helps you compare and deploy changes quickly, simply, and with zero errors.

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

Dynamic Search Conditions

It is very common in information systems to have functions where the users are able to search the data by selecting freely among many possible criterias. When you implement such a function with SQL Server there are two challenges: to produce the correct result and have good performance.

 
more...
No comment yet.