Database Administrators Stack Exchange
Database Administrators Stack Exchange

Most Popular

more tags
1500 questions
20
votes
3 answers

PostGIS vs. SQL Server for GIS data

So I'm recently starting at a new company and have a lot of ArcGIS users who seem really keen on going forwards with a PostGIS instance to serve some data to our customers. While I don't have an issue with this, we are a 95% SQL Server and 5% Oracle…
LowlyDBA - John M
  • 10,922
  • 11
  • 42
  • 62
20
votes
1 answer

"Limit 1000,25" vs "limit 25 Offset 1000"

Recently I've found out that MySQL has a offset feature. I've been trying to find documentation about the results of offset, or the difference in between offset and the limit variant, but I can't seem to find what I'm looking for. Lets say I have…
Martijn
  • 303
  • 1
  • 2
  • 8
20
votes
2 answers

Can foreign keys cause deadlocks and hinder READ COMMITTED SNAPSHOT?

This is a followup question from: https://stackoverflow.com/questions/7684477/is-it-possible-to-set-transaction-isolation-level-snapshot-automatically I'm still having deadlock/timeout situations in the ASP.NET application when running large reports…
Tim Schmelter
  • 719
  • 2
  • 9
  • 20
20
votes
6 answers

Count where two or more columns in a row are over a certain value [basketball, double double, triple double]

I play a basketball game which allows to output its statistics as a database file, so one can calculate statistics from it that are not implemented in the game. So far I've had no problem caluclating the statistics I wanted, but now I've run into a…
user39509
20
votes
3 answers

How to implement business logic permissions in PostgreSQL (or SQL in general)?

Let's assume I have a table of items: CREATE TABLE items ( item serial PRIMARY KEY, ... ); Now, I want to introduce the concept of "permissions" for each item (please note, I'm not talking about database access permissions here, but…
JohnCand
  • 515
  • 1
  • 5
  • 10
20
votes
3 answers

Implications of changing compatibility mode in SQL Server from 100 to 110

I have a database Mydatabase created in SQL Server 2008 R2. I have upgraded to SQL Server 2012. I was trying to execute below query to calculate percentile select Distinct [KEY],PERCENTILE_CONT(0.25) within group(order by EachPrice) OVER(Partition…
Shiva
  • 787
  • 3
  • 6
  • 17
20
votes
4 answers

Is "CREATE INDEX` in MySQL a Linear Operation?

What I mean is the following: If creating an index on a table with n rows takes t time. Will creating an index on the same table with 1000*ntake approximately 1000*t time. What I'm trying to achieve is to to estimate the time it takes to create the…
Nifle
  • 1,472
  • 7
  • 17
  • 30
20
votes
4 answers

How to handle table design with variable columns

I have a table design scenario and as a non-DBA type, would like opinions on which is more scaleable. Say you are asked to record information on houses for a metro area, starting with a small neighborhood (200 houses) but eventually growing to…
Schmitty23
  • 301
  • 1
  • 2
  • 4
20
votes
5 answers

What is the exact relationship between a database transaction and locking?

This is a humble question asked in the spirit of increasing my knowledge; kindly be gentle in your response. As a long-time application developer, I know at some level what a transaction is (I use them all the time). Leaving aside transaction…
Laird Nelson
  • 303
  • 2
  • 6
20
votes
6 answers

How do I find out if a procedure or function exists in a mysql database?

How do I find out if a procedure or function exists in a mysql database? and is there any discovery option? like a show procedures; (e.g. like show tables;)
xenoterracide
  • 2,851
  • 5
  • 30
  • 33
20
votes
2 answers

Is there a complete list of Online operations for each SQL Server version?

As we are about to change some of the large tables in our 5TB database I found myself needing a list of the operations that can be performed online and the which require full locks to be held while they run. Ideally, this list would also contain…
Thomas Kejser
  • 6,208
  • 2
  • 22
  • 46
20
votes
6 answers

MySQL import csv file ERROR 13 (HY000): Can't get stat of /path/file.csv (Errcode: 2)

I am an absolute beginner to MySQL (5.5.34) / Linux (Ubuntu 12.04 LTS)` I have created a simple database with one table. When trying to import data into it via a 'data_test.csv file an error message 13 appears. Exert from Terminal follows: mysql>…
Guillermo
  • 203
  • 1
  • 2
  • 5
20
votes
1 answer

Weird SQL Server instance crash on casting to numeric

While working with C# Entity Framework I noticed a crash of my SQL Server instance. I was able to track it down to this statement: SELECT * FROM dbo.[TestTable] where mpnr in…
SteLoe
  • 342
  • 1
  • 8
20
votes
1 answer

How to transfer the ownership of the dbo schema in SQL Server 2012

I've accidentally given a user the ownership of the db_owner schema (using the check box in the UI as below) and now I cannot: Transfer ownership to another user Drop the user from DB (though I can delete login in the SQL Server) I tried The…
Preet Sangha
  • 907
  • 4
  • 11
  • 22
20
votes
1 answer

Is using DISTINCT as a hint in subqueries useful?

Does adding DISTINCT in the following example have any impact on the query running time? Is it wise to use it as a hint sometimes? SELECT * FROM A WHERE A.SomeColumn IN (SELECT DISTINCT B.SomeColumn FROM B)
Yosi Dahari
  • 517
  • 5
  • 15
1 2 3
…
99 100