SQL with Manoj

Watch & Subscribe my SQL videos on YouTube | Join me on Facebook

SQL Basics – AGGREGATE Functions in SQL Server

December 21, 2016 Leave a comment

Aggregate Functions provides us basic calculation over a group or a set of values. Aggregate functions are deterministic functions as they return the same value every time they are called by using a specific set of input values.

The Basic Aggregate Functions used day today in SQL Server are: COUNT, MIN, MAX, SUM, AVG.

You can use below query to create an Employee table for this demo:

USE [AdventureWorks2014]
GO

DROP TABLE IF EXISTS dbo.Employee

SELECT
    P.BusinessEntityID AS EmpID, P.Title, P.FirstName, P.MiddleName, P.LastName, 
    E.Gender, E.MaritalStatus, E.BirthDate AS DOB, E.HireDate AS DOJ, E.JobTitle, 
    D.Name AS DeptName, S.Rate
INTO dbo.Employee -- Create a new table here and load query data
FROM [Person].[Person] P
INNER JOIN [HumanResources].[Employee] E 
		ON E.BusinessEntityID = P.BusinessEntityID
CROSS APPLY (
    SELECT TOP 1 DepartmentID
    FROM [HumanResources].[EmployeeDepartmentHistory] DH 
    WHERE DH.BusinessEntityID = E.BusinessEntityID 
    ORDER BY StartDate DESC) EDH
INNER JOIN [HumanResources].[Department] D 
		ON D.DepartmentID = EDH.DepartmentID
INNER JOIN [HumanResources].[EmployeePayHistory] S 
		ON S.BusinessEntityID = P.BusinessEntityID

SELECT * FROM dbo.Employee -- 316
GO

1. COUNT() function returns the number of rows in a table or a group, and returns an INT (integer).

-- To get count of all reords in a table:
select COUNT(*) from dbo.Employee -- 316

-- Within Group: get count of records having Gender = 'M':
select COUNT(*) from dbo.Employee WHERE Gender = 'M' -- 228

-- Using DISTINCT with COUNT:
select COUNT(FirstName) from dbo.Employee -- 316
select COUNT(DISTINCT FirstName) from dbo.Employee -- 224

select COUNT(LastName) from dbo.Employee -- 316
select COUNT(DISTINCT LastName) from dbo.Employee -- 270

–> Using COUNT() function with GROUP BY

-- Get count of Employees in every Department:
select 
	DeptName, 
	COUNT(*) as EmpCount
from dbo.Employee
GROUP BY DeptName

2. MIN(), MAX() and AVG() functions returns the Minimum, Maximum and Average values from a table or a group.

-- Check the Minimum, Maximum and Average salaries of all Employes:
SELECT 
	MIN(Salary) as minSal, 
	MAX(Salary) as maxSal, 
	AVG(Salary) as avgSal
FROM dbo.Employee

–> Using MIN(), MAX(), AVG() functions with GROUP BY

-- Check Department wise Minimum, Maximum and Average salaries:
SELECT 
	DepartmentName, 
	MIN(Salary) as minSal, 
	MAX(Salary) as maxSal, 
	AVG(Salary) as avgSal
FROM dbo.Employee
GROUP BY DepartmentName

3. SUM() function returns the Sum of all values from a table or a group.

-- Check the budget of a company to pay their Employee's salary:
select SUM(Salary) as TotalSalary
from dbo.Employee

–> Using SUM() function with GROUP BY

-- Check Department wise budget of salary:
select 
	DepartmentName, 
	SUM(Salary) as TotalSalary
from dbo.Employee
GROUP BY DepartmentName

4. Using all Aggregate function together to get all information in a single query:

SELECT 
	COUNT(*)	as EmpCount, 
	MIN(Salary) as minSal, 
	MAX(Salary) as maxSal, 
	AVG(Salary) as avgSal, 
	SUM(Salary) as TotalSalary
FROM dbo.Employee

-- GROUP BY example with Aggregate functions:</strong>
SELECT 
	DepartmentName, 
	COUNT(*)	as EmpCount, 
	MIN(Salary) as minSal, 
	MAX(Salary) as maxSal, 
	AVG(Salary) as avgSal, 
	SUM(Salary) as TotalSalary
FROM dbo.Employee
GROUP BY DepartmentName

–> More Aggregate Functions in SQL Server are: COUNT_BIG, CHECKSUM_AGG, STDEV, STDEVP, GROUPING, GROUPING_ID, VAR, VARP. You can check them here: https://docs.microsoft.com/en-us/sql/t-sql/functions/aggregate-functions-transact-sql

Categories: SQL Basics Tags: Aggregate Functions

SQL Error – SQL Server service failed to Start | Windows could not start the SQL Server

December 15, 2016 4 comments

Today I was trying to connect to a SQL Server instance of my DEV machine from SSMS, it took bit more time than usual and threw me an error:

Cannot connect to [SQL_Instance_name], A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: Named Pipes Provider, error: 40 – Could not open a connection to SQL Server) (Microsoft SQL Server, Error: 2)

I immediately checked SQL Services, and yes the SQL Server service was not running. But when I tried to run the service it didn’t turn up and it gave me an error:

Windows could not start the SQL Server (MSSQLSERVER) on Local Computer. For more information, review the System Event Log. If this is a non-Microsoft service, contact the service vendor, and refer to service-specific error code 14.

Now, the only thing to know what would have happened is the Event viewer.

–> You can open “Event Viewer” by any of the below options:

1. Shortcut: Eventvwr.msc

2. Type “Event Viewer” in search box on Windows 8 and above.

3. Open Control Panel –> System and Maintenance –> Administrative Tools –> Event Viewer

Now on the “Event Viewer” window go to: Windows Logs –> Application

Check the logs on the General or Details tab:

I checked all errors and it threw following errors in sequence:

Error: 49910, Severity: 10, State: 1. (Params:). The error is printed in terse mode because there was error during formatting. Tracing, ETW, notifications etc are skipped.

Error: 17312, Severity: 16, State: 1. (Params:). The error is printed in terse mode because there was error during formatting. Tracing, ETW, notifications etc are skipped.

Error: 17300, Severity: 16, State: 1. (Params:). The error is printed in terse mode because there was error during formatting. Tracing, ETW, notifications etc are skipped.

Error: 33086, Severity: 10, State: 1. (Params:). The error is printed in terse mode because there was error during formatting. Tracing, ETW, notifications etc are skipped.

Error: 701, Severity: 17, State: 65. There is insufficient system memory in resource pool ‘internal’ to run this query.

–> So, if you query these errors in sys.messages then you will see that one of the error (id = 17300) is related to insufficient memory:

select * 
from sys.messages 
where language_id = 1033 
and message_id IN (49910,17312, 17300, 33086)

SQL Server was unable to run a new system task, either because there is insufficient memory or the number of configured sessions exceeds the maximum allowed in the server. Verify that the server has adequate memory. Use sp_configure with option ‘user connections’ to check the maximum number of user connections allowed. Use sys.dm_exec_sessions to check the current number of sessions, including user processes.

–> Let’s also check SQL Server ERROR LOGS, go to Error Log location on you SQL Server machine: C:\Program Files\Microsoft SQL Server \MSSQL11.MSSQLSERVER \MSSQL\Log

I’ve removed unnecessary part here as the error log was too long, highlighted the real cause of the issue, and that is: Failed allocate pages: FAIL_PAGE_ALLOCATION 1.

…
2016-12-15 09:34:14.82 Server SQL Server detected 2 sockets with 10 cores per socket and 10 logical processors per socket, 20 total logical processors; using 20 logical processors based on SQL Server licensing. This is an informational message; no user action is required.
2016-12-15 09:34:14.82 Server SQL Server is starting at normal priority base (=7). This is an informational message only. No user action is required.
2016-12-15 09:34:14.82 Server Detected 143359 MB of RAM. This is an informational message; no user action is required.
…
2016-12-15 09:34:15.27 Server Using dynamic lock allocation. Initial allocation of 2500 Lock blocks and 5000 Lock Owner blocks per node. This is an informational message only. No user action is required.
2016-12-15 09:34:15.27 Server Lock partitioning is enabled. This is an informational message only. No user action is required.
2016-12-15 09:34:20.30 Server Failed allocate pages: FAIL_PAGE_ALLOCATION 1
2016-12-15 09:31:14.07 Server
Process/System Counts Value
—————————————- ———-
Available Physical Memory 145982603264
Available Virtual Memory 140678812086272
Available Paging File 167077097472
Working Set 337784832
Percent of Committed Memory in WS 100
Page Faults 94990
System physical memory high 1
System physical memory low 0
Process physical memory low 1
Process virtual memory low 0
2016-12-15 09:31:14.07 Server Error: 49910, Severity: 10, State: 1. (Params:). The error is printed in terse mode because there was error during formatting. Tracing, ETW, notifications etc are skipped.
…
2016-12-15 09:31:14.08 Server
MEMORYCLERK_SQLGENERAL (node 0) KB
—————————————- ———-
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 1240
2016-12-15 09:31:14.08 Server Error: 17312, Severity: 16, State: 1. (Params:). The error is printed in terse mode because there was error during formatting. Tracing, ETW, notifications etc are skipped.
2016-12-15 09:31:14.08 Server SQL Server shutdown has been initiated
…
2016-12-15 09:31:14.08 Server
MEMORYCLERK_SQLCONNECTIONPOOL (node 0) KB
—————————————- ———-
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 48
2016-12-15 09:31:14.08 Server Error: 17300, Severity: 16, State: 1. (Params:). The error is printed in terse mode because there was error during formatting. Tracing, ETW, notifications etc are skipped.
…
2016-12-15 09:31:14.08 Server
MEMORYCLERK_SNI (node 0) KB
—————————————- ———-
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 16
2016-12-15 09:31:14.08 Server Error: 17300, Severity: 16, State: 1. (Params:). The error is printed in terse mode because there was error during formatting. Tracing, ETW, notifications etc are skipped.
…
MEMORYCLERK_SQLXP (node 0) KB
—————————————- ———-
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 16
2016-12-15 09:31:14.08 Server Error: 17300, Severity: 16, State: 1. (Params:). The error is printed in terse mode because there was error during formatting. Tracing, ETW, notifications etc are skipped.
…
2016-12-15 09:31:14.08 Server
MEMORYCLERK_SOSNODE (Total) KB
—————————————- ———-
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 26888
2016-12-15 09:31:14.08 Server Error: 17300, Severity: 16, State: 1. (Params:). The error is printed in terse mode because there was error during formatting. Tracing, ETW, notifications etc are skipped.
2016-12-15 09:31:14.08 Server
MEMORYCLERK_SOSOS (node 0) KB
—————————————- ———-
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 192
2016-12-15 09:31:14.08 Server Error: 17312, Severity: 16, State: 1. (Params:). The error is printed in terse mode because there was error during formatting. Tracing, ETW, notifications etc are skipped.
MEMORYCLERK_SOSMEMMANAGER (node 0) KB
—————————————- ———-
VM Reserved 480
VM Committed 336
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 0
2016-12-15 09:31:14.08 Server SQL Server shutdown has been initiated
…
2016-12-15 09:31:14.08 Server
MEMORYCLERK_XE (node 0) KB
—————————————- ———-
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 376
2016-12-15 09:31:14.08 Server Error: 33086, Severity: 10, State: 1. (Params:). The error is printed in terse mode because there was error during formatting. Tracing, ETW, notifications etc are skipped.
2016-12-15 09:31:14.08 Server
MEMORYCLERK_SQLLOGPOOL (node 0) KB
—————————————- ———-
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 152
2016-12-15 09:31:14.08 Server Error: 17312, Severity: 16, State: 1. (Params:). The error is printed in terse mode because there was error during formatting. Tracing, ETW, notifications etc are skipped.

2016-12-15 09:31:14.08 Server SQL Server shutdown has been initiated
2016-12-15 09:31:14.08 spid14s Error: 701, Severity: 17, State: 107.
2016-12-15 09:31:14.08 spid14s There is insufficient system memory in resource pool ‘internal’ to run this query.

In the Error Log above you can also see the Error IDs we saw in Event Log, I’ve highlighted them with the Memory Clerks.

–> Now let’s wee if we can start SQL Server with minimal configuration by applying the Startup Parameter “-f”:

Open SQL Server Configuration Manager (SSCM) –> select “SQL Server Services” –> right click on SQL Server service, and select Properties.

Add “-f” as Startup Parameter, as shown below.

Again go back to the SSCM and Start the SQL Server service, this time it will start as you have set SQL Server to run with minimum configuration, and thus it will run on limited memory.

–> Now I opened the SSMS and connected to the respective instance, under Object Explorer right click on Instance name, and selected Properties. Moved to the Memory page and checked the Maximum server memory (in MB) setting. It was just 128 MB, so I increased it to 110 GB as my server RAM was 140 GB.

–> Again went to the SSCM and removed the Startup Parameter “-f”, and restarted SQL Server services.

Now I was able to login to SQL Server instance without any issues !!!

Categories: DBA Stuff, SQL Errors Tags: Event Viewer, FAIL_PAGE_ALLOCATION, SQL Server ERROR LOG, SQL Server service, SQL Service

Microsoft released SQL Server vNext (2017) – New features and enhancements in CTP 1.x

November 20, 2016 5 comments

On 16th Nov 2016 Microsoft announced the first Community Test Preview (CTP 1.0) of SQL Server vNext that will run both on Windows & Linux. Not only Linux, but it will be supported on Docker, and macOS (via Docker) too.

For now I’m calling the vNext version as SQL Server 2017. (2018 is not called officially, I’m calling it just to avoid any confusion 🙂 )

This announcement was along with the release of SQL Server 2016 SP1, and the vNext version also supports features added in SQL Server 2016 SP1.

–> Download vNext bits:

To download the SQL Server vNext you can Register and Download the Full version or Free evaluation version (180 days).

Or, directly download the ISO (~2 GB): SQLServerVnextCTP1-x64-ENU.iso

–> Check version and SQL build:

select @@version

Microsoft SQL Server vNext (CTP1) – 14.0.1.246 (X64)
Nov 1 2016 23:24:39
Copyright (c) Microsoft Corporation
Developer Edition (64-bit) on Windows 10 Enterprise 6.3 (Build 14393: ) (Hypervisor)

–> New Features & Enhancements:

>> Database Engine:

1. All new features in SQL Server 2016 SP1.

2. New compatibility level 140.

3. Improvements to the way incremental statistics update thresholds are computed.

4. New DMVs added:
– sys.dm_exec_query_statistics_xml added, to get query execution plan for in-flight requests.
– sys.dm_os_host_info, to provide operating system information for both Windows and Linux.

5. Enhancements to In-Memory Tables & SPs:
– Can now use sp_spaceused, sp_rename, CASE, TOP (N) WITH TIES
– More than 8 indexes

6. Clustered Columnstore Indexes now support LOB columns (nvarchar(max), varchar(max), varbinary(max)).

7. New STRING_AGG() aggregate function has been added.

8. Database roles are created with R Services for managing permissions associated with packages.

9. New Japanese collations are added.

– Features added in CTP 1.1

10. Memory-optimized tables and indexes now support computed columns
   – Full support for JSON functions
   – and CHECK constraints
   – Natively compiled modules now support the CROSS APPLY operator

12. New string functions CONCAT_WS, TRANSLATE, and TRIM are added.
– and the WITHIN GROUP clause is now supported for the STRING_AGG function.

– Features added in CTP 1.2

13. Online index build and rebuild support for non-clustered Columnstore indexes.

14. Support for SUSE Linux Enterprise Server v12 SP2

15. SQL Server Early Adoption Program (EAP), msdn blog on EAP

– Features added in CTP 1.3

16. Full text search, now available for all supported Linux distros

17. Resumable online index rebuilds

18. Temporal Tables Retention Policy

19. Indirect checkpoint performance improvements

20. Online non-clustered columnstore index buill and rebuild support added

21. Availability Groups enhancements:
   – Cluster-less Availability Groups support added
   – Minimum Replica Commit Availability Groups setting
   – Availability Groups can now work across Windows-Linux to enable cross-OS migrations and testing

22. New DMV sys.dm_db_stats_histogram, for examining statistics

23. 5 new dynamic management views to return information about Linux process:
   – sys.dm_linux_proc_all_stat
   – sys.dm_linux_proc_cpuinfo
   – sys.dm_linux_proc_meminfo
   – sys.dm_linux_proc_sql_maps
   – sys.dm_linux_proc_sql_threads

>> SQL Server Analysis Services (SSAS):

1. 1400 Compatibility level for tabular models.

2. Modern Get Data query and import data features for tabular models.

3. Support for ragged hierarchies with the new Hide Members property.

4. Support for drillthrough by using Detail Rows. This includes the Default Detail Rows expression property and DETAILROWS DAX function.

5. Table-level security, restricts user permissions on table data and table names.

6. New DAX IN function.

7. Encoding hints, an advanced feature used to optimize processing (data refresh) of large in-memory Tabular models.

>> SQL Server Integration Services (SSIS):

– Support Scale Out of SSIS: to run SSIS on multiple machines. With Scale Out Master and Workers, packages can be distributed to execute on different Workers automatically. Check more details here in MSDN.

>> SQL Server R support: Check full details here in MSDN.

–> For new Features released in CTP 2.0 check here.

–> Download & Install SQL Server vNext & SSMS:

–> References:

>> MSDN Blog announcement

>> MSDN for SQL Server vNext

>> SQL Server vNext Release Notes

Categories: SQL Server 2017 Tags: Download SQL Server, Install SQL Server, Install SSMS, SQL Server 2017

Now “CREATE OR ALTER” Stored Procedure, Function, View, Trigger with SQL Server 2016

November 19, 2016 2 comments

SQL Server 2016 release was packed with lot of new features, and I tried to cover most of them, [check here]. This includes some of the major new features like, Polybase, Temporal Tables, JSON support, Stretch DB, Row Level Security, Dynamic data Masking, etc. are very unique to the other Database systems in competition.

But Microsoft’s SQL Server team also keeps on adding few features in every release which were already there in other Database systems, so that developers could use those and make their life easier, like the new IF EXISTS option with DROP & ALTER statements I already discussed in my [previous post].

Now, with the recent Service Pack 1, one more feature has been added, which developers (mainly from the Oracle background) were missing from long time, and that is CREATE OR ALTER option while creating programming modules, like:

1. Stored Procedures (SP)

2. Functions (UDFs)

3. Views

4. Triggers

–> Now you can create a new Stored Procedure without checking its existence, simply by using the new CREATE OR ALTER option, like below:

CREATE OR ALTER PROCEDURE dbo.spgetEmployeeDetails
	@EmpID INT
AS
BEGIN
	SELECT BusinessEntityID, Title, FirstName, MiddleName, LastName 
	FROM Person.Person
	WHERE BusinessEntityID = @EmpID
END
GO

… you can execute the above code multiple times and it won’t fail. First time this CREATEs the SP, next time it will ALTER it.

–> Previously you need to add an IF EXISTS() condition to check if the SP already exists or not. If exists then drop and then create a new SP, like:

IF EXISTS(SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_NAME = 'spgetEmployeeDetails')
	DROP PROCEDURE dbo.spgetEmployeeDetails
GO

CREATE PROCEDURE dbo.spgetEmployeeDetails
	@EmpID INT
AS
BEGIN
	SELECT BusinessEntityID, Title, FirstName, MiddleName, LastName 
	FROM Person.Person
	WHERE BusinessEntityID = @EmpID
END
GO

Categories: SQL Server 2016 Tags: CREATE OR ALTER, SQL Server 2016

SQL Server 2016 1st Service Pack (SP1) is out (download, new features & enhancements)

November 17, 2016 4 comments

Almost 6 months back i.e. on 1st June 2016 Microsoft released SQL Server 2016 RTM, i.e. full and final version, which you can [check and download here].

Yesterday (i.e. 16-Nov-2016) Microsoft released the 1st Service Pack (SP1) of SQL Server 2016.

–> Download:

To download the SQL Server 2016 SP1 you can Register and Download the Full version or Free evaluation version (180 days).

… or you can Download the new Setup utility here, which provides you option to do a Basic or Custom installation, or download the ISO or CAB file (~2.5 GB).

… or you can also just download the Service Pack (SP1) (~550 MB), instead of the whole Setup (~2.5 GB).

–> What’s new SP1:

1. Features which were only available in Enterprise edition are now enabled in Standard, Web, Express, and LocalDB editions, link.

2. List of Bugs and issues fixed, link.

3. CREATE OR ALTER syntax for Stored Procedures, Views, Functions, and Triggers.

4. DBCC CLONEDATABASE (source_database_name, target_database_name), with optional WITH NO_STATISTICS, NO_QUERYSTORE. Creates a duplicate database by cloning Schema, metadata and statistics, without the data.

5. OPTION (USE HINT(‘hint1’, ‘hint2’)), support for a more generic query hinting is added, link.

6. Post this Service Pack (SP1) Parallel INSERTs in INSERT..SELECT to local temporary tables is disabled by default and will require TABLOCK hint for parallel insert to be enabled.

7. New DMVs are added, and some enhanced:
   – sys.dm_exec_valid_use_hints to list hints
   – sys.dm_exec_query_statistics_xml to return showplan XML transient statistics
   – sys.dm_db_incremental_stats_properties to check incremental statistics for the specified table
   – New column instant_file_initialization_enabled is added to sys.dm_server_services, to allow DBAs to programmatically identify if Instant File initialization (IFI) is in effect at the SQL Server service startup.
   – New column estimated_read_row_count is added to sys.dm_exec_query_profiles
   – New columns sql_memory_model and sql_memory_model_desc are added to sys.dm_os_sys_info, to provide information about the locking model for memory pages, and to allow DBAs to programmatically identify if Lock Pages in Memory (LPIM) privilege is in effect at the service startup time.