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

learn more… | top users | synonyms (2)

4
votes
2answers
5k views

Using T-SQL to dynamically create Extended Properties

I would like to add Description and Status in the Extended Properties of a large database to all my tables and columns so that someone can fill in the values. It would be very laborious to add these ...
3
votes
2answers
8k views

Creating a PostgreSQL SERIAL column using pgAdmin3

When I use pgAdmin3 to create a column of type serial, why does it instead create nextval('prices_id_seq'::regclass)? Should we keep this type defined by pgAdmin? Manually create the table with SQL ...
1
vote
1answer
783 views

MySQL stored routine performance while using PREPARE

Instead of maintaining stored routines for each database in my current environment I have decided to create separate database just for stored routines storage. Mainly I am using them for reporting. ...
1
vote
2answers
357 views

“Rewinding” a Postgresql database

I have heard that Postgresql uses an append-only format for it's databases, and if this is true I should be able to 'rewind' the database to a previous point in time by removing the commits that came ...
4
votes
2answers
4k views

Error while taking backup using SSMS

I am using SSMS. I have about 5 logins. For a particular login I have all server roles other than sysadmin. It is a sql login (not windows authentication). In user roles it has all permission for ...
2
votes
1answer
621 views

Paging in SQL Server 2012 vs. previous versions

I have used lot of paging code in SQL Server 2008. The format is With CTE( -- SQL With RowNumber ) -- SELECT ALL Page N This is working fine. But I have heard that SQL Server 2012 has better ...
1
vote
1answer
498 views

GROUP BY with OR/AND clause?

Lets say my database looks like this: Table: messages +----+------+----+----------+----------+ | id | from | to | double_1 | double_2 | +----+------+----+----------+----------+ | 1 | 1 ...
2
votes
1answer
153 views

How can I identify the number of times a View is called in SQL Server?

How can I identify the number of times a View was called in SQL Server? I am looking for an answer that is similar to this below which shows how it can be done for stored procedures. How can I ...
1
vote
3answers
166 views

T-SQL Issues With Defining 'AS'

I am creating a fully dynamic application but have ran into a bit of a hiccup. There are multiple 'undefined' fields that can be defined by the users. The only problem is redisplaying them. Currently ...
3
votes
2answers
101 views

Identity proceeds as normal, but with a leading 10…?

So I noticed a very strange thing when tinkering with my database. I had just inserted a row which was ID 47, then after inserting some more, I realised that somehow my Identity had skipped all the ...
2
votes
3answers
1k views

Is transaction log lost after backup on SQL Server?

I have database that has regular nightly backup (full recovery, full backup). I noticed some irregularities in data and wanted to check out transaction log to see what happened. I restored earlier ...
1
vote
1answer
39 views

Simple query not using indexes

My query is not using indexes. It is a simple query. Can someone explain why that's the case? The query: select TIME_FORMAT(start_time,'%H:%i') start_time, TIME_FORMAT(end_time,'%H:%i') ...
2
votes
1answer
620 views

How to enhance the SQL query performance for thousands and millions of huge data

Now Our team are involved in a forum data analysis project. Its target data is so huge(I think). We should find the potential informations from it. Such as the popular products,the customers activity,...
1
vote
3answers
10k views

Oracle SUM - ORA-00937: not a single-group group function

For every journey want to sum them to work out the distance between. SELECT Journey.Journey_No, Stages.Stage_ID, SUM(Stages.Distance_Between) FROM Journey, Journey_Stages, Stages WHERE Journey....
2
votes
2answers
1k views

SQL3550W: … target column has been defined as GENERATED ALWAYS

I am trying to export + import data from a table in a db2 database to another. But while running the export command I am getting the following error. SQL3550W The field value in row "26" and column ...
0
votes
2answers
350 views

Storing data in PostgreSQL: One table or two?

I've just started using PostgreSQL 9.2 and my data consists of product prices at various points in time, usually a different price every month. Question: Because every product can have different ...
0
votes
2answers
75 views

Accessing Database without using SQL

This might be some ambiguous but I have not much info about this so I need some start point; As a general idea; in conventional RDBMS', there are a DB engine and storage of data. We use the language '...
3
votes
3answers
2k views

Relational vs Non-Relational Database for Events Database

I'm trying to find out whether an SQL or no-SQL solution would be better for creating an events database. I'm creating a ticketing system, similar to ticket master. I know that for either database-...
7
votes
1answer
1k views

LATCH_EX Waits on Resource METADATA_SEQUENCE_GENERATOR

We have a process that generates an inventory report. On the client side, the process splits of a configurable number of worker threads to build a chunk of data for the report that corresponds to one ...
2
votes
1answer
682 views

Replacing a PHP loop with a MySQL Query

I have a MySQL database table where I store a user_id and a hash each time someone logs in. The hash is stored in a persistant cookie so it doesn't change as users log in and out of the site. The ...
3
votes
2answers
4k views

Get all partition names for a table

I want to list all the partitions created by dynamic triggers in PostgreSQL 9.1. I was able to generate a count of partitions using this related answer by Frank Heikens. I have a table foo with an ...
4
votes
1answer
2k views

Update “NULL” string to Actual NULL value

I have a table that contains NULL values but the problem is that some of the values are actually string "NULL" and not actual NULLS so when you trying something like where date is null it will not ...
0
votes
2answers
134 views

How to write a script to push the server time to out?

Background: I am programming a database in Ms-Access and using SQL-server as the Back-end. But since my database will be a multi-user platform I need to make sure that whenever a user is posting data ...
0
votes
2answers
2k views

Only allow one checked row in a Column in SQL Server [duplicate]

Background: I've only just begun programming in SQL... barely begun even. But I have been thrown into the deep-end left to find a way and learn to program SQL. Question: Is is possible to have a ...
1
vote
4answers
1k views

Unsure how to implement certain constraints and relations in this relational database schema

I am in the middle of designing the dB for school project- airline ticketing database.(Using MySQL workbench) So far, I came up with the following design: Here are couple things I can't figure out: ...
1
vote
2answers
7k views

Oracle SQL, how to filter on a set by a rank?

I am struggling with the following SQL query right now: http://sqlfiddle.com/#!4/736a7/12 select uc.* from usercredential uc inner join users u on u.userid = uc.userid inner join credential c on c....
0
votes
1answer
58 views

How should I set up these tables in my relational database?

I'm working on a project where a person can create a survey with custom questions and display the questions in whatever order they choose. They can break the questions into sections. The problem I'm ...
0
votes
1answer
466 views

exec xp_cmdshell 'sc \\<servername/serverip> start <service_name>'

When I execute this on sql server on another machine (in same sub net) I am getting "Access is denied". But when I did it from command prompt (sc \ start ) it is working. Please advice why I am ...
20
votes
2answers
45k views

How to optimize a query that's running slow on Nested Loops (Inner Join)

TL;DR Since this question keeps getting views, I'll summarize it here so newcomers don't have to suffer the history: JOIN table t ON t.member = @value1 OR t.member = @value2 -- this is slow as hell ...
2
votes
4answers
2k views

search for something in the first 1000 records in the table

If I do the following MySQL query select * from table where name Like '%john%' limit 5; that mean the query will search all the mysql database table and return only the top 5 results that match the ...
2
votes
3answers
83 views

How to take SQL Server database under this scenario?

I have a database of size 290 GB running on SQL Server 2005 (compression is not available) and disk space is z: drive 250 GB and Y: drive is 80 GB. How can I take a full backup in this scenario? How ...
0
votes
2answers
12k views

MySQL: Join one Row in a table with two Rows in other table

In my MYSQL Database COMPANY. I have two tables, like below in my diagram (arrow shows relations): `users` `user_login` +--------------+ +-----------------------...
2
votes
1answer
115 views

Find application password expiration date

I changed password of my application on one sql server? when I am using Login property command I am getting last login date. I want to know when my application password is going to expired in SQL ...
16
votes
2answers
18k views

MySQL and window functions

It seems that MySQL does not support window functions. E.g. the simple: COUNT(*) OVER() AS cnt does not work. What I am not sure is if this applies to commercial version as well (I assume the ...
3
votes
2answers
2k views

Reducing high VLF count

I have been using Brent Ozar's sp_blitz script to review our database setup and have found that the VLF count is high (13562!) on the primary DB since changing to Full recovery model. I ran a full ...
1
vote
2answers
1k views

MySQL: CAST + SUBSTR vs. FLOOR + MOD

I have a field in a mysql database where dates are stored as BIGINT like YYYYMMDDHHMMSS (PHP: date('YmdHis')). I'm building a query, where I check the amount of rows per hour and have tried two ...
5
votes
1answer
931 views

How does order of index affect performance in SQL Server?

I am wondering how does order of index affect performance (ASC, DESC) ? If it affects, why ? And which order should I choose ?
1
vote
1answer
897 views

import csv data stored in a blob column

Here's what i need to do: Users will upload CSV files into SQL Server 2012 blob column Each night, i would like to take each file and import the data into table I will have 2 web sites using the ...
1
vote
2answers
46 views

Order by on an alphanumerical column

I have a column that has strings of the following type: Miller 10 Allen 20 King 10 .... Jones 100 I try to sort the column based on the numerical part of the data. I tried the following: SELECT *...
1
vote
1answer
6k views

Is there a way to truncate table that has foreign a key?

Is there a way to truncate table that has foreign keys ? Delete and reseed can take too long. Is deleting and recreating keys only way ? If so is there a tool that does this ?
2
votes
2answers
29k views

How to check if Oracle DB process is running in Unix and where Oracle DB is installed

I am new to Unix and trying to find out a command that checks if Oracle DB is installed in my Unix box. I came across commands ps -ef|grep smon and ps -ef|grep pmon in net. But which is the correct ...
1
vote
1answer
989 views

Multiple like conditions for same column?

How do I write a query for a Oracle database when I have several "like" values for the same column (in my example data "name")? My example data for table "address": name like "John" name like "Jon%"...
3
votes
1answer
2k views

SQL Server stored procedure working when run manually, not running from SQL Server Agent

I have a procedure that runs fine using the execute command in SSMS, however putting the same command in a job gives the following error. line 9, character 9, unexpected end of input The code ...
2
votes
1answer
39 views

Which MySQL directories to back up using FTP

My server has been hacked and I only have FTP access to recover my data before reinstalling the OS. Which/where are the directories that needs to be backed up? Will MySQL be recovered back to its ...
7
votes
5answers
1k views

Database design: Dividing multiple identical tables, good or bad?

I am very new at SQL and databases in general. I only use them for the occasional homework so I haven't even tried to master them. I have seats at a theater, the seats are divided into 4 main areas (...
0
votes
4answers
4k views

Space at the end of string

I have a SQL code like shown below: declare @str nvarchar(max),@i int set @i=0 set @str='abc ' declare @tbl Table(a nvarchar(max)) insert @tbl select @str while (select a from @tbl)<>'' begin ...
2
votes
3answers
4k views

Can I make sure two columns dont have the same value

If I have a table that looks like this CREATE TABLE foo ( id INT NOT NULL AUTO_INCREMENT, aa INT NOT NULL, bb INT NOT NULL, PRIMARY KEY (id), UNIQUE KEY (aa, bb), CONSTRAINT aa_ref ...
3
votes
2answers
4k views

How can I improve this nested MySQL query to avoid redundant WHERE statement?

I am trying to improve on a nested MySQL query, using the following table structure (omitting a few dozen not relevant to my question): tbl_users: ID | email (all subscribers to our email list) ...
6
votes
1answer
2k views

How do I optimize large table so queries that target only recent data perform optimaly?

So I have this table that is ever growing. Most queries are targeting just recent data, say one month old. I suppose this is common problem but I have no idea how it can be solved. I am open to ...
3
votes
4answers
2k views

What is the best permissions to set for only creating and managing own databases?

I want to create a login for a new user who could only create and manage their own databases. Other databases on the server should be read-only to that user. What would be a good set of roles/...