Is there any way to do a SELECT query without any optimization in SQL Server?

For the query plan itself, you can force compilation every time using the following option on your query:

OPTION (RECOMPILE);

However I suspect what is happening is that the second and subsequent executions of the query are pulling the data from memory instead of disk, and of course memory is faster than disk.

If you are trying to test the scenario where this data always comes from disk, there's not really a good way to do that on your production instance without affecting anything else, because you can only drop clean buffers for the entire instance at a time, not for a single database, never mind table. So what you could do perhaps is set up an instance on the same hardware, with just this table, and run the following before every run of the query:

CHECKPOINT;
DBCC DROPCLEANBUFFERS;

However, this does not make a lot of sense, as ideally you will be querying data that is in memory, and you should be optimizing for the best case scenario. You should have some idea of what the worst case scenario is like, but again, this should not be the normal situation, unless you have a very big database and a very small amount of memory (in which case, open the wallet and buy more memory - it's much cheaper than optimizing for the lack of it).


Do postgres autovacuum properties persist for DB replications?

VACUUM (both manual and auto) do not run on a physical replica.  The results of a VACUUM running on the master get replicated over, just like other changes do.  Per-table vacuum settings are stored in database catalog tables, and so get replicated over, but they have no effect unless/until the replica gets promoted.

For logical replica, its VACUUM settings are completely separate from the publisher.  Both the server-level and table-level settings are just whatever you set them to.


MySQL memory usage keeps growing. 30GB growth in 3 weeks

Well, I did not spot a likely cause of the 30GB, but there are some issues to address:

Observations:

Version: 5.7.19-17-log
64 GB of RAM
Uptime = 23d 17:12:41
You are not running on Windows.
Running 64-bit version
You appear to be running entirely (or mostly) InnoDB.

The More Important Issues:

Normally I would suggest increasing innodb_buffer_pool_size, but since the goal here is to figure out why so much RAM is being used; I will put that off.

Tables are being opened at a large rate.  Perhaps because of lots of short connections?  Perhaps queries with lots of tables?  The OS apparently has a limit of 1024 files per process.  This is unreasonably small for MySQL; try to change the OS limit (to, say, 4096) so that open_file_limit can go up, and then table_open_cache can go up -- to, say, 1000.

'open_files_limit', '1024' -- this disagrees with the unreasonable setting (102400) in my.cnf.  Probably the OS overrode it.

innodb_log_file_size is unreasonably low at only 32M; raise it to 200M.  (See the 5.7 manual for specifics on how to do that.)

Do you have essentially no "transactions"?  That is, are you happy with autocommit=ON?

Some of the details below indicate inefficient queries -- find them so we can discuss them and hopefully make them run more efficiently.

Are you using SSD drives?  (Some of the numbers hint that you are.)

Hundreds of SHOW commands are being run per minute.  (Production systems rarely need to run any SHOW commands.)  Are you using some 3rd party software that can't remember what the schema is?

Details and other observations:

( Innodb_buffer_pool_pages_flushed ) = 220,833,222 / 2049161 = 107 /sec -- Writes (flushes)
-- check innodb_buffer_pool_size

( innodb_buffer_pool_size / _ram ) = 20480M / 65536M = 31.2% -- % of RAM used for InnoDB buffer_pool

( Opened_tables ) = 84,342,523 / 2049161 = 41 /sec -- Frequency of opening Tables
-- increase table_open_cache

( Opened_table_definitions ) = 3,568,448 / 2049161 = 1.7 /sec -- Frequency of opening .frm files
-- Increase table_definition_cache and/or table_open_cache.

( open_files_limit ) = 1,024 -- ulimit -n
-- To allow more files, change ulimit or /etc/security/limits.conf or in sysctl.conf (kern.maxfiles & kern.maxfilesperproc) or something else (OS dependent)

( Table_open_cache_overflows ) = 84,342,104 / 2049161 = 41 /sec
-- May need to increase table_open_cache

( Table_open_cache_misses ) = 84,342,523 / 2049161 = 41 /sec
-- May need to increase table_open_cache

( (Innodb_buffer_pool_reads + Innodb_buffer_pool_pages_flushed) ) = ((42793206 + 220833222) ) / 2049161 = 128 /sec -- InnoDB I/O
-- Increase innodb_buffer_pool_size?

( Innodb_os_log_written / (Uptime / 3600) / innodb_log_files_in_group / innodb_log_file_size ) = 234,594,526,208 / (2049161 / 3600) / 2 / 32M = 6.14 -- Ratio
-- (see minutes)

( Uptime / 60 * innodb_log_file_size / Innodb_os_log_written ) = 2,049,161 / 60 * 32M / 234594526208 = 4.88 -- Minutes between InnoDB log rotations Beginning with 5.6.8, this can be changed dynamically; be sure to also change my.cnf.
-- (The recommendation of 60 minutes between rotations is somewhat arbitrary.) Adjust innodb_log_file_size. (Cannot change in AWS.)

( Innodb_rows_deleted / Innodb_rows_inserted ) = 94,976,602 / 102716752 = 0.925 -- Churn
-- "Don't queue it, just do it." (If MySQL is being used as a queue.)

( Innodb_row_lock_waits ) = 398,208 / 2049161 = 0.19 /sec -- How often there is a delay in getting a row lock.
-- May be caused by complex queries that could be optimized.

( Innodb_dblwr_writes ) = 16,335,687 / 2049161 = 8 /sec -- "Doublewrite buffer" writes to disk. "Doublewrites" are a reliability feature. Some newer versions / configurations don't need them.
-- (Symptom of other issues)

( expand_fast_index_creation ) = OFF -- ALTER and OPTIMIZE may be greatly sped up by using ON.
-- Probably better to be ON.

( innodb_thread_concurrency ) = 0 -- 0 = Let InnoDB decide the best for concurrency_tickets.
-- Set to 0 or 64. This may cut back on CPU.

( innodb_print_all_deadlocks ) = OFF -- Whether to log all Deadlocks.
-- If you are plagued with Deadlocks, turn this on. Caution: If you have lots of deadlocks, this may write a lot to disk.

( local_infile ) = ON
-- local_infile = ON is a potential security issue

( bulk_insert_buffer_size / _ram ) = 8M / 65536M = 0.01% -- Buffer for multi-row INSERTs and LOAD DATA
-- Too big could threaten RAM size. Too small could hinder such operations.

( Created_tmp_disk_tables ) = 2,577,361 / 2049161 = 1.3 /sec -- Frequency of creating disk "temp" tables as part of complex SELECTs
-- increase tmp_table_size and max_heap_table_size.
Check the rules for temp tables on when MEMORY is used instead of MyISAM. Perhaps minor schema or query changes can avoid MyISAM.
Better indexes and reformulation of queries are more likely to help.

( tmp_table_size ) = 512M -- Limit on size of MEMORY temp tables used to support a SELECT
-- Decrease tmp_table_size to avoid running out of RAM. Perhaps no more than 64M.

( (Com_insert + Com_update + Com_delete + Com_replace) / Com_commit ) = (88969136 + 202120925 + 856731 + 0) / 7 = 4.17e+7 -- Statements per Commit (assuming all InnoDB)
-- Low: Might help to group queries together in transactions; High: long transactions strain various things.

( Select_scan ) = 19,298,109 / 2049161 = 9.4 /sec -- full table scans
-- Add indexes / optimize queries (unless they are tiny tables)

( Com_insert + Com_delete + Com_delete_multi + Com_replace + Com_update + Com_update_multi ) = (88969136 + 856731 + 0 + 0 + 202120925 + 0) / 2049161 = 142 /sec -- writes/sec
-- 50 writes/sec + log flushes will probably max out I/O write capacity of normal drives

( expire_logs_days ) = 1 -- How soon to automatically purge binlog (after this many days)
-- Too large (or zero) = consumes disk space; too small = need to respond quickly to network/machine crash.
(Not relevant if log_bin = OFF)

( innodb_autoinc_lock_mode ) = 1 -- Galera: desires 2 -- 2 = "interleaved"; 1 = "consecutive" is typical; 0 = "traditional".

( slave_pending_jobs_size_max / max_allowed_packet ) = 16M / 4M = 4 -- For parallel slave threads
-- slave_pending_jobs_size_max must not be less than max_allowed_packet

( slow_query_log ) = OFF -- Whether to log slow queries. (5.1.12)

( long_query_time ) = 10.000000 = 10 -- Cutoff (Seconds) for defining a "slow" query.
-- Suggest 2

( back_log / max_connections ) = 92 / 214 = 43.0%

( Connections ) = 170,473,414 / 2049161 = 83 /sec -- Connections
-- Increase wait_timeout; use pooling?

Abnormally large (not necessarily "bad"):

Binlog_cache_use = 130 /sec
Bytes_sent = 15636572 /sec
Com_release_savepoint = 0.042 /HR
Com_rollback_to_savepoint = 0.39 /sec
Com_savepoint = 0.042 /HR
Com_show_create_table = 0.39 /sec
Com_show_fields = 0.83 /sec
Com_show_table_status = 0.39 /sec
Com_show_triggers = 0.43 /sec
Com_update = 99 /sec
Created_tmp_files = 0.26 /sec
Handler_commit = 1423 /sec
Handler_savepoint = 0.084 /HR
Handler_savepoint_rollback = 0.79 /sec
Innodb_buffer_pool_pages_made_not_young = 4570 /sec
Innodb_buffer_pool_read_ahead = 9.24e+7
Innodb_master_thread_active_loops = 2.02e+6
Innodb_rows_deleted = 46 /sec
Open_table_definitions = 16,384
Performance_schema_digest_lost = 4.1e+7
Qcache_hits = 543 /sec       -- This seems strange since the QC is OFF
Qcache_inserts = 198 /sec
Sort_rows = 10139 /sec
Ssl_default_timeout = 7,200
Ssl_finished_accepts = 53
Ssl_session_cache_size = 0.0MB
eq_range_index_dive_limit = 200
innodb_purge_threads = 4
table_definition_cache = 16,384

Note

The buffer_pool will grow until it gets to 20GB.  So, I would expect MySQL to use some amount more than that, but not 30GB.  Did it keep growing after 30?


Which is better in a master-slave setup to use local queue or write changes to remote instantly?

It's fine to write to the master directly. A database does much more for you than just persist your changes to disk. It manages transaction, enforces integrity, etc. If you introduce a queue you introduce a new cog in the machine that can break and a lot of complexity that you need to manage for yourself and that is actually not required in most cases.

I worked with MySQL master servers having thousands of connections all the time and handling thousands of inserts/updates per second without problem. InnoDB is quite efficient if your tables are well designed and you have your configuration properly optimized. There are many ways how tables and my.cnf can be optimized for writes if that's your main concern. Some general tips:

Keep your innodb_log_file_size big enough to keep about an hour
worth of changes.
If you can afford have a large enough server to
keep the hot dataset in innodb_buffer_pool
In InnoDB don't use complex primary keys. For inserts AUTO_INCREMENT primary keys are the best.
Look into table partitioning if you have tables in the tens of gigabytes range.

(These are only the very basics there are much more your can do here)

You should worry about IO usage first because CPU and Memory are less likely to become the bottleneck. Keep an eye on IO utilization to identify when you're likely to hit the ceiling of what your system can do. If your writes are competing for locks it's possible you will experience high CPU usage but that's usually coming from mutex contention not "actual" CPU work. These can be remedied designing your tables and queries in such a way that they don't lock out each other.


How to take advantage of AWS EC2 instance store (SSD) in a database environment?

The ephemeral disk is excellent for exactly the two use cases you mentioned, swap and /tmp.  Don't underestimate the performance benefits, here -- the ephemeral disks are not only SSDs, they're also physically inside the host machine, unlike EBS, which is network-attached, so you get high bandwidth and low latency to the storage device.

Otherwise, their applications are somewhat limited for "normal" database setups... but in certain other applications, anything where the machine itself is also more ephemeral by nature (QA/dev environments, scalable read replicas), they can offer excellent performance.

If you're the experimenting/creative type, you might toy with a RAID-1 array split between EBS and ephemeral (which could theoretically improve your read throughput without jeopardizing durability).

  seems to be a waste of the money spent on reserving the SSD storage.

Ephemeral disks don't cost extra -- they are included in the price of the instance, so the cost for a given instance type is the same whether you activate them or not.

Incidentally -- always enable them when you launch an instance that includes them, because you can't activate them later.


and welcome to Stack Exchange. Going forward, try to break questions out individually. Here's how to write a great question.

  Do sp_blitz script take into consideration delete, insert, update costs as well?

sp_BlitzIndex has several sections based on SQL Server's diagnostic management views.

The missing indexes are based on sys.dm_db_missing_index_details, which don't take into account delete/update/inserts.
The existing indexes list is based on sys.dm_db_index_usage_stats and sys.dm_db_index_physical_stats, which do take into account deletes/updates/inserts.

  Moreover, how can i list bad indexes via sp_blitz? and how are they determined to be bad?

sp_BlitzIndex doesn't call indexes bad. It treats indexes a lot like human behaviors - for example, hoarding isn't necessarily bad in small amounts, or agoraphobia, or kleptomania, or being a furry. All kinds of behaviors are okay in small doses. It's up to you, not the script, to determine if an index is bad.


OLE DB Driver sql 2008r2 to Oracle failing

Install the Oracle Instant client - both the 32 and 64 bit versions find orasql12.dll and register it with regsvr32 orasql12.dll and c:\windows\syswow64\regsvr32.exe orasql12.dll for the 32 bit version. and add the oracle bin directory the the path environment variable

Make sure that you can see the oracle driver in ODBC both 32bit and 64bit and under data providers in the management studio. Check allow "Allow Inprocess" in the driver properties or run EXEC master.dbo.sp_MSset_oledb_prop N'OraOLEDB.Oracle', N'AllowInProcess', 1


is it safe to grant permission to all users in Microsoft sql server

I'm not entirely sure what problem you're trying to solve, but if you grant the kind of access you're describing to "Everyone" and you get hacked, now you have opened up a lot more possibilities for someone to do damage to your systems. That is access that they could not have done if access had only been granted to a select few people.  It sounds like you want to have certain people with the ability to move database files around as needed. This will require a login and user and the ALTER DATABASE permission as well as an AD group that is either a local admin on the server or has been granted specific access to the folder(s) where the database files are stored. See the below question and answer about ALTER Database.
https://stackoverflow.com/questions/9798968/permissions-required-to-run-alter-database-set-single-user-statement-on-sql-se


How to represent foreign key in an ER diagram?

ER Diagrams were originally used only to represent the ER model.  The ER model does not use foreign keys to represent relationships.  It uses lines between boxes.  The lines have some kind of indicator for cardinality at either end or both ends.  Sometimes, a relationship will be indicated separately by a diamond.

Today, more than half of the ER diagrams floating around are really diagrams of a relational model, and not of an ER model.  A relational model has the foreign keys included in the tables, and these serve to implement the relationships which the ER model identifies.  And a relational model will have an extra table, often called a "junction table" between two entity tables that are linked by a many-to-many relationship.  This junction table contains two or more foreign keys.

There are many ways to represent a relational model. Perhaps the simplest is the "Relationship Diagram" that MS Access can produce from a completed database.  This will be fairly complete, if the database builder has identified the foreign keys.

There are many tools that are more sophisticated than MS Access for making diagrams on a larger scale.  Some of these are used before building the database.  Some are used after.


In Postgres, is it possible to get all rows from a table where a column can be cast to a different type?

You can create a function for this:

create function is_valid_ip(p_data text)
  returns boolean
as
$$
begin
  return p_data::inet is not null;
exception
  when others then return false;
end;
$$
language plpgsql;

This function has the advantage that the same validation is done when casting and when filtering the rows.

BUT this will be quite slow, because an exception block in PL/pgSQL is quite expensive and catching one is even more expensive. So if you have a large number of invalid addresses this is going to be really slow.

You can use the function like this:

select *
from the_table
where is_valid_ip(address)

Another option is to use a regular exception for this:

create function is_valid_ip(p_data text)
  returns boolean
as
$$
  select p_data ~ '[0-9]{1,3}\.[0-9]{1,3}\.[0-9]{1,3}\.[0-9]{1,3}';
$$
language sql;

This function will perform a lot faster than the one that uses casting, but has the downsize that it uses a slightly different rule for checking if the IP address is valid. The regex only check IPv4 addresses, so will most probably also want to extend the regex to check for IPv6 addresses as well (which the cast will do automatically)


How do I identify the natural key of a table?

You are right - a natural key is indeed a combination of one or more columns that uniquely identifies one row in a table.

Natural keys are formed from the business attributes we identify for an entity type. They form part of the vocabulary the users would employ while discussing these entities. They arise "naturally" in discussion: hence the name. Any combination of such attributes that respect the rules of normalisation can form a natural key. Sometimes there can be more than one combination that works. We refer to these as alternate keys. For example, a house could be identified by number, street and town or by latitude and longitude or by postcode and number. There are three different combinations of values that are commonly used by the business, each of which could uniquely identify exactly one house. There are three alternate natural keys.

For your problem you must identify a combination of columns which could uniquely identify a row and distinguish it from the other rows in the table. It will be a combination which will allow you to identify duplicates, as @sp_BlitzErik pointed out. In other words, you can say this row is a duplicate of that row because these columns have the same value in both.

Be careful you choose a combination that must be true because of the definition of the table and the type of data it holds, and not just happens to be true because of the values that happen to be in the table at this particular point in time.

Natural keys stand in contrast to surrogate keys. Surrogate keys are a thing that we invent when implementing a logical data model in physical database management software to account for real-world limitations and peculiarities. Surrogate keys are, as the name suggests, a surrogate for the natural key. They stand in for the natural key and perform its function. Commonly we do this because the natural key is long and contains strings which the DBMS does not process efficiently. The problem is, because we invent them, we can invent values for them and thus enforce uniqueness where the natural key would say there is duplication.

Ideally surrogate keys should only ever be hidden inside our software and never be exposed to the users (though they often are).


MySQL - Creating sort index and update query takes forever

You have joined HST on PCL but ON condition refers to the HST itself and there is no relation between HST and PCL. The table PCL is fully scanned and the only restriction used is the WHERE p.BS = 'B' AND p.SrNo <18000 that has no corresponding index.

UPDATE

  I want to fetch the date when the value of HST.HP first exceeds or equals PCL.T1.

UPDATE PCL AS pp
  JOIN ( SELECT h.Date AS date_the_value_first_exceeded
           FROM PCL AS p
           JOIN HST AS h  ON h.Date = p.Date      -- Joining tables by dates
          WHERE h.HP >= p.T1                      -- The condition you want
          ORDER BY p.Date ASC                     -- Order the result chronologically
          LIMIT 1                                 -- Single line required
       ) AS dd                                    -- Unrestricted join, each to each
   SET pp.T1A = dd.date_the_value_first_exceeded
 WHERE pp.BS = 'B'
   AND pp.SrNo < 18000
;

The version with MIN() function:

UPDATE PCL AS pp
  JOIN ( SELECT MIN(h.Date) AS date_the_value_first_exceeded
           FROM PCL AS p
           JOIN HST AS h  ON h.Date = p.Date      -- Joining tables by dates
          WHERE h.HP >= p.T1                      -- The condition you want
       ) AS dd                                    -- Unrestricted join, each to each
   SET pp.T1A = dd.date_the_value_first_exceeded
 WHERE pp.BS = 'B'
   AND pp.SrNo < 18000
;


Is there a way to generate table create script in TSQL?

Back in 2007, I asked for an easy way to generate a CREATE TABLE script via T-SQL rather than using the UI or SMO. I was summarily rejected.
However, SQL Server 2012 makes this very easy. Let's pretend we have a table with the same schema across multiple databases, e.g. dbo.whatcha:
CREATE TABLE dbo.whatcha
(
  id INT IDENTITY(1,1),
  x VARCHAR(MAX),
  b DECIMAL(10,2),
  y SYSNAME
);

The following script uses the new sys.dm_exec_describe_first_results_set dynamic management function to retrieve the proper data types for each of the columns (and ignoring the IDENTITY property). It builds the #tmp table you need, inserts from each of the databases in your list, and then selects from #tmp, all within a single dynamic SQL batch and without using a WHILE loop (that doesn't make it better, just simpler to look at and allows you to ignore Database_Ref_No entirely :-)).
SET NOCOUNT ON;

DECLARE @sql NVARCHAR(MAX), @cols NVARCHAR(MAX) = N'';

SELECT @cols += N',' + name + ' ' + system_type_name
  FROM sys.dm_exec_describe_first_result_set(N'SELECT * FROM dbo.whatcha', NULL, 1);

SET @cols = STUFF(@cols, 1, 1, N'');

SET @sql = N'CREATE TABLE #tmp(' + @cols + ');'

DECLARE @dbs TABLE(db SYSNAME);

INSERT @dbs VALUES(N'db1'),(N'db2');
  -- SELECT whatever FROM dbo.databases

SELECT @sql += N'
  INSERT #tmp SELECT ' + @cols + ' FROM ' + QUOTENAME(db) + '.dbo.tablename;'
  FROM @dbs;

SET @sql += N'
  SELECT ' + @cols + ' FROM #tmp;';

PRINT @sql;
-- EXEC sp_executesql @sql;

The resulting PRINT output:
CREATE TABLE #tmp(id int,x varchar(max),b decimal(10,2),y nvarchar(128));
  INSERT #tmp SELECT id,x,b,y FROM [db1].dbo.tablename;
  INSERT #tmp SELECT id,x,b,y FROM [db2].dbo.tablename;
  SELECT id,x,b,y FROM #tmp;

When you are confident it's doing what you expect, just uncomment the EXEC.
(This trusts you that the schema is the same; it does not validate that one or more of the tables has since been changed, and may fail as a result.)


It is hard to give a definitive answer on this question until you actually find a difference. I have found none but that does not mean there is no difference only that I have not seen one in the tests I have made.

The easy test is for performance. Either getting the next value in a loop or using a numbers table as a source to generate multiple values at a time. In my tests there were no difference in performance between using no cache and a cache of 1 value but there was a significant performance improvement of using a cache of 2.

This is the code I used to test performance:

declare @D datetime = getdate();

declare @I int = 0;
while @I < 9999
  select @I = next value for dbo.S;

select datediff(millisecond, @D, getdate());

Result:

Cache        Time(ms)
------------ --------
NO CACHE     1200
1            1200
2             600
1000           70

To dig a bit deeper I used the extended events sqlserver.metadata_persist_last_value_for_sequence and sqlserver.lock_acquired to see if there was something different in how the values persists to the system table.

I used this code to test for no cache and cache size of 1 and 4.

DECLARE @S NVARCHAR(max) = '
CREATE EVENT SESSION SeqCache ON SERVER
ADD EVENT sqlserver.lock_acquired(
    WHERE (sqlserver.session_id=({SESSIONID}))),
ADD EVENT sqlserver.metadata_persist_last_value_for_sequence(
    WHERE (sqlserver.session_id=({SESSIONID})))
ADD TARGET package0.event_file(SET filename=N''d:\SeqCache'');';

SET @S = REPLACE(@S, '{SESSIONID}', CAST(@@SPID AS NVARCHAR(max)));

EXEC (@S);

GO

CREATE SEQUENCE dbo.S
AS INT
    START WITH 1
    INCREMENT BY 1
    MINVALUE 1
    MAXVALUE 9999
    NO CYCLE
    NO CACHE;
--    CACHE 1;
--    CACHE 4;

GO

ALTER EVENT SESSION SeqCache ON SERVER STATE = START;

GO

DECLARE @I INT = 0;
WHILE @I < 10
  SELECT @I = NEXT VALUE FOR dbo.S;

GO

ALTER EVENT SESSION SeqCache ON SERVER STATE = STOP;
DROP EVENT SESSION SeqCache ON SERVER;
DROP SEQUENCE dbo.S;

There is no difference in output for using no cache and cache of 1.

Sample output:

name                                      persisted_value mode
----------------------------------------- --------------- -----
lock_acquired                             NULL            SCH_S
lock_acquired                             NULL            IX
lock_acquired                             NULL            U
metadata_persist_last_value_for_sequence  1               NULL
lock_acquired                             NULL            SCH_S
lock_acquired                             NULL            IX
lock_acquired                             NULL            U
metadata_persist_last_value_for_sequence  2               NULL
lock_acquired                             NULL            SCH_S
lock_acquired                             NULL            IX
lock_acquired                             NULL            U
metadata_persist_last_value_for_sequence  3               NULL

When using a cache of 4.

name                                      persisted_value mode
----------------------------------------- --------------- -----
lock_acquired                             NULL            SCH_S
lock_acquired                             NULL            IX
lock_acquired                             NULL            U
metadata_persist_last_value_for_sequence  4               NULL
lock_acquired                             NULL            SCH_S
lock_acquired                             NULL            SCH_S
lock_acquired                             NULL            SCH_S
lock_acquired                             NULL            SCH_S
lock_acquired                             NULL            IX
lock_acquired                             NULL            U
metadata_persist_last_value_for_sequence  8               NULL

The SCH_S lock is done when a value is needed. And when the cache is exhausted it is followed by a IX and the a U lock and finally the event metadata_persist_last_value_for_sequence is fired.

So there should be no difference between using no cache and and cache 1 when it comes to potentially losing values on an unexpected shutdown of SQL Server.

Finally I noticed something in the Message tab in SSMS when creating a sequence with cache 1.

  The cache size for sequence object 'dbo.S' has been set to NO CACHE.

So, SQL Server thinks there is no difference and tells me so. There is however a difference in sys.sequences in the column cache_size. It is NULL for no cache and 1 for a cache of 1.


How can I get the maxium number of concurrent connections to MySQL over a specific time interval?

The only thing I can think of is finding some what (maybe through a cron job) to record the 'Threads_connected' values every minute, and then calculate the max of these values.

I'm not sure if you can do exactly what you are asking for without resetting the 'Max_used_connections' before your monitoring period.


Thats about 25% of the server space.

First and foremost, you really need to increase the size of your drive or move the error logs to a different drive. ~40GB for a C:\ drive might've been an okay default years ago, but you can quickly burn through that with misc files from applications - as you're seeing.

You can easily configure this via SQL Server Configuration Manager:

by modifying the directory for the -e parameter, which controls the error log destination.

  How can I remove the archive logs as I don't need them and reduce the
  size in which the archive files grow to before recycling.

You can of course just delete the files manually, but it sounds like you really want to reduce the number of logs used and/or reduce their size.

The steps contained in the above link detail how to do this in SQL Server Management Studio:

  In Object Explorer, expand the instance of SQL Server, expand
  Management, right-click SQL Server Logs, and then click Configure.
  In the Configure SQL Server Error Logs dialog box, choose from the
  following options.

  a. Log files count

  Limit the number of the error log files before they are recycled

  Check to limit the number of error logs created before they are
  recycled. A new error log is created each time an instance of SQL
  Server is started. SQL Server retains backups of the previous six
  logs, unless you check this option, and specify a different maximum
  number of error log files below.

  Maximum number of error log files

  Specify the maximum number of archived error log files created before
  they are recycled. The default is 6, not including the current one.
  This value determines the number of previous backup logs that SQL
  Server retains before recycling them.

  b. Log file size

  Maximum size for error log file in KB

  You can set the size amount of each file in KB. If you leave it at 0
  the log size is unlimited.


The SQL standard prescribes double quotes for quoted identifiers:

SELECT * FROM "tablename"

MySQL and SQL Server can be configured to comply with the standard, while other DBMSes comply out of the box. However, as others suggested in comments, you should avoid using quoted identifiers if possible, because it opens opportunities for bugs and unexpected behaviour in the SQL code.


Improve query speed for large, wide table

For your example query:
         SELECT * FROM claims
         WHERE gender='F'
         AND age BETWEEN 16 AND 20
         LIMIT 100000;
try to add index on age.
But I suggest to use "SQL SERVER PROFILER" and "Database Engine Tuning Adviser" to monitor your queries, and then add suitable indexes, and make suitable changes.


Is it REALLY possible that the order will not be guaranteed for this particular redundant derived table?

Most databases are quite clear about the fact that an ORDER BY in a subquery is either:

Not allowed: E.g. SQL Server, Sybase SQL Anywhere (unless complemented with TOP or OFFSET .. FETCH)
Meaningless: E.g. PostgreSQL, DB2 (again, unless complemented with OFFSET .. FETCH or LIMIT)

Here's an example from the DB2 LUW manual (emphasis mine)

  An ORDER BY clause in a subselect does not affect the order of the rows returned by a query. An ORDER BY clause only affects the order of the rows returned if it is specified in the outermost fullselect.

The wording is quite explicit, just like PostgreSQL's:

  If sorting is not chosen, the rows will be returned in an unspecified order. The actual order in that case will depend on the scan and join plan types and the order on disk, but it must not be relied on. A particular output ordering can only be guaranteed if the sort step is explicitly chosen.

From this specification, it can be followed that any ordering resulting from the ORDER BY clause in a derived table is merely accidental and may coincidentally match your expected ordering (which it does in most databases in your trivial example), but it would be unwise to rely on this.

Side note on DB2:

In particular, DB2 has a lesser known feature called ORDER BY ORDER OF <table-designator>, which can be used as follows:

SELECT C1 FROM
   (SELECT C1 FROM T1
      UNION
    SELECT C1 FROM T2
    ORDER BY C1 ) AS UTABLE
ORDER BY ORDER OF UTABLE

In this particular case, the ordering of the derived table can be explicitly re-used in the outer most SELECT

Side note on Oracle:

For years it has been a practice in Oracle to implement OFFSET pagination using ROWNUM, which can be reasonably calculated only after ordering a derived table:

SELECT *
FROM (
  SELECT rownum AS rn, t.* -- ROWNUM here depends on the derived table's ordering
  FROM (
    SELECT * FROM table ORDER BY time DESC
  ) t
) t
WHERE rn BETWEEN 10 AND 20

It can be reasonably expected that at least in the presence of ROWNUM in a query, future Oracle versions will not break this behaviour in order not to break pretty much all the legacy Oracle SQL out there, which has not yet migrated to the much more desireable and readable SQL standard OFFSET .. FETCH syntax:

SELECT * FROM table ORDER BY time DESC OFFSET 10 ROWS FETCH NEXT 10 ROWS ONLY


If you are not granted the needed permissions, you will not be able to see the Agent, no matter whether you run Enterprise/Standard/Datacenter, etc...

The roles needed are sysadmin, or the individual roles here - http://msdn.microsoft.com/en-us/library/ms188283.aspx


How can I fetch parent table's value within a trigger?

Tables:

create table PARENT
(
ID BIGINT PRIMARY KEY,
NAME VARCHAR(255) NOT NULL
);

create table CHILD
(
ID BIGINT PRIMARY KEY,
NAME VARCHAR(255) NOT NULL,
PARENT_ID BIGINT NOT NULL
);

Trigger:

DELIMITER $$
 CREATE TRIGGER namecheck BEFORE INSERT ON CHILD
   FOR EACH ROW
BEGIN
  DECLARE parentname VARCHAR(255);

  IF NEW.NAME IS NULL OR NEW.NAME=''
  THEN
    SELECT NAME
    INTO @parentname
    FROM PARENT
    WHERE ID=NEW.PARENT_ID;
    SET NEW.NAME = @parentname;
  END IF;
END;
$$
DELIMITER ;

Test:

mysql> insert into PARENT values ( 1,'phil' );
Query OK, 1 row affected (0.01 sec)

mysql> insert into CHILD values(1,'',1);
Query OK, 1 row affected (0.02 sec)

mysql> select * from CHILD;
+----+------+-----------+
| ID | NAME | PARENT_ID |
+----+------+-----------+
|  1 | phil |         1 |
+----+------+-----------+
1 row in set (0.00 sec)

mysql>

There's no error checking etc, but I'll leave that as an exercise for you.


I found this:

  Removed several “smelly” methods:

  purge(): was an internal implementation detail, not something for end user code to call

  Removed success() in Connection, Query

from
Incompatible Library Changes

But I have not idea how to replace it.


View information on historical deadlocks

Start with sp_BlitzLock. It's a free open source stored procedure written by Erik Darling that examines the built-in System Health Extended Events session in SQL Server 2012 & newer.
You can download it from the First Responder Kit Github repository.
To install it, just run sp_BlitzLock.sql to install the stored procedure, then run:
EXEC sp_BlitzLock

The first result set is a set of deadlock details, and the second is analysis that looks at which apps, users, and tables are most commonly involved in your deadlocks. It also gives you parameters to use for sp_BlitzCache to analyze their query plans, and sp_BlitzIndex to analyze over-indexing and under-indexing issues on the tables that may be contributing.


A good starting point to make a decision in order to use or not this kind of value is the MySQL official manual.

In short:

Optimization

  Declare columns to be NOT NULL if possible. It makes SQL operations
  faster, by enabling better use of indexes and eliminating overhead for
  testing whether each value is NULL. You also save some storage space,
  one bit per column. If you really need NULL values in your tables, use
  them. Just avoid the default setting that allows NULL values in every
  column.

Problems with NULL values

  In SQL, the NULL value is never true in comparison to any other value,
  even NULL. An expression that contains NULL always produces a NULL
  value unless otherwise indicated in the documentation for the
  operators and functions involved in the expression

  When reading data with LOAD DATA INFILE, empty or missing columns are
  updated with ''. To load a NULL value into a column, use \N in the
  data file


How to manage DEFAULT PRIVILEGES for USERs on a DATABASE vs SCHEMA?

Where can I find a decent guide, tutorial or video series on this?

You'll find everything in the manual. Links below.
Granted, the matter is not trivial and sometimes confusing. Here is a recipe for the use case:
Recipe

I want to have it configured so only the hostdb_admin can create
(and drop and alter) tables;
the hostdb_mgr can read, insert, update and delete on all tables by default;
and the hostdb_usr can only read all tables (and views).

As superuser postgres:
CREATE USER schma_admin WITH PASSWORD 'youwish';
-- CREATE USER schma_admin WITH PASSWORD 'youwish' CREATEDB CREATEROLE; -- see below
CREATE USER schma_mgr   WITH PASSWORD 'youwish2';
CREATE USER schma_usr   WITH PASSWORD 'youwish3';

If you want a more powerful admin that can also manage databases and roles, add the role attributes CREATEDB and CREATEROLE above.
Grant each role to the next higher level, so all levels "inherit" at least the set of privileges from the next lower level (cascading):
GRANT schma_usr TO schma_mgr;
GRANT schma_mgr TO schma_admin;

CREATE DATABASE hostdb;
REVOKE ALL ON DATABASE hostdb FROM public;  -- see notes below!

GRANT CONNECT ON DATABASE hostdb TO schma_usr;  -- others inherit

\connect hostdb  -- psql syntax

I am naming the schema schma (not hostdb which would be confusing). Pick any name. Optionally make schma_admin the owner of the schema:
CREATE SCHEMA schma AUTHORIZATION schma_admin;

SET search_path = schma;  -- see notes

ALTER ROLE schma_admin IN DATABASE hostdb SET search_path = schma; -- not inherited
ALTER ROLE schma_mgr   IN DATABASE hostdb SET search_path = schma;
ALTER ROLE schma_usr   IN DATABASE hostdb SET search_path = schma;

GRANT USAGE  ON SCHEMA schma TO schma_usr;
GRANT CREATE ON SCHEMA schma TO schma_admin;

ALTER DEFAULT PRIVILEGES FOR ROLE schma_admin
GRANT SELECT                           ON TABLES TO schma_usr;  -- only read

ALTER DEFAULT PRIVILEGES FOR ROLE schma_admin
GRANT INSERT, UPDATE, DELETE, TRUNCATE ON TABLES TO schma_mgr;  -- + write, TRUNCATE optional

ALTER DEFAULT PRIVILEGES FOR ROLE schma_admin
GRANT USAGE, SELECT, UPDATE ON SEQUENCES TO schma_mgr;  -- SELECT, UPDATE are optional

For and drop and alter see notes below.
Postgres 14 adds the predefined, non-login roles pg_read_all_data and pg_write_all_data to give read-only / write-only access to all objects in all schemas. That goes beyond what's being asked here, but may be useful. See:

PostgreSQL: Give all permissions to a user on a PostgreSQL database

As things get more advanced I'll also have questions to apply similar
restrictions on TRIGGERS, stored procedures, VIEWS and perhaps other objects.

Views are special. For one:

... (but note that ALL TABLES is considered to include views and foreign tables).

And for Updatable Views:

Note that the user performing the insert, update or delete on the view
must have the corresponding insert, update or delete privilege on the
view. In addition the view's owner must have the relevant privileges
on the underlying base relations, but the user performing the update
does not need any permissions on the underlying base relations (see
Section 38.5).

Triggers are special, too. You need the TRIGGER privilege on the table, and:

What are the privileges required to execute a trigger function in PostgreSQL 8.4?

But we are already over-expanding the scope of this question ...
Important Notes
Ownership
If you want to allow schma_admin (alone) to drop and alter tables, make the role own all objects. The documentation:

The right to drop an object, or to alter its definition in any way, is not treated as a grantable privilege; it is inherent in the owner, and cannot be granted or revoked.
(However, a similar effect can be obtained by granting or revoking membership in the role that owns the object; see below.)
The owner implicitly has all grant options for the object, too.

ALTER TABLE some_tbl OWNER TO schma_admin;

Or create all objects with the role schma_admin to begin with, then you need not set the owner explicitly. It also simplifies default privileges, which you then only have to set for the one role:
Pre-existing objects
Default privileges only apply for newly created objects and only for the particular role they are created with. You'll want to adapt permissions for existing objects, too:

Permission denied for relation <table>

The same applies if you create objects with a role that does not have DEFAULT PRIVILEGES set, like the superuser postgres.  Reassign ownership to schma_admin and set privileges manually - or set DEFAULT PRIVILEGES for postgres as well (while connected to the right DB!):
ALTER DEFAULT PRIVILEGES FOR ROLE postgres GRANT ...  -- etc.

Default privileges
You were missing an important aspect of the ALTER DEFAULT PRIVILEGES command. It applies to the current role unless specified otherwise:

Can't alter default privileges

Default privileges only apply to the current database. So you don't mess with other databases in the DB cluster. The documentation:

for all objects created in the current database

You may also want to set default privileges for FUNCTIONS and TYPES (not just TABLES and SEQUENCES), but those might not be needed.
Default privileges for PUBLIC
Default privileges granted to PUBLIC are rudimentary and overestimated by some. The documentation:

PostgreSQL grants default privileges on some types of objects to
PUBLIC. No privileges are granted to PUBLIC by default on tables,
columns, schemas or tablespaces. For other types, the default
privileges granted to PUBLIC are as follows: CONNECT and CREATE TEMP TABLE for databases; EXECUTE privilege for functions; and USAGE
privilege for languages.

Bold emphasis mine. typically the one command above is enough to cover everything:
REVOKE ALL ON DATABASE hostdb FROM public;

In particular, no default privileges are granted to PUBLIC for new schemas. It may be confusing that the default schema named "public" starts with ALL privileges for PUBLIC. That's just a convenience feature to ease the start with newly created databases. It does not affect other schemas in any way. You can revoke these privileges in the template database template1, then all newly created databases in this cluster start without them:
\connect template1
REVOKE ALL ON SCHEMA public FROM public;

The privilege TEMP
Since we revoked all privileges on hostdb from PUBLIC, regular users cannot create temporary tables unless we explicitly allow it. You may or may not want to add this:
GRANT TEMP ON DATABASE hostdb TO schma_mgr;

search_path
Don't forget to set the search_path. If you only got the one database in the cluster you can just set the global default in postgresql.conf.
Else (more likely) set it as property of the database, or just for involved roles or even the combination of both. Details:

How does the search_path influence identifier resolution and the “current schema”

You may want to set it to schma, public if you use the public schema as well, or even (less likely) $user, schma, public ...
An alternative would be to use the default schema "public" which should work with default settings for search_path unless you changed that. Remember to revoke privileges for PUBLIC in this case.
Related

Grant privileges for a particular database in PostgreSQL
PostgreSQL - DB user should only be allowed to call functions


It sounds like what you're looking for is an audit.  You can achieve this through the use of SQL Server Audit.  With this tool, you will be able to audit Server and Database level actions and access.

To find out more information about how to use this tailored to your implementation, take a look at this BOL reference on SQL Server Audit.

This is available in Enterprise and Datacenter editions.


MySQL error: Access denied for user 'a'@'localhost' (using password: YES)

Here is a quick-and-dirty method for checking out how MySQL performs successful authentication.

Please run this query:

SELECT USER(),CURRENT_USER();

USER() reports how you attempted to authenticate in mysqld

CURRENT_USER() reports how you were allowed to authenticate by mysqld

Sometimes, USER() and CURRENT_USER() are different. That's because mysql authentication follows a specfic protocol.

According to MySQL 5.0 Certification Study Guide

pages 486,487 state the following on mysql's authentication algorithm:

  There are two stages of client access control:

  In the first stage, a client attempts to connect and the server either
  accepts or rejects the connection. For the attempt to succeed, some
  entry in the user table must match the host from which the client
  connects, the username, and the password.

  In the second stage (which occurs only if a client has already
  connected sucessfully), the server checks every query it receives from
  the client to see whether the client has sufficient privileges to
  execute it.

  The server matches a client against entries in the grant tables based
  on the host from which the client connects and the user the client
  provides. However, it's possible for more than one record to match:

  Host values in grant tables may be specified as patterns contains
  wildcard values. If a grant table contains entries from
  myhost.example.com, %.example.com, %.com, and %, all of them
  match a client who connects from myhost.example.com.

  Patterns are not allowed for the User values in grant table entries,
  but a username may be given as an empty string to specify an anonymous
  user. The empty string matches any username and thus effectively acts
  as a wildcard.

  When the Host and the User values in more than one user table record
  match a client, the server must decide which one to use. It does this
  by sorting records with the most specific Host and User column values
  first, and choosing the matching record that occurs first in the
  sorted list, Sorting take place as follows:

  In the Host Column, literal values such as localhost, 127.0.0.1,
  and myhost.example.com sort ahead of values such as %.example.com
  that have pattern characters in them. Pattern values are sorted
  according to how specific they are. For example, %.example.com is
  more specific than %.com, which is more specific than %.

  In the User column, non-blank usernames sort ahead of blank usernames.
  That is, non-anonymous users sort ahead of anonymous users.

  The server performs this sorting when it starts. It reads the grant
  tables into memory, sorts them, and uses the  in-memory copies for
  access control.

From this description, you do not need to worry about the order of the mysql.user tables since there is an in-memory copy of the grant tables which is sorted as previously mentioned.

With regard to how you logged in, only mysql -u a worked. Go back and login again and run these commands

SELECT USER(),CURRENT_USER();
SELECT user,host,password FROM mysql.user;

Make sure that

every user has a password.
there are no anonymous users (when user is blank)

This is just a guess, but I suspect mysql -u a of connecting via localhost because when the connection protocol is not specified, the default is to connect via the socket file. There may exist an entry in mysql.user that allow anonymous localhost connection.

Run this query:

SELECT user,host,password FROM mysql.user WHERE user='' AND host='localhost';

If you get back a row with no password, that fully explains why mysq -u a works.

UPDATE 2012-01-19 11:12 EDT

Craig Efrein brought up an interesting question: if two identical usernames exist in the mysql.user table, one with a password and one without, does that mean that MySQL denies authentication when not using a password?

This question is an excellent heads up about MySQL user authentication.

Please note that the primary key of mysql.user is host,user. There are no other indexes. This allows multiple occurrences of a username. Each occurrence can have a different password or no password. This allows user 'dbuser' to login locally (dbuser@localhost) using no password and the same user login from another server within a given netblock (dbuser@'10.1.2.20') with a password like 'pass1' and that user to login remotely from anywhere (dbuser@'%') with a remote password like 'pass2'.

Given the authentication algorithm that MySQL uses, there are no restrictions placed on users with the presence or absense of a password.

This is why MySQL 5.0 Certification Study Guide says on Page 498 Paragraph 6 in its bulletpoints brings out how to cleanup the authentication process:

  On Unix, MySQL comes with a mysql_secure_installation script that can
  perform several helpful security-related operations on your
  installation. The script has the following capabilities:

  Set a password for the root accounts
  Remove any remotely accessible root accounts.
  Remove the anonymous user accounts. This improves security because
  it prevents the possibility of anyone connecting to the MySQL server
  as root from a remote host. The results is that anyone who wants to
  connect as root must first be able to log in on the server host, which
  provides an additional barrier against attack.
  Remove the test database (If you remove the anonymous accounts, you
  might also want to remove the test database to which they have
  access).


Multiple one to many relationships between tables

If a task can have multiple assignees, then you may want a many-to-many table between employee and tasks.  However, the current model works well if the task can only be assigned to one employee at a time and you don't want to record assignment history.

It is not uncommon to have multiple one-to-many relationships between two tables.  You have correctly modeled the case you have described.   However, I generally separate created-by and owners relations.  This permits someone other than the owner to enter proposals and tasks.  A created-by fields is typically used as an audit field which should not be modified, while the owner field usually would be modifiable.

You do have a many-to-many relationship between employees and proposals. This resolved by the tasks table.


Create a mysql replication from multiple master and single slave server?

You need to modify the master and relay repositories to be stored into tables and then define channels for each master.

STOP SLAVE;
SET GLOBAL master_info_repository = 'TABLE';
SET GLOBAL relay_log_info_repository = 'TABLE';

CHANGE MASTER TO MASTER_HOST='master1', MASTER_USER='rpl', MASTER_PORT=3451, MASTER_PASSWORD='' \
MASTER_LOG_FILE='master1-bin.000006', MASTER_LOG_POS=628 FOR CHANNEL 'master-1';

Refer: https://dev.mysql.com/doc/refman/5.7/en/replication-multi-source.html


PostgreSQL not using index during count(*)

If I interpret your added information correctly, you have 21 distinct  source_id with roughly 1 million rows each (divided pretty evenly among each source_id). That means your query counts roughly 3/4 of the whole table. An index typically can't buy much either way.

Test with EXPLAIN (just EXPLAIN is enough for this purpose) to see the estimated cost for each variant:

EXPLAIN SELECT .... -- your query

And take note of all cost= numbers in the output.

Then (only in your session):

SET enable_seqscan = off;

And repeat the procedure. Once more after:

SET enable_indexscan = off;

This should tell you why Postgres chooses a sequential scan. Typically, if your index is substantially smaller than the table (index entries substantially smaller than table rows and / or substantially fewer rows) and if conditions for an index-only scan are met, Postgres will choose that route - unless your cost settings are seriously out of touch with the reality of your setup.

Most importantly, the visibility map has to show that whole pages are visible to all transactions. VACUUM updates the visibility map after writes. Read details on the linked Wiki page.

You can try your query once more immediately after running VACUUM ANALYZE customer;.

If index-only scans are not possible, a sequential scan will most probably be faster than a bitmap index scan, meaning that your index would be of no use.

Update for Postgres 9.6

There was an important restriction for partial indexes, improved in Postgres 9.6. The release notes:

  Allow use of an index-only scan on a partial index when the index's WHERE clause references columns that are not indexed (Tomas
  Vondra, Kyotaro Horiguchi)

  For example, an index defined by CREATE INDEX tidx_partial ON t(b) WHERE a > 0 can now be used for an index-only scan by a query that
  specifies WHERE a > 0 and does not otherwise use a. Previously
  this was disallowed because a is not listed as an index column.


Composite Primary Key on partitioned tables, and Foreign Keys

ALL unique constraints involved in a partitioning scenario MUST have the partition column defined as part of the base index definition (subset of the index key) - (not merely an included column). In other words, you will not be able to create a unique constraint on just the ID column in this scenario.

Set up a partition function and scheme.

CREATE PARTITION FUNCTION [PF_dbo_Test] (DATE) AS RANGE RIGHT
FOR VALUES ('2018-01-01','2018-02-01','2081-03-01')
GO

CREATE PARTITION SCHEME [PS_dbo_Test] AS PARTITION [PF_dbo_test]
TO ([PRIMARY],[PRIMARY],[PRIMARY],[PRIMARY])

Create a table with a PRIMARY KEY that references the partition scheme.

IF OBJECT_ID('[dbo].[Test]') IS NOT NULL
    DROP TABLE [dbo].[Test]
GO

CREATE TABLE [dbo].[Test] (
    [TransactionDate] DATE
    ,[ID] INT CONSTRAINT [PK_Test] PRIMARY KEY CLUSTERED ([TransactionDate] ASC)
    ) ON PS_dbo_Test (TransactionDate)

Now, try to create a unique constraint on the partitioned table without specifying the partition column.

CREATE UNIQUE NONCLUSTERED INDEX [UX_Test] ON [dbo].[Test] ([ID] ASC)
    WITH (
            PAD_INDEX = OFF
            ,STATISTICS_NORECOMPUTE = OFF
            ,SORT_IN_TEMPDB = OFF
            ,IGNORE_DUP_KEY = OFF
            ,DROP_EXISTING = OFF
            ,ONLINE = OFF
            ,ALLOW_ROW_LOCKS = ON
            ,ALLOW_PAGE_LOCKS = ON
            )

  Msg 1908, Level 16, State 1, Line 17 Column 'TransactionDate' is
  partitioning column of the index 'UX_Test'. Partition columns for a
  unique index must be a subset of the index key.

So, when you ask the question:

  I cannot decide whether to use composite FKs, referencing both columns
  of the PK, or single column FKs, referencing the id column supported
  by a unique index.

I'm afraid SQL Server has decided for you. You will have to define your FK's with both TransactionDate AND ID.


Schema design for products with multiple variants/attributes?

This is just the info from @lesandru reply, I really find it very useful, so credits to him and @sahalMoidu

Applying normalization to your problem the solution is as given. Run and see it on Fiddle

Fiddle

CREATE TABLE products
    (
     product_id int auto_increment primary key,
     name varchar(20),
     description varchar(30)

    );

INSERT INTO products
(name, description)
VALUES
('Rug', 'A cool rug'  ),
('Cup', 'A coffee cup');

create table variants (variant_id int auto_increment primary key,
                       variant varchar(50)
                       );
insert into variants (variant)
values ('color'),('material'),('size') ;
create table variant_value(value_id int auto_increment primary key,
                           variant_id int ,
                           value varchar(50)
                           );

insert into variant_value (variant_id,value)
values (1 ,'red'),(1 ,'blue'),(1 ,'green'),
        (2 ,'wool'),(2 ,'polyester'),
        (3 ,'small'),(3 ,'medium'),(3 ,'large');

create table product_Variants( product_Variants_id int  auto_increment primary key,
                            product_id int,
                            productVariantName varchar(50),
                            sku varchar(50),
                            price float
                            );

create table product_details(product_detail_id int auto_increment primary key,
                             product_Variants_id int,

                             value_id int
                             );

insert into product_Variants(product_id,productVariantName,sku,price)
values (1,'red-wool' ,'a121',50);

insert into product_details(product_Variants_id , value_id)
values( 1,1),(1,4);

insert into product_Variants(product_id,productVariantName,sku,price)
values (1,'red-polyester' ,'a122',50);

insert into product_details(product_Variants_id , value_id)
values( 2,1),(2,5);


How to find if difference between all row data for a specific columns are same

validate the pattern that each data row has a difference of 0.50

For example, look at

WITH cte AS ( SELECT Column1 - COALESCE(LEAD(Column1) OVER (ORDER BY Column1), 0) delta
              FROM table )
SELECT delta, COUNT(delta)
FROM cte
GROUP BY delta


Why does my heap not fill its pages completely while an identical clustered index can fill to m_freeCnt = 0?

'page split' and 'heap' should not be used in the same sentence. Do you mean why wasn't the row of size 393+change fit in the free space of the first page of the heap? That is because the PFS free space info has not high enough resolution, see Managing Extent Allocations and Free Space:

  The PFS has one byte for each page, recording whether the page is
  allocated, and if so, whether it is empty, 1 to 50 percent full, 51 to
  80 percent full, 81 to 95 percent full, or 96 to 100 percent full.

Would fit in page, indeed, but the PFS only says that the page is '95_PCT_FULL' and the record inserted is fairly big and close to the free space as-per-PFS (95% of 8060 is 403), so the INSERT chooses to allocate a new page.

With a B-Tree the record position is deterministic due to key order, so the engine must load the page and check the free space, and the record will fit.


SQL query to display names of customers who have purchased all the DVD

You can try something like:

select c.Customer_Name
from Customer c
join (  select Customer_No
        from DVD_Purchase
        group by Customer_No
        having count(distinct DVD_No) = (select count(*) from DVD)
        ) d on c.Customer_No = d.Customer_No

Explaination: first we need to know the number of DVDs available.

select count(*)
from DVD

Then we need to get the number of distinct DVDs purchased by the customers, and to compare it with the total we got previously.

select Customer_No
from DVD_Purchase
group by Customer_No
having count(distinct DVD_No) = <number of DVDs available>

And finally, we just need to join with customers table to retrieve the names.


Why have SQL Server services disappeared from services.msc?

Can't explain why this cured my problem, but while using services.msc on the computer that wasn't showing the SQl Server services, I clicked on Action->Connect To Another Computer. I connected to the other DB server (where SQl Server services appear ok on its services.msc) and then I connected back to  he local computer (DB01). The SQL Server services now appear as expected


Why do MySQL tables crash? How do I prevent it?

It is rather easy for a MyISAM table to crash.

In the header of every MyISAM table is a counter that tracks how many open file handles there are against the table.

If you start up mysql and the number in the header does not match the number of actual file handles against, mysqld treates the table as crashed.

If a simple REPAIR TABLE mdl_user makes it work again every time without loss of data, this may indicate that you have a very highly trafficked site that writes to mdl_user.

If dozens of tables require this REPAIR TABLE, I would convert all the tables to InnoDB. However, if the mdl_user table is the only table with this problem, there is something you can do (for this example, let say the database is moodle);

If you want all tables left as MyISAM

STEP 01 : Create a Repair Table Script

echo "REPAIR TABLE moodle.mdl_user;" > /var/lib/mysql/MoodleStartUp.sql

STEP 02 : Declare the Repair Script as the startup file

Add this to /etc/my.cnf

[mysqld]
init-file=/var/lib/mysql/MoodleStartUp.sql

STEP 03 : Restart mysql

Every restart of mysql will trigger the Repair Table Script

If you want all tables to become InnoDB

Run this code to make a bulk conversion script of MyISAM tables to InnoDB, and view it

MYSQL_USER=root
MYSQL_PASS=password
MYSQL_CONN="-u${MYSQL_USER} -p ${MYSQL_PASS}"
echo "SET SQL_LOG_BIN = 0;" > /root/ConvertMyISAMToInnoDB.sql
mysql ${MYSQL_CONN} -A --skip-column-names -e"SELECT CONCAT('ALTER TABLE ',table_schema,'.',table_name,' ENGINE=InnoDB;') InnoDBConversionSQL FROM information_schema.tables WHERE engine='MyISAM' AND table_schema NOT IN ('information_schema','mysql','performance_schema') ORDER BY (data_length+index_length)" > /root/ConvertMyISAMToInnoDB.sql
less /root/ConvertMyISAMToInnoDB.sql

Once you are satisfied with the conversion script's contents, then run it

mysql ${MYSQL_CONN} < /root/ConvertMyISAMToInnoDB.sql

UPDATE 2012-03-15 14:00 EDT

@Kevin, While using MyISAM, what if running out of Disk Space is your isuue?

Here is something to consider: According to MySQL 5.0 Certification Study Guide,

bulletpoint #11 says the following on Pages 408,409 Section 29.2:

  If you run out of disk space while adding rows to a MyISAM table, no
  error occurs. The server suspends the operation until space becomes
  available, and then completes the operation.

When you run out of disk space, do not just shutdown or kill mysql. The count of the open file handles in any currently used MyISAM will not have been cleared. Thus, the MyISAM table is marked crashed. If you can free up disk space in the data volume with mysqld still running, mysqld will soldier on once disk space is made available.


MySQL Cluster SQL Node not synchronizing

There can be issues regarding the syncing of the FRMs stored centrally in the data nodes and the local copies on each MySQL Server. The workaround is to stop the mysqld; delete the FRM files for Cluster tables (and DB in this case) from the mysqld's datadir; start mysqld and then run SHOW TABLES.


How to calculate the max number of worker threads in SQL Server?

It seems someone has changed the maximum worker threads setting to a non-default value, which will override the default calculated value. The current configured value can be determined with:

EXEC sp_configure 'max worker threads';

I'll add the value is sometimes set mistakenly as a knee-jerk reaction to address symptoms rather than the underlying cause. Some valid reasons for configuring the value are called out in the documentation but the default calculation is appropriate for most workloads.


Load database from Solaris (Sybase 15.0.3) to VMWare Solaris (Sybase 15.7.0)

Recently I had a similar problem …

Here goes all the required steps with comments … !

  [On Source]

  ============================================================================

  1) Checks on Source

  a.  Run (on solaris prompt) On source machine

  Execute:

uname -a

  In my case the output was:

SunOS clusterz1 5.10 Generic_142900-03 sun4u sparc SUNW,SPARC-Enterprise*

  b.  On source Sybase

  Execute:

select @@version
go

  In my case the output was:

Adaptive Server Enterprise/15.0.3/EBF 16736 ESD#2/P/Sun_svr4/OS 5.8
/ase1503/2707/64-bit/FBO/Sun Jul 26 10:29:50 2009

  2) Checks on Target

  a.   Run (on solaris prompt) On target machine

  Execute:

uname –a

  In my case the output was:

SunOS s_wcli0070 5.10 Generic_142910-17 i86pc i386 i86pc

  b.  On Target Sybase

  Execute:

select @@version
go

  In my case the output was:

Adaptive Server Enterprise/15.7.0/EBF 19499 SMP /P/Solaris AMD64/OS 5.10
/ase157/2820/64-bit/FBO/Fri Sep 16 01:47:15 2011

  3)  Operations on Source

  a.   Set requested Database to “single”  user
  mode

  Execute:

sp_dboption Audit, "single", true go

  You should get something similar to:

Database option 'single user' turned ON for database 'Audit'.
Running CHECKPOINT on database 'Audit' for option 'single user' to take effect.
Execution time: 0,437 seconds

  b.  Position yourself on the requested database

  Execute:

use Audit
go

  You should get something similar to:

Execution time: 0,016 seconds

  c.  Flush Statistics

  Execute:

sp_flushstats
go

  You should get something similar to:

DBCC execution completed. If DBCC printed error messages, contact a
user with System Administrator (SA) role. DBCC execution completed. If
DBCC printed error messages, contact a user with System Administrator
(SA) role. Execution time: 0,094 seconds

  For more details check the following URL:

  http://infocenter.sybase.com/help/index.jsp?topic=/com.sybase.help.ase_15.0.sprocs/html/sprocs/sprocs109.htm

  d.  Lets write all dirty pages …

  Execute:

checkpoint
go

  You should get something similar to:

Execution time: 0,031 seconds

  For more details check the following URL:

  http://infocenter.sybase.com/help/index.jsp?topic=/com.sybase.help.ase_15.0.commands/html/commands/commands10.htm

  e.  Lets suspend updates on the requested database

quiesce database qaudit hold Audit
go

  You should get something similar to:

Execution time: 0,016 seconds

  f.  Lets resume updates on the requested database

  Execute:

quiesce database qaudit release
go

  You should get something similar to:

Execution time: 0,016 seconds

  For more details check the following URL:

  http://infocenter.sybase.com/help/index.jsp?topic=/com.sybase.help.ase_15.0.commands/html/commands/commands73.htm

  Note: If you don't do the commands in the right sequence you can get
  the following error when trying to load the database dump:

load database Audit from "compress::9::/dumps/PRODUCTION.Audit_zdump"
go

Adaptive Server cannot load this database because the database that
was dumped was not quiescent when the dump was performed. Run
sp_flushstats before DUMP DATABASE and ensure that the database is not
updated during the dump.

Sybase error code=3151 Severity Level=16, State=2, Transaction State=0
Line 1"

  g.  Lets perform the compressed dump of the requested database

  Execute:

dump database Audit to
"compress::9::/DATADUMPS/daily/PRODUCTION.Audit_zdump"
go

  You should get something similar to:

Backup Server session id is:  39.  Use this value when executing the
'sp_volchanged' system stored procedure after fulfilling any volume
change request from the Backup Server. Backup Server: 4.132.1.1:
Attempting to open byte stream device:
'compress::9::/DATADUMPS/daily/PRODUCTION.Audit_zdump::00' Backup
Server: 6.28.1.1: Dumpfile name 'Audit12139079A9  ' section number 1
mounted on byte stream
'compress::9::/DATADUMPS/daily/PRODUCTION.Audit_zdump::00' Backup
Server: 4.188.1.1: Database Audit: 3456 kilobytes (2%) DUMPED. Backup
Server: 4.188.1.1: Database Audit: 8918 kilobytes (9%) DUMPED. Backup
Server: 4.188.1.1: Database Audit: 9090 kilobytes (18%) DUMPED. Backup
Server: 4.188.1.1: Database Audit: 9348 kilobytes (26%) DUMPED. Backup
Server: 4.188.1.1: Database Audit: 9520 kilobytes (34%) DUMPED. Backup
Server: 4.188.1.1: Database Audit: 9690 kilobytes (43%) DUMPED. Backup
Server: 4.188.1.1: Database Audit: 9862 kilobytes (51%) DUMPED. Backup
Server: 4.188.1.1: Database Audit: 10032 kilobytes (60%) DUMPED.
Backup Server: 4.188.1.1: Database Audit: 10204 kilobytes (68%)
DUMPED. Backup Server: 4.188.1.1: Database Audit: 13422 kilobytes
(76%) DUMPED. Backup Server: 4.188.1.1: Database Audit: 13594
kilobytes (85%) DUMPED. Backup Server: 4.188.1.1: Database Audit:
13764 kilobytes (93%) DUMPED. Backup Server: 4.188.1.1: Database
Audit: 13916 kilobytes (100%) DUMPED. Backup Server: 3.43.1.1: Dump
phase number 1 completed. Backup Server: 3.43.1.1: Dump phase number 2
completed. Backup Server: 3.43.1.1: Dump phase number 3 completed.
Backup Server: 4.188.1.1: Database Audit: 13932 kilobytes (100%)
DUMPED. Backup Server: 3.42.1.1: DUMP is complete (database Audit).
Execution time: 12,078 seconds

  For more details check the following URL:

  http://infocenter.sybase.com/help/index.jsp?topic=/com.sybase.help.ase_15.0.commands/html/commands/commands54.htm

  h.  Lets take out the single user mode on the requested database …

  Execute:

use master
go

sp_dboption Audit, "single", false
go

  You should get something similar to:

Database option 'single user' turned OFF for database 'Audit'. Running
CHECKPOINT on database 'Audit' for option 'single user' to take
effect. Execution time: 0,015 seconds Execution time: 0,047 seconds

  [On Target]

  4)  Operations on target

  a.  Load compressed dump

  Execute (on Sybase):

load database Audit from "compress::9::/dumps/PRODUCTION.Audit_zdump"
go

  You should get something similar to:

Backup Server session id is: 10. Use this value when executing the
'sp_volchanged' system stored procedure after fulfilling any volume
change request from the Backup Server. Backup Server: 4.132.1.1:
Attempting to open byte stream device:
'compress::9::/dumps/PRODUCTION.Audit_zdump::000' Backup Server:
6.28.1.1: Dumpfile name 'Audit12139081CC  ' section number 1 mounted on byte stream 'compress::9::/dumps/PRODUCTION.Audit_zdump::000'
Backup Server: 4.188.1.1: Database Audit: 8836 kilobytes (1%) LOADED.
Backup Server: 4.188.1.1: Database Audit: 44038 kilobytes (8%) LOADED.
Backup Server: 4.188.1.1: Database Audit: 87050 kilobytes (17%)LOADED.
Backup Server: 4.188.1.1: Database Audit: 130058 kilobytes (25%) LOADED.
Backup Server: 4.188.1.1: Database Audit: 173070 kilobytes (33%) LOADED.
Backup Server: 4.188.1.1: Database Audit: 216590 kilobytes (42%) LOADED.
Backup Server: 4.188.1.1: Database Audit: 259602 kilobytes (50%) LOADED.
Backup Server: 4.188.1.1: Database Audit: 303122 kilobytes (59%) LOADED.
Backup Server: 4.188.1.1: Database Audit: 346134 kilobytes (67%) LOADED.
Backup Server: 4.188.1.1: Database Audit: 386582 kilobytes (75%) LOADED.
Backup Server: 4.188.1.1: Database Audit: 429594 kilobytes (83%) LOADED.
Backup Server: 4.188.1.1: Database Audit: 473114 kilobytes (92%) LOADED.
Backup Server: 4.188.1.1: Database Audit: 512028 kilobytes (100%) LOADED.
Backup Server: 4.188.1.1: Database Audit: 512036 kilobytes (100%) LOADED.
Backup Server: 3.42.1.1: LOAD is complete (database Audit).
Started cross-platform conversion for database Audit.
Started cross-platform conversion for system objects.
Cross-platform conversion for database Audit: 1136 pages completed.
Completed cross-platform conversion for system objects.
Started cross-platform conversion for user objects.
Cross-platform conversion for database Audit: 10049 pages completed.
Cross-platform conversion for database Audit: 20249 pages completed.
Cross-platform conversion for database Audit: 30449 pages completed.
Cross-platform conversion for database Audit: 40649 pages completed.
Cross-platform conversion for database Audit: 50849 pages completed.
Cross-platform conversion for database Audit: 61049 pages completed.
Cross-platform conversion for database Audit: 71249 pages completed.
Cross-platform conversion for database Audit: 81449 pages completed.
Cross-platform conversion for database Audit: 91649 pages completed.
Cross-platform conversion for database Audit: 101849 pages completed.
Cross-platform conversion for database Audit: 112049 pages completed.
Cross-platform conversion for database Audit: 122249 pages completed.
Cross-platform conversion for database Audit: 132449 pages completed.
Cross-platform conversion for database Audit: 142649 pages completed.
Cross-platform conversion for database Audit: 152849 pages completed.
Cross-platform conversion for database Audit: 163049 pages completed.
Cross-platform conversion for database Audit: 173249 pages completed.
Cross-platform conversion for database Audit: 183449 pages completed.
Cross-platform conversion for database Audit: 193649 pages completed.
Cross-platform conversion for database Audit: 203849 pages completed.
Cross-platform conversion for database Audit: 214049 pages completed.
Cross-platform conversion for database Audit: 224249 pages completed.
Cross-platform conversion for database Audit: 234449 pages completed.
Cross-platform conversion for database Audit: 244649 pages completed.
Cross-platform conversion for database Audit: 254849 pages completed.
Completed cross-platform  conversion for user objects.
Started cross-platform conversion for log records.
Cross-platform conversion for database Audit: 35 pages completed.
Completed cross-platform conversion for log records.
Completed cross-platform conversion for database Audit.
Started estimating recovery log boundaries for database 'Audit'.
Database 'Audit', checkpoint=(179295, 3), first=(179295, 3), last=(179295, 6).
Completed estimating recovery log boundaries for database 'Audit'.
Started ANALYSIS pass for database 'Audit'.
Completed ANALYSIS pass for database 'Audit'.
Started REDO pass for database 'Audit'.
The total number of log records to process is 4.
Redo pass of recovery has processed 1 committed and 0 aborted transactions.
Completed REDO pass for database 'Audit'.
Use the ONLINE DATABASE command to bring this database online;
ASE will not bring it online automatically.
Execution time: 57,64 seconds

  b.  Just put the requested database online

  Execute (on Sybase):

online database Audit go

  You should get something similar to:

Started estimating recovery log boundaries for database 'Audit'.
Database 'Audit', checkpoint=(179295, 3), first=(179295, 3), last=(179295, 6).
Completed estimating recovery log boundaries for database 'Audit'.
Started ANALYSIS pass for database 'Audit'.
Completed ANALYSIS pass for database 'Audit'.
Recovery of database 'Audit' will undo incomplete nested top actions.
Database 'Audit' appears to be at an older version '15030' than the present installation at version '15700'; ASE will assess it, and upgrade it as required.
Database 'Audit': beginning upgrade step [ID  1564]: executing SQL statement ("declare @dbid int select @dbid = db...")
Database 'Audit': beginning upgrade step [ID     2]: validate basic system type data
Database 'Audit': beginning upgrade step [ID     6]: alter table (table syspartitions)
Database 'Audit': beginning upgrade step [ID     9]: executing SQL statement ("if exists (select 1 from syscolumns...")
Database 'Audit': beginning upgrade step [ID    10]: alter table (table sysobjects)
Database 'Audit': beginning upgrade step [ID    11]: alter table (table syscolumns)
Database 'Audit': beginning upgrade step [ID    69]: executing SQL statement ("if 2 = (select count(name) from sys...")
Database 'Audit': beginning upgrade step [ID    27]: alter table (table syscomments)
Database 'Audit': beginning upgrade step [ID    80]: alter table (table sysattributes)
Database 'Audit': beginning upgrade step [ID    91]: executing SQL statement ("update systypes set length=@@maxpag...")
Database 'Audit': beginning upgrade step [ID   245]: Update system roles in system tables.
Database 'Audit': beginning upgrade step [ID   246]: clear unused columns (table sysindexes)
Database 'Audit': beginning upgrade step [ID   248]: update system catalog protection data
Database 'Audit': beginning upgrade step [ID  1105]: noting the present database upgrade level
Database 'Audit': beginning upgrade step [ID  1132]: noting the present database upgrade level
Database 'Audit': beginning upgrade step [ID  1299]: noting the present database upgrade level
Database 'Audit': beginning upgrade step [ID  1400]: noting the present database upgrade level
Database 'Audit': beginning upgrade step [ID  1502]: executing SQL statement ("if exists (select 1 from sysobjects...")
Database 'Audit': beginning upgrade step [ID  1503]: executing SQL statement ("create view sysquerymetrics (uid, g...")
Database 'Audit': beginning upgrade step [ID    73]: executing SQL statement ("update sysobjects set sysstat = sys...")
Database 'Audit': beginning upgrade step [ID  1550]: noting the present database upgrade level
Database 'Audit': beginning upgrade step [ID  1702]: noting the present database upgrade level
Database 'Audit': beginning upgrade step [ID  1719]: executing SQL statement ("update sysattributes set object_inf...")
Database 'Audit': beginning upgrade step [ID  1720]: noting the present database upgrade level
Upgrade has finished in database 'Audit'. ASE is now updating table level statistics for its system tables. Please be patient.
Checking table 'sysstatistics' (object ID 24): Logical page size is 2048 bytes.

Checking partition 'sysstatistics_24' (partition ID 24) of table 'sysstatistics'. The logical page size of this table is 2048 bytes.

The indexes for 'sysstatistics' are already correct.  They will not be rebuilt.
Checking table 'sysobjects' (object ID 1): Logical page size is 2048 bytes.

Checking partition 'sysobjects_1' (partition ID 1) of table 'sysobjects'. The logical page size of this table is 2048 bytes.

The indexes for 'sysobjects' are already correct.  They will not be rebuilt.
Checking table 'sysindexes' (object ID 2): Logical page size is 2048 bytes.

Checking partition 'sysindexes_2' (partition ID 2) of table 'sysindexes'. The logical page size of this table is 2048 bytes.

The indexes for 'sysindexes' are already correct.  They will not be rebuilt.
Checking table 'syscolumns' (object ID 3): Logical page size is 2048 bytes.

Checking partition 'syscolumns_3' (partition ID 3) of table 'syscolumns'. The logical page size of this table is 2048 bytes.

The indexes for 'syscolumns' are already correct.  They will not be rebuilt.
Checking table 'systypes' (object ID 4): Logical page size is 2048 bytes.

Checking partition 'systypes_4' (partition ID 4) of table 'systypes'. The logical page size of this table is 2048 bytes.

The indexes for 'systypes' are already correct.  They will not be rebuilt.
Checking table 'sysprocedures' (object ID 5): Logical page size is 2048 bytes.

Checking partition 'sysprocedures_5' (partition ID 5) of table 'sysprocedures'. The logical page size of this table is 2048 bytes.

The indexes for 'sysprocedures' are already correct.  They will not be rebuilt.
Checking table 'syscomments' (object ID 6): Logical page size is 2048 bytes.

Checking partition 'syscomments_6' (partition ID 6) of table 'syscomments'. The logical page size of this table is 2048 bytes.

The indexes for 'syscomments' are already correct.  They will not be rebuilt.
Checking table 'syssegments' (object ID 7): Logical page size is 2048 bytes.

Checking partition 'syssegments_7' (partition ID 7) of table 'syssegments'. The logical page size of this table is 2048 bytes.

The table 'syssegments' has no indexes.
Checking table 'syslogs' (object ID 8): Logical page size is 2048 bytes.

Checking partition 'syslogs_8' (partition ID 8) of table 'syslogs'. The logical page size of this table is 2048 bytes.

*** NOTICE: Space used on the log segment is 401 pages (0.78 MB), 0.39%.
*** NOTICE: Space reserved on the log segment is 0 pages (0.00 MB), 0.00%.
*** NOTICE: Space free on the log segment is 101999 pages (199.22 MB), 99.61%.
The table 'syslogs' has no indexes.
Checking table 'sysprotects' (object ID 9): Logical page size is 2048 bytes.

Checking partition 'sysprotects_9' (partition ID 9) of table 'sysprotects'. The logical page size of this table is 2048 bytes.

The indexes for 'sysprotects' are already correct.  They will not be rebuilt.
Checking table 'sysusers' (object ID 10): Logical page size is 2048 bytes.

Checking partition 'sysusers_10' (partition ID 10) of table 'sysusers'. The logical page size of this table is 2048 bytes.

The indexes for 'sysusers' are already correct.  They will not be rebuilt.
Checking table 'sysalternates' (object ID 11): Logical page size is 2048 bytes.

Checking partition 'sysalternates_11' (partition ID 11) of table 'sysalternates'. The logical page size of this table is 2048 bytes.

The indexes for 'sysalternates' are already correct.  They will not be rebuilt.
Checking table 'sysdepends' (object ID 12): Logical page size is 2048 bytes.

Checking partition 'sysdepends_12' (partition ID 12) of table 'sysdepends'. The logical page size of this table is 2048 bytes.

The indexes for 'sysdepends' are already correct.  They will not be rebuilt.
Checking table 'syskeys' (object ID 13): Logical page size is 2048 bytes.

Checking partition 'syskeys_13' (partition ID 13) of table 'syskeys'. The logical page size of this table is 2048 bytes.

The indexes for 'syskeys' are already correct.  They will not be rebuilt.
Checking table 'sysgams' (object ID 14): Logical page size is 2048 bytes.
The total number of data pages in this table is 8.
The indexes for 'sysgams' are already correct.  They will not be rebuilt.
Checking table 'sysusermessages' (object ID 15): Logical page size is 2048 bytes.

Checking partition 'csysusermessages_15' (partition ID 15) of table 'sysusermessages'. The logical page size of this table is 2048 bytes.

The indexes for 'sysusermessages' are already correct.  They will not be rebuilt.
Checking table 'sysreferences' (object ID 16): Logical page size is 2048 bytes.

Checking partition 'sysreferences_16' (partition ID 16) of table 'sysreferences'. The logical page size of this table is 2048 bytes.

The indexes for 'sysreferences' are already correct.  They will not be rebuilt.
Checking table 'sysconstraints' (object ID 17): Logical page size is 2048 bytes.

Checking partition 'sysconstraints_17' (partition ID 17) of table 'sysconstraints'. The logical page size of this table is 2048 bytes.

The indexes for 'sysconstraints' are already correct.  They will not be rebuilt.
Checking table 'systhresholds' (object ID 18): Logical page size is 2048 bytes.

Checking partition 'systhresholds_18' (partition ID 18) of table 'systhresholds'. The logical page size of this table is 2048 bytes.

The table 'systhresholds' has no indexes.
Checking table 'sysroles' (object ID 19): Logical page size is 2048 bytes.

Checking partition 'sysroles_19' (partition ID 19) of table 'sysroles'. The logical page size of this table is 2048 bytes.

The indexes for 'sysroles' are already correct.  They will not be rebuilt.
Checking table 'sysattributes' (object ID 21): Logical page size is 2048 bytes.

Checking partition 'sysattributes_21' (partition ID 21) of table 'sysattributes'. The logical page size of this table is 2048 bytes.

The indexes for 'sysattributes' are already correct.  They will not be rebuilt.
Checking table 'sysslices' (object ID 22): Logical page size is 2048 bytes.

Checking partition 'csysslices_22' (partition ID 22) of table 'sysslices'. The logical page size of this table is 2048 bytes.

The indexes for 'sysslices' are already correct.  They will not be rebuilt.
Checking table 'systabstats' (object ID 23): Logical page size is 2048 bytes.

Checking partition 'systabstats_23' (partition ID 23) of table 'systabstats'. The logical page size of this table is 2048 bytes.

The indexes for 'systabstats' are already correct.  They will not be rebuilt.
Checking table 'sysstatistics' (object ID 24): Logical page size is 2048 bytes.

Checking partition 'sysstatistics_24' (partition ID 24) of table 'sysstatistics'. The logical page size of this table is 2048 bytes.

The indexes for 'sysstatistics' are already correct.  They will not be rebuilt.
Checking table 'sysxtypes' (object ID 25): Logical page size is 2048 bytes.

Checking partition 'sysxtypes_25' (partition ID 25) of table 'sysxtypes'. The logical page size of this table is 2048 bytes.

The indexes for 'sysxtypes' are already correct.  They will not be rebuilt.
Checking table 'sysjars' (object ID 26): Logical page size is 2048 bytes.

Checking partition 'sysjars_26' (partition ID 26) of table 'sysjars'. The logical page size of this table is 2048 bytes.

The indexes for 'sysjars' are already correct.  They will not be rebuilt.
Checking table 'sysqueryplans' (object ID 27): Logical page size is 2048 bytes.

Checking partition 'sysqueryplans_27' (partition ID 27) of table 'sysqueryplans'. The logical page size of this table is 2048 bytes.

The indexes for 'sysqueryplans' are already correct.  They will not be rebuilt.
Checking table 'syspartitions' (object ID 28): Logical page size is 2048 bytes.

Checking partition 'syspartitions_28' (partition ID 28) of table 'syspartitions'. The logical page size of this table is 2048 bytes.

The indexes for 'syspartitions' are already correct.  They will not be rebuilt.
Checking table 'syspartitionkeys' (object ID 29): Logical page size is 2048 bytes.

Checking partition 'syspartitionkeys_29' (partition ID 29) of table 'syspartitionkeys'. The logical page size of this table is 2048 bytes.

The indexes for 'syspartitionkeys' are already correct.  They will not be rebuilt.
Checking table 'sysencryptkeys' (object ID 98): Logical page size is 2048 bytes.

Checking partition 'sysencryptkeys_98' (partition ID 98) of table 'sysencryptkeys'. The logical page size of this table is 2048 bytes.

The indexes for 'sysencryptkeys' are already correct.  They will not be rebuilt.
WARNING: One or more indexes on user tables may have been marked as 'suspect' making these indexes unusable. Use the sp_post_xpload stored procedure to check and rebuild these indexes.
Database 'Audit' is now online.
Execution time: 6,516 seconds

  */


What's causing this ERROR 2000 (HY000): Unknown MySQL error

Well I figured it out, and it isn't pretty. I guess I'll have to file a bug with MySQL.

The server is compiled with OpenSSL.
The working client is compiled with OpenSSL.
The client that can't connect is compiled with yaSSL.

The user I was trying to connect with used the sha256_password authentication plugin. When I dropped and re-created the user with mysql_native_password instead, the problem went away and I can connect from all clients. It appears that the sha256_password plugin doesn't work if the server and client are compiled against different SSL providers. (It might not even work with yaSSL at all.)


Append elements to array only if the element does't exist

It's not super efficient, but you can define something like your solution as a function:

create or replace function set_append(anyarray, anyelement)
  returns anyarray
  immutable
  PARALLEL safe
  language sql as
$$
  select case when $2=any($1) then $1 else $1 || $2 end;
$$;

You can also use that to define an aggregate:

CREATE AGGREGATE set_agg( ANYELEMENT ) (
  SFUNC = set_append,
  STYPE = ANYARRAY,
  INITCOND = '{}');

This will let you select set_agg(x order by y) to control the order of the distinct elements where you can't do select array_agg(distinct x order by y).


Get rows where lastLoginTimestamp is in last 7 days

The values seem to be Unix timestamps, stored as strings. You can use the special datetime functions that MySQL has for conversions:

SELECT *
FROM representative
WHERE DATE_ADD(FROM_UNIXTIME(lastLoginTimestamp), INTERVAL 7 DAY) >= NOW() ;

but it won't be very good for efficiency. Much more efficient, if there is an index on the column, to do it the other way around:

(we assume that the values in the columns are CHAR(10). If some values have smaller length, you'll get erroneous results.)

SELECT *
FROM representative
WHERE lastLoginTimestamp
      >= CAST(UNIX_TIMESTAMP(NOW() - INTERVAL 7 DAY) AS CHAR(10));

The above condition if you want all rows, starting from exactly 7 days ago, same time as now. If you want rows starting from 6 days ago at midnight, so previous 6 days + today (whatever that is, a few minutes or 23 hours, 59 mins):

SELECT *
FROM representative
WHERE lastLoginTimestamp
      >= CAST(UNIX_TIMESTAMP(CURRENT_DATE - INTERVAL 6 DAY) AS CHAR(10));


Cumulative array aggregation in Postgres

You want to use window functions.

select data, array_agg(unnest) over (partition by data order by ordinality)
 from records, unnest(path) with ordinality

In this case, the default "framing" does exactly what you want: from the beginning to the current row; so you don't have to include a framing clause.


Using SQL Server for small file storage is usually a bad idea for different reasons.

SQL Server costs money. A lot of money. Even if everything works well, you're paying an extra for what a simple file server (if done right) would do for "free".
SQL Server is transactional, and ACID compliancy has it's overhead. It's much slower than writing to disk without a transaction. NTFS has partial write protection, but the overhead is much smaller than with SQL Server.
Writing to a filesystem is straightforward, but to access SQL Server you need to go through a driver, network/shared memory etc. Everything you do to access the files now needs the database in the middle.
It's not meant to be a file store. Eventually, what you get is a huge .mdf file on the disk which you can't do much with. Want to free up space on disk after deletion? You need to shrink the database which can take ages.

There are multiple other reasons.
Perhaps another way to put it - SQL Server is not meant for this kind of thing, and if you start using it for files it's going to grow very fast. Now, managing a small database is one thing, and a huge one is another (number of rows not relevant for this discussion, only data size) - and you don't want to go into that area if you don't have to.


Why is my biggest segment resulting in an empty table?

You might have empty blocks below the high water mark (HWM). Delete statement, by default, doesn't lower the HWM; however, TRUNCATE, CTAS to name few do (down to MINEXTENTS on the table). Thus the empty blocks are considered as used blocks.

Simple Demo:

SQL> create table t1(col1 varchar2(100));

Table created.

SQL> select bytes from user_segments where segment_name='T1';

     BYTES
----------
     65536

SQL> insert into t1 select 'sample text of line'||level from dual connect by level<=2000;

2000 rows created.

SQL> commit;

Commit complete.

SQL> select bytes from user_segments where segment_name='T1';

     BYTES
----------
    131072

SQL> delete from t1;

2000 rows deleted.

SQL> commit;

Commit complete.

SQL> select bytes from user_segments where segment_name='T1';

     BYTES
----------
    131072

You see, the size of the table is still same though the table is empty.

SQL> truncate table t1;

Table truncated.

SQL> select bytes from user_segments where segment_name='T1';

     BYTES
----------
     65536


When is Service Master Key Exactly generated?

... but there is another non official blog which says ...

I'd go with the official unless for some reason it's been clearly proven otherwise.

  Should we correct the information in the blog?

It'd be nice, but I'm not sure what the chances would be of that happening. Worth a shot.

  If it is created during a setup then it should not wait for some objects to be created ...

You can look in sys.symmetric_keys and see the service master key (in master) which has a create date and modified date. If you do a fresh install of SQL Server, these will be the same and should be roughly around the time of the first startup as part of the install which would make the official documentation correct.


As Shanky implied in the comments, your Max Server Memory value is probably not changed from the default. SQL is designed to use everything at its disposal.

I've written a script, based off work from Jonathan Kehayias of SQLskills, that will let you know the value you should use.

Jon advises reserving the following amount of RAM for the operating system:

1 GB of RAM for the OS
plus 1 GB for each 4 GB of RAM installed from 4 – 16 GB
plus 1 GB for every 8 GB RAM installed above 16 GB RAM

Anything left over from that, can be allocated to SQL Server, assuming it's a dedicated instance.

If your VM only has 6 GB available, the recommended value is 3 GB. To change it, you would write something like this (or change it in the Management Studio UI):

EXEC sp_configure 'show advanced', 1;
EXEC sp_configure 'max server memory (MB)', 3072;
RECONFIGURE WITH OVERRIDE;
GO


Without knowing the logic in the query or the underlying dataset, the easiest (but not necessarily the best) way to do what you're asking is to use these 2 queries:

SELECT  'TOTALSTUFF' as 'Label', FORMAT(SUM(c."COUNTWORKORDERS"),'##,##0')
AS 'Value'
FROM (SELECT DISTINCT
dpt.dateperiodtypeid
,wo.workordercode
, 1 AS countworkorders
, wo.successfulcompletionind AS countpositivecontact
FROM (SELECT 'CALYEAR' AS dateperiodtypeid
UNION ALL
SELECT 'CALMONTH'
UNION ALL
SELECT 'ROLLBCKPM24MBYMAM') ddpt
JOIN wh.cpy_dateperiodtype_dim dpt
ON dpt.dateperiodtypeid = ddpt.dateperiodtypeid
JOIN wh.cpy_dateperiod_dim dp
ON dp.dateperiodtypekey = dpt.dateperiodtypekey
JOIN wh.mwm_workorder_fact wo
ON wo.createdatetime >= dp.startdate
AND cast(wo.createdatetime as Date) <= dp.enddate
JOIN wh.cpy_employee_dim e
ON e.employeekey = wo.assignedtokey
JOIN wh.cpy_employee_dim mgr
ON mgr.employeekey = e.managerkey
JOIN wh.cpy_org_dim o
ON o.orgid = e.orgid
WHERE wo.dnpind = 1) c
WHERE c.dateperiodtypeid = 'CALMONTH';

and

SELECT 'NONSTUFF' as 'Label', FORMAT(SUM(c.countworkorders -
countpositivecontact),'##,##0') AS 'Value'
FROM (SELECT DISTINCT
dpt.dateperiodtypeid
,wo.workordercode
, 1 AS countworkorders
, wo.successfulcompletionind AS countpositivecontact
FROM (SELECT 'CALYEAR' AS dateperiodtypeid
UNION ALL
SELECT 'CALMONTH'
UNION ALL
SELECT 'ROLLBCKPM24MBYMAM') ddpt
JOIN wh.cpy_dateperiodtype_dim dpt
ON dpt.dateperiodtypeid = ddpt.dateperiodtypeid
JOIN wh.cpy_dateperiod_dim dp
ON dp.dateperiodtypekey = dpt.dateperiodtypekey
JOIN wh.mwm_workorder_fact wo
ON wo.createdatetime >= dp.startdate
AND cast(wo.createdatetime as Date) <= dp.enddate
JOIN wh.cpy_employee_dim e
ON e.employeekey = wo.assignedtokey
JOIN wh.cpy_employee_dim mgr
ON mgr.employeekey = e.managerkey
JOIN wh.cpy_org_dim o
ON o.orgid = e.orgid
WHERE wo.dnpind = 1) c
WHERE c.dateperiodtypeid = 'CALMONTH';

This is just using Column aliases as described here - https://msdn.microsoft.com/en-GB/library/ms176104.aspx

Edited to add a better explanation:

The change is in the columns section of the query. In your original you're requesting that SQL Server returns:

SELECT  FORMAT(SUM(c."COUNTWORKORDERS"),'##,##0')
AS 'TOTALSTUFF' ,FORMAT(SUM(c.countworkorders -
countpositivecontact),'##,##0') AS 'NONSTUFF'

which is 2 columns, one named TOTALSTUFF and one named NONSTUFF. In the first of my reworked queries we request:

SELECT  'TOTALSTUFF' as 'Label', FORMAT(SUM(c."COUNTWORKORDERS"),'##,##0')
AS 'Value'

First we request a dummy column 'TOTALSTUFF' and alias it as 'Label'. Next we pull back the value that was aliased as TOTALSTUFF in your original query and this time alias it as 'Value'

The logic behind your 2 values TOTALSTUFF and NONSTUFF is the same, so I could just split out the columns in the column list.


You need to first CROSS JOIN the two tables, list_of_dates and post_categories and then LEFT JOIN posts:

SELECT
    ld.date     AS date,
    pc.name     AS categoryName,
    COUNT(p.id) AS postCount
FROM
    ( SELECT
          DATE_ADD('2016-12-28', INTERVAL seq.seq DAY) AS date
      FROM
          seq_0_to_999 AS seq
      WHERE
          DATE_ADD('2016-12-28', INTERVAL seq.seq DAY) <= '2017-03-28'
    ) AS ld                   -- list_of_dates
CROSS JOIN
    post_categories AS pc     -- list_of_categories
LEFT JOIN
    posts AS p
  ON  p.user_id = 65
  AND p.created_at >= ld.date AND p.created_at < ld.date + INTERVAL 1 DAY
  AND p.category_id = pc.id
GROUP BY
    ld.date,
    pc.id, pc.name
ORDER BY
    ld.date ASC ;


Is it a good practice to use index hints in production code?

Although your question is about MySQL, Brent Ozar has a good article on this topic about SQL Server.
Generally you want to avoid it for a few reasons, data/stats/indexes can change in the future and then using that index would be bad and hurt performance instead. But if nothing else will solve this, then you can always use it and document it for the future.
I've used RDS a few times and it can be night and day different than using a database on a machine I've noticed. Are you using one for development and one for production? Or what's the different uses for the two? And do both have the same amount of CPU/RAM?


Insert Does Not Increment Primary Key Correctly

The application is our ERP and was purchased from a vendor. Their support does not extend to SQL help so I have no way of seeing how it is doing the insert. There are no sequences in the database at all either.

You could run a trace using SQL Profiler to catch the exact statement that your application is executing.

Also, a slight alteration to Scott's suggestion, I wouldn't use a TABLOCK, instead, something like:

BEGIN TRANSACTION
SELECT @customer_salesrep_uid = max(customer_salesrep_uid)
    FROM YourTable WITH (XLOCK, READPAST)...

This should reduce the risk of locks on the table.


pg_hba.conf allow mutliple I.P. addresses for single user

Putting adding two entries (one for each IP doesn't work: postgres just sees the first entry, which ether matches or doesn't and completely ignores the second entry.

I don't believe that's true. PostgreSQL won't match it if the address is wrong, from the docs on pg_hba.conf

  Each record specifies a connection type, a client IP address range (if relevant for the connection type), a database name, a user name, and the authentication method to be used for connections matching these parameters. The first record with a matching connection type, client address, requested database, and user name is used to perform authentication. There is no “fall-through” or “backup”: if one record is chosen and the authentication fails, subsequent records are not considered. If no record matches, access is denied.

If the client's address does not match, it continues looking for one that does.