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
458 views

How to delete the old backup differencial files?

I'm (trying) managing a large number of sql server instances among a number of machinces running SQL Server 2005 and 2008. Almost all the DB are in Simple Recovery Model. I'm doing full backups every ...
1
vote
1answer
44 views

Instant File Initialization on Backup drive

I know IFI (Instant File Initialization) is good to have enabled on SQL Servers. How about backup archive locations? We backup our DBs to a local SSD drive (so it backs up quickly), then throughout ...
0
votes
1answer
136 views

Modify a value inside of a nested dynamic column

In the table (items), I have a dynamic column (attr). I have a very simple nested dynamic column. INSERT INTO items (attr) VALUES (COLUMN_CREATE('level1', COLUMN_CREATE('level2','level2_value'))); ...
0
votes
0answers
23 views

How to write parent / firstChildLevel / secondChildLevel

I am still pretty new when it comes to SQL, but I am attempting to write Hierarchy of tables which will act as parent / firstChildLevel / secondChildLevel. I understand the reasoning behind it, but I ...
5
votes
4answers
25k views

Get counts grouped by month and year

I am trying to report on data in e-email messaging system and wanted to have SQL tally counts by month/year. SELECT YEAR(crdate) AS y, MONTH(crdate) AS m, COUNT(*) AS tally FROM ...
2
votes
4answers
939 views

Can SQL Server stop me from running an UPDATE statement without a WHERE clause?

Whenever I have to run an update statement, I always get nervous that I might forget the WHERE clause and overwrite the entire column in the database. Is there any setting or plugin or addon I can ...
1
vote
1answer
510 views

Select all most recent records

I have this two tables (and their main fields): Target: Message: Id IdMessage Id IdMain IdReply DatePosted ================ ...
2
votes
4answers
5k views

NVARCHAR column as PRIMARY KEY or as UNIQUE column

I'm developing a SQL Server 2012 database and I have a doubt about nvarchar columns as primary keys. I have this table: CREATE TABLE [dbo].[CODES] ( [ID_CODE] [bigint] IDENTITY(1,1) NOT NULL, ...
2
votes
0answers
31 views

Query optimization - contact should be extracted at most once for a particular customer

I have created a database for a telemarketing company Scenario is, the telemarketing company is selling contacts to the customers. Customers log on to the website and search for the contacts for ...
0
votes
0answers
149 views

Many to many update query

SQL Server 2012 Notification Source can have multiple deliveries type and each delivery type can have one notification templates (For future, Each delivery type can multiple templates). Notification ...
0
votes
2answers
44 views

Problems with Insert( simple query - can't think something )

I have 2 tables Tblservicedesk and Tblservicedesk_temp Collumns: CodServiceDesk ID_Solicitacao Solicitante Departamento Tecnico Grupo Status Prioridade ...
0
votes
1answer
292 views

Sql Rank for records contain multiple combinations

I am trying to filter the records which contains multiple combinations and removing the records which contains a single combination. Database DB2 Query Used: select * from (select b.Store,b....
0
votes
1answer
157 views

Need some help with this query or some ideas for change it to better model

I've a dilemma in my application and I don't get how to solve it at DB level. The idea is that a user can be subscribed to one or more services but services are show depending on user type. This can ...
-1
votes
1answer
231 views

Joining 3 tables of given sizes in most efficient way

Say I have 3 tables, A, B , C of sizes 10, 10^4 and 10^6. What is the most efficient way to join them? This is my reasoning. Assuming you join over primary key, if you join A with C first, you will ...
3
votes
0answers
126 views

Change Tracking across several Tables

I'm looking into providing DB level change tracking within my system. I need to be able to track changes at an entity level, rather than just individual tables. Right now, we have triggers on each of ...
2
votes
2answers
551 views

Database-agnostic stored procedure call passing array of name/value pairs

Background Looking to pass a set of name/value pairs into a stored procedure in a database-agnostic way, using JDBC. A database structure is defined as follows: CREATE TYPE array_parameters AS ( ...
1
vote
0answers
88 views

Deleting already duplicated Filestream Files

I have three FileStreams (FS1 on F drive, FS2 on H drive, FS3 on E drive) belonging to the same FileStream group of one particular database (DB) which is in Simple recovery mode in the SQL Server 2012....
1
vote
2answers
249 views

How to configure Standby database in oracle for read/write

I have configured a physical standby database in Oracle 11gR2, and the standby database it's working fine. All the changes in the principal database are replicating to the standby database, the ...
0
votes
1answer
130 views

Convert an interaction matrix into a ternary relation

I have what I'm calling a sparse "interaction matrix" of data in MS Excel that I wish to convert into a "ternary relation" (table) in Access. The spreadsheet data are in this pattern: Name Act-1 ...
0
votes
1answer
346 views

Setting a column's default value by adding a constraint

I wrote this query: ALTER TABLE test2 add constraint setting_value33 default male FOR namee; But I get this error when running it: You have an error in your SQL syntax; check the manual that ...
1
vote
1answer
2k views

Can Execute trigger in SQL Server 2008 R2

I'm trying to create a simple trigger to update a table's column after it was inserted. But when I execute the trigger it produces an error: Cannot execute script. Additional information: Unable ...
5
votes
4answers
1k views

Composing the “ultimate database maintenance script”

I'm trying to compose T-SQL script that will: A) Shrink database size to the minimum possible size; B) Make integrity checks, and completely "reorganize" and optimize the database so that it is in a "...
1
vote
1answer
189 views

Sql Insert into with duplicate key

Ok so, I'm still a beginner in databases. I have this code: $sql=" INSERT INTO complaints_members (password, complaint) VALUES ('$mypassword','$submit') ON DUPLICATE KEY UPDATE ...
2
votes
1answer
307 views

Why does my MySql Savel replicate changes that are neither in binlog_do_db nor replicate_do_db?

I have a standard simple master slave replication that usually runs fine. But sometimes, all of a sudden, I get replication errors on the "phpmyadmin" table. I have explicitly only specified one ...
1
vote
1answer
211 views

Modifying a non managed table partition

I ran across a partition that was initially setup (sql 2012) beginning of 2014 to break by month but wasn't updated for 2015. The main issue is that the last partition was setup as Jan 2015 so Feb ...
0
votes
0answers
79 views

Why does the SQL Server 2012 SA account always show up with creation date of 4/8/2003?

Why does the SQL Server 2012 SA account always show up with creation date of 4/8/2003? I am not sure why this is happening and if this is something that needs to be resolved. I noticed that on ...
0
votes
1answer
35 views

Query performance with primary keys of different data types

Is there a significant difference in performance if i decide to use INT or BIGINT as data type of a primary key in a table? Of course if i use BIGINT the value will have twice it's size. But as the ...
0
votes
1answer
103 views

Nice query to get my friends

my table has this structure: User_ID | Followed_ID Example row: 33 | 36 Means that user 33 follows user 36. If exists a record like 36 | 33 then the two users are considered friends. What'...
0
votes
0answers
257 views

Emulate regex matching in ANSI SQL

I'm working in an ANSI SQL environment (no regex) on a script that is supposed to take the first 8-10 consecutive digits out of a text field that could: have alphanumeric characters, and some other ...
2
votes
1answer
88 views

How to store a 64-digit key of alpha numerical values into MySQL Database

I am student studying in Database Administration (first year), one of our task requires a to design a login form for corporate and regular users. Below are the requirements for the username and ...
2
votes
1answer
404 views

Order by where conditions

SELECT * FROM table WHERE key REGEXP 'some long regular expression' OR key REGEXP 'some other long regular expression' ORDER BY key REGEXP 'some long regular expression' DESC, key REGEXP 'some other ...
1
vote
1answer
167 views

How to include a where clause in a query from another column

I have two tables: pointer_table(col_name varchar, col_val varchar, wh_clause varchar) value_table(col_1 varchar, col_2 varchar, value varchar) col_name in pointer_table denotes the column in ...
0
votes
1answer
154 views

Is there a reason my CTE with a basic subquery in the second stage of the CTE is failing?

Ok... anyone aid with this sorry...i've been playing with some of my works reports and i thought id found a way to mix two results into one when i came across a small issue... if i run my CTE code ...
0
votes
0answers
295 views

count rows with same value and get column from other table

I'm trying to count product numbers for a period of time there are three tables. invoice (table) Column Column ------------------------------------------- |invoicenumber ...
0
votes
0answers
77 views

How can I make a foreign key from a fragment of the primary key?

I have this table: CREATE TABLE Phones ( Brand VARCHAR(20), Model VARCHAR(20), PRIMARY KEY (Brand, Model) ); A mobile phone can't be uniquely identified just by its model (for example: HTC (One) or ...
2
votes
2answers
243 views

SELECT WHERE CASE

I have the following table (MySQL): -------------------------------------------------------- | id |product_id| store_id | stock_id | priority | --------------------------------------------------...
1
vote
2answers
137 views

DB backups for production to DR without creating jobs in prod

I had two servers(SQL 2005) A and B. B is Production server and A is DR. I want to take backups for B server databases from A server. We can not create any maintenance plans or jobs in B production ...
4
votes
2answers
282 views

Moving data from table with VARCHAR(50) fields to table with numeric fields increases table size

I want to understand why migrating data in a table with all VARCHAR(50) fields to a table with optimized smaller types caused the new table (containing 61,065,164 rows) to be 4.46 GB, which is larger ...
2
votes
0answers
475 views

Transform XPath map into XML document using relational data [closed]

Background Most modern databases have XML functions that can be used to extract data in an XML format. I want to avoid the task of manually calling XML functions to extract the data. This problem ...
0
votes
1answer
226 views

HAVING ANY in Postgres

I'm looking for the right syntax to filter groups (i.e. groups of rows that have been GROUP BY'd) where at least one row fits a certain condition. For example if I wanted to select a group where at ...
1
vote
1answer
876 views

invalid memory alloc request size on simple query with array_agg on Postgres 9.3

Postgresql 9.3 - Debian 7 54gb RAM 8 cores (On google compute engine) I have a huge table called search_token (~50M rows) and I'm trying to execute a very simple query that is: select array_agg(st....
0
votes
1answer
31 views

Insert where not already available for FK?

I'm looking for a simpler way to do this as I'm clearly thinking like a developer instead of a DBA. How would a DBA tackle this? I have a table with 3 column: PK, Name, FK. PK Name FK 1 ...
1
vote
2answers
389 views

Count values from one table using two columns in another table

I have a problem. I have table_1 (col1, col2, value) and table_2 (col1, col2). I want to count values from table_1 but only the rows which exist in table_2. In both tables col1 and col2 are key (not ...
3
votes
2answers
43k views

Windows could not start the SQL Server (mssqlserver) service on local computer error 17058

I'm working with virtual machine on the windows 8. There is a SQL Server 2008 on the virtual machine. And in the services; when I try to start the SQL Server service getting an error like this: Can ...
1
vote
0answers
110 views

Removing Orphan Pull merge replication subscriptions

I have a merge replication set where I have removed one of the pulled subscriptions. On the subscriber the subscription shows up after deletion. It will not go away. I tried the methods provided on ...
1
vote
1answer
17k views

Conversion failed when converting the nvarchar value '0.000000' to data type int

I have a code select CONVERT(NUMERIC, CASE ISNULL(FLEXFIELD4,'') WHEN '' THEN 0 ELSE FLEXFIELD4 END ) FROM DEIT_INDENT_DTL_INBOUND_RXL WHERE IND_INDENT_NO='NEE15SR000003' which is ...
-1
votes
1answer
2k views

use variable as column name in where clause in sql

I want to use a variable as column name in my SQL query. Problem: I can not use dynamic SQL as the SQL query that I have is inside of a user defined inline table function. What are other possible ...
0
votes
0answers
92 views

Query gives not result

I have a quite simple table with different values. When I search a specific column no results are given. What could be the error? For example I search for an "id": db=# select * from table where id =...
4
votes
1answer
63 views

Checking for multiple identical values in a Posgresql array

I have a simple table in Postgresql: CREATE TABLE data (id integer, values integer[]); INSERT INTO data VALUES (1, '{1,2,3,4,5}'); INSERT INTO data VALUES (2, '{1,1,2,3,4,5}'); INSERT INTO data ...
2
votes
1answer
1k views

SQL Maintenance Rebuild Fails

I am pretty new to SQL and I have this maintenance job that keeps failing and I am not sure how to troubleshoot this. I have troubleshot other job failures with google help to success but this one is ...