Because no errors are shown (But this 2021-01-01T09:15:45.594^C297 does not look OK!), you should proceed with the next step as shown in the procedure.
Do a check if your password is changed.
If your password is not changed, please create a simple init-file (i.e. the same as is suggested in step 3), and try again.
If your password is changed, then do not forget to delete the init-file.
EDIT
:  I did a reset of my 'root' password following the procedure.
All output of this session is here, with some NOTE's in between:
First we notice that we do not know the password:
C:\Program Files\MySQL\MySQL Server 8.0>mysql -u root -p
Enter password: *******
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)

C:\Program Files\MySQL\MySQL Server 8.0>

Then a mysql-init.txt file is created:
C:\Program Files\MySQL\MySQL Server 8.0>type mysql-init.txt
ALTER USER 'root'@'localhost' IDENTIFIED BY 'MyNewPass';

Then we stop the MySQL server. My server is name MySQL80, but you can also stop it from the services window (more details here)
C:\Program Files\MySQL\MySQL Server 8.0>net stop MySQL80
The MySQL80 service is stopping.
The MySQL80 service was stopped successfully.

Password reset with:

Note the double \\ in the path names, and the " surrounding the complete path
My default settings for MySQL are located here "D:\MySQL Server 8.0\my.ini", this is almost sure different at your location

    C:\Program Files\MySQL\MySQL Server 8.0>bin\mysqld.exe --defaults-file="D:\\MySQL Server 8.0\\my.ini" --init-file="C:\\Program Files\\MySQL\\MySQL Server 8.0\\mysql-init.txt" --console
    2021-01-01T18:51:09.333120Z 0 [Warning] [MY-010915] [Server] 'NO_ZERO_DATE', 'NO_ZERO_IN_DATE' and 'ERROR_FOR_DIVISION_BY_ZERO' sql modes should be used with strict mode. They will be merged with strict mode in a future release.
    2021-01-01T18:51:09.333840Z 0 [System] [MY-010116] [Server] C:\Program Files\MySQL\MySQL Server 8.0\bin\mysqld.exe (mysqld 8.0.22) starting as process 21888
    2021-01-01T18:51:09.341869Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.
    2021-01-01T18:51:09.908748Z 1 [System] [MY-013577] [InnoDB] InnoDB initialization has ended.
    2021-01-01T18:51:10.083573Z 0 [System] [MY-011323] [Server] X Plugin ready for connections. Bind-address: '::' port: 33060
    2021-01-01T18:51:10.143779Z 0 [Warning] [MY-010068] [Server] CA certificate ca.pem is self signed.
    2021-01-01T18:51:10.144078Z 0 [System] [MY-013602] [Server] Channel mysql_main configured to support TLS. Encrypted connections are now supported for this channel.
    2021-01-01T18:51:10.192745Z 0 [System] [MY-010931] [Server] C:\Program Files\MySQL\MySQL Server 8.0\bin\mysqld.exe: ready for connections. Version: '8.0.22'  socket: ''  port: 3306  MySQL Community Server - GPL.

Now the server is started with output to console, to stop the server type CTRL+C:
2021-01-01T18:51:20.697066Z 0 [System] [MY-013105] [Server] C:\Program Files\MySQL\MySQL Server 8.0\bin\mysqld.exe: Normal shutdown.
^C
C:\Program Files\MySQL\MySQL Server 8.0>2021-01-01T18:51:21.447960Z 0 [System] [MY-010910] [Server] C:\Program Files\MySQL\MySQL Server 8.0\bin\mysqld.exe: Shutdown complete (mysqld 8.0.22)  MySQL Community Server - GPL.

We start the server again, the normal way (or via services):
C:\Program Files\MySQL\MySQL Server 8.0>net start mysql80
The MySQL80 service is starting.
The MySQL80 service was started successfully.

And we try to logon:
C:\Program Files\MySQL\MySQL Server 8.0>mysql -u root -pMyNewPass
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 8.0.22 MySQL Community Server - GPL

Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> alter user 'root'@'localhost' IDENTIFIED BY 'stackoverflow';

With this last command the password can be changed to 'stackoverflow'. I am sure any who read this can think of a better password.


SQL Server Full-text population deadlock

Lets answer question 2 first. As you see in your first error message, it tells you to issue an:

ALTER FULLTEXT INDEX ON <table_name> RESUME POPULATION

If the reindex started automatically, this would not be needed.

Now, as to the first issue, let's work this through.

The Fulltext index build needs to ensure that the text of the fields used in the nonpersisted computed column does not get updated while the index is being rebuilt. Otherwise, the index would be corrupted. This will probably be done by holding shared page locks on the data while the persisted data is being built, then by holding exclusive locks on the data while the index is being updated.

On the other hand, the Bulk Copy process is attempting to add rows, which will update pages in the table. Since the process is Atomic (e.g. will ensure that all of the data is inserted correctly before releasing the implicit transaction), the process will check for the correct pages to insert (possibly pseudo-random sequence) data into, then lock the page to insert that data, and hold the lock until all of the data is in place. This can result in the deadlocks you are experiencing.

For the bulk update process, while updating, the JOINs will be executed, using Shared locks to identify the rows to be updated, then exclusive locks (on either the rows or the pages, depending) will be taken to perform the update. Again, if the sequence of rows/pages is pseudo-random compared to the sequence of pages checked by the FT index rebuild, some pages could be locked that the rebuild needs, while others could be locked by the rebuild that the update needs.

How could this be fixed? That is a more difficult question. It would be less likely to happen if you took out the overhead of calculating the non-persisted data in the index build (by persisting the field), but this would expand your data usage. You could also implement some form of a flag that the other processes check, turning it on before starting the rebuild and off after the rebuild completes, that would wait those processes until the rebuild is done.

There may be other possible solutions, but those are all I can think of at this moment.


Why does postgres choose to sort instead of scanning the index?

Generally speaking, reading a tuple from an index is more expensive then reading directly  from the relation.
This is due to the overhead of traversing the tree as well as occasionally reading from the table (due to changes for example)

Thus, if you scan the entire table Postgres will come to the conclusion that it should scan the table.
Limiting the scan immediately changes the above as the number of tuples that need scanning is much more limited.


What are Objective Business Reasons to Prefer SQL Server 2008R2 over 2012?

I don't know that you can really be objective about choosing a lesser version over a current version. It is more or less forced eventually. Hardware goes out of date and purchasing any new hardware may require using a more current version of Windows Operating System; which that in turn requires you to upgrade SQL Server. I actually had conference call over a client that is in this very boat now.

As well your hand can be forced by Microsoft's release plan. Once a major version is released you will reach a point that you can no longer purchase the older version. Which is the case now with SQL Server 2012, you can't purchase a SQL Server 2008 R2 license. You may use that version but you will have to purchase under the SQL Server 2012 licensing model. SQL Server 2014 CTP2 was just released the other day so after a certain number of days/months after the RTM release you will not be able to purchase SQL Server 2012.

The overall decision has to come from the business' money holders. Business meetings I have taken part in the one thing that is always considered when building a new system out for an application is licensing considerations (support maintenance annual cost, licensing upgrades, etc.). The pros and cons of licensing what version/edition should always be considered up front. I have been in environments where the business was feed the wrong information on what quantity/type of license to purchase, then when they were audited because of industry standards they got bitten pretty hard from it (or the scape goat got fired). It was ultimately their responsibility.

The best thing I would suggest is looking at Software Assurance. Especially since in the two years covered SQL Server 2014 should be released. You may pay a good bit more now, but with the discount pricing you will save a considerable amount of money when you go ahead and purchase SQL Server 2014 licenses. Especially if Microsoft decides to change their licensing model again, although I have heard nothing that indicates they are going to.


PostgreSQL: Calculate balance working backwards from current balance


How much data is needed to show MySQL Cluster's performance scaling vs. InnoDB

MySQL Cluster stores indexed columns in memory, so the largest practical data size for an in-memory cluster is around 3GB.  If you store tables with non-indexed columns on disk, then you can scale the dataset further

As MySQL Cluster automatically shards your tables across multiple nodes, with each node being a master. This gives very high performance for OLTP workloads, especially those that are write-intensive.  To get the best levels of performance, you need to introduce load to the cluster, ie configure multiple API nodes, with each node configured with multiple threads to access the Cluster.

The latest 7.2GA release of MySQL Cluster includes new functionality called Adaptive Query Localization, which serves to push JOIN queries down to the data nodes, reducing network hops and therefore latency.

To get the best handle on performance, there is a Performance Guide published as follows (note, registration is required):
http://www.mysql.com/why-mysql/white-papers/guide-to-optimizing-performance-of-the-mysql-cluster/


Correct way to restore dbs to new server

I am not sure what your issue is here.

When you restore the database, the data (mdf) and log (ldf) files have be on disk someplace.  The WITH MOVE just tells SQL where to put them.
You can't have a SQL server without databases, every install will have 4 databases out of the box, master, msdb, model & tembdb, they are system databases, and they each have mdf & ldf files someplace, just like the user database you want to restore.
As  scsimon mentions in a comment, you probably do not want your SQL files on the C drive, best practice explained to me is for to have 4 drives for SQL,

Data Drive (mdf)
Log file drive (ldf)
Tempdb (mdf & ldf for tempdb)
Backups (bak & trn, which should be copied off at least nightly)


Understanding Oracle "LOG_ARCHIVE_START" parameter

First of all, the documentation that you have referenced are not from Oracle 12c, one is from Oracle 9i and another one is not the official documentation.

Second of all, the parameter log_archive_start is deprecated in Oracle 10g, meaning no longer available in the next release.

  Starting with Oracle Database 10g Release 1 (10.1), the LOG_ARCHIVE_START initialization parameter has been deprecated. Archiving is now automatically started when the database is placed in ARCHIVELOG mode.

Database Upgrade Guide 10g

Finally, You can use the following query to select all the deprecated parameters.

SQL> SELECT name from v$parameter
     WHERE isdeprecated = 'TRUE' ORDER BY name;

Documentation: Deprecated and Desupported Features


The short answer is you can't. The rules for the expression of the generated column are (see create-table-generated-columns.html):

Generated column expressions must adhere to the following rules. An
error occurs if an expression contains disallowed constructs.

Literals, deterministic built-in functions, and operators are
permitted. A function is deterministic if, given the same data in
tables, multiple invocations produce the same result, independently of
the connected user. Examples of functions that are nondeterministic
and fail this definition: CONNECTION_ID(), CURRENT_USER(), NOW().

Stored functions and user-defined functions are not permitted.

Stored procedure and function parameters are not permitted.

Variables (system variables, user-defined variables, and stored
program local variables) are not permitted.

Subqueries are not permitted.

A generated column definition can refer to other generated columns,
but only those occurring earlier in the table definition. A generated
column definition can refer to any base (nongenerated) column in the
table whether its definition occurs earlier or later.

[...]


Regarding create index and lock word in SQL statement used for SQL Server

Three questions in one ....

OK, here are some answers:

The INCLUDE(Title) option is new in SQL Server 2005. It does NOT create an index on that column - the index is created on (QueueDateTime, Status) only. But in the leaf level of that non-clustered index, the Title column's value is included - so if you have a query something like:

SELECT QueueDateTime, Title
FROM dbo.YourTable
WHERE QueueDateTime > (some value) AND Status = 1

then this query can be satisfied by using just the index leaf level pages - the index covers the query - you don't need to go back to the actual data tables to get your answer to your query.

This can very significantly improve performance, and increase scalability, too.

See a good explanation of the benefits of including columns in non-clustered indexes here.

As for the WITH (UPDLOCK, HOLDLOCK) - you should check out this other SO question on the topic and the excellent answer provided by Andrew. And here's yet another question on the same topic with an excellent answer by Remus Rusanu.

Both table hints were available at least since SQL Server 2000 (or possibly even earlier - I can't remember versions earlier than that, though).


Comprehensive troubleshooting of DatabaseMail

I checked which .Net versions I had installed

then I installed .NET 3.5 and all started to work nicely.

I believe it is poorly documented (or at least for me) the fact that DatabaseMail needs .NET 3.5.

SQL Server 2016 - Database Mail doesn't work without .NET 3.5

How can I see the current Database Mail configuration?

EXEC msdb.dbo.sysmail_help_configure_sp;
EXEC msdb.dbo.sysmail_help_account_sp;
EXEC msdb.dbo.sysmail_help_profile_sp;
EXEC msdb.dbo.sysmail_help_profileaccount_sp;
EXEC msdb.dbo.sysmail_help_principalprofile_sp;

EXEC msdb.dbo.sysmail_help_account_sp

this link below has also helped me specially when I need to copy the database mail settings from one server into another:

The database mail configuration saved into a temp table

I got the following script that helps troubleshooting databasemail basics.

Pay attention to the comments and run one step at a time.

USE msdb
 GO

-- Check that the service broker is enabled on MSDB.
-- Is_broker_enabled must be 1 to use database mail.
SELECT is_broker_enabled FROM sys.databases WHERE name = 'msdb';
-- Check that Database mail is turned on.
-- Run_value must be 1 to use database mail.
-- If you need to change it this option does not require
-- a server restart to take effect.
EXEC sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
EXEC sp_configure 'Database Mail XPs';

-- Check the Mail queues
-- This system stored procedure lists the two Database Mail queues.
-- The optional @queue_type parameter tells it to only list that queue.
-- The list contains the length of the queue (number of emails waiting),
-- the state of the queue (INACTIVE, NOTIFIED, RECEIVES_OCCURRING, the
-- last time the queue was empty and the last time the queue was active.
EXEC msdb.dbo.sysmail_help_queue_sp -- @queue_type = 'Mail' ;

-- Check the status (STARTED or STOPPED) of the sysmail database queues
-- EXEC msdb.dbo.sysmail_start_sp -- Start the queue
-- EXEC msdb.dbo.sysmail_stop_sp -- Stop the queue
EXEC msdb.dbo.sysmail_help_status_sp;

-- Check the different database mail settings.
-- These are system stored procedures that list the general
-- settings, accounts, profiles, links between the accounts
-- and profiles and the link between database principles and
-- database mail profiles.
-- These are generally controlled by the database mail wizard.

EXEC msdb.dbo.sysmail_help_configure_sp;
EXEC msdb.dbo.sysmail_help_account_sp;
--  Check that your server name and server type are correct in the
--      account you are using.
--  Check that your email_address is correct in the account you are
--      using.
EXEC msdb.dbo.sysmail_help_profile_sp;
--  Check that you are using a valid profile in your dbmail command.
EXEC msdb.dbo.sysmail_help_profileaccount_sp;
--  Check that your account and profile are joined together
--      correctly in sysmail_help_profileaccount_sp.
EXEC msdb.dbo.sysmail_help_principalprofile_sp;

-- I’m doing a TOP 100 on these next several queries as they tend
-- to contain a great deal of data.  Obviously if you need to get
-- more than 100 rows this can be changed.
-- Check the database mail event log.
-- Particularly for the event_type of "error".  These are where you
-- will find the actual sending error.
SELECT TOP 100 *
FROM msdb.dbo.sysmail_event_log
ORDER BY last_mod_date DESC;

-- Check the actual emails queued
-- Look at sent_status to see 'failed' or 'unsent' emails.
SELECT TOP 100 *
FROM msdb.dbo.sysmail_allitems
ORDER BY last_mod_date DESC;

-- Check the emails that actually got sent.
-- This is a view on sysmail_allitems WHERE sent_status = 'sent'
SELECT TOP 100 *
FROM msdb.dbo.sysmail_sentitems
ORDER BY last_mod_date DESC;

-- Check the emails that failed to be sent.
-- This is a view on sysmail_allitems WHERE sent_status = 'failed'
SELECT TOP 100 *
FROM msdb.dbo.sysmail_faileditems
ORDER BY last_mod_date DESC

-- Clean out unsent emails
-- Usually I do this before releasing the queue again after fixing the problem.
-- Assuming of course that I don't want to send out potentially thousands of
-- emails that are who knows how old.
-- Obviously can be used to clean out emails of any status.
EXEC msdb.dbo.sysmail_delete_mailitems_sp
  @sent_before =  '2017-09-28',
  @sent_status = 'failed';


How to debug an hanging impdp importation

I got it to work with a weird fix : I removed the 'APPLICATION/APPLICATION@XE' and entered the username/password manually with the interactive prompt.

I consulted this page: http://www.orafaq.com/wiki/Datapump and realized that I was not getting an equivalent of this line Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production, which tipped me it was a connection problem. By trying to remove the username/password from the command line it suddenly fixed the problem.

I am afraid I can't exactly say why this helped, but I hope this can be useful to someone.


Does HOT Update re-arrange existing rows on a page to create a contiguous space?

No, HOT doesn't defragment pages. The cost of doing the needed index updates would be excessive.

PostgreSQL would write the new version of row 1 to a new page.


How to recover a database from a failed Availability Group?

The first attempt would be to try to get either the primary replica back online, or to get the synchronized secondary replica online to ensure that there is no data loss.  But provided this is a true disaster recovery scenario (as your original question implies), then it would seem as though your actual cluster (WSFC) has failed.  With the database entering the recovery_pending state, you can't unjoin the database to recover it.  Likewise, you would need the cluster in order to do a failover.  So the following assumes that you can't force quorum on either of the sync replicas, and you need to failover to the async.

The next step at this point to recover to the async secondary that is currently online, would be to force quorum (WSFC) and then perform a forced failover (with the potential for data loss).

For more information, I highly recommend you read up on BOL regarding the topic Perform a Forced Manual Failover of an Availability Group


If you are looking for data models to start with, I'd recommend you take a look at: http://www.databaseanswers.org/data_models/index.htm

This is the most comprehensive list of data model examples that I found so far. In fact I have used some samples as starting point for some of my projects. These models won't show you how to take horizontal fragmentation into consideration, though.

Update:

You may consider these basic techniques for horizontal fragmentation*:

Range Partitioning: Each fragment is assigned a different value range
Value List: For each fragment a list of attribute values is given
Hash Values: Applying a hash function to tuples, each fragment is
responsible for a given hash range

*Source - Distributed Database Systems: Fragmentation and Allocation by Katja Hose, Ralf Schenkel


Import .sql file into multiple databases

If you're running on Linux/Unix, you can do it with a bit of shell:

for DB in `mysql -u root -r --silent -p -e 'show databases;' | egrep -v "_schema$|^mysql$|^sys$"`
do
  mysql -uroot -p $DB < test.sql
done

Will obviously be easier if you temporarily set it up to be passwordless.


How to set up the SQL Server to read a database from external hard disk or a different hard disk from my PC

You will want to use the MOVE option in the restore command to tell the restore process where to put each of the files in the backup set. This is done for each logical file name specifying the new physical location.

See the details in this technet article

It also shows how to get the logical file names from the backup set.


Is this design in 3NF? Why to choose another design not in 3NF

Both designs are technically 3NF, depending on your data population.

What a lot of people who have only a cursory understanding of formal normalization theory don't understand is that redundancy in foreign keys isn't really redundancy by definition.  Transitive dependencies and partial dependencies apply to the way non-key attributes relate to key attributes.

If your composite keys happen to contain (or even consist of) foreign keys then whether your foreign key has the look of being denormalized isn't relevant.  (i.e. Design 1 isn't redundant in a way which is significant to the formal definition of normal forms.)

The practical issue comes down to what the values of your keys are.  If you have natural keys and the Part key consists of IDObject=123 and IDPart=1 - i.e. your IDPart values are natural number sequences which are only unique within the context of a given IDObject value, then you need to use design 1 (weak entities).

However, what a lot of people do for good (and less good) reasons is apply surrogate primary keys to every or nearly every table.  I'm not going to get into whether or why to use surrogate keys.  That is a holy war for another question.  However, if you happen to have table-wide unique (e.g. IDENTITY) values for IDPart, then your primary key for Part is more complex than it needs to be using design 1.  In that case, there is no need for IDObject in the PK of Part because the table already has a unique key just using IDPart.  In such a case, design 2 is sufficient and simpler, therefore better, as a rule of thumb.

In practice, I would say weak entities are much more common in logical models than in physical models, if for no other reason than that most physical models tend to use surrogate primary keys.


What is default behavior of PostgreSQL without specifying COLLATION?

A collation is an SQL schema object that maps an SQL name to operating system locales. Whatever you shall setup as a default collation settings for the database. By default implicitly that collation will choose your database.

In PostgreSQL nevertheless, the initial set of collation names is
platform-dependent.

There are no case insensitive collations, but there is the citext extension.
The standard approach to doing case-insensitive matches in PostgreSQL has been to use the lower function when comparing values, for example
SELECT * FROM tab WHERE lower(col) = LOWER(?);

This works reasonably well, but has a number of drawbacks:
It makes your SQL statements verbose, and you always have to remember to use lower on both the column and the query value.
It won't use an index, unless you create a functional index using lower.
If you declare a column as UNIQUE or PRIMARY KEY, the implicitly generated index is case-sensitive. So it's useless for case-insensitive searches, and it won't enforce uniqueness case-insensitively.
From the documentation:

The citext data type allows you to eliminate calls to lower in SQL queries, and allows a primary key to be case-insensitive. citext is locale-aware, just like text, which means that the matching of upper case and lower case characters is dependent on the rules of the database's LC_CTYPE setting. Again, this behavior is identical to the use of lower in queries. But because it's done transparently by the data type, you don't have to remember to do anything special in your queries.

For your reference: PostgreSQL Documentation & StackOverFlow Ref Here


Disaster Recovery through cloning drives in sql server 2014 enterprise

Firstly, you need to determine what the acceptable Recovery Point Objective (total data loss) and Recovery Time Objective (time taken to restore the system) is for each database on your production server.
If the amount of data loss your business is willing to accept is less than the interval that your disks are replicated to your DR site then this won't work as a strategy. If it takes longer to clone and provision the disks for your DR server than the RTO, then this solution won't work.
Download Brent Ozar's First Responder Kit, which includes a High Availability and Disaster Recovery worksheet to help you determine and design a suitable DR solution for your databases.
There are many options within SQL Server itself for providing DR capabilities that might be better suited than SAN replication (although that is always an option). Log Shipping, for example, is a simple, low-cost solution that might achieve lower RPO and RTO than hourly SAN replication and can be simpler than having to clone LUNs and attach them to the SQL Server.


What are possible causes for consistently volatile INSERT performance in MySQL tables?

The simple answer is: it is not possible to guess what is the source of contention.

The more detailed: MySQL 5.6+ can instrument a break-down of query execution time, so that you can see if performance is stalled waiting on IO, locks, etc.

The diagnostic feature is called performance_schema.  The easiest way to start using it, is to download MySQL Workbench 6.1 and chose "Performance Reports" (under Performance).


Different execution times running a query

All operating systems and all applications use a concept called "caching".

It means - when the data is first read from a slow memory device (like, a hard disk), it is saved in a fast memory device (like, RAM) for some time, to facilitate faster lookups.

The same applies to RDBMS.

First time the data blocks that build up your query results are read from disk, second time they are read from memory.

Details can be explored using OS and database tools. If you specify what RDBMS and what OS you are on, we can help you get the details. For PostgreSQL it's about EXPLAIN command.


MySQL .frm file format: how to extract column info?

This is bit late but in case anyone is still interested.....

I have been helping a student to write a program (in PHP!) to recreate the CREATE TABLE statement that is implicit in mySQL .frm files, by attempting t parse the .frm files directly. (yes we know it can be done with various tools!!).

It was very difficult to track down the structure of the file and what is available at the site reference by mpen above is incomplete and sometimes incorrect.

Eventually I was able to track down the source code for mysqlfrm.exe and from there I located the particular class file that does the .frm file parse. It is written in Python, which is very readable - even if you dot know the language.

I was able to use this file (apart from a few minor corrections) to work out the indices in the .frm file where everything is stored and how to recover the various fields.

Here is the link...

http://www.commongrounds.co.uk/Library/lib/python2.7/site-packages/mysql/utilities/common/?DA and the required file is aptly names frm_reader.py/

Let it not be said that the .frm file format is forgotten!!


I don't know what the best way necessarily is to store it -- but there's at least a better option than using a varchar(39) (or varchar(40) if you needed it signed) ; instead use a decimal(39,0).  From the mysql docs:

  Fixed-Point (Exact-Value) Types

  The DECIMAL and NUMERIC types store
  exact numeric data values. These types
  are used when it is important to
  preserve exact precision, for example
  with monetary data. In MySQL, NUMERIC
  is implemented as DECIMAL, so the
  following remarks about DECIMAL apply
  equally to NUMERIC.

  MySQL 5.1 stores DECIMAL values in
  binary format. Before MySQL 5.0.3,
  they were stored as strings. See
  Section 11.18, “Precision Math”.

  In a DECIMAL column declaration, the
  precision and scale can be (and
  usually is) specified; for example:

salary DECIMAL(5,2)

  In this example, 5 is the precision
  and 2 is the scale. The precision
  represents the number of significant
  digits that are stored for values, and
  the scale represents the number of
  digits that can be stored following
  the decimal point.

  Standard SQL requires that
  DECIMAL(5,2) be able to store any
  value with five digits and two
  decimals, so values that can be stored
  in the salary column range from
  -999.99 to 999.99.

  In standard SQL, the syntax DECIMAL(M)
  is equivalent to DECIMAL(M,0).
  Similarly, the syntax DECIMAL is
  equivalent to DECIMAL(M,0), where the
  implementation is permitted to decide
  the value of M. MySQL supports both of
  these variant forms of DECIMAL syntax.
  The default value of M is 10.

  If the scale is 0, DECIMAL values
  contain no decimal point or fractional
  part.

  The maximum number of digits for
  DECIMAL is 65, but the actual range
  for a given DECIMAL column can be
  constrained by the precision or scale
  for a given column. When such a column
  is assigned a value with more digits
  following the decimal point than are
  permitted by the specified scale, the
  value is converted to that scale. (The
  precise behavior is operating
  system-specific, but generally the
  effect is truncation to the
  permissible number of digits.)

It's stored packed, so it'll take up less space than the varchar (18 bytes, if I'm doing my math right), and I'd hope you'd be able to do math on it directly, but I've never tried with that large of a number to see what happens.


Two servers, different execution plans. Several orders of magnitude difference in run time

Comparing details from the two execution plans provided:

Slow plan was compiled on build 11.0.2100.60 - SQL Server 2012 RTM
Fast plan was compiled on build 12.0.4100.1 - SQL Server 2014 SP1
Slow plan uses the version 70 cardinality estimator
Fast plan uses the version 120 cardinality estimator
Slow plan has estimated available DOP of 2 (half the visible CPUs)
Fast plan has estimated available DOP of 4 (half the visible CPUs)
Estimated memory grant available is also different between the two
Parameter values used during compilation are identical

With so many fundamental software and hardware differences, it is not really surprising the plans are different.

Ideally, development and test environments should match production as closely as possible. They should certainly be on the same version of SQL Server (and note the 2012 installation there is still at RTM, two full Service Packs and seven further Cumulative Updates behind current.

Without a statistics-only copy of the schema involved in the query, it is hard to know what to suggest to improve the 2012 query plan as it stands. If you are comfortable using hints, you might try adding:

OPTION (LOOP JOIN, HASH JOIN, CONCAT UNION, QUERYTRACEON 8649);

at the end of the query to encourage the optimizer in the general direction of the better plan. The last part of that hint collection (setting trace flag 8649 for the query) is undocumented and not supported for production.

Those hints may or may not be successful, since the underlying issue is the inaccurate cardinality estimation provided by the 70 level cardinality estimator. Query rewrites or indexing changes might be successful as well, but it's really not practical to guess on that from here.

Related reading:

Forcing a Parallel Execution Plan
Different Plans on UAT and PROD
Multiple Plans for an "Identical" Query
Different Plans for "Identical" Servers


Percona tool kit will I think, but I used these views by Roland Bouman

I've added them below:

CREATE OR REPLACE VIEW I_S_INDEXES
AS
SELECT TABLE_SCHEMA
, TABLE_NAME
, INDEX_NAME
, INDEX_TYPE
, IF(NON_UNIQUE, 'NO', 'YES') IS_UNIQUE
, GROUP_CONCAT(
CONCAT('`',COLUMN_NAME,'`')
ORDER BY IF( INDEX_TYPE = 'BTREE' -- when BTREE then
, SEQ_IN_INDEX -- column order is important
, 0) -- else
, COLUMN_NAME -- only column content
) COLUMNS
FROM information_schema.STATISTICS
GROUP BY TABLE_SCHEMA
, TABLE_NAME
, INDEX_NAME
, INDEX_TYPE
, NON_UNIQUE
;

CREATE OR REPLACE VIEW
I_S_REDUNDANT_INDEXES
AS
SELECT l.TABLE_SCHEMA
, l.TABLE_NAME
, CASE
WHEN l.COLUMNS = r.COLUMNS
AND (l.IS_UNIQUE = r.IS_UNIQUE)
THEN GREATEST(l.INDEX_NAME, r.INDEX_NAME)
ELSE l.INDEX_NAME
END REDUNDANT_INDEX_NAME
, GROUP_CONCAT(
DISTINCT
CASE
WHEN l.COLUMNS = r.COLUMNS
AND (l.IS_UNIQUE = r.IS_UNIQUE)
THEN LEAST(l.INDEX_NAME, r.INDEX_NAME)
ELSE r.INDEX_NAME
END
) INDEX_NAME
FROM I_S_INDEXES l
INNER JOIN I_S_INDEXES r
ON l.TABLE_SCHEMA = r.TABLE_SCHEMA -- index on the same table
AND l.TABLE_NAME = r.TABLE_NAME
AND l.INDEX_NAME != r.INDEX_NAME -- but not identical
AND l.INDEX_TYPE = r.INDEX_TYPE
AND CASE
WHEN l.COLUMNS = r.COLUMNS -- we require column equality
AND (l.IS_UNIQUE = 'NO' -- redundant if not unique
OR l.IS_UNIQUE = r.IS_UNIQUE) -- or if same uniqueness as other
THEN TRUE
WHEN l.INDEX_TYPE = 'BTREE' -- when BTREE
AND INSTR(r.COLUMNS, l.COLUMNS) = 1 -- and l is a prefix
AND l.IS_UNIQUE = 'NO' -- redundant if not unique
THEN TRUE
ELSE FALSE
END
GROUP BY l.TABLE_SCHEMA
, l.TABLE_NAME
, REDUNDANT_INDEX_NAME


ASP.NET & SQL Server 2012 timeouts once a month

It might be related with leaking connections exhausting the connection pool.
If the connections are not released properly they will remain active and will not be returned to the connection pool until the next Garbage Collection executes. If a lot of connections are opened in a short time the connection pool might fill up before the GC runs, and the following connection requests will end up waiting for a free connection in the pool, eventually timing out.

https://msdn.microsoft.com/en-us/library/8xx3tyca(v=vs.110).aspx


Should I represent two distinct relationships via only one associative table?

To delete a news-category pair in schema B, you have to do either a DELETE or an UPDATE depending on whether the relevant row has id_gallery specified or not. How about inserts, are you going to just INSERT or check first for rows with nulls to UPDATE?

So, I strongly recommend option A. However, I would drop the surrogate id columns and use (id_news, id_category) and (id_news, id_gallery) as PK.


When to mark functions as PARALLEL RESTRICTED vs PARALLEL SAFE?

If a function contains an query that uses a CTE, and that function is used by a parallel worker process, the query and the CTE will only be executed in the private process context of the parallel worker. There is no shared state involved in creating or scanning that CTE.

So it is safe to mark the function PARALLEL SAFE.

The quotation from the documentation says that a CTE defined in a query that should be parallelized can only be scanned by the leader process, not by the parallel workers, precisely for the reason that the CTE is not shared between processes. This has no impact on CTEs defined in queries running in functions called by parallel workers, because such nested queries won't be parallelized anyway:

  Even when it is in general possible for parallel query plans to be generated, the planner will not generate them for a given query if any of the following are true:

  [...]

  The query is running inside of another query that is already parallel. For example, if a function called by a parallel query issues an SQL query itself, that query will never use a parallel plan.


TSQL query for viewing start/end of all SQL Agent jobs yesterday

You just need to filter where the step_id = 0 which is the job outcome and has the total run duration.

SELECT TOP ( 1000 )
        h.run_date ,
        MIN(h.run_time) ,
        j.name ,
        j.description ,
        MAX(h.run_duration) run_duration
FROM    msdb..sysjobhistory h
        JOIN msdb..sysjobs j ON j.job_id = h.job_id
WHERE   step_id = 0
GROUP BY h.run_date ,
        j.name ,
        j.description ,
        h.instance_id ,
        j.job_id
HAVING  MAX(h.run_duration) > 1000  -- << -- 10 minutes encoded in HHMMSS (digits)
ORDER BY h.run_date DESC ,
        2 DESC


How to copy all firewall rules and apply them on a new server?

I am delighted to say that I have used the link provided by Scott Hodgin in the comments,
and it worked fine, although the order is not the same, and the original server was involved in clustering and the new one is not.....

I recommend this script, for those who need to deal with database server installations, and consequently firewalls.

Powershell scripts to export and import firewall rules

This is a partial picture of the firewall on the original server:

This is a partial picture of the firewall on the new server:


How to remove from a table using left join to get the id of the same table by using alias

You can use JOIN in the FROM part instead of WHERE. This is the multi-table delete syntax, details in  mysql docs: DELETESyntax.

Example:

DELETE fa.*
FROM cce_sa_fa AS fa
  JOIN examinations AS ex
    ON ex.id = fa.exam
WHERE fa.exam IN (1,2,...)
  AND ex.name <> fa.name ;

Another way would be to convert the IN subquery to EXISTS:

DELETE
FROM cce_sa_fa AS fa
WHERE EXISTS
      ( SELECT *
        FROM examinations AS ex
        WHERE ex.id = fa.exam
          AND ex.name <> fa.name
      )
WHERE fa.exam IN (1,2,...) ;


A global temporary table is global, because its definition is persisted an other sessions can use the table as well, given that they have the necessary privilege on it. The content of the table is of course specific to the session using it.

Creating Global Temporary Tables

  Global temporary tables are permanent database objects that are stored
  on disk and visible to all sessions connected to the database.

Yes, you need to grant the privileges on it for other users or roles, if you want this to be accessible to them.

Oracle did not have other any other type of temporary table, up until 18c, which introduced Private Temporary Tables, which are similar to temporary tables in SQL Server:

Creating Private Temporary Tables

  Private temporary tables are temporary database objects that are
  dropped at the end of a transaction or session. Private temporary
  tables are stored in memory and each one is visible only to the
  session that created it.


How to create a cascading query finding parent/child rows from table

with table1 as
(
  select 1 as id, 'COUNTRY' as lvl, 'UK' as value, null as parentid from dual union all
  select 2 as id, 'COUNTRY' as lvl, 'FRA' as value, null as parentid from dual union all
  select 3 as id, 'CITY' as lvl, 'LONDON' as value, 1 as parentid from dual union all
  select 4 as id, 'CITY' as lvl, 'PARIS' as value, 2 as parentid from dual union all
  select 5 as id, 'RIVER' as lvl, 'SEINE' as value, 4 as parentid from dual union all
  select 6 as id, 'RIVER' as lvl, 'THAMES' as value, 3 as parentid from dual
)
select value from table1
connect by id = prior parentid
start with value = 'THAMES';

VALUE
------
THAMES
LONDON
UK


I have just started as a DBA

Solving problems using Backup and Restore is the bread-and-butter of a DBA.

  move a 4TB database from a physical server to a virtual server with little down time.

Define "little".

The safest, simplest thing is to leave the database in Simple recovery, and use Full/Differential backups to move the data.

You can also switch to Full recovery and use Log backups instead of Differential backups.

In both cases you would

1) Take a Full backup

2) Restore the Full backup on the new server WITH NORECOVERY

3) Begin Downtime

4) Take a final Log Backup or Differential Backup

5) Restore the all the Log Backups or the Differential Backup WITH RECOVERY

6) Set the database OFFLINE on the old server

7) End Downtime

You can perform steps 1, 2, 4, and 5 as many times as you want in testing, and to measure the required downtime.


Builtin\Administrators doesn't permit access

As commented, UAC is blocking your connection to SQL Server. Run it as administrator and it should be fine.


Is there really no way in SQL to update all fields in a table without explicitly typing field names

Unless I've missed something here - and I may have - I think this is fairly straightforward.

Essentially the only problem here is you don't want to type out all the column names- that can easily be automated with a bit of dynamic SQL using the sys tables as hinted at by Randolph West.

Something like

select @Sql = COALESCE(@Sql + ', ', '') + COLUMN_NAME
from
    INFORMATION_SCHEMA.COLUMNS
where
    TABLE_NAME='Table'

Which will list out all the columns separated with commas, as if you typed them ready for insert/update (or select).

This post: Selecting column name dynamically in an insert query is a stack overflow question that goes into the detail.

EDIT:

Just thought I'd add for clarity and to consolidate some comments:-

Anything can be added to the @Sql 'building' bit, for example if you want the table name put before the column name, the select part would be:

select @Sql = COALESCE(@Sql + ', ', '') + TABLE_NAME + '.' + COLUMN_NAME

You can execute that variable using

sp_executesql @Sql

Or just

SELECT @Sql

to give it to you in a query execution window, which you can copy & paste and run (which I would usually do - you can double check the code first and edit if needed)


Best Practices When Inserting Data (opinion question)

YES

check data at the client

Check the data on the client side (eg in JavaScript) for the stupid simple checks. eg NOT NULL, attribute < someValue, strings are in the correct format (regex), etc.  Feel free to make some REST calls for the validation.

Also, use good GUI designs.  The use of Drop Downs help limit what PK values the end users can use.

check data at the middle tier

Check the data at the middle tier because:  Not everyone uses web browsers.  Not everyone will send "clean data".
https://xkcd.com/327/

enforce data rules inside the DB

On of the primary purpose of a database is to store valid data.

Always enforce constraints within the database.  There could be more than one application accessing the database.  You want those rules to apply to all applications.

For an application assertion, use a function/procedure.


Strange query optimization issue - logically the same, different performance

You really need to provide SHOW CREATE TABLE.  A guess is that autoid is a different type in the two tables.  A strong clue is the 8 (BIGINT?) and the 12 (some kind of VARCHAR or DECIMAL?).

Redundant:  Donor.autoid = Visit.autoid is in both the ON (where it belongs) and the WHERE (where it is harmlessly redundant).

When JOINing, please qualify all columns -- which table are FName, VisitDate, etc, in??


We can find with subquery each conversation_id for user_id from table conversations_users and use left join for get one reply for each conversation_id from conversation_reply table (see sqlfiddle example and code below)

select cp1.* from conversations_reply cp1
left join conversations_reply cp2
  on (cp1.conversation_id = cp2.conversation_id AND cp1.id < cp2.id)
where cp2.id is NULL and
  cp1.conversation_id in (
    select conversation_id from conversations_users where user_id = 4
  )
order by cp1.id desc limit 10;

Output will be

+---+-------+--------+---------------+
|id |user_id|body    |conversation_id|
+---+-------+--------+---------------+
|9  |      4| message|              4|
|8  |      4| message|              3|
|7  |      1| message|              1|
|6  |      4| message|              2|
+---+-------+--------+---------------+


Is it a better idea (performance is key) to store in a normalized table and retrieve by JOIN or in a single nvarchar column and retrieve using LIKE?

Definitely you want to use option 2. Not only will your queries be faster (= is always faster than like) but you can also index on that keyword field for even faster queries AND your storage space will be significantly reduced since you are not storing the long keyword strings for each website.


Query performance and join hints, plan cost, and duration

Another option to optimizer hints if you cannot even touch the query is to use Plan Guides.  However, since your query statement is dynamic with many unparameterized tokens, I doubt it's possible.

Since you know your data, I think you're better off just staying the path of query hints.  Statistics have been known to be lies, lies and damn lies.  It can only provide an overall shape of the data, but for specific conditions (e.g. fund_src_name_id values, state_id values, combination of the two etc) it can just produce the wrong plan.


I only have backup of a TDE encrypted SQL Server database--can it be restored it to another server?

The primary purpose of TDE is that someone can't just take the backup or database files and start up the database on another server.  Accordingly, the answer is no, you cannot restore it without restoring the certificate that was used to encrypt it.


An owner is a database principal that has ownership of a particular object, giving that principal full rights to the object(including ALTER, SELECT, INSERT, UPDATE, and DELETE).  This principal can either be a database user or schema, depending on the context in which the object was created and the permissions of the principal that created the object.  In the case of no explicit object ownership, the object is owned by the related schema owner.

There is also the db_owner role which exists in each database.  Members of this role are considered to have ownership of the database and all objects within that database.  Essentially, members of the db_owner role have "sysadmin/sa" rights in that specific database.

'dbo' is a default database user that is made a member of the db_owner role.  Additionally, the 'dbo' schema is also created and used as default schema to create objects within.  This schema is owned by the 'dbo' user.

To view object ownership, you can use the following query against the DMVs:

select
    SCHEMA_NAME(s.schema_id)+'.'+OBJECT_name(o.object_id) [ObjectName]
    ,u.name [ObjectOwner]
    ,so.name [SchemaOwner]
from
    sys.objects o
    left join sys.schemas s on (o.schema_id = s.schema_id)
    left join sys.database_principals so on (s.principal_id = so.principal_id)
    left join sys.database_principals u on (o.principal_id = u.principal_id)

For more detail, you can read Aaron's link or this article from Kathi Kellenberger.


shoud we normalize user profile table with fields like interests,musics,videos,sports, etc?

The solution I would choose depends on a few things.  If the number of these columns is relatively low (and not subject of frequent changes) then I would go with the solution you suggested, ie. one table for each attribute and foreign keys on these.  This way you have to define a new table and add a column for every new attribute and modify your queries accordingly.

If this is not your case than EAV suggested by @Zachariha should be considered, but it's worth reading this answer before.  It is very easy to extend (just add a new row to a table) but can go very difficult to maintain - in my opinion mostly because it's hard to follow that who plays in which team.

The scenario you see in your current schema can be the result of either not normalizing data or denormalizing later in order to speed up queries.  Normally, I keep heavily denormalized tables separate from base tables to keep my data safe from violations of referential integrity.


I would just make a synonym in db2 (assuming the table exists in db1).  This will let you point directly to the source table without obfuscating behind a view, and will prevent you from needing to maintain a view if the schema of the table changes:

CREATE SYNONYM dbo.CommonTableName FOR db1.dbo.CommonTableName


How to create an index on an integer json property in postgres

Try this instead:

CREATE INDEX user_reputation_idx ON users(cast("user"->>'reputation' AS int));

The Postgres syntax shortcut :: for casts is not allowed without additional parentheses in an index definition (see @bma's comment). It works with the standard SQL function, though: cast(expression AS type) This is not related to the json type per se.

Either way, you can still use the syntax shortcut expression::type in expressions that utilize the index.


It's strange how many "problems" CXPACKET is causing :)

You can find my answer to similar question at this link: https://dba.stackexchange.com/a/141399/28611

But I will re-post my answer here as well

The CXPACKET was always the confusing wait type for younger DBAs and some predictably wrong reactions are generally expected. There are multiple aspects of the CXPACKET wait type and I've tried in Troubleshooting the CXPACKET wait type in SQL Server article to bring on the table most of the reasons for high CXPACKET but also to explain the background of the CXPACKET, as the proper understanding of parallelism in SQL Server is the key for understanding

So for those who don't want to get in details, I will post the summary of the article here (but I would definitely suggest reading of the article for complete info about CXPACKET wait type):

  Do not set MAXDOP to 1, as this is never the solution
  Investigate the query and CXPACKET history to understand and    determine whether it is something that occurred just once or twice,
  as it could be just the exception in the system that is normally
  working correctly
  Check the indexes and statistics on tables used by the query and make    sure they are up to date
  Check the Cost Threshold for Parallelism (CTFP) and make sure that    the value used is appropriate for your system
  Check whether the CXPACKET is accompanied with a LATCH_XX (possibly    with PAGEIOLATCH_XX or SOS_SCHEDULER_YIELD as well). If this is the
  case than the MAXDOP value should be lowered to fit your hardware
  Check whether the CXPACKET is accompanied with a LCK_M_XX (usually    accompanied with IO_COMPLETION and ASYNC_IO_COMPLETION). If this is
  the case, then parallelism is not the bottleneck. Troubleshoot those
  wait stats to find the root cause of the problem and solution


If you mean that you are trying to restore a database backup from a more recent version, such as SQL Server 2008 R2, to a previous version such as SQL Server 2008, then you cannot do such a restore.

You could restore a SQL Server 2008 database to a SQL Server 2008 R2, since the backup can be promoted to the new level.

If you need to move the data to a lower level server it will likely require scripting the database definitions to create a database on the earlier version of SQL Server.  Then the data will need to be copied to that server.

There are a number of posts on doing this, such as:

Restoring a backup to an older version of SQL Server

http://www.mssqltips.com/sqlservertip/2810/how-to-migrate-a-sql-server-database-to-a-lower-version/

EDIT:  Although there is no way just to attach the files and make it work, the good news is that the changes between SQL Server 2008 and 2008 R2 are few.  Therefore, you have a good chance of migrating code and data to the SQL Server 2008 server using techniques such as posted above without any major problems. (Other than the time and nuisance.)


The equivalent backup tool for MySQL is Percona XtraBackup.
This does not do a dump like mysqldump, it makes a physical copy of the tablespace files without blocking clients using the database. The documentation is pretty good quality.


Track all modifications to a PostgreSQL table

Answer compiled from comments

You would put triggers for any DML event on the table that write information about the event to a log table. If you want to track DDL too, have a look at event triggers - sticky bit.

Some example implementations given by a-horse-with-no-name:

Tracking changes in PostgreSQL by Hans-Jürgen Schönig
Impossibly Lean Audit System for Postgres with hstore
Copying Pavel Stehule’s simple history table but with the jsonb type by Glyn Astill
Audit trigger in the PostgreSQL Wiki

Even if they are several years old, the basic idea hasn't changed and all of them are valid choices. The source for the fourth alternative has been updated recently: https://github.com/2ndQuadrant/audit-trigger


Dynamic Data Masking performance overhead

No, there is no overhead involved (not heard of any yet). I have used it in my Azure SQL Database and works like a charm.

Data Masking is a quick solution for data security as you can control the users who can view the data, but on a broader aspect this cannot be the sole security solution.

Please read below links for better understanding:

Dynamic Data Masking (Microsoft documentation)
Encrypting SQL Server: Dynamic Data Masking by Robert Sheldon


How do I do a complex GROUP BY in MySQL?

What about doing a little math against your ID column to dynamically generate the group?

SELECT grp, FLOOR(id/10) AS id_grp
FROM animals
GROUP BY grp, id_grp

This would give you groups of 10 based on the ID of the record.  I used your animals table above to generate the data below.

Sample data

 INSERT INTO animals VALUES
 ('mammal',10,'dog'),('mammal',11,'dog'),('mammal',12,'dog'),
 ('mammal',21,'cat'),('mammal',22,'cat'),('mammal',23,'cat'),
 ('mammal',24,'cat'),('mammal',25,'cat'),('mammal',26,'cat'),
 ('bird',30,'penguin'),('bird',31,'penguin'),('bird',32,'penguin'),
 ('bird',33,'penguin'),('fish',44,'lax'),('fish',45,'lax'),
 ('fish',46,'lax'),('fish',47,'lax'),('fish',48,'lax'),
 ('mammal',31,'whale'),*'fish',51,'lax'),('fish',52,'lax'),
 ('fish',53,'lax'),('fish',54,'lax'),('bird',10,'ostrich');

Query Output

 +--------+--------+
 | grp    | id_grp |
 +--------+--------+
 | fish   |      4 |
 | fish   |      5 |
 | mammal |      1 |
 | mammal |      2 |
 | mammal |      3 |
 | bird   |      1 |
 | bird   |      3 |
 +--------+--------+
 7 rows in set (0.00 sec)


Technically yes, you can change the Instance's default Collation without reinstalling by "rebuilding the system databases" via the SETUP program. This is documented in the following two places on MSDN:

Rebuild System Databases
Set or Change the Server Collation

However, as can be inferred from the instructions on those two pages, this is not exactly a simple / low-risk process. And, this only changes the 4 system databases, not any user databases.

There is also the undocumented -q option for sqlservr.exe as linked to in @irimias's answer (please see Changing the Collation of the Instance, the Databases, and All Columns in All User Databases: What Could Possibly Go Wrong? for a detailed description of the behavior of this option, as well as the potential scope of impact). This option changes all databases (system and user), but is undocumented, so not guaranteed to work 100% (but it might).

Now, while you did mention the default Collation for new databases, one should still keep in mind that it is easy enough to specify the desired Collation via the COLLATE clause as follows:

CREATE DATABASE [NewDatabaseName] COLLATE Hebrew_100_CI_AS;

Still, that is not 100% the same as the Instance being the same Collation as the Database since some columns of various DMVs, plus tempdbs and msdbs default Collation will still be the same as the Instance default, which increases the chances of getting Collation-mismatch errors. But, those can still be worked-around using the COLLATE clause on any predicates or JOIN conditions on strings fields that get those mismatch errors.


Is there a way to group often used tables and queries in SSMS?

I have my db objects in source control with one file per table, view, SP etc. When working on a project I create a working folder and paste into it a shortcut to the relevant objects. Note this is a shortcut, not a copy, so the real files remain in the place source control expects to see them. I can drag into SSMS from the shortcut.


Most queries become slower with partitioning.
The exceptions I know are:

a query that needs a sequential scan, but contains the partitioning key in the WHERE clause
a query that joins two partitioned tables, and the join condition matches the partition key for both
a query that groups and aggregates by the partitioning key

So you should rather consider how data expire. Partition your tables so that you can get rid of old, obsolete data by dropping partitions.


Why the instance name of SQL Server Express is changing?

This app isn't using the regular SQL Server Express Edition. Express Edition runs as a service such that you'd see it in the Services control panel (on Windows), and there would be a stable instance name, as you're expecting.

This service you're connecting to uses the (confusingly named) SQL Server Express LocalDB. This is often referred to as just "LocalDB" to reduce the confusion of the name overlap. LocalDB runs in process--that is, it isn't a service, but is started up by the app that uses is.

LocalDB supports a number of configurations, depending on how the application is intended to be accessed. Check out the section in the docs titled "Automatic and Named Instances" as well as the section titled "Shared Instances of LocalDB".


Automatically and robustly restarting a Galera cluster

I would say the best practice would be to ensure that one node is always still running, when the others have stopped normally. (systemctl stop mysql/mariadb)
Then restart one of the previously stopped nodes, and when that restarted node reports in logfiles that it connected to the running old node, and that it's ready to serve connections, you may restart the old one still running.
If all nodes have been stopped, you need to figure out manually which is the newest node (by using (ubuntu):galera_recovery, note the highest change no.)
Maybe even have edit the safe_to_bootstrap line in the grastate.dat file.
That node then needs to restart the cluster (create the new unique ID for the others to connect to) (by using (ubuntu): galera_new_cluster).
The remaining nodes simply start db with 'systemctl start mariadb'.
Sounds as if there's a reason they didnt yet built that in to systemd ;) ?


Restore DB from SQL Server 2008 R2 to 2014 Express

Yes, you can, as long as the database is < 10 GB and doesn't use any features not supported by Express (you didn't mention what edition was being used in 2008 R2).

Stop using the broken point-and-click UI, and instead use a proper RESTORE DATABASE command:

RESTORE DATABASE DBFrom2008 FROM DISK = 'C:\backups\2008backup.bak'
  WITH REPLACE, RECOVERY,
  MOVE 'data_file_name' TO 'C:\...\database_name.mdf',
  MOVE 'log_file_name'  TO 'C:\...\database_name.ldf';

If you get errors from that (after replacing all of the obvious bits that are specific to your database and system), post those.


For me, this means that the daemon has crashed and was restarted, but the lockfile that mysqld uses to prevent multiple daemons from accessing the same database is still there...

I fix it with

service mysqld stop
service mysqld start

Not sure if you can just to a restart instead of a stop then start, and I haven't figured out the cause yet for the crash either.


Using the value of a JSON object that is stored in a PostgreSQL JSON array

You can use json_array_elements to turn a JSON array into a rowset:

SELECT  "ID" as JobId
,       t->>'id' as TaskId
FROM    Table1 j
CROSS JOIN
        json_array_elements("Details"->'tasks') t
WHERE   cast(t->>'due_on' as date) = '2015-06-08';

The cast(... as date) works around the 2015-06-8 date which misses a leading 0 before the day.  If your format is always YYYYMMDD, you can omit the cast.


Scalability of concurrent log restore for different databases on SQL Server

For 1 & 2 - You need to take into account VLFs as well. Too many VLFs will slow down the restore of Transaction log.

Also, all the restores are logged in msdb--> restorefile, restorefilegroup and
restorehistory tables, so many concurrent sessions will have to write to the same table and if those tables are not maintained (purged) properly, there will be contention/blocking.

As a side note, I have seen blocking especially when you logship 50+ databases with the same restore frequency.

For 3, I will just refer to the most authoritative wait stats repository.


Which items are table entities and which are fields?

It sounds like the main issue here is that the attributes of certain entities (such as characters) can change over time, and you want to be able to track all the changes.

One way to do this might be to think of it as a data versioning problem.

One possible schema might look like this:

book
----
  ID
  name
  sequence_number (which book it is in the series)
  start_date (use this to track the dates that book covers)
  end_date (use this to track the dates that book covers)

character_base
--------------
  id (PK)
  first_book (FK to book.id)

character
--------
  id
  base_id (FK to character_base.id)
  version_number (in your case, this is probably optional)
  firstname
  lastname
  nickname
  hometown
  notes
  birthdate
  rank (fk to rank reference table: rank.ID)
  start_date (the begining of the time range for which this version is valid)
  end_date (the end of the time range for which this version is valid)

rank
----
  ID
  name

Now, whenever you create a new "version" of your character, you need to create a new record in character, but make sure you point the base_id to the same value as the previous value. Also remember to always populate start_date and end_date and ensure there are no gaps in the range.

To find out which version of a character is present in which books, you'd have to join character to book with a condition such that the character's version start and end date falls within a book's start and end date.

You can apply the versioning pattern for character to other entities that can be versioned, such as platoon or company if their attributes (such as name) need to be tracked over time.


Can't run query using "localhost\sqlexpress"

You can only reference servers that are listed under Server Objects -> Linked Servers as well as the local server via what you get back from @@SERVERNAME. Four part naming does not trigger a NETBIOS / DNS lookup. If you are referencing the local machine anyway, why not just use three part naming?


Postgres inet array contains a single ip

While McNets's solutions would also work, it's possibly the easiest to use ANY with an operator, in this case equality:

SELECT '10.10.10.10' = ANY ('{10.10.10.10, 10.10.10.20}'::inet[]);

The background is that an inet array is no different from any other array type in this regard - ANY will work with any boolean-returning operator that is defined on the base type.

The above applies to equality of single IP addresses.  If you need to match a single IP against a range, you'll need the <<= operator instead of =:

SELECT '10.10.10.10'::inet <<= ANY ('{10.10.10.10, 10.10.10.20}'::inet[]);

 ?column?
──────────
 t

SELECT '10.10.10.10'::inet <<= ANY ('{10.10.10.0/28, 10.10.10.20}'::inet[]);

 ?column?
──────────
 t

In your case, the solution would look like

WHERE '192.168.1.5' <<= ANY ("ipAddresses")

The performance of this can be poor for the lack of really efficient indexing.  To address this, see the suggestion in McNets's answer.


Unused Indexes - Consider Primary Key Constraint Supporting Indexes?

Primary Key and Unique constraints are enforced by a supporting index for performance and to enforce uniqueness. Just because the index is not used for searching does not mean it isn't being used to guarantee uniqueness of the key values.

Whether or not a table has a primary key constraint or any unique constraints is not a performance decision - it is a data integrity decision. The primary key gets a primary key constraint and the candidate keys get unique constraints.

Whether or not the primary key index is clustered or nonclustered, on the other hand, is a performance question. Doing a Binoogle search on that will find many hits, including https://stackoverflow.com/questions/2206541/index-view-how-to-choose-the-clustered-index.


In this case, simply sorting by ItemNameCode doesn't give you the desired sort. Using ORDER BY ItemNameCode would sort the PT15 values like this:

PT15-12-42
PT15-12-572-575
PT15-3-11

But, you expect them sorted like this:

PT15-3-11
PT15-12-42
PT15-12-572-575

Based on your expected results, I would describe the desired sort behavior as "The string is made up of segments separated by dashes. The first segment is alphanumeric, and each subsequent segment is numeric. Sorting should be done by the first segment, then by the second segment, then by the third segment, etc"

To accomplish this, you'll need to essentially break the ItemNameCode into separate calculated columns, and cast the numeric segments to integer before sorting.

This method of splitting the string apart into separate columns is pretty ugly, but works:

SELECT ItemNameCode, c1.seg1, c2.seg2, c3.seg3, c4.seg4
FROM dbo.AlphaNumeric x
OUTER APPLY (SELECT CASE WHEN CHARINDEX('-',x.ItemNameCode,1) = 0 THEN x.ItemNameCode
                        ELSE SUBSTRING(x.ItemNameCode,1,CHARINDEX('-',x.ItemNameCode,1)-1)
                    END AS seg1,
                    CHARINDEX('-',x.ItemNameCode,1)+1 AS pos1) c1
OUTER APPLY (SELECT CASE WHEN c1.pos1 = 1 THEN NULL
                        WHEN CHARINDEX('-',x.ItemNameCode,c1.pos1) = 0 THEN SUBSTRING(x.ItemNameCode,c1.pos1,8000)
                        ELSE SUBSTRING(x.ItemNameCode,c1.pos1,CHARINDEX('-',x.ItemNameCode,c1.pos1)-(c1.pos1))
                    END AS seg2,
                    CASE WHEN c1.pos1 = 1 THEN 1
                        ELSE CHARINDEX('-',x.ItemNameCode,c1.pos1)+1
                    END AS pos2) c2
OUTER APPLY (SELECT CASE WHEN c2.pos2 = 1 THEN NULL
                        WHEN CHARINDEX('-',x.ItemNameCode,c2.pos2) = 0 THEN SUBSTRING(x.ItemNameCode,c2.pos2,8000)
                        ELSE SUBSTRING(x.ItemNameCode,c2.pos2,CHARINDEX('-',x.ItemNameCode,c2.pos2)-(c2.pos2))
                    END AS seg3,
                    CASE WHEN c2.pos2 = 1 THEN 1
                        ELSE CHARINDEX('-',x.ItemNameCode,c2.pos2)+1
                    END AS pos3) c3
OUTER APPLY (SELECT CASE WHEN c3.pos3 = 1 THEN NULL
                        WHEN CHARINDEX('-',x.ItemNameCode,c3.pos3) = 0 THEN SUBSTRING(x.ItemNameCode,c3.pos3,8000)
                        ELSE SUBSTRING(x.ItemNameCode,c3.pos3,CHARINDEX('-',x.ItemNameCode,c3.pos3)-(c3.pos3))
                    END AS seg4,
                    CASE WHEN c3.pos3 = 1 THEN 1
                        ELSE CHARINDEX('-',x.ItemNameCode,c3.pos3)+1
                    END AS pos4) c4
ORDER BY c1.seg1, CAST(c2.seg2 AS INT), CAST(c3.seg3 AS INT), CAST(c4.seg4 AS INT)

If the 2nd, 3rd, 4th, etc values are not strictly numeric, and you also needed alphanumeric sorting, you could try using this alternate ORDER BY clause on the same query:

ORDER BY c1.seg1,
        RIGHT('00000' + COALESCE(c2.seg2,''),5),
        RIGHT('00000' + COALESCE(c3.seg3,''),5),
        RIGHT('00000' + COALESCE(c4.seg4,''),5)


Can Oracle be installed on Windows without being an administrator?

No, but there are workarounds

One workaround is to run a virtual machine that has Oracle and only Oracle installed. this has the significant benefit of insuring that the many kernel tweaks required of oracle are constrained within one instance.

Consider trying deployment within: VMWare Thinapp, VMWare Player (maybe), and I believe VirtualBox offers a user-land install, based on the documentation here.


Using an IF statement for all rows in a query

Your description looks messy. Expression from your SELECT statement will be evaluated for each row in received result set. I didn't understand why do you need to join boxscores table. Actually your original query should return more than one row. If you want to find the number of yellowcards for team_id = 2 you can use such query:

SELECT SUM(IF(e.hometeam_id = 2, x.homebenchyellowcards, x.awaybenchyellowcards))
FROM games AS e
JOIN miscstats AS x ON e.miscstats_id = x.miscstats_id
WHERE e.hometeam_id = 2 OR e.awayteam_id = 2;


Mysql: Any way to properly index 3 ENUM columns with the same options ? (A OR B OR C)

Because you need to use OR logic in your WHERE predicate, there's not a way to create one index that covers every case unfortunately.
Fortunately you're basically on the money with a virtual field or rather you should create a generated column, and then you can create an index on that single column so your queries are performant.


Unable to turn on compression using innodb

Error Code: 1031. Table storage engine for 'table_example' doesn't
  have this option.

I have gone through your error it's based on ROW_FORMAT.

As per MySQL documentation here If you omit the ENGINE option, the default storage engine is used. The default engine is InnoDB as of MySQL 5.5.5 (MyISAM before 5.5.5). You can specify the default engine by using the --default-storage-engine server startup option, or by setting the default-storage-engine option in the my.cnf (For Linux environment) or my.ini (for windows environment) configuration file.

As MySQL documented here The default row format for InnoDB tables is defined by innodb_default_row_format variable, which has a default value of DYNAMIC. The default row format is used when the ROW_FORMAT table option is not defined explicitly or when ROW_FORMAT=DEFAULT is specified.

The COMPRESSED row format offers the same storage characteristics and capabilities as the DYNAMIC row format but adds support for table and index data compression. The COMPRESSED row format supports index key prefixes up to 3072 bytes. This feature is controlled by the innodb_large_prefix variable, which is enabled by default.

  Note: The compact family of row formats also optimizes CHAR column storage when using a variable-length character set such as utf8mb3
  or utf8mb4.

For further your ref here , here


How can I convert from Double Precision to Bigint with PostgreSQL?

There are two ways to typecast in Postgres:

You either do it the SQL standard way:

select cast(3.141593 as bigint);

or you could use the Postgres-specific cast operator: ::

select (3.141593 :: bigint);

You might also want to consider the various rounding functions.


Is it possible to recover deleted rows from ibdata1?

Probably not.

  My purpose is to learn how to recover tables and data from ibdata1 file.

You should not be trying to do so.

That file is in a proprietary format might could, potentially, change between DBMS versions.

In order to Restore anything about a database, you should be working with the Backup tools relevant to your DBMS.  Trying to glean anything from the shattered remains of a broken database file is a Fools Errand.


SQL Server Script to replicate recent inserted data to subscriber and to remove same data from publisher

You can use a Temporal Table at the subscriber for this, although you need to use some custom initialization scripts to preserve the history table at the subscriber.
Then insert at the publisher and delete whenever you want.  Both the insert and delete will be replicated to the subscriber, and the history table will have the data after it's deleted from the main subscriber table.


delete all customers with email ending with example.com with specific DATE period

Something like this:

delete customers
where email like '%example.com'
and date_created between '2018-09-15' AND '2018-09-28';

But first, run a select to see if it will delete the correct rows:

select * from customers
where email like '%example.com'
and date_created between '2018-09-15' AND '2018-09-28';


Why do we need to use proxies for syadmin job steps?

Proxies allow non-sysadmins to manage and maintain SQL jobs

I had similar thoughts for the longest time. BUT we did finally find a valid business use for them. The short answer is, proxies allow you to delegate SQL Agent jobs to non-sysadmins so they can manage, change and/or execute those jobs using various hooks into powerful SQL features like SSIS, SSAS, command-line, etc.

For example, we are increasingly getting developers who are implementing data marts and ETL SQL jobs using SSIS and/or Analysis Services. But, as you might imagine, their coding prowess isn't always up to par. Our DBAs used to spend hours trouble-shooting the poor coding and ETL because they had the sysadmin rights to the SQL Agent jobs and the developers did not. And those SQL Agent ETL jobs can take forever to run. And if it breaks, you're running it again and again at the behest of the developers who can't do that.

Proxies change all that. With proxies enabled in SQL we can give the developers a credential that maps a the proxy to allow them to manage, monitor, and maintain those ETL/SSIS jobs. No more DBA interaction required.

Because their jobs are assigned with their proxy/credential account, they (the developers or non-sysadmins) can review those jobs, re-run them, change them etc.

For example, we associated their domain\user with a SQL Server Credential/Proxy as shown in Figure A and B. Figure B is the actual AD password of the user so you may need to Skype and share your screen so they can type in their password:

Figure A: Create a credential

Figure B: Associate the credential with an Active Directory non-sysadmin

Then, we created/assigned the credential to the proxy to the SQL job functions we wanted to delegate as shown in Figure C. We named our credential identically to the proxy--so don't be confused there.

Figure C: Associate the proxy with the credential for the job types you'd like to delegate.

Finally, we associated the proxy with the particular job. as shown in Figure D. Now, those developers have full control over those specific jobs and SQL Agent features within those jobs.

Figure D: Add/run the proxy to the jobs you'd like to delegate.

Note: If your organization has a mandatory AD password change every few months, you will have to share your screen and allow the user to change their password for their associated SQL credential.


Maximum number of TEXT/BLOB columns per table/performance of table with many of them?

If you lump all the column_## columns into a JSON field, you can have a limit of 4GB.

The question of how much gets complicated.  InnoDB has 4 ROW_FORMATs:  REDUNDANT, COMPACT, DYNAMIC, COMPRESSED.  I think you could have 400 columns of VARCHAR(255) or TEXT (or ...) in ROW_FORMAT=DYNAMIC.  That format uses a 20-byte pointer in the record to where the actual text is stored.

If you changed the block size from the default 16KB to 32KB, you could get about 800 columns.  Going to 64KB would hit the limit of 1017 columns (in 5.6.9).  Changing the block size is non trivial, so I don't recommend it.

More Limits.  (Hmmm... maybe I should add your question there.)

These two statements do go together:  "performance of this would be horrendous" and "I do need to filter against the content of the columns".  This is because DYNAMIC has an extra disk fetch for each column you need to 'filter' on.  On the other hand, in some situations (short text, not too many columns, etc), the entire text of some of the columns may be stored inline.

COMPACT might actually be better.  This format tries to keep the text in the record.  But if the record would become bigger than about 8K, it peels off the longer cells for off-block storage.  It will put the first 768 bytes of record inline.  From that, you are limited to about 10 columns.  (Try 12 columns, it will spit at you.)

I believe you will need innodb_file_per_table=1 for DYNAMIC.

How will you be 'filtering'?


You first need a list of all possible dates in that range. This can be done using the generate_series() function in Postgres:

select dt::date dt
from generate_series(date '2015-01-01', date '2015-04-01', interval '1' day) dt

Using this you can create a list of all possible attendances:

with all_dates as (
  select dt::date dt
  from generate_series(date '2015-01-01', date '2015-04-01', interval '1' day) dt
)
select s.*, ad.dt
from students s
  cross join all_dates ad;

The above uses a common table expression to make the select a bit more readable (at least in my eyes). It can be seen as a "temporary view" that only exists for the runtime of the query. If you want, you can ignore that and just assume that there is a table called all_dates that contains all dates in the range you want.

The result of this can the be used to find those rows where not attendance row exists for a student on that date:

with all_dates as (
  select dt::date dt
  from generate_series(date '2015-01-01', date '2015-04-01', interval '1' day) dt
)
select s.*, ad.dt
from students s
  cross join all_dates ad
where not exists (select 1
                  from attendance a
                  where a.matric = s.matric
                  and a.a_date = ad.dt);

The not exists subquery can also be re-written as a left join, which might be faster:

with all_dates as (
  select dt::date dt
  from generate_series(date '2015-01-01', date '2015-04-01', interval '1' day) dt
)
select s.*, ad.dt
from students s
  cross join all_dates ad
  left join attendance a on a.matric = s.matric and a.a_date = ad.dt
where a.a_date is null;

A third option is to "subtract" the list of attendances from the list of possible attendances. Sets in SQL can be subtracted using the except operator:

with all_dates as (
  select dt::date dt
  from generate_series(date '2015-01-01', date '2015-04-01', interval '1' day) dt
)
select s.*, ad.dt
from students s
  cross join all_dates ad

except

select s.*, a.a_date
from student s
  join attendance a on a.matric = s.matric;


Make Postgres database temporarily read-only (for performing volume snapshots)

After culling answers elsewhere on the internet, I devised a solution. The other answers were in and of themselves, incomplete. So I am presenting an answer here in hopes it will benefit others.

The Strategy

Disable connections to the database (not the cluster).
Set the database's default_transaction_read_only setting to true.
Terminate the existing connections to that database.
Re-enable (read-only) connections.

Once that is done, you would (in my solution):

Perform the CHECKPOINT (I think this is the safest, but a pg_xlog_switch() would be appropriate for very high-load servers)
Take the volume snapshot
Reverse the previous steps. (But this is tricky!)

Pitfalls

Terminating connections while they are mid-transaction is probably a bad idea. Better to kill idle connections, wait for a few seconds, then kill idle ones, wait a few more, repeat until they are all gone.
At some point, you'll have to kill open/hung queries or abort the backup.
At the start of a transaction of session, Postgresql takes a kind of a snapshot of the process table. You have to reset this snapshot every time you go to check if unwanted processes are still there. See pg_stat_clear_snapshot()
Restoring the read-write state is not so simple. If read-only connections now exist, you must terminate them in order for the new read-write status to take effect. But new connections might arrive while killing existing ones. So again, you must

Disable connections to the database
change default_transaction_read_only status to false
kill the existing connections
Re-enable (r/w) connections to the database

Alternate strategy

Another strategy is to change the permissions on the role used by the application. This can be quite messy and is less general.

For instance, you'd have to revoke/re-grant on not just tables, but sequences, large objects, and probably the schema itself. Further, what exactly is the behavior of existing connections when you change the access? Probably no impact, which means you also need to kill those backends. Finally, let's say the application has read-write access to most tables, but not to others in the schema. You'd have to make sure your re-granting doesn't include those objects as well.

Another possibility is to LOCK all the tables, by querying the catalog and performing a dynamic query. That seemed to perilous for my tastes.

Implementation

Pause_service

The database instance name is 'gitlabhq' and the username of the application is 'gitlab'. Replace it with your own:

  psql -Upostgres  <<'PAUSE_DB'
    -- 1. disable new connections
    alter database gitlabhq_production with allow_connections = off;
    -- 2. Make DB read-only
    alter database gitlabhq set default_transaction_read_only = true;
    -- 3. Inobtrusively but safely terminate current connections
    DO $X$ BEGIN
        -- kill open idle connections, try up to 9x. Last time, kill regardless
        FOR i IN 1..10 LOOP
          PERFORM pg_terminate_backend(pid) from pg_stat_activity where usename = 'gitlab'
            and (i >= 10 OR state in ('idle', 'disabled' ));
          PERFORM pg_stat_clear_snapshot();
          EXIT WHEN NOT EXISTS ( select pid from pg_stat_activity where usename = 'gitlab' );
          RAISE NOTICE 'pg backends still open: sleeping 2 seconds';
          PERFORM pg_sleep(2);
          PERFORM pg_stat_clear_snapshot();
        END LOOP;
        -- send notice if still open connections
        IF EXISTS ( select pid from pg_stat_activity where usename = 'gitlab' ) THEN
            RAISE NOTICE 'Hung backends. Backup might not be 100%% consistent';
        END IF;
    END;$X$;
    -- 4. Allow read-only connections while checkpointing/snapshotting
    alter database gitlabhq with allow_connections = on;
    CHECKPOINT;

Resume

    alter database gitlabhq_production with allow_connections = off;
    alter database gitlabhq set default_transaction_read_only = false;
    SELECT pg_stat_clear_snapshot();
    SELECT pg_terminate_backend(pid) from pg_stat_activity where usename = 'gitlab';
    alter database gitlabhq with allow_connections = on;

There's a possibility that in this last step you will kill long-running read-only/SELECT queries, but in my experience, such long-running queries can last minutes if not hours, and it's acceptable to kill these in order to ensure uptime for everyone else.


How can I disable the @ symbol in SQL scripts for sqlplus?

The problem really only occurs because you placed the @media directive at the beginning of a new line. If you change your code like this:

css VARCHAR2(32767) DEFAULT '@media only screen and (min-width: 980px) {

or like this:

css VARCHAR2(32767) DEFAULT
'@media only screen and (min-width: 980px) {

then it works fine.

I know it does not answer your specific question about ignoring the @ character, but it does solve your problem ...

More generally, all you need is to avoid having any @xxxx at the beginning of a line you pass to sqlplus. Just place it at the back of the previous line.

Note that just adding white space at the beginning of the line won't work.

Another possibility is to add a comment in front of each @xxxx clause, no matter where it is located. Something like this:

css VARCHAR2(32767) DEFAULT '
/* */ @media only screen and (min-width: 980px) {

sqlplus will handle that as regular text. And your CSS parser should ignore it as a comment.


The very last CAST is missing a parenthesis

Set @d2 = CAST(GetDate()-2 As Date

should be

Set @d2 = CAST(GetDate()-2 As Date)

That fixes the main issue. (also, once that was fixed you had and extra END at the very end which I removed--or alternately, adding an additional BEGIN.)

    Declare @d1 datetime, @d2 datetime
    Set @d1 = '01/01/2013'
    Set @d2 = '01/04/2013'

    if @d2 >= CAST(GetDate() As Date)
      Begin
        if Not Exists(Select TOP 1 * FROM farquard WHERE CAST(stinktoothruth As Date) = @d2)
          BEGIN
            Set @d2 = CAST(GetDate()-2 As Date)
          END
      END
    ELSE
          BEGIN
            Set @d2 = CAST(GETDATE()-1 As Date)
          END
--END REMOVED

OR, alternately,

Declare @d1 datetime, @d2 datetime
Set @d1 = '01/01/2013'
Set @d2 = '01/04/2013'

if @d2 >= CAST(GetDate() As Date)
Begin
  if Not Exists(Select TOP 1 * FROM farquard WHERE CAST(stinktoothruth As Date) = @d2)
      BEGIN
        Set @d2 = CAST(GetDate()-2 As Date)
    END
ELSE
--BEGIN ADDED
begin
      BEGIN
        Set @d2 = CAST(GETDATE()-1 As Date)
      END
  END
end


Importing CSV file into a PostgreSQL table

I used psql to push the CSV file to the table, as suggested by @a_horse_with_no_name.

psql -h port -d db -U user -c "\copy products from 'products.csv' with delimiter as ',' csv header;"

It only took a couple of minutes to copy the table, compared to 10+ hours with the python script.


What exactly does the last_value() window function do?

This happens because default window frame is range between unbounded preceding and current row, so the last_value() never looks beyond current row unless you change the frame.

From MSDN:

  If ORDER BY is not specified entire partition is used for a window
  frame. This applies only to functions that do not require ORDER BY
  clause. If ROWS/RANGE is not specified but ORDER BY is specified,
  RANGE UNBOUNDED PRECEDING AND CURRENT ROW is used as default for
  window frame. This applies only to functions that have can accept
  optional ROWS/RANGE specification. For example, ranking functions
  cannot accept ROWS/RANGE, therefore this window frame is not applied
  even though ORDER BY is present and ROWS/RANGE is not.

Since last_value() has an optional order by clause, its default window frame ends at the current row.  So with the default frame, no matter which partition or ordering you choose, last_value() returns the value from the current row.


How-to implement an entity with an unknown maximum number of attributes?

You could do this. It would allow good performance for normal duration games, while allowing you to also store long running games.

CREATE TABLE InningRuns (
    GameId INT NOT NULL REFERENCES [...],
    Team CHAR(4) NOT NULL, --'Home','Away'
    Inning1 TINYINT, --Seeing how more than 255 runs are not really possible in an inning
    Inning2 TINYINT,
    [...],
    Inning9 TINYINT,
    ExtraInnings XML | TINYINT[] | VARBINARY | ETC., --Use to hold any runs in extra innings.
    PRIMARY KEY (GameId, Team)
)

You could further normalize and have a row for each unique combination of game, team, & inning. This would allow you as many innings as the InningId datatype would allow.

CREATE TABLE InningRuns (
    InningRunId INT IDENTITY PRIMARY KEY,
    GameId INT NOT NULL REFERENCES [...],
    Team CHAR(4) NOT NULL, --'Home','Away'
    InningId TINYINT, --Seeing how more than 255 innings might be excessive
    Runs TINYINT,
    UNIQUE (GameId, Team, InningId)
)

Edit: I know PostgreSQL uses Sequences instead of IDENTITY, I don't recall the correct syntax off hand, so translate accordingly.


The primary problems with GUIDs, especially non-sequential ones, are:

Size of the key (16 bytes vs. 4 bytes for an INT):  This means you're storing 4 times the amount of data in your key along with that additional space for any indexes if this is your clustered index.
Index fragmentation: It is virtually impossible to keep a non-sequential GUID column defragmented because of the completely random nature of the key values.

So what does this mean to your situation?  It comes down to your design.  If your system is simply about writes and you have no concern about data retrieval, then the approach outlined by Thomas K is accurate.  However, you have to keep in mind that by pursuing this strategy, you're creating many potential issues for reading that data and storing it.  As Jon Seigel points out, you will also be occupying more space and essentially having memory bloat.

The primary question around GUIDs is how necessary they are.  Developers like them because they ensure global uniqueness, but it's a rare occasion that this kind of uniqueness is necessary.  But consider that if your maximum number of values is less than 2,147,483,647 (the maximum value of a 4 byte signed integer), then you're probably not using the appropriate data type for your key.  Even by using BIGINT (8 bytes), your max value is 9,223,372,036,854,775,807.  This is typically enough for any non-global database (and many global ones) if you need some auto-incrementing value for a unique key.

Finally, as far as using a heap versus a clustered index, if you are purely writing data a heap would be most efficient because you are minimizing overhead for inserts.  However, heaps in SQL Server are extremely inefficient for data retrieval.  My experience has been that a clustered index is always desirable if you have the opportunity to declare one.  I have seen the addition of a clustered index to a table (4 billion+ records) improve overall select performance by a factor of 6.

Additional information:

GUIDs as PRIMARY KEYs and/or the clustering key - Kimberly L. Tripp
Disk space is cheap... - Kimberly L. Tripp (hat tip Zane )
GUID vs INT Debate – Microsoft SQL Server Tips & Tricks


How to get results in separate rows after joining two tables

What you need is not a join which combines tables horizontally, you need a union set operator to join them vertically.
In your images all tables are named 'table 1' so I changed one of them to 'table 2' to make the query valid.

SELECT PlanName AS ProductName, Amount, StartDate, EndDate
FROM Table1
UNION ALL
SELECT DiscountName, Amount, StartDate, EndDate
FROM Table2
ORDER BY ProductName;

HTH


Is there's another function to optimize this query in MySQL?

You can use a regular expression to match '1 ' to '6 ' at the beginning of the string:

data.pro rlike '^([1-6] )'

Now put this in a CASE:

case when data.pro rlike '^([1-6] )'
     then cast(substring(data.pro from 1 for 1) as unsigned)
end

See the result in MySQL 5.5 on SQLFiddle.


Select only last row from table for list of values

For big tables, modern PostgreSQL (your example suggests Postgres 11, since PKs are not supported for partitioned tables before that), and many input values, this should perform excellently:

SELECT t1.*
FROM   unnest('{\xaaa,\xbbbb}'::bytea[]) a
CROSS  JOIN LATERAL (
   SELECT *
   FROM   t1
   WHERE  t1.a = a.a
   ORDER  BY t1.r_pointer DESC
   LIMIT  1
   ) t1;

Your PK on (a, r_pointer) provides just the index needed for this.

Related:

How to properly implement compound greatest-n filtering


create table usr_t_user_reg
(username varchar2(30),
db_instance  varchar2(30),
primary key(username,db_instance)
)
/

CREATE OR REPLACE VIEW usr_v_user_not_reg AS
  SELECT username "User", db_instance "Instance",
    (
      CASE
        WHEN username IN
        (
          SELECT username
          FROM sys.dba_users
          MINUS
          SELECT username
          FROM usr_t_user_reg
        ) THEN 'not registered'
        WHEN username IN
        (
          SELECT username
          FROM usr_t_user_reg
          MINUS
          SELECT username
          FROM sys.dba_users
        ) THEN 'no longer present'
      END
    ) "Status"
  FROM usr_t_user_reg
  WHERE db_instance = 'TEST_DB'
    AND username NOT IN
    (
      SELECT username
      FROM usr_t_user_reg
      INTERSECT
      SELECT username
      FROM sys.dba_users
    )
/

Running the script with sqlplus gives the following error message
Table created.

          FROM sys.dba_users
               *
ERROR at line 8:
ORA-01031: insufficient privileges

this is one of the errors where the error description of the Oracle Database Error Messages 11g Release 2does not give a usefule hint

ORA-01031: insufficient privileges
Cause: An attempt was made to change the current username or password without the appropriate privilege. This error also occurs if attempting to install a database without the necessary operating system privileges. When Trusted Oracle is configure in DBMS MAC, this error may occur if the user was granted the necessary privilege at a higher label than the current login.
Action: Ask the database administrator to perform the operation or grant the required privileges. For Trusted Oracle users getting this error although granted the the appropriate privilege at a higher label, ask the database administrator to regrant the privilege at the appropriate label.

In the Oracle Database SQL Language Reference 11g Release 2 you can find the following prerequisite for creating a view

The owner of the schema containing the view must have the privileges necessary to either select, insert, update, or delete rows from all the tables or views on which the view is based. The owner must be granted these privileges directly, rather than through a role.

Neither SELECT_CATALOG_ROLE role nor DBA role is sufficient for creating your view.
So issue a
GRANT SELECT ON DBA_USERS TO your_user;

and now your view will work.
Instead of the object privilege SELECT ON DBA_USERS you can grant the system privilege SELECT ANY DICTIONARY. In  contrast to SELECT_CATALOG_ROLE this is not a role but a system privilege.
Note that the error message spots to line 8 of the CREATE VIEW statement that is the line that actually contains the object that causes the error.


MySQL Schema Design: One Possible Values Between Multiple Columns

id, species_name, dominant_color, owner_id,
type ENUM('mammal', 'bird', 'reptile', 'arachnid', ...),
other_characteristics TEXT

Where other_characteristics has JSON in it.  You can add whatever you want there.

Other tips:  Do not blindly use (255); use some reasonable limit.  Once wingspan is in the JSON, you can get rid of the ridiculously precise DECIMAL(11,8).


How to refresh reference to objects in Visual Studio SSDT?

Ok, I've find out the answer for my question. One man had similar case and put it on MSDN. And Microsoft answer was that Visual Studio will not implement Visual Studio SSDT InteliSense reference refreshment feature. The workaround they've proposed is to remove and recreate the "database reference" in particular SSDT project. So I've removed old database reference and next create it back again. After such modification InteliSense drop-down list for references for TSQL code has been refreshed and works well. So when you add new objects to the referenced database you have to remove and create again database reference to be able to see newly created objects in InteliSense drop-down list when coding.


SELECT very slow when no results and a LIMIT is specified

Postgres does a bad job estimating the frequency of the combination of predicates in your query:

SELECT * FROM tbl
WHERE  related_id = 1 AND platform = 'p2'::platforms
LIMIT  20;

Each of your predicates is not very selective on its own - that information is available to Postgres ("most common values") - assuming your statistics are up to date:

  ~ 21 million rows total.
  ~ 3 million rows WHERE related_id=1
  ~ 8 million rows WHERE platform=p2

IOW, ~ every 7th row passes the 1st filter and ~ every 3rd row passes the 2nd. Postgres does the (naive) math and expects that roughly every 20th row qualifies. Since there is no ORDER BY, any 20 qualifying rows will do. The fastest way should be to scan the table sequentially and be done after ~ 400 rows - only a few data pages, very cheap.

Using any indexes adds some extra cost, Postgres needs to scan index and table. (Exception: index-only scans, which are not possible here for your SELECT *). That will only pay if Postgres would otherwise have to read enough additional pages to estimate that to be more expensive. That's how I would explain that you see a sequential scan for a small LIMIT, but a bitmap index scan for a big (or no) LIMIT.

Unfortunately, the combination of your predicates turns out to be unexpectedly rare. Postgres has to scan the whole table just to find only 2 qualifying rows. (The index would actually be much cheaper in any case.)

  2 rows WHERE related_id=1 AND platform=p2

The combined frequency of values in multiple columns is not available to Postgres. Think about it: gathering statistics like that would quickly get out of hand.

A very simple and efficient solution for this particular case: Create a partial index:

CREATE INDEX related_id_1_platform_2_idx ON tbl (id)
WHERE  related_id = 1 AND platform = 'p2'::platforms;

Not only will this super-tiny index (2 rows) be a perfect match for your query, it will also make a count estimate available for your particular combination (entry in pg_class.reltuples). The actual index column is mostly irrelevant for this one, pick a small column, typically best to make it the PK.

If one of the two predicates can change, there is a more generic approach. Say, if related_id = 1 is the stable condition, then create:

CREATE INDEX related_id_1_idx ON tbl (platform)
WHERE  related_id = 1;

The index column is relevant again. This may not be enough to tip the scales because Postgres only collects complete statistics for index columns of functional indexes (else it relies on the statistics of the underlying table). I propose:

CREATE INDEX related_id_1_func_idx ON tbl ((platform::text::platforms))  -- double parens!
WHERE  related_id = 1;

Note the extra pair of parentheses - a syntactical necessity for the cast shorthand.
The expression platform::text::platforms doesn't actually change anything - it casts your enum to text and back. But it makes Postgres collect full statistics on the (presumed) new values.

Now, (after ANALYZE tbl) we have full statistics including most common values of platform for related_id = 1.

Check with:

SELECT *
FROM   pg_stats
WHERE  schemaname = 'public'  -- actual schema
AND    tablename  = 'related_id_1_func_idx';  -- actual idx name

And Postgres should choose the index for your case - if you repeat the same expression in your query. So:

SELECT ...
WHERE related_id = 1 AND platform::text::platforms = 'p2'::platforms;

Related:

Index that is not used, yet influences query

About most common values in the Postgres statistics:

Speeding up a GROUP BY, HAVING COUNT query