I have created script to find selectivity of each columns for every tables. In those some tables with less than 100 rows but selectivity of column is more than 50%. where Selectivity = Distinct Values / Total Number Rows So, are those column are eligible for index? Or, can you tell, how much minimum rows require for eligibility to create...
If I want to save any changes in a table, previously saved in SQL Server Management Studio (no data in table present) I get an error message: "Saving changes is not permitted. The changes you have made require the following tables to be dropped and re-created. You have either made changes to a table that can't be re-created or enabled t...
Hi, I have a view that basically just returns all records from a table, and adds a column 'isodate' which is supposed to be the date in ISO-Format. CREATE VIEW [dbo].[v_bedarfe] AS SELECT *,convert(varchar(16),datum,20) As isodat FROM bedarfe GO The "datum"-field is smalldatetime. The results of a query on isodat were...'surprisi...
I encountered an interesting SQL table (column names listed): From_TableName | From_Id | To_TableName | To_Id From what I understood, this table is used for all table relationships in the database. There's not a lot of data (more than 10000 lines in the previously mentioned table) in the database, so, I guess, performance was not that i...
I have a form that selects a bunch of rows from a table in my MSSQL server, now, this form is used to update data, which it does correctly, after I update the data using the form however, it redirects back to a page with a datagrid of all the rows in that table, and allows me to select another row to update, if I select the row I just up...
Hello, I am quite new to MySQL and it is great in reducing data through queries. However, it seems to be hard to get both records, if you want records containing duplicates. The table looks like: ID Value more columns.. 1 4 1 4 1 5 2 4 3 5 3 5 4 4 4 5 I want both(!) records with duplicate values, li...
I want a field in a table of SQL Server 2008 to contain a preset number of string values for user to be able then to select one value from a dropdown listbox in my WPF front-end app. Is it possible only through assigning a numeric int type in a SQL Server table field and then doing all the stuff through C# front-end coding, or are there...
I'm working on a multi-tenant rails application using PostgreSQL schemas for different clients. Rails migrations don't work with multiple schemas out of the box, so I made the following rake task to migrate all schemas and it seems to work. My question is if others have implemented better and more elegant solutions. I would also be reall...
I basically have two tables: A(id int, name varchar(10), info varchar(10)) B(id int, item varchar(10)) A 1 A Hello 2 B World 3 C Foo B 1 apple 1 orange 1 hammer 2 glass 2 cup Using the following query... mysql> SELECT a.id, a.name, a.info, group_concat(b.item SEPARATOR ' ') FROM a LEFT OUTER JOIN b ON (a.id=b.id) ...
I have two tables, omc_categories and omc_products. I want to pull out categories name where omc_proudcts.category_id is equal to omc_categories.id. I created the following sql but I am not sure which is right one. SELECT C.Name AS CatName FROM omc_categories AS C LEFT JOIN omc_products AS P ON C.id = P.category_id WHERE...
Hi guys, how can I execute such query in django: SELECT * FROM keywords_keyword WHERE id not in (SELECT keyword_id FROM sites_pagekeyword) In the latest SVN release we can use: keywords = Keyword.objects.raw('SELECT * FROM keywords_keyword WHERE id not in (SELECT keyword_id FROM sites_pagekeyword)') But RawQuerySet doesn't support ...
I have a normalized sql server 2005 database. An example of a table that is something like this: Location LocationID, CustomerID, OrderID This is abbreviated. However, the normal query syntax simply uses joins to show the location as city state zip and the name of the customer and so on. I would like to implement full text search on...
I'm trying to improve the performance on a query that is running very slowly. After going through the Actual Execution plan i found that a Clustered index seek was taking up 82%. Is there any way for me to improve the performance on an Index Seek? Below is an image of the problem Index Seek from the execution plan as well as the index...
I've some prob with the function TABLE in ORACLE. SET SERVEROUTPUT ON SIZE 100000; DECLARE int_position NUMBER(20); TYPE T_REC_EMP IS RECORD ( nameFile VARCHAR2(200) ); R_EMP T_REC_EMP ; -- variable enregistrement de type T_REC_EMP TYPE TAB_T_REC_EMP IS TABLE OF T_REC_EMP index by binary_integer ; t_rec TAB_T_REC_EMP ; -- var...
I have two tables both of which have columns StartDate and EndDate. I'm trying to return a single resultset that contains all date ranges from one table (TableA), and all complement date ranges from the other one (TableB). CREATE TABLE [dbo].[TableA]( [ID] [int] NOT NULL, [StartDate] [datetime] NOT NULL, [EndDate] [datetime...
I have a table with a TestNumber column with four possibilites (ex: 1 - 4), a TestDate column, and other data. I want to make a query that will return a user defined year and test numbers. For example, I want to return test Numbers 1 and 2 that have a date with year 2008. Edit: I basically want to combine these two statements into one...
Dears, I need your help to optimize the query below. Let us assume we have a web application for articles. The software use two table;one is the article table and the second one is the users table. The article table hold the date when the article is created,the id,the body,the title & the section. Let us assume that we have one section ...
I have a table in Oracle 10 that is defined like this: LOCATION HOUR STATUS -------------------------------------- 10 12/10/09 5:00PM 1 10 12/10/09 6:00PM 1 10 12/10/09 7:00PM 2 10 12/10/09 8:00PM 1 10 12/10/09 9:00PM 3 10 12/10/09 10:00PM 3 10 ...
I need to rewrite the query below so that it is as efficient as possible. Any suggestions on how to increase the performance of this query? I plan on setting columns t2_1 and t2_2 to not nullable and assigning a default value, I'm mainly curious as to if/how I can modify the join so that I can move the "WHERE ... IN ... " sections ther...
Tried to get a bit more info on this, but figured I'd just ask.... I attempted to perform a table truncate on Oracle 10g with the drop storage, however the results aren't entirely what I was expecting. Basically, the table in question had over 30 million records and over 3Gb allocated for it, but somehow the space allocated for it sti...