December 24, 2018 at 4:31 am
In parent child, I want to pass EmployeeId and get the list of all parents from tree till given EmployeeId as well as all child to which I am parent of those.
when I am giving EmployeeId, it retuns two lists,
1- All your parents, reverse entry (immediate parent then grand parent and grand grand parent and so on up till NULL)
2- The second list should give list of all children and their children those which belongs to only me.
How can I do this? code added below...
queries-to-manage-hierarchical-or-parent-child
USE [att_test]
GO
CREATE TABLE [dbo].[MyEmployees](
[EmployeeID] [int] NOT NULL,
[FirstName] [nvarchar](30) NOT NULL,
[LastName] [nvarchar](40) NOT NULL,
[Title] [nvarchar](50) NOT NULL,
[DeptID] [smallint] NOT NULL,
[ManagerID] [int] NULL,
CONSTRAINT [PK_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
INSERT [dbo].[MyEmployees] ([EmployeeID], [FirstName], [LastName], [Title], [DeptID], [ManagerID]) VALUES (1, N'Ken', N'Sánchez', N'Chief Executive Officer', 16, NULL)
INSERT [dbo].[MyEmployees] ([EmployeeID], [FirstName], [LastName], [Title], [DeptID], [ManagerID]) VALUES (16, N'David', N'Bradley', N'Marketing Manager', 4, 273)
INSERT [dbo].[MyEmployees] ([EmployeeID], [FirstName], [LastName], [Title], [DeptID], [ManagerID]) VALUES (23, N'Mary', N'Gibson', N'Marketing Specialist', 4, 16)
INSERT [dbo].[MyEmployees] ([EmployeeID], [FirstName], [LastName], [Title], [DeptID], [ManagerID]) VALUES (273, N'Brian', N'Welcker', N'Vice President of Sales', 3, 1)
INSERT [dbo].[MyEmployees] ([EmployeeID], [FirstName], [LastName], [Title], [DeptID], [ManagerID]) VALUES (274, N'Stephen', N'Jiang', N'North American Sales Manager', 3, 273)
INSERT [dbo].[MyEmployees] ([EmployeeID], [FirstName], [LastName], [Title], [DeptID], [ManagerID]) VALUES (275, N'Michael', N'Blythe', N'Sales Representative', 3, 274)
INSERT [dbo].[MyEmployees] ([EmployeeID], [FirstName], [LastName], [Title], [DeptID], [ManagerID]) VALUES (276, N'Linda', N'Mitchell', N'Sales Representative', 3, 274)
INSERT [dbo].[MyEmployees] ([EmployeeID], [FirstName], [LastName], [Title], [DeptID], [ManagerID]) VALUES (285, N'Syed', N'Abbas', N'Pacific Sales Manager', 3, 273)
INSERT [dbo].[MyEmployees] ([EmployeeID], [FirstName], [LastName], [Title], [DeptID], [ManagerID]) VALUES (286, N'Lynn', N'Tsoflias', N'Sales Representative', 3, 285)
----------------------------------------------
;
WITH Hierarchy(EmployeeID, FirstName, LastName, Title, Generation, ManagerId)
AS
(
SELECT EmployeeID, FirstName, LastName, Title, 0, ManagerID
FROM MyEmployees AS FirtGeneration
WHERE ManagerID IS NULL
UNION ALL
SELECT NextGeneration.EmployeeID, NextGeneration.FirstName, NextGeneration.LastName, NextGeneration.Title, Parent.Generation + 1, Parent.EmployeeID
FROM MyEmployees AS NextGeneration
INNER JOIN Hierarchy AS Parent ON NextGeneration.ManagerID = Parent.EmployeeID
)
SELECT *
FROM Hierarchy
OPTION(MAXRECURSION 32767)
--all posible parents of @id
;
DECLARE @EmployeeId int;
SET @EmployeeId = 275;
WITH tblParent AS
(
SELECT *
FROM MyEmployees WHERE EmployeeID = @EmployeeId
UNION ALL
SELECT MyEmployees.*
FROM MyEmployees JOIN tblParent ON MyEmployees.EmployeeId = tblParent.ManagerID
)
select * from MyEmployees WHERE EmployeeId = @EmployeeId
union all
SELECT * FROM tblParent
WHERE EmployeeId <> @EmployeeId
OPTION(MAXRECURSION 32767)
;
-- all posible childs of @userTypeId
DECLARE @EmployeeId int;
SET @EmployeeId = 274;
WITH tblChild AS
(
SELECT *
FROM MyEmployees WHERE ManagerID = @EmployeeId
UNION ALL
SELECT MyEmployees.* FROM MyEmployees JOIN tblChild ON MyEmployees.ManagerID = tblChild.EmployeeID
)
SELECT *
FROM tblChild
OPTION(MAXRECURSION 32767)
December 24, 2018 at 5:04 am
People are unlikely to download your rar file. Rather than uploading a (potentially harmful) file, include the script in the in text of your post. Could you also include yours attempt(s) when you do so please?
Thanks.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
December 24, 2018 at 5:15 am
Thom A - Monday, December 24, 2018 5:04 AMPeople are unlikely to download your rar file. Rather than uploading a (potentially harmful) file, include the script in the in text of your post. Could you also include yours attempt(s) when you do so please?Thanks.
I have added the sample for you.
December 24, 2018 at 8:22 am
What is missing here? What don't you see? What do you not understand about the lists? You've given some requirements (though not the results you expect given that data) and some code, but haven't seemed to dig into what works or doesn't work.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
December 24, 2018 at 11:04 am
>> In parent-child, I want to pass emp_id and get the list of all parents from tree till given emp_id as well as all children to which I am the parent of those. <<
what you are doing is called an “adjacency list model” and you should stop. There is a Turkish proverb that says no matter how far you have gone down the wrong road turnaround. What you’re doing is mimicking non-relational pointer chains using SQL with non-normalized data.
You also did not do a good job with it. Why do you think identifiers are numeric? What math do you do with them? What is the square root of your credit card number? Since they are on a nominal scale (if you don’t know about scales and measurements, you need to stop and catch up your education), they have to be character strings. Also, the prefix “tbl_” is a design flaw called a Tibble. We never mix data and metadata in a name.
Google “nested set model” and your queries become trivial. If you want to learn other methods for modeling trees and hierarchies in SQL, get a copy of my book on this topic.
Please post DDL and follow ANSI/ISO standards when asking for help.
December 24, 2018 at 11:39 am
jcelko212 32090 - Monday, December 24, 2018 11:04 AM>> In parent-child, I want to pass emp_id and get the list of all parents from tree till given emp_id as well as all children to which I am the parent of those. <<what you are doing is called an “adjacency list model†and you should stop. There is a Turkish proverb that says no matter how far you have gone down the wrong road turnaround. What you’re doing is mimicking non-relational pointer chains using SQL with non-normalized data.
You also did not do a good job with it. Why do you think identifiers are numeric? What math do you do with them? What is the square root of your credit card number? Since they are on a nominal scale (if you don’t know about scales and measurements, you need to stop and catch up your education), they have to be character strings. Also, the prefix “tbl_†is a design flaw called a Tibble. We never mix data and metadata in a name.
Google “nested set model†and your queries become trivial. If you want to learn other methods for modeling trees and hierarchies in SQL, get a copy of my book on this topic.
I actually find it laughable that you think identifiers have to be character strings when it is probably just fine for them to be numeric even if you aren't doing math on the values. I really find it frustrating when people put artificial constraints on things just because they think they are the smartest person in the room.
December 24, 2018 at 2:05 pm
jcelko212 32090 - Monday, December 24, 2018 11:04 AM>> In parent-child, I want to pass emp_id and get the list of all parents from tree till given emp_id as well as all children to which I am the parent of those. <<what you are doing is called an “adjacency list model†and you should stop. There is a Turkish proverb that says no matter how far you have gone down the wrong road turnaround. What you’re doing is mimicking non-relational pointer chains using SQL with non-normalized data.
You also did not do a good job with it. Why do you think identifiers are numeric? What math do you do with them? What is the square root of your credit card number? Since they are on a nominal scale (if you don’t know about scales and measurements, you need to stop and catch up your education), they have to be character strings. Also, the prefix “tbl_†is a design flaw called a Tibble. We never mix data and metadata in a name.
Google “nested set model†and your queries become trivial. If you want to learn other methods for modeling trees and hierarchies in SQL, get a copy of my book on this topic.
Your queries may certainly become trivial with a full conversion to "Nested Sets" but, if you stick only to "Nested Sets", maintenance of the "Nested Sets" is a pain especially if something goes wrong and a human needs to troubleshoot and repair the problem. Use the "Adjacency List" to maintain the hierarchy and do conversions to rebuild the "Nested Sets" after such maintenance is complete.
Of course, you don't want to use traditional "Push Stack" methods for such things because they're RBAR on steroids. The methods in the following article will do a large Adjacency List to Nested Sets conversion in just seconds and a million node hierarchy in less than a minute.
Hierarchies on Steroids #1: Convert an Adjacency List to Nested Sets
Of course, if you want to avoid most on-the-fly hierarchical traversals for most of the things you might interrogate a Directed Acyclic Graph (a fancy name for certain types of hierarchies like a parts list or employee org chart), you can make the leap to a pre-aggregated table to get your answers. It takes only the same amount of time to transform to from an Adjacency List as it does transformation to Nested Sets.
Hierarchies on Steroids #2: A Replacement for Nested Sets Calculations
As for your insistence that identifiers should not be numeric because you don't use them in calculations, I'll tell you the same thing I tell you every time you post such lunacy... post demonstrable code that actually works in SQL Server using an "Employee" table or "Personnel" table (or whatever your "standards" say to call it today) to prove your point. Before you do that, though, I suggest you get a good book on the proper use of identifies and keys and how they work in SQL Server and why. I know things like primary keys being immutable, narrow, and meaningless to the end user might escape you but you can learn about such things by reading something besides your own books. 😉
Here's a good video on the subject by someone that used to work on this type of stuff at Microsoft and I consider her to be an expert on the subject. If you think there's no math involved on numeric keys, you've got another thing coming. 😉
https://www.youtube.com/watch?v=QjCEkI8Qm5c
Seriously, Joe... reconsider your position on the idea of never using numeric keys (which you call an "ID", although I agree that's a terrible name for it).
--Jeff Moden
December 24, 2018 at 3:37 pm
shamshad.ali - Monday, December 24, 2018 4:31 AMIn parent child, I want to pass EmployeeId and get the list of all parents from tree till given EmployeeId as well as all child to which I am parent of those.
when I am giving EmployeeId, it retuns two lists,
1- All your parents, reverse entry (immediate parent then grand parent and grand grand parent and so on up till NULL)
2- The second list should give list of all children and their children those which belongs to only me.How can I do this? code added below...
queries-to-manage-hierarchical-or-parent-childUSE [att_test]
GO
CREATE TABLE [dbo].[MyEmployees](
[EmployeeID] [int] NOT NULL,
[FirstName] [nvarchar](30) NOT NULL,
[LastName] [nvarchar](40) NOT NULL,
[Title] [nvarchar](50) NOT NULL,
[DeptID] [smallint] NOT NULL,
[ManagerID] [int] NULL,
CONSTRAINT [PK_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
INSERT [dbo].[MyEmployees] ([EmployeeID], [FirstName], [LastName], [Title], [DeptID], [ManagerID]) VALUES (1, N'Ken', N'Sánchez', N'Chief Executive Officer', 16, NULL)
INSERT [dbo].[MyEmployees] ([EmployeeID], [FirstName], [LastName], [Title], [DeptID], [ManagerID]) VALUES (16, N'David', N'Bradley', N'Marketing Manager', 4, 273)
INSERT [dbo].[MyEmployees] ([EmployeeID], [FirstName], [LastName], [Title], [DeptID], [ManagerID]) VALUES (23, N'Mary', N'Gibson', N'Marketing Specialist', 4, 16)
INSERT [dbo].[MyEmployees] ([EmployeeID], [FirstName], [LastName], [Title], [DeptID], [ManagerID]) VALUES (273, N'Brian', N'Welcker', N'Vice President of Sales', 3, 1)
INSERT [dbo].[MyEmployees] ([EmployeeID], [FirstName], [LastName], [Title], [DeptID], [ManagerID]) VALUES (274, N'Stephen', N'Jiang', N'North American Sales Manager', 3, 273)
INSERT [dbo].[MyEmployees] ([EmployeeID], [FirstName], [LastName], [Title], [DeptID], [ManagerID]) VALUES (275, N'Michael', N'Blythe', N'Sales Representative', 3, 274)
INSERT [dbo].[MyEmployees] ([EmployeeID], [FirstName], [LastName], [Title], [DeptID], [ManagerID]) VALUES (276, N'Linda', N'Mitchell', N'Sales Representative', 3, 274)
INSERT [dbo].[MyEmployees] ([EmployeeID], [FirstName], [LastName], [Title], [DeptID], [ManagerID]) VALUES (285, N'Syed', N'Abbas', N'Pacific Sales Manager', 3, 273)
INSERT [dbo].[MyEmployees] ([EmployeeID], [FirstName], [LastName], [Title], [DeptID], [ManagerID]) VALUES (286, N'Lynn', N'Tsoflias', N'Sales Representative', 3, 285)----------------------------------------------
;
WITH Hierarchy(EmployeeID, FirstName, LastName, Title, Generation, ManagerId)
AS
(
SELECT EmployeeID, FirstName, LastName, Title, 0, ManagerID
FROM MyEmployees AS FirtGeneration
WHERE ManagerID IS NULL
UNION ALL
SELECT NextGeneration.EmployeeID, NextGeneration.FirstName, NextGeneration.LastName, NextGeneration.Title, Parent.Generation + 1, Parent.EmployeeID
FROM MyEmployees AS NextGeneration
INNER JOIN Hierarchy AS Parent ON NextGeneration.ManagerID = Parent.EmployeeID)
SELECT *
FROM Hierarchy
OPTION(MAXRECURSION 32767)--all posible parents of @id
;
DECLARE @EmployeeId int;
SET @EmployeeId = 275;
WITH tblParent AS
(
SELECT *
FROM MyEmployees WHERE EmployeeID = @EmployeeId
UNION ALL
SELECT MyEmployees.*
FROM MyEmployees JOIN tblParent ON MyEmployees.EmployeeId = tblParent.ManagerID
)
select * from MyEmployees WHERE EmployeeId = @EmployeeId
union all
SELECT * FROM tblParent
WHERE EmployeeId <> @EmployeeIdOPTION(MAXRECURSION 32767)
;
-- all posible childs of @userTypeId
DECLARE @EmployeeId int;
SET @EmployeeId = 274;
WITH tblChild AS
(
SELECT *
FROM MyEmployees WHERE ManagerID = @EmployeeId
UNION ALL
SELECT MyEmployees.* FROM MyEmployees JOIN tblChild ON MyEmployees.ManagerID = tblChild.EmployeeID
)
SELECT *
FROM tblChild
OPTION(MAXRECURSION 32767)
I guess I don't understand your question. If you're using just an "Adjacency List" (parent/child table) as you are, your 2nd and 3rd query appear to do exactly what you asked for. What else are you looking for?
--Jeff Moden
December 25, 2018 at 10:20 am
jcelko212 32090 - Monday, December 24, 2018 11:04 AM... I really find it frustrating when people put artificial constraints on things just because they think they are the smartest person in the room.
When I'm teaching a class on basic data modeling, I told this joke:
a teacher is quizzing her kids. She then starts calling on them to get the answer to a simple modification.
"Billy, what is 5×6?"
"I think it's Friday.."
"No! Not even close! Mary, what is 5×6?"
"It is red, teacher!"
"No. Sammy, what is 5×6?"
"30, teacher."
"Correct! Now Sammy, tell everyone how you got that answer."
"I divided Friday by red."
This lets me lead into the concepts of scales and datatypes. An identifier uses a nominal scale. By definition, you can't do math on nominal scale. In ISO standards, encodings use a basic Latin alphabet, digits and a limited set of punctuation marks. Unicode requires that all alphabets, celebrities, etc. used on earth include this set of characters. Since most of the world on the Metric system (SI), as well as other ISO standards, this lets everybody communicate their data.
In fact, Phil Factor recently had a column griping about the morons that use inappropriate datatypes. How many times have you seen a date modeled with a big integer? How about ZIP Codes stored as integers? Hey, following your logic, what if I stopped writing '78727' and use 'LXXVMMMDCCXXVII' for my ZIP Code in my address? Or maybe I could use octal!
I really find it frustrating when people fail to follow international standards, and try to design databases without any idea of what a data model is.
Please post DDL and follow ANSI/ISO standards when asking for help.
December 25, 2018 at 2:17 pm
Lynn Pettis - Monday, December 24, 2018 11:39 AMjcelko212 32090 - Monday, December 24, 2018 11:04 AM... I really find it frustrating when people put artificial constraints on things just because they think they are the smartest person in the room.
When I'm teaching a class on basic data modeling, I told this joke:
a teacher is quizzing her kids. She then starts calling on them to get the answer to a simple modification.
"Billy, what is 5×6?"
"I think it's Friday.."
"No! Not even close! Mary, what is 5×6?"
"It is red, teacher!"
"No. Sammy, what is 5×6?"
"30, teacher."
"Correct! Now Sammy, tell everyone how you got that answer."
"I divided Friday by red."
This lets me lead into the concepts of scales and datatypes. An identifier uses a nominal scale. By definition, you can't do math on nominal scale. In ISO standards, encodings use a basic Latin alphabet, digits and a limited set of punctuation marks. Unicode requires that all alphabets, celebrities, etc. used on earth include this set of characters. Since most of the world on the Metric system (SI), as well as other ISO standards, this lets everybody communicate their data.
In fact, Phil Factor recently had a column griping about the morons that use inappropriate datatypes. How many times have you seen a date modeled with a big integer? How about ZIP Codes stored as integers? Hey, following your logic, what if I stopped writing '78727' and use 'LXXVMMMDCCXXVII' for my ZIP Code in my address? Or maybe I could use octal!
I really find it frustrating when people fail to follow international standards, and try to design databases without any idea of what a data model is.
AHH, I see the problem here, the teacher had the students believe that numbers used for IDs had a value, and those values could replace the number. Numbers in a nominal scale don't have a value, seems the teacher missed out teaching the students that important part, and thus deprived them of the option of using a number as an ID, and making use of such simple functions as the IDENTITY property in SQL Server.
Hopefully the teacher will make sure to cover that next time. 🙂
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
December 25, 2018 at 3:29 pm
It got strange at times. The kids could not conceptually separate a number, a numeral, a character string, and a digit. The concepts are just a little too abstract for some people. I even ran into some kids were into numerology. Did you know that the double four Chinese domino is the number of human virtues (eight of them. I can't remember what they are), but it's easy to remember because all the spots are red, like blood.
It was even worse with strings! I had a lot of students who did not believe that a blank is a printing character in the IT world. They got confused over upper and lower case letters ("job" is a noun; "Job" is an Old Testament prophet, etc), and always trying to read a string as it was a word.
I remember the 3270 terminals, but at a friend to work for a company where they had to lay out the input screens in such a way that they were never 13 lines on a screen. The superstitious input clerks would never type data on the 13th line. :crying:
I did, however, stress that a physical count of physical insertion attempts on one machine, to one table, which is what the IDENTITY table property is, can never be a key in RDBMS. It has nothing whatsoever to do with the data, so it's never an attribute of the entity being modeled. But it sure looks like a UNIX record number or magnetic tape file for people that don't think abstractly.
Please post DDL and follow ANSI/ISO standards when asking for help.
December 25, 2018 at 3:49 pm
jcelko212 32090 - Tuesday, December 25, 2018 3:29 PMIt got strange at times. The kids could not conceptually separate a number, a numeral, a character string, and a digit. The concepts are just a little too abstract for some people. I even ran into some kids were into numerology. Did you know that the double four Chinese domino is the number of human virtues (eight of them. I can't remember what they are), but it's easy to remember because all the spots are red, like blood.It was even worse with strings! I had a lot of students who did not believe that a blank is a printing character in the IT world. They got confused over upper and lower case letters ("job" is a noun; "Job" is an Old Testament prophet, etc), and always trying to read a string as it was a word.
I remember the 3270 terminals, but at a friend to work for a company where they had to lay out the input screens in such a way that they were never 13 lines on a screen. The superstitious input clerks would never type data on the 13th line. :crying:
I did, however, stress that a physical count of physical insertion attempts on one machine, to one table, which is what the IDENTITY table property is, can never be a key in RDBMS. It has nothing whatsoever to do with the data, so it's never an attribute of the entity being modeled. But it sure looks like a UNIX record number or magnetic tape file for people that don't think abstractly.
On the contrary, it does require some abstract thinking to understand just how important a unique "record number" actually is and how it can not only save a huge amount of disk space compared to nature keys but also how it is important mathematically behind the scenes in SQL Server.
Heh... anyway, here you and I are talking about databases on Christmas. Neither of us must have a real life. 😀 On that note, Merry Christmas, Joe. I hope this finds you and yours in good health and good spirits.
Same for everyone else. Merry Christmas, folks.
--Jeff Moden
December 26, 2018 at 1:50 pm
>> On the contrary, it does require some abstract thinking to understand just how important a unique "record number" actually is and how it can not only save a huge amount of disk space compared to nature keys but also how it is important mathematically behind the scenes in SQL Server. <<
I disagree. When people see an exposed record number, they tend to think it's real in the sense that it's part of the data. It is not; it is part of the physical storage! Instead of thinking in sets, your mindset drops into a sequential file structure (very often a spreadsheet because that's what they've learned). Pretty soon they're talking about "next", "prior", and all the other things we used to have we were reading data off of a magnetic tape in the 1960s.
They think that uniqueness comes from a physical position in this list, rather than from attributes. So if there is a natural key in the data, instead of ensuring it with a UNIQUE or PRIMARY KEY constraint, they just skip it. Those key attributes should be in the data anyway, assuming that you want any data integrity in your schema.
>> Heh... anyway, here you and I are talking about databases on Christmas. Neither of us must have a real life. 😀 On that note, Merry Christmas, Joe. I hope this finds you and yours in good health and good spirits. <<
I had a life one weekend; it is grossly over-rated. 😀
Please post DDL and follow ANSI/ISO standards when asking for help.
December 26, 2018 at 2:06 pm
jcelko212 32090 - Tuesday, December 25, 2018 10:20 AMWhen I'm teaching a class on basic data modeling, I told this joke:
a teacher is quizzing her kids. She then starts calling on them to get the answer to a simple modification.
"Billy, what is 5×6?"
"I think it's Friday.."
"No! Not even close! Mary, what is 5×6?"
"It is red, teacher!"
"No. Sammy, what is 5×6?"
"30, teacher."
"Correct! Now Sammy, tell everyone how you got that answer."
"I divided Friday by red."This lets me lead into the concepts of scales and datatypes. An identifier uses a nominal scale. By definition, you can't do math on nominal scale. In ISO standards, encodings use a basic Latin alphabet, digits and a limited set of punctuation marks. Unicode requires that all alphabets, celebrities, etc. used on earth include this set of characters. Since most of the world on the Metric system (SI), as well as other ISO standards, this lets everybody communicate their data.
In fact, Phil Factor recently had a column griping about the morons that use inappropriate datatypes. How many times have you seen a date modeled with a big integer? How about ZIP Codes stored as integers? Hey, following your logic, what if I stopped writing '78727' and use 'LXXVMMMDCCXXVII' for my ZIP Code in my address? Or maybe I could use octal!
I really find it frustrating when people fail to follow international standards, and try to design databases without any idea of what a data model is.
A good explanation of Common Core Math but a complete straw man argument for prohibiting using numeric values as identifiers. It also makes the assumption that all users are that stupid. Some may be if the system is properly documented (questionable at times) your assumptions are just that, assumptions.
December 26, 2018 at 2:06 pm
jcelko212 32090 - Tuesday, December 25, 2018 3:29 PM
>> On the contrary, it does require some abstract thinking to understand just how important a unique "record number" actually is and how it can not only save a huge amount of disk space compared to nature keys but also how it is important mathematically behind the scenes in SQL Server. <<
I disagree. When people see an exposed record number, they tend to think it's real in the sense that it's part of the data. It is not; it is part of the physical storage! Instead of thinking in sets, your mindset drops into a sequential file structure (very often a spreadsheet because that's what they've learned). Pretty soon they're talking about "next", "prior", and all the other things we used to have we were reading data off of a magnetic tape in the 1960s.
They think that uniqueness comes from a physical position in this list, rather than from attributes. So if there is a natural key in the data, instead of ensuring it with a UNIQUE or PRIMARY KEY constraint, they just skip it. Those key attributes should be in the data anyway, assuming that you want any data integrity in your schema.
>> Heh... anyway, here you and I are talking about databases on Christmas. Neither of us must have a real life. 😀 On that note, Merry Christmas, Joe. I hope this finds you and yours in good health and good spirits. <<
I had a life one weekend; it is grossly over-rated. 😀
Agreed on your last. I'll continue to agree to disagree on the former. Numeric keys are powerful tools that meet all 6 attributes of proper keys and they can have a deep and highly beneficial impact on performance, disk usage, and memory usage, all of which affect performance in many areas. Also not that users aren't necessarily supposed to see these keys at all (there are worthwhile exceptions)
Unique
Immutable (Static)
Narrow (Byte Count, shared amongst ALL NCI's if a CI)
NotNull
FixedWidth
Ever-Increasing
Did you watch the YouTube at the link I provided? Considering your response, I'm thinking you haven't taken the time to do so.
--Jeff Moden
Viewing 15 posts - 1 through 15 (of 19 total)
You must be logged in to reply to this topic. Login to reply