2
votes
1answer
153 views

Entity relationship many to many

Is an association class in UML the same as an Link entity in ERD?
5
votes
1answer
760 views

Are foreign keys better than indexes?

I have a replicated copy of an application database. I am exploring how to change the constraints and indexes to better serve our reporting requirements. Because this is a copy of the application ...
2
votes
2answers
2k views

Adding Timestamp to Exported Filename in SQL Server Management Studio

I am exporting to a CSV using a job with xp_cmdshell. I need the filename to include a timestamp. The code I currently have is... EXEC xp_cmdshell 'bcp "SELECT * FROM dbo.tablename" queryout ...
2
votes
2answers
2k views

SQL Server Management Studio .bak files

I'm new to SQL SMS and need help. I have been asked to check that .BAK files restored be checked using SELECT queries in SQL SMS. I have tried to create a new database but get the error "CREATE ...
0
votes
3answers
780 views

Is it possible to edit sys.messages texts?

Is it possible to add a new language to SQL Server 2008 and edit SQL error messages with message_id less than 50000?
3
votes
3answers
2k views

Natural (numeric) sort on string

I've got a table of data like so: Process_Id Stage_Id Step_Id P1 P1.5 P1.5.1 P1 P1.5 P1.5.10 P1 P1.5 P1.5.11 P1 P1.5 P1.5.12 P1 ...
2
votes
1answer
8k views

How to drop variable in SQL Server [closed]

I have declare a variable in SQL Server: Declare @variable How can I drop or enable the variable when I don't need it?
2
votes
2answers
224 views

Connecting to SQL Server 2008 on windows server 2008

Hell all I am new to windows server 2008 and we are trying to transfer our website from hosting to virtual server. I installed SQL Server 2008 Express edition and I did the things like opening port ...
2
votes
2answers
339 views

Configuring SQL Server profiler

Every 15 seconds or so, the application I am trying to profile fires: @@TRANCOUNT > 0 COMMIT TRAN IF @@TRANCOUNT > 0 COMMIT TRAN IF @@TRANCOUNT > 0 COMMIT TRAN SET IMPLICIT_TRANSACTIONS OFF ...
4
votes
2answers
884 views

Read mysql config (my.cnf) using mysql console

I have a mysql server that is not under my control (I don't have ssh on that machine). I only have access to one database using mysql client. How can I have access to config options that are declared ...
2
votes
1answer
786 views

Difference between SSAS 2005 and SSAS 2008

I'm asking this question since i'm planning to book a seminar, but the provider told me that they only offer trainings for SSAS 2008 or as individual training which are expensive. So my question: ...
5
votes
1answer
4k views

What is the meaning of n_live_tup and n_dead_tup in pg_stat_user_tables

What is the meaning of n_live_tup and n_dead_tup in pg_stat_user_tables or pgstattuple?
4
votes
4answers
2k views

Best pratice to store dates group by months-year key par value

I am making one service which needs to hold data grouped and calculated by year-month combination. I know how to calculate data and put it on new table. But I am confused what kind of data type I ...
1
vote
2answers
71 views

stack data into a single column

SELECT * would result in: course_id + group ----------------- 1 + SE123 1 + BA134 2 + SE213 3 + SE223 3 + BA344 4 + SE222 I would like it be like ...
0
votes
2answers
7k views

Opening a .backup file for PostgreSQL

I'm going to warn you that whoever explains this needs to talk to me like I'm five years old, because this is something way, way beyond my level of expertise. In short, I have what I think is a ...
14
votes
3answers
4k views

Can you explain this execution plan?

I was researching something else when I came across this thing. I was generating test tables with some data in it and running different queries to find out how different ways to write queries affects ...
2
votes
1answer
218 views

Multiple databases, in multiple geographic locations while preserving PostgreSQL triggers and data consistency

I've read a ton over 300 pages of db setups in the last day but I feel a little overwhelmed with all of the potential options and lack of documentation on how PostgreSQL triggers function across ...
6
votes
3answers
3k views

How to identify columns order in a table

I need to list columns from a table in the table definition order: select * from syscolumns where id = object_id('MyTable') --order by colid By examining syscolumns tables two columns look ...
4
votes
1answer
303 views

SQL Server Storage sql_variant

USE tempdb ; GO DROP TABLE tbl ; GO CREATE TABLE tbl ( i SQL_VARIANT NOT NULL ) ; GO INSERT INTO tbl (i) VALUES (1) ; GO SELECT i FROM tbl ; GO DBCC IND ('tempdb','tbl',-1) ; GO DBCC ...
4
votes
2answers
6k views

Performance settings for MyISAM tables - keep everything in memory

I have a 8-core MySQL server with 32 GB of RAM, holding 40 MyISAM tables (total: ~4GB of data). Since the databases size is several times smaller than the available memory, how can I make full usage ...
9
votes
2answers
229 views

SQL SERVER Storage of TinyInt

In SQL Server, why is a tinyint stored with 9B in the row. For some reason there seems to be an additional one byte at the end of the NULL bitmap mask. USE tempdb ; GO CREATE TABLE tbl ...
1
vote
3answers
826 views

access columns returned by psql from bash

I want to use value returned by psql in bash. This is the script: psql "connection parameters" -c "SELECT pg_database_size('dbname');" The output is like this: pg_database_size ...
1
vote
1answer
1k views

What is the replacement for EXEC sp_dboption <dbname> in SQL Server 2012?

From microsoft connect you learn, that it is closed by design. But they do not tell how to replace EXEC sp_dboption master to query the current db options.
1
vote
1answer
696 views

How to know how many lines are copied from table - psql

I want to write a script to backup a table using COPY. psql "connection parameters" -c "COPY (SELECT * FROM tbl WHERE insertion_date > 'date') TO STDOUT WITH CSV HEADER;" | bzip2 -c > ...
3
votes
3answers
2k views

Finding five-minute interval prior to current time

I want to find the five minute interval just before the current time. So if the current time is 8:43 I want to return 8:35 and 8:40. Here is the query that I have written and it is working as ...
2
votes
0answers
7k views

Alternative OpenSource/free to MySQL Cluster Manager Client? [closed]

I need to deploy a MySQL cluster but initially no bucks for the MySQL Cluster Manager Client. It's at least $5k and I actually think in my case it will go way over that. I looked a lot on Google (a ...
2
votes
1answer
2k views

Best way to compare strings using pre-built ngrams?

I'm struggling with an n-gram problem and have been hunting the Web for examples to help me out and have noticed n-grams being discussed on this forum. I'm working on a system where we've got scraped ...
0
votes
2answers
920 views

Recommended plugin for SQL server 2008 [closed]

Is there any recommended plugin for SQL server 2008 to use when you are working as database administration, Business Intelligence developer or Database developer?
2
votes
1answer
300 views

Query logical processing phases while mixing old and new join syntax

Could anyone clarify why order of tables in FROM matters if I mix old and new syntax for joining tables? For example, SELECT * FROM table1 a, table2 b INNER JOIN table3 c ON (c.a_id = a.id) -- ...
2
votes
1answer
156 views

Lock Pages in Memory for SQL Server

What is a recommendataion regarding turning on Lock Pages in Memory for 64 bit SQL Servers. Is there any implication on turning on lock pages in memory setting?
3
votes
1answer
466 views

How do I prevent updates on an entire table (but allow inserts) in Mysql 5.5?

I want to make the rows in a Mysql table immutable, once they are inserted I don't want it to be possible to change them. How can I do this? This question handles making individual rows immutable, ...
4
votes
4answers
150 views

Getting the value of 'foo' for the closest previous row with higher 'bar'

From this initial data: select level as foo, mod(ora_hash(level),4) as bar from dual connect by level<9; /* FOO BAR ---------------------- ...
0
votes
1answer
105 views

Transfer ERD between Databases on SQL Server

I've a development database on SQL Server 2008. I want to copy the production database back to my development server to get the latest data for testing. Unfortunately the production database is ...
15
votes
1answer
25k views

How to use psql with no password prompt?

I wrote a script to REINDEX indexes in a database. Here is one of them: echo -e "\nreindex for unq_vbvdata_vehicle started at: `date "+%F %T"`" >> ${LOG_FILE} psql -U ${USERNAME} -h ${HOSTNAME} ...
2
votes
4answers
7k views

decrypt/get sql user password

I have a sql server 2005 database server. I have administrative access(sa access) however I need the password to a specific account on the server. I can't really reset the password to this account ...
5
votes
2answers
934 views

How to automatically stagger transaction log shipping to minimize bandwidth usage peaks?

The following question pertains to Microsoft SQL Server transactional log shipping (TLS). We're using SQL Server 2008 R2 SP1, although the question is probably relevant for all recent editions. ...
9
votes
1answer
2k views

Interpreting SQL Server's Showplan XML

I just rolled out a feature on my site http://sqlfiddle.com that allows users to view the raw execution plans for their queries. In the case of PostgreSQL, MySQL, and (to some extent) Oracle, looking ...
38
votes
4answers
13k views

What are Objective Business Reasons to Prefer SQL Server 2012 over 2008 R2?

My company is facing the decision whether to purchase SQL Server 2012 Denali or SQL Server 2008 R2 for a new database server. I am looking for objective reasons to choose one over the other. Our ...
8
votes
3answers
244 views

Nonclustered Index Insert

Say I have a table like this: create table SomeTable ( id int identity(1, 1) not null primary key clustered, SomeString1 varchar(50) not null, SomeString2 varchar(50) not null ) go ...
4
votes
2answers
6k views

Can mysql restore a single table from a large mysqldump?

What is the best way to restore a single table from a large (~5GB) database mysqldump file? update: I have found solutions (posted below) using command line tools to parse the table, but is there a ...
0
votes
1answer
87 views

can I copy-paste SQL Server 2005 Express on XP Pro or Vista machine and make it work without installer?

I am having installer problems on several machines trying to install SQL Server Express 2005 Express as well as later versions (but I would be happy with 2005 anyway). However, I do have one machine ...
6
votes
1answer
145 views

Are there any fully declarative implementations of SQL

I was recently bitten by a bug ( in my code ) where these two queries had drastically different runtimes: select * from smalltable st inner join bigtable bt on st.btid = bt.btid select * from ...
1
vote
3answers
5k views

How should I Schedule SQL Server 2008 queries to run every month?

I need to schedule some queries to run at the beginning of every month (for monthly reports) and I read that creating a JOB in sql server 2008 was the way to go. Unfortunately, I don't have the ...
3
votes
1answer
2k views

PostgreSQL Scheduled Database Backup

For scheduling a daily back up on a postgres database we execute the following command: 0 2 * * * /usr/local/pgsql/bin/pg_dump -Fp -b -U database_name \ > ...
1
vote
2answers
125 views

Where does a type specific field go?

Let's say I have table of cars and table of car manufacturers. Each car references one manufacturer. Furthermore, each car that has manufacturer x requires an extra field. Say, cars from that ...
3
votes
1answer
226 views

MySQL chain replication

I have 3 servers A, B and C. What I want to do is replicate databases like this A->B->C, where A is replicating DB1 and DB2 to B and B is replicating those same databases to C, but I would also like ...
2
votes
3answers
1k views

performance of multi field primary key or 'contrived' 'semi artificial' key

This is not a question regarding the benefits or otherwise of using an artificial auto increment key in any given table against using a multi-field 'primary key'. That discussion (or argument) can ...
4
votes
1answer
393 views

index not being used

I have an exact same myisam table on master and slave. The indexes are not being used on Master server. mysql> explain select count(date_time) from ox_data_summary_ad_hourly where date_time = ...
5
votes
1answer
3k views

Dropping unique constraint on PostgreSQL table taking 10+ hours

We have a PostgreSQL database with a fairly large table (~50 million rows). The table has a unique constraint across several columns, each of which is indexed. For context, I recently deleted about ...
3
votes
1answer
2k views

How do query notifications detect changes in the query result?

I am working with a legacy database that has implemented query notifications against a table called ColorList. While running a server-side trace for an unrelated issue, I noticed the query SELECT ...

15 30 50 per page