Database Administrators

Q&A for database professionals who wish to improve their database skills

Latest Questions

4 votes

1 answers

104 views

"The data type of a column of a partitioned table can't be changed."

sql-server partitioning datatypes alter-table documentation

[The documentation claims](https://learn.microsoft.com/en-us/sql/t-sql/statements/alter-table-transact-sql?view=sql-server-ver17#alter-column) > The data type of a column of a partitioned table can't be changed. It repeats this claim elsewhere [such as here](https://learn.microsoft.com/en-us/sql/t-s...

CREATE PARTITION FUNCTION pf(int)
AS RANGE RIGHT FOR VALUES(10, 20, 30)
GO

CREATE PARTITION SCHEME ps
AS PARTITION pf
ALL TO ([primary]);
GO

CREATE TABLE Part
(
    id INT IDENTITY PRIMARY KEY CLUSTERED,
    name VARCHAR(50)
) ON ps(id);
GO

INSERT INTO Part (name)
SELECT TOP(40) CONVERT(VARCHAR(50), [text])
FROM sys.messages;
GO

ALTER TABLE Part
ALTER COLUMN [name]
SQL_VARIANT;
GO

So what am I missing?

J. Mini (1237 rep)

May 29, 2025, 03:29 PM • Last activity: May 29, 2025, 07:17 PM

1 votes

0 answers

56 views

Understanding of sqlplus STARTUP command syntax

oracle sqlplus documentation

On https://docs.oracle.com/en/database/oracle/oracle-database/19/sqpug/STARTUP.html, the document states that: > ## Startup command > ... > > where db options has the following syntax: ``` [FORCE] [RESTRICT] [PFILE=filename] [QUIET] [ MOUNT [dbname] | [ OPEN [open_db_options] [dbname] ] | NOMOUNT ]...

On https://docs.oracle.com/en/database/oracle/oracle-database/19/sqpug/STARTUP.html , the document states that: > ## Startup command > ... > > where db options has the following syntax:

[FORCE] [RESTRICT] [PFILE=filename] [QUIET]  [ MOUNT [dbname] |  [ OPEN [open_db_options] [dbname] ] | NOMOUNT ]

So it seems that the if you want to use force and restrict, you have to use something like startup force restrict. But by my testing, startup restrict force also works. But the document describe the option like [FORCE] [RESTRICT], to me, this indicates an order, and force should go first, then restrict. Is my understanding correct?

Just a learner (2082 rep)

Apr 26, 2025, 03:25 PM • Last activity: May 23, 2025, 02:50 AM

-3 votes

1 answers

95 views

How do you document an existing SQL database owned by someone else?

sql-server documentation

I am working with an existing Microsoft SQL database that does not have documentation. I want to write documentation for myself. However, because I did not create this database myself, I want to avoid editing said database. This means I cannot use Extended Properties or create new Database Diagrams...

                                  I am working with an existing Microsoft SQL database that does not have documentation. I want to write documentation for myself. However, because I did not create this database myself, I want to avoid editing said database. This means I cannot use Extended Properties or create new Database Diagrams in Microsoft SQL Server Management Studio.

What tools and processes are recommended for documenting an existing SQL database owned by someone else?

*(Note: this differs from the similarly named question  which is about documenting a database you own as it is developed)*

Stevoisiak (111 rep)

Feb 25, 2025, 05:53 PM • Last activity: Feb 25, 2025, 09:04 PM

-2 votes

1 answers

22 views

Online docs has incorrect JVM flag unsafe_aggressive_sstable_expiration for TimeWindowCompactionStrategy

configuration cassandra errors documentation environment-variables

I was installing the unsafe_aggressive_sstable_expiration and found out that there is a mistake in the documentation. The documentation told us that we needed to enable in the jvm -Dcassandra.unsafe_aggressive_sstable_expiration=true, while we actually needed to use -Dcassandra.**allow_**unsafe_aggr...

                                  I was installing the unsafe_aggressive_sstable_expiration and found out that there is a mistake in the documentation. The documentation told us that we needed to enable in the jvm -Dcassandra.unsafe_aggressive_sstable_expiration=true, while we actually needed to use -Dcassandra.**allow_**unsafe_aggressive_sstable_expiration=true. I think it is important that this gets fixed because this can led the users to some time loss until they find out the issue. 

Adding to that, it would be great if you could add some documentation on the procedure to install these tags. For instance, we did not know if these tags needed to be in the cassandra.yaml file or if they had to be in the env-sh file. It was in the second that they needed to be installed, and this led us to some time loss as well.

Thanks for your attention

Jon Corral (1 rep)

Jul 4, 2024, 12:43 PM • Last activity: Aug 1, 2024, 03:24 AM

1 votes

1 answers

97 views

Understanding "Mode" Parameter and Finding Documentation for Event Properties in SQL Server

sql-server extended-events documentation

I'm working with an extended event session in SQL Server and encountered a part of the T-SQL code that I don't fully understand. Specifically, I'm puzzled by the use of "Mode = 3 OR Mode = 4" in the following portion of the code: ... ADD EVENT sqlserver.latch_suspend_end (WHERE ( Duration > 0 AND (M...

                                  I'm working with an extended event session in SQL Server and encountered a part of the T-SQL code that I don't fully understand. Specifically, I'm puzzled by the use of "Mode = 3 OR Mode = 4" in the following portion of the code:

    ...
    ADD EVENT sqlserver.latch_suspend_end
    (WHERE (
    Duration > 0
    AND (Mode = 3 OR Mode = 4)
    AND has_waiters = 1
    )
    )
    ...


I've been trying to find documentation or any sort of information that explains what "Mode = 3" and "Mode = 4" represent in this context, but so far, I've had no luck.

Furthermore, I'm interested in finding a comprehensive source of documentation or reference that describes the properties and possible values for different events, not just for this specific example. Could anyone point me towards the relevant documentation or explain what these Mode values signify in this extended event session? 

Thank you in advance for your help!
                                

Fajela Tajkiya (1239 rep)

Jul 15, 2023, 11:29 PM • Last activity: Jul 16, 2023, 08:55 PM

3 votes

3 answers

2531 views

Question about 'user process' in the context of Oracle

oracle documentation process

The following is an excerpt from [Oracle's documentation][1]: > When a user runs an application > program (such as a Pro*C program) or > an Oracle tool (such as Enterprise > Manager or SQL*Plus), Oracle creates a > user process to run the user's > application. As per my understanding, a user process...

                                  The following is an excerpt from Oracle's documentation :

> When a user runs an application
> program (such as a Pro*C program) or
> an Oracle tool (such as Enterprise
> Manager or SQL*Plus), Oracle creates a
> user process to run the user's
> application.



As per my understanding, a user process is a piece of software that can connect to an Oracle server. You (the user) can start this piece and then connect to Oracle. If so, why does Oracle create a user process to run the user's application?
                                

Just a learner (2082 rep)

Jul 6, 2011, 04:21 PM • Last activity: May 15, 2023, 03:23 PM

0 votes

1 answers

172 views

Where to find documentation about the log reader procedures?

sql-server replication sql-server-2014 transactional-replication documentation

From the replication monitor in sql server transactional replication, when I have a look at what has been replicated, from publisher to the distributor, I get the following picture: [![enter image description here][1]][1] It does not show on the replication monitor, therefore running the internal pr...

                                  From the replication monitor in sql server transactional replication,
when I have a look at what has been replicated, from publisher to the distributor,
I get the following picture:

It does not show on the replication monitor, therefore running the internal procedures to get hold of what has been read from the transaction log and replicated to the distributor I use the following procedure:

    use [master]
    exec [distribution]..sp_MSenum_logreader_sd 
    @name = N'myserver-Product-102', 
    @time = N'20170410 16:36:16.917'

this gives me the following results:
(same data as on the picture above, plus more information)

**Question:**

I am struggling to find any documentation on the stored procedure that I use to find out what the log reader agent has been doing:
**sp_MSenum_logreader_sd**

where can I find information about this procedure?

I also would like to save this info into a temp table or table variable, so that when I spot an error_id  that is not zero
I can get extra info as you can see below, based on the error_id from the picture above.

    -- error_id 5467351 comes from the above result set
    use [master]
    exec [distribution]..sp_MSget_repl_error 
    @id = 5467351

Marcello Miorelli (17274 rep)

Apr 11, 2017, 03:44 PM • Last activity: Mar 27, 2023, 03:57 AM

-1 votes

1 answers

79 views

Is there any pattern behind the naming conventions of PostgreSQL Table-Level Locks?

postgresql naming-convention documentation

I know that PostgreSQL has 8 level of table locks which are, from least restrictive to most: 1. ACCESS SHARE 2. ROW SHARE 3. ROW EXCLUSIVE 4. SHARE UPDATE EXCLUSIVE 5. SHARE 6. SHARE ROW EXCLUSIVE 7. EXCLUSIVE 8. ACCESS EXCLUSIVE However, I can't really determine any pattern or convention to these n...

                                  I know that PostgreSQL has 8 level of table locks which are, from least restrictive to most:
ACCESS SHARE
ROW SHARE
ROW EXCLUSIVE
SHARE UPDATE EXCLUSIVE
SHARE
SHARE ROW EXCLUSIVE
EXCLUSIVE
ACCESS EXCLUSIVE

However, I can't really determine any pattern or convention to these names.  There are literally 5 words total: ACCESS, SHARE, ROW, EXCLUSIVE, and UPDATE, which are grouped together, seemingly at random, for all 8 levels.  I find myself preferring to refer to them as just L1 through L8 rather than their actual names.

Are these names just the result of historical conventions that evolved organically in a way which doesn't really have any meaning besides "look at the docs and see what level each is?"
                                

DaleSwanson (113 rep)

Dec 7, 2022, 01:18 AM • Last activity: Dec 7, 2022, 08:33 AM

3 votes

1 answers

144 views

Error in the documentation MariaDB -> mysqlbinlog: `--rewrite-db` and `--database`

replication backup mariadb mysqlbinlog documentation

In the mysqlbinlog utility [documentation][1], the `--rewrite-db=name` option has the following explanation > Filtering (e.g. with --database=name ) happens **AFTER** the database rewrites have been performed. However, my tests show that the filtering with the `--database` option happens **BEFORE**...

In the mysqlbinlog utility documentation , the --rewrite-db=name option has the following explanation > Filtering (e.g. with --database=name ) happens **AFTER** the database rewrites have been performed. However, my tests show that the filtering with the --database option happens **BEFORE** the --rewrite-db option is applied. I reproduced the issue in the following way: I created a database db1 with a table t1 that contains one column c1. Binlog format = ROW.

CREATE DATABASE db1;
use db1;
CREATE TABLE t1 (c1 varchar(255));

Then I executed the following command:

FLUSH LOGS;

Made an insert into the table

INSERT INTO t1 (c1) VALUES (row in db1.t1);

Executed the command again

FLUSH LOGS;

I use the command below (the --vv parameter decrypts the commands) to check what is extracted from the binlogs:

mysqlbinlog mysql-bin.000033 --rewrite-db='db1->new_db_name' --database='new_db_name' -vv

Only comments are output, no INSERT commands. However, if I specify the name db1 in the --database parameter, then INSERT commands will be displayed:

mysqlbinlog mysql-bin.000033 --rewrite-db='db1->new_db_name' --database='db1' -vv

output:

...
...
BINLOG '
eI98YxMBAAAANgAAABwCAAAAADMAAAAAAAEAC25ld19kYl9uYW1lAAJ0MQABDwL8AwFyNVlH
eI98YxcBAAAAMQAAAE0CAAAAADMAAAAAAAEAAf/+DQByb3cgaW4gZGIxLnQxmq6ESw==
'/*!*/;
### INSERT INTO new_db_name.t1
### SET
###   @1='row in db1.t1' /* VARSTRING(1020) meta=1020 nullable=1 is_null=0 */
...
...

Thus, it turns out that mysqlbinlog first filters the binlog file with the --database parameter and only then makes replacements with the --rewrite-db parameter, which contradicts the documentation. I tested similar scenarios for MySQL(no MariaDB), and it works as it should, the filtering is run **AFTER** the replacements. Do I understand correctly that this is an error in the documentation? Or am I misunderstanding something?

Ivan (614 rep)

Nov 22, 2022, 11:19 AM • Last activity: Nov 23, 2022, 10:06 PM

1 votes

2 answers

3026 views

Is it possible to create a comment for a table in postgres? If not - how is a table typically documented in a similar manner to columns

postgresql documentation

It's possible to have documentation of a particular column within a postgres table, as it outlined here: https://dba.stackexchange.com/questions/160667/how-to-add-comment-to-a-column-in-a-table-in-postgres I'm not sure how to create a comment for a particular table though, and am interested in doing so _on_ the database (rather than in confluence or something similar). The only thing that I can think of is to create an additional table documentation__tables, or something similar, and with columns such as

table_name  |  description
--------------------------
...         | ...

Where table_name would match the name of the table from the schema. I'm not sure if there's a more idiomatic approach than this, or if there's a typical approach to documenting a table of the database within the database itself.

baxx (326 rep)

Nov 14, 2022, 03:52 PM • Last activity: Nov 14, 2022, 09:22 PM

0 votes

2 answers

557 views

How does SQL Server delayed durability work?

sql-server transaction-log documentation

I'm reading documentation for the [delayed durability feature][1] of SQL Server and saw this table: [![enter image description here][2]][2] It states that database level settings always take precedence over commit level seeing. But I'm not able to understand the table. As I understand, 2 is database...

                                  I'm reading documentation for the delayed durability feature  of SQL Server and saw this table:

It states that database level settings always take precedence over commit level seeing. But I'm not able to understand the table.

As I understand, 2 is database level setting and 3 must be commit level setting. But why their intersection position 4 states the result is delayed durable?

Could someone help me to understand how to read this table?

Fajela Tajkiya (1239 rep)

Aug 27, 2022, 07:35 PM • Last activity: Aug 28, 2022, 07:32 AM

244 votes

18 answers

123090 views

How do you document your databases?

sql-server documentation

I find that most of my clients are not documenting their databases at all and I find that pretty scary. To introduce some better practice, I would like to know what tools/process people are using. * How do you document your database? (SQL-Server) * What tool do you use? * Documentation Storage Forma...

                                  I find that most of my clients are not documenting their databases at all and I find that pretty scary. To introduce some better practice, I would like to know what tools/process people are using.

* How do you document your database? (SQL-Server) 
* What tool do you use? 
* Documentation Storage Format for database schema/meta-data?
 * Word documents
 * Excel spreadsheet
 * Plain Text
* Documentation process or policies?

I am not talking about reverse engineering / document a existing database, but mainly on the documentation best practices while you develop your system/database.
                                

user316 (951 rep)

Jan 11, 2011, 07:39 AM • Last activity: May 10, 2022, 04:11 AM

2 votes

2 answers

1854 views

Is there a postgres function documentation pattern that is similar in concept to javadoc?

postgresql functions documentation

I am mostly a Java developer. Today I am writing some postgres functions. Out of habit I keep trying to document these functions using a Javadoc-like comment style describing the function parameters and return type. Is there an official or widely supported pattern for documenting postgres functions?

                                  I am mostly a Java developer. Today I am writing some postgres functions. Out of habit I keep trying to document these functions using a Javadoc-like comment style describing the function parameters and return type. Is there an official or widely supported pattern for documenting postgres functions?
                                

Freiheit (249 rep)

Sep 5, 2019, 07:14 PM • Last activity: Mar 19, 2022, 08:52 PM

0 votes

2 answers

220 views

I think SQL Docs are wrong for sys.dm_db_index_physical_stats

sql-server index fragmentation documentation

Go to this documentation page: [https://learn.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sys-dm-db-index-physical-stats-transact-sql?view=sql-server-ver15][1] Then go to the index_level column explanation: > The nonleaf levels of indexes are only processed when mode...

                                  Go to this documentation page:
https://learn.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sys-dm-db-index-physical-stats-transact-sql?view=sql-server-ver15 

Then go to the index_level column explanation:

> The nonleaf levels of indexes are only processed when mode = DETAILED.

Now go to the Scanning Modes section:

> The LIMITED mode is the fastest mode and scans the smallest number of pages. For an index, only the parent-level pages of the B-tree (that is, the pages above the leaf level) are scanned. For a heap, the associated PFS and IAM pages are examined and the data pages of a heap are scanned in LIMITED mode.
> 
> With LIMITED mode, compressed_page_count is NULL because the Database
> Engine only scans non-leaf pages of the B-tree and the IAM and PFS
> pages of the heap.

The scanning modes section is wrong, right? Limited mode only scans leaf pages.
Let me know your thoughts. I wanted to check with the community before posting it to the proper channels.
                                

Victor Barajas (363 rep)

Nov 9, 2021, 01:05 AM • Last activity: Nov 10, 2021, 08:10 AM

0 votes

1 answers

85 views

What does "through another column" mean in the MySQL docs?

mysql documentation

In the MySQL doc section on [avoiding full table scans](https://dev.mysql.com/doc/refman/5.6/en/table-scan-avoidance.html), one of the cases in which MySQL will use a full table scan is described like so: > You are using a key with low cardinality (many rows match the key value) through another colu...

                                  In the MySQL doc section on [avoiding full table scans](https://dev.mysql.com/doc/refman/5.6/en/table-scan-avoidance.html) , one of the cases in which MySQL will use a full table scan is described like so:

> You are using a key with low cardinality (many rows match the key value) through another column. In this case, MySQL assumes that by using the key it is likely to perform many key lookups and that a table scan would be faster. 

I am having trouble understanding this.

For a starter, I'm not sure if I should parse the phrase as "using a key ... through another column" or "low cardinality ... through another column."  Neither interpretation seems clear.

I can get the general idea that if I'm selecting a huge proportion of the table, like 75% or whatever, then the index will be slower to use (because of the dives to get the rows) than just reading the whole table.  But I don't get what "through another column" has to do with it.

Can anyone explain this sentence?

Wildcard (587 rep)

Oct 7, 2021, 07:37 PM • Last activity: Oct 7, 2021, 08:38 PM

0 votes

1 answers

62 views

How to organise and understand a terribly modelled database

oracle database-design documentation

I have recently joined a company where there are hundreds of schemas and thousands of tables. All the schemas we have are from other vendors who have allowed us access to their database. The entire database is poorly documented (little to no documentation). Vendors also don't care for documentation,...

                                  I have recently joined a company where there are hundreds of schemas and thousands of tables. All the schemas we have are from other vendors who have allowed us access to their database. The entire database is poorly documented (little to no documentation). Vendors also don't care for documentation, hence they don't provide us with any documentation.

The other employees themselves find majority of their time figuring out what the data represents and if the data is current. There are numerous front end tools (not built by our company) which allow us to pull data (which is correct and relevant). However, then the issue becomes that we don't know the schemas, tables or columns the front end is using to pull the data.

I cannot create new tables as the tables are owned by 3rd parties who have allowed us access to their database. How can I go about documentation and how do I figure out what schemas, tables and columns front end tools are using to pull data.

Tables don't even have foreign keys to figure out how they are linking to other tables. For example in tableA there may be a column called car_id, but in tableB there is a column called cid (which is the car_id). They both link together.

Paul Shan (1 rep)

May 5, 2021, 05:34 AM • Last activity: May 5, 2021, 12:20 PM

2 votes

2 answers

124 views

Where is the documentation for version 1.0 of PostgreSQL wire protocol

postgresql documentation protocol

The current [documentation][1] refers only to version 3 of the wire protocol. However, there is the following note in the documentation: > For descriptions of the earlier protocol versions, see previous releases of the PostgreSQL documentation However, I can't find anything about previous versions o...

                                  The current documentation  refers only to version 3 of the wire protocol. 

However, there is the following note in the documentation: 

>  For descriptions of the earlier protocol versions, see previous releases of the PostgreSQL documentation 

However, I can't find anything about previous versions of the wire protocol.

Eyal leshem (145 rep)

Apr 21, 2021, 06:24 PM • Last activity: Apr 22, 2021, 06:03 AM

14 votes

1 answers

1869 views

Is this an error in SQL Server's documentation for ROLLBACK?

sql-server transaction rollback documentation

[This][1] is the SQL Server's document talking about the `ROLLBACK` statement. On that page it states that the syntax of it is as follows: ROLLBACK { TRAN | TRANSACTION } [ transaction_name | @tran_name_variable | savepoint_name | @savepoint_variable ] [ ; ] Apparently the stuff inside square bracke...

                                  This  is the SQL Server's document talking about the ROLLBACK statement. On that page it states that the syntax of it is as follows:

    ROLLBACK { TRAN | TRANSACTION }   
         [ transaction_name | @tran_name_variable  
         | savepoint_name | @savepoint_variable ]   
    [ ; ]  

Apparently the stuff inside square brackets are optional and it seems you should choose one from TRAN or TRASACTION. But in fact you can completely omit both and ROLLBACK is a perfectly valid statement. Is this an error or intentionally?
                                

Just a learner (2082 rep)

Apr 11, 2021, 08:54 AM • Last activity: Apr 13, 2021, 12:42 PM

0 votes

1 answers

633 views

Mysql InnoDB Full-Text Search IN BOOLEAN MODE Sort Rows In Order of Decreasing Relevance

mysql innodb full-text-search documentation

In the official documentation of [Boolean Full-Text Searches][1]: > Boolean full-text searches have these characteristics: > > - They do **not** automatically sort rows in order of decreasing relevance. However, when running full-text searches `IN BOOLEAN MODE` (mysql v8.0.23), they are sorted by re...

In the official documentation of Boolean Full-Text Searches : > Boolean full-text searches have these characteristics: > > - They do **not** automatically sort rows in order of decreasing relevance. However, when running full-text searches IN BOOLEAN MODE (mysql v8.0.23), they are sorted by relevance in order of decreasing relevance without having to use ORDER BY score

SELECT product_category_id,
MATCH(product.barcode, product.brand_name, product.generic_name) AGAINST('paracetamol*' IN BOOLEAN MODE) as score
FROM product
WHERE 
MATCH(product.barcode, product.brand_name, product.generic_name) AGAINST('paracetamol*' IN BOOLEAN MODE);

Result:

+---------------------+--------------------+
| product_category_id | score              |
+---------------------+--------------------+
|                  97 | 3.4546611309051514 |
|                  91 | 3.4546611309051514 |
|                  91 | 3.4546611309051514 |
|                  91 | 3.4546611309051514 |
|                 123 | 3.4546611309051514 |
|                 123 | 3.4546611309051514 |
|                  91 | 3.4546611309051514 |
|                  91 | 1.7273305654525757 |
|                  97 | 1.7273305654525757 |
|                  97 | 1.7273305654525757 |

What have I missed here?

Yousif (27 rep)

Mar 23, 2021, 08:10 PM • Last activity: Mar 24, 2021, 12:42 AM

3 votes

1 answers

100 views

Are there best practices or standardized syntax for writing transformation logic?

data-warehouse etl business-intelligence documentation

This may not be the appropriate forum, but its the closest I could find. I write a lot of source to target mappings for work. These documents just describe the operations/logic/transformations/etc. needed to get the data from A to B. I find myself phrasing the same thing different ways, or sometimes...

                                  This may not be the appropriate forum, but its the closest I could find.  I write a lot of source to target mappings for work.  These documents just describe the operations/logic/transformations/etc. needed to get the data from A to B.  I find myself phrasing the same thing different ways, or sometimes the descriptions get convoluted if the logic is complex.  

Are there any best practices for writing the transformation logic for relational databases?  For example, how to say 'Join tables A,B, and C for each combination of fields D,E, and F select the maximum value between fields G and H.'  I feel that somebody has probably standardized the natural language for relational database transformation logic, but I can't find anything to that effect.

SG187 (31 rep)

Jun 26, 2015, 05:01 PM • Last activity: Jan 2, 2021, 02:05 AM

Showing page 1 of 20 total questions