In the code below I am using a recursive CTE(Common Table Expression) in SQL Server 2005 to try and find the top level parent of a basic hierarchical structure. The rule of this hierarchy is that every CustID has a ParentID and if the CustID has no parent then the ParentID = CustID and it is the highest level. DECLARE @LookupID int --O...
I'm working on a database for a small web app at my school using SQL Server 2005. I see a couple of schools of thought on the issue of varchar vs nvarchar: Use varchar unless you deal with a lot of internationalized data, then use nvarchar. Just use nvarchar for everything. I'm beginning to see the merits of view 2. I know that nva...
What would be the best way to design a threaded commenting system so that it doesn't hammer the database? ...
I have a column which is of type nvarchar(max). How do I find the length of the string (or the number of bytes) for the column for each row in the table? ...
I have three tables page, attachment, page-attachment i have data like this page ID NAME 1 first page 2 second page 3 third page 4 fourth page attachment ID NAME 1 foo.word 2 test.xsl 3 mm.ppt page-attachment ID PAGE-ID ATTACHMENT-ID 1 2 1 2 2 2 3 3 3 I woul...
In MS SQL Server, create my scripts to use customizable variables: declare @somevariable int select @somevariable = -1 insert into foo values ( @somevariable ) I'll then change the value of @somevariable at runtime, depending on the value that I want the particular situation. Since it's at the top of the script it's easy to see & reme...
It is generally accepted that the use of cursors in stored procedures should be avoided where possible (replaced with set based logic etc). If you take the cases where you need to iterate over some data, and can do in a read only manner, are fast forward (read only forward) cursor more or less inefficient than say while loops? From my in...
I'm setting up an web application with a FreeBSD PostgreSQL back-end. I'm looking for some database performance optimization tool/technique. Does someone know one? ...
Answers for multiple databases welcome! ...
What is the SQL query to select all of the MSSQL Server's logins? Thanks, more then one of you had the answer I was looking for SELECT * FROM syslogins ...
We've just started using LINQ to SQL at work for our DAL & we haven't really come up with a standard for out caching model. Previously we had being using a base 'DAL' class that implemented a cache manager property that all our DAL classes inherited from, but now we don't have that. I'm wondering if anyone has come up with a 'standard'...
First of all, I should point out that I'm not asking this question for any practical reason. I'm just asking out of sheer curiosity. Why is it that SQL distributions are so non-standard? I know there's an ANSI standard for SQL, but it just seems that developers of SQL databases go out of their way to find different ways to do things. ...
What tool(s) do you use to generate documentation for a database (preferably into a .chm help file)? ...
i have three tables tag, page, pagetag With the next data page ID NAME 1 page 1 2 page 2 3 page 3 4 page 4 tag ID NAME 1 tag 1 2 tag 2 3 tag 3 4 tag 4 pagetag ID PAGEID TAGID 1 2 1 2 2 3 3 3 4 4 1 1 5 1 2 6 1 3 I would like to get a str...
At the moment my code (PHP) has too many SQL queries in it. eg... // not a real example, but you get the idea... $results = $db->GetResults("SELECT * FROM sometable WHERE iUser=$userid"); if ($results) { // Do something } I am looking into using stored procedures to reduce this and make things a little more robust, but I have some...
Can you please point to alternative data storage tools and give good reasons to use them instead of good-old relational databases? Imo most applications rarely use full power of SQL, it would be interesting to see how to build a SQL-free application. ...
I am facing problem with an Oracle Query in a .net 2.0 based windows application. I am using "System.Data.OracleClient" to connect to oracle database. Name of database is "myDB". Below the the connection string I am using: *Data Source=(DESCRIPTION =(ADDRESS_LIST =(ADDRESS = (PROTOCOL = TCP) (HOST = 172.16.0.24)(PORT = 1522)))(CONNECT_...
Hi, If you create an Oracle dblink you cannot directly access LOB columns in the target tables. For instance, you create a dblink with: create database link TEST_LINK connect to TARGETUSER IDENTIFIED BY password using 'DATABASESID'; After this you can do stuff like: select column_a, column_b from data_user.sample_table@TEST_LINK ...
What is the difference between INNER JOIN and OUTER JOIN? ...
Feel free to edit the title if you know how to formulate the question better. (Tagging is a problem as well.) The problem may be too difficult in this general form, so let us consider a concrete example. You get a screenful of stackoverflow questions by requesting /questions ?sort=newest page. Next page link leads to /questions?page=2 &...