Determine if queries have small or large results

ANALYSIS

Look back at your question where you said

  "If you have a lot of queries with small results, the default block size may lead to memory fragmentation" & "If most of your queries have large results (check the Qcache_total_blocks and Qcache_queries_in_cache status variables)"

Here is the context right from the MySQL Documentation

  If you have a lot of queries with small results, the default block size may lead to memory fragmentation, as indicated by a large number of free blocks. Fragmentation can force the query cache to prune (delete) queries from the cache due to lack of memory. In this case, decrease the value of query_cache_min_res_unit. The number of free blocks and queries removed due to pruning are given by the values of the Qcache_free_blocks and Qcache_lowmem_prunes status variables.

  If most of your queries have large results (check the Qcache_total_blocks and Qcache_queries_in_cache status variables), you can increase performance by increasing query_cache_min_res_unit. However, be careful to not make it too large (see the previous item).

Look at your numbers

Qcache_total_blocks : 18381
Qcache_free_blocks : 4250
Qcache_queries_in_cache 7027

Given these numbers

23.12% (4250 / 18381) of the Query Cache is fragmented
There are 2.6158 (18381 / 7027) blocks per query

On average, you can interpret this to mean one of the following:

76.88% (100 - 23.12) of the blocks have data
76.88% (100 - 23.12) of each block has data

Each individual block's fragmentation cannot be fixed. On the other hand, the query cache's total fragmentation can be adjusted by doing one thing the Documentation says (which I highlighted and italicized) : decrease the value of query_cache_min_res_unit.

If you want more performance (as I had italicized), you can increase performance by increasing query_cache_min_res_unit. This will introduce more fragmentation and more query cache pruning.

YOUR ACTUAL QUESTION

  Is it correct interpretation that total_blocks / queries_in_cache = average number of blocks for each cached query (in my case 2.6)?

On average, Yes

  Would it make any sense to increase query_cache_min_res_unit from the default 4096?

Depends on what you want, more performance or less fragmentation.

With 23.12% fragmentation, I would work on decreasing query_cache_min_res_unit to lower the query cache's pruning activity against small queries. Strictly in terms of the query cache, performance will take care of itself. When you can drop the fragmentation percentage, you could possibly increase the query_cache_size to fit more query results IF AND ONLY IF the results are frequently accessed.

Think about this as well: each block is 1K since the Documentation says

  The query_cache_size value is aligned to the nearest 1024 byte block.

Thus, 2.6K per query result. Again, this gives you more reason to focus on reducing fragmentation.

UPDATE 2014-10-28 12:53 EDT

I just remembered something that I needed to correct. The size of a block is not 1K. I believe that the size of a query cache block is actually set by query_alloc_block_size. If this is the case, then 2.6 blocks is not 2.6K as I thought before.

Given the default value for query_alloc_block_size is 8K, then 2.6 blocks would be 20.8K.

In light of this, let's look at the numbers again.

TOTAL BLOCKS

18381 (number of total blocks) X 8192 (8K default block size) rounded up

mysql> select CEILING(18381 * 8192 / power(1024,2)) QueryCacheSizeInMB;
+--------------------+
| QueryCacheSizeInMB |
+--------------------+
|                144 |
+--------------------+

FREE BLOCKS

4250 (number of free blocks) X 8192 (8K default block size) rounded up

mysql> select CEILING(4250 * 8192 / power(1024,2)) QueryCacheFreeMB;
+------------------+
| QueryCacheFreeMB |
+------------------+
|               34 |
+------------------+

Simply subtraction shows that 110M is in use for the 7027 queries.

AVERAGE SPACE PER QUERY RESULT

mysql> select format(110 * power(1024,2) / 7027 / 1024,2) KBPerQueryResult;
+------------------+
| KBPerQueryResult |
+------------------+
| 16.03            |
+------------------+

That yields 16.03K per results.

BUT WAIT ...

Mathematically, we both figured out that 2.6 blocks per query was 20.8K.  Since average space per query result (16.03K) is a few bytes above 2.0 blocks, fragmentation is all but guaranteed.

SUGGESTION

The Documentation says of query_alloc_block_size

  If you have problems with memory fragmentation, it might help to increase this parameter.

Therefore, you should increase query_alloc_block_size to 16K, 24K or 32K. Try any multiple of 8K until the fragmentation is lower. Due this in conjunction with either increasing or decreasing query_cache_size.

GIVE IT A TRY !!!


How to show the Availability group listener instead of the nodes?

Is there a way I could make sqlProd_ts to show up on the picture above, instead of the nodes?

No, much as Dan Guzman has said, the listener is not an instance of SQL Server and as such will not respond to browser interrogations. In fact, availability groups do not use the browser service.

If you want people to use the listener, tell them the listener name.


Mongodb - Returning the points nearest to a location, with distance

MongoDB has built in support for geoindexing.  You don't need to do the calculation yourself.

Basically, you would create a field with the lat/long stored as an array or as sub documents, something like one of these:

{ loc : [ 50 , 30 ] } //SUGGESTED OPTION
{ loc : { x : 50 , y : 30 } }
{ loc : { lon : 40.739037, lat: 73.992964 } }

Then index the new loc field appropriately:

db.places.ensureIndex( { loc : "2d" } )

Finally you can then use one of the operators to query a point for the nearest 20 results:

db.places.find( { loc : { $near : [50,50] } } ).limit(20)

You could, of course, just use MongoDB to store the data, then pull the information out of the DB with a find() and do the calculation client-side but I imagine that is not what you want to do.

If the distance part of the equation is what you want:

http://www.mongodb.org/display/DOCS/Geospatial+Indexing#GeospatialIndexing-geoNearCommand

The $geoNear operator will return the distance also.  An example:

> db.runCommand( { geoNear : "places" , near : [50,50], num : 10 } );
{
        "ns" : "test.places",
        "near" : "1100110000001111110000001111110000001111110000001111",
        "results" : [
                {
                        "dis" : 69.29646421910687,
                        "obj" : {
                                "_id" : ObjectId("4b8bd6b93b83c574d8760280"),
                                "y" : [
                                        1,
                                        1
                                ],
                                "category" : "Coffee"
                        }
                },
                {
                        "dis" : 69.29646421910687,
                        "obj" : {
                                "_id" : ObjectId("4b8bd6b03b83c574d876027f"),
                                "y" : [
                                        1,
                                        1
                                ]
                        }
                }
        ],
        "stats" : {
                "time" : 0,
                "btreelocs" : 1,
                "btreelocs" : 1,
                "nscanned" : 2,
                "nscanned" : 2,
                "objectsLoaded" : 2,
                "objectsLoaded" : 2,
                "avgDistance" : 69.29646421910687
        },
        "ok" : 1
}

The "dis" : 69.29646421910687 elements are what you are looking for, there is also a spherical distance option.

For all this, how to use the distances, and more, take a look here for more information on geo indexes and how to use them:

http://www.mongodb.org/display/DOCS/Geospatial+Indexing/


Why is DTA recommending non-clustered indexes that match the clustered Primary Key index?

The index does not match exactly what already exists.  The Clustered index (the primary key) contains all the other data related to the table.  But the nonclustered index (NCI) contains just the primary key. The resulting NCI is much narrower than the clustered index.

Having a non-clustered index on just the primary key has a few benefits that may be relevant here.  But, I also want to caution you to take the DTA and the suggested missing indexes with a grain of salt.

Benefits of having a non-clustered index in addition to the clustered index (on the same key).

Foreign Key Check Constraints - If other tables use this one as a
foreign key reference, then having a narrow NCI will speed this up
dramatically.
COUNT or EXISTS - If you frequently check for the existence of this
value (or count them) without any other conditions, then having an
NCI will also be beneficial.  Having a narrower index to use for
these is extremely efficient, especially if the base table is very
wide.

Basically, even though both would be seek operations, if the NCI can be used it will typically be very narrow and can fit more ID values per page on disk so will reduce memory grants.


Missing the pg_ctl package in Postgres 9.3 installation

In Debian/Ubuntu, the functionalities of pg_ctl  are provided by pg_ctlcluster. Quoted from its manpage:

NAME

       pg_ctlcluster - start/stop/restart/reload a PostgreSQL cluster

SYNOPSIS

       pg_ctlcluster [options] cluster-version cluster-name action -- [pg_ctl
       options]

       where action = start|stop|restart|reload|promote

DESCRIPTION

       This program controls the postmaster server for a particular cluster.
       It essentially wraps the pg_ctl(1) command. It determines the cluster
       version and data path and calls the right version of pg_ctl with
       appropriate configuration parameters and paths.

It's not technically true that pg_ctl is not installed with postgres packaged for Debian/Ubuntu. It's actually installed in /usr/lib/postgresql/X.Y/bin, where X.Y is the postgres version. This path is intentionally not in any user's default PATH since it should never needed to be invoked directly.

The reason for this setup is that the Debian/Ubuntu packaging supports several PostgreSQL instances running in parallel, either identical or different major versions.

To learn about that, it's best to read first https://help.ubuntu.com/community/PostgreSQL , before the real PostgreSQL manual or any other non-Ubuntu documentation.

postgres-xc is a multi-master fork of PostgreSQL that shouldn't be confused with PostgreSQL itself. The hint suggesting to install it to get pg_ctl is an unfortunate nonsense, probably a machine-generated advice based on the contents of postgres-xc.


Should I create new table, or add new columns to existing one for song lyrics?

As per normalization, you should not have similar value attributes in multiple columns. In future, let's say, if you have to add 10 more languages later, your table will become huge. Moreover, adding new columns in a table with lots of data in a production environment is not so easy. Relational data should not be considered like a Flat Excel file. Searching for a combination of languages in lyrics will not be easy as well. Another issue could be that you will have lots of NULL values, because not all songs would have lyrics in all the languages. NULL is a special value, and handling it requires special care while formulating queries, and lots of common mistakes happen due to not accounting for the NULL.

Instead, you can create a new table, let's say, songs_lyrics. It will have three columns, viz., song_id (Foreign Key to ID in songs table), language_id (Foreign Key to ID in a language master table), lyrics (text containing the lyrics in the specific language). You will also have a composite Primary Key on (song_id, language_id)

CREATE TABLE songs_lyrics (
  song_id INT NOT NULL,
  language_id INT NOT NULL,
  lyrics TEXT,
  PRIMARY KEY (song_id, language_id),
  FOREIGN KEY (song_id) REFERENCES song(ID),
  FOREIGN KEY (language_id) REFERENCES language(ID)
) engine= InnoDB;

You will also need to create the Master table for the languages:

CREATE TABLE language (
  ID INT NOT NULL AUTO_INCREMENT
  name VARCHAR(64) NOT NULL
  PRIMARY KEY (ID)
) engine=InnoDB;


MongoDB - Don't know what to do with file

The .ns file is the file in which MongoDB stores the namespaces of a database. So most likely, what you have is not a dump, but a copy of the datafiles of a single database. Note that this most likely is only a partial copy of a MongoDB instance.

Here is what to do in case you already have an instance of MongoDB running.

Start an other MongoDB instance on another port with the dbpath pointing to the directory with those files.
Connect to the database instance and verify the data.
Make a dump of the data.
Stop the new instance.
Restore the dump to the original instance
Optionally delete the "rogue" datafiles

In case you do not have any instance running yet, simply start MongoDB with dbpath pointing to the directory with the data files or copy the datafiles into your dbpath.


activate Service Broker on a database in an Availability Group

First, create and configure the service broker endpoint
select * from sys.tcp_endpoints

Create a new end point for the service broker:
CREATE ENDPOINT [Avaruu5alus]
    STATE = STARTED
    AS TCP  (LISTENER_PORT = 4022, LISTENER_IP = ALL )
    FOR SERVICE_BROKER (AUTHENTICATION = WINDOWS)

GRANT CONNECT ON ENDPOINT::[Avaruu5alus] TO [PUBLIC]

SQL Server Broker is not Multisubnet aware. You need to set RegisterAllProvidersIP to 0
Thereafter take the availability group offline and bring online again.
USE [master]
GO
ALTER DATABASE [Avaruu5alusDB] SET  ENABLE_BROKER WITH NO_WAIT
GO


Oracle 11G Startup failed after altering the number of system processes

First you can try:

STARTUP NOMOUNT
ALTER SYSTEM SET processes=400;
SHUTDOWN IMMEDIATE
STARTUP

If Oracle cannot start even in NOMOUNT mode then you can fetch configuration information from spfile:

strings $ORACLE_HOME/dbs/spfile$SID.ora > /tmp/pfile

Then edit processes in /tmp/pfile cleanup some mess made by strings start Oracle with pfile and recreate spfile:

STARTUP pfile=/tmp/pfile
CREATE spfile FROM pfile='/tmp/pfile';


Is it possible to change the family_guid in MSSQL?

I have encountered the exact same scenario as you - many DBs all based off a template, and all need to go into RDS.

AFAIK there is no way to change the underlying family_guid in a simple command.
However, using the .bacpac export/import will assign a new family_guid to the imported DB. Sadly you can't directly import a .bacpac into RDS (the import fails, and you can't delete the database!)

So, as a work around, I ended up with the following process:

Create a .bacpac (contains both data & schema) of the original DB.
Import that .bacpac into a staging database (could be LocalDB, or another instance somewhere, or even the same instance as the original DB).
Create a traditional backup (.bak) of the staging DB.
Upload the backup to S3.
Run the msdb.dbo.rds_restore_database command on the S3 backup file.

Quite a process for what should be a simple task!


Oracle: force shutdown as root without knowing logins

A shutdown is already in progress. You can either

Wait
Login using su - oracle and then sqlplus / as sysdba and issue the command shutdown abort if that's what you really want.


financial transactions with and without commissions

For any application where you are tracking money changing hands, you should always use a Double Entry Accounting system.  Double entry accounting has been the standard for tracking money for over 500 years.  There is no good reason to use any other method.

This entails a TRANSACTION table and a TRANSACTION_DETAIL table.  The transaction table has one record per transaction that includes header information, like the date, the total amount, any categorization attributes or relationships, etc.

The transaction detail has two or more records per transaction.  These records break down where the money comes from and goes to.  In your case, you might have three records per transaction.  One shows the money coming in from the payment service.  The other two show the money going out to a commission account and to an income account.


As you have nothing dynamically built in your query, you don't have to use EXECUTE at all.

Inside the function body, you can simply do an INSERT if temp_table already exists:

INSERT INTO temp_table (fielda, fieldb, fielbc)
SELECT a, b, c
  FROM dblink('dbname=dbname port=5432 host=a.b.c.d user=user password=pw',
              E'SELECT fielda, fieldb, fieldc FROM data_table')
       AS t(a, b, c);

If the table is not there yet, you can replace the INSERT line with

CREATE TEMPORARY TABLE AS
...

Note that in your present approach (SELECT ... INTO temp_table) the table that is being created is not a temporary table, and on the next run the trigger will run into an error saying the table already exists.


There is no such direct language construct. If you want to do an "unnatural order" (based on the data type), then you can either join to some other table that maps the values and has another column to define the order for each value, or you can use CASE in your ORDER BY, such as

CREATE TABLE t(col int IDENTITY)
INSERT INTO t DEFAULT VALUES
GO 8

SELECT *
FROM t
WHERE col IN(5, 3, 4)
ORDER BY
 CASE col
  WHEN 5 THEN 1
  WHEN 3 THEN 2
  WHEN 4 THEN 3
 END

I'm using int above, but that doesn't change the concept. If you don't like repeating the "list of values" in the ORDER BY as well as the WHERE you can skip the WHERE and do a TOP for as many rows that you want. In any case, not neat, but since there isn't such language construct...


Remove duplicates in SQL Server Group By

Windowing functions can help here.

SELECT CASE WHEN RowNum = 1 THEN TypeName ELSE NULL END AS TypeName, ProductName
FROM (
SELECT Type.Name AS TypeName,Product.Name AS ProductName, ROW_NUMBER() OVER(PARTITION BY Type.Name ORDER BY Product.Name) AS Rownum, COUNT([whatever you want to count]) AS ItemCount
FROM Product INNER JOIN Type ON Type.Id = Product.TypeId
GROUP BY Type.Name, Product.Name
) X

The MSDN reference for the OVER clause is here, by the way:

https://msdn.microsoft.com/en-us/library/ms189461.aspx


Some SQL products – like PostgreSQL, for instance – allow you to have nested CTE definitions, enabling you to write your queries like this:

SELECT val1, val2, ...
FROM
 (  SELECT top 1 id as val1
    FROM TableA
    ) A
CROSS JOIN
 (  SELECT top 1 id as val2
    FROM TableB
     ) B
CROSS JOIN
 (
    WITH r AS (                              -- nested CTE definition
      SELECT * FROM film WHERE length = 120
    )
    SELECT CASE WHEN @flag = 'Y' THEN (
        SELECT * FROM r
        UNION ALL
        SELECT * FROM film
        WHERE length = 130
        AND NOT EXISTS (
          SELECT * FROM r
        )
    ) ELSE NULL END val3
  ) C

SQL Server does not support this, though, as ypercubeᵀᴹ explains in a comment.

However, as per another comment by ypercubeᵀᴹ, you can move the CTE definition to the beginning of the entire query. That way you you can still reference it in the C derived table. So your query will look like this:

WITH r AS (                              -- the CTE definition goes here
  SELECT * FROM film WHERE length = 120
)
SELECT val1, val2, ...
FROM
 (  SELECT top 1 id as val1
    FROM TableA
    ) A
CROSS JOIN
 (  SELECT top 1 id as val2
    FROM TableB
     ) B
CROSS JOIN
 (
    SELECT CASE WHEN @flag = 'Y' THEN (
        SELECT * FROM r                  -- this CTE reference is valid
        UNION ALL
        SELECT * FROM film
        WHERE length = 130
        AND NOT EXISTS (
          SELECT * FROM r                -- as is this one
        )
    ) ELSE NULL END val3
  ) C


psql: fatal database does not exist error

psql assumes that you want to connect to a database, you can either provide one (just after the command) or it will assume you want to connect to a database whose name is the same as your username (or the account name of the process that started psql):

So, you could write:

psql my_db

or

psql -d my_db

or yet

psql --dbname=my_db

... and psql will connect to the (local) database named my_db.

You have more connection options that you can check in the documentation.

If you don't have yet a database created, check createdb.


MySQL Range partitioning vs Hash partitioning

Short answer: Neither.

Long answer:

Partitioning is not a panacea.  It does not automatically provide performance.  In fact, it sometimes slows down processing.

I have never see a use case where BY HASH helped.  I have seen a few use cases for BY RANGE.  Off hand, it does not sound like your use case will benefit.

Please provide SHOW CREATE TABLE and a few of the critical SELECTs.  With those, I will either

Explain why partitioning is useless.  Or...
Explain how to benefit from partitioning for those queries.

In either case, I will probably suggest index (including PK) changes.  Proper indexes is where performance usually comes from.

In another direction...  Is this some form of "multi-tenant" app?  Will it eventually out-grow the server?  If so, Sharding may be in your future.  This is when you scatter "clients" (or whatever) across multiple servers.  But, alas, MySQL does not have any built-in support of such.  There are 3rd party packages (eg, Spider) that do.


Optimizing removal of duplicate rows from recursive CTE query

How about this? Kind of a hack - but row_number should be quicker than distinct.

;WITH Lineage AS
(
SELECT
    Name
   ,CASE WHEN ROW_NUMBER() OVER (PARTITION BY ParentName ORDER BY ParentName) > 1
         THEN NULL
         ELSE ParentName END AS ParentNameToJoin
   ,ParentName AS ParentNameToDisplay
FROM
    #Project
WHERE
    Name in ('C1','C2')
UNION ALL
SELECT
    p.Name
   ,CASE WHEN ROW_NUMBER() OVER (PARTITION BY p.ParentName ORDER BY p.ParentName) > 1
         THEN NULL
         ELSE ParentName END AS ParentNameToJoin
   ,p.ParentName
FROM
    #Project as p
INNER JOIN
    Lineage ON p.Name = Lineage.ParentNameToJoin
)
SELECT
    Name
   ,ParentNameToDisplay
FROM
    lineage


How to LEFT JOIN a date table onto each customer (GROUP BY) in a customer purchase table? Can a JOIN be done for dates after initial purchase?

To follow on from my comment. I'd recommend a date table.

Sample Customer Purchase Data

IF OBJECT_ID('tempdb..#CustomerPurchases') IS NOT NULL DROP TABLE #CustomerPurchases
GO
CREATE TABLE #CustomerPurchases (PurchaseDate date, CustomerName varchar(20))
INSERT INTO #CustomerPurchases (PurchaseDate, CustomerName)
VALUES
 ('2016-07-04','Jon Snow')
,('2016-07-06','Jon Snow')
,('2016-07-07','Jon Snow')
,('2016-07-07','Jon Snow')
,('2016-07-07','Jon Snow')
,('2016-07-05','Daenerys Targaryen')
,('2016-07-06','Daenerys Targaryen')
,('2016-07-09','Daenerys Targaryen')
,('2016-07-09','Daenerys Targaryen')
,('2016-07-10','Daenerys Targaryen')

Sample Date Table

IF OBJECT_ID('tempdb..#DateTable') IS NOT NULL DROP TABLE #DateTable
GO
CREATE TABLE #DateTable (DateList date)
INSERT INTO #DateTable (DateList)
VALUES
 ('2016-07-04')
,('2016-07-05')
,('2016-07-06')
,('2016-07-07')
,('2016-07-08')
,('2016-07-09')
,('2016-07-10')

You could cross join the list of dates to then get a full list of all customers and all dates. Then join to the actual sales data to return the Boolean value you're after.

SELECT
dt.DateList
,cu.CustomerName
,(CASE WHEN cp.PurchaseDate IS NULL THEN 0 ELSE 1 END) PurchaseMade
FROM #DateTable dt
CROSS JOIN  (
                SELECT DISTINCT
                CustomerName
                FROM #CustomerPurchases
            ) cu
LEFT JOIN   (
                SELECT DISTINCT
                CustomerName
                ,PurchaseDate
                FROM #CustomerPurchases
            ) cp
    ON dt.DateList = cp.PurchaseDate
    AND cu.CustomerName = cp.CustomerName

Results would look like this

DateList    CustomerName        PurchaseMade
2016-07-04  Daenerys Targaryen  0
2016-07-05  Daenerys Targaryen  1
2016-07-06  Daenerys Targaryen  1
2016-07-07  Daenerys Targaryen  0
2016-07-08  Daenerys Targaryen  0
2016-07-09  Daenerys Targaryen  1
2016-07-10  Daenerys Targaryen  1
2016-07-04  Jon Snow            1
2016-07-05  Jon Snow            0
2016-07-06  Jon Snow            1
2016-07-07  Jon Snow            1
2016-07-08  Jon Snow            0
2016-07-09  Jon Snow            0
2016-07-10  Jon Snow            0

If you were to do this then your date table would obviously be much wider than the week example I've done. You could then just use date parameters to restrict to the date range you're after. Something like this;

DECLARE @StartDate date; SET @StartDate = '2016-07-05'
DECLARE @EndDate date; SET @EndDate = '2016-07-08'
SELECT
dt.DateList
,cu.CustomerName
,(CASE WHEN cp.PurchaseDate IS NULL THEN 0 ELSE 1 END) PurchaseMade
FROM #DateTable dt
CROSS JOIN  (
                SELECT DISTINCT
                CustomerName
                FROM #CustomerPurchases
            ) cu
LEFT JOIN   (
                SELECT DISTINCT
                CustomerName
                ,PurchaseDate
                FROM #CustomerPurchases
            ) cp
    ON dt.DateList = cp.PurchaseDate
    AND cu.CustomerName = cp.CustomerName
WHERE dt.DateList BETWEEN @StartDate AND @EndDate
ORDER BY CustomerName, DateList

Which would give these results

DateList    CustomerName        PurchaseMade
2016-07-05  Daenerys Targaryen  1
2016-07-06  Daenerys Targaryen  1
2016-07-07  Daenerys Targaryen  0
2016-07-08  Daenerys Targaryen  0
2016-07-05  Jon Snow            0
2016-07-06  Jon Snow            1
2016-07-07  Jon Snow            1
2016-07-08  Jon Snow            0


As you cannot use the vendor name as identifier, since it may be duplicated (as it happens with the name of a person), you need a true identifier to be set upon creation. When someone identifies themselves in your system, they cannot identify by their name, since it is not enough to uniquely identify them. Thus you don't have any other option than assigning them any proper id when you create the record on your vendors table and identify them by that id.

For this purpose you can create an ad-hoc column (filled in its most simple approach with an auto incremental series), or use a contact email, Tax identifier or any other key that you know is unique, instead of the name.


Why is a functional dependency described as a "one-to-many" relationship?

I think that the FD can't be described as "One-to-one" relationships. In One-to-one relationships, the X value identifies a Y value and vice-versa. In FD this doesn't happen. In fact, the Y values can be shared from different tuples with the same X value.

Think to a simple table with only cities, states and nations (And don't care about the redundancy). We know that the state identifies one and one nation, but a single nations doesn't identify a single state.

So, generally  X implies Y but Y doesn't imply X.

A last observation: an one-to-one relationship need that the tables involved in the relationship must have the same number of rows, in order to associate to every a one b and to associate to every b one a. In the textbook you'll find a lot of FD in which this doesn't happen.

So we can describe a FD as an association of a Y value to many (only sometimes one) X values. So it's called an one-to-many relationship.


How can I enable the reports menu in SQL databases

I think it's not because of the version of your DB server but because of the version of your Management Studio.

I tried to connect a SQLServer 2016 with a Management Studio 2014, and indeed, the Report feature is disabled (only for databases).

And then tried with a Management Studio 2016, the Report feature is now available.

So, try to upgrade your Management Studio to the newest version.


What is the impact on the Cost-Based Optimizer (CBO) when implementing ISCSI SAN solid-state drives

The cost-based optimizer does not (currently) change its estimated IO costs based on your hardware.

You say this question is geared towards answering what filegroup and partitioning strategy is best for your environment. In that case, who cares what the optimizer does? The question is what works well in your environment, and the real answer is to test it.


Is it possible to pipe the result of a mysqldump straight to rsync as the source argument?

It's not clear exactly what you're trying to accomplish, but I'll take a couple of guesses and see how close I get.

If you're wanting to let rsync magically handle the difference between your local dump file and a remote dump file, you could just replace the | with && so that if mysqldump finishes without an error, then rsync will transfer the differences after the backup is complete, and if mysqldump fails, the synch won't happen.

On the other hand, if you're just wanting to transfer the dump file across the network and drop it somewhere other than the local machine, then this is an option:

mysqldump -u root -p database_to_backup | ssh myuser@mysite.com 'cat > /var/www/db_backup_file.sql'

...where /var/www/db_backup_file.sql is a path on the remote machine.

Or if you wanted to save it both locally and remotely at the same time:

mysqldump -u root -p database_to_backup | tee db_backup_file.sql | ssh myuser@mysite.com 'cat > /var/www/db_backup_file.sql'


How do I compare large stored procedures?

This question exists on two levels:

Comparing between two databases:

In this context you do not need to worry about length (outside of perhaps using DATALENGTH as a quick indicator of there being a difference and hence no need to compare the actual string values). Here you just need to make sure that the definitions are identical, and that does not require SUBSTRING, but it does require adding COLLATE Latin1_General_100_BIN2 to the comparison to ensure that you catch differences in case, etc that otherwise evaluate as being equal (using an everything-sensitive Collation, such as anything ending with _CS_AS_WS_KS[_SC], can't catch differences when equality is determined by combining characters and/or linguistic rules).

Within a particular environment, an object should be identical across all databases where it is deployed to. There can't be any differences due to scripting since none of the definitions are being compared to the actual source.

While comparisons on this level are easier (since you need not worry about white-space differences), they can also be misleading if all definitions are identical, yet they are all different from the source definition in the exact same way.
Comparing between database and source script

In this context you do need to worry about length and white-space variation depending on how your source object scripts are written and how they are packaged up for deployment. Here T-SQL is not going to help you as there are too many variations in what could be different yet still be "the same".

This is where several 3rd party tools come into play and you should choose one of them. They can be configured to ignore certain white-space as well as include additional properties and sometimes even permissions.

If you want to know if there are differences in Production that should either be removed OR factored into the source definition, you can only determine that by comparing to the source object script.

If you do not have source scripts for each object, then you absolutely need to do this and now is as good of a time as any to start.

If you do not have your source scripts in some sort of version control (Git, SVN, TFS, anything..), then you absolutely need to do this and now is as good of a time as any to start.

On a related note, the equation for the max size (in bytes, not "characters") is correct:

SELECT POWER(CONVERT(BIGINT, 2), 31) - 1
-- 2147483647

That value matches the max size of an INT as noted here. Since NVARCHAR is UTF-16, that is either 2 or 4 bytes per Code Point (a "character" is comprised of 1 or more Code Points, such as when using combining characters), hence the max "length" of a string is half of that, assuming that all "characters" are of the single, 2-byte variety (and they usually are). Meaning:

SELECT 2147483647 / 2
-- 1073741823

So the max amount of "characters" in an NVARCHAR(MAX) column is 1,073,741,823.

HOWEVER, this is irrelevant for SUBSTRING since it doesn't error in T-SQL if requested to return more characters than are in the input string. For example:

SELECT SUBSTRING('A', 1, 100);
-- A

So you can just put an arbitrary high value as the 3rd parameter.


New and old passwords on same MySQL database?

It is possible before version 5.7, where it has been disabled.

To set up an old password for a user, use command:

SET PASSWORD FOR 'some_user'@'some_host' = OLD_PASSWORD('mypass');

More information: Password Hashing in MySQL.

You can enable/disable old passwords globally or per session. On a server where they are enabled globally (i.e. the server was started with old_passwords = 1), run this:

SET @@session.old_passwords = 0;
SET PASSWORD FOR 'test'@'localhost' = PASSWORD('mypass');

to disable them for a session. The PASSWORD in the above snippet will return a password in the new format, affecting only the current session.


Evaluate a string value as a computed expression in an SQL statement (sthg like EVAL)

for my specific problem, an answer could be :

    DROP FUNCTION stringExp2Int;
    DELIMITER $$
    CREATE FUNCTION stringExp2Int (expression VARCHAR(20))
        RETURNS INT
        NO SQL
        BEGIN
            DECLARE val INT DEFAULT 0;
            DECLARE ct INT DEFAULT 1;
            WHILE ct <= LENGTH(expression) do
                SET val = val + SUBSTR(expression,ct,1);
                SET ct = ct + 2;
            END WHILE;
             RETURN val;
        END
    $$
    DELIMITER ;

Using LOCATE to find the next separator, could be more secure :

    DROP FUNCTION stringExp2Int;
    DELIMITER $$
    CREATE FUNCTION stringExp2Int (expression VARCHAR(20))
        RETURNS INT
        NO SQL
    BEGIN
      DECLARE val INT DEFAULT 0;
      DECLARE ct INT DEFAULT 1;
      DECLARE pos INT DEFAULT 0;
      DECLARE sep CHAR(1) DEFAULT "+";

      WHILE ct <= LENGTH(expression) DO
         SET pos = LOCATE(sep,SUBSTR(expression,ct,LENGTH(expression)-ct+1));
         IF pos >= 1 THEN
            BEGIN -- when pos = 1 : add a null value
                SET val = val + SUBSTR(expression,ct,pos-1);
                SET ct = ct + pos;
            END;
        ELSE
            BEGIN
                SET val = val + SUBSTR(expression,ct,LENGTH(expression)-ct+1);
                SET ct = ct + LENGTH(expression)-ct+1;
            END;
        END IF;
     END WHILE;
     RETURN val;
    END
    $$
    DELIMITER ;

Trials :

mysql> SELECT stringExp2Int("");
+-------------------+
| stringExp2Int("") |
+-------------------+
|                 0 |
+-------------------+

mysql> SELECT stringExp2Int("+");
+--------------------+
| stringExp2Int("+") |
+--------------------+
|                  0 |
+--------------------+

mysql> SELECT stringExp2Int("1");
+--------------------+
| stringExp2Int("1") |
+--------------------+
|                  1 |
+--------------------+

mysql> SELECT stringExp2Int("+1");
+---------------------+
| stringExp2Int("+1") |
+---------------------+
|                   1 |
+---------------------+

mysql> SELECT stringExp2Int("1+");
+---------------------+
| stringExp2Int("1+") |
+---------------------+
|                   1 |
+---------------------+

mysql> SELECT stringExp2Int("1+1");
+----------------------+
| stringExp2Int("1+1") |
+----------------------+
|                    2 |
+----------------------+

mysql> SELECT stringExp2Int("+1+1+");
+------------------------+
| stringExp2Int("+1+1+") |
+------------------------+
|                      2 |
+------------------------+

mysql> SELECT stringExp2Int("+++1+++1+++");
+------------------------------+
| stringExp2Int("+++1+++1+++") |
+------------------------------+
|                            2 |
+------------------------------+

mysql> SELECT stringExp2Int("+++001+++001+++");
+----------------------------------+
| stringExp2Int("+++001+++001+++") |
+----------------------------------+
|                                2 |
+----------------------------------+

and :

mysql> SELECT stringExp2Int(detailNote) FROM A;
+---------------------------+
| stringExp2Int(detailNote) |
+---------------------------+
|                        16 |
+---------------------------+
1 row in set (0.00 sec)

I close this question...hope it could be useful for someone...


If started from the same SQL window, then no - you will always get the latest result set (or nothing, if the last command returns no rows, like in the case of COMMIT).  Not sure if this is OK for you, but you can open as many query windows as you want and so have as many result sets.

(The above means that I'm not aware any such plugins.)


SSRS Report by URL with parameter-value AND retain full top menu

The sages of #sqlcommunity (namely @JeffRush) have informed me that this is not possible. It has to do with the two different interfaces --

  "..that is something that only exists when you are accessing
  Reports interface. URL access hits ReportServer [interface]." (emphasis mine)

Hope that helps future readers.


Can you remove the redirect from transactional replication from an HADR publisher?

Apparently I was overthinking the problem. Running the following:

Exec dbo.sp_redirect_publisher @original_publisher   =  'SQLServer03',
                               @publisher_db         =  'LMDB25',
                               @redirected_publisher =  NULL;
Go

where @original_publisher is the currently and original primary node,
and @publisher_db is the database name.

To replace the redirects so that replication will work normally again simply re-run the command changing the NULL parameter value to the server node name of the previously offline server node:

Exec dbo.sp_redirect_publisher @original_publisher   =  'SQLServer03',
                               @publisher_db         =  'LMDB25',
                               @redirected_publisher =  'SQLServer04';
Go


Is it possible to see which SPID uses which scheduler (worker thread)?

Thanks to Solomon Rutzky:

  Have you tried correlating to the scheduler_id column in sys.dm_exec_requests?

I was able to get the information I was looking for.

With this query, I can see which session is using which CPU_ID (scheduler):

SELECT
    s.cpu_id,
    s.status,
    db_name(r.database_id) as [databaseName],
    w.last_wait_type,
    w.return_code,
    t.task_state,
    t.pending_io_count,
    t.session_id,
    r.sql_handle
FROM  sys.dm_os_Schedulers s
JOIN sys.dm_os_workers w
    ON w.scheduler_address = s.scheduler_address
JOIN sys.dm_os_tasks t
    ON t.task_address = w.task_address
JOIN sys.dm_exec_requests r
    ON r.scheduler_id = s.scheduler_id
order by 1,3

To get the SQL statements that are running, I change the query to:

SELECT
    s.cpu_id,
    s.status,
    db_name(r.database_id) as [databaseName],
    w.last_wait_type,
    w.return_code,
    t.task_state,
    t.pending_io_count,
    t.session_id,
    r.sql_handle,
    te.text
FROM  sys.dm_os_Schedulers s
JOIN sys.dm_os_workers w
    ON w.scheduler_address = s.scheduler_address
JOIN sys.dm_os_tasks t
    ON t.task_address = w.task_address
JOIN sys.dm_exec_requests r
    ON r.scheduler_id = s.scheduler_id
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) te
order by 1,3

But that only gives me (obviously) the tasks that have an SQL_handle.

It seems that most of the threads on that server are being used by the system itself to keep everything in sync. Most of the workers are being used as "system jobs". The last_wait_type of those tasks is mostly REDO_THREAD_PENDING_WORK or HADR_WORK_QUEUE.

Although I have the answer I was looking for, I still didn't find the source of the problem. I'm going to open another question for that ( Who is using my worker threads? SQL Server 2014 - HADR ).


Why does this query not return records where both fields using CASE WHEN are zero?

You need to do a LEFT OUTER JOIN on goal to pick up the 0-0 games.

SELECT mdate,
  team1,
  sum(CASE WHEN teamid=team1 THEN 1 ELSE 0 END) score1,
team2,
  sum(CASE WHEN teamid=team2 THEN 1 ELSE 0 END) score2
  FROM game
LEFT OUTER JOIN goal ON matchid = id
group by mdate, matchid, team1,team2
order by mdate, matchid, team1 ,team2

You were nearly there.


How to give precedence to rows from one table over matching rows in another?

If you need the value for only one "key", you can use COALESCE() function with 2 subqueries:

-- query 1a
SELECT COALESCE(
           (SELECT value FROM branch_settings WHERE key = prm.key),
           (SELECT value FROM company_settings WHERE key = prm.key)
       ) AS value
FROM
    (VALUES ('key.A')) AS prm (key) ;

You could also expand the list of keys inside the VALUES, to search more than one key:

-- query 1b
FROM
    (VALUES ('key.A'), ('key.B'), ..., ('key.Z')) AS prm (key) ;

If you want to search many keys however, it might be more efficient to convert the subqueries to joins. Either two LEFT joins:

-- query 2
SELECT prm.key, COALESCE(b.value, c.value) AS value
FROM
    (VALUES ('key.A'), ('key.B'), ..., ('key.Z')) AS prm (key)
  LEFT JOIN branch_settings AS b ON b.key = prm.key
  LEFT JOIN company_settings AS c ON c.key = prm.key ;

or a FULL JOIN, similar to @James' answer:

-- query 3
SELECT key, COALESCE(b.value, c.value) AS value
FROM
    (SELECT * FROM branch_settings
     WHERE key IN ('key.A', 'key.B', ..., 'key.Z')
    ) AS b
  FULL JOIN
    (SELECT * FROM company_settings
     WHERE key IN ('key.A', 'key.B', ..., 'key.Z')
    ) AS b
  USING (key) ;

There is a subtle difference between queries 1b, 2 and query 3. The last query will give you in the results only the (parameter) keys that appear in at least one of the tables. Query 1b and 2 will have all the (parameter) keys in the results, with NULL in value for keys that can'e be found in either table.


While many database systems will actually behave like your first version, for MySQL, version 2 is the expected behaviour, see the documentation about repeatable read:

  This is the default isolation level for InnoDB. Consistent reads within the same transaction read the snapshot established by the first read. This means that if you issue several plain (nonlocking) SELECT statements within the same transaction, these SELECT statements are consistent also with respect to each other.

This means: if you select the same row, you get the same data (from the snapshot), even if the row has been deleted by another transaction. This is what the sql standard requires: it has to prevent "non-repeatable reads" (in addition to "dirty reads" of lower isolation levels), but every database system can implement it differently in details.

In MySQL, if you want to block another transaction from updating the rows in repeatable read, you need to lock them by e.g. using select ... for update. A simple select will not place a lock unless you are in serializable isolation mode.


Convert MySQL database from latin1 to utf8mb4 - and take care of German umlauts

There are two possible questions here, and they have two different answers --

How do I make all new tables utf8mb4

It can be done (for one database) while creating a database:

CREATE DATABASE dbname
    DEFAULT CHARACTER SET utf8mb4
    DEFAULT COLLATE       utf8mb4_unicode_ci;

You can ALTER a database (similar syntax), but that only provides a default for future tables.

There are bugs relating to inheriting from the 'server' variables:  http://dev.mysql.com/worklog/task/?id=3811 , so I suggest that you simply be explicit on each database.

How do I convert existing latin1 tables

If you have a table declared to be latin1 and correctly contains latin1 bytes, and you would like to change all the char/text columns to utf8...

ALTER TABLE tbl CONVERT TO CHARACTER SET utf8mb4;

This changes the definition and actively changes the necessary bytes in the columns.

ALTER TABLE tbl MODIFY col1 ... CHARACTER SET utf8mb4;

is similar to the above, but works only one column at a time, and needs
exactly the right stuff in the MODIFY clause.  Hence, it would be quite tedious.  However, it is useful if you want to change only some of the columns.


What are the SQL Server 2012 versions of FIRST() and LAST()?

You were close - FIRST and LAST are from Access; in SQL Server (starting with SQL Server 2012) they are FIRST_VALUE() and LAST_VALUE().

So, if you are 2012 or better (or Azure SQL Database), here's one way to get your answer:

CREATE TABLE #fl
(
  IdentityColumn INT IDENTITY,
  Value INT
);

INSERT #fl(Value) SELECT 10;
INSERT #fl(Value) SELECT 45;
INSERT #fl(Value) SELECT 65;
INSERT #fl(Value) SELECT 95;
INSERT #fl(Value) SELECT 200;

SELECT TOP (1) LAST_VALUE(Value) OVER (ORDER BY IdentityColumn)
            - FIRST_VALUE(Value) OVER (ORDER BY IdentityColumn)
  FROM #fl
  ORDER BY IdentityColumn DESC;

GO
DROP TABLE #fl;


Lots of stuff wrong here, thankfully lots that can be fixed.

Problems:

You have a heap. Good odds this is heavily fragmented and pages are spread throughout the 82GB data file. See sys.dm_db_index_physical_stats for guidance on checking fragmentation.
You only have 6GB of memory, if you're lucky maybe 4GB is available to the buffer pool.
Reading between the lines, you are using dog slow SATA spinner drives.
A scan of the table will require 11GB of random IO across that dog slow drive and churn the buffer pool completely 3 times over.

Fixes:

Create a clustered index on the table. CardTransactionId looks like the only sensible choice currently.
You desperately need memory. 128GB would be reasonable for an 82GB data warehouse.
You have woefully inadequate IO. SSDs would the cheapest fastest fix.

11GB does not fit into 6GB, it really is that simple. A very rough estimate suggests the table will occupy ~1.5million 8KB pages which given 100 IOPS would take approximately 4 hours to read from disk (assuming worst case, 100% random read, no read-ahead etc).


Oracle 11g - Spool file - enforcing not quoted columns

set SQLFORMAT is a SQLcl option.

I am not aware of any option in SQLcl that removes the quotes.

SQL*Plus starting with version 12.2 supports csv output with the SET MARKUP option:

SQL> set markup csv on
SQL> SELECT * FROM PRODUCT_COMPONENT_VERSION;

"PRODUCT","VERSION","VERSION_FULL","STATUS"
"Oracle Database 19c Enterprise Edition ","19.0.0.0.0","19.3.0.0.0","Production"

You can simply disable quotes if needed:

SQL> set markup csv on quote off
SQL> SELECT * FROM PRODUCT_COMPONENT_VERSION;

PRODUCT,VERSION,VERSION_FULL,STATUS
Oracle Database 19c Enterprise Edition ,19.0.0.0.0,19.3.0.0.0,Production

Setting a custom delimiter:

SQL> set markup csv on delimiter | quote off
SQL> SELECT * FROM PRODUCT_COMPONENT_VERSION;

PRODUCT|VERSION|VERSION_FULL|STATUS
Oracle Database 19c Enterprise Edition |19.0.0.0.0|19.3.0.0.0|Production


This is to do with a libpcap dependency that mongosniff has, to explain why this is the case, take a look here and here for the history.

At the moment, that means that you need to go to the MongoDB downloads page and get the mongosniff binary from one of the tarballs provided there.  For 64-bit Linux, version 2.4.6 specifically, you would want this version, though there is no specific reason not to use a later version of the mongosniff tool with 2.4.6

I would also note that Wireshark has MongoDB dissectors built in, so you can also use that to read/view a standard tcpdump of MongoDB traffic as an alternative.  For an example, take a look at my Q&A over on Serverfault related to filtering:

https://serverfault.com/a/521966/108132


MySQL: SELECT with SUM() and COUNT() as new columns


Downgrading PostgreSQL is not supported.

You may get lucky and it may work if you have a simple database.
Your way is the best way; test the result as well as you can.


What is the best practice to setup the file share for data synchronization? Network path or local machine

You have to really read up on AlwaysON. AlwaysON ships only log blocks. So there is no fileshare concept.

On the windows cluster, you can set a witness as fileshare. So, the witness (disk or fileshare) is not AlwaysOn specific. It is required by the Windows Server Failover Cluster to maintain quorum during node failures. Check What exactly is a File Share Witness and when should I use one?

Some good references to get you started :

AlwaysOn Availability Groups Quiz and FAQ [Video] - by Brent Ozar.
Overview of AlwaysOn Availability Groups
Creation and Configuration of Availability Groups


I think the first level of arrays is not useful, especially if you are going to retrieve permissions by name.

I would store it like this:

CREATE TABLE forms (id serial, form jsonb);
INSERT INTO forms (id, form) VALUES
(1, '{"permissions":
         {"perm-name-1": {"who": ["group1","group2"]},
          "perm-name-2": {"who": ["group1","group3"]}}
     }'),
(2, '{"permissions":
         {"perm-name-1": {"who": ["group5","group6"]},
          "perm-name-2": {"who": ["group7","group8"]}}
     }');

Then both your queries are quite easy to write:

For the first one you can use the "any" operator ?|:

select *
from forms
where form -> 'permissions' -> 'perm-name-1' -> 'who' ?| array['group1', 'group5']

For the second one, the ? operator is enough:

select *
from forms
where form -> 'permissions' -> 'perm-name-1' -> 'who' ? 'group1'

You didn't ask for it, but you can also query for rows where a specific permissions contains all specified groups. That can be done using the ?& operator. The following would return the row with ID=1

select *
from forms
where form -> 'permissions' -> 'perm-name-1' -> 'who' ?& array['group1', 'group2'];

The following would return nothing:

select *
from forms
where form -> 'permissions' -> 'perm-name-1' -> 'who' ?& array['group2', 'group3'];


Select query with custom column and value of other column

The answer Is Concat

You can use concat to combine an array and value of column

example query:

Select id, name, emp_id, concat("Work of ", name) as process, from table1


Is it possible to deny RESTORE permissions to login with db_owner role membership?

The Microsoft RESTORE statement Docs say this about database permissions:

If the database being restored does not exist, the user must have CREATE DATABASE permissions to be able to execute RESTORE. If the database exists, RESTORE permissions default to members of the sysadmin and dbcreator fixed server roles and the owner (dbo) of the database.
RESTORE permissions are given to roles in which membership information is always readily available to the server. Because fixed database role membership can be checked only when the database is accessible and undamaged, which is not always the case when RESTORE is executed, members of the db_owner fixed database role do not have RESTORE permissions.

I'd suggest providing the person who requires db_owner level permissions be a separate login from actual database owner.  Membership in the db_owner database role prevents that user from performing a restore.
Logins can be prevented from performing RESTORE DATABASE by denying them CREATE ANY DATABASE permission.
As a test, I did this on my SQL Server 2008 R2 instance.
Create a test login:
USE master;
GO
IF NOT EXISTS (SELECT 1 FROM sys.server_principals sp WHERE sp.name = 'Test_db_owner')
BEGIN
    CREATE LOGIN Test_db_owner WITH PASSWORD = 'awsdaqwer0987)(FA&897afda2345';
END
GO

Create a test database:
IF EXISTS (SELECT 1 FROM sys.databases d WHERE d.name = 'Test_db_owner')
BEGIN
    ALTER DATABASE Test_db_owner SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
    DROP DATABASE Test_db_owner;
END
CREATE DATABASE Test_db_owner;
GO

Create a user in the test database for the login above, and add the user to the db_owner database role:
USE Test_db_owner;
CREATE USER [Test_db_owner] FOR LOGIN [Test_db_owner];
EXEC sys.sp_addrolemember @rolename = 'db_owner', @membername = 'Test_db_owner';
GO

Create a backup of the database:
USE master;
BACKUP DATABASE Test_db_owner TO DISK = 'D:\SQLServer\MV\Backup\Test_db_owner.bak' WITH INIT;

Test the restore statement as the login:
EXECUTE AS LOGIN = 'Test_db_owner';
RESTORE DATABASE Test_db_owner FROM DISK = 'D:\SQLServer\MV\Backup\Test_db_owner.bak'
    WITH REPLACE, RECOVERY;
REVERT;

Output:
Msg 3110, Level 14, State 1, Line 25
User does not have permission to RESTORE database 'Test_db_owner'.
Msg 3013, Level 16, State 1, Line 25
RESTORE DATABASE is terminating abnormally.
Cleanup:
DROP LOGIN Test_db_owner;
DROP DATABASE Test_db_owner;

FYI, the database owner is changed like this:
ALTER AUTHORIZATION ON DATABASE::[xyz] TO [login_name];

(or with sys.sp_changedbowner, which is deprecated)
Membership in the db_owner role is granted like this:
ALTER ROLE [db_owner] ADD MEMBER [login_name];

Although both statements seem to affect the "database owner", that is not the case; only the ALTER AUTHORIZATION statement can modify which login owns the database.  See this excellent answer by Remus Rusanu.
If you want to allow the login to restore databases in the non-production environment, make them a member of the dbcreator server role:
EXEC sys.sp_addsrvrolemember @loginame = 'Test_db_owner'
    , @rolename = 'dbcreator';


Approaches for handling nvarchar sources to data warehouse

In SQL Server with Table and Index Compression (Row, Page or Columnstore) NVarchar columns don't use two bytes per character.  They use Unicode Compression.

Here's an example:

use master
go
drop database TestUnicodeCompression
go
create database TestUnicodeCompression

go
use TestUnicodeCompression
drop table if exists A
drop table if exists B
go
create table A(id int identity, a nvarchar(200) default cast(newid() as nvarchar(200) ))

create table B(id int identity, a varchar(200) default cast(newid() as varchar(200)))

go

with q as
(
  select top 1000000 row_number() over (order by (select null)) i
  from sys.objects o, sys.columns c, sys.columns c2
)
insert into A(A) select cast(newid() as varchar(200))
from q;

with q as
(
  select top 1000000 row_number() over (order by (select null)) i
  from sys.objects o, sys.columns c, sys.columns c2
)
insert into B(A) select cast(newid() as varchar(200))
from q;

go
alter table A rebuild with (data_compression=none)
alter table B rebuild with (data_compression=none)
go

select 'before compression' state,
       cast( object_name(object_id) as varchar(10)) table_name,
       used_page_count
from sys.dm_db_partition_stats
where object_id in (object_id('A'), object_id('B'))

go

alter table A rebuild with (data_compression=page)
alter table B rebuild with (data_compression=page)

go

select 'after compression' state,
        cast( object_name(object_id) as varchar(10)) table_name,
        used_page_count
from sys.dm_db_partition_stats
where object_id in (object_id('A'), object_id('B'))

outputs

(1000000 rows affected)

(1000000 rows affected)
state              table_name used_page_count
------------------ ---------- --------------------
before compression A          11114
before compression B          6581

(2 rows affected)

state             table_name used_page_count
----------------- ---------- --------------------
after compression A          6210
after compression B          6062

(2 rows affected)


UPDATE table2 AS b1, ( SELECT b.id, MIN(IFNULL(a.views, 0)) AS counted
                       FROM table1 a
                       JOIN table2 b ON a.id = b.id
                       GROUP BY id
                       HAVING counted > 0 ) AS b2
SET b1.number = b2.counted
WHERE b1.id = b2.id


Replication in same server within different databases. Mysql 5.6

Cause it's on the same server, why don't you use a trigger for this?

If you just wanna replicate data you can add a triger on update or on insert or both whatever you need. This way you can use your condition easily. Master/Slave replication would be better for different servers.

You can look here for mysql Trigger examples.
And here is a sample for your case.


Data Warehouse - DB2 to SQL Server: how & what impacts on ETL?

One thing is hardware resources. The current server will use a certain amount of memory, CPU and IO to do its job. For a 1TB database this is likely to be non-trivial. If you move that work to a new server it is still going to need (more-or-less) the same amount of memory, CPU and IOPS. If the new server is already hosting applications and serving their needs it may not have capacity to also accommodate the new needs of the migrated application. Result: everything runs slowly; sadness abounds.

DW work tends to use big sequential reads. OLTP work usually has small random IO. If you mix these two on one hardware box it could prove tricky to configure the storage (SAN array?) for best performance for either, likely resulting in mediocre performance for both.

Each instance of SQL Server has a fixed overhead, such as tempdb and minimum memory. The more instances there are on one box the more overhead has to be paid for. Also you'll likely have to over allocate resources to each instance to accommodate peak load, again costing more. With everything in a single instance this cost can be spread. The flip side is that it can be more work to isolate normal work from a greedy task on the same instance, but not impossible. Making this decision would be a consultancy engagement, not a Q&A post.


MySQL - Blank fields in tab-delimited .txt file causing errors in LOAD DATA INFILE

Use SET form of LOAD DATA INFILE (load the whole line into a user-defined variable and then parse it into separate fields).

Example:

LOAD DATA INFILE 'The/path/to/my/file.txt'
INTO TABLE my_table
IGNORE 1 ROWS
( @temp )
SET @count = LENGTH(@temp) - LENGTH(REPLACE(@temp, '\t', '')),
    id = CASE WHEN @count > 0
              THEN SUBSTRING_INDEX(@temp, '\t', 1)
              ELSE NULL END,
    field1 = CASE WHEN @count > 1
                  THEN SUBSTRING_INDEX(SUBSTRING_INDEX(@temp, '\t', 2), '\t', -1)
                  ELSE DEFAULT() END,
    field2 = CASE WHEN @count > 2
                  THEN SUBSTRING_INDEX(SUBSTRING_INDEX(@temp, '\t', 3), '\t', -1)
                  ELSE CURRENT_TIMESTAMP END,
    /* and similar expressions for all another fields */

The problem is that a record is inserted always - ever if a line is empty. So you need to remove those "empty" records after importing, and set some correct literal values to NOT-NULL fields which do not have default value.

  Depending on the column, it may be of type Date or Int.

You can easily create unique parsing statement for each separate field dependent by its data type. Like

fieldX = CASE WHEN @count > X
              THEN some_parsing_function_or_expression(SUBSTRING_INDEX(SUBSTRING_INDEX(@temp, '\t', X+1), '\t', -1))
              ELSE {default value proper for field's data type} END,

or the same using intermediate variable

@tmpX = CASE WHEN @count > X
             THEN SUBSTRING_INDEX(SUBSTRING_INDEX(@temp, '\t', X+1), '\t', -1))
             ELSE 'default value proper for field data type' END,
fieldX = CASE WHEN @tmpX = 'default value proper for field data type'
              THEN @tmpX
              ELSE some_parsing_function_or_expression(@tmpX) END,


DBeaver supports any database that has a JDBC driver. So, the best thing to do is to go to the SQLite people themselves - they have a list of such drivers here. The page itself is quite old, so don't worry about the dates - just check around and see which one best suits your needs/is supported. Just as a matter of interest, SQuirreL SQL also works via JDBC - it's pure Java and is, IHMO, more mature than DBeaver, but YMMV.


Just use SET DATEFIRST, e.g.

set datefirst 1
go


Two things sprint to mind:

The process could be partly CPU bound on a single thread. You don't state what you CPU resource is, but if you have a 4-core arrangement then it coudl be that the ~25% use you are seeing is a single thread bouncing between the cores. Similarly with 8 cores it could be using 2x parallelism so two cores worth of CPU resource which is 25% of the total available. Hyperthreading may confuse this further as the virtual cores exposed by HT do not perform completely independently.
In this case your CPU is the bottleneck and trying to improve the concurrency of your operation may be the solution.
The process may not be streaming effectively so during the "read something, do something, write something" process there will be no disk activity during the "so something" phase. Often processes do not try to buffer I/O to improve this situation because adding the extra buffering layer is extra complication for (sometimes) minimal gain, it requires more memory, and it is less friendly to other concurrent processes. In comparison to your file copy test: there is almost nothing by way of "do something" stage there.
Again increasing the concurrency of your process may help a lot here.

A lot of processes like this are still optimised for spinning-metal in mind, so they will avoid concurrency where it would create I/O contention because with disc+head drives this can harm performance a lot more than it helps - addding the extra processing threads woudl increase CPU utilisation but can make the discs much more of a bottleneck due to head thrashing. I/O contention is much less of a problem with solid state drives (particularly for read operations) with SSDs because you don't have the flapping heads problem so you can increase the concurrency of I/O operations for benefit rather than detriment.

If your ETL tool can't be told to increase concurrency, or the dependencies in your overall process mean further concurrency is not even possible, the if you have several such processes to run try running more than one instance of your ETL tool at once.


Is there any other way to replace cursor for the following scenario

Insert into IA
 select T1.id1,T1.status,T1.details from
IA T1 where not exists (
Select 1 from Snapshot_IA WHERE
T1.id1 = T2.ID1
and T2.status = 'close')


ACE.OLEDB.12 installed but everything has a 0 on sp_msset_oledb_prop

You may need to run commands to enable them.

   EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'AllowInProcess', 1
GO
   EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'DynamicParameters', 1
GO
   EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'NestedQueries', 1
GO
   EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'NonTransactedUpdates', 1
GO
   EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'SqlServerLIKE', 1
GO


SQL Server - Logical Reads lowered, Execution time remained the same

some 7 seconds for 500k to return, and also a lot of time to render the grid you're likely displaying the results in.

You are waiting 7 seconds because that's how much it takes for SQL to push 500k rows to your client. Look at client statistics in your SSMS, see Database Engine Query Editor:

Include Client Statistics: Includes a Client Statistics window that contains statistics about the query and about the network packets, and the elapsed time of the query.
SQL Server Execution Times: CPU time = 47 ms, elapsed time = 769 ms.

Your actual query executes in 47ms. Elapsed time is much longer (still under 1 second) because of network waits. You can confirm this using wait stats analysis, read How to analyse SQL Server performance for details, including how to capture the query wait stats.
Ultimately, the problem is returning 500k rows to the client. There cannot be any reason for such operation, no human user can comprehend half a million rows. Process data on the back end.

Half a million rows is used since it is easier to notice speed changes with more than less data

Well, in this case you have sent yourself on a snipe hunt. There is no problem, other that one of your own doing in marshaling and rendering 500k rows. Is a completely bogus scenario, no app should retrieve 500k rows. And processing of 500k rows(eg. aggregates) should tests... the processing, including the aggregates.


I hate to say this, but there's a bug in Books Online. The Books Online page on partition switching says:

  Source and target tables must have the same column structure and
  order. The tables must have the same columns with the same names and
  the same data type, length, collation, precision, scale, nullability,
  and PRIMARY KEY constraints (if any), and also have the same settings
  for ANSI_NULLS and QUOTED IDENTIFIER.

But as you've discovered ANSI_PADDING is also involved - both the incoming & outgoing tables have to have the same setting. There is no way to fix this after the data is loaded.


Having a VARCHAR as a part of an index would be cumbersome to manage although doable.

I would design the tables as follows

CREATE TABLE folderkeys
(
    id INT NOT NULL AUTO_INCREMENT,
    userid INT NOT NULL,
    foldername VARCHAR(260),
    PRIMARY KEY (id),
    UNIQUE KEY (userid,foldername)
) ENGINE=MyISAM;
CREATE TABLE datatable
(
    id INT NOT NULL AUTO_INCREMENT,
    folderkey_id INT NOT NULL,
    filename VARCHAR(1024),
    PRIMARY KEY (id),
    KEY (folderkey_id)
) ENGINE=MyISAM;

This will make the datatables indexes as character-compacted as possible

This will make it as easy doing queries like

INSERT IGNORE INTO folderkeys (userid,foldername) VALUES (1,'first');
SELECT id INTO @fldrkey FROM folderkeys WHERE userid=1 AND foldername='first';
INSERT INTO datatable (folderkey_id,filename) VALUES (@fldrkey,'...');

If you want the filename from userid 1 foldername 'first', you would do queries like this

SET @GivenUserID = 1;
SET @GivenFolder = 'first';
SELECT A.userid,A.foldername,B.filename FROM
(SELECT id FROM folderkeys WHERE userid = @GivenUserID AND foldername=@GivenFolder) A
INNER JOIN datatable B ON A.id = B.folderkey_id;

If you want filenames from all folders for userid 27, you would do queries like this

SET @GivenUserID = 27;
SELECT A.userid,A.foldername,B.filename FROM
(SELECT id FROM folderkeys WHERE userid = @GivenUserID) A
INNER JOIN datatable B ON A.id = B.folderkey_id;

Give it a Try !!!

I would normally recommend using InnoDB. Since you have lots of BLOB data, I'd rather not spend time trying to tune your log buffer and log files sizes. I would go with MyISAM for now.

BTW I gave you the tables with Option A in mind


Spool in Shell script not working for me

I took some code snippets from a backup script that I use.
You can redirect all output with the exec command, then
redirect it back. You can also surround everything with
braces and redirect that to a log file.

Personally I think bash is a better shell to program in,
if you don't have bash ksh works too.

#!/bin/bash

export MY_LOG_FILE=my_log_file.log

exec >${MY_LOG_FILE}
exec 2>&1

export ORACLE_BASE=/home/oracle/admin
export ORACLE_HOME=/software/oracle/product/11.2.0
export TNS_ADMIN=${ORACLE_HOME}/network/admin
export LD_LIBRARY_PATH=${ORACLE_HOME}/lib:${LD_LIBRARY_PATH}
export ORACLE_SID=mysid

echo "Started at" `date +%Y/%m/%d" "%H:%M:%S`

sqlplus -s ${ORA_LOGIN_SYSADM} <<EOF
SET SERVEROUTPUT ON
SET FEED OFF
BEGIN
   DBMS_OUTPUT.PUT_LINE('xyz');
END;
/
EOF

exec 1>&-
exec 2>&-
exec >/dev/tty
exec 2>&1

if [ `egrep -n "ORA-|RMAN-" ${MY_LOG_FILE}` -ne 0 ]
then
   echo " ech.sh sql   Error"
   egrep -n "ORA-|RMAN-" ${MY_LOG_FILE}
   exit 1
else
   echo " ech.sh  Successful"
fi


How assigning a value to Variable is working in SSRS

SSRS isn't doing anything special.  It's actually the query that's making the distinction to either pull all fuel types or just the one specified, though I suspect the book has a typo because it looks to be missing an extra pair of parenthesis.  The real magic is supposed to be happening in the predicate, here:

WHERE  (YEAR(FuelPrice.PriceStartDate) = @year) AND (Propulsion.Description = @PropulsionType)
   OR  (YEAR(FuelPrice.PriceStartDate) = @year) AND (@PropulsionType = 'All')

This really should be written in one of the two following ways:

WHERE  ((YEAR(FuelPrice.PriceStartDate) = @year) AND (Propulsion.Description = @PropulsionType))
   OR  ((YEAR(FuelPrice.PriceStartDate) = @year) AND (@PropulsionType = 'All'))

Or, another way:

WHERE (YEAR(FuelPrice.PriceStartDate) = @year)
 AND  ((Propulsion.Description = @PropulsionType)
        OR  (@PropulsionType = 'All'))

Basically what this is doing is in the event  your report is passed the 'All' parameter, don't filter on Propulsion.Description.  If a parameter is passed that is not 'All', you want to filter on that specific value.  Taking SSRS out of it, you can see the working example via this dbfiddle.uk link.


School marks database with "mark categories"

Marks are related to categories and subjects, so you need a SubjectId column in the Marks table so that you know what subject each record relates to.

CREATE TABLE [dbo].[Marks] (
    [Id] INT IDENTITY (1, 1) NOT NULL,
    [CategoryId] INT NOT NULL,
    [SubjectId] INT NOT NULL,
    [Name] NVARCHAR(MAX) NOT NULL,
    [Value] DECIMAL(18,3) NOT NULL
)

CREATE TABLE[dbo].[Categories] (
    [Id] INT IDENTITY (1, 1) NOT NULL,
    [Name] NVARCHAR(MAX) NOT NULL,
    [Average] decimal(18,3) NOT NULL
)

CREATE TABLE[dbo].[Subjects] (
    [Id] INT IDENTITY (1, 1) NOT NULL,
    [Name] NVARCHAR(MAX) NOT NULL,
    [Teacher] NVARCHAR(MAX)  NOT NULL,
    [Average] decimal(18,3) NOT NULL,
)

It seems as if you were envisioning creating separate category records for every subject (i.e., you would have a tests category for writing, and a tests category for math), but you should just need one category of each type of category (tests, written, lab, etc.).  The categories get tied to the subjects by the marks table, which links to both of the other tables.


How can I execute a stored procedure whose name is in a variable?

You can use the variable directly:

exec @stored_procedure_name;

Please see the BOL Reference on EXECUTE.


mysqlcheck: unknown variable 'max_allowed_packet=100M'

According to this bug report (resolved as "not a bug"), you shouldn't put settings for mysqld in the [client] section of your .ini file.

Put the max_allowed_packet option in the [mysqld] section instead.

See Using Option Files for more details.


SQL Server 2014 - Strange SP behavior during compilation

The phenomenon you are experiencing is called Deferred Name Resolution.

  When a stored procedure is created, the statements in the procedure
  are parsed for syntactical accuracy. If a syntactical error is
  encountered in the procedure definition, an error is returned and the
  stored procedure is not created. If the statements are syntactically
  correct, the text of the stored procedure is stored in the
  sys.sql_modules catalog view.

  When a stored procedure is executed for the first time, the query
  processor reads the text of the stored procedure from the
  sys.sql_modules catalog view and checks that the names of the objects
  used by the procedure are present. This process is called deferred
  name resolution because table objects referenced by the stored
  procedure need not exist when the stored procedure is created, but
  only when it is executed.

  > Deferred name resolution can only be used when you reference
  nonexistent table objects. All other objects must exist at the time
  the stored procedure is created. For example, when you reference an
  existing table in a stored procedure you cannot list nonexistent
  columns for that table.

The only way I know around this problem would be to use dynamic SQL.


Invalid directory path error while connecting to database

I already had that problem. The thing is that Oracle saves your objects in Uppercase. When you use the FOPEN, you MUST use directory name in uppercase as well. Try this:

v_FileHandle := UTL_FILE.FOPEN ('LOG_DIR','Lect.csv', 'r');


Can UPDATE lead to deadlocks in SQL SERVER?

Here, my assumption is that each query may scan rows in a different order which implies locks on rows (resources) will be taken in different orders.

The scenario, as you've outlined it, is unlikely under normal circumstances.  The exact same query being run concurrently will most likely use the same execution plan - which means they will both scan the index on TABLEX in the same order.  This situation would most likely lead to blocking rather than deadlocks between the two identical queries.
This assumes you have an index on Column2 that can be used to seek to the right spot, and then scan the subset of rows that need to be updated.
If you don't have an index on Column2, you could end up with a parallel scan of the index / table, and thus your updates could be in an indeterminate order.  Consider a plan like this:

The order of rows coming out of the clustered index scan will vary depending on how many cores are being used by the plan, and how busy each core is doing other work - as well as disk-related waits and things like that.  Unless the "gather streams" is order-preserving, the rows will be updated in a sort of random order.  This could lead to deadlocks as you've described.
In summary, the answer to your question depends on several factors, including the details of your table / indexes, isolation levels, server and database level settings, etc.  Hopefully this gives you some idea of what's involved though.


Placing Transaction Log on Separate Volume [solid state]?

Short answer, use a single array, there is unlikely to be any performance gain from separating logs from data across 8 SSD drives. See SQL on SSDs: Hot and Crazy Love for a more detailed (and entertaining) commentary on SSDs. Pay particular attention to the notes on correlated failures of SSDs.

Separating logs from data on SSDs is more an RPO (recovery point objective) than performance issue. The notion being that you could reduce your RPO by separating logs from data such that in the event of the data array failing, your log array should/could remain accessible. The cautious would consider a different make/model of drive in each of the two arrays to mitigate the correlated failure issue if RPO was critical.

The comments regarding bus bandwidth are irrelevant. If you need to shift that much IO, you've got bigger issues to worry about.


Oracle SQL Developer equivalent of "SELECT TOP xxx ROWS" in SQL Server

There are two parts to this.

RDBMS side
Client side (SQL*Developer)

Client Side

In SQL*Developer, you can drag+drop the table from the Connection to the worksheet in order to get a SELECT statement.  A blog on it is here.

If you use the Query Builder, you can drag+drop multiple tables and SQL*Developer will create the JOIN syntax based on defined FK.
ThatJeffSmith-Oracle goes over the speed improvements for 18.2 here.

In both cases, the Top n rows clause is not part of the resulting SQL statement.

RDBMS

Oracle database introduced a TOP n ROW clause in 12c.

Example:  https://oracle-base.com/articles/12c/row-limiting-clause-for-top-n-queries-12cr1


Will more indexes on a table affect performance?

An INSERT will touch the clustered index, or heap, and every nonclustered index.  An UPDATE will touch the clustered index, or heap, and every nonclustered index that contains, at either the key or the leaf level, the column being updated.  A DELETE will touch the clustered index, or heap, and every nonclustered index.  An exception to these statements is when a record is filtered out of the index.

A general rule of thumb is that the more indexes you have on a table, the slower INSERT, UPDATE, and DELETE operations will be.  This is why adding indexes for performance is a trade off, and must be balanced properly.

Hope this helps,

Matt


do single AZ AWS RDS instances self heal?

AWS will restart the database if the host has a problem .

"Automatic Host Replacement
Amazon RDS will automatically replace the compute instance powering your deployment in the event of a hardware failure."

I don't know if AWS can guarantee in case of hardware failure that you have all transactions. At least having automatic backup you have the status less than 5 min before the failure.

See https://aws.amazon.com/rds/details/


Cleanup / Prune Unreferenced Data in Dimension Tables

(Normally, I would say "don't bother".  But I see that your Dim table is nearly as big as the Fact table.  Hmmmm...  Perhaps it was unwise to even have the Dim table??  Think about that.)

For building the Used table (and assuming a Trigger handles incoming Inserts):

From the Fact table, build a list of useful values (Used).  Do this one chunk at a time, say, 1000 rows, as determined by the PRIMARY KEY.  This list goes into an extra table, Used.  One transaction per chunk.  De-dup and add a PK.  See my comments on chunking.

Now, walk through the Dim table, perhaps only 100 rows at a time, and do a multi-table delete, using LEFT JOIN Used ... WHERE ... IS NULL to determine what is not needed.

Be cautious -- the DELETE and the TRIGGER may step on each other.  That is, there may be deadlocks necessitating re-execution.  But 100 rows at a time should keep that at a nice balance between minimizing deadlocks and maximizing speed.  One transaction per chunk.

Yes, it will take time -- both for the discovery of Used and for the DELETEs.  But it should not matter.  Everything else is humming along, with only very rare and brief interference.

I guess the TRIGGER would need to insert into Used when a row is inserted into Dim.


@NathanJolly's comment about using privileges is really the correct answer, since if a user actually has the privileges to delete or update the table, they could likely find a way to work around the suggestions below.

The tactics discussed below should be considered a "soft" immutability implementation -- it's only really good for keeping honest people honest by preventing them from doing things they can but shouldn't.  It will not prevent deliberate malicious action by a user with too many permissions.

You can do this in a BEFORE UPDATE and BEFORE DELETE trigger.  It won't work properly in an AFTER trigger, since the work has already been done.

In MySQL 5.5 and above:

Prevent deletion:

mysql> CREATE TRIGGER t1_bd BEFORE DELETE ON t1 FOR EACH ROW SIGNAL
       SQLSTATE '45000' SET MESSAGE_TEXT = 'table `t1` does not support DELETE';

Attempts to delete will throw a fatal exception on the first row, stopping the query, and the row(s) in question will not be deleted.

mysql> DELETE FROM t1 WHERE id = 1;
ERROR 1644 (45000): table `t1` does not support DELETE

Prevent updates with a similar trigger:

mysql> CREATE TRIGGER t1_bu BEFORE UPDATE ON t1 FOR EACH ROW SIGNAL
       SQLSTATE '45000' SET MESSAGE_TEXT = 'table `t1` does not support UPDATE';

The MESSAGE_TEXT argument can take a string of up to 128 characters.

Prior to MySQL 5.5 you can throw an error by calling a procedure that doesn't exist.  The error message will be in the form:

ERROR 1305 (42000): PROCEDURE {schema}.{nonexistent-procedure-name} does not exist.

Given this bit of common structure, you can create a nonsense procedure name using backtick escapes that will form the first part of a sentence that makes sense when the phrase "does not exist" is appended to the error message by the server.

mysql> CREATE TRIGGER t1_bd BEFORE DELETE ON table_name FOR EACH ROW
       CALL `t1: DELETE capability for this table`;

This suggestion is bound to leave people scratching their heads until you see the error that it returns:

mysql> delete from t1 where id = 2;
ERROR 1305 (42000): PROCEDURE test.t1: DELETE capability for this table does not exist

lol... but it works.  Here, your "error message" content is limited to the maximum length of a stored procedure name, which is 64 characters.  The backticks make the spaces acceptable in the nonexistent procedure name.

Of course, if you're in 5.1 or previous and someone has privileges to actually create such a procedure, they can bypass this restriction by creating a procedure that does nothing, which will result in the trigger doing nothing and the delete/update succeeding.

In either case, if the user has permission to drop the triggers, the mechanism is easily bypassed in this way also... if a user can create tables, they could create a new table, INSERT ... SELECT from the old table to the new table, modify data as desired, rename the tables... so we're back to permissions as the correct solution again.

Caveat lector.


You can find the description for how to configure automatic compaction in section 3.7 of the documentation. The basic info regarding the configuration of CouchDB can be found in section 3.1.

As I have never turned of automatic compaction and am not sure why you want this, I think you can play around with the settings to achieve it. There is no attribute to set automatic compaction to false.

A word of warning: the basic principle of revisions (_rev) in CouchDB is to keep copies of documents for each revision. That means if you have a document sized 100 kb you will consume n * 100 kb disc space where n is the number of revisions. Please keep that in mind and be aware, that with many documents and many changes, your disk will be full very quickly.


Missing + after [[:digit:]]

select  regexp_replace('AAA 123 ppp','(^[[:alpha:]]+) ([[:digit:]]+) ([[:alpha:]]+)','\3 \1 \2')
from    dual

  ppp AAA 123


Will altering an nvarchar(max) column to nvarchar(50) lock the table?

There's no way for us to fully answer your question. Depends on way too many factors, including the table definition, other columns in the table, if the data is off page, the T-SQL generated by your ORM, and so on. The documentation is pretty good though:

  WITH ( ONLINE = ON | OFF)  Applies
  to: SQL Server 2016 through SQL Server 2017 and Azure SQL Database.

  Allows many alter column actions to be performed while the table
  remains available. Default is OFF. Alter column can be performed on
  line for column changes related to data type, column length or
  precision, nullability, sparseness, and collation.

  Online alter column allows user created and auto statistics to
  reference the altered column for the duration of the ALTER COLUMN
  operation. This allows queries to perform as usual. At the end of the
  operation, auto-stats that reference the column are dropped and
  user-created stats are invalidated. The user must manually update
  user-generated statistics after the operation is completed. If the
  column is part of a filter expression for any statistics or indexes
  then you cannot perform an alter column operation.

  While the online alter column operation is running, all operations
  that could take a dependency on the column (index, views, etc.) will
  block or fail with an appropriate error. This guarantees that online
  alter column will not fail because of dependencies introduced while
  the operation was running.

  Online alter column has similar requirements, restrictions, and
  functionality as online index rebuild. This includes:

  Online index rebuild is not supported when the table contains legacy
  LOB or filestream columns or when the table has a columnstore index.
  The same limitations apply for online alter column.

  An existing column being altered requires twice the space allocation;
  for the original column and for the newly created hidden column.

  The locking strategy during an alter column online operation follows
  the same locking pattern used for online index build.

In addition it's also pretty straightforward to test. I'm on SQL Server 2017 testing with the default read committed isolation level. First I'll create the table:

DROP TABLE IF EXISTS dbo.CONVERT_ME;

CREATE TABLE dbo.CONVERT_ME (
    ID BIGINT NOT NULL,
    OVERWEIGHT_COLUMN VARCHAR(MAX) NOT NULL,
    PRIMARY KEY (ID)
);

INSERT INTO dbo.CONVERT_ME WITH (TABLOCK)
SELECT TOP (1500000) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)), REPLICATE('Z', 50)
FROM master..spt_values t1
CROSS JOIN master..spt_values t2
OPTION (MAXDOP 1);

Then I'll alter the column OFFLINE, which is the default:

SET STATISTICS IO, TIME ON;

BEGIN TRANSACTION;

ALTER TABLE dbo.CONVERT_ME
ALTER COLUMN OVERWEIGHT_COLUMN VARCHAR(50) NOT NULL WITH (ONLINE = OFF);

My machine this takes about three seconds. The operation takes a schema modification lock during the entire transaction which prevents any other lock from being taken on it, including querying it with NOLOCK.

Now I'll try it with WITH (ONLINE = ON). The operation is now eligible for parallelism, so it may run faster on your server. A schema modification lock is still taken on the object, but it occurs near the end instead of throughout the whole transaction. Here's an example snapshot of the lock table to show the schema modification locks held during most of the transaction:

Note that an object level schema modification lock has not yet been taken. Here is what it looks like after the operation has finished but before the transaction has committed:

A schema modification lock on the object, along with a few others, are taken briefly near the end.

So great, there should be no problem if you use WITH (ONLINE = ON), right? As I said before the time the operation takes is going to depend on a lot of factors. Consider a different table which has many more data pages:

DROP TABLE IF EXISTS dbo.CONVERT_ME;

CREATE TABLE dbo.CONVERT_ME (
    ID BIGINT NOT NULL,
    OTHER_COLUMN VARCHAR(8000) NOT NULL,
    OVERWEIGHT_COLUMN VARCHAR(MAX) NOT NULL,
    PRIMARY KEY (ID)
);

INSERT INTO dbo.CONVERT_ME WITH (TABLOCK)
SELECT TOP (1500000) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)), REPLICATE('Z', 4200), REPLICATE('Z', 50)
FROM master..spt_values t1
CROSS JOIN master..spt_values t2
OPTION (MAXDOP 1);

On my machine the same ALTER TABLE now takes 39635 ms when run OFFLINE and 117877 ms when run ONLINE.

My advice to you is to read the documentation until you feel confident that you understand the locking considerations and to test with your real table's schema populated with real data before doing the switch in production.

PLEASE NOTE:

Not sure about Azure SQL Database, but in regular SQL Server the ONLINE option is only for Enterprise Edition. Attempting to do this in another edition will result in the following error:

  Msg 1712, Level 16, State 1, Line XXXXX
  Online index operations can only be performed in Enterprise edition of SQL Server.


SQL Server sp_BlitzCache - Many Plans for One Query

So close! Problem is you didn't actually read the page you linked to, which says:

  If you’d like to find more queries that sp_BlitzCache didn’t report as performance problems, this query will list all of them in the cache:

Run that query and bask in the warm knowledge that comes with reading - not just clicking on - the documentation. ;-)


Three tables with some matching data and some not - want to data from all three

Not sure if I'm completely following but maybe this is what you are looking for?

I'm taking all three tables and then using UNION ALL (combine but don't remove duplicate records) to convert them into a single table, placing NULL's in the columns that are not shared. Then I'm taking that consolidated table and grouping on your two common columns (JobNumber, CostCode). I'm not sure what kind of data you're looking to get out of the other three columns...are you looking for the COUNT of records from each table for that JobNumber+CostCode? Or maybe the SUM?

So this sample query, will give you a unique list of JobNumber+Costcode, and the Sum of Costs, Budgets and CommitedCosts for each one:

SELECT x.JobNumber, x.CostCode
    , Costs = SUM(x.Costs)
    , Budgets = SUM(x.Budgets)
    , CommitedCosts = SUM(x.CommitedCosts)
FROM (
    SELECT JobNumber, CostCode
        , Costs
        , Budgets = NULL
        , CommitedCosts = NULL
    FROM Costs
    UNION ALL
    SELECT JobNumber, CostCode
        , Costs = NULL
        , Budgets
        , CommitedCosts = NULL
    FROM Budgets
    UNION ALL
    SELECT JobNumber, CostCode
        , Costs = NULL
        , Budgets = NULL
        , CommitedCosts
    FROM CommitedCosts
) x
GROUP BY x.JobNumber, x.CostCode


PostgreSQL Trigger to Update Inserted Row With Data From Another Table

Most likely you're going in wrong direction but I have only gut feelings for that.
But, you can play with triggers - maybe it will do a work. However it seems that you will have massive updates in each trigger call because you are updating all rows from sensor with new label, which seems just wrong.
As I'm not sure about your specific needs, I will just push you in most proper way (I hope) with these changes:

CREATE OR REPLACE FUNCTION update_name()
RETURNS trigger AS
$$
BEGIN
    IF TG_OP = 'INSERT' THEN
        INSERT INTO sensor(datavalue, created, label_id, label_name)
        SELECT NEW.datavalue, NEW.created, NEW.label_id, b.label_name
        from labels b
        where new.label_id = b.label_id;

    ELSIF TG_OP = 'UPDATE' THEN
        UPDATE sensor
        SET label_name = b.label_name
        from labels b
        where new.label_id = b.label_id;

    END IF;
    RETURN NULL;

END
$$  LANGUAGE plpgsql;

CREATE TRIGGER name_update_trigger
BEFORE INSERT OR UPDATE ON sensor
FOR EACH ROW
WHEN (pg_trigger_depth() < 1)
EXECUTE PROCEDURE update_name();

Here, you do these changes instead (before) insert or update. Insert will be proper, update needs to be adjusted or removed as from your description it seems like you will be making only inserts here.
And please note that there is limiter as proposed here https://dba.stackexchange.com/a/103661/213360.


MYSQL primary key strategy for microservices architecture

Corrected:

with MySQL You can store GUID/UUID data as char/varchar in case of UUID() and UNSIGNED INTEGER in case of UUID_SHORT()

My personal vision - UUID_SHORT not working case, it is unsigned INT same as Auto-Increment and You can read restrictions in function description.

UUID() will return result as utf8 string - "eabd691c-b552-11e6-942e-ee8ae5dda6a8"

Business logic for primary key - work well, You always mange this process and do not need spend time for call function.

add after question in comment:

performance not depend from business logic or auto increment, it depend mostly from 2 parameters:

size of column
function duration time

INT (BIGINT) always will be smaller than CHAR(36) - as result less memory usage, more data in memory for index recreate operations (such as massive insert)

Increment will be faster any other form of random generator in 99% of cases

So in case of TAXNUMBER as primary key:

advantage - no any function for generate this type of key
disadvantage - most of them will take more memory than INT (BIGINT)

Add some visual examples:
make a proper test it very hard, but this is small example:
was created 3 tables, with only 1 column ID - primary key

UNSIGNED INT AUTOINCREMENT
BINARY(16)
VARCHAR(36)

was created stored procedure which insert 11M of records in each table, time results, very close:

11m41s - INT
12m40s - UUID() -> binary(16)
12m40s - UUID() -> varchar(36)

with time difference will need adjust server settings
but if we check size on disk (tables include only 1 column, PK)

-rw-r-----  1 _mysql  _mysql   1.1G 29 Nov 01:00 t__binary.ibd
-rw-r-----  1 _mysql  _mysql   268M 29 Nov 01:01 t__uint.ibd
-rw-r-----  1 _mysql  _mysql   1.6G 29 Nov 01:00 t__varchar.ibd

we can see the difference, and as bigger will be data, as much more will be difference


db2 connecting to a db via alias and storing user name and password

DB2 won't store your username and password - neither the server, nor the client. What you could do is set up an ODBC setting in Windows. (I am assuming you are using Windows given you mention Excel, though I could be incorrect in that). The ODBC setting allows you to store the username and password on that Windows system. Do be careful with that though as Windows will warn you that the password isn't stored the most securely.


Oracle Database: View schema without access to data

I'd create a procedure returning all tables/columns and grant access to that procedure:

  create or replace package pkg_ddl_read as

    procedure get_table_cols(v_cur in out sys_refcursor);

  end;

  create or replace package body pkg_ddl_read as

    procedure get_table_cols(v_cur in out sys_refcursor) is
    begin
      open v_cur for
        select owner, table_name, column_name
        from dba_tab_cols;
    end;

  end;

  grant execute on pkg_ddl to <myuser>;

Depending on what privileges the creating user (not the user you want to give access to) has and whether you want to allow the reading user to get the DDL for every table in the database or only for a certain subset, you might want to

use all_tab_cols / user_tab_cols instead of dba_tab_cols
add a where clause to the procedure


MS SQL Server installation fails with error message

There is an entire directory of logging done by the installer. If you are getting this from the graphical popup try looking in the \Setup Bootstrap folder under your installation of Sql Server. This is created whether it succeeds or not. The path is something like so...

C:\Program Files\Microsoft SQL Server\110\Setup Bootstrap\Log

In there you will find logs in separate folders by date and time. Like so..

\20160502_124028

Start with the Detail.txt log going all the way to the bottom and work your way up looking for anything with the word 'error' in it. That should give you more information about what is happening.

Also, I do see an access violation in the middle of your stack. That is:
HResult : 0x80004005

Hi Sofia,
     The detail.txt is a good place to start. It does refer to the file that was inaccessable. It is: "C:\Program Files\Microsoft SQL Server\MSSQL11.SQLEXPRESS12\MSSQL\Binn\perf-MSSQL$SQLEXPRESS12sqlctr.ini" at the very beginning of the stack.

Whether or not this is the issue with the other editions of Sql Server you attempted to install I couldn't say. However if you aren't logged in as local Administrator while trying the install you should log in with the local Administrator account and retry the install.


How to apply TagName for all backup files

Specify the tag for the database backup as well:

BACKUP AS COMPRESSED BACKUPSET DATABASE TAG MyTAG PLUS ARCHIVELOG TAG MyTAG;

Ok, I tested this, this does not add the specified tag to the controlfile backup. The below creates an additional controlfile backup and adds the specified tag to that as well:

BACKUP AS COMPRESSED BACKUPSET DATABASE TAG MyTAG INCLUDE CURRENT CONTROLFILE PLUS ARCHIVELOG TAG MyTAG;


Query for sub-array in multi-dimentional array

Here is one way in Postgres 9.3+ with generate_subscripts():

SELECT a.*
FROM   array_test a
JOIN   LATERAL generate_subscripts(a.array_column, 1) i
               ON a.array_column[i:i] = '{{1,2,3}}'::int[];

This way I am comparing whole slices of a 2-dimensional array. Note the double curly braces!: '{{1,2,3}}'. LATERAL and a join condition is probably the shortest syntax. If your slices are not unique within each array, rather use an EXISTS semi-join to get unique rows in the result (also works with older versions):

SELECT a.*
FROM   array_test a
WHERE  EXISTS (
   SELECT 1
   FROM   generate_subscripts(a.array_column, 1) i
   WHERE  a.array_column[i:i] = '{{1,2,3}}'::int[]
   );

If you are unfamiliar with the Postgres array implementation, this may seem contrived. Read on ...

Defining a column as integer[][] (signifying a 2-dimenstional array) is effectively the same as just integer[], because Postgres does currently not differentiate array data types with different dimensions. Each array type can have any number of dimensions as long as they match up internally.

Per documentation:

  The current implementation does not enforce the declared number of
  dimensions either. Arrays of a particular element type are all
  considered to be of the same type, regardless of size or number of
  dimensions. So, declaring the array size or number of dimensions in
  CREATE TABLE is simply documentation; it does not affect run-time
  behavior.

The simple expression:

WHERE 1 = ANY (array_column)

... tests for equality with any base element, ignoring dimensions completely. Per documentation

  The left-hand expression is evaluated and compared to each element of
  the array using the given operator.

Bold emphasis mine. It's just not possible to compare array slices this way.
Similar with array operators:

WHERE '{1,2,3}'::int[] <@ array_column

... would match for all arrays that include all of the base elements, again regardless of dimensions.

Per documentation:

  Array comparisons compare the array contents element-by-element, using
  the default B-tree comparison function for the element data type. In
  multidimensional arrays the elements are visited in row-major order
  (last subscript varies most rapidly). If the contents of two arrays
  are equal but the dimensionality is different, the first difference in
  the dimensionality information determines the sort order.

Bold emphasis mine.

Related older answer on SO with some other techniques:

PostgreSQL two dimensional array intersection


Oracle EM 11g - query to find out CPU utilization

This query runs on 11.1.0.7 Enterprise and provides similar results to the the OEM Grid performance page. Both require the diagnostics package.

SELECT TO_CHAR(SAMPLE_TIME, 'HH24:MI ') AS SAMPLE_TIME,
       ROUND(OTHER / 60, 3) AS OTHER,
       ROUND(CLUST / 60, 3) AS CLUST,
       ROUND(QUEUEING / 60, 3) AS QUEUEING,
       ROUND(NETWORK / 60, 3) AS NETWORK,
       ROUND(ADMINISTRATIVE / 60, 3) AS ADMINISTRATIVE,
       ROUND(CONFIGURATION / 60, 3) AS CONFIGURATION,
       ROUND(COMMIT / 60, 3) AS COMMIT,
       ROUND(APPLICATION / 60, 3) AS APPLICATION,
       ROUND(CONCURRENCY / 60, 3) AS CONCURRENCY,
       ROUND(SIO / 60, 3) AS SYSTEM_IO,
       ROUND(UIO / 60, 3) AS USER_IO,
       ROUND(SCHEDULER / 60, 3) AS SCHEDULER,
       ROUND(CPU / 60, 3) AS CPU,
       ROUND(BCPU / 60, 3) AS BACKGROUND_CPU
  FROM (SELECT TRUNC(SAMPLE_TIME, 'MI') AS SAMPLE_TIME,
               DECODE(SESSION_STATE,
                      'ON CPU',
                      DECODE(SESSION_TYPE, 'BACKGROUND', 'BCPU', 'ON CPU'),
                      WAIT_CLASS) AS WAIT_CLASS
          FROM V$ACTIVE_SESSION_HISTORY
         WHERE SAMPLE_TIME > SYSDATE - INTERVAL '1'
         HOUR
           AND SAMPLE_TIME <= TRUNC(SYSDATE, 'MI')) ASH PIVOT(COUNT(*)
  FOR WAIT_CLASS IN('ON CPU' AS CPU,'BCPU' AS BCPU,
'Scheduler' AS SCHEDULER,
'User I/O' AS UIO,
'System I/O' AS SIO,
'Concurrency' AS CONCURRENCY,
'Application' AS  APPLICATION,
'Commit' AS  COMMIT,
'Configuration' AS CONFIGURATION,
'Administrative' AS   ADMINISTRATIVE,
'Network' AS  NETWORK,
'Queueing' AS   QUEUEING,
'Cluster' AS   CLUST,
'Other' AS  OTHER))
ORDER BY 1

This query is not as good as what Grid displays but it's close.