I need some help with a SQL query... I have a SQL table that holds in a column details of a form that has been submitted. I need to get a part of the text that is stored in that column and put it into a different column on the same row. The bit of text that I need to copy is always in the same position in the column. Any help would b...
In standard SQL, is there a way to say: select mubmle as x from mumblemmble And get more than one line of results, like this x _ 1 2 3 without creating temporary tables? I can do it in SQL Server using row_count() if I know some table that has enough rows, like: select row_number() over (order by x.SomeColumn) from (sele...
Hi, What is the best practice for SQL testing (stored procedures, views, queries, triggers...) excluding automated testing? Most of the time a query works or returns thousands of rows, so it is a bit difficult to check them. I want to know how you usually do the testing of a SQL query. Can you point me to a good (online) reference? ...
The pragmatists have won the argument of surrogate vs. natural primary keys in favor of surrogate keys*. In my own work I always use SQL Server identity columns without a second thought. But it occurs to me that, for a table to be in 1st normal form, I should be able to identify a natural key and enforce it with a unique constraint. I ca...
I need to add an index to a table, and I want to recompile only/all the stored procedures that make reference to this table. Is there any quick and easy way? EDIT: from SQL Server 2005 Books Online, Recompiling Stored Procedures: As a database is changed by such actions as adding indexes or changing data in indexed columns, the origi...
Hi Friends, Can anybody please, let me know if there is a way to find out the exact time, when a table was updated.I mean when a column was updated. Please, help. Thanks, ...
I would like to create a sql query that reports the percentage of results in a particular range. for instance 20% of the values between 10 to 20 40% of the values between 20 to 32.5 Server - MSSQL ...
How would you write a SQL query to get the size of blob in a more human readable form? The example would be something like a large word document is being stored in a blob on a table. I would want to execute something like: select fnc_getReadableSize(documents.doc) from documents where id = ? output: 23.4 MB ...
Hi, I was wondering if there is a way in SQL Server 2000 to create all dates given a start and end date as a result. I know I can achieve this with T-SQL looping. I am looking for a non looping solution. Also in 2005 you can use the recursive with clause. The solution can also be using a T table that has numbers in it to join with the ta...
I've read that it's unwise to use ToUpper and ToLower to perform case-insensitive string comparisons, but I see no alternative when it comes to LINQ-to-SQL. The ignoreCase and CompareOptions arguments of String.Compare are ignored by LINQ-to-SQL (if you're using a case-sensitive database, you get a case-sensitive comparison even if you a...
I need to speed up a query. Is an index table what I'm looking for? If so, how do I make one? Do I have to update it each insert? Here are the table schemas: --table1-- | --tableA-- | --table2-- id | id | id attrib1 | t1id | attrib1 attrib2 | t2id | attrib2 ...
I'm guessing Visual Studio is bugging out on me. I changed the stored procedure the a TableAdapter query uses and now the users keep getting the following error. Procedure or function 'usp_GetEventNotificationSubscribers' expects parameter '@FacilityCode', which was not supplied. Nowhere in the application is 'usp_GetEventNotificationS...
What is the simplest way to delete records with duplicate name in a table? The answers I came across are very confusing. Related: Removing duplicate records from table ...
Disclaimer: This is an "asked-and-answered question" posted in accordance with the FAQ statement that it's "perfectly fine to ask and answer your own programming question". Its purpose is to encourage members of the SQL Anywhere programming community to use StackOverflow by seeding the "sqlanywhere" tag with some real-world content. Edit...
For example DbVisualizer can be used to connect to a DB and create nice diagram out of existing tables and their relations. But in this specific case I do not have a live database but I have bunch of create table and alter statements. Is there any tool to generate similar diagrams out of SQL DDL? ...
is there a way to select one per every 5 records in sql server with linq? ...
I know Django 1.1 has some new aggregation methods. However I couldn't figure out equivalent of the following query: SELECT player_type, COUNT(*) FROM players GROUP BY player_type; Is it possible with Django 1.1's Model Query API or should I just use plain SQL? ...
How do I create a foreign key from table tGeoAnswers column 'locationId' to table tLocations column 'id'? ALTER TABLE tGeoAnswers ADD FK_Answer_Location FOREIGN KEY (locationId) REFERENCES tLocations(id) I am trying this code that I found but I get the following error: The definition for colu...
I need to write a query in t-sql or linq that matches db records that contain the most of the user input words. exmaple: nvarchar field in db: "The quick brown fox jumps over the lazy dog" User input: "brown cow" The program would match that record because it has the word brown. let me know if i need to provide more examples. ...
I've got a table structure that can be summarized as follows: pagegroup * pagegroupid * name has 3600 rows page * pageid * pagegroupid * data references pagegroup; has 10000 rows; can have anything between 1-700 rows per pagegroup; the data column is of type mediumtext and the column contains 100k - 200kbytes data per row userdata...