sql

UNIQUE argument for INDEX creation - what's for?

Why does INDEX creation statement have UNIQUE argument? As I understand, the non-clustered index contains a bookmark, a pointer to a row, which should be unique to distinguish even non-unique rows, so insuring non-clustered index to be unique ? Correct? So, do I understand that no-unique index can be only on clustered table? since ...

Converting varchar to int using phpmyadmin

ok.. Im a complete noob at SQL and having trouble... Im trying to convert a table from varchar to int.. The reason for this is because Im trying to order the numbers on my website by the highest first.. Thats not the problem but because its a varible its odering them wrong. When I try changing the table in PhpMyAdmin to an INT I get thi...

Postgresql function returns composite - how do I access composite values as separate columns?

I have a Postgresql function which returns a composite type defined as (location TEXT, id INT). When I run "SELECT myfunc()", My output is a single column of type text, formatted as: ("locationdata", myid) This is pretty awful. Is there a way to select my composite so that I get 2 columns back - a TEXT column, and an INT column? ...

Selecting all rows whos foreign key points to a specific primary key?

I have two tables. One is a 'Users' table. Each row (user) in the table has a unique id (primary key). The second table is a 'Tasks' table. Each row in the Tasks table has a foreign key which points to the user (in the Users table) that owns that task. Using SQL Express 2008, what query must I use to obtain a list of all tasks assigned...

group by problem

SQL group by problem  I have a SQL group by problem. My table has the following form. Cust_id.  Price_id     Price. ---------------------------- 1.          556.        5000. ----------------------------- 2.          654.        600. 2.          432.         487. 2.          546.         500. --------------------------- 3. ...

is there any Benchmarking tool for available for testing SQL server CE ?

HI , is there any Benchmarking tool available for testing SQL server CE ? ...

SQL tree traversal

I am not totally sure I am naming this right, but please bear with me. I am wondering if is possible to do something like this in SQL(MySQL specifically): Let's say we have tree-like data that is persisted in the database in the following table: mysql> desc data_table; +------------------------+---------------------+------+-----+--...

Help with MySQL query that is based on the number of clicks on an item

I have a table setup like this (simplified for example): user_id item_id click_dt Each item_id has many user_id's. Basically it's storing the clicks on items, associated to a user_id. I want to query through this and list only the latest user_id for each item_id, based on click_dt. So if there are 5 clicks for item_id 55, the last ...

Collation Conflict

i always got the message "Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "Latin1_General_CI_AI" in the equal to operation." when i run the script that came from MSSQL 2005 server. btw i used MSSQL 2008. here is the script USE [database1] GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO --exec rsp_S...

SQL: Select first row in each a GROUP BY group?

As the title suggests, I'd like to select the first row of each set of rows grouped with a GROUP BY. Specifically, if I've got a "purchases" table that looks like this: > SELECT * FROM purchases: id | customer | total 1 | Joe | 5 2 | Sally | 3 3 | Joe | 2 4 | Sally | 1 I'd like to query for "the id of the largest...

mysql left join and sum group by

Hi i using this mysql code to generate this data below: code: SELECT L.LEAVETYPE, LA.IDLEAVETYPE, LA.IDLEAVETABLE, SUM( LA.NOOFDAYS ) FROM LEAVETYPE AS L LEFT JOIN LEAVEAPPLICATION AS LA ON LA.IDLEAVETYPE = L.IDLEAVETYPETABLE GROUP BY L.LEAVETYPE When i add the Where condition it will only show the LeaveType with value. How can i ...

compression, defragmentation, reclaiming space, shrinkdatabase vs. shrinkfile

[1] states: "When data is deleted from a heap, the data on the page is not compressed (reclaimed). And should all of the rows of a heap page are deleted, often the entire page cannot be reclaimed" "The ALTER INDEX rebuild and reorganize options cannot be used to defragment and reclaim space in a heap (but they can used to defragmen...

C# Exception when remotely connection to SQL Server 2005 instance

Hi guys, Trying to connect to a SQL Server 2005 instance remotely but getting the following exception: A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow r...

What's wrong with this T-SQL select statement?

Hi, I'm reading an article from this website, but when i run the code that article provided, i get the error: Msg 102, Level 15, State 1, Line 16 Incorrect syntax near '.'. I double checked the syntax and can't find the cause of this error. The code is as follows, formatted by SQL Prompt. Anybody can help me with it? Great thanks. ...

Core Data: 3 table join?

I know Core Data is not a database and there are many differences. Is this one? In a database, I would commonly have the following A ->> B ->> C "A" has many "B" which has many "C" The query, "Give me all A's which have c.attr = 'X' is easily written like: select * from a, b, c where a.id = b.aid and b.id = c.bid and c.attr = 'X' ...

what is index and can non-clustered index be non-unique?

Subquestion to my question [1]: All definitions of (MS SQL Server) index (that I could find) are ambiguous and all explanations, based on it, narrate something using undefined or ambiguously defined terms. What is the definition of index? For ex., the most common definition of index from wiki (http://en.wikipedia.org/wiki/Index_(d...

kill all user connections in SQL Azure

B"H I need to alter a table and the Sql Azure just spins its wheels. I think its because there is a connection somewhere that is accessing that table. How can I see who is accessing my tables and how can I terminate their connections. I am looking for something similar to ALTER DATABASE xxx SET SINGLE_USER WITH ROLLBACK IMMEDIATE GO...

mysql select conditional on existence of future records

I have the following situation. I have a database of transactions about multiple contracts. I am looking for the first transaction where alpha < alphaBound and the contract has a future, meaning there are records after the date for the same contract ID. To do this, I have been doing: select transactionID, date, alpha, ...

removing root nodes from xmls in t-sql and merging them

I'm actually planning on the best way to merge xml data from within the SQL database. I have a table that contains an xml datatype. Inside it are xml's with similar schemas. I wanted to merge, let's say, two xmls and they both contain a <Custom></Custom> root on both ends. What is the best way to do this? First xml: <Custom> <Dat...

NULL elements in FOR XML Clause / Alternative to XSINIL

I have some legacy code similar to: ... '<field1>' + case when field1 is null then '' else cast( field1 as varchar ) end + '</field1>' + ... Which generates the following XML for empty elements: .... <field1></field1> ... And I'm replacing the query with FOR XML: SELECT field1, ... FOR XML RAW, ELEMENTS Now, ...