SQL Server Functions

SQL Server Functions

Sometimes you read books on subjects about SQL Server and you just want to learn the basics, well here are the basics of SQL Server Functions

Functions can be Scalar or Table-valued
Scalar returns one value and Table-valued functions (TVF) returns…well a table of results and this are usually found in the FROM clause of a statement.

Functions can be either Deterministic or Nondeterministic
Deterministic = This means they return the same value any time they are called with a specific set of input values.
i.e. SELECT LEN(‘TEST) will always return 4

Nondeterministic = different results every time
i.e SELECT GETDATE()

Example code is taken from the MSDN website

The following example creates the user-defined function (Scalar) ISOweek. This function takes a date argument and calculates the ISO week number. For this function to calculate correctly, SET DATEFIRST 1 must be invoked before the function is called.
IF OBJECT_ID (N’dbo.ISOweek’, N’FN’) IS NOT NULL
DROP FUNCTION dbo.ISOweek;
GO
CREATE FUNCTION dbo.ISOweek (@DATE datetime)
RETURNS int
WITH EXECUTE AS CALLER
AS
BEGIN
DECLARE @ISOweek int;
SET @ISOweek= DATEPART(wk,@DATE)+1
-DATEPART(wk,CAST(DATEPART(yy,@DATE) as CHAR(4))+’0104′);
–Special cases: Jan 1-3 may belong to the previous year
IF (@ISOweek=0)
SET @ISOweek=dbo.ISOweek(CAST(DATEPART(yy,@DATE)-1
AS CHAR(4))+’12’+ CAST(24+DATEPART(DAY,@DATE) AS CHAR(2)))+1;
–Special case: Dec 29-31 may belong to the next year
IF ((DATEPART(mm,@DATE)=12) AND
((DATEPART(dd,@DATE)-DATEPART(dw,@DATE))>= 28))
SET @ISOweek=1;
RETURN(@ISOweek);
END;
GO
SET DATEFIRST 1;
SELECT dbo.ISOweek(CONVERT(DATETIME,’12/26/2004′,101)) AS ‘ISO Week’;

The following example returns an inline table-valued function.
It returns three columns ProductID, Name and the aggregate of
year-to-date totals by the store as YTD Total for each product sold to the store.

IF OBJECT_ID (N’Sales.ufn_SalesByStore’, N’IF’) IS NOT NULL
DROP FUNCTION Sales.ufn_SalesByStore;
GO
CREATE FUNCTION Sales.ufn_SalesByStore (@storeid int)
RETURNS TABLE
AS
RETURN
(
SELECT P.ProductID, P.Name, SUM(SD.LineTotal) AS ‘Total’
FROM Production.Product AS P
JOIN Sales.SalesOrderDetail AS SD ON SD.ProductID = P.ProductID
JOIN Sales.SalesOrderHeader AS SH ON SH.SalesOrderID = SD.SalesOrderID
JOIN Sales.Customer AS C ON SH.CustomerID = C.CustomerID
WHERE C.StoreID = @storeid
GROUP BY P.ProductID, P.Name
);

That is a quick review of SQL Functions, if you have any questions, just leave a comment below.

ISNULL or COALESCE

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;

GO
USE master;
IF EXISTS (SELECT name FROM sys.tables
      WHERE name = ‘wages’)
   DROP TABLE wages;
GO
CREATE TABLE wages
(
   emp_id      tinyint    identity,
   hourly_wage   decimal   NULL,
   salary      decimal    NULL,
   commission   decimal   NULL,
   num_sales   tinyint   NULL
);
GO
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);
GO
SET NOCOUNT OFF;
GO
SELECT CAST(COALESCE(hourly_wage * 10 * 10, salary, commission * num_sales) AS money) AS ‘Total Salary’
FROM wages;

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

e.g

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!

Recovering from a Suspect Database

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.

 

Useful DBCC Commands : DBCC Command List

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 Commands
1.DBCC CHECKALLOC

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.

2.DBCC CHECKCATALOG

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.

3.DBCC CHECKCONSTRAINTS

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.

4.DBCC CHECKDB

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.

5.DBCC CHECKTABLE

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).

6.DBCC CHECKFILEGROUP

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.

7.DBCC CHECKIDENT

DBCC CHECKIDENT returns the current identity value for the specified table and allows you to correct the identity value if necessary.

8.DBCC DBREINDEX

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.

9.DBCC INDEXDEFRAG

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).

10.DBCC INPUTBUFFER

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. )

11.DBCC OPENTRAN

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.

12.DBCC PROCCACHE

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.

13.DBCC SHOWCONTIG

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.

14.DBCC SHRINKDATABASE

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.

15.DBCC SHRINKFILE

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).

17.DBCC USEROPTIONS

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.

Rebuilding Indexes Using A Script On SQL Server

There comes a time when you have to rebuild all the indexes in a database on SQL Server, and you have to figure out a quick way of doing this. You can set up different jobs and routines to schedule the rebuild, but it is handy to have a script.

You have to rebuild indexes when you are the administrator of a database and realised it is going very slow after you have done some investigation, and you have found out that databases have not been build for a very long time (or never).

If you want a quick way to rebuild your indexes on SQL Server using a script, then use the script below.

This script will find all the indexes within a database and rebuild them. You can change the script if you like to add ONLINE rebuilding if you want your tables to be accessible through the rebuild. I have used this index rebuild many times to speed up the process of fixing performance related problem within a database when you know that there has not been a proper maintenance plan set up for the database.

This script on SQL Server 2012, SQL Server 2016 and SQL Server 2017. I have tried this script on these editions and they work and probably will work on future editions.

I would try this script out in a development environment first just to make sure you are happy with the performance because it will have a performance hit on your server when this is running. It may also take a while to run the script as this is dependant on how big your tables are and how many indexes you have.

SQL Server Rebuilding Indexes Code

DECLARE @Database VARCHAR(255) 
DECLARE @Table VARCHAR(255) 
DECLARE @cmd NVARCHAR(500) 
DECLARE @fillfactor INT

SET @fillfactor = 100

DECLARE DatabaseCursor CURSOR FOR 
SELECT name 
FROM sys.databases

ORDER BY 1

OPEN DatabaseCursor

FETCH NEXT FROM DatabaseCursor INTO @Database 
WHILE @@FETCH_STATUS = 0 
BEGIN

SET @cmd = 'DECLARE TableCursor CURSOR FOR SELECT ''['' + table_catalog + ''].['' + table_schema + ''].['' +
 table_name + '']'' as tableName FROM [' + @Database + '].INFORMATION_SCHEMA.TABLES
 WHERE table_type = ''BASE TABLE'''

-- create table cursor 
 EXEC (@cmd) 
 OPEN TableCursor

FETCH NEXT FROM TableCursor INTO @Table 
 WHILE @@FETCH_STATUS = 0 
 BEGIN

IF (@@MICROSOFTVERSION / POWER(2, 24) >= 9)
 BEGIN
 -- SQL 2005 or higher command
 SET @cmd = 'ALTER INDEX ALL ON ' + @Table + ' REBUILD '
 
 --PRINT @cmd
 EXEC (@cmd)
 END
 ELSE
 BEGIN
 -- SQL 2000 command
 --DBCC DBREINDEX(@Table,' ',@fillfactor) 
 PRINT 1
 END

FETCH NEXT FROM TableCursor INTO @Table 
 END

CLOSE TableCursor 
 DEALLOCATE TableCursor

FETCH NEXT FROM DatabaseCursor INTO @Database 
END 
CLOSE DatabaseCursor 
DEALLOCATE DatabaseCursor

Why not try it and give it a go to see if it works for you?

Please leave a comment below if this script to rebuild your indexes work or what improvements you could do to it.

SQL Server 2017 Developer Edition is FREE

GUESS WHAT!

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

Download SQL Server 2017

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