December 14, 2012 at 6:34 am
Hi
i have table like
CREATE TABLE [dbo].[Mas_Shift]
(
[ShiftId] [int] IDENTITY(1,1) NOT NULL,
[FacilityId] [int] NULL,
[ShiftType] [nvarchar](5) NULL,
[Shift] [nvarchar](3) NULL
)
INSERT [dbo].[Mas_Shift] ([ShiftId], [FacilityId], [ShiftType], [Shift]) VALUES (4, 1, N'3', N'M')
INSERT [dbo].[Mas_Shift] ([ShiftId], [FacilityId], [ShiftType], [Shift]) VALUES (5, 1, N'3', N'N')
INSERT [dbo].[Mas_Shift] ([ShiftId], [FacilityId], [ShiftType], [Shift]) VALUES (6, 1, N'1', N'G')
i need o/p like
shift
---------
MN
NG
MG
THANK YOU
-------------
SWAT
December 14, 2012 at 6:39 am
Can you describe what you are trying to do here?
December 14, 2012 at 6:40 am
SELECT a.Shift+b.Shift
FROM [dbo].[Mas_Shift] a
CROSS JOIN [dbo].[Mas_Shift] b
WHERE a.ShiftId < b.ShiftId;
December 17, 2012 at 9:19 am
CELKO (12/14/2012)
I hope you know that IDENTITY has no place in RDBMS. It is how non-SQL programmers fake a magnetic tape file in SQL. You also do not know about ISO-11179 naming rules. Is this what you meant if you had a valid schema with a key?CREATE TABLE MAS_Shifts
(facility_id INTEGER NOT NULL,
shift_name CHAR(3) NOT NULL,
PRIMARY KEY (facility_id, shift_name),
shift_type CHAR(5) NOT NULL);
INSERT INTO MAS_Shifts
VALUES (1, 'M', 3') , (1, 'N', '3'), ( 1, 'G', '1');
After that, I have no idea what your question is. Can you try again?
I hope you know better and will disregard the above misleading advise.
IDENTITY has a very important place in RDBMS and especially in SQL Server. It's very often the best candidate for a surrogate PK.
IDENTITY-like structures are implemented in most of the known RDBMS(s) and they have nothing to do with magnetic tape files or/and punch cards!
December 17, 2012 at 9:26 am
OP requirements very obscure.
What should happen if there is another group of data with different FacilityId:
INSERT [dbo].[Mas_Shift] ([ShiftId], [FacilityId], [ShiftType], [Shift]) VALUES (7, 2, N'3', N'A')
INSERT [dbo].[Mas_Shift] ([ShiftId], [FacilityId], [ShiftType], [Shift]) VALUES (8, 2, N'3', N'B')
INSERT [dbo].[Mas_Shift] ([ShiftId], [FacilityId], [ShiftType], [Shift]) VALUES (9, 2, N'1', N'C')
If FacilityId is a group denominator, then you can use the following:
SELECT a.Shift+b.Shift
FROM [dbo].[Mas_Shift] a
JOIN [dbo].[Mas_Shift] b
ON b.FacilityId = a.FacilityId
WHERE a.ShiftId < b.ShiftId;
December 18, 2012 at 2:59 am
CELKO (12/17/2012)
IDENTITY has a very important place in RDBMS and especially in SQL Server. It's very often the best candidate for a surrogate PK.
You might want to actually read Dr. Codd and any book on RDBMS. Please quote Dr. Codd on this topic in TODS, any presentation or any speech, show me where it is in his 12 rules, etc.
People get "exposed physical locator" and surrogate mixed up; they are totally different concepts. Let me start with an appeal to authority, with a quote from Dr. Codd:
...
To me, the above is like:
I'm telling you:
There is no God!
You telling me:
You might want to actually read the Bible...
and
Let me start with an appeal to authority, with a quote from Pope...
There are theories and there is a real life. And in the real life there are cases where you will have a great trouble finding Natural Keys. We had numerous discussion on this forum where I've tried to get from you an example of Natural Keys for very common database entities such as "Person" and "Company" and you didn't manage to advise anything useful (something which will work internationally for people and for all companies sizes).
But let try another one. Let say Vatican wants a database of all Christian saints. What kind of Natural Key will you advise? SSN number will not work, regardless of what Dr. Codd might written in his nice books...
Also, there are solutions where IDENTITY is a best candidate for PK anyway - data warehouses. Can you advise anything better than identity for slowly changing dimensions?
There are other aspects which make IDENTITY to be very good PK and performance one of them...
I have proven to you once my googling skills, haven't I? So, you do like authorities... :
From C. J. Date's book "An Introduction to Database Systems, C J Date, 8th Edition":
Page 434, reference 14.21 - he refers to P. Hall, J. Owlett and S J P Todd "Relations and Entities" and says:
"Surrogate Keys are keys in the usual Relational sense but have the following specific properties:
They always involve exactly one attribute.
Their values serve solely as surrogate (hence the name) for the entities they stand for ......
When a new entity is inserted into the database, it is given a surrogate key value that has never been used before and will never be used again, even if the entity in question is subsequently deleted.
Ideally surrogate keys value would be system-generated.
You took Dr. Codd's from the same site, didn't you. Actually, it was not there to explain why surrogate keys shouldn't be used, quite the opposite...
So looks like some other authorities do not have any problem with using Surrogates, especially system-generated ones. That SQL Server IDENTITY is for!
You stance on the IDENTITY, shows that you are very knowledgeable theoretic, but you don't have much real life experience with using SQL Server in enterprise level solutions.
Should I say that you know nothing about SQL Server, that you should take and read at least some book on it(not yours, of cause)? Microsoft SQL Server Book Online will do for starter...
:hehe:
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply