DO NOT USE this for questions targeting Microsoft SQL Server, use the tag sql-server instead. "SQL" is an acronym for "Structured Query Language", it is used in relational database management systems (RDBMS) to query, update, delete, and insert data as well as modify the structure of the database. ...

learn more… | top users | synonyms (1)

0
votes
0answers
18 views

Data Mock up for my table

I want to mock up data for a table. So I need to generate a million sample records for the table? Table structure is like this: CREATE TABLE Employees( EmployeeID int NOT NULL, FirstName ...
4
votes
1answer
246 views

Why is SELECT * much faster than selecting all columns by name?

On a table with columns a,b,c,d,e,f,g,h,i,j,k I get: select * from misty order by a limit 25; Time: 302.068 ms And: select c,b,j,k,a,d,i,g,f,e,h from misty order by a limit 25; Time: 1258.451 ms ...
2
votes
1answer
54 views

How to check foreign keys related to a table

How to see foreign keys related to a table in MySql? Background : I wanted to drop a table in MySql which has a foreign key constraint. When I do it I get this: Error Code: 1217. Cannot delete or ...
3
votes
1answer
53 views

How is encoding done in SQL database tables for pattern matching?

Are strings in table columns represented as bit patterns or Unicode? Does the database engine bring them into memory in a different representation character set from the disk-persisted ...
1
vote
1answer
22 views

Creating a global temp table in a SQL Agent job

I want to create a global temp table in a SQL Server Agent Job. Here is the code: CREATE TABLE ##TestEmployee(Id int, EmployeeName nvarchar(50)) When I execute in SSMS, all is well. I then put ...
-1
votes
0answers
19 views

TCP Provider: The semaphore timeout period has expired

In Sql Server2008 R2 and Windows Server 2008R2, when I start Replication I have this error: TCP Provider: The semaphore timeout period has expired Please help me to solve it... Thanks!
0
votes
0answers
19 views

For any SQL query, do there exists a unique translation into relation algebra

I have been studying databases and I understand that for each query that a database user type, the DBMS translate them into relational algebra operations before applying them to the database. My worry ...
2
votes
1answer
39 views

Query to flatten schema

I have the following tables and columns: Users id | other-non-important-columns Containers id | Users_id | other-non-important-columns Content id | Container_id | other-non-important-columns ...
1
vote
0answers
21 views

Find all tables that are related to a view recursively sql server

I have a view-A that is comprised of other views (View-B View-C) and I would like to list all the tables that used by View-A (which would include the tables used in View-B and View-C). When I use ...
0
votes
1answer
12 views

Where to look for Agent history in Replication?

In SQL server 2008R2 replication configuration, "agent history cleanup distribution" job by default runs every 10 minutes and deletes any historical data older the 48hrs. If I increase the retention ...
0
votes
0answers
24 views

Need some help building a query to get info from several tables

I have this semi-complex table structure: And I need to return this data: { "id": "10111001", "rep_id": "30000001", "targets_id": "60011001", "brands_id": "10010001", "brand_name": ...
1
vote
1answer
23 views

How to get row count from all tables of a schema ( without using information schema )

The query should collect data from table name , schema name from information.schema and row count should be taken from actual table.
0
votes
1answer
45 views

Arithmetic operations on a table

can anyone give me some advice on the following situation: I have two basic tables: Then a view created by the two: select product.IDPRODUCT, sum(quantity) as totalOut, PRODUCTSTORAGE.DATAINPUT, ...
2
votes
3answers
64 views

SQL actually executed as planned in ORACLE?

I wonder if SQL is definitely executed as execution plan. I mean.. is the actual SQL execution process always consistent with execution plan, i.e. does the same SQL always give the same plan?
2
votes
1answer
25 views

Dividing values from a single column into 2 based on a rule

I am trying to create an SQL query to divide into 2 separate columns based on a rule. My table is as follows Name | Item ID | Parent ID ----- | -------- | ---------- A | 1 | Null B ...
0
votes
1answer
22 views

How to insert UNIQUE IDs values from several tables into events table

I have a table called events defined as follow: rep_id, hcp_id, brand_id, email_id and content_id is the IDs in other tables. event_type should be kind of array containing this values: [ ...
0
votes
1answer
15 views

Function for generate a huge and possible random relationship between tables

I have this two tables and N:M relationship between them: Let's said I am managed to create a lot of media and emails records but I need to create the relationship between them. I can do this ...
1
vote
1answer
102 views

Selecting rows from same name table from multiple databases in single result set

For some reason, I have a table Table_1 in n databases (same name and schema structure) instead of one single databases. The columns are like this: | Column_1 | Column_2 | I can select data from ...
1
vote
0answers
26 views

SQL 2008 R2 Agent stopping sp_who2 running and can't view database or Agent properties

We're experiencing an unusual issue with SQL 2008 R2 SP2 (10.50.4297) where the SQL Agent had stopped but would not restart (this has been fine for months). After investigating the issue we had to ...
0
votes
2answers
24 views

Insert data into table where 4 fields are the same and the fifth changes

I have a table with this structure: username varchar(35) startdate datetime enddate datetime saledate datetime packageid int for each username there are 5 packages, so one username has 5 records ...
1
vote
1answer
62 views

Slow Performance caused by Correlated Sub-queries & Clustered Index Seek

I'm running a query in which one of the tables (FIELDDATA) has nearly 400,000 records which is taking more than 2 minutes to execute. I ran the execution plan, and noticed that the clustered seek ...
-1
votes
0answers
23 views

need to but the date for the sql how can i do that can anyone help me with it please [on hold]

This is the code I did the coding correct but I need to put the date in the coding could anyone help me with this please where do i put the dateFROM and DateTo in the coding below SELECT ...
0
votes
0answers
27 views

Retreive data EXEC SQL for temp table? [on hold]

Is it possible to retrieve the data from usage of EXEC SQL and paste the data inside of a temp table? If yes, can you possible show me? I have a sample of data in this link ...
0
votes
1answer
33 views

Usage of ExecSQL in UDF?

Problem: is it possible to use sp_executesql or EXEC inside of a user defined function? Thanks!
0
votes
0answers
11 views

How to connect to a |Database from SQL Script, in SQL developer

Hi i am trying to connect to a DB using a connection string mentioned in a Script file.. in SQL developer. I am using the below format Connect USERNAME/PASSWORD@HOSTNAME:PORT/Service_name.. But its ...
0
votes
1answer
25 views

Passing filter params to a sub query

I'd like to create a view from the query below, but the query contains a sub query which I need to apply a filter to (the place is marked with a comment). I'm wondering if it's possible somehow to ...
1
vote
1answer
40 views

Generate rows based on value in 2 columns. Bug?

I have the following table in Oracle: ID START END _ID01 AB050 AB100 _ID01 AB101 AB150 _ID02 AB0251 AB0300 _ID01 AB00301 AB00302 I want to generate as many rows and every ...
1
vote
2answers
52 views

How to pull number code from strings buried in parentheses?

I have string data (ProductDescription) that includes product names and ID numbers and need to pull the ID numbers for cross-referencing... Data Samples: Widget (1234) Different Widget (123) Many ...
0
votes
1answer
13 views

How do I get the some of the values from 2 foreign keys in a table?

I've spent a good amount of time on this but, still have no idea as to the sql query I can use to get the student name, and class name from the payments_per_student table. edit: This using JPA work ...
1
vote
1answer
25 views

Optimize view (and underlying table) for averaging timestamps into hours

I have this table: CREATE TABLE spp.rtprices ( "interval" timestamp without time zone NOT NULL, rtlmp numeric(12,6), rtmcc numeric(12,6), rtmcl numeric(12,6), node_id integer NOT NULL, ...
1
vote
3answers
45 views

SQL self join with condition

I have table like: bu_id bu_name parent_id 1 DefaultBU NULL 601 bu1 1 602 bu2 601 603 bu3 NULL 604 bu4 603 I want to retrieve the ...
-2
votes
1answer
34 views

Usage of COALESCE? [closed]

Today: table abc a b c d ---- ---- ---- ---- 1 null 2 3 null null null 7 null 5 null 4 1 = e 7 = b 5 = f 3 = i 2 = w 4 = o The Goal is to retrieve this ...
0
votes
1answer
30 views

Options for structuring multiple associations to a record

This is 101 level stuff and I promise to read a book later, but in the meantime, given the following three tables: Product --------- ProductId (uniqueidentifier) User --------- UserId ...
7
votes
1answer
838 views

Why 0 is equal to empty string?

I need some help in finding why the following T-SQL statement returns 1 (true): SELECT IIF( 0 = '', 1, 0) I guess someone has change an ANSI option like SET ANSI_NULLS or something else that is ...
-1
votes
0answers
26 views

SQL Broadband Market Dilemma [closed]

edit -- Hi I see this post has been closed. Not sure what that means, but I'm guessing I wont be getting an answer. Any idea on where I can go on the internet to get an answer. I've been on-line for ...
-2
votes
1answer
17 views

Why does the user only get prompted once [closed]

DECLARE invoice_date INVOICE.INV_DATE%TYPE := '11-MAY-2014'; customer_code CUSTOMER.CUS_CODE%TYPE := 10010; invoice_terms INVOICE.INV_TERMS%TYPE := 30; invoice_status INVOICE.INV_STATUS%TYPE := ...
0
votes
2answers
52 views

Single query for searching for records without knowing what the search column is for

I have a single line search where i should be able to search for address or city or zip or state. I should be able to show data of what ever that matches for the typed in word. Suppose user had typed ...
3
votes
1answer
88 views

How to index for timestamp queries

I have a products table where I insert around 150,000 records a day. Most of them are redundant, but I need to keep them because of the new expiration date. I get product feeds from about 5 vendors a ...
0
votes
0answers
16 views

Insert Unique Records into Linked Server Table (from SQL to Oracle)

My issue is that I am trying to load only unique primary key records into oracle from sql. In the insert into stored proc statement I tried doing it by where primary key does not equal primary key ...
0
votes
0answers
35 views

How can I calculate the difference between my time column in seconds? [closed]

I have got a large database, one of the columns is the time with this format "08:27:22" and I am looking to find out the difference of seconds between the "open doors" and "close door" events. The ...
0
votes
2answers
51 views

Database Design - Cardinality

I am currently studying for my MTA Database Fundamentals cert, and I came across this question during my studies. However, the answer doesn't make any sense to me. Here is the question/answer: It ...
0
votes
1answer
21 views

Postgres: Disable “similarity” column output on “SELECT SIMILARITY”?

I've tried disabling similarity column output on SELECT SIMILARITY but still cannot. Below query will generate a column similarity: SELECT SIMILARITY(title, 'Nation'), title FROM gallery WHERE title ...
10
votes
0answers
107 views

SQL Server recreating plans each day

We have this problem in our production environment. Microsoft SQL Server 2008 R2 (SP1) - 10.50.2500.0 (X64) - Enterprise Edition (64-bit) on Windows NT 6.1 (Build 7601: Service Pack 1). SQL Server ...
0
votes
3answers
44 views

Load specific element from XML

I have the following xml: <root> <123 type="line"> <aa type="item"> <value>1</value> </aa> </123> <234 type="line"> <bb type="item"> ...
0
votes
1answer
27 views

How to serialize on SQL server metadata?

I tried to find an answer in Kalen Delaney's SQL Server Concurrency but there is no mention of metadata. The problem is how to ensure that only one of these reads and index creates executes from one ...
-1
votes
0answers
15 views

beginner's book for SQL server 2012 [closed]

I am new to SQL server 2012 Administration. I want to start learning it form 0. I have keen interest in learning SQL server DBA. Could someone please help me with a good book which can help me from ...
5
votes
2answers
81 views

Why do so many queries have aliases? [closed]

I've spent a lot of time searching for, researching and implementing various complex MySQL queries over the last several years. Many of them use lots of aliases for tables, even when none are ...
0
votes
0answers
29 views

What is wrong with my ERD?

I am trying to create a reservation/booking ERD. Is their anything wrong with my current ERD? It must show a supertype and subtype, and it must identify attributes for regular, weak, and associative ...
1
vote
1answer
49 views

How to Get one Single statement of two table value in SQL

I wanted to retrieve two table values in one single statement such as, (Example Bank statements..) Table 1: Invoice SELECT ClientNo, InvoiceDate, InvoiceNo, TotalDue FROM Invoice_TBL WHERE ...
6
votes
2answers
66 views

Select lowest ID and calculate deviation from the lowest

I'm trying to create a view. I have some data in my SQL Server database, and I want to order them by their mark1 and mark2. Now when I sort them I want to select the mark1 who has the lowest mark2 and ...