SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 table object is not showing.
 New Topic  Reply to Topic
 Printer Friendly
Previous Page
Author Previous Topic Topic Next Topic
Page: of 2

harsh_athalye
Flowing Fount of Yak Knowledge

India
5509 Posts

Posted - 10/03/2006 :  08:58:08  Show Profile  Visit harsh_athalye's Homepage  Click to see harsh_athalye's MSN Messenger address  Send harsh_athalye a Yahoo! Message  Reply with Quote
quote:
they use the same text book, or having the same teacher


I don't think any book or teacher is dumb enough to recommend that kind of table design and dynamic sql.

May be one of them is a mentor of other

Harsh Athalye
India.
"Nothing is Impossible"
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
36653 Posts

Posted - 10/03/2006 :  11:30:57  Show Profile  Visit tkizer's Homepage  Reply with Quote
quote:
Originally posted by funketekun

quote:
Originally posted by tkizer

The SELECT portion needs to be dynamic.
Tara Kizer



why?



Because otherwise you'll get an error.

What Peso has shown you with EXEC is dynamic SQL.

Tara Kizer
Go to Top of Page

funketekun
Constraint Violating Yak Guru

Australia
491 Posts

Posted - 10/03/2006 :  11:41:32  Show Profile  Visit funketekun's Homepage  Reply with Quote
[/quote]
CREATE VIEW vwMyHugeView
AS
SELECT *, '00000' AS TableName FROM [00000]
UNION ALL
SELECT *, '00001' AS TableName FROM [00001]
UNION ALL
SELECT *, '00002' AS TableName FROM [00002]
UNION ALL
SELECT *, '00003' AS TableName FROM [00003]
[/quote]

peso, anyway to do it without typing the table name ([00000]) 500 times?
p.s. the table names are different numbers. They are not 00001...00500
Go to Top of Page

Kristen
Test

United Kingdom
22403 Posts

Posted - 10/03/2006 :  11:48:37  Show Profile  Reply with Quote
Write a macro in your editor?

or do the equivalent in SQL:

SELECT 'SELECT *, ''' + TABLE_NAME + ''' AS TableName FROM [' + TABLE_NAME + '] UNION ALL '
FROM INFORMATION_SCHEMA.TABLES
ORDER BY TABLE_NAME

Kristen
Go to Top of Page

harsh_athalye
Flowing Fount of Yak Knowledge

India
5509 Posts

Posted - 10/03/2006 :  11:50:30  Show Profile  Visit harsh_athalye's Homepage  Click to see harsh_athalye's MSN Messenger address  Send harsh_athalye a Yahoo! Message  Reply with Quote
quote:
Originally posted by Kristen

Write a macro in your editor?



Kristen,

Is it really possible to write macro in QA? or you are implying some other editor?

Harsh Athalye
India.
"Nothing is Impossible"
Go to Top of Page

Kristen
Test

United Kingdom
22403 Posts

Posted - 10/03/2006 :  11:58:29  Show Profile  Reply with Quote
Not that I know of, I was implying some other editor

Kristen
Go to Top of Page

harsh_athalye
Flowing Fount of Yak Knowledge

India
5509 Posts

Posted - 10/03/2006 :  12:20:51  Show Profile  Visit harsh_athalye's Homepage  Click to see harsh_athalye's MSN Messenger address  Send harsh_athalye a Yahoo! Message  Reply with Quote
quote:
Originally posted by Kristen

Not that I know of, I was implying some other editor

Kristen



For a moment, I believed you knew some nice cool trick to customize the QA using macros...buddy, that would have made life lot more easier !

Harsh Athalye
India.
"Nothing is Impossible"
Go to Top of Page

funketekun
Constraint Violating Yak Guru

Australia
491 Posts

Posted - 10/03/2006 :  13:26:24  Show Profile  Visit funketekun's Homepage  Reply with Quote
kristen, thats a nice trick.
thanks
Go to Top of Page

funketekun
Constraint Violating Yak Guru

Australia
491 Posts

Posted - 10/03/2006 :  14:07:31  Show Profile  Visit funketekun's Homepage  Reply with Quote
peso,

I created the hugeView, but doesn't the performace going to decrease drastically? As the view is gonna have millions of records.
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
36653 Posts

Posted - 10/03/2006 :  14:14:40  Show Profile  Visit tkizer's Homepage  Reply with Quote
Well you'd need to use a partitioned view in order to see performance gains. That's got huge scope though.

Dynamic SQL will cause performance issues as well.

Why not just write inline SQL in your application that handles which table to go to?

Your best performance solution would be to redesign your database so that you don't have all of these different tables with the same layout.

Tara Kizer
Go to Top of Page

funketekun
Constraint Violating Yak Guru

Australia
491 Posts

Posted - 10/03/2006 :  14:31:27  Show Profile  Visit funketekun's Homepage  Reply with Quote
tkizer,

what is inline sql?
inline sql = non dynamic sql?


Edited by - funketekun on 10/03/2006 14:34:35
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
36653 Posts

Posted - 10/03/2006 :  14:35:34  Show Profile  Visit tkizer's Homepage  Reply with Quote
It's when you call your select/insert/update/delete statements directly from your application and not using stored procedures. There is no benefit to using stored procedures in this case.

We don't allow any access to the database except via stored procedures, so you wouldn't be able to go down this route if you worked with me. But then again I'd require you to change your database design so that dynamic SQL wasn't required, plus to be properly normalized.

Tara Kizer
Go to Top of Page

funketekun
Constraint Violating Yak Guru

Australia
491 Posts

Posted - 10/03/2006 :  14:47:47  Show Profile  Visit funketekun's Homepage  Reply with Quote
tkizer,
I can talk to the guy to redesign it. But I don't know how to redesign it.
Go to Top of Page

jsmith8858
Dr. Cross Join

USA
7423 Posts

Posted - 10/03/2006 :  15:00:38  Show Profile  Visit jsmith8858's Homepage  Reply with Quote
You do understand the basic concept we are talking about, right?

If you have 3 tables like this:

Emp0001
Emp0002
Emp0003

and each table contains the name, title, dept, and hire date for a single employee, and the table name contains the employee number, there is no easy way to query all of your employees at once -- they are all in different tables! Thus, you have to dynamically query them one by one and combine the results, or you have to write a view to combine them all and then query the view. And, of course, to add a new employee, you must add a new table! And then alter your view that combines them all and so on.

Instead, the design should simply be:a single table of employees, where each row in the table represents an employee. i.e., like this:

Employees

EmployeeNumber (Primary key), Name, Title, Dept, Hire Date

0001,Jeff,....
0002,Ed,....
0003,Joe,....

..etc...

And now, you can query all employees at once. Adding a new employee is adding a row to a table -- very simple and easy. Deleting an employee is deleting a row. Querying a specific employee is done with a simple "WHERE EmployeeNumber=xxxx" clause on a SELECT. You can sort and filter and do updates and anything you want with basic, simple SQL statements. In other words, that's how things are supposed to work!

Does this basic concept make sense? Can you see now why the design is so bad?

I would really take the time to be sure that this all makes sense, ask questions here if you are not sure, and then discuss this with whoever designed the database. Because working with a design like this is a nightmare -- even simple little SQL statements will be monsters and very hard to write and very slow to perform.

Using the example I gave, if all employees are in one table and we want to know all of them hired since 1/1/1996, we just write:

select * from Employees where HireDate >= '1/1/1996'

Think about writing that same, simple statement with each employee instead stored in their own table! now, imagine writing a more complicated one, like average salary by department by year or something like that!


- Jeff

Edited by - jsmith8858 on 10/03/2006 15:03:12
Go to Top of Page

funketekun
Constraint Violating Yak Guru

Australia
491 Posts

Posted - 10/03/2006 :  20:10:26  Show Profile  Visit funketekun's Homepage  Reply with Quote
jsmith, i undertand, i cant do anything about it. i didnt design it.
Go to Top of Page

jsmith8858
Dr. Cross Join

USA
7423 Posts

Posted - 10/03/2006 :  21:19:39  Show Profile  Visit jsmith8858's Homepage  Reply with Quote
Then maybe it is time to re-design it. The time needed to fix what is there and then rewrite the exiting sql from scratch is going to be far less than writing or maintaining just a few basic sql statements with the current design.

You said you would speak with the guy who did design it -- did you do that yet? (be diplomatic, of course!)

- Jeff
Go to Top of Page
Page: of 2 Previous Topic Topic Next Topic  
Previous Page
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.14 seconds. Powered By: Snitz Forums 2000