Recursive Common Table Expresson and Hierarchy Example

USE [TEST]
GO

IF  EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_ManagerEmployee_EmployeeID]') AND parent_object_id = OBJECT_ID(N'[dbo].[Employee]'))
ALTER TABLE [dbo].[Employee] DROP CONSTRAINT [FK_ManagerEmployee_EmployeeID]
GO

USE [TEST]
GO


IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Employee]') AND type in (N'U'))
DROP TABLE [dbo].[Employee]
GO

USE [TEST]
GO


SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[Employee](
    [EmployeeID] [int] NOT NULL,
    [ManagerEmployeeID] [int] NULL,
    [EmpoyeeName] [varchar](50) NOT NULL,
    [Percentage] decimal(6,4) NOT NULL
 CONSTRAINT [PK_Employee_EmployeeID] PRIMARY KEY CLUSTERED
(
    [EmployeeID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

ALTER TABLE [dbo].[Employee]  WITH CHECK ADD  CONSTRAINT [FK_ManagerEmployee_EmployeeID] FOREIGN KEY([ManagerEmployeeID])
REFERENCES [dbo].[Employee] ([EmployeeID])
GO

ALTER TABLE [dbo].[Employee] CHECK CONSTRAINT [FK_ManagerEmployee_EmployeeID]
GO

set nocount on

INSERT [dbo].[Employee] ([EmployeeID], [ManagerEmployeeID], [EmpoyeeName], [Percentage]) VALUES (1, NULL, N'CEO/Owner', 1.00)
INSERT [dbo].[Employee] ([EmployeeID], [ManagerEmployeeID], [EmpoyeeName], [Percentage]) VALUES (2, 1, N'Director 001', 0.3)
INSERT [dbo].[Employee] ([EmployeeID], [ManagerEmployeeID], [EmpoyeeName], [Percentage]) VALUES (3, 1, N'Director 002', 0.7)
INSERT [dbo].[Employee] ([EmployeeID], [ManagerEmployeeID], [EmpoyeeName], [Percentage]) VALUES (4, 2, N'Manager 001: Report to Director 001', 0.4)
INSERT [dbo].[Employee] ([EmployeeID], [ManagerEmployeeID], [EmpoyeeName], [Percentage]) VALUES (5, 2, N'Manager 002: Report to Director 001', 0.6)
INSERT [dbo].[Employee] ([EmployeeID], [ManagerEmployeeID], [EmpoyeeName], [Percentage]) VALUES (6, 3, N'Manager 003: Report to Director 002', 0.1)
INSERT [dbo].[Employee] ([EmployeeID], [ManagerEmployeeID], [EmpoyeeName], [Percentage]) VALUES (7, 3, N'Manager 004: Report to Director 002', 0.9)
INSERT [dbo].[Employee] ([EmployeeID], [ManagerEmployeeID], [EmpoyeeName], [Percentage]) VALUES (8, 4, N'Employee 001: Report to Manager 001',1)
INSERT [dbo].[Employee] ([EmployeeID], [ManagerEmployeeID], [EmpoyeeName], [Percentage]) VALUES (9, 5, N'Employee 002: Report to Manager 002',1)
INSERT [dbo].[Employee] ([EmployeeID], [ManagerEmployeeID], [EmpoyeeName], [Percentage]) VALUES (10, 6, N'Employee 003: Report to Manager 003',1)
INSERT [dbo].[Employee] ([EmployeeID], [ManagerEmployeeID], [EmpoyeeName], [Percentage]) VALUES (11, 7, N'Employee 004: Report to Manager 004',0.75)
INSERT [dbo].[Employee] ([EmployeeID], [ManagerEmployeeID], [EmpoyeeName], [Percentage]) VALUES (12, 7, N'Employee 005: Report to Manager 004',0.25)
GO


SELECT [EmployeeID]
      ,[ManagerEmployeeID]
      ,[EmpoyeeName]
      ,[Percentage]
  FROM [dbo].[Employee]
  ORDER BY 2,1

GO

with EmployeeHierarchy
    ([EmployeeID]
    ,[EmplyeeRank]
    ,[ManagerEmployeeID]
    ,[EmpoyeeName]
    ,[Percentage]
    )
as
(
SELECT [EmployeeID]
      ,1 As [EmplyeeRank]
      ,[ManagerEmployeeID]
      ,[EmpoyeeName]
      ,ISNULL([Percentage], 1)
  FROM [TEST].[dbo].[Employee]
  WHERE [ManagerEmployeeID] IS NULL
UNION ALL
SELECT E.[EmployeeID]
      ,1+EH.EmplyeeRank As [EmplyeeRank]
      ,E.[ManagerEmployeeID]
      ,E.[EmpoyeeName]
      ,CAST(ISNULL(E.[Percentage], 1)*EH.[Percentage] AS DECIMAL(6,4))
  FROM    EmployeeHierarchy EH INNER JOIN [TEST].[dbo].[Employee] E
        ON EH.EmployeeID=E.ManagerEmployeeID
)


SELECT
     [EmployeeID]
      ,[EmplyeeRank]
      ,[ManagerEmployeeID]
      ,[Percentage]
      ,[EmpoyeeName]
      FROM EmployeeHierarchy
      --WHERE [EmplyeeRank]=4
      order by 2,3,1;
GO

Enable SQL Server 2008 Dedicated Administrator Connection

Problem

One of the junior SQL Server Database Administrators in my company approached me yesterday with a dilemma. He want to know how to enabled the Dedicated Administrator Connection in SQL Server 2008. At first I started to tell him, but figured it would be smarter to document the options and share the information.  This tips shows you how you can enable this feature in SQL 2008.

Solution

In SQL Server 2005, Microsoft introduced a new feature called Dedicated Administrator Connection (DAC). Using this feature a SQL Server Database Administrator can connect to a SQL Server Instance when the database engine is not responding to regular connections. During such a scenario a DBA can connect to the SQL Server Instance to troubleshoot and to kill any of the SQL Server Processes which are causing the issues.
The DAC allows database administrators to connect to a SQL Server Instance and to execute T-SQL commands to troubleshoot and fix issues rather than rebooting the SQL Server which could lead to database corruption or other problems. By default, the remote Dedicated Administrator Connection feature is disabled in SQL Server 2005 and later versions. It’s a good practice to enable the DAC feature once the SQL Server 2005 or SQL Server 2008 is installed on every instance as this will help you troubleshoot issues when regular connections are not responding. However, only one dedicated administrator connection is allowed at a time on SQL Server 2005 and later versions.

---

Enable Dedicated Administrator Connection in SQL Server 2008 Using TSQL

Execute the below T-SQL to enable remote clients to utilize the Dedicated Administrator Connection.

Use master GO /* 0 = Allow Local Connection, 1 = Allow Remote Connections*/ sp_configure 'remote admin connections', 1 GO RECONFIGURE GO

Enable Dedicated Administrator Connection in SQL Server 2008 Using SQL Server 2008 Management Studio
Database Administrators can also enable Dedicated Administrator Connection Feature using SQL Server 2008 Management Studio. This can be done by right clicking the SQL Server Instance and selecting the Facets option from the drop down list as shown in the snippet below.

This will open up View Facets window as shown in the snippet below. Here you need to select Surface Area Configuration facet as highlighted and then select the option as “True” for RemoteDacEnabled.

Finally, click OK to save the configuration changes in the View Facets window.

You can also enable other database engine features like AsHocRemoteQueriesEnabled, ClrIntegrationEnabled, DatabaseMailEnabled, OleAutomationEnabled, ServiceBrokerEndpointActive, SoapEndpointsEnabled, SQLMailEnabled, WebAssistanceEnabled, XPCmdShellEnabled etc when required using the Surface Server Configuration Facet which is available in SQL Server 2008 Management Studio.

Once the Dedicated Administrator Connection is enabled you can connect to SQL Server 2008 using either SQL Server Management Studio or using SQLCMD.

---

Using DAC with SQLCMD

You need to be a member of the sysadmin fixed server role in order to use this feature. The “-A” switch is used to specify that the user is trying to connect to SQL Server using the Dedicated Administrator Connection.
The syntax to use DAC is mentioned below.

SQLCMD –S [SQL Server Name] –U [User Name] –P [Password] –A

---

Using DAC with SQL Server Management Studio

You need to specify “ADMIN:” before the SQL Server Instance name when trying to connect to an SQL Server Instance to using DAC feature as shown in the snippet below.

Once you are connected to SQL Server Instance using DAC, then you can execute code such as the code below to check the SQL Server health.

-- Locking Information SELECT * FROM sys.dm_tran_locks GO -- Cache Status SELECT * FROM sys.dm_os_memory_cache_counters GO -- Active Sessions SELECT * FROM sys.dm_exec_sessions GO -- Requests Status SELECT * FROM sys.dm_exec_requests GO

Next Steps

• Make sure you have enabled Dedicated Administrator Connection Feature on all the SQL Server 2005 and on SQL Server 2008 servers.
• If at any point database engine is not allowing regular connections, then use this feature to troubleshoot issues without shutting down SQL Server.
• DAC for SQL Server 2005

Related Tips: More | Become a paid author

Last Update: 7/24/2009

Share:

Share

Free SQL Server Learning Free webcast - Introduction to SQL Azure Demystify TempDB Performance and Manageability

Comments and Feedback:

Tuesday, January 11, 2011 - 11:54:19 AM - Jeremy Kadlec

Read The Tip

Ashish, Great tip. I have one comment for SQL Server 2008 R2.  In order to start the DAC in SSMS, I believe you need to navigate to File | New | Database Engine Query, then specify 'ADMIN:' prior to the server name.  I believe as long as you have sa rights you can connect to the DAC either with a SQL Server standard login or Windows Authentication. I do not think you can use the 'Connect' option from Object Explorer in order to start a DAC session. Finally, I think you will know if the DAC connection has been established when you look at the bottom status bar in SSMS and see 'ADMIN:SQLServerName'. I hope this helps. Thank you, Jeremy Kadlec

Friday, August 26, 2011 - 8:21:32 AM - Leo Smulders	Read The Tip
Hi, I Observe that 'remote admin connections' is not an advanced option. So setting 'show advanced options' to 1 is not necessary, neither to show, nor to change 'remote admin connections'. Leo Smulders Lucy Software B.V.

Friday, August 26, 2011 - 8:54:33 AM - Jeremy Kadlec	Read The Tip
Leo, Thank you for the post.  We will update the tip.  I see the same behavior in SQL Server 2008 R2. Thank you, Jeremy Kadlec

copied from:

http://www.mssqltips.com/sqlservertip/1801/enable-sql-server-2008-dedicated-administrator-connection/

Here are some common sql server default ports used:

SQL Component	Port (TCP -default or UDP )
SQL Service	1433
SQL Server DAC (Dedicated Admin Connection)	1434
SQL Browser Service	1434(UDP)
SQL Monitoring	1444
SQL Server Analysis Services Redirector	2382
SQL Server Analysis Service	2383
SQL Server Reporting Services	80
SQL Database Mirroring	5022

By default, the DAC only listens on the loop-back IP address (127.0.0.1), port 1434. If TCP port 1434 is not available, a TCP port is dynamically assigned when the Database Engine starts up. When more than one instance of SQL Server is installed on a computer, check the error log for the TCP port number.

P.S.
1) check this dmv for more informations
select * from sys.dm_exec_connections

2)
 sqlcmd –S127.0.0.1,1434

3) enable DAC:
use master
go
sp_configure 'remote admin connections', 1
go
RECONFIGURE;
go

4) 
sqlcmd -S127.0.0.1 -E -A
-A means dedicated administration connection
-S should not contain port, why?

5)
you can shoutdown server from  sqlcmd, just like Sybase
shutdown
go

6)
isql is the baby from Sybase ASE
osql is newer than isql
sqlcmd is newer than osql
powershell is newer than sqlcmd



Reference:
1) http://joshrobi.blogspot.ca/2008/02/sql-server-default-ports.html
2) http://sqlbuzz.wordpress.com/2011/08/09/sql-server-default-ports/

Conceptual, Logical and Physical Data Model

Here we compare these three types of data models. The table below compares the different features:

Feature	Conceptual	Logical	Physical
Entity Names	✓	✓
Entity Relationships	✓	✓
Attributes		✓
Primary Keys		✓	✓
Foreign Keys		✓	✓
Table Names			✓
Column Names			✓
Column Data Types			✓

Below we show the conceptual, logical, and physical versions of a single data model.

Conceptual Model Design

Logical Model Design

Physical Model Design

We can see that the complexity increases from conceptual to logical to physical. This is why we always first start with the conceptual data model (so we understand at high level what are the different entities in our data and how they relate to one another), then move on to the logical data model (so we understand the details of our data without worrying about how they will actually implemented), and finally the physical data model (so we know exactly how to implement our data model in the database of choice). In a data warehousing project, sometimes the conceptual data model and the logical data model are considered as a single deliverable.

	The terms "conceptual". "logical", and "physical" are frequently used in data modeling to differentiate levels of abstraction versus detail in the model. Although there is no general agreement, let alone accepted authority, which defines these terms, nevertheless data modelers generally understand the approximate scope of each.
Con- ceptual E-R Model	A conceptual entity-relationship model shows how the business world sees information. It suppresses non-critical details in order to emphasize business rules and user objects. It typically includes only significant entities which have business meaning, along with their relationships. Many-to-many relationships are acceptable to represent entity associations. A conceptual model might discover that there is a need to house information about each person in an organization. While considerable thought is given to discovering and describing the relevant properties of each person, the designers accept implicitly that each person is distinct and unique. A conceptual model may include a few significant attributes to augment the definition and visualization of entities. No effort need be made to inventory the full attribute population of such a model. A conceptual model may have some identifying concepts or candidate keys noted but it explicitly does not include a complete scheme of identity, since identifiers are logical choices made from a deeper context.
Logical E-R Model	A logical entity-relationship model is provable in the mathematics of data science. Given the current predominance of relational databases, logical models generally conform to relational theory. Thus a logical model contains only fully normalized entities. Some of these may represent logical domains rather than potential physical tables. For a logical data model to be normalized, it must include the full population of attributes to be implemented and those attributes must be defined in terms of their domains or logical data types (e.g., character, number, date, picture, etc.). A logical data model requires a complete scheme of identifiers or candidate keys for unique identification of each occurrence in every entity. Since there are choices of identifiers for many entities, the logical model indicates the current selection of identity. Propagation of identifiers as foreign keys may be explicit or implied. Since relational storage cannot support many-to-many concepts, a logical data model resolves all many-to-many relationships into associative entities which may acquire independent identifiers and possibly other attributes as well.
Physical Database Schema	A physical data model is a single logical model instantiated in a specific database management product (e.g., Sybase, Oracle, Informix, etc.) in a specific installation. The physical data model specifies implementation details which may be features of a particular product or version, as well as configuration choices for that database instance. These include index construction, alternate key declarations, modes of referential integrity (declarative or procedural), constraints, views, and physical storage objects such as tablespaces.
In Summary	The conceptual model is concerned with the real world view and understanding of data; the logical model is a generalized formal structure in the rules of information science; the physical model specifies how this will be executed in a particular DBMS instance. Various data modeling methodologies and products provide these layers of abstraction in different ways. Some address only the physical implementation; some model only the logical structure; others may provide elements of all three but not necessarily in three separate views. In each case it helps the data modeler to understand the level of abstraction to which a particular feature or task belongs.

Reference:
http://www.1keydata.com/datawarehousing/data-modeling-levels.html
http://www.aisintl.com/case/CDM-PDM.html