Structured Query Language (SQL). For questions about the language, not Microsoft SQL Server (tag sql-server).

learn more… | top users | synonyms (2)

0
votes
1answer
122 views

SSMS 2008 / 2014 showing different values for TempDB file sizes

Here's a fun one. Last week I reconfigured the initial file sizes in our tempdb database to 1GB each. We had an event a few weeks back that locked up our server due to a table with no indexes ...
0
votes
2answers
89 views

Querying MySQL, duration increases 10x fold after adding one more Join

I have a somewhat complicated query consisting of many joins on an issue tracker/project management database in MySQL. My issue is that my query was doing OK while I was in the process of building it ...
2
votes
1answer
411 views

Disk Crashed, NDF(Index) file lost

I have database with : -two filegroups (PRIMARY and INDEX) -three files .mdf(data), .ndf(index) and .ldf(log) every file is on different disk, yesterday disk where was NDF file crashed so I lost ...
0
votes
0answers
44 views

LIKE or CHARINDEX to check String Contains a Substring [duplicate]

To check a string contains a sub string or not we can use [LIKE] or [CHARINDEX]. I know that like uses indexing in case we have name%.(while %name% may not use index) .Also charindex does not use ...
0
votes
2answers
511 views

Script for listing tables having no primary key oracle 9i [closed]

I'm looking for a script to find tables within my database that have no primary key.
1
vote
0answers
2k views

Connection to SQL Server failed. A network-related or instance-specific error occurred while establishing a connection to SQL Server

I'm getting this error while trying to connect to sql server. Now this never happened before and it's really annoying. I found out in my sql configuration manager that SQL server service has stopped ...
1
vote
1answer
596 views

How to match a version of SQL Server 2008?

It is needed to install a new instance of SQL to the existing Windows SQL Server 2008. I have no idea what was used to originally install the existing 4 instances. Is it R5 SP32? Thanks to MS ...
2
votes
0answers
71 views

Can there be any case where it would require additional indexes while all columns compose primary key

Ok i don't know whether this question is asked before or not Here my table SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[tblProductPagesFeatures]( [...
1
vote
1answer
1k views

Why isn't SQL Server using my non-clustered index and doing a clustered index scan?

Here are my full tables: CREATE TABLE [dbo].[tblCrawlUrls]( [cl_IdUrl] [int] IDENTITY(1,1) NOT NULL, [cl_CrawlNormalizedUrl] [nvarchar](200) NOT NULL, [cl_RooSiteId] [smallint] NOT ...
3
votes
1answer
261 views

IOPS on Amazon RDS

I'm very very new to AWS. Is one insert record operation on an Amazon RDS table counted as 1 IOP? I have a use-case of inserting about 1 million records every day, and would like the job to finish ...
3
votes
2answers
258 views

idiom to treat empty result set as zero

This is obviously a SSCCE. I have an inventory table that holds the number of items (numOfItems) in a warehouse at any given date (inventoryDate). Both are integers to keep things simple: CREATE ...
3
votes
1answer
102 views

Running a query on an improperly indexed, 600gb table

This is my first post on here and I'm really baffled by this. I have joined a team and somewhere in the past a horrific table was created which has been bloated to a point of 600+gb of data. ...
0
votes
1answer
110 views

Access write conflicts with views and insteadof triggers?

I have a legacy linked table in a msaccess database that is linked to a sql-server table. The linked table is being synced periodically to a different set of tables that have been split up for ...
1
vote
1answer
1k views

num_rows in dba_tables not reflecting the real number of rows in tables Oracle 9i

I'm querying dba_tables in order to get a listing of tables and their rowcount with following sql request : select * from dba_tables where owner='dbSID'; But I notice that even for some non empty ...
0
votes
2answers
193 views

I need to insert data from one table to another after casting the values

i have #temp table containing all details to insert into table(named:Customer) The column names of two tables are exactly equal,but datatypes are different.So i need to cast it before insert into ...
3
votes
1answer
2k views

Converting Cursor based filtering logic to CTE based

I have a cursor that runs long for about minutes which I was able to reduce to run in seconds using a CTE (Common Table Expressions). The result set was identical until there was given a condition ...
0
votes
1answer
64 views

column value is not increment after 10

I tried below 2 codes.... showing same error 1.SELECT columnname FROM tablename ORDER BY columnname DESC LIMIT 1 SELECT MAX(columnname ) AS max FROM tablename I am trying to increment value of ...
1
vote
3answers
4k views

get row count of last select query

I am using a scheduled script which exports the (new) rows of the PostgreSQL database into a textfile. There are also several instances of this script running for several DBs: COPY (SELECT ... FROM .....
0
votes
1answer
400 views

Sales by month cross tab query using pivot

I am trying to show sales by month using PIVOT in Sql Server 2005. I know it's a common question, but I just can't figure out what I am missing here: select ShippedYear, 1 as Jan, 2 as Feb from ( ...
0
votes
2answers
1k views

MySQL: How to fetch nth level child menus of a parent menu

I have a menu which is parent of multiple menu items and each child menu has their own child menus. This pattern is continued till 5-6 vertical hierarchy level. I don't want to write a query which ...
1
vote
2answers
679 views

Getting root node for a given leafs IDs

I got the following tables: +-----------------+ | PRODUCTS | +-----------------+ | #product_id | | #company_id | +-----------------+ | | +---------------------+ ...
0
votes
1answer
483 views

How can I reseed all identity columns?

I deleted all rows from 20 tables after disabling the constraints, triggers using the stored proc SP_MsForEachTable. Now before the import happens, I need to reseed all the identity columns back to ...
3
votes
2answers
535 views

Slow fulltext search due to wildly innacurate row estimates

Fulltext queries against this database (storing RT tickets) seem to be taking a very long time to execute. The attachments table (containing the fulltext data) is about 15GB. The database schema is ...
4
votes
1answer
970 views

“A severe error occurred” - Login Failed (but only occasionally)

I'm using a C# application to execute a query against SQL Server. Here's the relavent portion of an exception I'm encountering once every twenty-four hours or so: System.Data.SqlClient.SqlException (...
4
votes
2answers
1k views

Pulling Top queries returns NULL in query plan and sql text

I'm using the following code to pull our top 20 queries (ordered by CPU): SELECT TOP 20 qs.sql_handle ,qs.execution_count ,qs.total_worker_time AS [Total CPU] ,qs.total_worker_time / ...
0
votes
1answer
111 views

Add range of values under a column using rowid

How do i add range of values in a column based on row id? For example: I have to add all the values under column "time" where rowid>=1 and rowid<=5. Additionally is it possible to divide the then ...
0
votes
3answers
328 views

How to rewrite subquery to return only 1 row

I need to somehow check if query result is NULL, MySQL returns error Subquery returns more than 1 row. How should I rewrite it in order to work correctly please? SELECT DISTINCT user.id, user....
3
votes
2answers
6k views

Get table name from union query?

This is my query SELECT Id, productName, Largeimagepath, Discount, Price, Image FROM tablename1 where Active =1 union SELECT Id, productName, Largeimagepath, Discount, Price, Image FROM tablename2 ...
1
vote
2answers
239 views

Merge Table Row Values to Header of Another Table

Table 1 id | Field | Values ---------------------------------------- 1 | 'Field01' |Name 2 | 'Field02' |Age 3 | 'Field03' |Gender 4 ...
2
votes
0answers
103 views

Service broker routes and DNS

Apologies for the long post but I've recently seen some strange behaviour with Service Broker when the routes it uses reference a DNS entry, and I am wondering if anyone has seen the same? We have ...
1
vote
1answer
256 views

Are Blob Sizes Fixed?

I'm using sqlalchemy to store objects in a database. The kind of object I'm creating is unknown until run time. I could be storing something as simple as a string. I could be storing a jpg. I ...
0
votes
2answers
641 views

A database of all combinations of 39 numbers taken 5 at a time, total combinations = 575757

I want to create a numerical database of all combinations of 39 integers(1 Through 39) taken 5 at a time. I know the total possible combinations = 575757. This would be the size of the database. I ...
0
votes
2answers
6k views

Insert trigger checking for specific column

I have got an insert trigger on a table. The table got a field named 'source'. The source field could be populated with values b or left empty as in null. i want the trigger only to fire if the source ...
-3
votes
1answer
188 views

Selecting a row and row above [closed]

I need to select a row from a table if it has "PART_NO=2" and the row just above it. What should be the query. I'm using Toad for Oracle. Thanks. **Data** *Title------Part_seq_no------*End_time ----...
1
vote
1answer
312 views

Why do ASC and DESC come *after* a CASE clause?

This query works: DECLARE @Ordering NVARCHAR(50) SET @Ordering = 'aggregateid' DECLARE @OrderDirectionInt INT SET @OrderDirectionInt = 0 --1 SELECT * FROM ( SELECT ROW_NUMBER() OVER (...
0
votes
0answers
34 views

MSSQL Insert takes over 5 hours, when inserting more than specific number of rows [duplicate]

I have a question connected with MS SQL. When inserting less then about 1350000 rows to the table it all takes about 2 minutes, but when number of inserted rows is bigger, then a time needed to insert ...
3
votes
1answer
62 views

Transforming multi column table to different structure table

I am a software developer who got data in the following format: TABLE [dbo].[SensorValues]( [timestamp] [datetime] NOT NULL, [Sensor1] [float] NULL, [Sensor2] [float] NULL, [Sensor3] [...
8
votes
1answer
45k views

Best practice to shrink Tempdb in a production environment

What is best practice to use when shrinking Temporary db in SQL Server 2008? Is it risky to use the following? use tempdb GO DBCC FREEPROCCACHE -- clean cache DBCC DROPCLEANBUFFERS -- clean buffers ...
0
votes
1answer
483 views

export and import all the permissions on schema objects within a database SQL Server 2008

Is it possible to export all the users roles and their permissions on schema objects within a database in a .sql file and import them later in an other database ?
1
vote
1answer
226 views

MySQL select data over time that matches pattern over several rows

We currently log data from our plants running remotely for diagnostic purposes on MySQL. I have been asked to come up with a typical running profile for several variables. For this, I would require ...
5
votes
1answer
11k views

Optimizing large database query (25+ million rows, using max() and GROUP BY)

I'm using Postgres 9.3.5 and I have a large table in the database, currently it has over 25 million rows and it tends to get even larger rapidly. I'm trying to select specific rows (all unit_ids with ...
0
votes
1answer
249 views

Why does mysql give different result for this summations? [duplicate]

Can someone help understand the following behavior? mysql> select (1/3 + 1/3 + 1/3); +-------------------+ | (1/3 + 1/3 + 1/3) | +-------------------+ | 1.0000 | +-------------------+ ...
0
votes
0answers
71 views

How to model relation between customers, tariffs and discout

I have following tables (apart from others, that I will omit here): Customers, Tariffs, Discounts and Products (EDIT: and Orders). Each product has its price, and is kept in appropriate column in the ...
0
votes
1answer
199 views

Select latest record for each ID

I have three tables that contains data as below: Users Id Name Other_Columns --------------------------- 1 John Blah 2 Ricky Blah 3 Stella Blah 4 Bob Blah Saldo Id ...
0
votes
0answers
23 views

Undo SQL Update Azure DB [duplicate]

I have accidentally updated over 3,000 records in one of my tables hosted on Azure. This was not a transaction and I did not have any back ups for this table. I've read around and found ApexSQL Log ...
2
votes
2answers
590 views

SQL order by query results in any arbitrary (but reproducible) manner

For testing purposes I need to get some data from a set of database tables in any arbitrary (but reproducible) order. The idea being that I can later compare two runs using a textual diff tool. Is ...
0
votes
2answers
97 views

How to JOIN more than two tables?

Following is the schema of my Tables: Marks_Table (SubjectCode,TypeID,MarksObtd) Subjects_Table (SubjectCode, Subject) Types_Table (TypeID, Type) I want to Select the Marks_Table table with ...
-1
votes
2answers
2k views

Remove a character from a records enclosed between double quotes

I have a table in which every row have data like: 0150566115,"HEALTH 401K","IC,ON","ICON HEALTH 401K",,,1,08/21/2014 What I want is to remove every comma (,) that is enclosed between double quotes "...
1
vote
2answers
2k views

How to count the rows from 1 table but from two different column

SELECT distinct Major, COUNT(Major) AS MajorCount FROM [College Management System].[dbo].[ApplicantCourseDetails] group by Major SELECT distinct MinorI, COUNT(MinorI) AS MinorCount FROM [College ...
0
votes
0answers
128 views

mysql select latest per group using 2 different tables along with self join

I have a query SQL Fiddle which contains 2 tables tbl_data CREATE TABLE tbl_data ( id int(10) unsigned NOT NULL, type varchar(32) NOT NULL DEFAULT '', title varchar(255) NOT NULL DEFAULT '...