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 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…
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…
Next table shows how SQL stands with the other technologies. The data is taken fromStackOverflow.
Data charted looks like in the next figure
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;
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 inmsdbandmasterare marked as system objects, so if you have your own customised scripts you…
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.
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…
This post is about the importance of a right index to a query. The following simple query was considered.
[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…
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 yourSysprepserver or You want to change from the default instance name to another and etc.
The dynamic viewsys.serversenables you to see the…
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…
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 foundhere.
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…
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, andActive Directory Integrated Authentication
are the two new authentication options introduce
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…
TheCOUNTfunction 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…
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
CREATE TABLE dbo.People (Id int primary key nonclustered,InfoJson nvarchar(max) -- nvarchar(1000), nvarchar(100)) WITH (MEMORY_OPTIMIZED=ON)
try to create the…
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.
If you want to switch the orientation to vertical, then you have an option "Toggle…
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…