|
Hey there,
I'm having a little issue with a query. Basically I'm trying to do some paging. There's many ways to do it, I know, but what I came up with was this:
SELECT TOP 10 columnA, columnB
FROM table
WHERE columnA not in (
SELECT TOP 21190 columnA
FROM table
ORDER BY columnC DESC)
ORDER BY columnC DESC
Some notes about it:
columnA is the key
columnD, columnC and columnA are index1
columnD and columnA are index2
columnC is index3.
In SELECT TOP 21190, that number is pageNumber * pageSize
Now... it seems that the very last 'ORDER BY columnC DESC'is the problem. Like it is the query takes around 1 minute to run. If I take that line out, the query runs in less than a second. Or, if I change the ORDER BY to be done by columnA, it also runs in less than a second.
Any ideas?
|
|
|
|
|
You need to do an order ASC in one of those queries for this to work. Then you don't need the 'not in', which is your problem. Select in desc order, ending with the first record you want. Then select in your outer query the number you want, in ASC order.
Christian Graus
Please read this if you don't understand the answer I've given you
"also I don't think "TranslateOneToTwoBillion OneHundredAndFortySevenMillion FourHundredAndEightyThreeThousand SixHundredAndFortySeven()" is a very good choice for a function name" - SpacixOne ( offering help to someone who really needed it ) ( spaces added for the benefit of people running at < 1280x1024 )
|
|
|
|
|
Hi, I want to debug a stored proc on our server but the database contains close to 30k stored procs. So when I press the expand button on the server explorer pane I have to wait for at least 30 minutes before VS unfreezes... This is getting quite annoying...
Out of these 30k stored proces around 95% are useless to me so I would like to filter them by their names.
All the ones I don't want start with 'zDP_'.
Do you know if there is a way to apply a filter before pressing the +?
Alternatively, is there another tool I could use to debug my stored procs?
We are running SQL Server 2005.
Thanks
Fred
modified on Tuesday, May 27, 2008 12:30 PM
|
|
|
|
|
hi everybody
I created a website with SQL express database in server explorer, everything is going just fine and now I want to upload the site and the database to the internet so that my customer can test it.
The server on which I want to upload my site to requires me to upload a .mdf file to the server, I searched my computer for the mdf file but I couldn't find it.
Any help please?
Thanks
And ever has it been that love knows not its own depth until the hour of separation
|
|
|
|
|
we have a database publishing wizard to do so search google and download it
If You win You need not Explain............
But If You Loose You Should not be there to Explain......
|
|
|
|
|
hi friends
i have a server that installed o it a sql server 2000
i want to create a connection to the database on this sql server.
my server name is (local);
when i try to login into my database from query analyzer:
i type "(local)" instead of server name and username and password of a auser that i created for login to database.
its worked correctly.
but when i try to do this action from my home pc, an error appear that contanis below message:
"Sqlserver does not exist or access denied."
i enter my server ip instead of "(local)" in my query analyzer from my home pc.
now how can i login to my database from my home pc????
what do i enter server name ? an ip address or else???
please help me
nobody help you...
you have to help you yourself
and this is success way.
|
|
|
|
|
(local) only works if you are logged in locally to the machine where the instance of SQL Server is installed. You will probably need to use the IP address of the machine from home, unless you have some way to resolve the hostname (like if you are logged in thru a VPN). Then you can type in your username and password into QueryAnalyzer just like you would if you were on the machine locally.
|
|
|
|
|
Video:
http://www.microsoft.com/hk/webcast/default.aspx?sid=211[^]
SQL server 2008 will help enable information technology to drive business intelligence throughout the organization. All your saved data could be easy to analyze and present by reports and charts for you to study the trends and make the right business decision.
|
|
|
|
|
Thanks for sharing!
The latest version looks good; I am considering upgrade to this 2008 version.
|
|
|
|
|
How can i pass the parameters to triggers?
|
|
|
|
|
You don't. The trigger has all the data from the insert/update/delete in special tables called inserted and deleted. Google triggers and yuo will find plenty of examples.
Hope this helps
Bob
Ashfield Consultants Ltd
|
|
|
|
|
hi..
can anybody tell me using c#.net to create advanced search like all words, anywords, and exact word, if i have to search in 5-6 tables, means if i give "red" in search text box then how can i search red in 5-6 tables and i dont know which column contains the word red.
|
|
|
|
|
Check this[^]
I Love T-SQL
"Don't torture yourself,let the life to do it for you."
|
|
|
|
|
Actually , i am making a car website like cars.com, and i have seen in cars.com they allow search on keywords, I too want to implement that feature but how can i, bcoz i have normalized the features of car into 5-6 tables and i want to search it in that 5-6 tables.
|
|
|
|
|
Hi friends..
1. I want to know abot SQL SERVER Security(Logins, Server Roles, Credentials) under particular server.
2. And about security(users, Roles, Schemas) under particular Database.
Plz let me know detailed explanation with example.
Regards
Willington D
D.Willington
|
|
|
|
|
Try BOL or Google, its too big a subject to answer in a forum - there are loads of articles and advice out there.
Bob
Ashfield Consultants Ltd
|
|
|
|
|
Hi
I was searched with google for sql security..., but i didn't get any usefull topics.., they were confused me. Thats y i posted this query.., If u have any articals plz.. let me know the URL.
Regards,
Willington D
D.Willington
|
|
|
|
|
If you cannot find a simple enough article to explain SQL security then perhaps you would be better off employing someone who does understand it to set up the security on your system. The saying 'a little knowledge is a dangerous thing' is very applicable to security.
Bob
Ashfield Consultants Ltd
|
|
|
|
|
To big a topic to give "detailed explanation with example", and well - that would also mean I'd do your work for you.
So I'll simply say that you should read the SQL Server documentation, some books about SQL Server administration (SQL Server 2005 Administrators Companion provides a chapter on it) and "online books" from MSDN.
|
|
|
|
|
Hello Friends
I want Sql Server 2005 to work in a LAN
Please let me know how to configure it
Thanks
If You win You need not Explain............
But If You Loose You Should not be there to Explain......
|
|
|
|
|
Could you be more specific about what you're struggling with?
If your network is up and running and your software firewalls are on both Server and Clients are set up correctly, you shouldn't be having any trouble.
It's really straight forward in most cases.
|
|
|
|
|
Hi,
am trying to upsize certain tables in MS Database to SQL.I tried the upsizing wizard,but for certain tables its giving me errors like "unable to drop table".
Is their any stored procedures which will help us to upsize the ables to SQL?
|
|
|
|
|
Create the tables in SQL then import the data from the MDB.
If you have multiple tables with constraints, either import the data in the correct order, or temporarili remove the constraints.
|
|
|
|
|
Im using sql2005
Im trying to export my DB to another server
The problem is with dependencies
The Generate Scripts option really sucks, i cant beleive this is the best that a bunch of guys at microsoft can come up with
It scripts SP1, before tables referenced by SP1, and it only gets worst when trying to export the whole DB
What are you guys using to export your DB to a remote server??
Ive tried scriptio already but it doesnt export data and doesnt care at all about dependencies
Ive also tryed microsoft publishing wizard, but its as bad as the Generate Scripts
Please advice, i cant be wasting days just to export my DB
When i was using sql2000, the scripts where generated with only one error in a view referencing a view that hadnt been created yet, but now with sql2005, man...I cant beleive it, errors everywhere
Thanks in advance
Alexei Rodriguez
|
|
|
|
|
AlexeiXX3 wrote: The Generate Scripts option really sucks, i cant beleive this is the best that a bunch of guys at microsoft can come up with
No it isn't, so stop bitching about it!
Sorry - I have this thing today where I see a lot of people bitching about Microsoft when the problem lies with their own ignorance.
AlexeiXX3 wrote: What are you guys using to export your DB to a remote server??
Personally, I tend to backup the database, then restore it on the other server. But if you want a scripting option then there is a tool downloadable from the Microsoft website: Microsoft SQL Server Database Publishing Wizard 1.1[^]
AlexeiXX3 wrote: When i was using sql2000, the scripts where generated with only one error in a view referencing a view that hadnt been created yet, but now with sql2005, man...I cant beleive it, errors everywhere
Stop bitching about it! It is really irritating. I almost didn't help you because I was getting so damned pissed off with you. Yes, Microsoft has faults, but this is not one of them. The tool I linked to is really rather good and a heck of a lot better than what you got in SQL Server 2000.
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
