-->
SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 

SQL Mighty

I'm a passionate SQL Server professional with 10+ years of experience with databases. Developer, Administrator and Architect with 10+ years of expertise in data analysis, design, programming, performance tuning, upgrades, migrations, high availability solutions implementation, backup & recovery strategies and database capacity planning expertise.

SQL Server: DBCC CHECKDB Command with Complete Explanation

SQL Server is used widely for the management of database. It is possible that the data gets corrupt due to different reasons. Therefore, to deal with this issue SQL Server has a command, which act as a manual solution for corruption of the database. This command is referred as DBCC…

Read more

0 comments, 443 reads

Posted in SQL Mighty on 17 March 2017

Resolution of Database Consistency Error in SQL Server

There are so many facilities and amenities in SQL Server along with built-in methods, which assist users in some bad situations. It is possible that sometimes SQL server put the users in annoying circumstances when it becomes very difficult for them to access the database. At such situations, the very…

Read more

0 comments, 609 reads

Posted in SQL Mighty on 16 February 2017

SQL and the other technologies

Next table shows how SQL stands with the other technologies. The data is taken from StackOverflow.
Data charted looks like in the next figure
References:

Read more

0 comments, 197 reads

Posted in SQL Mighty on 28 January 2017

SCOPE_IDENTITY() or @@IDENTITY

I've seen a lot of usage of both functions in the T-SQL codes. However, their usage sometimes matters. The expectation from @@IDENTITY can sometimes be not the desired.
This post shows why you should use SCOPE_IDENTITY() instead of @@IDENTITY when using it with tables that have triggers.  
CREATE DATABASE IdentDB;

Read more

0 comments, 2,643 reads

Posted in SQL Mighty on 27 January 2017

Indexes optimize for the system databases

Maintenance of the system databases in SQL Server regarding the indexes is important too. The system tables also have indexes and their maintenance is of asset on busy environments.
It’s important to know that the indexes in msdb and master are marked as system objects, so if you have your…

Read more

0 comments, 299 reads

Posted in SQL Mighty on 23 December 2016

SQL Server and Hyper-threading

This post is about the CPU utilisation in the Standard, Web and Express editions of SQL Server because there is limitation for them. Licensing is given at the end of this post.
Physical environments
According to the official msdn references ([1], [2], [3]), Table…

Read more

0 comments, 245 reads

Posted in SQL Mighty on 20 December 2016

CPU Spikes Caused by Periodic Scheduled Jobs

In this article I’ll show you how to eliminate periodic spikes in the CPU that are caused by the jobs that execute periodically. Figure 1 shows such a situation. You can see the spikes occurring every minute.
Figure 1. CPU spikes caused by periodic-scheduled jobs
There is a good reason…

Read more

0 comments, 90 reads

Posted in SQL Mighty on 1 December 2016

What does an Index mean to a Query

This post is about the importance of a right index to a query. The following simple query was considered.
<?query --
SELECT
    [GroupBy1].[A1] AS [C1]
    FROM ( SELECT
        MAX([Extent1].[CreatedDate]) AS [A1]
        FROM [dbo].[GeoIP2Location] AS [Extent1] WITH (NOLOCK)
    )  AS [GroupBy1]
--?>
Even simple, this query was causing troubles on our…

Read more

0 comments, 82 reads

Posted in SQL Mighty on 11 November 2016

Changing SQL Server name

This post is about how to rename a stand-alone SQL server instance. There could be some reasons to do that: You simply forgot to change the name of your Sysprep server or You want to change from the default instance name to another and etc.
The dynamic view sys.servers enables…

Read more

0 comments, 94 reads

Posted in SQL Mighty on 2 October 2016

Be a good administrator

Administering SQL Server
Below are the topics every Administrator has to keep up with.
Backup / Restore:
Understand the backup / restore options SQL Server provides for you.Implement those on your databases to meet your users' needs.Test those backups regularly to make sure they are good and to give you…

Read more

0 comments, 53 reads

Posted in SQL Mighty on 19 September 2016

Target Recovery Interval and Indirect Checkpoint

Introduction
The process that writes the dirty pages from the system memory (RAM) to the disk is called the checkpoint process. Dirty pages consist of the data and log pages as well as the information for the transaction log. Detailed information about the checkpoint can be found here.
Indirect…

Read more

0 comments, 86 reads

Posted in SQL Mighty on 12 July 2016

Connect to SQL Azure database from SSMS

In this article I'll show you how to connect to SQL Azure database instance and create a simple database. First you have to own an Azure account. In the account you have to have a resource (your database server).  
Some of the details from your resource can be used for…

Read more

0 comments, 77 reads

Posted in SQL Mighty on 17 June 2016

Two new authentication options in SSMS 2016

Until SSMS 2014 there were the two standard authentication options for connecting to a SQL Server instance: Windows Authentication and SQL Server Authentication. The new options are available only for the Database Engine:
Active Directory Password Authentication, and
Active Directory Integrated Authentication
are the two new authentication options introduce
Active…

Read more

0 comments, 256 reads

Posted in SQL Mighty on 14 June 2016

The CHECK constraints unlike behaviours

The CHECK constraints enforce integrity by limiting the values that are accepted by one or more columns. You can create a CHECK constraint with any logical expression that returns TRUE or FALSE based on the logical operators.
CHECK constraints are similar to FOREIGN KEY constraints in that they control the…

Read more

0 comments, 64 reads

Posted in SQL Mighty on 13 June 2016

The COUNT function in T-SQL

The COUNT function is among the most used functions in the T-SQL codes. Even though COUNT is easy to use, it should be used carefully because it could often not return the desired result. For the big tables, the counting of the rows could cause blocking as well as take…

Read more

0 comments, 92 reads

Posted in SQL Mighty on 11 June 2016

ISJSON() restriction in T-SQL 2016

Have a database on SQL Server 2016 instance with an In-Memory file group so that you can create tables with the MEMORY_OPTIMIZED option set to ON. Like in the example below
--table
CREATE TABLE dbo.People (  Id int primary key nonclustered,  InfoJson nvarchar(max) -- nvarchar(1000), nvarchar(100) ) WITH (MEMORY_OPTIMIZED=ON)
try…

Read more

0 comments, 73 reads

Posted in SQL Mighty on 6 June 2016

SSMS new feature - Showplan comparison

A new functionality in SSSM 2016 is the ability to compare two execution plans. In one of the execution plans by right clicking and choosing the "Compare Showplan" option you're already having the comparison. 
Example
If you want to switch the orientation to vertical, then you have an option "Toggle…

Read more

0 comments, 93 reads

Posted in SQL Mighty on 2 June 2016

SSMS bug #1

This happens at my instance of SSMS and at the colleagues' one it doesn't. The SQL Server Agent duplicates its nodes like on the figure:
 Reconnecting to the instance doesn't resolve the issue. Restarting SSMS does it.

Read more

0 comments, 68 reads

Posted in SQL Mighty on 1 June 2016

SSMS new feature - Live execution plans

SQL Server 2016 brings one of the new features - The live execution plan. You can enable it by clicking the highlighted icon on the Toolbar.
Then you have a live flow of the execution plan.

Read more

0 comments, 68 reads

Posted in SQL Mighty on 24 May 2016

SSMS 2016 installation - The new SQL Server Management Studio 2016

This spackle hints toward installation of the SQL Server 2016 SSMS. The installation of SSMS 2016 is different and it's a separate installation. It's not a part of the SQL Server installation as it was the case until SQL Server 2014.
The link from the window below forwards you to…

Read more

0 comments, 105 reads

Posted in SQL Mighty on 24 May 2016

Older posts