Earlier I had asked the question: Where (or how) should I define the schema in a select statement when using PostgreSQL? The answer I accepted was to modify the search_path for the connecting user so that the schema need not be specified in the SQL. However, now I wonder if I should always specify the schema in SQL rather than allow th...
Hi, Ideally I want to do this: UPDATE TOP (10) messages SET status=10 WHERE status=0 ORDER BY priority DESC; In English: I want to get the top 10 available (status=0) messages from the DB and lock them (status=10). A message with a higher priority should be gotten first. unfortunately MS SQL doesn't allow an order by clause in the up...
Assume there is a table named "myTable" with three columns: {**ID**(PK, int, not null), **X**(PK, int, not null), **Name**(nvarchar(256), not null)}. Let {4, 1, аккаунт} be a record on the table. select * from myTable as t where t.ID=4 AND t.X = 1 AND ( t.Name = N'аккаунт' ) select * from myTable as t ...
Which do you think is faster in a php script: $query = "SELECT... FROM ... ORDER BY first_val"; or while($row = odbc_fetch_array($result)) $arrayname[] = array( "first_key" => $row['first_val'], "second_key" => $row['second_val'], etc... ); sort($array...
I have some data in string format that represents binary data (e.g. '0x0002'). Is there some function or trick where I can convert these from literal strings to binary? That is, I want '0x0002' to become 0x0002, and SELECT CAST('0x0002' AS BINARY(20)) obviously won't do that. I did come up with some painfully slow process that involve...
I´ve got this in a INSERT statment to MSSQL 2008 System.Data.SqlClient.SqlException: The conversion of a datetime2 data type to a datetime data type resulted in an out-of-range value. ...
What is the best practice for right-justifying a numeric in TSQL? I have to format a fixed length extract file and need to have the Numeric fields right justified. (I am using SQL Server 2005) I found this, which seems pretty straight forward. right(' '+convert(varchar(20),a.num),12) Here is the full Select statement sel...
Using Access 2007, I have an update that I am trying to build a script or macro or even just a useful chunk of SQL that I can keep and use whenever I want to run the update... ...I have figured out how to this with an update query, but, I don't want to have to spend an hour or so everytime I want to update... Basically, I am cross walk...
I have 2 tables (srcTable1 & destTable) that have identical schemas. I am trying to copy all rows from srcTable to destTable and ignore the duplicates. I thought I could just add a WHERE clause with a subquery that would give me only the rows that aren't duplicates. However, it doesn't seem to work. I don't get any rows inserted or selec...
Is it possible to call (the equivalent of) aspnet_regsql.exe from managed code? My idea is to create a class to automagically build the database for an installed web app, which uses the ASP.NET Membership provider. I could probably call aspnet_regsql.exe direct from code, but I came across the RegSql class(http://msdn.microsoft.com/en-...
Hi All, I have Java JDBC application running against an Oracle 10g Database. I set up a PreparedStatement to execute a query, and then call ps.executeQuery() to run it. Occasionally the query takes a long time, and I need to kill it. I have another thread access that PreparedStatement object, and call cancel() on it. My question is,...
I'm designing a database schema, and I'm wondering what criteria I should use for deciding whether each column should be nullable or not. Should I only mark as NOT NULL only those columns that absolutely must be filled out for a row to make any sense at all to my application? Or should I mark all columns that I intend to never be null...
I was experiencing a hard time creating FK relationships in my relational SQL database and after a brief discussion at work, we realized that we have nullable columns which were most likely contributing to the problem. I have always viewed NULL as meaning unassigned, not specified, blank, etc. and have really never seen a problem with t...
I would like to write an SQL query that searches for a keyword in a text field, but only if it is a "whole word match" (e.g. when I search for "rid", it should not match "arid", but it should match "a rid". I am using MySQL. Fortunately, performance is not critical in this application, and the database size and string size are both com...
(EDIT: Made it a community wiki as it is more suited to a collaborative format) There are a plethora of ways to access SQL Server and other databases from .Net. All have their pros and cons and it will never be a simple question of which is "best" - the answer will always be "it depends". However, I am looking for a comparison at a...
Are User Defined Data Types in MS SQL Server something that a intermediate SQL user should know and use? What are pros and cons of using UDTs? ...
I have a string of email recipients in the format like this: DECLARE @recipients VARCHAR(MAX); .... PRINT @recipients; /* the result [email protected];[email protected];[email protected];... */ "SELECT DISTIECT ..." is a simple and powerful SQL statement, but it works against a table. Is there a simple way to select distinct r...
I've got a table and want to outer-join another table, getting only the first row (the one with lowest nr) of the second table using Oracle 10g. Edit: nr is unique within an id Table x Table y id id nr code 1 1 1 B 2 1 2 A 3 2 2 A Expected result: id nr code 1 1 B ...
Hi friends, It's possible to find the number of rows in a table: select count(*) from tablename Is it possible to find the number of columns in a table? Thanks in Advance. Praveen J ...
Are there rules of thumb for developers when to use join instead of subquery or are they the same. ...