Tag Info

Hot answers tagged

8

As far as I understand it you are not actually talking about an UPSERT here just combining two different CRUD operations in one stored procedure. CREATE PROC InsertOrUpdateYourTable @Id int = NULL OUTPUT, @Foo INT, @Bar VARCHAR(10) AS IF @Id IS NULL BEGIN INSERT INTO ...


5

For example, take a table T with an index on attributes a and b. Is the index useful for the query: select * from T where a='foo' There are two questions there: Can an index on (a,b) be used at all for this query? The answer to that is generally yes. Maybe not all databases have that capacity, but most mainstream ones do AFAIK. Replace the select ...


3

If you do want to use one procedure, you could use the MERGE statement. Sample merge code: create table testo(Id int identity(1,1) NOT NULL, somechar char(1), someint int, AddedTime datetime2(0), LastModifiedTime datetime2(0)); alter table testo add constraint [PK_testo] primary key(Id); --Clustered index on target table. --No index necessary on the ...


3

Rows in postgresql have no fixed order. Not only are records placed where ever there is free space records can also move around. This is because when a row is updated a new version of the row is created in a new location while the old version continues to live in it's old location until it's removed by vacuum. A CLUSTER operations sorts all rows but does ...


3

Yes, but it depends on which index key you search by. Think of it like one of those old "white pages" phone books. In a phone book, people are ordered on the pages in the order LastName, FirstName. That means there are two components to the phone book's index. If you're looking for all of the people with the last name of "Smith", you just find the ...


3

Technically speaking, there is no single answer for "MySQL" itself, because MySQL has a pluggable storage engine architecture and the server itself is essentially unconcerned with and unaware of how the physical storage of data is accomplished. Those details are handled by the storage engines. The most relevant storage engine in MySQL is InnoDB. Can ...


2

PostgreSQL experience. We have table with 3 columns table test with columns id, first, second then we have two indexes first_second_idx (first, second) first_idx (first) then if we use statement (1) select * from test where first = 'whatever'; query planner should use index first_idx ( 'whatever' value is for example 5% of all records). It's ...


2

UPDATED version based on feedback: CREATE TABLE group ( group_id INT NOT NULL AUTO_INCREMENT, name VARCHAR(255), PRIMARY KEY(team_id) ); CREATE TABLE player ( player_id INT NOT NULL AUTO_INCREMENT, last_name VARCHAR(50), first_name VARCHAR(50), group_id INT, player_rank INT, PRIMARY KEY(player_id) ); CREATE TABLE game ( ...


2

It sounds like you're looking at something similar to, if not identical to, a multi-tenant architecture. In a multi-tenant architecture, all the tenants (clients, customers) share something. They might share only the server and the dbms, with each tenant getting a private database. Or they might share the database, with each tenant getting a private ...


2

The way MySQL stores data depends on storage engine. A concept of a page exists in InnoDB while in MyISAM there are blocks. In InnoDB a page is fixed 16k while a block in MyISAM is of fifferent size depending on type of the block and the size of the record in it. InnoDB stores tables in B+ tree data structures that they call indexes. The table itself ...


1

Currently I am running my business on 2x SQL servers which have 38 GB of ram at all As well as the replication strategy to improve availability the though the replica taking over if the primary fails, you must factor in a god backup strategy - in what follows I'm going to assume that you already have this (if not, you need to factor it into your ...



Only top voted, non community-wiki answers of a minimum length are eligible