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

learn more… | top users | synonyms (2)

1
vote
1answer
39 views

How can I speed up a stored procedure?

Here was an interview question that I just had. I would like to know what a good answer to this would be and what the different options could be. We have a stored procedure that is taking 26 ...
-1
votes
0answers
16 views

Get results from a query without calculate it

i'm working with a big database more than 50 GB and basically the system that is using this database is to generate reports, I want to know if there is a way when I try to generate a report for second ...
0
votes
0answers
56 views

Trying to find a MIN() workaround for an indexed view

I'm trying to speed up a SQL Server query using indexed views and I have one slow element which I think is due to the use of GUIDs for the particular comparison column. The GUID can be mapped to an ID ...
0
votes
0answers
6 views

SQL 2012 Availability Group issue - The core cluster resources do not move when we initiate AG fail-over

I have a Windows 2012R2 cluster with two nodes and a File Share quorum. I am running SQL 2012 SP2 and I have configured a test AG with a dummy database. When I fail-over the AG, the Availability ...
0
votes
1answer
205 views

Sessionizing Log Data Using SQL window functions: tweaking a query (incrementing session ID and event length)

I'm referencing to a very good blog entry, that describes how to sessionize log data with SQL window functions. I'm no database pro and I don't really understand the logic behind this query (you can ...
12
votes
5answers
301 views
+250

Group daily schedule into [Start date; End date] intervals with the list of week days

I need to convert data between two systems. First system stores schedules as a plain list of dates. Each date that is included in the schedule is one row. There can be various gaps in the sequence ...
0
votes
1answer
13 views

(SQL) User tables are separated into two sections

Using SQL Plus, I create 13 tables via an .sql file, each containing primary and foreign keys. When I execute "select table_name from user_tables;" they are displayed in this partitioned fashion. ...
0
votes
1answer
17 views

Dumping and restoring tables in PostgreSQL - ERROR: extra data after last expected column

I am trying to dump and restore multiple tables from a database on one server, to a different database on another server. Both are running Postgres 9.4 on linux. The command for the backup was: $ ...
-1
votes
3answers
38 views

how to find the last date from the week number as input

I need to find the last date of week when i pass year and week number as input in my stored procedure. week starts on monday and last day of week will be friday. Database - IBM db2 9.7 platform - ...
0
votes
1answer
13 views

List objects in SQL file

We are trying to migrate a database from SQL 2014 to Azure SQL. In SSMS, you can right click on the database and then on a task to "Deploy Database to Windows Azure SQL Database..". This task ...
5
votes
1answer
43 views

Database stored procedure with a “preview mode”

A fairly common pattern in the database application I work with is a need to create a stored procedure for a report or utility that has a "preview mode". When such a procedure does updates, this ...
0
votes
1answer
142 views

Converting sql into mongodb nosql

I am trying to convert oracle sql into its mongodb equivalent version. create table my_hll as select mod(ora_hash(n), 1024) bucket, max(num_zeroes(trunc(ora_hash(n)/1024)))+1 val from previous_data ...
-1
votes
1answer
12 views

Data Quality Service (DQS) and Master Data Services (MDS) [on hold]

Best practice question. Is anyone using one of these in production? Can you share your experience and the business need you were solving with it? I am learning about them and do not really see the ...
0
votes
1answer
15 views

Result consisted of more than one row in procedure

i confused with this i try not using cursor and same get error so i back using cursor but before this day my query success and can generate but this get error please help me i already look other ...
83
votes
8answers
20k views

Where are some useful SQL puzzles to teach SQL in a workplace?

I'm looking for beginner and intermediate level SQL puzzles, that I can point trainees at for practice. I'm aware of http://sqlzoo.net/ which is a great resource - is there anything else out there ...
2
votes
1answer
38 views

Search for X at the beginning of a line within a multi-line string

On SQL Server (2008), I have a column which is varchar(max). I want to query for all rows where the blurb contains a lettered list: Some sentence is here a. foo b. bar c. baz Some more text later ...
0
votes
1answer
52 views

Simple SQL question [on hold]

I'm try to resolve this but I don't understand? My query Select FirstName, City FROM Customer WHERE City NOT IN ('Paris') Error Your answer does not contain the expression <>
8
votes
2answers
232 views
+250

Unexpected CASE evaluation logic

I always understood that the CASE statement worked on a 'short-circuit' principle in that evaluation of subsequent steps does not take place if a prior step is evaluated to true. (This answer Does SQL ...
2
votes
1answer
59 views

Why is Include Actual Execution Plan not included in @@Rowcount when it produces a row count?

While updating a column in a table the last day I was shown a new technique by our Senior DBA to reduce reads and contention. The pseudo-code is WHILE [always true condition] BEGIN UPDATE ...
0
votes
1answer
144 views

Switching a partition on a table with one to many relation

There is two main tables in database (SQL Server 2014) table1: ---------------------------------------------- id name datetime image table2: -------------------------------- ...
6
votes
3answers
93 views

Query wont compile/run

I have a query that won't run on the production server but it runs on the test server. It seems like the query plan won't compile or its compiling a very bad query plan. I have updated all statistics ...
0
votes
1answer
15 views

VMware vFabric Data Director [on hold]

https://www.brentozar.com/archive/2013/04/cloud-alternatives-to-microsoft-sql-server/ I liked the idea of VMware vFabric Data Director so I can host data in own cloud / data centre. It seems this ...
1
vote
2answers
29 views

How to Join two table using pivot with combining two field in one table

I have two table in this format Job_Skill ID_Skill Min_Job Idea_Job Max_Job ===================================== 1 0 0 1 2 ...
2
votes
1answer
53 views

Amazing improvement of script execution speed when split - not understood

I have an ETL job that merges data into about 20 columns of a fact table, all integer non-null (default 0) columns. There are 8 merge statements to completely update all the columns and they involve ...
1
vote
1answer
34 views

Use a scalar hashing function in a computed column - non-deterministic?

I have a scalar function select [dbo].[fn_get_hash_fields] ('blah','dbo') to which is passed a table and a schema and which returns an expression representing a concatenation of all column values ...
1
vote
1answer
232 views

Query multiple rooms with different conditionals (Adults, Children)

I have a simple query where I select available x Rooms with x Adults + x Children per hotel that matches a date range, but I'm having a hard time trying to figure out how to query a list of rooms per ...
5
votes
3answers
86 views

Need help with null field values

I am having a problem with syntax. I have the following statement which updates a field in a table with a value from a field in a different table. I need the statement to update the field with the ...
0
votes
1answer
26 views

Script to Delete Tables With A Prefix

I am running SQL Serve 2008 and we have a few test tables that were obviously used for testing. The prefix of the tables are Test_, my question is, will this script delete all tables with the prefix ...
0
votes
0answers
18 views

How to use a cross reference table for different features of a product in different versions?

I am creating a database for one of my classes. I am using SQL Server 2014. I am designing a database that monitors the source control of development of different products. This company has different ...
0
votes
0answers
11 views

transfer the new posts on local to host [on hold]

I want instead of creating new posts on webhost, firstly created on localhost and then I transferred to the webhost; this is not problem for me till here, but After transferring a number of posts, if ...
0
votes
1answer
19 views

OLEDB provider for linked server reported change of schema version error

I am trying to run a report but its giving this message: An error has occurred during report processing. (rsProcessingAborted) Query execution failed for dataset 'ParamYear'. ...
2
votes
3answers
42 views

Extract string in PostgreSQL 9.2

I got the following column: full_path "gorfs"."absolute_pathname" It keeps the path of a BLOB, like on the example below: Note that the 4865 is the account_id.The path is always like: ...
2
votes
0answers
31 views

How do I guarantee inventory numbers are unique across all tables?

I have an assortment of physical objects which need to be inventoried and each type of object has it's own table. For two example objects, let's use 'swords' for the table containing different swords ...
0
votes
1answer
19 views

How to use pivot when there are three fields and u want to put them in one category

I have data in this format Skill Min Ideal Max ========================== C++ 0 1 1 I want to display it like this Criteria C++ ================= Min ...
0
votes
1answer
109 views

ALTER SESSION from a Control File

I am using a Control File to import data from a CSV file into a table, using sqlldr to import the data, where a large number of fields are numeric values with integer and decimal part separated by ...
0
votes
0answers
53 views

SQL Server data file growing

We have an odd situation happening with one of our tables in our database and I am hoping someone can shed some light on it. This table has 3 columns id(bigint), ...
0
votes
0answers
29 views

Convert table values into column header without PIVOT [number of column values is dynamic] - Oracle 10g [duplicate]

I have a table in the following format ID NUM P_VAL Q_VAL ---------------------------------------- 445120000 00 101 25 445120000 10 103 25 445120002 00 ...
13
votes
3answers
33k views

Pivot rows into multiple columns

I have a SQL Server instance that has a linked server to an Oracle server. There is a table on the Oracle server called PersonOptions which contains the following data: ╔══════════╦══════════╗ ║ ...
0
votes
0answers
14 views

cannot call json_array_elements on a scalar [on hold]

I have JSON values like this: [{"lang_id":179,"language_id":26,"phy_id":82,"language":"Duch"}, {"lang_id":177,"language_id":16,"phy_id":82,"language":"ENGLISH"}, ...
0
votes
2answers
44 views

Name already been used by an existing constraint after renaming the table and creating a new one

I would like to know why, if I alter a table name to different name and then create a table with the same name, I am getting the error "name already been used by existing constraint". This is the ...
1
vote
1answer
21 views

Versioning entities in db with draft option, select query

I have postgresql db where i keep data for jee application. User can edit those data without creating new version of entity(draft). During edit, entity is copied as draft(version set to -1) after ...
1
vote
2answers
78 views

How can I remove 'Using temporary ' and speed up my query

Hi I was working for hours to speed up this query using indexes, but its useless.. im tyring to optimize by eliminating 'Using temporary' and using only indexes. I wonder if there is another ...
-6
votes
1answer
28 views

SQL Simple Question [on hold]

This looks correct to me, however I receive a table where figures exceed $40.0?
4
votes
1answer
187 views

Query to determine start and end dates based on time overlap

Given the following data: id | user_id | started | closed | dead ------------------------------------------------------------------------------------------- 7714 ...
2
votes
2answers
343 views

Select a range of values

I have the following data in rows: 1 2 3 6 7 15 16 I want to write a SELECT statement to return contiguous ranges. Given the sample data above, the output would be: 1,2,3 6,7 15,16
1
vote
0answers
26 views

Is this database in 1NF, 2NF and 3NF?

I'm doing a software development course and I have to normalise my tables as part of it. Can anyone double check that my data is normalised correctly? The database is for a gym. It has these tables ...
0
votes
0answers
41 views

Design a ER for hospital database system [on hold]

I trying to design an ER Diagram for a hospital database, and the developer of the database determent what they want in the system of the hospital .. but some of what they want I cannot understand it ...
-1
votes
0answers
33 views

normalize table data

Maybe my question is duplicate or asked many times, but it's rare, and I really need this. I have a table, which contents looks like: Id points 3 20 3 42 4 40 4 60 6 20 6 30 5 10 ...
0
votes
1answer
260 views

Select only rows that equal x but not x and y?

I'm having quite the challenge with selecting hierarchical data in MySQL although I feel the schema should be optimized for it since it's based on the WordPress database model. I've got 4 tables: ...
2
votes
3answers
101 views

Return the uniqueidentifier generated by a default on insert

Goal Retrieve the latest guid value in real time after you have inserted the value in the table Problem Don't know how to do it Info The code should only specify new values for address and ...