Why are queries parsed in such a way that disallows the use of column aliases in most clauses?

Summary

There's no logical reason it couldn't be done, but the benefit is small and there are some pitfalls that may not be immediately apparent.

Research Results

I did some research and found some good information. The following is a direct quote from a reliable primary source (that wishes to remain anonymous) at 2012-08-09 17:49 GMT:

  When SQL was first invented, it had no aliases in the SELECT clause.
  This was a serious shortcoming that was corrected when the language
  was standardized by ANSI in about 1986.

  The language was intended to be "non-procedural"--in other words, to
  describe the data that you want without specifying how to find it. So,
  as far as I know, there's no reason why an SQL implementation couldn't
  parse the whole query before processing it, and allow aliases to be
  defined anywhere and used everywhere. For example, I don't see any
  reason why the following query shouldn't be valid:

select name, salary + bonus as pay
from employee
where pay > 100000

  Although I think this is a reasonable query, some SQL-based systems
  may introduce restrictions on the use of aliases for some
  implementation-related reason. I'm not surprised to hear that SQL
  Server does this.

I am interested in further research into the SQL-86 standard and why modern DBMSes don't support alias reuse, but haven't had the time to get very far with it yet. For starters, I don't know where to get the documentation or how to find out who exactly made up the committee. Can anyone help out? I also would like to know more about the original Sybase product that SQL Server came from.

From this research and some further thought, I have come to suspect that using aliases in other clauses, while quite possible, simply has never been that high a priority for DBMS manufacturers compared to other language features. Since it is not that much of an obstacle, being easily worked around by the query writer, putting effort into it over other advancements is not optimal. Additionally, it would be proprietary as it is obviously not part of the SQL standard (though I'm waiting to find out more on that for sure) and thus would be a minor improvement, breaking SQL compatibility between DBMSes. By comparison, CROSS APPLY (which is really nothing more than a derived table allowing outer references) is a huge change, that while proprietary offers incredible expressive power not easily performed in other ways.

Problems With Using Aliases Everywhere

If you allow SELECT items to be put in the WHERE clause, you can not only explode the complexity of the query (and thus the complexity of finding a good execution plan) it is possible to come up with completely illogical stuff. Try:

SELECT X + 5 Y FROM MyTable WHERE Y = X

What if MyTable already has a column Y, which one is the WHERE clause referring to? The solution is to use a CTE or a derived table, which in most cases should cost no extra but achieves the same final end result. CTEs and derived tables at least enforce the resolution of ambiguity by allowing an alias to be used only once.

Also, not using aliases in the FROM clause makes eminent sense. You can't do this:

SELECT
   T3.ID + (SELECT Min(Interval) FROM Intervals WHERE IntName = 'T') CalcID
FROM
   Table1 T
   INNER JOIN Table2 T2
      ON T2.ID = CalcID
   INNER JOIN Table3 T3
      ON T2.ID = T3.ID

That's a circular reference (in the sense that T2 is secretly referring to a value from T3, before that table has been presented in the JOIN list), and darn hard to see. How about this one:

INSERT dbo.FinalTransaction
SELECT
   newid() FinalTransactionGUID,
   'GUID is: ' + Convert(varchar(50), FinalTransactionGUID) TextGUID,
   T.*
FROM
   dbo.MyTable T

How much do you want to bet that the newid() function is going to be put into the execution plan twice, completely unexpectedly making the two columns show different values? What about when the above query is used N levels deep in CTEs or derived tables. I guarantee that the problem is worse than you can imagine. There are already serious inconsistency problems about when things are evaluated only once or at what point in a query plan, and Microsoft has said it will not fix some of them because they are expressing query algebra properly--if one gets unexpected results, break the query up into parts. Allowing chained references, detecting circular references through potentially very long such chains–these are quite tricky problems. Introduce parallelism and you've got a nightmare in the making.

Note: Using the alias in WHERE or GROUP BY isn't going to make a difference to the problems with functions like newid() or rand().

A SQL Server way to create reusable expressions

CROSS APPLY/OUTER APPLY is one way in SQL Server to create expressions that can be used anywhere else in the query (just not earlier in the FROM clause):

SELECT
   X.CalcID
FROM
   Table1 T
   INNER JOIN Table3 T3
      ON T.ID = T3.ID
   CROSS APPLY (
      SELECT
         T3.ID + (SELECT Min(Interval) FROM Intervals WHERE IntName = 'T') CalcID
   ) X
   INNER JOIN Table2 T2
      ON T2.ID = X.CalcID

This does two things:

Makes all expressions in the CROSS APPLY get a "namespace" (a table alias, here, X) and be unique within that namespace.
Makes it obvious everywhere not only that CalcID is coming from X, but also makes it obvious why you can't use anything from X when joining table T1 and T3, because X hasn't been introduced yet.

I'm actually quite fond of CROSS APPLY. It has become my faithful friend, and I use it all the time. Need a partial UNPIVOT (which would require a PIVOT/UNPIVOT or UNPIVOT/PIVOT using native syntax)? Done with CROSS APPLY. Need a calculated value that will be reused many times? Done. Need to rigidly enforce execution order for calls over a linked server? Done-with a screaming improvement in speed. Need only one type of row split to 2 rows or with extra conditions? Done.

So at the very least, in DBMS SQL Server 2005 and up, you have no further cause for complaint: CROSS APPLY is how you DRY in the way you are wanting.


SQL Server Snapshot Isolation Level: How to create an Update Conflict?

I am using SQL Server version:

Microsoft SQL Server 2017 (RTM-CU1) (KB4038634) - 14.0.3006.16 (X64)

Run your scripts in this order and you will be able to simulate a Update Conflict.

Connection 1

CREATE DATABASE FoodDatabase;
GO
ALTER DATABASE FoodDatabase SET ALLOW_SNAPSHOT_ISOLATION ON;
GO

USE [FoodDatabase];
GO

CREATE TABLE dbo.food
(
    FoodId int PRIMARY KEY IDENTITY(1,1),
    FoodDesc varchar(255) null,
);
GO

INSERT INTO dbo.food
VALUES ('broccoli'), ('strawberry');
GO

--Transaction Session 1:
SET TRANSACTION ISOLATION LEVEL SNAPSHOT
BEGIN TRANSACTION -- do not commit transaction yet
UPDATE dbo.Food
SET FoodDesc = 'Grape' WHERE FoodId = 1;

Output:

  (2 rows affected)

  (1 row affected)

Connection 2

--Transaction Session 2:
USE [FoodDatabase];
GO
SET TRANSACTION ISOLATION LEVEL SNAPSHOT
BEGIN TRANSACTION -- do not commit transaction yet
UPDATE dbo.Food
SET FoodDesc = 'Lettuce' WHERE FoodId = 1;

Session from connection 2 is blocked by connection 1. You can see details about the lock by using sp_whoIsActive written by Adam Machanic.

<lock_type>keylock</lock_type>
    <database_name>FoodDatabase</database_name>
    <hobt_id>72057594043105280</hobt_id>
    <schema_name>dbo</schema_name>
    <object_name>food</object_name>

Go back to Connection 1 window and run a COMMIT statement. You will see following error message in Connection 2 messages tab.

  Msg 3960, Level 16, State 3, Line 6 Snapshot isolation transaction
  aborted due to update conflict. You cannot use snapshot isolation to
  access table 'dbo.food' directly or indirectly in database
  'FoodDatabase' to update, delete, or insert the row that has been
  modified or deleted by another transaction. Retry the transaction or
  change the isolation level for the update/delete statement.

Helpful resource:

Understanding the Available Transaction Isolation Levels


Understanding "bitmap heap scan" and "bitmap index scan"

How does PostgreSQL knows by just a bitmap anything about rows' physical order?

The bitmap is one bit per heap page. The bitmap index scan sets the bits based on the heap page address that the index entry points to.

So when it goes to do the bitmap heap scan, it just does a linear table scan, reading the bitmap to see whether it should bother with a particular page or seek over it.

  Or generates the bitmap so that any element of it can be mapped to the pointer to a page easily?

No, the bitmap corresponds 1:1 to heap pages.

I wrote some more on this here.

OK, it looks like you might be misunderstanding what "bitmap" means in this context.

It's not a bit string like "101011" that's created for each heap page, or each index read, or whatever.

The whole bitmap is a single bit array, with as many bits as there are heap pages in the relation being scanned.

One bitmap is created by the first index scan, starting off with all entries 0 (false). Whenever an index entry that matches the search condition is found, the heap address pointed to by that index entry is looked up as an offset into the bitmap, and that bit is set to 1 (true). So rather than looking up the heap page directly, the bitmap index scan looks up the corresponding bit position in the bitmap.

The second and further bitmap index scans do the same thing with the other indexes and the search conditions on them.

Then each bitmap is ANDed together. The resulting bitmap has one bit for each heap page, where the bits are true only if they were true in all the individual bitmap index scans, i.e. the search condition matched for every index scan. These are the only heap pages we need to bother to load and examine. Since each heap page might contain multiple rows, we then have to examine each row to see if it matches all the conditions - that's what the "recheck cond" part is about.

One crucial thing to understand with all this is that the tuple address in an index entry points to the row's ctid, which is a combination of the heap page number and the offset within the heap page. A bitmap index scan ignores the offsets, since it'll check the whole page anyway, and sets the bit if any row on that page matches the condition.

Graphical example

Heap, one square = one page:
+---------------------------------------------+
|c____u_____X___u___X_________u___cXcc______u_|
+---------------------------------------------+
Rows marked c match customers pkey condition.
Rows marked u match username condition.
Rows marked X match both conditions.

Bitmap scan from customers_pkey:
+---------------------------------------------+
|100000000001000000010000000000000111100000000| bitmap 1
+---------------------------------------------+
One bit per heap page, in the same order as the heap
Bits 1 when condition matches, 0 if not

Bitmap scan from ix_cust_username:
+---------------------------------------------+
|000001000001000100010000000001000010000000010| bitmap 2
+---------------------------------------------+

Once the bitmaps are created a bitwise AND is performed on them:

+---------------------------------------------+
|100000000001000000010000000000000111100000000| bitmap 1
|000001000001000100010000000001000010000000010| bitmap 2
 &&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&
|000000000001000000010000000000000010000000000| Combined bitmap
+-----------+-------+--------------+----------+
            |       |              |
            v       v              v
Used to scan the heap only for matching pages:
+---------------------------------------------+
|___________X_______X______________X__________|
+---------------------------------------------+

The bitmap heap scan then seeks to the start of each page and reads the page:

+---------------------------------------------+
|___________X_______X______________X__________|
+---------------------------------------------+
seek------->^seek-->^seek--------->^
            |       |              |
            ------------------------
            only these pages read

and each read page is then re-checked against the condition since
there can be >1 row per page and not all necessarily match
the condition.


Reducing Memory Pressure When Performing Parallel Staging Table Loads

If you take a look at my answer over here, I mention a new(ish) query hint called MAX_GRANT_PERCENT that you can use to further control memory grants at the query level.

Documentation is here.

Assuming queries are in the default pool and you haven't altered it, right now they can ask for 25% of your 768 GB of RAM. That's a lot of memory.

You'll have to play with reducing the percentage until you find a balance between concurrency and not spilling to disk-ency.

Hope this helps!


How do I get the execution plan for a view?

Use the information_schema.views table
This will generate the EXPLAIN for all views
mysql -uroot -p -AN -e"select concat('explain ',view_definition) from information_schema.views" > /root/ExplainViews.sql

This will generate the EXPLAIN for all views in the mydb database
mysql -uroot -p -AN -e"select concat('explain ',view_definition) from information_schema.views where table_schema = 'mydb'" > /root/ExplainViews.sql

Give it a Try !!!
UPDATE 2012-03-22 11:30 EDT
@MattFenwick, your answer is a whole lot simpler than mine. Here is an example I tried out on my PC running MySQL 5.5.12. I ran EXPLAIN on both the SELECT version from your answer and the EXPLAIN generated from my answer:
mysql> explain select * from bigjoin;
+----+-------------+-------+--------+---------------+---------+---------+---------------+------+-------------+
| id | select_type | table | type   | possible_keys | key     | key_len | ref           | rows | Extra       |
+----+-------------+-------+--------+---------------+---------+---------+---------------+------+-------------+
|  1 | SIMPLE      | k     | index  | NULL          | PRIMARY | 4       | NULL          |   14 | Using index |
|  1 | SIMPLE      | a     | eq_ref | PRIMARY       | PRIMARY | 4       | test.k.id_key |    1 | Using index |
|  1 | SIMPLE      | b     | ALL    | NULL          | NULL    | NULL    | NULL          |    4 |             |
+----+-------------+-------+--------+---------------+---------+---------+---------------+------+-------------+
3 rows in set (0.00 sec)

mysql> explain select `a`.`id_key` AS `id_key1`,`b`.`id_key` AS `id_key2` from ((`test`.`idlist` `k` left join `test`.`id_key_table` `a` on((`k`.`id_key` = `a`.`id_key`))) left join `test`.`new_keys_to_load` `b` on((`k`.`id_key` = `b`.`id_key`)));
+----+-------------+-------+--------+---------------+---------+---------+---------------+------+-------------+
| id | select_type | table | type   | possible_keys | key     | key_len | ref           | rows | Extra       |
+----+-------------+-------+--------+---------------+---------+---------+---------------+------+-------------+
|  1 | SIMPLE      | k     | index  | NULL          | PRIMARY | 4       | NULL          |   14 | Using index |
|  1 | SIMPLE      | a     | eq_ref | PRIMARY       | PRIMARY | 4       | test.k.id_key |    1 | Using index |
|  1 | SIMPLE      | b     | ALL    | NULL          | NULL    | NULL    | NULL          |    4 |             |
+----+-------------+-------+--------+---------------+---------+---------+---------------+------+-------------+
3 rows in set (0.00 sec)

mysql>

They both produced the same EXPLAIN plan. I will change my answer to implement your way. You get a +1 from me although it's +2 for simplicity. You should go ahead and accept your own answer on this one.
Here is an interesting factoid about VIEWs in MySQL : A view is represented in two places in the information_schema database

information_schema.views : table_schema,table_name identify, and view_definition
information_schema.tables : table_schema,table_name where ENGINE is NULL

This will generate the EXPLAIN for all views
mysql -uroot -p -AN -e"select concat('explain select * from ',table_schema,'.',table_name,';') from information_schema.tables WHERE engine IS NULL" > /root/ExplainViews.sql

or
mysql -uroot -p -AN -e"select concat('explain select * from ',table_schema,'.',table_name,';') from information_schema.views" > /root/ExplainViews.sql

This will generate the EXPLAIN for all views in the mydb database
mysql -uroot -p -AN -e"select concat('explain select * from ',table_schema,'.',table_name,';') from information_schema.tables WHERE table_schema='mydb' AND engine IS NULL;" > /root/ExplainViews.sql

or
mysql -uroot -p -AN -e"select concat('explain select * from ',table_schema,'.',table_name,';') from information_schema.views WHERE table_schema='mydb';" > /root/ExplainViews.sql


Inside MySQL, see the GRANTs.  They are hopefully of the form of

GRANT ALL ON dbname.* TO user@'11.22.33.44' IDENTIFIED BY '...'.

Your probably may be solved as simply as copying that and changing the IP address.  Well, change the IDENTIFIED BY part to IDENTIFIED BY PASSWORD('*1234ASDF...') -- the 41-char encrypted password you see in mysql.user.

Note: If you are on 5.7, or have an "authentication plugin" the instructions may be quite different.

Or...

It may be that the OS has firewall protections for port 3306 (or whatever MySQL is using), and you need to poke another hole in the firewall.


A CROSS JOIN gives you the Cartesian product of all the rows in the two tables and then applies any filtering from the WHERE clause. It rarely serves a purpose for your more common table relationships, but there are some occasions where it's handy.

For example, you may need to assign relationships between a static table of Properties and some Items stored in another table. The easiest way to add a new Item and relate it's needed Properties would be to insert the results of a CROSS JOIN between the two tables into a linking table between them. Specific values for those properties could then be added later via other interfaces, but they would have the framework assigned already without having to hard-code the interface.


Run SQL Server Snapshot Agent automatically

I believe you are looking for sp_startpublication_snapshot.  You'll want to execute this at the Publisher on the publication database.

Regarding the error: from a path could not be read, you'll want to ensure the Snapshot Agent process account has sufficient permissions to generate the snapshot.  The process account needs the following permissions per BOL:

At minimum, be a member of the db_owner fixed database role in the distribution database.
Have write permissions on the snapshot share.
The account that is used to connect to the Publisher must at minimum
be a member of the db_owner fixed database role in the publication
database.

This is covered in Replication Agent Security Model.


How to take offline a database in Oracle?

Open a DBA connection instead of a regular connection (View / DBA to display the DBA panel), where you can use the Modify State menu:


Simplest way to use sub query.

SELECT emp_id,count(*) as count FROM ( SELECT *, count(p.id) FROM present p GROUP BY pre_date , emp_id ) d group by d.emp_id


Identifying which values do NOT match a table row

Use EXCEPT:

SELECT * FROM
  (values (1),(2),(3),(4)) as T(ID)
EXCEPT
SELECT ID
FROM [TABLE];

See SqlFiddle.

The values constructor will only work on SQL Server 2008 or later. For 2005, use

SELECT 'value'
UNION SELECT 'value'

as detailed in this SO answer.


Selecting large data set on windows and linux

Depends on your client libraries, the way your app handles memory, and what you're doing inside the fetch.

Pulling back one row at a time is a very dangerous design pattern for database servers to handle.  They have to materialize all your results first, then spoon feed them to you one row at a time while you slice & dice results.  You might be holding locks or slowing down the database server.  Instead, pull all of the data into memory on your app and do the slicing and dicing after you've let go of your locks, OR do your slicing and dicing in batches on the database server.  Batch processing is way more efficient than row-by-row processing.  In the DBA community, this is known as ReBAR - Row By Agonizing Row.


Why is a new user able to create tables in PostgreSQL?

TL;DR: New users can create tables in the public schema because people complained that it was too hard when they couldn't.

If you dislike the defaults, you should probably create a new template database with the initial configuration that you want. For example, you might:

DROP SCHEMA public;

or

REVOKE ALL ON SCHEMA public FROM public;
GRANT USAGE ON SCHEMA public TO public;

in your template.

If you wish the public user to have no rights on a database, you should additionally:

REVOKE ALL ON DATABASE mydbname FROM public;
GRANT CONNECT ON DATABASE mydbname TO public;

so that the public user cannot create schemas or use temp tables.

Personally, if I was designing this, I'd give users the TEMP right on the database by default, but not CREATE (schemas in database) or CREATE (tables in the public schema). I'd reserve those for the owner.

They're choices that were made a long time ago, though, and it's pretty hard to change them now.

As it is, there are regular complaints that it's too hard to get started with PostgreSQL because you have to create a user account and often want to create a database too. Why don't we just auto-create them and default to 'trust' as the auth-mode to make it easy? Why doesn't the postgres user default to having the password postgres? Why don't we just auto-create users if they exist in the OS? etc.

There are some genuine usability problems for new users - in particular, most people have no idea what peer auth is, or why just running psql after installing PostgreSQL tells them there's no user by the name they're logged in as.

It's also messy that pg_hba.conf is a config file, but users are created at the SQL level. This split confuses users.

Lots of things, though, are compromises between secure defaults and easy defaults where the project isn't ever going to make everybody happy.


What is the SQL command to get 20 closest points from some center location

If a SPATIAL INDEX does not work for you, see
  http://mysql.rjweb.org/doc.php/latlng
It is a description of how to efficiently "find the nearest" on the globe.


UTF8 Trouble while migrating from MSSQL to MySQL with MySQL Workbench

Hex A0 probably came from a Word document, where it is a "hard space".

It appears that you did not specify that the source was 'latin1' and the destination is 'utf8'.  Otherwise, A0 should have turned into C2A0.  That seems correct.  However, this seems incorrect: "Driver sends Unicode data as UTF-8".  Or perhaps just inconsistent with "Collation SQL_Latin1_General_CP1_CI_AS migrated to utf8_general_ci".  Did you do anything in ODBC to tell it to convert latin1 to utf8?

You have an inconsistency -- some places you mention utf8, some mention utf8mb4.  Outside MySQL, there is only "utf8" (or "UTF-8"), but inside MySQL there is a difference between utf8 and utf8mb4.  If you intend to handle Chinese or Emoji, go with utf8mb4.


Non-Clustered Indexes - keys and nonkeys

You are asking the query optimizer to produce a plan that can answer the query:

SELECT *
FROM tbl_pics
WHERE deleted = 0 AND map_id = 1 AND hidden = 0;

The rest is fluff (including the OR pic_id = @pic_id). This will be a table scan, guaranteed, because of the low selectivity of the predicates involved (I'm sure deleted and hidden are 0/1, and map_ip I doubt it has any significant impact). The only predicate that could save the query is pic_id = @pic_id but by placing it in an OR condition you killed it's chance. No secondary index can help it, realistically. The addition of ROW_NUMBER will ad a sort, most likely, but the real damage is the scan.

This is a lost cause. Come up with realistic requirements.


Get percentage free space on database volumes

Personally, I don't know why you need to do this from within SQL Server. You can get this as often as you want and fairly straightforward using PowerShell, or C#, or even VBScript in a scheduled task of some kind, and then have that program update a table if you really need this stored in the database (and frequency would be dictated by how volatile your disks are and how current you need the data to be). I recommend against shelling out to the OS to collect this information while an end user is waiting.

That said, in SQL Server 2012 (and 2008 R2 SP1+) there is a new DMV called sys.dm_os_volume_stats. This DMV has total size and available size so you can perform any resulting calculations from there. I haven't tested how current this data is - it may be seconds behind or more.


In SQL Server, are there any other options of data Import and Export

SQLCMD can be used to export data. The default output is sometimes unwieldy for that, but you can use some command-line options to turn off the headers, trim trailing spaces, and to use a comma separator instead of the default space:

sqlcmd -Q "SET NOCOUNT ON; SELECT TOP(10) name, object_id, type FROM sys.objects;" ^
  -h-1 -W -s ","

Returns:

sysrscols,3,S
sysrowsets,5,S
sysclones,6,S
sysallocunits,7,S
sysfiles1,8,S
sysseobjvalues,9,S
syspriorities,17,S
sysdbfrag,18,S
sysfgfrag,19,S
sysdbfiles,20,S

Use the -o option to send the output to a file:

sqlcmd -Q "SET NOCOUNT ON; SELECT TOP(10) name, object_id, type FROM sys.objects;" ^
  -h-1 -W -s "," -o C:\temp\SysObjects.txt

A custom .NET program (Console app, Windows app, or even Web app) can be created to import and/or export. For importing, since Table-Valued Parameters (TVPs) are not an option here, it is sometimes faster to transform/serialize the data into XML, and then execute a stored procedure that will accept the XML parameter and using the .nodes() function, deserialize it back into a rows that can be inserted via the INSERT INTO table (fields) SELECT x.value(),... FROM @XmlParam.nodes(); construct. Of course, you wouldn't want to do 1 million rows in this manner, so you would break the import file into batches of X rows and send 100 - 500 at a time. If there is a lot of data then maybe straight INSERT statements are better, but in that case you would want to wrap each batch of X rows into an explicit transaction using SqlTransaction.

Depending on how comfortable you (or someone you work with) are with .NET programming, you can use SQLCLR to build your own custom import and/or export functionality. This would work in all editions of SQL Server in all versions starting with SQL Server 2005.

Along these lines, there is a commercially available library of SQLCLR functions and procedures called SQL# (which I am the creator of). Some of the functions and stored procedures handle importing and exporting data. The File_SplitIntoFields Stored Procedure returns a result set where each row is a line from the input file, but each field in the file is a separate column. By default the datatype for each field is NVARCHAR(MAX), but there is an optional input parameter to specify what the exact datatype of each column should be. This makes it rather easy to do:

INSERT INTO dbo.MyImportTable (Col1, Col2, Col3, Col4)
  EXEC SQL#.File_SplitIntoFields
    @FilePath = N'',
    @RegExDelimiter = N',',
    @DataTypes = N'NVARCHAR(100),INT,DATETIME,BIT';

And there is an equivalent Stored Procedure called INET_SplitIntoFields that does the same thing, but rather than sourcing the data from a file, it streams it directly from a URL (hence no need to download the file locally first).

For exporting, there is a Stored Procedure called DB_BulkExport that combines many of the features of SQLCMD and SSIS. I created this after needing to do several exports and not being able to decide between SQLCMD and SSIS since each one has its pros and cons, and each one has one or two features that the other one doesn't have but all of those features were needed. For example, DB_BulkExport doesn't need any hard-coded output field specification and can handle SELECT * queries (something I couldn't figure out how to manage in SSIS but was easy with SQLCMD). It can also handle text-qualification of fields without having to update the source query to concatenate the double-quotes in there (which makes for a very ugly and hard to maintain query). In fact, it can be set to text-qualify ALL fields, or just fields that need it based on their datatype (strings, dates, GUIDs, etc).


Does the database open and you just can't view a single table, or are you unable to browse your database at all? Are other databases on the server affected, or just one? What's the current database status - offline, suspect, or something else? Each can mean different steps to correct or repair the issue.

Also, if you've got a backup handy, you'll want to keep it nearby in case you can't repair the corruption that caused this problem.

If you can open the database, to start you'll want to scan it with DBCC and see if anything obvious comes up:

DBCC CHECKDB ('YourDatabase') WITH NO_INFOMSGS

From there, deal with the error messages or warnings that you see - it should provide enough detail that you're able to search for the specific issues (or update your question to include those details so that we can help you more directly).

SqlMag has a great series on corruption, what it is, and how to respond to it, starting here:

http://sqlmag.com/blog/sql-server-database-corruption-part-i-what-corruption

Jump ahead to section 9 if you want to see their suggestions for fixing it, but be sure you understand what you're dealing with before you take any action.


Listing active PostgreSQL foreign data wrappers

The information you're looking for is in various pg_catalog tables. pg_class is the main table that represents all relations, normal or foreign; you can limit it to foreign tables by including WHERE relkind='f'. Information on the foreign data wrappers themselves is in pg_foreign_data_wrappers, on servers in pg_foreign_servers, and on foreign tables in pg_foreign_tables.
Start with select relname from pg_class where relkind='f' to just get a list of table names, and from there you can use the pg_catalog docs to find whatever other pieces of information you need for what you're trying to do.


According to the true nature of things (as I understand it), I suggest two tables:
CREATE TABLE product (
   product_id serial PRIMARY KEY
 , product    text NOT NULL
 --   more attributes of the product
);

CREATE TABLE offer (
   offer_id   serial PRIMARY KEY
 , product_id int NOT NULL REFERENCES product
 , price      int           -- prices in cent
 , price_alt  text          -- overrules price if present
 , valid_from timestamp NOT NULL
 , valid_to   timestamp     -- optional
 --   more attributes of the offer
 , CONSTRAINT some_kind_of_price_required
      CHECK (price IS NOT NULL OR price_alt IS NOT NULL)
);

We are dealing with two different entities:

The product per se, with all its (hardly changing) attributes. Goes into table product.

The deal you are offering, with a price, a "price alternative" (price_alt) a start time (valid_from), an optional end time (valid_to), ... Table offer.

This adequate model has technical advantages:

You automatically have a history of prices, which you can keep or delete or backup (and restore without interfering with the working system).

Most of the data goes into product. Most of the updates go into offer, in the form of new entries, so INSERTs really. INSERTing small rows is much cheaper than UPDATEs on big rows.

You can always add a VIEW (or MATERIALIZED VIEW if you need the read performance) to provide a complete table of current offers including product details.
Price
Assuming freely formulated price alternatives (price_alt), hence data type text. If it's just the same recurring phrases, use an enum or create a lookup-table and only store a price_alt_id in offer.
I would define that price_alt overrules price. There can still be a price (might serve as guideline for haggling or as minimum), but that's optional. The constraint some_kind_of_price_required enforces that some kind of price is always present.
Either way, I'd expect an actual price in the majority of offers. Use an integer column and store the amount in cent. That's typically simpler and faster. An int only occupies 4 bytes, where a numeric for your case typically occupies 8-12 bytes.
Additional columns with mostly NULL values are cheap:

Calculating and saving space in PostgreSQL
Adding nullable column to table costs more than 10 minutes

The design with an additional column for price_alt is much more effective and also much cleaner than switching to text representation of numeric prices (that would be a very bad decision). A view could show a single price column as formatted text (overruled by price_alt).
I would define valid offers as:

the latest offer per product with valid_from pre-dating "now"
valid_to IS NULL or greater than "now"
product in store (outside the scpoe of this answer).

So there is at most one offer per product at any given time.


What is the default 'window' an aggregate function is applied to?

Your example code:

SELECT
  someCol,
  someCol2,
  AVG(someCol3) [avg]
FROM
  tbl
GROUP BY
  someCol,
  someCol2

The "window" for the AVG(someCol3) column is the combination of unique values for each someCol, someCol2 tuple, by virtue of the GROUP BY clause.

You could rewrite the above query as:

SELECT
  someCol,
  someCol2,
  AVG(someCol3) OVER (PARTITION BY someCol, someCol2)
FROM
  tbl

The results would be similar, although not identical.  The 2nd variant returns a result row for every row in the tbl table, whereas the 1st variant returns a result row for every unique combination of someCol, someCol2.  In the 2nd result set, each row would contain the average value of someCol3 for each unique combination of someCol, someCol2 by virtue of the PARTITION BY clause.

You can see this by creating a test-bed:

IF OBJECT_ID('dbo.TestAgg') IS NOT NULL
DROP TABLE dbo.TestAgg;
CREATE TABLE dbo.TestAgg
(
    someCol int NOT NULL
    , someCol2 int NOT NULL
    , someCol3 decimal(10,5) NOT NULL
);

INSERT INTO dbo.TestAgg (someCol, someCol2, someCol3)
VALUES (1, 1, 1)
    , (2, 2, 2)
    , (2, 2, 3)
    , (4, 1, 4);

SELECT ta.someCol
    , ta.someCol2
    , AVG(ta.someCol3)
FROM dbo.TestAgg ta
GROUP BY ta.someCol
    , ta.someCol2
ORDER BY ta.someCol
    , ta.someCol2;

SELECT ta.someCol
    , ta.someCol2
    , AVG(ta.someCol3) OVER (PARTITION BY ta.someCol, ta.someCol2)
FROM dbo.TestAgg ta
ORDER BY ta.someCol
    , ta.someCol2;

The results:

+---------+----------+------------------+
| someCol | someCol2 | (No column name) |
+---------+----------+------------------+
|       1 |        1 |         1.000000 |
|       2 |        2 |         2.500000 |
|       4 |        1 |         4.000000 |
+---------+----------+------------------+

+---------+----------+------------------+
| someCol | someCol2 | (No column name) |
+---------+----------+------------------+
|       1 |        1 |         1.000000 |
|       2 |        2 |         2.500000 |
|       2 |        2 |         2.500000 |
|       4 |        1 |         4.000000 |
+---------+----------+------------------+

If you specify a blank OVER () clause, the "window" is the entire resultset:

SELECT ta.someCol
    , ta.someCol2
    , COUNT(ta.someCol3) OVER ()
    , COUNT(ta.someCol3) OVER (PARTITION BY ta.someCol, ta.someCol2)
    , AVG(ta.someCol3) OVER ()
    , AVG(ta.someCol3) OVER (PARTITION BY ta.someCol, ta.someCol2)
FROM dbo.TestAgg ta

+---------+----------+------------------+------------------+------------------+------------------+
| someCol | someCol2 | (No column name) | (No column name) | (No column name) | (No column name) |
+---------+----------+------------------+------------------+------------------+------------------+
|       1 |        1 |                4 |                1 |         2.500000 |         1.000000 |
|       2 |        2 |                4 |                2 |         2.500000 |         2.500000 |
|       2 |        2 |                4 |                2 |         2.500000 |         2.500000 |
|       4 |        1 |                4 |                1 |         2.500000 |         4.000000 |
+---------+----------+------------------+------------------+------------------+------------------+

You can perform multiple aggregates on a single column however, you cannot do it in a single SELECT ... statement; you must use a subquery or CTE to do so:

SELECT s.someCol
    , s.someCol2
    , SUM(avg_someCol3)
FROM (
    SELECT ta.someCol
        , ta.someCol2
        , avg_someCol3 = AVG(ta.someCol3) OVER (PARTITION BY ta.someCol, ta.someCol2)
    FROM dbo.TestAgg ta
    ) s
GROUP BY s.someCol
    , s.someCol2

Returns:

+---------+----------+------------------+
| someCol | someCol2 | (No column name) |
+---------+----------+------------------+
|       1 |        1 |         1.000000 |
|       2 |        2 |         5.000000 |
|       4 |        1 |         4.000000 |
+---------+----------+------------------+

However, in this contrived example, the value of 5 makes no sense that I can understand as the sum of an average.  Perhaps your real problem makes more sense.  Perhaps you really want this:

SELECT s.someCol
    , s.someCol2
    , avg_someCol3
FROM (
    SELECT ta.someCol
        , ta.someCol2
        , avg_someCol3 = AVG(ta.someCol3) OVER (PARTITION BY ta.someCol, ta.someCol2)
    FROM dbo.TestAgg ta
    ) s
GROUP BY s.someCol
    , s.someCol2
    , s.avg_someCol3

Results:

+---------+----------+--------------+
| someCol | someCol2 | avg_someCol3 |
+---------+----------+--------------+
|       1 |        1 |     1.000000 |
|       2 |        2 |     2.500000 |
|       4 |        1 |     4.000000 |
+---------+----------+--------------+

This is more easily obtained by using the DISTINCT keyword, although it make me shudder to think I just suggested using it:

SELECT DISTINCT ta.someCol
    , ta.someCol2
    , AVG(ta.someCol3) OVER (PARTITION BY ta.someCol, ta.someCol2)
FROM dbo.TestAgg ta


Setting up log shipping with an availability group

Robert Douglas describes the exact solution to my problem in his post:

Why log shipping backups fail in an Always On Availability Group – Resolved

Robert writes:

  ...the availability group was trying to enforce a backup being taken off the secondary replica, and the log shipping job didn’t like that.

  Here's how to correct it.

  In Management Studio expand AlwaysOn High Availability and then Availability Groups.
  Right click on your chosen group and choose properties.
  In the left pane select backup preferences.
  Select 'Primary' as your backup source.  Click Okay.


I would suggest that you either want a single "couple" table instead of two, if the properties of each coupling are sufficiently similar, or a couple table that the two existing types of couple "inherit" from, like so:

RC                  Couple                                Egg
----------          ---------------------------           ----------------
C ID (FK)   ----->  C ID (PK)                    <-----   C ID (FK)
(rc props)    |     (generic couple properties)           (egg properties)
              |
BC            |
----------    |
C ID (FK)   --'
(BC props)

You could still have breeding and racing details for any could in this arrangement (which might actually be fine, of course) but each egg can only be associated with one couple. You won't have problems with overlapping ID ranges as there is only one range of IDs for couples when modeled this way, and all other tables with data relating to couples only have the one FK linking to the could record.


How to start building your own DBMS (based on PostgreSQL)?

My advice is to get involved with an open source database project. I would go with PostgreSQL, but there are of course alternatives.

If you go with PostgreSQL:

Use the software. It is impossible to learn about databases without actually using them.
Build PostgreSQL from source.
Subscribe to the mailing lists and read them. There you can read what knowledgeable people have to say, and one can learn a lot from that.
Whenever you run into a problem or don't understand the workings of something, dig into the source. If you get stuck, ask for help.
Read this article.
Tune in on the pgsql-hackers mailing list.
Start reviewing patches and contributing.

Keep your eyes open for the different ways that other databases do it. After a few years, you will have gained some understanding. Then you may be in a position to come up with your own ideas on architecture.


Since you are selecting fields from Table1 only, it is pointless to get the data from a join applied on table 1 and table 2.
After testing both queries on my server, i have noticed that the processing time has reduced using the first query (nested selects).
hope this answers your question.


The thing with being a "serious" DBA (and by that I mean, you are a person responsible for meeting a defined SLA on a system handling physical goods or other people's money) is that it is very difficult to get the job without already having had the job for 5 years - experience is everything in this game, a reputation as a safe pair of hands who isn't going to panic and make a bad situation worse, and has the maturity to weigh up commercial and technical considerations. That is what makes DBAs different from developers - that we need to make decisions in real time - and from sysadmins - that we are only one step removed from the cash that flows through the business, personal details of customers, stock levels in the warehouse, etc, so we are much more business-focussed.

The usual way in is that you are either a developer or a sysadmin with an interest in databases who gets involved with DBA work, fills in when other DBAs are unavailable, and gradually work your way sideways into the job. That is the best route, it is almost like an apprenticeship. I dare say it is the same if you want to be a "web" DBA it is similar but I have no personal experience of that.


See the documentation.

  Using index

  The column information is retrieved from the table using only
  information in the index tree without having to do an additional seek
  to read the actual row. This strategy can be used when the query uses
  only columns that are part of a single index.

  If the Extra column also says Using where, it means the index is being
  used to perform lookups of key values. Without Using where, the
  optimizer may be reading the index to avoid reading data rows but not
  using it for lookups. For example, if the index is a covering index
  for the query, the optimizer may scan it without using it for lookups.

In short, your query is working as intended and is not performing an additional disk seek.


How to create a blank version of a PostgreSQL database

how to create "empty" or "blank" databases

I would use pg_dump with the --schema-only option to create the dump for an empty database:

pg_dump mydb -s > /mypath/myfile.sql

Using Linux, you could also pipe the output to psql and create a new, empty database in the same (or any other) database cluster right away. Example for the same cluster:

createdb mytemplate; pg_dump mydb -s | psql mytemplate

If you should create the database in a different db cluster, remember that roles or tablespaces are cluster-wide objects and not included in the dump of a single db. So, any custom roles (or other cluster-wide objects) have to be present in the new cluster before restoring to it.

This is much faster than truncating existing tables.

  a way to "reset" all the "current" values in any sequence

There is a dedicated command for that:

ALTER SEQUENCE RESTART;

Unless defined otherwise, this resets a sequence to 1. To define otherwise, use:

ALTER SEQUENCE RESTART WITH 123;

Or save the desired start value with the sequence:

ALTER SEQUENCE my_seq START WITH 123;

And use the simple form to RESTART.

To restart all sequences in the database, retrieve them from the catalog table pg_class:

DO
$do$
BEGIN

EXECUTE (
   SELECT string_agg('ALTER SEQUENCE ' || oid::regclass || ' RESTART;', E'\n')
   FROM   pg_class
   WHERE  relkind = 'S'  -- 'S' for sequences
   );

END
$do$

Sufficient privileges required. Superusers pass this test automatically.

Careful with that! You normally don't want to lose the current value for all sequences in your database. In any case, you may want to check what the SELECT cooks up before executing it.

So, I would create an empty copy and then run the above DO command. Then pg_dump again and restore to different servers.


GeoJSON MultiLineString import to MySQL with SRID/CRS 27700, 'out of range'

The way I've found to work around this to use ogr2ogr to import the eastings and northings (in a geojson file). An example code would be as follows:
ogr2ogr -f MySQL MySQL:test,user=root import_file_location \
-nln FILENAME -update -overwrite -lco GEOMETRY_NAME=MULTIPOLYGON

This will import as British National Grid coordinates, which can then be used to create buffers in MySQL.


Oracle - execute DML query as different user

A bit old question but if anybody is interested, there is a way to do it. Take a look at dbms_sys_sql package. It allows you to execute any SQL as any user on the database. It is how APEX does it. But be careful granting execute privileges to this package since it can be used to impersonate anybody on the database and it is very powerful.


On an ordinary spinning drive, you can get about 100 IOPs.  If all 180 rows needed to reach off-row to get items and none needed for template, then that is 180 + miscellany to get SELECT * 180 times.  That's about 2 seconds.

SSD drives would be faster.

Compressing, in the client, those two columns, then storing them in BLOBs, would shrink things a bunch.  This would probably lead to fewer IOPs.

It seems like populating memcached would be a one-time action, so the 2 seconds would not be a big deal?


"Temporary file write failure" when trying to change primary key in MySQL

I had the same problem, I did the following:

create table resources_with_key like resources;
alter table resources_with_key add primary key(`category_id`, `lang`, `model`, `rule`, `role_id`);
insert into resources_with_key select * from resources;


Identify cached data for a specific query in data cache

If the goal of your question is to determine if your stored procedure is having to do physical reads to satisfy the query (instead of retrieving the data from memory), you might be able to use the following query in the stored procedure to capture the physical reads during execution and possibly save that information for auditing purposes

declare @start_reads bigint
select @start_reads = reads from sys.dm_exec_requests where session_id = @@spid

-- Your query here

select reads - @start_reads from sys.dm_exec_requests where session_id = @@spid


This is working as intended. The indexes are objects written to the disk. The index creation is also written to the online logs. Creating indexes is expected to take up disk space and cause I/O operations.


Unable to conditionally commit/rollback transaction

There are two errors in your script.

First, delete does not affect "Found_rows()" if you don't have a "returning".  You should use

set @rc = row_count();

Second, your logic in the if(cond,trueval,falseval) is inversed, you should use

set @tran = (select if(@rc = @expected,'COMMIT','ROLLBACK'));

You want to commit when the result equals what you expected, don't you?


Another thing to try is to run rs.status() on the notebook server and compare the state it sees with the state you saw on the PRIMARY node. The differences there may be descriptive.

The thing with Mongo is that it's very stupid by design, it relies on the OS to do everything except run the database itself (it even doesn't bother implementing a caching layer, relying on the OS block cache for that). So when I see connectivity problems like you describe, I start thinking about OS level problems like firewalls.

There are two ways to configure a pair of MongoDB instances on different servers to not talk to each other:

Configure them to listen to localhost.
Configure one to require authentication, but not the other.

If you're not doing either of those (it isn't the first, and the second is unlikely), chances are good the Windows firewalls haven't been set up to allow the nodes to talk to each other. The main and port servers can do so since they're on the same node, but the notebook server would be on the other side of a firewall. Look to that to ensure you can communicate on the ports.


InnoDB: Ignoring the redo log due to missing MLOG_CHECKPOINT

Try the following:

Set innodb_log_checksums = ON on master and slave.
Remove rm /var/lib/mysql/ib_logfile*.
Restart.


Transaction log rollback broke Log Shipping chain?

Is it correct that performing a transaction log restore to a previous point in time will break the log shipping chain?

Any backup restored on primary database will break logshipping and you would have to start from scratch to configure logshipping.


Getting the contents of a json file as a variable in plpgsql?

If the file contains a valid JSON literal, you could read it in with pg_read_file() and assign to a json variable directly:

CREATE OR REPLACE FUNCTION file_read(file text)
  RETURNS void AS
$func$
DECLARE
   content json := pg_read_file(file, 0, 10000000);  -- arbitrary max. 10 MB
BEGIN
   -- do some more stuff here
END
$func$  LANGUAGE plpgsql;

But that requires superuser privileges for reasons explained in the manual where I linked.

You could make that a SECURITY DEFINER function, owned by a superuser, but be very careful who to grant the EXECUTE privilege then. Example:

Creating UUID extension as the non super privileged user


Why are there execution plan differences between OFFSET ... FETCH and the old-style ROW_NUMBER scheme?

The examples in the question do not quite produce the same results (the OFFSET example has an off-by-one error).  The updated forms below fix that issue, remove the extra sort for the ROW_NUMBER case, and use variables to make the solution more general:

DECLARE
    @PageSize bigint = 10,
    @PageNumber integer = 3;

WITH Numbered AS
(
    SELECT TOP ((@PageNumber + 1) * @PageSize)
        o.*,
        rn = ROW_NUMBER() OVER (
            ORDER BY o.[object_id])
    FROM #objects AS o
    ORDER BY
        o.[object_id]
)
SELECT
    x.name,
    x.[object_id],
    x.principal_id,
    x.[schema_id],
    x.parent_object_id,
    x.[type],
    x.type_desc,
    x.create_date,
    x.modify_date,
    x.is_ms_shipped,
    x.is_published,
    x.is_schema_published
FROM Numbered AS x
WHERE
    x.rn >= @PageNumber * @PageSize
    AND x.rn < ((@PageNumber + 1) * @PageSize)
ORDER BY
    x.[object_id];

SELECT
    o.name,
    o.[object_id],
    o.principal_id,
    o.[schema_id],
    o.parent_object_id,
    o.[type],
    o.type_desc,
    o.create_date,
    o.modify_date,
    o.is_ms_shipped,
    o.is_published,
    o.is_schema_published
FROM #objects AS o
ORDER BY
    o.[object_id]
    OFFSET @PageNumber * @PageSize - 1 ROWS
    FETCH NEXT @PageSize ROWS ONLY;

The ROW_NUMBER plan has an estimated cost of 0.0197935:

The OFFSET plan has an estimated cost of 0.0196955:

That is a saving of 0.000098 estimated cost units (though the OFFSET plan would require extra operators if you want to return a row number for each row).  The OFFSET plan will still be slightly cheaper, generally speaking, but do remember that estimated costs are exactly that - real testing is still required.  The bulk of the cost in both plans is the cost of the full sort of the input set, so helpful indexes would benefit both solutions.

Where constant literal values are used (e.g. OFFSET 30 in the original example) the optimizer can use a TopN Sort instead of a full sort followed by a Top.  When the rows needed from the TopN Sort is a constant literal and <= 100 (the sum of OFFSET and FETCH) the execution engine can use a different sort algorithm which can perform faster than generalized TopN sort.  All three cases have different performance characteristics overall.

As to why the optimizer does not automatically transform the ROW_NUMBER syntax pattern to use OFFSET, there are a number of reasons:

It's almost impossible to write a transform that would match all existing uses
Having some paging queries automatically transformed and not others could be confusing
The OFFSET plan is not guaranteed to be better in all cases

One example for the third point above occurs where the paging set is quite wide.  It can be much more efficient to seek the keys needed using a nonclustered index and manually lookup against the clustered index compared with scanning the index with OFFSET or ROW_NUMBER.  There are additional issues to consider if the paging application needs to know how many rows or pages there are in total.  There is another good discussion of the relative merits of the 'key seek' and 'offset' methods here.

Overall, it is probably better that people make an informed decision to change their paging queries to use OFFSET, if appropriate, after thorough testing.


InnoDB tables tend to occupy more space than MyISAM tables do.
While converting, new physical files are being created, so you have to have free space on the disk (Hard to estimate, but to be safe I would say 120G)
If you use Percona tools to make the change online, all modifications on the original table will be stored in a temporary table using triggers. So depending on the DML operations,i.e. inserts/updates/deletes, you may need more space. Be careful that on high traffic tables, triggers may affect the performance.
It could be that some InnoDB related configuration should be tuned.

If in your setup you have two or more servers, like master-master, or master-slave(s), I think it is better to do it offline, one server at a time.


I've managed to make it work with pointing slave to master IP instead of url, and putting slave ip to inbound rules of Master's security group


SQL application connection using one Windows user

If you're using IIS manager for application pools to configure identity for your application and your servers are Windows 2012 or later, you can look into using either a Managed Service Account or a Group Managed Service Account to run your app.  Either approach will eliminate the need to deal with passwords yet maintain a secure application login.

The comment provided by sepupic above is spot on though in that you should manage user permissions directly against the database separately, and how that's done will heavily depend upon what level of permissions are needed.

Hopefully this provides an approach to your situation though.


PostgreSQL partition hierarchy design for centralized logging

What can I expect performance-wise of such a trigger/partition scheme?

Context switches mean using triggers is always going to use a lot more CPU than a simple insert. The script below can be used to quantify how much impact that will have - and also demonstrates auto-creating partitions using triggers and compares the performance either way.

Please note I haven't included any indexing, or any consideration of update statements.

begin;
set role dba;
create role stack;
grant stack to dba;
create schema authorization stack;
set role stack;
--
--******** the above creates a nice clean schema as a test area
--
set client_min_messages to warning; --******** or you get a lot of "NOTICE:  merging column "xyz" with inherited definition" notices
--
create table phpheap(log_id serial not null, host text not null, logdate date not null, message text not null); --******** This table is used to compare 'insert' performance with that on the partitioned version
create table php(log_id serial not null, host text not null, logdate date not null, message text not null);
--
create function php_host_insert() returns trigger language plpgsql security definer as $$
begin
  set search_path to 'stack';
  execute 'insert into php_'||new.host||'_'||to_char(new.logdate, 'YYYYmMM')||'(log_id, host, logdate, message) values($1, $2, $3, $4)' using new.log_id, new.host, new.logdate, new.message;
  return null;
exception when undefined_table then
  execute 'create table php_'||new.host||'_'||to_char(new.logdate, 'YYYYmMM')||'(log_id int not null, host text not null check(host='''||new.host||'''), logdate date not null check(to_char(logdate, ''YYYYmMM'')='''||to_char(new.logdate, 'YYYYmMM')||'''), message text not null) inherits (php_'||new.host||')';
  execute 'insert into php_'||new.host||'_'||to_char(new.logdate, 'YYYYmMM')||'(log_id, host, logdate, message) values($1, $2, $3, $4)' using new.log_id, new.host, new.logdate, new.message;
  return null;
end;$$;
--
create function php_insert() returns trigger language plpgsql security definer as $$
begin
  set search_path to 'stack';
  execute 'insert into php_'||new.host||'(log_id, host, logdate, message) values($1, $2, $3, $4)' using new.log_id, new.host, new.logdate, new.message;
  return null;
exception when undefined_table then
  execute 'create table php_'||new.host||'(log_id int not null, host text not null check(host='''||new.host||'''), logdate date not null, message text not null) inherits(php)';
  execute 'create trigger trig_insert_php_'||new.host||' before insert on php_'||new.host||' for each row execute procedure php_host_insert()';
  execute 'insert into php_'||new.host||'(log_id, host, logdate, message) values($1, $2, $3, $4)' using new.log_id, new.host, new.logdate, new.message;
  return null;
end;$$;
--
create trigger trig_insert_php before insert on php for each row execute procedure php_insert();
--
\timing on
insert into phpheap(host, logdate, message) select 'host1', current_date-(generate_series(-99999, 0, 1)/1000)::integer, repeat('hello',20);
--******** output
--INSERT 0 100000
--Time: 1102.140 ms
insert into php(host, logdate, message) select 'host1', current_date-(generate_series(-99999, 0, 1)/1000)::integer, repeat('hello',20);
--******** output
--INSERT 0 0
--Time: 35615.498 ms
insert into php(host, logdate, message) select 'host1', current_date-(generate_series(-99999, 0, 1)/1000)::integer, repeat('hello',20);
--******** output
--INSERT 0 0
--Time: 34074.579 ms
\timing off
--
--******** Now we replace the trigger functions with the 'normal' kind that don't auto-create partitions
--
create or replace function php_host_insert() returns trigger language plpgsql security definer as $$
begin
  set search_path to 'stack';
  execute 'insert into php_'||new.host||'_'||to_char(new.logdate, 'YYYYmMM')||'(log_id, host, logdate, message) values($1, $2, $3, $4)' using new.log_id, new.host, new.logdate, new.message;
  return null;
end;$$;
--
create or replace function php_insert() returns trigger language plpgsql security definer as $$
begin
  set search_path to 'stack';
  execute 'insert into php_'||new.host||'(log_id, host, logdate, message) values($1, $2, $3, $4)' using new.log_id, new.host, new.logdate, new.message;
  return null;
end;$$;
--
\timing on
insert into php(host, logdate, message) select 'host1', current_date-(generate_series(-99999, 0, 1)/1000)::integer, repeat('hello',20);
--******** output
--INSERT 0 0
--Time: 28457.146 ms
\timing off
--
rollback;


CPU cost in SQL Availability Group for synchronous and asynchronous DB's

We are wondering if there's a difference in CPU usage of a synchronous or an asynchronous node?

Yes, there will be a difference but only because these modes talk to each other in vastly different frequencies and ways. The difference will entirely depend on workload and there is no way to "guesstimate" what that will be.

  Also one of my colleagues is saying there's a limitation in the number of Listeners …

There technically isn't and you're correct, generally at least 1 listener per availability group so that you can always connect to the primary. Maybe you have some other type of network appliance or device that can do probing and update an internal object to always point to the primary - that's possible and a listener may not be needed. Sometimes there are company requirements where all applications have to use a specific endpoint/dns for each application. So, generally, this will depend on your requirements but there isn't anything really stopping you from creating listeners except for IP addresses in an available range (if using IPv4).


Will NVME SSD speed up heavy queries, e.g. selects at large offsets / table scans etc?

It is quite obvious, is it not?

If the IO side of the database is the bottleneck, then having a better IO side will speed things up.

Now, most of the time - outside of data warehouses - IO should not bottleneck that bad once you leave the really low IOPS side of Hard discs. A proper database server will have enough RAM (again, outside of data warehouses) hat a lot is cached - which means that no, you may not really need THAT much database bandwidth and it will likely NOT get you 4x the throughput.

But if you have like 5gb RAM and a 100GB table and you make a table scan, then yes, faster IO means faster query processing. Obviously.

Also Note: NVM does not necessarily mean the SSD is also faster. Not sure they are any, but you better make sure your NVM SSD is up to the task. There are some really cheap ones that are a: not optimized for the write performance needed and b: just too slow to use up the NVM ports.


At the top of the script put the following

SET NOCOUNT ON

That will hide the n rows effected messages.


Too Many database connections on Amazon RDS

Here is a Stored Procedure to kill long running SELECTs

DELIMITER $$

DROP PROCEDURE IF EXISTS `test`.`Kill_Long_Running_Selects` $$
CREATE PROCEDURE `test`.`Kill_Long_Running_Selects` (time_limit INT,display INT)
BEGIN

    DECLARE ndx,lastndx INT;

    DROP TABLE IF EXISTS test.LongRunningSelects;
    CREATE TABLE test.LongRunningSelects
    (
        id INT NOT NULL AUTO_INCREMENT,
        idtokill BIGINT,
        PRIMARY KEY (id)
    ) ENGINE=MEMORY;
    INSERT INTO test.LongRunningSelects (idtokill)
    SELECT id FROM information_schema.processlist
    WHERE user<>'system user' AND info regexp '^SELECT' AND time > time_limit;

    SELECT COUNT(1) INTO lastndx FROM test.LongRunningSelects;
    SET ndx = 0;
    WHILE ndx < lastndx DO
        SET ndx = ndx + 1;
        SELECT idtokill INTO @kill_id
        FROM test.LongRunningSelects WHERE id = ndx;
        CALL mysql.rds_kill(@kill_id);
    END WHILE;

    IF lastndx > 0 THEN
        IF display = 1 THEN
            SELECT GROUP_CONCAT(idtokill) INTO @idlist FROM test.LongRunningSelects;
            SELECT @idlist IDs_KIlled;
            SELECT CONCAT('Processes Killed : ',lastndx) Kill_Long_Running_Selects;
        END IF;
    END IF;

END $$

To kill SELECTs running longer than 30 seconds, you run this

CALL test.Kill_Long_Running_Selects(30,0);

If you want to see the connections being killed, you run this

CALL test.Kill_Long_Running_Selects(30,1);

Perhaps you can create a MySQL Event to call this Stored Procedure every minute.

If Amazon does not let you have the EVENT privilege, you will have to write an external  shell script on the EC2 server to connect to the DB and run the Stored Procedure. That shell script can be put into a crontab.

If Amazon does not let you have the PROCESS and SUPER privileges, you may need to move the DB out of RDS and into another EC2 instance running MySQL to accomplish this. You could then create the MySQL Event without Amazon's hosting restrictions.


Redesigning a table that has a lot of columns (not always related)

Not really an answer - but too big for a comment. Just a few ideas/discussion.

"our database was never designed with ANY plan in mind" - I hear yeh, sister!

Been there, done that. Once worked with a Btrieve (precursor to Pervasive) system that had 35,000 (not a typo - that's thirty five thousand) fields in one table. Agree with @timpone - hesitant about tables with > 30 fields. Tables should be like women - tall and slim, not short and fat :-).

It's a lot of work to redesign. Start with core functionality - what's the minimum amount of tables required? I would consider PostgreSQL if I were you, but like @Austin, that's just my opinion.

Read up on the different RDBMSs - check out their functionality. Is there anything that Btrieve has that they do/don't? As far as I can see, the main thing that Btrieve brought to the party was arrays - they are a breach of the relational model and should be avoided at all costs in an RDBMS.

You say "a LARGE amount of them only store a CHAR(1) which can be Y/N. (Side note: am I correct in assuming BIT will take up less space?)". With space being so cheap these days, this is not really  an issue.


My immediate suspicion is that there is another my.cnf present on the system. You should run

locate my.cnf

and see if the reports multiple my.cnf files

If there is only one my.cnf, then my.cnf is most likely in the wrong folder.

It should be in one of the following places

/etc/my.cnf
/etc/mysql/my.cnf
/var/lib/mysql/my.cnf
~/my.cnf

Please check for these. Remove extras.


Writeback is always written to the leaf level of your dimension, and has to be because of the structure of your writeback table (where the difference between the original value and the new value is stored).

Have a look at the writeback tables in your data source and you'll see it contains fields for all your measures, and the key of all your dimensions, so the change always needs to be distributed to that level.

From the documentation on write-enabled partitions

  Changes to leaf and nonleaf cells are handled differently. A leaf cell
  represents an intersection of a measure and a leaf member from every
  dimension referenced by the measure group. The value of a leaf cell is
  taken directly from the fact table, and cannot be divided further by
  drilling down. If a cube or any partition is write-enabled, changes
  can be made to a leaf cell. Changes can be made to a nonleaf cell only
  if the client application provides a way of distributing the changes
  among the leaf cells that make up the nonleaf cell.

The only thing you can really influence is how the changes are distributed to the leaf level cells when you write back to another attribute.
This is documented in the UPDATE CUBE

You basically have 4 options:

USE_EQUAL_ALLOCATION
USE_EQUAL_INCREMENT
USE_WEIGHTED_ALLOCATION [ BY Weight_Expression]
USE_WEIGHTED_INCREMENT [ BY Weight_Expression]

I'm not sure how well Excel allows you to pick an allocation method however.


Partitioned Database restore is very slow on SQL Server 2012 with SP2

Well I don't wonder about the restoring time. I wonder about your file amount. 15k files for 112MB? What the h...?

Your symptom is nearly the same as during file copying. If you have two disc (disc1, disc2) and you try to copy 100.000 files each 4KB in size from disc1 to disc2 it may take hours. If you copy one zip file without compression (containing those files) with 400.000KB file size and then extract it afterwards on disc2, it would take a few seconds.

You'll stressing your I/O subsystem with many I/O operations. Consider to merge those files back to one or just a few. I'm not sure what has driven you to those design?


Clients timing out with no apparent reason

Have you checked the error log? In Object Explorer, expand Management, expand SQL Server Logs, right-click Current... and choose Show SQL Server Log. Hopefully there will be more details there about what is causing the timeouts. If you can post anything suspect that you find there, we can help you troubleshoot further (though it may be something obvious, like a logon trigger or resource governor).


Understanding optimisation of complex SELECT queries

Why there are massive differences in performance between the queries?
(assuming that you have at least defined indexes for the columns used in the subquery criteria and in the joining conditions)

Basically because MySQL optimizer is not smart enough to figure out that all these queries are equivalent. So, it probably produces different execution plans for the different queries. If you do not have properly defined FOREIGN KEY constraints, the optimizer may be actually right, there is no guarantee that the queries return identical results.

So, what to do to increase performance and have a standard way of writing this type of query variants?

Several things that affect MySQL queries performance:

Do not use id IN (SELECT subquery) if you can avoid it. It's not very well optimized in most MySQL versions (see point 6 below). Use joins if you can.
Replace UNION with UNION ALL if that doesn't change the result set (you could do that in EG3 query).
Don't use implicit joins (with commas in the FROM clause and the joining condition in the WHERE clause). Use explicit JOIN syntax. As an example, your EG1 is actually the same as (this is not for performance but for consistence):

SELECT people.*
  FROM people
       LEFT JOIN (criterion1 SELECT) c1 ON people.id=c1.pid
       LEFT JOIN (criterion2 SELECT) c2 ON people.id=c2.pid,
       JOIN (criterion3 SELECT) c3 ON people.id=c3.pid
 WHERE ( c1.pid IS NOT NULL OR c2.pid IS NOT NULL );

The above query has an OR condition that is relevant to 2 tables (and their joins to the people, so 3 tables actually). This is usually not very good perfomance-wise.
You can try rewriting using EXISTS. This will make your queries easier to write and it may help with performance, too:

SELECT people.*
  FROM people
 WHERE EXISTS
         (criterion3 SELECT modified with `people.id = some_table.pid`)
   AND ( EXISTS
           (criterion1 SELECT modified with `people.id = some_table.pid`)
      OR EXISTS
           (criterion2 SELECT modified with `people.id = some_table.pid`)
       ) ;

If your host allows it, try/test MariaDB (it's a MySQL fork-replacement) that has introduced several improvements in queries execution in its latest versions. The optimizer will be a bit smarter identifying equivalent queries and certainly smarter as it has some new algorithms implemented that affect queries will joins, subqueries among other things.

MySQL 5.6 has also a few improvements in the optimizer but it's not available yet as a stable release.


How to make a unique datetime without seconds?


As you show in your question, the Microsoft Docs say:

  When a symmetric key is encrypted with a password instead of a certificate (or another key), the TRIPLE DES encryption algorithm is used to encrypt the password. Because of this, keys that are created with a strong encryption algorithm, such as AES, are themselves secured by a weaker algorithm.

This is a simple warning that a key protected by a password is weaker than the same key protected by a certificate.

So, in order to compromise the symmetric key, you need to know the password.  An attacker might obtain the password by cracking the 3-DES encryption; they would then have access to symmetric key, and by extension everything that is protected by that key.

The take-away is, don't use a password to protect a key if the data protected by the key is valuable.  Use a certificate to encrypt the symmetric key.


Mongo Scaling - Too Many Connections - Linear Horizontal Scaling with 100s of App Servers

The hard connection limit was actually 20,000 but it was removed in version 2.6 and you are now only limited by memory or open file descriptors (whichever you run out of first).  That being said, with connections running into the thousands, even if you can manage it you will be using a lot of memory (1MB per connection for the stack).

At that point you start to evaluate other options for mongos deployment.  You can have a dedicated set of mongos hosts and take advantage of some of the nice HA options built into the drivers and pass in an list of possible mongos processes to use.  If you are concerned about latency, you could potentially collocate (some of) the mongos processes on your shard hosts.

Basically you've started to hit the number of nodes where you are into more advanced design decisions and it really depends on several factors as to what is right for you now, and what is right for you in the future.  If you have some money to throw at it, this is perfect fodder for the type of consulting MongoDB offer (disclosure: I was once one of the people delivering such consulting).  If not, you are asking the right questions, so keep an eye on resource utilisation and see what makes sense for your deployment as you grow.


Which table gets the FK? Is there a clear Parent-Child relationship here?

If the business requirement is

a business rule, every part must belong to exactly one product line.

So if the rule is, each part belongs to one product line the other side of that relationship is each product line contains zero-to-many parts which should convince you this is a clear instance of a parent/child relationship.
How a something exists in the real world versus how it exists within a business context/database are not always strongly tied.  If I have a part on my desk I do not care about it's product line.  But if I'm a business and I am selling the part I will probably need to categorize that part for search/reporting/sales/commission purposes.
The other columns you will probably need FKs on (PartType, Grade, Family, PopularityCode, UnitClass) will have the same physical implementation (parent table, foreign key reference) even if the context might not be the same (Each Part is described by One Part Type/PartType describes zero-to-many Parts, etc).
Won't get into the column types or other aspects of the design because that's not pertinent to the question but I'll be happy to provide guidance elsewhere.


SQL Server 2008 R2 Most Efficient Full Text Search Field Type

varbinary is for binary data.

nvarchar is for character data.

Regardless of what the full text index is capable of indexing, use a data type suited to the domain.

Also, you will be able to see the contents of a nvarchar column in directly SSMS.

Best Practices for Choosing a Language When Creating a Full-Text Index
Best Practices for Integrated Full Text Search (iFTS) in SQL 2008

Performance issues are more likely to do with the frequency of maintenence of indexes, and hardware such as where your data is stored (as in a separate Filegroup), and memory usage, and indexing type (full population, incremental, manual, and auto change tracking population).

  Note that SQL Server 2008 full-text search is more aggressive in
  memory usage and may use more I/O; 64‑bit architectures are better
  than 32‑bit in doing crawl tasks in general.

  Sometimes crawl can be too aggressive in committing memory and this
  could cause sqlserver/system to have fewer resources than needed. It
  is highly recommended that you do the following:

  Limit the memory used by the crawl (full population) by setting max server memory to an appropriate value to prevent the crawl task from
  taking all available memory.
  Increase the page file size if applicable.

  Indexing performance is generally superior to SQL Server 2005.
  However, in certain situations there may be some performance
  degradation as follows:

  Crawl on large documents (~>8KB). In non 64‑bit platforms, this might be slower in some scenarios.
  varchar type data. This is because SQL Server 2005 does a better job with the varchar type than with a text type, whereas in the new
  full-text architecture there is no difference in theory between varchar data and text data.
  Noninteger keys, especially GUID columns used as a full-text key
  Too many unique keywords. It has been shown in random document crawl modes that performance can be slower than SQL Server 2005 (when
  uniquekeycount > 80 million).

  Ref: SQL Server 2008 Full-Text Search: Internals and Enhancements


Problem reading data from secondary when reorganizing clustered index

So, after running out of possible solutions we opened a support case to Microsoft. They asked to run a tool to gather some info while the process was running and afterwards they analyzed it. Here is their answer:

You select command is running fine if it starts before reorganize index job is initiated
Select command is failing if it starts after re-organize job is initiated.
Found the above behavior is expected behavior in AG.

Even though the read operations do not take shared locks because of row versioning, these operations take schema stability (Sch-S) locks, which can block redo operations that are applying DDL changes. DDL operations include ALTER/DROP tables and Views but not DROP or ALTER of stored procedures.
In our case while re-organize index is running on primary redo operation for the same is being performed on secondary replica and is acquiring Sch-M (Schema modification locks), when select command is trying to access the same replica it is not able to acquire Sch-S (Schema Stability) locks as it is already occupied by redo thread which is having Sch-M locks.
In this scenario your application is generating errors including time out errors.

To avoid this kind of situations it is recommended to schedule re-organize index task in off business hours

We don't have "off business hours", we run 24/7/365. Is not a definitive answer, but at least we know the root cause of this issue. So the approach will be to temporarily change connection string so the task that is failing will read from primary AG node instead of secondary AG node the day the reindex run.


Many to Many association table PK as FK in other table

The culprit is the autogenerated key: school_teacher is a many-to-many junction table using an autogenerated PK and the FKs of school and teacher..

Why do you need it? If school and teacher is a candidate key I would concider that as the p.k.

EDIT: sketch

CREATE TABLE SCHOOLS
( SCHOOL_ID ... NOT NULL PRIMARY KEY
, ...
);

CREATE TABLE TEACHERS
( TEACHER_ID ... NOT NULL PRIMARY KEY
, ...
);

CREATE TABLE SCHOOL_TEACHERS
( SCHOOL_ID ... NOT NULL
      REFERENCES SCHOOLS (SCHOOL_ID)
, TEACHER_ID ... NOT NULL PRIMARY KEY
      REFERENCES TEACHERS (TEACHER_ID)

,     PRIMARY KEY (SCHOOL_ID, TEACHER_ID)
);

I find the rule "A student belongs to a teacher of a particular school" a bit odd, but if that is what you like to enforce:

CREATE TABLE STUDENTS
( STUDENT_NO NOT NULL PRIMARY KEY
, SCHOOL_ID ... NOT NULL
, TEACHER_ID ... NOT NULL

,     FOREIGN KEY (SCHOOL_ID, TEACHER_ID)
      REFERENCES SCHOOL_TEACHERS (SCHOOL_ID, TEACHER_ID)
, ...
);


Does SQL Server cache the result of a multi-statement table-valued function?

The results of a multi-statement table-valued function (msTVF) are never cached or reused across statements (or connections), but there are a couple of ways that an msTVF result may be reused within the same statement. To that extent, an msTVF is not necessarily repopulated each time it is called.

Example msTVF

This (deliberately inefficient) msTVF returns a specified range of integers, with a timestamp on each row:

IF OBJECT_ID(N'dbo.IntegerRange', 'TF') IS NOT NULL
    DROP FUNCTION dbo.IntegerRange;
GO
CREATE FUNCTION dbo.IntegerRange (@From integer, @To integer)
RETURNS @T table
(
    n integer PRIMARY KEY,
    ts datetime DEFAULT CURRENT_TIMESTAMP
)
WITH SCHEMABINDING
AS
BEGIN
    WHILE @From <= @To
    BEGIN
        INSERT @T (n)
        VALUES (@From);

        SET @From = @From + 1;
    END;
    RETURN;
END;

Static table variable

If all the parameters to the function call are constants (or runtime constants), the execution plan will populate the table variable result once. The remainder of the plan may access the table variable many times. The static nature of the table variable can be recognised from the execution plan. For example:

SELECT
    IR.n,
    IR.ts
FROM dbo.IntegerRange(1, 5) AS IR
ORDER BY
    IR.n;

Returns a result similar to:

The execution plan is:

The Sequence operator first calls the Table Valued Function operator, which populates the table variable (note this operator returns no rows). Next, the Sequence calls its second input, which returns the contents of the table variable (using a Clustered Index Scan in this case).

The giveaway that the plan is using a 'static' table variable result is the Table Valued Function operator below a Sequence - the table variable needs to be fully populated once before the remainder of the plan can get going.

Multiple accesses

To show the table variable result being accessed more than once, we will use a second table with rows numbered from 1 to 5:

IF OBJECT_ID(N'dbo.T', 'U') IS NOT NULL
    DROP TABLE dbo.T;

CREATE TABLE dbo.T (i integer NOT NULL);

INSERT dbo.T (i)
VALUES (1), (2), (3), (4), (5);

And a new query that joins this table to our function (this could equally be written as an APPLY):

SELECT T.i,
       IR.n,
       IR.ts
FROM dbo.T AS T
JOIN dbo.IntegerRange(1, 5) AS IR
    ON IR.n = T.i;

The result is:

The execution plan:

As before, the Sequence populates the table variable msTVF result first. Next, nested loops is used to join each row from table T to a row from the msTVF result. Since the function definition included a helpful index on the table variable, an index seek can be used.

The key point is that when the parameters to the msTVF are constants (including variables & parameters) or treated as runtime constants for the statement by the execution engine, the plan will feature two separate operators for the msTVF table variable result: one to populate the table; another to access the results, possibly accessing the table multiple times, and possibly making use of indexes declared in the function definition.

Correlated parameters and non-constant parameters

To highlight the differences when correlated parameters (outer references) or non-constant function parameters are used, we will change the contents of table T so the function has much more work to do:

TRUNCATE TABLE dbo.T;

INSERT dbo.T (i)
VALUES (50001), (50002), (50003), (50004), (50005);

The following modified query now uses an outer reference to table T in one of the function parameters:

SELECT T.i,
       IR.n,
       IR.ts
FROM dbo.T AS T
CROSS APPLY dbo.IntegerRange(1, T.i) AS IR
WHERE IR.n = T.i;

This query takes around 8 seconds to return results like:

Notice the time difference between rows in column ts. The WHERE clause limits the final result for a sensibly-sized output, but the inefficient function still takes a while to populate the table variable with 50,000-odd rows (depending on the correlated value of i from table T).

The execution plan is:

Notice the lack of a Sequence operator. Now, there is a single Table Valued Function operator that populates the table variable and returns its rows on each iteration of the nested loops join.

To be clear: with just 5 rows in table T, the Table Valued Function operator runs 5 times. It generates 50,001 rows on the first iteration, 50,002 on the second...and so on. The table variable is 'thrown away' (truncated) between iterations, so each of the five calls is a full population. This is why it is so slow, and each row takes about the same time to appear in the result.

Side notes:

Naturally, the scenario above is deliberately contrived to show how poor performance can be when the msTVF populates many rows on each iteration.

A sensible implementation of the above code would set both msTVF parameters to i, and remove the redundant WHERE clause. The table variable would still be truncated and repopulated on each iteration, but only with one row each time.

We could also fetch the minimum and maximum i values from T and store them in variables in a prior step. Calling the function with variables instead of correlated parameters would allow the 'static' table variable pattern to be used as noted earlier.

Caching for unchanged correlated parameters

Returning to address the original question once more, where the Sequence static pattern cannot be used, SQL Server can avoid truncating and repopulating the msTVF table variable if none of the correlated parameters have changed since the prior iteration of a nested loop join.

To demonstrate this, we will replace the contents of T with five identical i values:

TRUNCATE TABLE dbo.T;

INSERT dbo.T (i)
VALUES (50005), (50005), (50005), (50005), (50005);

The query with a correlated parameter again:

SELECT T.i,
       IR.n,
       IR.ts
FROM dbo.T AS T
CROSS APPLY dbo.IntegerRange(1, T.i) AS IR
WHERE IR.n = T.i;

This time the results appear in around 1.5 seconds:

Note the identical timestamps on each row. The cached result in the table variable is reused for subsequent iterations where the correlated value i is unchanged. Reusing the result is much faster than inserting 50,005 rows each time.

The execution plan looks very similar to before:

The key difference is in the Actual Rebinds and Actual Rewinds properties of the Table Valued Function operator:

When the correlated parameters do not change, SQL Server can replay (rewind) the current results in the table variable. When the correlation changes, SQL Server must truncate and repopulate the table variable (rebind). The one rebind happens on the first iteration; the four subsequent iterations are all rewinds since the value of T.i is unchanged.


What is transaction-managed data blocks?

It's a block that can be modified transactionally.

The best explanation is probably from page 106 in Tom Kyte's Expert Oracle Database Architecture

  ... transaction information when relevant (only blocks that are
  transaction-managed have this information-- a temporary sort block
  would not, for example) regarding active and past transactions on
  the block ...

Most blocks, of course, are subject to transactional modification so they would have transaction information.


Creating a view (or table) from JSON data with two different shapes

I recommend not to store the data as JSON in the database, particularly if you want to query them with SQL. Storing the data will be easy that way, but your queries will be much more complicated and slower.

If you want to use several SQL statements in client code or a function in the database should mostly depend on the question if you want to keep your business logic in the application or in the database.

There shouldn't be too many round trips necessary if you use RETURNING as in

INSERT INTO artist (name)
VALUES ('Jethro Tull')
RETURNING id;

You can even insert several rows with a construct like

WITH a AS (
   INSERT INTO artist (name)
   VALUES ('Jethro Tull')
   RETURNING id
)
INSERT INTO album (name, artist_id)
SELECT ('Catfish Rising', a.id)
FROM a;


Should a MySQL replication slave be set to read only?

When a Slave is read-only, it is not 100% shielded from the world.
According to MySQL Documentation on read-only

This variable is off by default. When it is enabled, the server permits no updates except from users that have the SUPER privilege or (on a slave server) from updates performed by slave threads. In replication setups, it can be useful to enable read_only on slave servers to ensure that slaves accept updates only from the master server and not from clients.

Thus, anyone with SUPER privilege can read and write at will to such a Slave...
Make sure all non-privileged users do not have the SUPER Privilege.
If you want to revoke all SUPER privileges in one shot, please run this on Master and Slave:
UPDATE mysql.user SET super_priv='N' WHERE user<>'root';
FLUSH PRIVILEGES;

With reference to the Slave, this will reserve SUPER privilege to just root and prevent non-privileged from doing writes they would otherwise be restricted from.
UPDATE 2015-08-28 17:39 EDT
I just learned recently that MySQL 5.7 will introduce super_read_only.
This will stop SUPER users in their tracks because the 5.7 Docs say

If the read_only system variable is enabled, the server permits client updates only from users who have the SUPER privilege. If the super_read_only system variable is also enabled, the server prohibits client updates even from users who have SUPER. See the description of the read_only system variable for a description of read-only mode and information about how read_only and super_read_only interact.
Changes to super_read_only on a master server are not replicated to slave servers. The value can be set on a slave server independent of the setting on the master.
super_read_only was added in MySQL 5.7.8.


What is the performance impact of using CHAR vs VARCHAR on a fixed-size field?

A similar question was asked before

Performance implications of MySQL VARCHAR sizes

Here is the excerpt of my answer

You must realize the tradeoffs of using CHAR vs VARCHAR

With CHAR fields, what you allocate is exactly what you get. For example, CHAR(15) allocates and stores 15 bytes, no matter how characters you place in the field. String manipulation is simple and straightforward since the size of the data field is totally predictable.

With VARCHAR fields, you get a completely different story. For example VARCHAR(15) actually allocates dynamically up to 16 bytes, up to 15 for data and, at least, 1 additional byte to store the the length of the data. If you have the string 'hello' to store that will take 6 bytes, not 5. String manipulation must always perform some form of length checking in all cases.

The tradeoff is more evident when you do two things:
 1. Storing millions or billions of rows
 2. Indexing columns that are either CHAR or VARCHAR

TRADEOFF #1
 Obviously, VARCHAR holds the advantage since variable-length data would produce smaller rows and, thus, smaller physical files.

TRADEOFF #2
 Since CHAR fields require less string manipulation because of fixed field widths, index lookups against CHAR field are on average 20% faster than that of VARCHAR fields. This is not any conjecture on my part. The book MySQL Database Design and Tuning performed something marvelous on a MyISAM table to prove this. The example in the book did something like the following:

ALTER TABLE tblname ROW_FORMAT=FIXED;

This directive forces all VARCHARs to behave as CHARs. I did this at my previous job back in 2007 and took a 300GB table and sped up index lookups by 20%, without changing anything else. It worked as published. However, it did produce a table almost double in size, but that simply goes back to tradeoff #1.

You could analyze the data being stored to see what MySQL recommends for column definition. Just run the following against any table:

SELECT * FROM tblname PROCEDURE ANALYSE();

This will traverse the entire table and recommend column definitions for every column based on the data it contains, the minimum field values, maximum field values, and so forth. Sometimes, you just have to use common sense with planning CHAR vs VARCHAR. Here is a good example:

If you are storing IP addresses, the mask for such a column is at most 15 characters (xxx.xxx.xxx.xxx). I would jump right at CHAR(15) in a heartbeat because the lengths of IP addresses will not vary all that much and the added complexity of string manipulation controlled by an additional byte. You could still do a PROCEDURE ANALYSE() against such a column. It may even recommend VARCHAR. My money would still be on CHAR over VARCHAR in this instance.

CHAR vs VARCHAR issues can be resolved only through proper planning. With great power comes great responsibility (cliche but true).

UPDATE

When it comes to MD5, the computation of strlen internally should be eliminated when switching  the entire row format. There would be no need to change the field definition.

If the MD5 key is the only VARCHAR present, I would go for it and convert the table row format to fixed. If there is a signifcant number of other VARCHAR fields present, they would benefit as well. In exchange, the table would expand to about twice its size. But queries should accelerate about 20% more without additional tuning.


Table design: paired values of genetic data

Your first attempt makes by far the most sense - (the one with 4 fields).

As a rule, tables should be "tall and slim" rather than "short and fat"

It means that you can easily compare different alleles (for the same locus) belonging to the same individual - which is MUCH more difficult with your second schema.

The second schema means that you will have to use windowing/analytic
functions far more frequently - and if you're using MySQL (which is very
common in biology unfortunately) you won't have that facility.

Speaking as someone with both genetic and computer science degrees, I'd go with the first schema - I've worked with similar data (FragileX) - comparing different alleles - it was important for us to distinguish the father and the mother (we were doing multi-generational pedigrees).

I would recommend you include (arbitrary) - allele1 as the father's and allele2 as the mothers as part of your design - you never know when this knowledge might come in useful. Maybe you could add another field (isParentKnown) so that you can distinguish between those samples for which the parents are known  from the ones for which they are not.


I assume you mean how to get the INSERT statement to execute without error? (A column doesn't "execute".)
Apparently, there is no column named "SIS ID" in the dbo.eNtsaRegPeopleLists table. That is what the error message is telling you.
You did right by enclosing the column name in square brackets (double quotes is another option). So double-check what the column name really is. Perhaps the square brackets are part of the column name. Or that the space between SIS and ID isn't a space but some other invisible character (like char(255)). We can't say which it is, so it is for you to determine. One option is to do:
EXEC sp_help 'eNtsaRegPeopleLists'

And see what output you get for the column names.


Replace/upgrade mirrored server hardware

I think I would first outline something like this before testing it while recording time with a stopwatch.

D-X

get the newProd and newMirror ready (OS + SQL Server + User Account, job, ...)
build scripts for every step (back, restore, mirror, ...)

D-3

add a new IP to oldProd
update DNS with new IP and TTL = 300s (Time To Live)

D, H-2 or H-1 (I assume backup and restore both take 30min)

full backup of oldProd
restore newProd and newMirror

D, H (Downtime)

Stop applications and DB access
Backup logs on oldProd
Restore logs on newProd
Remove new IP from oldProd
Add new IP to newProd
Start applications and DB access

D,  H+something

Build new mirror between newProd and newMirror

Another option for 4. (downtime should be lower)

Break mirror with oldMirror
Backup/Restore logs to newProd
Create new mirror between oldProd and newMirror
Manual failover to newProd
Break new mirror
Create new mirror between newProd and newMirror

I may have forgotten a couple things.


Too much data queued in replication slots

You are measuring how much WAL you have retained on the primary not yet sent to the replica.  But the hard drive filling up is the one on the replica, not the one on the master, so this is not the right thing to measure.

So what you need to measure is the amount of WAL received by the replica, but not yet replayed and eligible for removal.  You can do this by connecting to the replica (if it is in hot-standby mode) and running pg_last_wal_receive_lsn() and  pg_last_wal_replay_lsn().  But that is probably not quite right either, as just because it has been replayed doesn't mean it can be and has been removed, it still has to wait for the restartpoint.  So it would probably be better to check the disk space directly, for example by using ssh to run a df command.


Create and use Fulltext Index in a Stored Procedure

I don't believe you can, at least not within the same proc.

Stored procs are compiled before running, so that command is going to be checked for validity before it executes.

Full text is an entirely separate executable, and while you can certainly issue SQL commands that create a new full text index, or initiate a rebuild of one, or whatever, those commands aren't going to work like normal SQL commands (they can't be within an ATOMic transaction, they can't possibly roll back, etc.).

This means that just because the CREATE FULLTEXT INDEX statement succeeds, that doesn't guarantee the index is populated and/or useful.

It's like issuing a msdb.dbo.sp_start_job to run a SQL agent job. The statement finishes quickly and returns control, but the agent could very well still be running (for a very long time).

Maybe you can trick it by calling separate stored procs, one that creates the full text index and kicks off a full build, and then another that checks the status of that build and loops till it is complete, before calling a proc that runs the query?

It seems likely to me that you will return a value faster by doing a normal WHERE Scrubbed_Account_Number LIKE '%blahblah%', even though that will do a full table scan.

In fact, I guarantee that will be faster, since it will have to do a full table scan to build the full text index.


Compare Past Records to Most Recent Record

One way would be using CROSS APPLY.

If you identify the most recent record for each product in the first table and then pass the criteria into the cross apply.

SELECT
      ProductId
   ,  MAX(CASE WHEN t1.RunFailure = t2.RunFailure THEN 1 ELSE 0 END) AS IsMostCurrentRunSameLevelAsLastFailedRun
   FROM <SomeTable> t1
   CROSS APPLY (
      SELECT TOP 1
         RunFailure
         FROM <SomeTable> st
         WHERE st.ProductId = t1.ProductId
         AND   st.LevelCode = t1.LevelCode
         AND   st.SampleDate < t1.SampleDate
         ORDER BY st.SampleDate) AS t2
   GROUP BY t1.ProductId

Alternatively, look into the windowing functions. You can do a great deal with them that may be more efficient.