All Questions

Filter by
Sorted by
Tagged with
12
votes
3answers
16k views

Tracing, debugging and fixing Row Lock Contentions

Off late, I've been facing a lot of row lock contentions. The table in contention seems to be a particular table. This is generally what happens - Developer 1 starts a transaction from Oracle ...
2
votes
1answer
298 views

Are there other sites still using SQL Server with codepage 850 or codepage 437 for their sql scripts?

Using one of these codepages is a disaster when scripts are edited by Visual Studio or TFS, but sometimes it is difficult to change old standards. What must be observed, when changing to the current ...
9
votes
2answers
3k views

How to migrate SQL Server Stored Procedures using temporary tables or table variables to Oracle?

C# developer encouraged by management to write SQL Server stored procedures often produce procedures like this create table #t1 (...); insert into #t1 Select ... from table_a where ...; insert into #...
11
votes
1answer
990 views

SQL Server 2008 R2 Dirty reads - how non-atomic?

I am wondering "how dirty" dirty reads can get under a read-uncommited isolation level. I understand that rows that have been updated but not yet committed are visible, but: Can a row appear as ...
4
votes
1answer
198 views

Can I determine if a stored procedure uses dynamic sql without parsing the definition?

Since there seems to be no way to strip the procedure definition from comments without parsing cf. this, there seem to be no reliably T-SQL query to answer this question. Or can I get this ...
13
votes
3answers
10k views

SQL Server performance sudden degradation

I have a SQL Server 2005 that has become unpredictable of late, and I'm scratching my head as to why. Queries that executed in seconds are changing plans and taking minutes (taking the time in full ...
5
votes
2answers
247 views

Are there other sites forbidding SQL developer to use c style comments?

My personal opinion is that C style comments should not be used by sql developers. The problem with C Style comments is, that they do not nest. C Style comments are one of the lifesavers for the ...
7
votes
1answer
503 views

convert legacy showplan text into graphical format

Does anyone know of a way to convert legacy showplan text into a graphical format? In other words, can I take the StmtText returned from SET SHOWPLAN_TEXT ON and convert that to show me a graphical ...
6
votes
1answer
3k views

DBMS_Resource_Manager.Calibrate_IO Usage

DBMS_Resource_Manager has a procedure called Calibrate_IO with a Physical Disks input parameter. The documentation says this value should be set to the... Approximate number of physical disks in ...
7
votes
4answers
2k views

When to use MySQL query_cache?

Up until recently, I have viewed the query cache as a very important tool to improve query performance. Today, I was listening to a podcast that discussed tuning the query cache to 0, and using a ...
20
votes
2answers
4k views

Index vs. partition

Why is it not possible to achieve performance improvements with indexes only, so that other techniques like table partitioning becomes necessary? The question relates only to performance, of course ...
24
votes
7answers
15k views

Storing IP address

I have to store the IP address of all registered users in the database. I am wondering, how many characters should I declare for such a column? Should I support IPv6 as well? If so, what is the ...
5
votes
2answers
2k views

Should we use SQL Server partitions for a multi-tenant database design?

My organisation is creating a multi-tenant data architecture to support analysis of data uploaded by multiple different clients. The database is Microsoft SQL Server. It has been suggested that ...
4
votes
2answers
3k views

DBMS_REDEFINITION vs EXCHANGE PARTITION in oracle

In order to partition an existing non-partitioned table it is possible to use either the EXCHANGE PARTITION or DBMS_REDEFINITION. How to decide on which of those possibilities to choose for table ...
9
votes
4answers
17k views

How to design a relationship database table to store friendship relationship?

I want to design a table to store friendship relationship in my web project It should satisfy at least the following 4 conditions: who send the add-friend request e.g.(if A TO B then this column ...
21
votes
6answers
23k views

Is there any good tool for making Database Design and Prototypes? [closed]

I would like to have a good tool for designing the Database Schema with all the tables, columns, data types and relations. Today I mostly do this with pen and paper but I would like to do it in a good ...
2
votes
2answers
2k views

How can can I get object definitions stripped of all comments?

When I want to know if a stored procedure or views references a given name (table, view or column), I need a version of the object definition stripped of all comments. Is this possible by a SQL ...
6
votes
2answers
2k views

Can I call SMO from pure T-SQL?

I know how to script a stored procedure using PowerShell and SMO: [System.reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo") | out-null [System.Reflection.Assembly]::...
6
votes
1answer
13k views

Introduce partitions into an existing non-partitioned table

What has to be done to introduce range partitions into an existing non-partitioned table in oracle? Is there an easy to do and safe way to do this? Especially in cases where there are many ...
11
votes
2answers
665 views

large data movement

I would like to move billions of rows from schema1.table1 to new schema2.table2 where table2 is a refactored one from table1. Hence their table structure is different. both table1 and table2 are ...
5
votes
2answers
2k views

SQL Server agent Error

I am in a new shop and the below error started to show up on 2/22 repeatedly until 3/7. Event Type: Error Event Source: SQLSERVERAGENT Event Category: Alert Engine Event ID: 318 Description: ...
4
votes
2answers
1k views

How to index a many to many table most effectively

Should a many to many table be indexed? What kind of index would be best? Here's an example table: CREATE TABLE user_role ( userId INT, roleId INT ) --edit: drachenstern - I added the table def ...
5
votes
2answers
966 views

Best way to identify orphan data?

I have a few dozen tables, that have data in them, both for the core set of data, and then the company specific data. I can never delete the core set of data, that is global for all companies. But i ...
6
votes
3answers
1k views

Sub queries or joins?

Whats better in terms of SQL server efficiency; to use sub queries or joins? I know uncorrelated are better than correlated sub-queries. But what about joins? The SQL becomes more readable and ...
6
votes
3answers
5k views

Is WAL Database log on separate disk good idea on RAID10?

I have a RAID10-Server and have Postgres writing the log(pg_xlog a.k.a WAL) onto the same RAID10-Array. I use the WAL in sync-mode with a big buffer and do a lot of bulk inserts and updates, so that ...
6
votes
2answers
415 views

How to manage my own scripts

Every administrators, developers have their own collection of scripts (some patterns). Time to time they are dismissed from one job and they are employed to other one. Is there any good, easy used, ...
2
votes
1answer
792 views

Drop Oracle data file with 0 bytes and in offline (needs recovery) state

What can I do to drop a data file mistakenly created and currently with 0 byte and in offline (needs recovery) state?
4
votes
1answer
7k views

Optimal value for LOG_BUFFER parameter

The wait event “log buffer space” according to the documentation can indicate that the log buffer is too small or the I/O is slow. We have a system seeing this event in which the redo buffer ...
20
votes
5answers
9k views

What standard should I follow when naming tables and views?

What standard should I follow when naming tables and views? For instance, is it a good idea to put something like tbl_ at the beginning of table names? Should I designate code/lookup tables in some ...
36
votes
2answers
4k views

Why are Denali sequences supposed to perform better than identity columns?

In his answer to Which is better: identity columns or generated unique id values? mrdenny says: When SQL Denali comes out it will support sequences which will be more efficient than identity, but ...
11
votes
8answers
3k views

Identity Columns or UDF that explicitly generates a unique id?

I am in middle of a debate about whether it is better to make a PRIMARY KEY out of an Identity Columns, our out of a UDF that explicitly generates a unique id. I am arguing for the Identity Column. ...
0
votes
0answers
564 views

Whats better: identity columns or generated unique id values?

I am in middle of a debate about whether it’s better to use identity columns as a primary key on a DB table or have a generated value manually created. I am arguing for the sql managed identity ...
3
votes
1answer
712 views

acl to allow nested groups in owner attribute

I'm trying to create an ACL entry to allow users specified in the owner attribute of a group to manage the group, and also allow groups, including nested groups to be specified as owners. I've got ...
6
votes
1answer
2k views

Best way to automate sql server backup & restore from prod to dev?

I have a development server, and a production server. The dev server is inside our office at work inside our LAN. The production server is a remotely hosted windows pc, that is connected to our web ...
11
votes
1answer
22k views

How can I assign different random values to each row in a SELECT statement?

Please look at this code: create table #t1( id int identity (1,1), val varchar(10) ); insert into #t1 values ('a'); insert into #t1 values ('b'); insert into #t1 values ('c'); insert into #t1 ...
14
votes
1answer
13k views

Why does SQL*PLUS need a slash after CREATE TYPE?

I just had the problem that I defined a Type and tested it in TOAD and all was OK. But running under SQL*PLUS it threw an error. Example: CREATE OR REPLACE TYPE MyType AS OBJECT ( Item1 NUMBER, ...
4
votes
1answer
123 views

Permissions Dropped after Package Failed

I created a local db and then set up a one-off job to copy the live db to this new db. Or at least that's what I thought I'd done. The logs suggest that the job thought it had to create the db on live ...
5
votes
1answer
925 views

Which fillfactor is used by ALTER INDEX REBUILD?

From technet I got the impression, that I either have to explicitly set the wanted fillfactor or when I do not specify it, the default fillfactor 0 is choosen. For tables I know to get rows added ...
6
votes
1answer
237 views

IIS access log to SQL normalization

I am looking for insert IIS 6.0 access log ( 5 servers, and over 400MB daily ) to SQL database. What scares me is the size. There is a lot of information you are duplicating (i.e. site name, url, ...
3
votes
1answer
597 views

Oracle monitoring db status

I am looking for a way to monitor Oracle database status (health) via PHP script executing SQL query. There are system variables containing actual number of connection, sessions, work load, etc... ...
4
votes
2answers
465 views

Searching for a Data Dictionary for SQL or Oracle

I am looking for metadata specifications (data dictionary) for SQL Server and Oracle databases. By metadata map, I mean a definitive description of what objects make up database model and their ...
6
votes
2answers
1k views

Is there a way to have MySQL GROUP BY utilize trailing whitespace?

I recently had an issue come up where I noticed that MySQL would ignore trailing whitespace when issuing a GROUP BY. Here's the table: CREATE TABLE `myTable` ( `id` int(10) unsigned NOT NULL, `foo` ...
6
votes
2answers
2k views

Updating rows conditionally with a gapless ascending value

A user has a table they would like to update. Depending on the data in other columns they want a particular column to be updated with an ascending value starting from 1 that is gap-less. The rows ...
6
votes
1answer
202 views

In a Trigger, can I determine if a column was explicitly set to a values or not mentioned in update statement?

Given a Table with a column col1 like this: create table MyTest ( col1 int NULL, col2 decimal(7, 2) NULL ); insert into MyTest values ( 1, 1.1); Is there a way for a trigger for update to ...
2
votes
1answer
171 views

Dates in openldap acl rules

Is it possible to use dates in OpenLDAP's ACL entries? I'm looking to do something like to * by this/termDate < NOW none by * break
8
votes
2answers
3k views

Is there a free software addition to SQLite available for data-replication?

I am aware of the possibility to create my own trigger(ed) solution, but I didn't want to reinvent the wheel :-)
7
votes
2answers
2k views

How to convert primary keys into clustered primary keys for a great number of tables keeping referential identity

After discovering, that I better change the primary keys from non clustered to clustered. Cf this question about clustered indexes on identity columns, the next step is how to do this in a prcatical ...
12
votes
4answers
10k views

Installing default MySQL Tables in Windows (mysql_install_db)

In Linux, the default MySQL grant tables can be created with the mysql_install_db script, but that does not work on Windows. How can the default grant tables be installed on Windows? (No, I’m not ...
6
votes
3answers
1k views

Need database of categories for products and services

Need to develop a database schema that associates products and services into industry-standard categories. I know that both the schema and data are out there, yet I'm not finding it. There are ...
5
votes
1answer
4k views

database duplication VS database replication

Is there a difference between duplication and replication with reference to a database? I am not sure if I am correct at all, but duplication means to me that we are copying bit by bit without ...

15 30 50 per page