Programming SQL

is the Top or Order by done first?
I have a SP that I am rewriting and has something like: ************************************* Set ROWCOUNT = 100 SELECT * FROM ( SELECT TOP 100 PERCENT * FROM #Temp AS T1 ) AS Q1 WHERE Name <> 'offset_name' ************************************** Not sure why he is doing this. In the derived t... 23 Apr 2010 03:28

first (and last)
Hi there In a project being transferred from MS Access to MS SQL, I'd like an easy replace of First, such as: select a, first(b), group by a Normally I can not get B, but I just need one of them (any) (No I do not know anything about the data yet) Sonnich ... 22 Apr 2010 13:29

Recursive (Instead Of) Trigger
I've got a recursive Instead Of Delete trigger which I'm forced to use due to SQL 2005/2008 prohibiting cycles in cascade operations. I'm getting a (surprising) RI error when I single step through the trigger body and reach the point of the recursive Delete operation. Even though I do a "Step Into" I immediatel... 22 Apr 2010 18:42

DBCC MemoryStatus memory_node_id = ...
Ok, si if you run DBCC memorystatus on SQL Server 2005 or 2008 with NUMA enabled or not, you will get different results returned based on the NUMA architecture. Can someone tell me why on certain systems I get memory_node_id = 32 and memory_node_id = 64? When I look in sys.dm_os_memory_clerks as MS suggested to... 22 Apr 2010 10:21

Need help (again) with hierarhyID
Hi, For some reason I've never gotten used to hierarchyid way of doing things. Here's what I want to do: I want to specify a node and want my select statement to return that node's child nodes that are directly under that node. For example: Root Level 1 Level 1.1 Level 1.2 Level 1.2.1... 21 Apr 2010 23:59

How to substitute for a non-existing column in a joined table
Hi, Is there a simpler way than a UNION to return a default value of a joined table for which a corresponding row does not exist? The following example (not a working one, of course) illustrates what I'm after. I'd like to return 'N/A' as c2name if there is no matching row in t2 SELECT t1.c1 (CASE t2.t... 22 Apr 2010 15:34

OPENQUERY
I want to update data in My SQL using linked servers in SQL Server 2000. I want to know the syntax I have to write I thought about something like that but it doesn't work... update openquery(MysrvName, 'select * from tbl_data'), MyTable set MyNote = MyTable.NewNote + MyNote where MyID = MyTable.MyID ... 22 Apr 2010 18:42

Truncation at sixth decimal
Hi all I can't understand why SQL Server numeric data truncate at the sixth decimal place, when the decimal places should be much more. Here is the example. I have these 3 tables with test data: create table Consumi1 (Combustibile varchar(10), Valore decimal(30,19)) go create table Consumi2 (Combustibile varc... 22 Apr 2010 06:11

Need advice with ISNULL
Hi, ISNULL allows me to either return the data in a field or some specified value if the field is NULL. What I want to do is slightly different. I want to return 1 or a 0 based on whether or not there's data in the field. In other words, I don't want to return the data but a value of 1 if there is data. If ... 22 Apr 2010 18:42

Column not getting updated
Hi Guy, Trigger below is not updating ODS_INST_ID column as it should when new row is inserted. This behavior happens when new row is a copy of existing row i.e. source row already has data in ODS_INST_ID. So for example if source row has ODS_INST_ID = 1, copied row should have ODS_INST_ID = 2 but I see ODS_INST... 21 Apr 2010 12:35