Is a table intrinsically sorted by it's primary key? If I have a table with the primary key on a BigInt identity column can I trust that queries will always return the data sorted by the key or do I explicitly need to add the "ORDER BY". The performance difference is significant. ...
Similar to this question only the other way of flow. http://stackoverflow.com/questions/416881/insert-picture-into-sql-server-2005-image-field-using-only-sql I need to be able to save a image field out into the file system and be able to name the file with only using SQL. I don't want to use TEXTCOPY either because I need to use the c...
I have a database schema in SQL 2005 that I want to copy to a SQL 2000 server. It contains tables that have multiple owners. When I try to create a DTS package to transfer the schema I get conflicts because some of the tables have the same name (but different owners). It looks like it is trying to make all the tables to be owned by dbo. ...
I believe the answer is no. And am looking for a counter example to show that order of output is not guaranteed, absent an order by clause. consider: create table #order (orderId int primary key clustered , customerId int not null -- references customer(customerId) , orderDateTIme datetime not null) insert into #order values ...
in our sql server box(X64 machine with 8 core with 16G Ram), we found the performance is really bad after we a bunch of data been generated, some time we even cannot RDP to this box, there have several err msg on SQL error log as following: *2009-06-26 12:11:09.92 spid63 Error: 14151, Severity: 18, State: 1. 2009-06-26 12:11:09.92...
Say I have a date 01/01/2009, I want to find out what day it was e.g. Monday, Tuesday, etc... Is there a built in function for this in Sql2005/2008? Or do I need to use an auxiliary table? ...
Will Try-Catch capture all errors that @@ERROR can? In the following code fragment, is it worthwhile to check for @@ERROR? Will RETURN 1111 ever occur? SET XACT_ABORT ON BEGIN TRANSACTION BEGIN TRY --do sql command here <<<<<<<<<<< SELECT @Error=@@ERROR IF @Error!=0 BEGIN IF XACT_STATE()!=0 BEGIN ...
Hi all, I'm trying to show intervals of working hours/days it's should look like this: I have table where I'm storing day number, open time and closing time for each day Then I created query=> var groups = from s in this.OpenTimes orderby s.Day group s by new { s.Till, s.Start } into gr select new { Time = gr.Key.Start + "-" + g...
Here's what I'd like to do. For each table in linkedserver.database whose tablename is like 'text%' (inside loop) A. If current_table exists locally, drop it B. select * into table.name (local) from linkedserver.tablename (copy schema + data) C. Possibly check for errors and Print some text about it? Next Any idea if this script ...
Hello, I'm using MS SQL Server 2005. What's the best schema for a Wiki-like system? where users edit/revise a submission and the system keeps track of these submissions. Lets say we're doing a simple wiki-based system. Will keep track of each revision plus views and latest activity of each revision. In other screens, the system will l...
It seems to be fairly accepted that including the schema owner in the query increases db performance, e.g.: SELECT x FROM [dbo].Foo vs SELECT x FROM Foo This is supposed to save a lookup, because SQL Server will otherwise look for a Foo table belonging to the user in the connection context. Today I was told that always including the da...
Normaly i would do a delete * from XXX but on this table thats very slow, it normaly has about 500k to 1m rows in it ( one is a varbinary(MAX) if that mathers ). Basicly im wondering if there is a quick way to emty the table of all content, its actualy quicker to drop and recreate it then to delete the content via the delete sql statem...
I use SQL Server 2005. I have a simple logging table that my web application uses to track user activities and urls visited. The table design is very simple ID (identity value), LogDate (datetime), Activity (nvarchar(200)), Url (nvarchar(1000)) We mainly do Inserts into this table. Once in a while, our perform some queries against ...
Can anybody know how to remove all the indexes from database ? ...
How to find out column with NULL values allowed in the insert in whole database ? ...
Hello erveyone, I am new to SQL Server 2008 fill factor, as mentioned here in SQL Server 2008 BOL, http://msdn.microsoft.com/en-us/library/ms177459.aspx My 2 confusions, Whether fill factor applies to index page? Or applies to both index and data page? At the beginning, seems fill factor applies only to index page -- "The fill-facto...
Hello everyone, I am using SQL Server 2008 Enterprise version. I want to know if I do not explicitly set any maintenance jobs, are there any default maintenance jobs SQL Server will perform (like backup? rebuild index? truncate transaction log?)? Where to find the current maintenance jobs? thanks in advance, George ...
When I run SQL Server 2005 Database Tuning Advisor, it gives a recommendation to create an index, but it will recommends to index a column which already has an index on it. Why does it give a recommendation to create the same index again? Here is my SQL: SELECT t.name AS 'affected_table' , 'Create NonClustered Index IX_' + t.name +...
In Visio 2003 Architect version I can generate a SQL Server Database on the fly from UML ER diagram. But there is no such a way by using Visio 2007. My question is: Any other good and latest tools can support ER-> DB or DB->ER very well for SQL2005/2008? ...
Hello everyone, For the same ADO.Net statement, I want to make sure my understanding of isolation level and lock is correct. In default SQL Server isolation level (read committed), after read each row, the row will unlocked; If I raise isolation level to repeatable read, the lock (on the whole table? or some other level lock?) will be...