A basic sql query problem

Suppose we get two following tables:

Table A:
[USER_ID] [varchar] (11) NOT NULL ,
[COURSE_ID] [varchar] (11) NOT NULL ,

Table B:

[COURSE_ID] [varchar] (11) NOT NULL ,
[COURSE_NAME] [varchar] (50) NOT NULL ,
[COURSE_NO] [varchar] (15) NULL ,
[BEGIN_DATE] [datetime] NULL ,
[END_DATE] [datetime] NULL ,
[CREATER] [varchar] (11) NOT NULL ,

and during the execution of my program, I can get the current use's id (USER_ID), sayU0001.

How can I retrieve the result set containing[COURSE_NAME],[COURSE_ID],but the current user's id (U0001) have Not been assigned in Table A.

Thanks in advance.

Ricky.

if u know what the course will be taken by the userid U0001 then just query from table B and insert the data of user in the table A.say u want to assign the user U0001 to "Eng01" query with select course_id,course_name from tableB where course_name ='Eng01' and then assign this course_id and user_id to tableA...it is not clear to me that what u wants to do ?|||

Are you talking about joining between two table?

If yes, try SELECT TableA.User_ID, TableB.CourseID, TableB.Course_Name FROM TableB LEFT OUTER JOIN TableA ON TableA.CourseID = TableB.CourseID

I haven't test it yet, but try searching for "LEFT OUTER JOIN" if it doesn't work.

Hope that help.

|||

Sorry for my unclear description of my question first.

And nr2ea, you provide me helpful hint.

I have one more question: is there any difference between LEFT JOIN and LEFT OUTER JOIN?

Ricky.

8 hours to add a bit column

I ran the following:

ALTER TABLE Recipients ADD Obscene BIT NOT NULL DEFAULT 0

On a table with 80 million records. It's been running for 8 hours and counting now. This is ridiculous. No one else is using this server.

Server configuration:
SQL Server 2000 Enterprise Edition with SP3a
2 GB RAM
3.0 GHz P4 with hyperthreading
SCSI RAID

Any ideas why this is taking so long? Can I find out what it's doing? Is there anything I can do to make it go faster?You won't like any of my answers.

Before we descend into that morass though, crank up the NT Performance Monitor and have a look-see at the server. Is it CPU bound, disk bound, memory bound, or are all of those counters at reasonable levels? How many SQL threads (spids) are active? What do the SQL page counters look like?

My guess would be that the box is hideously RAM bound, that it is having page splits up the ying-yang, and that it might be disk bound as well.

Is your log file on a different disk device than your data files? Is that device mirrored instead of RAID? Do you have the ability to add RAM with the box running (some servers can do that!)?

-PatP|||If your answers give me any ideas and any closer to figuring these kinds of problems out, I like them. The business that I'm working for is very data driven and we are moving quickly in a database direction and we need this knowledge.

I've been watching CPU and disc levels in perfmon all day. The system has been steadily disc bound.
sp_who 'active' returns 17 rows.
How do I look at the page counters?

A RAM bound system exhibits itself as disc bound, correct? With lots of paging to/from disc.

The data files and temp db are on a SCSI RAID 5. The main log file is on a separate 250 GB IDE disc (it has grown to 150 GB in the past which wouldn't fit on the RAID)

You won't like any of my answers.

Before we descend into that morass though, crank up the NT Performance Monitor and have a look-see at the server. Is it CPU bound, disk bound, memory bound, or are all of those counters at reasonable levels? How many SQL threads (spids) are active? What do the SQL page counters look like?

My guess would be that the box is hideously RAM bound, that it is having page splits up the ying-yang, and that it might be disk bound as well.

Is your log file on a different disk device than your data files? Is that device mirrored instead of RAID? Do you have the ability to add RAM with the box running (some servers can do that!)?

-PatP|||if you trace an alter table statement it will show you the issue i think you are hitting.

alter table creates a new table in temp space with the new column, inserts all the data from the old into the new temp, then swaps the names around.

with 80million rows, i'd be willing to bet your are being I/O thottled either creating the temp (which using a low logged select..into..) or the insert, (which is using a fully logged insert into..select).

either way, 8 hours sux but doesnt really suprise me.|||I'v never had to wait 8 hours to add a column to a big table. Besides, I thought that the EM did the swap-trick and the alter table did not.|||i'd agree...8 hours is way out there.
you are correct...EM does the old swap'r'roo trick. that was an assumption on my part - uber apologies - that you were using the Enterprise Mangler.

i would suspect the same thing is happening underneath an alter, though.
(i'm moving into the 'out of my ass' realm so i'm going to qualify that statement as an 'idea'- not something i claim to know.)

the page counters are under the Memory object with some useful other SQL specifc page counters SQLServer:BufferManager.

i wonder if your disk queues are backing up for reads or writes?

perhaps that could help understand if a large read/write is actually occuring underneath your alter?|||hm, here's another though. Perhaps the Analyzer is waiting for the table to be freed from a lock. What does sysprocesses say?|||Just curious as to why you made it NOT NULL?

USE Northwind
GO

CREATE TABLE myTable99 (Col1 int)
GO
INSERT INTO myTable99(Col1) SELECT 1
GO

SELECT * FROM myTable99

ALTER TABLE myTable99 ADD Obscene BIT NOT NULL DEFAULT 0
GO

SELECT * FROM myTable99
GO

ALTER TABLE myTable99 ADD Obscene2 BIT DEFAULT 0
GO

SELECT * FROM myTable99
GO

DROP TABLE myTable99
GO

If you needed it null maybe you caould have performed batch updates after the fact, the changed ALTERed the column to make it NOT NULL...

Is it still running?|||OK, here comes Robert with his BCP again...But it's true, no matter how you look at it! Non-logged data load would beat "in-line" DDL+DML (because this is exactly what happens when you add a new NON-NULLable column with default - the only way to add a new non-nullable column) If you added the same column but made it nullable, - you'd be onto something else 7 hours and 59 minutes ago. At this point though you can't even interrupt this operation because ALTER TABLE is a fully logged operation. It means that every 0 that came from your DEFAULT is logged in your transaction log (thus its size is very explainable). If you decide to kill the process you'll be looking at 8+ hours of rollback. If you stop the service you'll be looking at "Recovering database x..." for probably as much.|||Just curious as to why you made it NOT NULL?

That's an application issue, right? We are trying to mark certain records as "obscene", so every record should be obscene or not. There should be no NULL. I didn't realize that this would be a large performance issue. If I knew in advance I could have dealt with NULL values in one way or another.

It finished overnight but as of 12:30 AM last night, it was running for 13 hours and still going.

I've previously added datetime columns to the same table with EM (this was added via QA with a ALTER TABLE statement) and it took less than an hour. I was really surprised that this took so long. Ideally, I know what to look for to remedy such an issue and how to prevent such things from happening.

thanks guys!|||Did you look at the code that em scripted for you?

Just make sure you don't have a table called tmp_yourtable..

WAIT...damn I just tested it...it's smart enough to add a _1 to the end...damn that's good|||WAIT...damn I just tested it...it's smart enough to add a _1 to the end...damn that's goodTricky little devils, ain't they ?

-PatP|||curious - when you added the datetime values last time did you set a default of getdate()? or something else? or allow them to be null?|||curious - when you added the datetime values last time did you set a default of getdate()? or something else? or allow them to be null?

They defaulted to NULL. And it was added through EM as opposed to an ALTER TABLE statement.

The exact time on adding the bit column was 17 hours and 23 minutes.

4 key primary key vs 1 key artificial primary key

Hi all

I have the following table

CREATE TABLE [dbo].[property_instance] (
[property_instance_id] [int] IDENTITY (1, 1) NOT NULL ,
[application_id] [int] NOT NULL ,
[owner_id] [nvarchar] (100) NOT NULL ,
[property_id] [int] NOT NULL ,
[owner_type_id] [int] NOT NULL ,
[property_value] [ntext] NOT NULL ,
[date_created] [datetime] NOT NULL ,
[date_modified] [datetime] NULL
)

I have created an 'artificial' primary key, property_instance_id. The 'true' primary key is application_id, owner_id, property_id and owner_type_id

In this specific instance
- property_instance_id will never be a foreign key into another table
- queries will generally use application_id, owner_id, property_id and owner_type_id in the WHERE clause when searching for a particular row
- Once inserted, none of the application_id, owner_id, property_id or owner_type_id columns will ever be modified

I generally like to create artificial primary keys whenever the primary key would otherwise consist of more than 2 columns.

What do people think the advantages and disadvantages of each technique are? Do you recommend I go with the existing model, or should I remove the artificial primary key column and just go with a 4 column primary key for this table?

Thanks Mattno the subject of surrogate and natural keys, we already have many threads (http://www.dbforums.com/search.php?action=showresults&searchid=479872&sortby=lastpost&sortorder=descending) -- not sure if that will work, it's a search for surrogate and natural

one thing troubles me, and that is how you have both owner_id and owner_type_id in your candidate key

doesn't that violate some normal form or another? isn't owner_type_id totall dependent on which owner it is?|||Originally posted by r937
no the subject of surrogate and natural keys, we already have many threads (http://www.dbforums.com/search.php?action=showresults&searchid=479872&sortby=lastpost&sortorder=descending) -- not sure if that will work, it's a search for surrogate and natural

one thing troubles me, and that is how you have both owner_id and owner_type_id in your candidate key

doesn't that violate some normal form or another? isn't owner_type_id totall dependent on which owner it is?

Thanks for the search keyword tips. I did indeed find a number of helpful threads.

Matt|||I believe the general resolution was that Plain-Bellied Sneetches prefer natural keys, while Star-Bellied Sneetches see the advantages of surrogate keys.

Experts opinions varried:
Moe: Surrogate Keys
Larry: Natural Keys
Curly: Nyuck nyuck nyuck

We are still awaiting final Papal Dispensation on the subject.|||hilarious

i'm afraid that rome will tell you that it can only be a natural primary key, as surrogates are the work of the devil

one of curly's best lines is "i'm tryna think but nothin happens!" which is how i feel sometimes when asked to list the pros and cons of surrogate keys versus natural

maybe i should just say the words "surrogate key" again, and perhaps also the words "natural key" -- so it shouldn't be a total loss, that way they will be salted quite heavily in this post, and therefore this thread, for the benefit of search engines

moe and larry: "niagara falls!! slowly i turn... step by step... inch by inch..."
[moe and larry advance on curly, who backs up into the bathroom, and falls into the bathtub]
moe: "what are you doing in there?!"
curly: "so it shouldn't be a total loss, i'm takin a bath"
moe: "a bath?!"
curly: "yeah, a bath"
moe (after a slight pause): "move over"|||For what it's worth:

it depends upon the scale of the data, the query composition and whether or not you need to sub index on a primary's sub key.

given

application_id, owner_id, and property_id

being the key, the whole key and nothing but the key, do you ever need to index to owner_id alone? if yes then:

owner_id, application_id, and property_id

and so on.

Do you join across tables, i.e. owner_id -> owner details and property_id -> property details? If so, which ordering of the composite key gives fastest access? Who the hell knows without knowledge of the data definition? Nobody, that's who.

There's no generic solution to this question, it depends entirely on your data, and you should normalize to a correctly third normal multiple key and then procedurally denormalize to a compound key if your unique scenario requires it.