What is a database?
I thought I would write this article for people who have no experience in SQL Server or know what a database is.
A database is a method of storing information where that information can be accessed to answer or to solve questions. There are many types of databases that you can use from many different companies like
• IBM DB2
With every database, you have to ability to insert, delete, update and search information that is stored within them.
Here are some examples that you would use a database
• Storing your music collection information
• Storing a sports team activity
• Storing pupils attendance at a school.
A vast amount of things that you use today will have some built-in database system for them to store data, i.e. your mobile phone.
Microsoft have a database software called SQL Server which they have been developing since 1989 and the current version of SQL Server is called SQL Server 2017.
There are many different versions of SQL Server 2017 that are designed for different people and different solutions. The main versions that people use are
• SQL Server Enterprise Edition
• SQL Server Standard Edition
• SQL Server Business Intelligence Edition
• SQL Server Developer Edition
• SQL Server Express
SQL Server Enterprise, SQL Server Standard and SQL Server Business Intelligence Edition all cost money to use in a production/business environment
SQL Server Developer Edition is free and can only be used on test systems and cannot be used in production/business environment
SQL Server Express can be used in a test environment and production environment for free.
SQL Server Enterprise and SQL Server Developer have all the features enabled and SQL Server Express has limited features enabled.
Within SQL Server, you can create a database and a database is a collection of tables that store the data. You access this data by using a special database language called T-SQL or you can use a frontend application as well.
If you found this useful, just leave a comment below
Performance Tuning with Query Store in SQL Server 2017
If you want to monitor the performance of your queries within SQL Server 2017, you can with a feature called Query Store. Query Store monitors changes to your queries to see if performance deteriorates or improves when the query plan changes.
When you create a new database, is disabled by default; you need to enable this feature.
To enable Query Store, do the following steps
1. In Object Explorer, right-click a database, and then click Properties.
2. In the Database Properties dialogue box, select the Query Store page.
3. In the Operation Mode (Requested) box, select On.
You would use Query Store when you want to
• Find queries that have become slow over time and fix them by forcing the queries to use a different query plan.
• Find out how many times a query was run in a set period.
• Find out how many times the query plan was changed.
• Analysis why your query plan changes.
The best way to learn about
is to play around with it so just enable it and refresh the database, so it looks like this
Then just double click on each item in the query store to see what they do. It is better to enable Query Store on a database that has many transactions so you will be able to see results appear fast. I would suggest using a development server to test this feature as turning on this feature can have a minor effect on performance on your server.
If you have any comments on this article, just leave a comment below.
SQL Server Information from a script.
SQL Server has a lot of useful information that you need to administrate or develop solutions. Here is a great SQL Script to find out information about your SQL Server Instance and operating system.
-- Get selected server properties SELECT SERVERPROPERTY('MachineName') AS [MachineName], SERVERPROPERTY('ServerName') AS [ServerName], SERVERPROPERTY('InstanceName') AS [Instance], SERVERPROPERTY('IsClustered') AS [IsClustered], SERVERPROPERTY('ComputerNamePhysicalNetBIOS') AS [ComputerNamePhysicalNetBIOS], SERVERPROPERTY('Edition') AS [Edition], SERVERPROPERTY('ProductLevel') AS [ProductLevel], SERVERPROPERTY('ProductVersion') AS [ProductVersion], SERVERPROPERTY('ProcessID') AS [ProcessID], SERVERPROPERTY('Collation') AS [Collation], SERVERPROPERTY('IsFullTextInstalled') AS [IsFullTextInstalled], SERVERPROPERTY('IsIntegratedSecurityOnly') AS [IsIntegratedSecurityOnly], SERVERPROPERTY('IsHadrEnabled') AS [IsHadrEnabled], SERVERPROPERTY('HadrManagerStatus') AS [HadrManagerStatus], SERVERPROPERTY('IsXTPSupported') AS [IsXTPSupported]; -- Windows information SELECT windows_release, windows_service_pack_level, windows_sku, os_language_version FROM sys.dm_os_windows_info WITH (NOLOCK) OPTION (RECOMPILE); -- Get configuration values for instance SELECT name, value, value_in_use, minimum, maximum, [description], is_dynamic, is_advanced FROM sys.configurations WITH (NOLOCK) ORDER BY name OPTION (RECOMPILE); -- SQL Server Services information SELECT servicename, process_id, startup_type_desc, status_desc, last_startup_time, service_account, is_clustered, cluster_nodename, [filename] FROM sys.dm_server_services WITH (NOLOCK) OPTION (RECOMPILE); -- Get processor description from Windows Registry EXEC xp_instance_regread N'HKEY_LOCAL_MACHINE', N'HARDWARE\DESCRIPTION\System\CentralProcessor\0', N'ProcessorNameString'; -- Volume info for all LUNS that have database files on the current instance SELECT DISTINCT vs.volume_mount_point, vs.file_system_type, vs.logical_volume_name, CONVERT(DECIMAL(18,2),vs.total_bytes/1073741824.0) AS [Total Size (GB)], CONVERT(DECIMAL(18,2),vs.available_bytes/1073741824.0) AS [Available Size (GB)], CAST(CAST(vs.available_bytes AS FLOAT)/ CAST(vs.total_bytes AS FLOAT) AS DECIMAL(18,2)) * 100 AS [Space Free %] FROM sys.master_files AS f WITH (NOLOCK) CROSS APPLY sys.dm_os_volume_stats(f.database_id, f.[file_id]) AS vs OPTION (RECOMPILE); -- File names and paths for TempDB and all user databases in instance SELECT DB_NAME([database_id]) AS [Database Name], [file_id], name, physical_name, type_desc, state_desc, is_percent_growth, growth, CONVERT(bigint, growth/128.0) AS [Growth in MB], CONVERT(bigint, size/128.0) AS [Total Size in MB] FROM sys.master_files WITH (NOLOCK) WHERE [database_id] > 4 AND [database_id] <> 32767 OR [database_id] = 2 ORDER BY DB_NAME([database_id]) OPTION (RECOMPILE)
After you have downloaded the Microsoft SQL Server Management Studio, you will find it is easy to install. Just double click the setup file and you should see something like this.
Just click the ‘Install’ button.
After you have installed the software, you can find the SQL Server Management Studio in Windows menu. When you open the software, you should see a splash screen.
Once the software is open, you should see a screen like this.
I will write more blog posts on how to use SQL Server Managment Studio in the future.
If you find this useful, just leave a comment below.
SQL Server 2017 Installation Guide
If you have downloaded SQL Server 2017 and you want to install it but you are not too sure how to? Well, this is a guide will help you through the steps needed to install SQL Server 2017.
I will presume you have already downloaded SQL Server 2017 so the first thing to do after you have download SQL Server 2017 is to run the setup file.
You should see a screen like the above.
After the splash screen has finished loading, you will see several options that you can pick.
I will show you how to do a custom install of SQL Server so you can see the options that are available to you. Press the ‘Custom’ button then on the next screen press ‘Install.’
You should see a screen like this if anything needed downloading.
Once this is complete, it is now time to install SQL Server 2017.
If you click on ‘Planning’ option, you can navigate through all the options to see if your PC/laptop has the correct specifications to run SQL Server 2017. After you are satisfied that your PC/laptop will be able to run SQL Server 2017, click the Installation tab and Click “New SQL Server stand-alone installation or add features to an existing installation.”
On the Product Key screen press Next (Developer should be highlighted)
On the Licence Screen press Next, after you have read the terms and conditions (I bet you will not read the terms and conditions)
Check the box if you want the Microsoft Updates, it is a good idea to accept them.
Click next until you come to Install Rules and if you cannot click Next on this screen, fix any errors that it shows you.
On the feature selection screen, you have many options that you can select for features you want to install within SQL Server. The only one that you need to install is the database engine so select ‘Database Engine Services’ and press next.
On the Instance Configuration screen, you have two choices; you can pick Default Instance or Named Instance.
An Instance is the name of your SQL Server.
You will use Default Instance if this is going to be the first installation of a SQL Server.
You would use Named Instances if you wanted to install multiple SQL Servers on to the same PC/Laptop
InstanceID is usually the name of your PC/Laptop if you have selected the Default Instance. If you choose Named Instance, then you can give the new installation a name. So if your PC is called ‘SELECTSQLSERVER’ and you wanted your SQL Server to have a special name like ‘STOCK’ then you would type in STOCK in the InstanceID.
I will show in a later blog on how you would connect to a named server and default server.
Click Next on the Instance Configuration.
Server Configuration – Click Next
Database Engine Configuration– Click Add Current User. You have two ways to connect to SQL Server. Windows Authentication or Mixed Mode. You will use Mixed Mode if you want to access SQL Server without a Windows Account. Click Next
On the Ready To Install screen, click install.
That is is, let me know in the comments below if you have trouble installing SQL Server 2017.
So you have come across a problem in a SQL Server query where you want to find out if a value is null and if it is, use another value. Which function should you use, ISNULL or COALESCE?
Well if you just want to do something simple and to evaluate the value to see if it’s NULL and use another value, then both commands would work if the other number is NOT NULL.
If you wanted to do something more advanced and compare a series of numbers and use the first number that is not null, then COALESCE is the function that you have to use.
Run the code into a SQL Server Environment to see how COALESCE works
ISNULL vs Coalese Code Example
SET NOCOUNT ON;
IF EXISTS (SELECT name FROM sys.tables
WHERE name = ‘wages’)
DROP TABLE wages;
CREATE TABLE wages
emp_id tinyint identity,
hourly_wage decimal NULL,
salary decimal NULL,
commission decimal NULL,
num_sales tinyint NULL
INSERT wages VALUES(10.00, NULL, NULL, NULL);
INSERT wages VALUES(20.00, NULL, NULL, NULL);
INSERT wages VALUES(30.00, NULL, NULL, NULL);
INSERT wages VALUES(40.00, NULL, NULL, NULL);
INSERT wages VALUES(NULL, 10000.00, NULL, NULL);
INSERT wages VALUES(NULL, 20000.00, NULL, NULL);
INSERT wages VALUES(NULL, 30000.00, NULL, NULL);
INSERT wages VALUES(NULL, 40000.00, NULL, NULL);
INSERT wages VALUES(NULL, NULL, 15000, 3);
INSERT wages VALUES(NULL, NULL, 25000, 2);
INSERT wages VALUES(NULL, NULL, 20000, 6);
INSERT wages VALUES(NULL, NULL, 14000, 4);
SET NOCOUNT OFF;
SELECT CAST(COALESCE(hourly_wage * 10 * 10, salary, commission * num_sales) AS money) AS ‘Total Salary’
There are some things to think about when using COALESCE, if you want to evaluate a series of numbers and all the values are NULL, at least one has to be a typed NULL
SELECT COALESCE(NULL, NULL);
will result in
Msg 4127, Level 16, State 1, Line 1
At least one of the arguments to COALESCE must be an
expression that is not the NULL constant.
but this will work
SELECT COALESCE(CAST(NULL AS INT), NULL);
Using ISNULL does not error the code, so this will work
SELECT ISNULL(NULL, NULL);
There are some minor differences between ISNULL and COALESCE and can act differently in different scenarios that you have to think about the outcome.
Did this help you, leave a comment below!
I hate when a database in SQL Server goes into Suspect mode.
If you a database administrator in SQL Server, one of the worst things that could happen to you is that you arrive at work one day and find out that one of your databases is in ‘Suspect’ mode in SQL Server.
What does Suspect mode mean, basically it means that your database is not working and you better hope you have a backup SQL Server. 🙂
You should always have a good backup and restore strategy in place for recovering from a Suspect database, but if you have not, there might be a temporary way of you turning on the database again to pull data out.
You should not use the below code as a way to recover then use the database because from my experience; your database is corrupted, (even if you run DBCC CHECKDB as it tells you everything is ok)
Recovering From Suspect Mode Code In SQL Server
EXEC sp_resetstatus <sql server database name>;
ALTER DATABASE <sql server database name> SET EMERGENCY
DBCC checkdb(<sql server database name>)
ALTER DATABASE <sql server database name> SET SINGLE_USER WITH ROLLBACK IMMEDIATE
DBCC CheckDB (<sql server database name>, REPAIR_ALLOW_DATA_LOSS)
ALTER DATABASE <sql server database name> SET MULTI_USER
Once you have run this code SQL Server., you are freely available to have a look at your data. My suggestion is that you copy all of the data into another database and use the new database in the future. Moving the data may take some time, but it will be worth it in the end. If you use the Suspect database in the future, what could happen is that the Suspect database might become corrupted again and there will be no fix for it. Also if you continue to use the Suspect database, your backups also have hidden faults in there.
After you have moved your data to a new database, I would suggest you investigate why the database went into the ‘Suspect’ mode in the first place SQL Server.
This could have happened from a power failure or a corrupted hard drive, but you need to make sure you find the root-cause because it could happen again.
Did this help? leave a comment below.
It’s still useful to know a DBCC command that you may need to perform on a server when your server is acting funny. Here are some common DBCC commands that you may need.
DBCC CHECKALLOC checks page usage and allocation in the database. Use this command if allocation errors are found for the database. If you run DBCC CHECKDB, you do not need to run DBCC CHECKALLOC, as DBCC CHECKDB includes the same checks (and more) that DBCC CHECKALLOC performs.
This command checks for consistency in and between system tables. This command is not executed within the DBCC CHECKDB command, so running this command weekly is recommended.
DBCC CHECKCONSTRAINTS alerts you to any CHECK or constraint violations.
Use it if you suspect that there are rows in your tables that do not meet the constraint or CHECK constraint rules.
A very important DBCC command, DBCC CHECKDB should run on your SQL Server instance on at least a weekly basis. Although each release of SQL Server reduces occurrences of integrity or allocation errors, they still do happen. DBCC CHECKDB includes the same checks as DBCC CHECKALLOC and DBCC CHECKTABLE. DBCC CHECKDB can be rough on concurrency, so be sure to run it at off-peak times.
DBCC CHECKTABLE is almost identical to DBCC CHECKDB, except that it is performed at the table level, not the database level. DBCC CHECKTABLE verifies index and data page links, index sort order, page pointers, index pointers, data page integrity, and page offsets. DBCC CHECKTABLE uses schema locks by default but can use the TABLOCK option to acquire a shared table lock. CHECKTABLE also performs object checking using parallelism by default (if on a multi-CPU system).
DBCC CHECKFILEGROUP works just like DBCC CHECKDB; only DBCC CHECKFILEGROUP checks the specified filegroup for allocation and structural issues. If you have a very large database (this term is relative, and higher-end systems may be more apt at performing well with multi-GB or TB systems), running DBCC CHECKDB may be time-prohibitive.
If your database is divided into user-defined filegroups, DBCC CHECKFILEGROUP will allow you to isolate your integrity checks, as well as stagger them over time.
DBCC CHECKIDENT returns the current identity value for the specified table and allows you to correct the identity value if necessary.
If your database allows modifications and has indexes, you should rebuild your indexes on a regular basis. The frequency of your index rebuilds depends on the level of database activity, and how quickly your database and indexes become fragmented. DBCC DBREINDEX allows you to rebuild one or all indexes for a table. Like DBCC CHECKDB, DBCC CHECKTABLE, DBCC CHECKALLOC, running DBREINDEX during peak activity times can significantly reduce concurrency.
Microsoft introduced the excellent DBCC INDEXDEFRAG statement beginning with SQL Server 2000. This DBCC command, unlike DBCC DBREINDEX, does not hold long-term locks on indexes. Use DBCC INDEXDEFRAG for indexes that are not very fragmented. Otherwise, the time this operation takes will be far longer than running DBCC DBREINDEX. In spite of its ability to run during peak periods, DBCC INDEXDEFRAG has had limited effectiveness compared to DBCC DBREINDEX (or drop/create index).
The DBCC INPUTBUFFER command is used to view the last statement sent by the client connection to SQL Server. When calling this DBCC command, you designate the SPID to examine. (SPID is the process ID, which you can get from viewing current activity in Enterprise Manager or executing sp_who. )
DBCC OPENTRAN is a Transact-SQL command that is used to view the oldest running transaction for the selected database. The DBCC command is very useful for troubleshooting orphaned connections (connections still open on the database but disconnected from the application or client), and identification of transactions missing a COMMIT or ROLLBACK. This command also returns the oldest distributed and undistributed replicated transactions, if any exist within the database. If there are no active transactions, no data will be returned. If you are having issues with your transaction log not truncating inactive portions, DBCC OPENTRAN can show if an open transaction may be causing it.
You may not use this too frequently. However, it is an interesting DBCC command to execute periodically, particularly when you suspect you have memory issues. DBCC PROCCACHE provides information about the size and usage of the SQL Server procedure cache.
The DBCC SHOWCONTIG command reveals the level of fragmentation for a specific table and its indices. This DBCC command is critical to determining if your table or index has internal or external fragmentation. Internal fragmentation concerns how full an 8K page is.
When a page is underutilised, more I/O operations may be necessary to fulfil a query request that if the page was full, or almost full.
External fragmentation concerns how contiguous the extents are. There are eight 8K pages per extent, making each extent 64K. Several extents can make up the data of a table or index. If the extents are not physically close to each other and are not in order, performance could diminish.
DBCC SHRINKDATABASE shrinks the data and logs files in your database.
Avoid executing this command during busy periods in production, as it has a negative impact on I/O and user concurrency. Also remember that you cannot shrink a database past the target percentage specified, shrink smaller than the model database, shrink a file past the original file creation size, or shrink a file size used in an ALTER DATABASE statement.
DBCC SHRINKFILE allows you to shrink the size of individual data and log files. (Use sp_helpfile to gather database file ids and sizes).
16. DBCC TRACEOFF, TRACEON, TRACESTATUS
Trace flags are used within SQL Server to enable or disable specific SQL Server instance characteristics temporarily. Traces are enabled using the DBCC TRACEON command and disabled using DBCC TRACEOFF. DBCC TRACESTATUS is used to displays the status of trace flags. You’ll most often see TRACEON used in conjunction with deadlock logging (providing more verbose error information).
Execute DBCC USEROPTIONS to see what user options are in effect for your specific user connection. This can be helpful if you are trying to determine if your current user options are inconsistent with the database options.
SQL Server 2017 Developer Edition is free!
Yes that is right, you can download SQL Server 2017 for free just by clicking on this link from Microsoft
The Developer Edition is the same as the Enterprise Edition, but the only difference is that you can’t use the Developer Edition in a production environment.
Downloading SQL Server 2017 Developer Edition is still great if you want to learn about all the new features that are now in SQL Server 2017.
If you don’t know what SQL Server is, well SQL Server from Microsoft it is a software solution to store and analysis data using a variety of tools.
You will mainly use
SQL Server Engine
SQL Server Integration Services
SQL Server Analysis Services
SQL Server Engine
This is where raw data is stored as information, and you can do a variety of tasks on this data to suit your needs.
SQL Server Integration Services
This tool is used to extract, transform and load information in a database or another storage locations
SQL Server Analysis Services
This is used for analysis information to try and find out patterns or summary information.
If you have never used SQL Server before, just download it and install it and try it out.
What features are you looking forward to in this Edition? Leave a comment below