SQL Server Transact SQL - TSQL - TOP WITH TIES

TOP Keyword is commonly used to get the Top Rows from a Result. SQL Server has option use it 'WITH TIES' to get similar rows together

WITH TIES
Used when you want to return two or more rows that tie for last place in the limited results set. Must be used with the ORDER BY clause. WITH TIES may cause more rows to be returned than the value specified in expression. For example, if expression is set to 5 but 2 additional rows match the values of the ORDER BY columns in row 5, the result set will contain 7 rows.

SELECT TOP (5) productid, unitprice FROM Production.Products
WHERE categoryid = 1 ORDER BY unitprice DESC;

You get the following result set.


productid                unitprice
----------- ---------------------
38                           263.50
43                             46.00
2                               19.00
1                               18.00
35                             18.00

If you want provide solutions to turn this query into a deterministic one—one solution that includes ties and another that breaks the ties. First, address the version that includes all ties by using the WITH TIES option. Add this option to the query, as follows.

SELECT TOP (5) WITH TIES productid, unitprice FROM Production.Products
WHERE categoryid = 1 ORDER BY unitprice DESC;

You get the following output, which includes ties.
productid                unitprice
----------- ---------------------
38                           263.50
43                             46.00
2                               19.00
1                               18.00
35                             18.00
76                             18.00

AlWaysOn Availability Group Enhancements in SQL Server 2016

• AlWaysOn Feature is there available since SQL Server 2012 and There are some enhancements in SQL Server 2014 is the increased maximum number of secondaries. SQL Server 2012 supported a maximum of four secondary replicas. AlwaysOn Availability Groups on 2014 supports up to eight secondary replicas. Also use SQL Server 2014 AlwaysOn Availability Groups to provide high availability for SQL Server databases hosted in Windows Azure etc .

• Database Level Fail over Trigger SQL Server 2016 is coming with solving pains on Failover , Currently Failover is happening based on instance health, on 2016 it can fail-over based on Database health as well. If any DB in an availability group fails the whole Group of Database will fail over to other. 

• More Auto fail-over Targets : Another Enhancement is increase in number of Automatic Failover partners , Which means you can have two more fail-over partners apart from Primary. In this 3 node Scenario , one node fails still we can have high availability with other two nodes.

• Domain independent Availability Group You can create an availability group in Work Group Computers .Earlier All the nodes which is participating in a availability group must be in same domain

• Load balancing on Availability group secondaries , Earlier Only first replica in secondaries get all the read only traffic even if you have more than one replicas. On 2016 you can Distribute the Workload for read only transaction across multiple secondaries 

• Distributed transactions are supported with AlwaysOn Availability Groups. 

This applies to distributed transactions between databases hosted by two different SQL Server instances. It also applies to distributed transactions between SQL Server and another DTC-compliant server.

The following requirements must be met:

Availability Groups must be running on Windows Server 2016 or Windows Server 2012 R2. For Windows Server 2012 R2, you must install the update in KB3090973 available at https://support.microsoft.com/en-us/kb/3090973.

Availability Groups must be created with the CREATE AVAILABILITY GROUP command and the WITH DTC_SUPPORT = PER_DB clause. You cannot currently alter an existing Availability Group.

• Basic Availability Group Feature will be supported by Standard Edition of SQL Server ( 2 Replicas Only and One Database per Group) , Basic Feature wont have readable secondary , no Backup on secondary replica etc , for those you have to choose advanced Availability group feature on Enterprise Edition. Whole idea behind basic Availability group is to retire Mirroring in Future
• Mirroring wont be deprecating on 2016 May be in future versions

The database transaction log file increasing for the DBs Participating In AlwaysOn Availability Groups

Issue

After Migrating High Available SQL Server 2012 Cluster Instance to SQL Server 2014  AlWays on Availability Group  transaction Log file keep growing and within couple of weeks almost the hard disk is full. Earlier My Db was in Simple recovery mode , I changed it to Full recovery mode to add it on Availability Group because of that the transaction log keeps increasing

Solution

It is  a very common problem the setting database recovery mode to FULL and then forgetting to backup the transaction log (LDF file). Let me explain how to fix it.

If you ready loose  a some data between backups, just set the database recovery mode to SIMPLE, then forget about LDF - it will be small. the solution for most of the cases. But for SIMPLE Recovery mode will not supporting by Availability group. In this case you have to take Transaction Log backups.
Recommended to have  a full backup per day. If you do a full backup per day, The frequency of the log backup will determine how much data you are allowed to lose in case of a failure. If you run your log backup every 15 minutes, expect to loose up to the last 15 minutes of data that changed. 15 minutes was good frequency for my environment.


AlwaysOn Availability Groups allows the offloading backups to a secondary replica. If you setup a log backup on secondary replica it will be truncating both primary and secondary logs. no need to have it on both nodes.

Where should backups occur? select the automated backup preference for the availability group, one of:

Prefer Secondary 

Specifies that backups should occur on a secondary replica except when the primary replica is the only replica online. In that case, the backup should occur on the primary replica. This is the default option.

Secondary only

Specifies that backups should never be performed on the primary replica. If the primary replica is the only replica online, the backup should not occur.

Primary

Specifies that the backups should always occur on the primary replica. This option is useful if you need backup features, such as creating differential backups, that are not supported when backup is run on a secondary replica.

Any Replica

Specifies that you prefer that backup jobs ignore the role of the availability replicas when choosing the replica to perform backups. Note backup jobs might evaluate other factors such as backup priority of each availability replica in combination with its operational state and connected state.

To take the automated backup preference into account for a given availability group, on each server instance that hosts an availability replica whose backup priority is greater than zero (>0), you need to script backup jobs for the databases in the availability group. To determine whether the current replica is the preferred backup replica, use the sys.fn_hadr_backup_is_preferred_replica function in your backup script. If the availability replica that is hosted by the current server instance is the preferred replica for backups, this function returns 1. If not, the function returns 0. By running a simple script on each availability replica that queries this function, you can determine which replica should run a given backup job.

If you use the Maintenance Plan Wizard to create a given backup job, the job will automatically include the scripting logic that calls and checks the sys.fn_hadr_backup_is_preferred_replica function. However, the backup job will not return the “This is not the preferred replica…” message. Be sure to create the job(s) for each availability database on every server instance that hosts an availability replica for the availability group.
:

Common table expressions CTE - SQL Server

Common table expression is a similar concept of a derived table in the sense that it’s a named table expression that is visible only to the statement .

A derived table is basically a subquery, except it is always in the FROM clause of a SQL statement. The reason it is called a derived table is because it essentially functions as a table as far as the entire query is concerned.

A common table expression (CTE) can be thought of as a temporary result set that is defined within the execution scope of a single SELECT, INSERT, UPDATE, DELETE, or CREATE VIEW statement. A CTE is similar to a derived table in that it is not stored as an object and lasts only for the duration of the query. Unlike a derived table, a CTE can be self-referencing and can be referenced multiple times in the same query.
A CTE can be used to:
Create a recursive query. For more information, see Recursive Queries Using Common Table Expressions.
Substitute for a view when the general use of a view is not required; that is, you do not have to store the definition in metadata.
Enable grouping by a column that is derived from a scalar subselect, or a function that is either not deterministic or has external access.
Reference the resulting table multiple times in the same statement.




Common Table Expression (CTE)  contains  three parts:

a) The name Starts  WITH keyword
b) List of Columns (optional)
c) Query (in parentheses after the AS keyword)

The query using the CTE must be the first query appearing after the CTE.

You have a query like this

SELECT * FROM (
SELECT A.productid, A.productname, E.categoryname,a.unitprice From Production.Products A Inner join Production.Categories E on E.categoryid = A.categoryid) T
WHERE T.unitprice > 50
ORDER BY T.categoryname

rewriting the query using CTE expression as follows

With T(productid, productname,categoryname, unitprice)
AS
(
SELECT P.productid, P.productname, C.categoryname,P.unitprice From Production.Products P
Inner join Production.Categories C on C.categoryid = P.categoryid
)
SELECT * FROM T
WHERE T.unitprice > 50
ORDER BY T.categoryname

PIVOT and UNPIVOT - SQL Server

Pivoting is a grouping and aggregating of data. Unpivoting is, in a sense, the inverse of pivoting.PIVOT rotates data from a state of rows to a state of columns; UNPIVOT rotates the data from columns to rows.

WITH PivotData AS
(
SELECT
< grouping column >,
< spreading column >,
< aggregation column >
FROM < source table >
)
SELECT < select list >
FROM PivotData
PIVOT( < aggregate function >(< aggregation column >)
FOR < spreading column > IN (< distinct spreading values >) ) AS P;

To understand PIVOT, let us create a Temporary Table #Books with sample records in below script:

if object_id('tempdb..#Books') is not null
drop table #Books
GO

CREATE TABLE #Books
(Book VARCHAR(150),Year INT,Price MONEY)
GO
INSERT INTO #Books VALUES('SQL 2012',2012,1300)
INSERT INTO #Books VALUES('Oracle',2012,1200)
INSERT INTO #Books VALUES('Azure',2012,1500)
INSERT INTO #Books VALUES('Azure',2014,1800)
INSERT INTO #Books VALUES('Oracle',2014,1300)

SELECT * FROM #Books PIVOT(SUM(Price ) FOR Book IN ([Azure], Oracle)) AS PVTTable

SELECT * INTO #BookPriceinResult FROM #Books PIVOT(SUM(price)
FOR book IN ([Azure], [Oracle])) AS PVTTable;

select * from #BookPriceinResult;

SELECT Book, Year, Price FROM #BookPriceinResult
UNPIVOT(Price FOR Book IN ([Azure], Oracle)) AS UNPVTTable;

The permissions granted to user 'domain\username' are insufficient for performing this operation. (rsAccessDenied) SQL Server Report Server (SSRS)

Reporting Services uses role-based security to grant user access to a report server. On a new report server installation, only users who are members of the local Administrators group have permissions to report server content and operations. To make the report server available to other users, you must create role assignments that map  user or group accounts to a predefined role that specifies a collection of tasks.

Verify that sufficient permissions have been granted and windows user account control (uac) restrictions have been addressed

It is an insufficient  privilege for current user you are running the report, give that user a privilege to run report / report folders

Start Internet Explorer As "Run as Administrator"
Open Your Report Server URL (http://'ServerName'/'ReportFolder')
Go to Site Settings

Security->New Role Assignment

Add DOMAIN/USERNAME or DOMAIN/USERGROUP

Step 2

Go to Folder Settings

Security->New Role Assignment

Add DOMAIN/USERNAME or DOMAIN/USERGROUP
Check the Report URL

Comparison of AlWays On Failover Cluster Instances (AOFCI) and AlWays on Availability Groups(AOAG)

Comparison of Failover Cluster Instances and Availability Groups

Both are called AlWaysOn Features only ( FCI & AG),AlwaysOn is not a single, specific feature. Rather, it is a set of availability features whose most salient components are the Failover Cluster Instances feature and the Availability Groups feature. 

 Regardless of the number of nodes in the FCI, an entire FCI hosts a single replica within an availability group. The following table describes the distinctions in concepts between nodes in an FCI and replicas within an availability group.

	Nodes within an FCI	Replicas within an availability group
Uses WSFC cluster	Yes		Yes
Protection level	Instance		Database
Storage type	Shared		Non-shared1
Storage solutions	Direct attached, SAN, mount points, SMB		Depends on node type
Readable secondaries	No2		Yes
Applicable failover policy settings	WSFC quorum FCI-specific Availability group settings3		WSFC quorum Availability group settings
Failed-over resources	Server, instance, and database		Database only

1While the replicas in an availability group do not share storage, a replica that is hosted by an FCI uses a shared storage solution as required by that FCI. The storage solution is shared only by nodes within the FCI and not between replicas of the availability group.

2Whereas synchronous secondary replicas in an availability group are always running on their respective SQL Server instances, secondary nodes in an FCI actually have not started their respective SQL Server instances and are therefore not readable. In an FCI, a secondary node starts its SQL Server instance only when the resource group ownership is transferred to it during an FCI failover. However, on the active FCI node, when an FCI-hosted database belongs to an availability group, if the local availability replica is running as a readable secondary replica, the database is readable.

3Failover policy settings for the availability group apply to all replicas, whether it is hosted in a standalone instance or an FCI instance.

https://msdn.microsoft.com/en-us/library/ff929171.aspx?f=255&MSPPError=-2147217396