Say that I create a table that that has a random uuid for a primary key. Is there typically a mechanism inside of the database to gracefully deal with collisions? How does it work? My mind is picturing something simple like "generate a key and check if it exists, and generate another one if it does exist." But if you have a simple non-sequential key, you could end up in this collision loop for quite some time.

I am confused about the InnoDB Engine. In the original document, it states that InnoDB uses a B Tree. However, in some other articles, they claim that InnoDB uses a B+ Tree. - https://dev.mysql.com/doc/refman/8.3/en/innodb-physical-structure.html - https://blog.jcole.us/2013/01/10/btree-index-structures-in-innodb/ - https://iorilan.medium.com/why-does-innodb-engine-use-the-b-tree-data-structure-for-indexing-6c4a41f6f356 Can you help me understand my confusion? I'm not sure whether the InnoDB Engine is a B Tree or a B+ Tree. I apologize if I've misunderstood it.

I have a Postgres table with a large number of indexed columns (roughly 100 indexed columns total, and yes, I need them all, and yes, they all need to be separately indexed). Any row update causes all indexes to be updated, which is a lot of work for the DB engine. I want to understand the concurrency implications of the discussion on the Postgres documentation page titled Index Locking Considerations , and also the fact that Postgres is single-threaded (multi-process) , in terms of how the current design affects reader and writer performance for a large number of concurrent queries, given that I have so many column indices. My interpretation of these things are the following (please correct any that are wrong): * Writers that are updating individual rows don't block readers, unless the reader is running a query that produces a result set that would include the row that is being updated. * Writers only block each other if they are trying to update the same row at the same time. * Concurrent updates to btree-based indices from multiple writers get merged according to a set of rules that generally does the right thing (so updating the same indexes at the same time does not cause writers to block, unless they are updating the same row). My questions are: * How can there even be multiple concurrent readers or writers, if Postgres is single-threaded? If you have multiple processes running, do they simply rely on the inter-process consistency of disk caches (or have to manually flush contents to disk) to coordinate concurrent updates? * What if anything can get blocked while a large number of indexes are being updated due to a row update? If anything can get blocked during an update, is it possible to turn a dial on the consistency-vs-availability tradeoff so that, for example, a row update is not atomic (i.e. so that the indexes are updated one at a time, but the update to all indexes doesn't have to happen atomically)? I'm OK with a lack of consistency in the name of higher concurrency.

When I connect to a server via SSMS, I can connect fine albeit a slow. When I click on a new query or try to run a view in a database on this server, the same prompt comes up but with everything greyed out and then after a while the prompt disappears and the view runs or new query tab opens: It's like SSMS is trying to connect to the server again every time I run something or even when clicking new query. How do I stop this prompt appearing?

I was wondering what is the best practice for drives and controllers when you are installing multiple Sql instances engines. Are the similar pros and cons as in having different drives and controllers for data and log disk? E.g having 10 instances engines residing on same D: disk with one controller or should you split them on different drives with own/shared controllers?

I have gone through the Wikipedia definition several times: > A database engine (or "storage engine") is the underlying software component that a database management system (DBMS) uses to **create, read, update and delete** (CRUD) data from a database. What I don't understand is what is left to do, isn't CRUD all that the databases do? **If the database engine performs these functions, what does the rest of the database do?**

I want to swap values of two columns in a table, And I found that in SQL we can do that by using Update:

update the_table set first_name = last_name, last_name = first_name;

It works But I wonder How SQL can do that without **overwrite** data in a column of other column?

This article describes the File System permissions that are needed for the SQL Server Database Engine to access any folder where database files are stored. My organization has dozens of database servers, each of which are storing files in a varying number of local attached drives. Is there an automated SQL (or PowerShell) script I can run that will return my ALL of the folders that do (or don't) have Full Control permissions for the NT SERVICE\MSSQLSERVER SID? I have already tried using Test-DbaPath from DBATools. This is returning results if the folder exists, however it is not letting me know if the SID has Full Control.

"Fundamental of Database Systems", 3rd ed. by Elmasri and Navathe, page 136 says: "We can physically order the records of a file on disk based on the values of one of their fields [...]" How could this "physical ordering" possibly enforced, especially with regard to fragmentation? Most databases use system calls to store their data in the file system of the OS, only very few are capable of doing raw i/o.

So today a professor told us that when the database has to make an update, internally (at low level) it makes a delete and then an insert with the updated fields. He then said that this is something made across all databases and then I started a discussion telling that I thought that it had no sense but I didn't had enough resources to support my position. He seems to know a lot but I can't understand why would dbs do that. I mean, I know that if you update a field and you need more space for that row, then it may delete the row physically and put it at the end with the new data. But if for example you reduce the space used, why would it delete and re insert it at the end? Is this even true? What are the benefits?

I'm seeking a bit of clarification around SQL Azure versions and upgrades. Upon trying to use some specific functionality it came to my attention that the compatibility level of the database was 100 (I needed 130). Upon looking a bit further I see that the engine version is reported as 12.0.2000.8 (equivalent to SQL Server 2014), but I need to be at a minimum engine version of 13.x in order to use compatibility level 130. What do I need to do to upgrade the engine version for this SQL Azure instance? The article *Hot patching SQL Server Engine in Azure SQL Database* specifically states: > Azure SQL Database is evergreen, meaning that it always has the latest version of the SQL Engine Additionally, answers like this one imply that the engine will always be v.latest but the versions reported will be incorrect. Does this mean that I can ignore the reported engine version and go ahead with setting the compatibility level, or are there other steps I need to take to upgrade the engine?

I'm writing some test code that is required to connect to a local SQL Server instance (SQL Server 2017 - Developer Edition) by IP address. I have the SQL Server instance up and running on my machine, but can't figure out the correct IP + Port to use for my connection string's Server property. As a starting point, I'm trying to at least manually connect to my server instance with SQL Server Management Studio (via the IP address) but I can't seem to figure it out. 1. How do I find the IP Address and Port of my local SQL Server instance? 2. What is the correct format I should be inputting the IP Address and Port into the Server Name field of SSMS (and is it the same format as a connection string Server property)? I've tried locating the IP address executing the following SQL code on it: SELECT CONNECTIONPROPERTY('net_transport') AS net_transport, CONNECTIONPROPERTY('protocol_type') AS protocol_type, CONNECTIONPROPERTY('auth_scheme') AS auth_scheme, CONNECTIONPROPERTY('local_net_address') AS local_net_address, CONNECTIONPROPERTY('local_tcp_port') AS local_tcp_port, CONNECTIONPROPERTY('client_net_address') AS client_net_address But the local_net_address and local_tcp_port fields are NULL and the client_net_address is set to ``: I've also tried going into the SQL Server Configuration Manager, under the SQL Server Network Configuration section, and enabling TCP/IP. Then I tried using a mix of the IP4 and IP6 addresses with Port 1433 (which is blank by default in the TCP/IP Properties, but I've tried leaving it blank and setting it to 1433) and that didn't work. (I made sure to restart the SQL Server service and SQL Server Browser service every time I made changes in here.) I've received the following errors when trying to connect via SSMS for the different combinations of the IP4 address with and without Port specified: **No Port Specified:** **Port Specified w/ Colon:** **Port Specified w/ Comma:** I've also tried connecting via 127.0.0.1 with the same combinations of Port 1433 above to no avail. (I receive similar errors except for the following combinations.) **127.0.0.1 w/o Port:** **127.0.0.1 w/ Port via comma:**

I want to decentralize my databases and upload it to Sia Skynet to give the users the ability to search on front-end. It is important to be able to deal with large amounts of data, but the RAM-based in-browser SQL engines (MiniSearch, AlaSQL, etc) are limited. Yeaterday I made a demo: I sorted and partitioned my key-value database into 1000 line files and I made an index for the files also. You can try out my demo here , or check it out on GitHub So my question is that are there any better solutions for large-in-browser databases? (I don't really want to reinvent the wheel 😆)

In DB2, there is this command that shows active EDUs (engine dispatchable units): db2pd -edus There are two fields in the respective output: EDU ID and TID. According to [this db2pd page from the DB2 knowledge center](https://www.ibm.com/support/knowledgecenter/SSEPGG_10.5.0/com.ibm.db2.luw.admin.cmd.doc/doc/r0011729.html#r0011729__pdedus) , each is defined as follows: > **EDU ID:** The unique identifier for the engine dispatchable unit (EDU). Except on Linux operating systems, the EDU ID is mapped to the thread ID. On Linux operating system the EDU ID is a DB2 generated unique identifier > > **TID**: Thread identifier. Except on Linux operating systems, the thread ID is the unique identifier for the specific thread. On Linux operating systems, this is a DB2 generated unique identifier I wanted to know what is considered EDU or thread on a Linux/Unix operating system? Are they the same? What is the difference?

I read a description from When to Use MEMORY or NDB Cluster from the MySQL website. > Operations involving transient, non-critical data such as session > management or caching. When the MySQL server halts or restarts, the > data in MEMORY tables is lost. but my created tables with ENGINES = MEMORY were not dropped upon mysql server restart. CREATE TABLE MY_TEMPORARY_TABLE ( COLUMN_1 varchar(255) DEFAULT NULL, COLUMN_2 varchar(255) DEFAULT NULL, COLUMN_3 varchar(255) DEFAULT NULL ) ENGINE=MEMORY DEFAULT CHARSET=utf8;

my question is about speed when perform service mysql start. with many tables (some not often used). what engines consumes more or less start time ? example: innodb --> the more tables, the more time it takes for mysql to start because it has to "load" indexes at start... but how is it with csv engine (no indexes) ; how mysql with "deal" with those tables during a mysql start ? Thanks (from Comment) I don't use innodb. I have some aria tables, tokudb tables and rocksdb tables.

Supposing I've started a new project that needs a database for persistence purposes. What are common practices to determine which database technology (MSSQL, MySQL, MongoDB, etc...) would best fit the requirements of the project? What are important considerations to keep in mind when looking for a proper database technology?

I am using SSMS to connect to DB Engine Query for DAC. I've seen some video tutorials where people use 'admin:' prefix before server name while connecting When trying to connect with that prefix I get an error shown in the picture above. I've checked that SQL Server Browser is running. Also I am running SSMS as an administrator and while Named Pipes protocol is disabled I've changed options to accept TCP/IP as a default protocol. What is the use of that prefix and how to avoid this error? Thanks for your time!

In one application, we have an audit table that uses the ArchiveEngine in conjunction with a few triggers to capture 100% of the changes that occur. The upside to this is that 20+ million rows occupies 145MB, not to mention that ArchiveEngine only supports INSERT and SELECT, rendering the trail immutable. The downside comes when you want to access that data in order to give the user audit information. Since the data is stored in compressed form and there are no indices, selection time can be ridiculous: mysql> select * from AUDIT_LOG WHERE Table_Name = 'myTable' AND Row_ID = 9024; +------------+--------+-------------+---------------------+---------------------+----------------+-------------+---------------------+ | Table_Name | Row_ID | Field_Name | Old_Value | New_Value | DB_User | modified_by | date_modified | +------------+--------+-------------+---------------------+---------------------+----------------+-------------+---------------------+ | myTable | 9024 | Flags | 0 | 1 | user@localhost | 826 | 2011-06-10 22:05:11 | | myTable | 9024 | Status | Unavailable | Pending | user@localhost | 826 | 2011-07-08 22:41:45 | | myTable | 9024 | Status | Pending | Processing | user@localhost | 826 | 2011-07-08 22:41:49 | | myTable | 9024 | Status | Processing | Calculated | user@localhost | 826 | 2011-07-08 22:41:54 | | myTable | 9024 | Fee_Paid | NULL | 185.00 | user@localhost | 826 | 2011-07-08 22:41:54 | | myTable | 9024 | Status | Calculated | Approved | user@localhost | 826 | 2011-07-08 22:47:04 | | myTable | 9024 | Approved | NULL | 2011-07-08 22:47:06 | user@localhost | 826 | 2011-07-08 22:47:04 | | myTable | 9024 | Approved_TZ | America/Yellowknife | America/Vancouver | user@localhost | 826 | 2011-07-08 22:47:04 | | myTable | 9024 | Approved_By | NULL | 826 | user@localhost | 826 | 2011-07-08 22:47:04 | | myTable | 9024 | Invoice_ID | NULL | 2372 | user@localhost | 825 | 2011-10-17 19:32:59 | +------------+--------+-------------+---------------------+---------------------+----------------+-------------+---------------------+ **10 rows in set (13.54 sec)** After conversion to InnoDB and adding a PK and indexing Row_ID and Table_Name, this same operation takes roughly 1/10th of a second. What I'm considering is a leaving the triggers + Archive, and then using a cron job to mirror the Archive in InnoDB. - Are there other ways this has been dealt with? - Any drawbacks or trade-offs that are apparent?

I want to check what type of sql is running on a Datasase server that I can access. I only have access to a web interface and a list of tables. Through the interface I can run queries on the tables that are present on a list. How can I get more information about the server and the version that the server is running. I have no idea about the IP or the PORT that the server is running. I want to know if the server is MySQL, Mircosoft SQL Server, Oracle SQL, Postgre SQL or other sql server. The website that I am talking about is this one: [w3schools.com SQL editor](http://www.w3schools.com/sql/trysql.asp?filename=trysql_select_all) . EDIT 2: although for some the command select sqlite_version() works for me it does not work. This is the screenshot of the response. EDIT 3 : On Chromium Browser the command is working properly. However on Firefox Browser the command did not work. I also mention that I am on running Linux. What do you think could be the reason why on Firefox and on Chrome I get different results ?