Downgrading SQL Server 2008 R2 Standard to Web Edition

Unfortunately looking at the MS matrix you cannot do this.  You were able to earlier because evaluation versions don't have that lock, but once you go full edition it's locked out

http://msdn.microsoft.com/en-us/library/ms143393%28v=sql.105%29.aspx

You could try an approach taken here but proceed with extreme caution.
http://skreebydba.com/2012/11/16/downgrading-sql-server-enterprise-to-standard-with-scripts/

[edit]:
This URL has the scripts to recreate logins, etc. that Aaron pointed out
Just a headsup though, this doesn't script out jobs.  If you restore MSDB to recreate jobs, make sure to

UPDATE MSDB..SysJobs
SET Originating_Server = 'SQLServerName'

It will also work for downgrades to web.


How can delete an orphaned temporary table on Mysql Innodb?

Go into the filesystem (DOS prompt, shell prompt, whatever) and delete (rm, whatever) any files starting with #sql

Also check to see how much free space exists on disk.  ALTER usually copies the entire table over.


Archiving MSSQL Database and Retrieving the archived data for reporting

It sounds like whatever application is feeding this database is dumping tons of records in there if as you say there are 1 million transactions a day.  See if whatever is feeding this database has a retention feature.  If you have 1 million + records a day you are going to run out of space quickly.

There are a lot of factors involved in this type of process.  Size of the database, number of transactions, network speeds, storage space, etc...

If I were in your shoes at this moment I would restore a copy of your backup either to another instance or to the same instance if you have room.  I would truncate out all data that is not needed.  I would then create a SQL agent job that will insert the data into the new database tables with the GetDate() function and whatever offset time is needed and do it in a batch function of maybe 1000 per batch.

Here is a sample of one that I use.  It can be an insert or delete batch:

SET NOCOUNT ON;
DECLARE @r INT;
SET @r = 1;
WHILE @r > 0
    BEGIN
        BEGIN TRANSACTION;
        --This could be an INSERT or DELETE etc.
        DELETE TOP (1000)
        FROM dbo.application_log
        WHERE(DATEADD(MM, -6, GETDATE()) > date_created);
        SET @r = @@ROWCOUNT;
        COMMIT TRANSACTION;
    END;

After that is completed, then run a delete batch to remove the data from the primary database, which will purge the old data.

Keep in mind this is a very simplistic model of a data transfer and purge.  There are many ways to accomplish this, but require a greater knowledge of SQL and would take much longer to explain than we have time for here.

Hope that helps.


export and import all the permissions on schema objects within a database SQL Server 2008

There are a couple of answers to a similar question that might be of use to you.

How to export all the security-related information from a SQL Server database

The answer I believe is yes. You can use SSMS to generate scripts and have it filtered to just users.

Here is a webpage detailing how you would Create Script to Copy Database Schema and All The Objects.


If you set up Replication, you can lose the entire server, yet recover everything except anything that was "in progress" when the disaster hit.

That is, the Slave is always nearly an up-to-the-second copy of the Master.  No backups, no locks, no reload, etc.

Then, if you like, you can "continually" backup the slave.  It won't impact the Master and all the activity there.

If your fear is that something will be deleted, then needs to be recovered, see CHANGE MASTER TO ... MASTER_DELAY ....


PostgreSQL: Separate tables vs single table to perserve disk space?

Because the resulting combined table would have unused rows for records from each respective table, the likelihood is that you would not gain space, but lose it.

More importantly than that, I would highly discourage making an architecture decision of that nature for reasons of disk-space. Disks are cheap--a well designed and functional application is not. If there is an issue of run-time performance (ie all 46 gb is being loaded into memory/swap space) then you may have an optimization and tuning issue on your hands.


SSIS package deployment. Changing protection level takes a very long time

It seems that I solved my own problem. I do not know exactly what cause it, but here is what i did.

It turns out that only one of the projects that we have had the problem I described. That project happened to have multiple packages included. For testing I removed all packages from the project and added one at the time, built the project after each step and tested the deployment. They all worked fine individually and ultimately I ended up with a project with all packages that deploys fast.

This got me thinking that maybe the project file/build files got hosed somehow and they all needed a full refresh/rebuild.


There are ways to tune backups by messing with different knobs like MAXTRANSFERSIZE or BUFFERCOUNT, or striping the file (which you've noted you're already doing).

The problem is that touching those knobs may still result in hitting the limits of your network and/or storage, and them not having any real impact on backup time.

Your first job should be to benchmark the storage you're backup up to using Crystal Disk Mark or DiskSpd. That'll give you some idea of how fast you can expect writes to be at their best.

The next thing you need to test is reads from the drives you're backing up from. If you run a backup to NUL, you can time how long it takes just the read portion of your backup, without having to write it to disk.

With both those numbers in mind, you can start messing with other knobs to see which ones get you closest to them, regardless of if your backup target is local or networked.


Storing and querying rolling data in PostgreSQL

All things considered, I would go with option 2. Dates will be evenly selected on, but I'm going to guess that for a given query only one or two date partitions will be involved. It's a shame you can't cluster on geolocation and partition on date, which would be ideal. Elevation tends to correlate with geolocation anyway, if the bounding boxes are sufficiently small.

Given the choices available, cleaner data operations and avoiding a daily vacuum is a good thing to have.

Delivering selects may be faster with option 1, though I suspect it will probably be a wash. With option 1, records with the same date and elevation are placed near each other in one big clustered index. With option 2, records with the same date and elevation are placed near each other in many smaller clustered indexes.


Using Echo statement writes Echo is ON in the text file

This command: ECHO 1>C:\Pharmsuite\Vercheck.txt

Does not do what you think it should do because 1> has a special meaning.

This problem can be fixed by adding a space before >:

ECHO 1 >C:\Pharmsuite\Vercheck.txt

Or in your case:

SET @CMD = 'ECHO '+@Ver+' >'+@Vercheck

It also works with this command (making sure there is a space before ECHO [thanks to Andriy M]):

SET @CMD = '>'+@Vercheck+' ECHO '+@Ver

You should look at:

Technet Using command redirection operators
SS64 Redirection:

  Numeric handles:

  STDIN  = 0  Keyboard input
  STDOUT = 1  Text output
  STDERR = 2  Error text output
  UNDEFINED = 3-9

  command 2> filename       Redirect any error message into a file
     command 2>> filename      Append any error message into a file
    (command)2> filename       Redirect any CMD.exe error into a file

Anyhow, you should avoid using xp_cmdshell. If you are trying to output something, Powershell or SQL Server command line commands are better options.

Using Powershell and Invoke-Sqlcmd:

Invoke-Sqlcmd -Query "SELECT GETDATE() AS TimeOfQuery;" -ServerInstance "MyComputer\MyInstance"
Invoke-Sqlcmd -InputFile "C:\MyFolder\TestSQLCmd.sql" | Out-File -filePath "C:\MyFolder\TestSQLCmd.rpt"

You obviously have to adapt it to your own requierements and you must also look at Daniel Hutmacher's answer using osql.


Why are my SQL Server backup ending as a file that is twice the size of my database?

Most likely, it's multiple backups inside the same file. To see it:

RESTORE HEADERONLY
FROM DISK = N'N:\MSSQL\DATA\CC\CC_backup_tues.bak' ;

If you see multiple backups in there, you have a couple of different options:

Specify a different file name per backup (I'm a huge fan of this - use date-based file names rather than days), or
Use the WITH INIT command to overwrite the last backup - this can be a bad idea if you ever need to go back more than a week


Your code looks mature, mostly, but see below! Once you fixed that, I don't see much that could go wrong with it. I share the doubts that it will improve performance much, though. And there may be better alternatives, depending on exact requirements.

General advice

For simple functions as displayed:

Use simple SQL function (LANGUAGE sql) which can be inlined in the context of an outer query. SQL is preferable for simple functions that are typically nested.

PostgreSQL Stored Procedure Performance

Remove the STRICT modifier. Contrary to what you might expect, this is probably not going to help with simple SQL functions, because it can prevent said inlining. (STRICT may help with expensive functions, though.)

Function executes faster without STRICT modifier?

Multiple users

To accommodate multiple users with different but more or less stable preferences, you could improve your design building on the typical default search_path setting  serach_path = "$user",public.
Create a separate version for each user with the same function name in their respective private schemas. Unless schema-qualified, each user will see his own version of the function fnc_common_format_money(). And they can all use the same queries.

Switch schema

Or, if we are dealing with changing requirements for a single user, you could switch the search_path manually (and an arsenal of functions with it):

SET search_path = "$user", verbose_format, public

verbose_format being one name of a schema with specialized functions. Could also come before "$user".
Related:

How can I fake inet_client_addr() for unit tests in PostgreSQL?

You may not need a regime for dynamic settings any more. If you still do, extend your table test_config with a column username (or schemaname) to store a separate row for each user (schema) and adapt your code accordingly. Make it a bit simpler and safer while being at it:

Code suggestions

For the "multiple users" scenario.

CREATE OR REPLACE FUNCTION fnc_test_config_precompiler()
  RETURNS trigger AS
$func$
BEGIN
   EXECUTE (
      SELECT format(fnc_source, NEW.username, NEW.money_format)
      FROM   test_config_fncs
      WHERE  fnc_name = 'test_fnc_common_format_money'
      );

   RETURN NEW;
END
$func$ LANGUAGE plpgsql;

CREATE TRIGGER test_config_precompile
AFTER INSERT OR UPDATE OF username, money_format      -- !!
ON test_config
FOR EACH ROW EXECUTE PROCEDURE fnc_test_config_precompiler();

...

INSERT INTO test_config_fncs(fnc_name, fnc_source)
VALUES ('fnc_common_format_money',
$fnc$
CREATE OR REPLACE FUNCTION %$1I.fnc_common_format_money(_value numeric)
  RETURNS text LANGUAGE sql IMMUTABLE AS
'SELECT to_char(_value, %$2L)';
$fnc$);

Major points

Build one function per user setting. (Optional, but the rest of the advice applies in any case.)
Build conditions into the trigger directly and call trigger function only after relevant columns have been changed or on INSERT (this way you do not have a reference to OLD in the trigger function, which would not work with the additional call ON INSERT.
The simple trigger only creates required functions and does not take care of orphaned versions.
Parameter name inside the body of an SQL function requires pg 9.2+.
Use format() to inject (sanitized!) parameters into the CREATE FUNCTION statement. Closing a possible security issue with SQL injection.


Cannot connect to Oracle 19 instance running in a container under Ubuntu 20.04 via WSL2 on Docker for Windows

Well, I found the culprit. The port assignment
      - 8080:8080

is not needed (it was legacy from previous work), and once I take it out, I can connect via SQL Developer and my app is happy as well. Not sure I understand the reason, but for now I am back in business.


moving large number of tables to different filegroups

There are a lot of things you are leaving out of your description, which the script will need to accommodate for - is the index a primary key or a unique constraint? are any of the columns descending? is the index filtered? does it have any INCLUDE columns? While you can certainly generate a script manually, why not use the Generate Scripts wizard?

in Object Explorer, right-click your database
choose Tasks > Generate Scripts...
Click Next
Choose "Select specific database objects" and then select all the tables named ABC...
Click Next
Click Advanced
Scroll down and change "Script Indexes" to True
Click OK
Change the option to "Save to new query window"
Click Next / Next / Finish

Now you have a query window with all of your scripts, you'll have to remove some and do some hand-massaging of others, but for the indexes that aren't part of the PK you should simply be able to search and replace for DROP_EXISTING = OFF to DROP_EXISTING = ON and then swap out [PRIMARY] for FG1...

Here is a script that uses sys.indexes, sys.columns, and sys.index_columns - it creates a unique index in the case where an explicit primary key existed (since you haven't provided the "re-create PK with DROP_EXISTING" syntax I've asked for). This deals with include columns, fill factors, puts the columns in the correct order and even makes sure to build the unique/clustered indexes first.

SET NOCOUNT ON;

DECLARE @sql NVARCHAR(MAX);

SET @sql = N'';

;WITH src AS
(
  SELECT
    obj = QUOTENAME(OBJECT_SCHEMA_NAME(i.[object_id]))
    + '.' + QUOTENAME(OBJECT_NAME(i.[object_id])),
    i.[object_id],
    i.index_id,
    i.name,
    uniq = CASE i.is_unique WHEN 1 THEN ' UNIQUE' ELSE '' END,
    type_desc = i.type_desc COLLATE SQL_Latin1_General_CP1_CI_AS,
    ff = ', FILLFACTOR = ' + CONVERT(VARCHAR(3), i.fill_factor),
    dest = CASE LEFT(OBJECT_NAME(i.[object_id]), 3)
        WHEN 'ABC' THEN 'FG1'
        WHEN 'DEF' THEN 'FG2'
        ELSE 'DEFAULT'
        END
  FROM sys.indexes AS i
  INNER JOIN sys.partitions AS p
  ON i.[object_id] = p.[object_id]
  AND i.index_id = p.index_id
  WHERE i.index_id > 0
  -- AND OBJECT_NAME(i.object_id) IN ('list','of','tables')
),
cols AS
(
  SELECT
    name = QUOTENAME(c.name),
    ic.key_ordinal,
    ic.[object_id],
    ic.index_id,
    sort = CASE ic.is_descending_key WHEN 1 THEN ' DESC' ELSE ' ' END,
    ic.is_included_column
  FROM sys.index_columns AS ic
  INNER JOIN sys.columns AS c
  ON ic.[object_id] = c.[object_id]
  AND ic.column_id = c.column_id
  WHERE ic.[object_id] IN (SELECT [object_id] FROM src)
)
SELECT @sql = @sql + CHAR(13) + CHAR(10)
    + N'CREATE ' + uniq + ' ' + type_desc + ' INDEX ' + QUOTENAME(name)
    + ' ON ' + obj + '(' + STUFF((SELECT ',' + name + sort FROM cols
        WHERE cols.object_id = src.object_id
        AND cols.index_id = src.index_id
        AND cols.is_included_column = 0
        ORDER BY cols.key_ordinal
        FOR XML PATH('')), 1, 1, '') + ')'
    + COALESCE(' INCLUDE(' + STUFF((SELECT ',' + name FROM cols
        WHERE cols.[object_id] = src.[object_id]
        AND cols.index_id = src.index_id
        AND cols.is_included_column = 1
        ORDER BY cols.key_ordinal
        FOR XML PATH('')), 1, 1, '') + ')', '')
    + ' WITH (DROP_EXISTING = ON' + ff
    + ') ON ' + dest + ';'
  FROM src
  ORDER BY uniq DESC, type_desc;

SELECT @sql;
-- EXEC sp_executesql @sql;

For completeness, here is a script that adds sys.partitions and several SQL Server 2008-specific features such as filtered indexes and data compression.

SET NOCOUNT ON;

DECLARE @sql NVARCHAR(MAX);

SET @sql = N'';

;WITH src AS
(
  SELECT
    obj = QUOTENAME(OBJECT_SCHEMA_NAME(i.[object_id]))
    + '.' + QUOTENAME(OBJECT_NAME(i.[object_id])),
    i.[object_id],
    i.index_id,
    i.name,
    uniq = CASE i.is_unique WHEN 1 THEN ' UNIQUE' ELSE '' END,
    type_desc = i.type_desc COLLATE SQL_Latin1_General_CP1_CI_AS,
    filter = CASE WHEN i.has_filter = 1 THEN ' WHERE ' + i.filter_definition ELSE '' END,
    ff = ', FILLFACTOR = ' + CONVERT(VARCHAR(3), i.fill_factor),
    dc = CASE p.data_compression_desc WHEN 'NONE' THEN ''
        ELSE ', DATA_COMPRESSION = ' + p.data_compression_desc END,
    dest = CASE LEFT(OBJECT_NAME(i.[object_id]), 3)
        WHEN 'ABC' THEN 'FG1'
        WHEN 'DEF' THEN 'FG2'
        ELSE 'DEFAULT'
        END
  FROM sys.indexes AS i
  INNER JOIN sys.partitions AS p
  ON i.[object_id] = p.[object_id]
  AND i.index_id = p.index_id
  WHERE i.index_id > 0
  -- AND OBJECT_NAME(i.object_id) IN ('list','of','tables')
),
cols AS
(
  SELECT
    name = QUOTENAME(c.name),
    ic.key_ordinal,
    ic.[object_id],
    ic.index_id,
    sort = CASE ic.is_descending_key WHEN 1 THEN ' DESC' ELSE ' ' END,
    ic.is_included_column
  FROM sys.index_columns AS ic
  INNER JOIN sys.columns AS c
  ON ic.[object_id] = c.[object_id]
  AND ic.column_id = c.column_id
  WHERE ic.[object_id] IN (SELECT [object_id] FROM src)
)
SELECT @sql = @sql + CHAR(13) + CHAR(10)
    + N'CREATE ' + uniq + ' ' + type_desc + ' INDEX ' + QUOTENAME(name)
    + ' ON ' + obj + '(' + STUFF((SELECT ',' + name + sort FROM cols
        WHERE cols.object_id = src.object_id
        AND cols.index_id = src.index_id
        AND cols.is_included_column = 0
        ORDER BY cols.key_ordinal
        FOR XML PATH('')), 1, 1, '') + ')'
    + COALESCE(' INCLUDE(' + STUFF((SELECT ',' + name FROM cols
        WHERE cols.[object_id] = src.[object_id]
        AND cols.index_id = src.index_id
        AND cols.is_included_column = 1
        ORDER BY cols.key_ordinal
        FOR XML PATH('')), 1, 1, '') + ')', '')
    + filter + ' WITH (DROP_EXISTING = ON' + ff + dc
    + ') ON ' + dest + ';'
  FROM src
  ORDER BY uniq DESC, type_desc;

SELECT @sql;
-- EXEC sp_executesql @sql;


NULL values on MDX query (Total Column has Values)

If you are an MDX novice, you should start simple and layer on complexity as you get the simple queries to work. Try getting just one of the values you're expecting. Once you get that, all the others should fall into place.

Start off with something like this:

SELECT
[BIN_SCH_Year].&[2010]  ON COLUMNS,
[BIN_SCH_INV_GRO].&[1]  ON ROWS
FROM [ProductsManagement]
WHERE [Measures].[BIN_SCH_REC_VAL]

If you find values, build on this query. If you don't, take a look at your relational database and make sure the data is loaded correctly.

Boa sorte!


Creating external tables from SQL Server using JDBC Drivers

All I need is to create an external table from MSSQL using JDBC drivers. Is this possible?

No.  The supported data sources are here:

Hadoop
Azure Blob Storage
SQL Server
Oracle
Teradata
MongoDB
ODBC Generic Types

https://docs.microsoft.com/en-us/sql/relational-databases/polybase/polybase-guide?view=sql-server-ver15


How to clear an Error 15404 after a DB restart (besides rebooting)

Not a solution, but you can work around the issue by making the job owner a SQL account.

Each time a job is started, SQL Server verifies the identity of the job owner and checks that it has permission to execute the job. If the owner is a Windows account, the engine needs to query Active Directory. If for any reason that fails, the job will not run. It could be because the AD server is busy, down or cut off from the network, or that the SQL Server service account doesn't have rights.

Since it works immediately after rebooting, that makes me think it might have something to do with cached credentials. Windows will save the credentials it looks up for later use. That cache is cleared on reboot. Perhaps something is corrupting the cache.


In my understanding of your question:

to check where network packet will go look at results of ROUTE PRINT (Hit [Windows]+[R], type CMD, hit [Enter]). Read article on Technet.
with TRACERT x.x.x.x (where x.x.x.x is your secondary replica servers IP address) you will see a route that's packet travels.


There's no UNIQUE key on your table, only a PRIMARY KEY on an AUTO_INCREMENT column, which never trigger the ON DUPLICATE KEY because, as you probably already know, you're passing id = NULL.

Change this:

KEY `unique_index` (`user_id`,`list_type`,`s_object_id`,`group_id`)

to this:

UNIQUE INDEX `unique_index` (`user_id`,`list_type`,`s_object_id`,`group_id`)


sp_sqlagent_get_perf_counters SQL Server 2005 running every 20 seconds

Does it matter, unless it is sucking resources? As I understand it (from some searches), it will run anyway.

There is an MS Connect item for SQL Server 2008 for CPU usage, but it looks unresolved


You can follow the steps mentioned in the link below to reset the SA password:

Disaster Recovery: What to do when the SA account password is lost in SQL Server 2005

Steps summarised below:

  Open SQL Server Configuration Manager from Start Menu > Programs > Microsoft SQL Server 20xx > Configuration Tools > relevant to the newest version of SQL Server you have installed (e.g. if you have 2005 and 2012 installed, use the 2012 version). Don't have a Start Menu? On Windows 8's Start screen, start typing SQL Server Con... until it shows up.
  Stop the SQL Server instance you need to recover by right-clicking the instance in SQL Server Services and selecting "Stop"
  Right-click the instance you just stopped, click Properties, and in the “Advanced” tab, in the Properties text box add “;–m” to the end of the list in the “Startup parameters” option (on newer versions, you can go directly to the "Startup Parameters" tab, type "-m" and click Add, without worrying about the syntax, the semi-colon, or anything else).
  Click the “OK” button, and restart the SQL Server Instance
  After the SQL Server Instance starts in single-user mode, the Windows Administrator account is able to connect to SQL Server using the sqlcmd utility using Windows authentication. You can use Transact-SQL commands such as "sp_addsrvrolemember" to add an existing login (or a newly created one) to the sysadmin server role.

  The following example adds the account "Buck" in the "CONTOSO" domain to the sysadmin role:

  EXEC sp_addsrvrolemember 'CONTOSO\Buck', 'sysadmin';

  Once the sysadmin access has been recovered, remove the “;-m” from the startup parameters using the Configuration Manager and restart the SQL Server instance one more time.

  NOTE: make sure there is no space between “;” and “-m”, the registry
      parameter parser is sensitive to such typos. You should see an entry
      in the SQL Server ERRORLOG file that says “SQL Server started in
      single-user mode.”

Additional resources:

Connect to SQL Server When System Administrators Are Locked Out
Leveraging Service SIDs to Logon to SQL Server 2012 and SQL Server 2014 Instances with Sysadmin Privileges
Recover access to a SQL Server instance using PsExec

Ultimately, you could always copy the database files to another instance, or even reinstall SQL Server (adding a local account as sysadmin during that process).


My Procedure isn't working right. It keeps giving me an error

You're right. While the procedure executes correctly from SQL or PL/SQL code, somehow SQLDeveloper raises an exception when you invoke the procedure (via the green arrow) and pass anything as input that contains a "@" sign. It's either a peculiar behavior of SQL Developer or a bug.

But executing it directly works fine, also from SQL Developer:


What are the potential causes for a sudden MySQL slowdown?

You could try the following to improve things

skip-host-cache and skip-name-resolve

Please follow these steps:

Step 01) Add the following to /etc/my.cnf

[mysqld]
skip-host-cache
skip-name-resolve

Step 02) service mysql restart

Step 03) Stop using mysql users that have DNS names. Run thus query:

SELECT user,host FROM mysql.user;

Any user that has a DNS name, simply replace it with IP Address (such as 10.85.115.73) or a netblock (such as 10.85.115.%)

You may also want to run this command intermittently (say one an hour in a crontab)

FLUSH HOSTS;

Give it a Try !!!


Toad 9.7.2 for Oracle hangs on Windows 7

I fixed the issue by going into Configure TOAD Options --> Schema Browser --> Data and Grids and there I unchecked Enable FK Lookup


Restoring the database to another server will not result in the data being unencrypted. However, you do need to:

...move the certificate or asymmetric key that is used to open the DEK.
The certificate or asymmetric key must be installed in the master database of the destination server, so that SQL Server can access the database files.
You must retain copies of both the certificate file and the private
key file in order to recover the certificate. The password for the
private key does not have to be the same as the database master key
password.

The following link provides you with the details:
https://msdn.microsoft.com/en-us/library/ff773063(v=sql.120).aspx
Once you have everything set up on the restored database, you can (of course) turn off TDE and let the server eventually de-encrypt the database, if that is your intention.
There is a Very Important Note on removing encryption from a database that is to be restored from SQL Server 2008 or 2008 R2 to another server at:
http://blogs.msdn.com/b/batuhanyildiz/archive/2012/10/16/how-to-enable-remove-transparent-data-encryption-tde.aspx
(This may not matter to you, depending on the source of the restored database.)


Why are there different Database Collations for different languages?

(The following copies portions of the first part of my answer to a related DBA.StackExchange Q&A: Why not use SQL_Latin1_General_CI_AS for a global system? and adds some additional information.)

Collations in SQL Server, with respect to handling differences between various languages (meaning: this is not an all-inclusive list of what Collations do), handle several aspects of string data:

Locale / LCID (referring to the Culture: en-US, fr-FR, etc)

This is used to determine culture-specific overrides to the default linguistic sorting and comparison rules used by Unicode / NVARCHAR data across all Collations as well as non-Unicode / VARCHAR data for Windows (i.e. non-SQL_) Collations.

The Unicode Collation Algorithm (UCA) defines how to sort strings based on various rules and options (various sensitivities, which case sorts first, which characters are "ignorable", etc). There is a default sort weight given to each character and this is known as the Default Unicode Collation Element Table (DUCET). However, it is not possible to capture the sorting and comparison rules for all languages in a single set of rules because characters are shared between languages that have different rules for how to treat those same characters. So the LCID indicates which set of overrides, if any, to apply to the default set of rules. And those culture-specific overrides are described in the Common Locale Data Repository (CLDR). For example:

SELECT CHAR(105) AS [LowerCase], CHAR(73) AS [UpperCase],
       UPPER(CHAR(105) COLLATE Turkish_100_CI_AS) AS [TurkishUpperCase];

SELECT 1 WHERE CHAR(105) = CHAR(73) COLLATE Turkish_100_CI_AS;
SELECT 2 WHERE CHAR(105) = CHAR(73) COLLATE Hebrew_100_CI_AS;

SELECT 3 WHERE NCHAR(105) = NCHAR(73) COLLATE Turkish_100_CI_AS;
SELECT 4 WHERE NCHAR(105) = NCHAR(73) COLLATE Hebrew_100_CI_AS;

Returns:

LowerCase    UpperCase    TurkishUpperCase
i            I            İ

2
4

To see additional examples of how different languages behave against the same comparison, please see my StackOverflow Answer: What is the point of COLLATIONS for nvarchar (Unicode) columns?.

For non-Unicode / VARCHAR data using SQL Server Collations (those starting with SQL_), the sort orders should be what is described in the following MSDN pages: Appendix D Sort Order for Selected Languages.
Code Page

This is the character set used for non-Unicode / VARCHAR across all Collations. To be clear, Code Pages do not apply to Unicode / NVARCHAR data as Unicode is a single character set. And to be super clear, Unicode is a single character set regardless of how it is encoded: UTF-8, UTF-16, or UTF-32.

Non-Unicode / VARCHAR is 8-bit, but that does not mean that it is always 1 byte per character. There are four Double-Byte Character Sets (DBCS) supported by Windows & SQL Server to allow mapping more than 256 characters. Similar to how UTF-8 works, these Double-Byte Character Sets are variable-length and will use 1 byte for some characters and two bytes for others.

The Code Page is determined by the LCID (noted above), and is the only meaningful difference between the various binary Collations (across all deprecated _BIN Collations or separately across all _BIN2 Collations; there is another difference between the _BIN and BIN2 Collations, but it isn't language-specific). This is because a) Unicode is a single character set, and b) sorting is based on the numeric value of each character, and that does not vary between languages.

Example of different characters for the same numeric value between Code Pages:

Check the top-right element in each of the following grids:

Code Page 1254 Windows Latin 5 / Turkish
Code Page 1255 Windows Hebrew

-- 0xF0 == 240 == ğ (in Turkish, Code Page 1254) == נ (in Hebrew, Code Page 1255)
DECLARE @Table TABLE (Turkish VARCHAR(10) COLLATE Turkish_100_CI_AS,
                      Hebrew VARCHAR(10) COLLATE Hebrew_100_CI_AS);

INSERT INTO @Table (Turkish, Hebrew) VALUES (0xF0, 0xF0);
SELECT * FROM @Table;

Returns:

Turkish    Hebrew
ğ          נ

Sensitivity

Case and Accent sensitivity can be controlled across all Collations. Kana and Width sensitivity can only be controlled when using the Windows Collations and are assumed to be "insensitive" when using the SQL_ Collations.

Also, all of the Windows Collations should have a binary option (at least the deprecated _BIN, if not also _BIN2) whereas there are only two SQL_ Collations that have the _BIN / _BIN2 options: SQL_Latin1_General_CP850 and SQL_Latin1_General_CP437.

Also, there are some behavioral differences for non-Unicode / VARCHAR data only when using the SQL_ Collations:

When sorting data using a case-sensitive Collation (e.g. SQL_Latin1_General_CP1_CS_AS), upper-case characters will sort ahead of lower-case characters.
String-sort is used, which sorts all punctuation ahead of all letters, instead of Word-sort, which ignores dashes and apostrophes within words (e.g. a-f sorts before aa using String-sort, but after it when using Word-sort).
No culture-specific character expansions are done (e.g. 'æ' = 'ae'). These expansions are based on the Unicode rules and vary based on the LCID, and if you need them while using VARCHAR data, then you need to use Windows Collations (i.e. not starting with SQL_).

There is, however, behavioral consistency between NVARCHAR data using any collation and VARCHAR data using a Windows Collation.

Hence, ideally, the SQL_ Collations shouldn't be used given the above restrictions and differences, not to mention that they are also deprecated (and there are only 77 of them and 3810 Windows Collations as of SQL Server 2014). If anything, try to use the most recent version of a particular Collation (e.g. _100_), and if offered, use one ending in _SC.

Unfortunately, SQL_Latin1_General_CP1_CI_AS is the default when installing a new instance in the US (at least). But one should not willingly pick a SQL_ Collation for new development, especially when needing to deal with multiple cultures.

Please also note:
Collation is not just set at the Database-level, it is also set at the Instance-level:

The Instance default Collation controls:

the default Collation of the system databases: master, model, msdb, and tempdb (including Temporary Table names)
the default Collation when creating new Databases, unless overridden via the COLLATE clause
cursor variable names
GOTO labels
variable / parameter names
the default Collation for new columns (via CREATE TABLE or ALTER TABLE...ADD) in Temporary Tables (but not Table Variables), unless a) overidden via the COLLATE clause, or b) the current Database when the Temporary Table is created is a Contained Database.

The Database default Collation controls:

the default Collation for new string (CHAR / VARCHAR / NCHAR / NVARCHAR) columns (via CREATE TABLE or ALTER TABLE...ADD), unless overridden via the COLLATE clause. this default also applies to Table Variables, but not Temporary Tables which take their default Collation from tempdb's default Collation (unless the database is a Contained Database in which case the default is the same as the local / current database).
the Collation used for string literals and local variables (the variable data, not the variable name, which is governed by the Instance's default Collation)


Message file not found when starting SQLPlus

This error usually occurs if you are executing sqlplus as a different user (other than the one which installed Oracle software), due to permission issues

Check Oracle Note: 356850.1


Capturing errors in a transaction and rollback - declare not valid at this position

DECLAREs must be placed before all executable code in a BEGIN...END block.  `START TRANSACTION is 'executable'.

So...

DELIMITER //
CREATE ...
BEGIN
    DECLARE _rollback DEFAULT 0;
    START TRANSACTION;
    ... -- other executables
    IF _rollback = 0
    THEN  COMMIT;
    ELSE  ROLLBACK;
    END IF;
END;
//
DELIMITER ;

You can also have nested BEGIN..END blocks.  But the declarations in your example should probably go at the beginning of the stored routine.  This is because BEGIN..END blocks provide "scoping" for variables such as _rollback.


I do not think it is possible to attempt to connect to a database without knowing the username, password, or whether it even exists.

If you could have someone log into MySQL (requires admin privileges without the MySQL name/password) they could run mysql> show databases; at the MySQL command line interface to get you started.


Trying to get multiple mysql instances running on Ubuntu dev box

Have you looked into the mysqld_multi tool ?

This will allow you to run different configurations and different distros using the same configuration file (/etc/mysql/my.cnf) and you could adjust the init.d script to use the mysqld_multi commands instead of mysqld.

Another tool might be mysql sandbox, but I've never used that personally on any distro.


Figured it out

SELECT temp1.[file], temp1.[ChangeDate] AS [AddedDate]
,temp1.[OldFolder]
,temp1.[NewFolder]
,(SELECT MIN(temp2.[ChangeDate])
FROM [Changes] AS temp2
WHERE temp2.[OldFolder] = temp1.[NewFolder]
AND temp2.[ChangeDate] > temp1.[ChangeDate]
AND temp2.[file] = temp1.[file]
) AS [RemovedDate]
FROM [Changes] AS temp1


Why shouldn't a pre-hashed password be used with CREATE LOGIN all the time?

The hashing method is presumably an implementation detail which may or may not change in future releases (as it has at least once already). They're telling you not to do it in order to absolve themselves of breaking your scripts/automation if you try to run them on newer or older releases. It's partially supported purely to allow for migrating logins.

At some point in the process, you would have to use the clear-text password to generate the hashed password, so I'm not sure you'd really gain much by supplying pre-hashed passwords.


Inserting rows into a truncated table will increase the size?

If I truncate all the tables in it and then start inserting new rows across tables will it increase the size or it still the same 50 GB?

It depends on how much free space was in the database before you started truncating, and how quickly you start inserting new rows.

As an optimization, a truncate on a large table (more than 128 extents) uses deferred drop. Within the truncate transaction, the allocation units associated with the table are simply marked for deallocation. After commit, the actual deallocation work is performed separately in batches by the system. As a result the space released by the truncate is gradually made available for reuse.

See Dropping and Rebuilding Large Objects

If you truncate all the tables in one transaction, then immediately start rapidly inserting rows to a database that had very little space remaining, the chances are quite good that the database would grow, at least for a short time until the deferred drops started to catch up.


Oracle return the first row, not returning the first row

The answer is right there, in the data.  (I assume that the time part is 00:00:00 in all of them):

The order by has ties. There is not one "first row" but 3 of them.

So, when the 2nd query restricts the result to one row, one of the 3 is - arbitrarily - chosen. It may not be the same row chosen the next time you run the same query!


update records that are there in .csv file which will be given as input

You can create an external table that references the data in your CSV.
You'll need a directory object creating that points to the location on the file system; in this case MY_DIR.

CREATE TABLE update_ids_ext
(
  rec_id               NUMBER,
  update_value         VARCHAR2(100)
)
ORGANIZATION EXTERNAL
  (  TYPE ORACLE_LOADER
     DEFAULT DIRECTORY my_dir
     ACCESS PARAMETERS
       ( RECORDS DELIMITED BY '\r\n'
      FIELDS TERMINATED BY ','
      OPTIONALLY ENCLOSED BY '"'
      MISSING FIELD VALUES ARE NULL
      (  rec_id ,
         update_value
      )
        )
     LOCATION (MY_DIR:'update_ids.csv')
  )

You can then just refer to your CSV file as if it were a table and perform an update however you like.
I've done an example using a merge and another using an update but the choice is yours.

MERGE INTO target_table tgt
 USING (SELECT rec_id, update_value
          FROM update_ids_ext ) src
 ON (tgt.rec_id = src.rec_id)
WHEN MATCHED THEN UPDATE SET tgt.column_value = src.update_value;

UPDATE target_table tgt
   SET ( tgt.column_value ) = (SELECT src.update_value
                                 FROM update_ids_ext src
                                WHERE tgt.rec_id = src.rec_id)
 WHERE EXISTS (SELECT 1
                 FROM update_ids_ext src
                WHERE tgt.rec_id = src.rec_id)

You probably need to look at the documentation for directory objects and external tables and possibly merge and update.


Variation on greatest-n-per-group where whole table is needed with indicator, not just greatest rows per group

Use a window function:
SELECT ROW_NUMBER() OVER (PARTITION BY repid ORDER BY date DESC) = 1 AS greatest, ...

This requires MySQL 8.0.02 or MariaDB 10.2.0 to use window functions.


Yes, totally possible. You just have some random syntax errors.

And I took the freedom to replace the term "stored procedure" in your query with "function", since Postgres does not have stored procedures. Just functions - doing almost, but not quite, the same.

This would work:

CREATE OR REPLACE FUNCTION func_test()
  RETURNS VOID AS
$func$
BEGIN
  CREATE OR REPLACE FUNCTION func_test2()
     RETURNS INT AS
  $$
    SELECT 1
  $$ LANGUAGE sql;
END
$func$ LANGUAGE plpgsql;

Or:

CREATE OR REPLACE FUNCTION func_test()
  RETURNS VOID AS
$func$
BEGIN

CREATE OR REPLACE FUNCTION func_test2()
  RETURNS INT AS
$$
BEGIN
   RETURN (SELECT 1);
END
$$ LANGUAGE plpgsql;

END
$func$ LANGUAGE plpgsql;

Compare:

Can I make a plpgsql function return an integer without using a variable?


Backup architecture for frequent backups of business critical DB

Do you use InnoDB tables or MyISAM? If the former, then use Percona's hot backup solution here for scheduled hot backups. If you're using MyISAM (why?), you can at best take warm backups - but you have to tell your users to stop updating every 15 mins - not the best idea.
HTH, Paul...


What is the best way to split a string into integers and units?

We can use APPLY to calculate the position of the various things we need, we use each APPLY in series like variables in procedural code.
We assume that the number is the start of the string, followed by arbitrary letters, a slash, another number, then more letters.
Note: no tally function needed here:

select
    cost,
    size,
    strength,
    v4.*

from example e

cross apply ( values (
    charindex('/', e.strength)
) ) as v1 (slash)

cross apply ( values (
    iif(slash = 0, e.strength, left(e.strength, slash - 1)),
    substring(e.strength, nullif(slash, 0) + 1, len(e.strength)) -- len means the rest of string
) ) as v2 (numer, denumer)

cross apply ( values (
    patindex('%[^0-9.]%', numer),  -- first non-number
    patindex('%[^0-9.]%', denumer)
) ) as v3 (numerUnit, denumerUnit)

cross apply ( values (
    try_cast(iif(numerUnit = 0, numer, left(numer, numerUnit - 1)) as real),
    iif(numerUnit = 0, null, substring(numer, numerUnit, len(numer))),

    try_cast(iif(denumerUnit = 0, denumer, left(denumer, denumerUnit - 1)) as real),
    iif(denumerUnit = 0, null, substring(denumer, denumerUnit, len(denumer)))
) ) as v4 (numerator, numeratorUnit, denumerator, denumeratorUnit)


Reason for having additional "type" table of all possible string values?

It seems the idea here is to restrict what itemTypes can be used in the item table to only those in the itemType table, while at the same time avoiding having to do a JOIN with the itemType table to get the actual value whenever you SELECT from the item table. It's a kind of denormalization in order to optimize SELECT statements. This comes at a cost of needing more storage space since every item record will have the full itemType name, potentially as long as 255 characters.

With a normalized approach you would have tables defined like this:

CREATE TABLE item (
  ...
  itemTypeID int unsigned NOT NULL,
  ...
  CONSTRAINT fk_item_itemType FOREIGN KEY (itemTypeID) REFERENCES itemType(itemTypeID)
) ENGINE = MyISAM;

CREATE TABLE itemType (
  itemTypeID int unsigned NOT NULL,
  name VARCHAR(255) NOT NULL,
  PRIMARY KEY (itemTypeID),
) ENGINE = MyISAM;

However, when SELECTing from the item table you would now have to do a JOIN in order to display the itemType:

SELECT i.itemID, i.name, it.name "itemTypeName"
FROM item i
  INNER JOIN itemType it USING (itemTypeID);

Another solution to this is using an ENUM and avoid the itemType table altogether:

CREATE TABLE item (
  itemID CHAR(5) NOT NULL,
  name VARCHAR(255) NOT NULL,
  itemType ENUM('gloves', 'boots', 'ring') NOT NULL,
  PRIMARY KEY (itemID)
) ENGINE = MyISAM;

The drawback of this approach is that you would have to ALTER the table whenever you want to add a new itemType.


Microsoft SQL Server 2008 R2 backup error

This error means that you "striped" your backup across multiple files, and you're only providing one of the files when you're trying to restore.

If you're backing up using SQL Server Management Studio, make sure that you click "remove" to remove any existing backup destinations before you "add" the file you want to back up to.


Find Uncompressed/Compressed Sizes of TimescaleDB Database/Table

If you want to find out savings from TimescaleDB's native compression, use this information view for the hypertable: https://docs.timescale.com/latest/api#timescaledb_information-compressed_chunk_stats

In the field, we're typically seeing seeing 90-98% storing savings across a large numbers of users.

For measuring ZFS, look at the numbers returned by zfs get compression and zfs get compressratio (see, e.g., https://www.servethehome.com/the-case-for-using-zfs-compression/)

If you are running native compression on top, you'll see that this is the amount of compression that ZFS adds on top of TimescaleDB's native compression.  Would love to hear what you see; would expect that to actually be pretty minimal (or even negative).


Return only one row from table with similar values in column that match a criteria

You can use the EXCEPT keyword:

CREATE TABLE #T1(c1 char(1), c2 int)
go

INSERT INTO #T1 Values('A',1)
INSERT INTO #T1 Values('A',2)
INSERT INTO #T1 Values('A',3)
INSERT INTO #T1 Values('A',4)
INSERT INTO #T1 Values('B',1)
INSERT INTO #T1 Values('B',2)
INSERT INTO #T1 Values('C',1)
INSERT INTO #T1 Values('C',3)
INSERT INTO #T1 Values('C',4)
INSERT INTO #T1 Values('D',2)
GO

SELECT c1 FROM  #T1
EXCEPT
SELECT c1 FROM #t1 WHERE c2 = 3

DROP table #T1


Sorting Result state taking very long time

If there is a foreign key from dw_fact_program_attendance(criteria_id) that REFERENCES dw_dimension_criteria(id), there is no need to join the two tables (but the optimizer is not yet smart enough to understand that.) You can have only one table and GROUP BY criteria_id:

SELECT
     a.criteria_id AS c0,
     COUNT(DISTINCT a.client_id) AS m0
FROM
     dw_fact_program_attendance AS a
GROUP BY
     a.criteria_id ;

An index on (criteria_id, client_id) would help efficiency.


SQL Server - How does contained database user password expiration work?

Ok, so it looks like only password complexity requirements apply and not expiration - https://docs.microsoft.com/en-us/sql/relational-databases/security/password-policy?view=sql-server-2017 - "Note SQL Database enforces password complexity. The password expiration and policy enforcement sections do not apply to SQL Database."


Migrating to a new (upgraded) SQL server

I would use Copy-DbaLogin from the dbatools powershell module.
This command migrates the logins from server A to server B.
From their website the example:
Copy-DbaLogin -Source sqlserver2014a -Destination sqlcluster -Force

Copies the logins from sqlserver2014a to sqlcluster
and  overwrites them if they exist by using the -Force parameter
What does it migrate?

Migrates logins with SIDs, passwords, defaultdb, server roles &
securables, database permissions & securables, login attributes
(enforce password policy, expiration, etc.)

Same source as the one mentioned above


Create a new table (multiple columns) by selecting random values from an existing table

I suggest a "data-modifying CTE":

WITH cte AS (
   SELECT *, row_number() OVER () AS rn
   FROM  (
      SELECT id
      FROM   tbl
      ORDER  BY random()
      LIMIT  600  -- 2 x 300
      ) sub
   )
INSERT INTO table2(source, target)
SELECT c1.id, c2.id
FROM   cte c1
JOIN   cte c2 ON c2.rn = c1.rn + 300;

In the CTE:

select 600 random rows (to create 300 new rows)
add a row number in the outer SELECT.

Then couple two values in a self join with 300 offset.

To get random rows from a huge table cheaply, consider:
Best way to select random rows PostgreSQL


if you backup to multiple files the backup will normally run faster - so I think this is a win win suggestion. If you do the file copy with something like robocopy then there is retry built in.


Parallel replication is not working on a 5.7 slave from a 5.7 master

MySQL can only use as many threads as are actually needed for replication given your database workload. The fact that those threads are visible would imply that parallel execution is in effect. Take a look at what a "SHOW PROCESSLIST" yields. More specifically, look at what the "time" column for the replication threads shows.


MySQL replication slave hangs after encountering SET @@SESSION.GTID_NEXT= 'ANONYMOUS';


You can copy frm and ibd files ONLY if the tablespace ID in idb file is the same as the tablespace ID in ibdata1, if it's not then the data will be corrupt and you will be missing tables.

When I had that problem I used this resource to recover files:

Recover InnoDB into MySQL

You may also find these links useful:

Recover InnoDB from ibd File
Manually Importing InnoDB MySQL Table Files


Select one column by two different names in the same query

select n1.name as "n1", n2.name as "n2"
 from main n1, main n2
 where n1.name = 'Ahmad'
     and n2.name = 'Sami';


Upgrading SQL Server 2012 from SP2 to SP3

1/ Microsoft recommends to test it in your UAT environment before proceeding with deployment in production environment.

2/ Backup system & user databases

3/ If you use analysis services backup Analysis Service configuration files, databases and repositories. Backup files present at location
C:\Program Files\Microsoft SQL Server\MSAS11.\OLAP\Data\
C:\Program Files\Microsoft SQL Server\MSAS11.\OLAP\Config\ directory.

4/ Make sure enough space is available on drive where resource/system databases are present. Resource database is utilized during service pack upgrade.

5/ Service pack upgrade would require a downtime of application. Make sure application does not access database during service pack upgrade. Make sure you stop all jobs and activities on database before proceeding. you don't need to shutdown database services to apply service pack.

6/ Make sure you are administrator on the system and always run the service pack executable with administrator privileges.

Although instructions for SP2 still relvant for SP3 also, please see the below source. I presume this a standalone instance?

Source: http://social.technet.microsoft.com/wiki/contents/articles/25632.how-to-install-sql-server-2012-sp2-on-standalone-sql-server-instance.aspx


Filter column by records matching a timezone in Postgres

No, there is not. The type name timestamp with time zone can be misleading. The time zone is not stored at all, it just serves as input / output modifier and all values are stored as UTC internally.

More:

Ignoring timezones altogether in Rails and PostgreSQL
Time zone storage in PostgreSQL timestamps


How to do Pagination with UUID v4 and Created Time on Concurrent Inserted Data?

SELECT * FROM payment_with_uuid
WHERE
created_time <= '2020-05-24 21:27:10' :: timestamp
AND
id <'fffe3477-1710-45c4-b554-b539a9ee8fa7'
ORDER BY created_time DESC, id DESC LIMIT 10;

This is wrong, but it shouldn't lead to the problem you indicate of the same row showing up on page 1, 2, etc.  Rather it would result in most rows failing to show up at all, because the two filters are implemented independently.  You need to implement the id filter only within ties of the created_time filter.  Elegance I guess is a matter of opinion, but it seems to me that the most elegant solution is the tuple comparator similar to what you had attempted to include in your original question.

SELECT * FROM payment_with_uuid
WHERE
(created_time,id) < ('2020-05-24 21:27:10' :: timestamp, 'fffe3477-1710-45c4-b554-b539a9ee8fa7')
ORDER BY created_time DESC, id DESC LIMIT 10;

Now the timestamp should really be exact, it doesn't look like yours is.
How is it getting rounded to the nearest second?  In my hands it looks more like 2020-05-25 09:16:29.380925-04

If for some reason you don't want to use the tuple comparator, then you need to include the timestamp twice, once for less than and once for equal to:

WHERE
created_time < '2020-05-24 21:27:10' :: timestamp
OR
(
    created_time = '2020-05-24 21:27:10' :: timestamp
    AND
    id <'fffe3477-1710-45c4-b554-b539a9ee8fa7'
)

In addition to not being very elegant, this will probably not use the index very effectively.  You could use boolean reasoning to re-write it to avoid that top-level OR, so that it can use the index, but then it will get even harder to read and understand.


Did you run a DBCC CHECKDB on the 'recovered' database? Is is consistent?

Your case doesn't hold water though. In theory the most you could had lost was the transactions present in the log (LDF) but not flushed in the data (MDF). That would amount, at most, to the dirty pages not flushed since the last checkpoint, which would be minutes of data loss, definitely not days.

  I'm also sure that it is the most recent .mdf / .ldf that is recovered and sent.

Being wrong on this point would be, by far, the simplest explanation. Other explanation is that you deleted the data after attach, which is kind of silly. Or the recovery returned a corrupted MDF that is missing data, but that would fail with corruption errors when trying to query it. I would double/triple check again that is indeed the latest mdf/ldf that was recovered.


Do I need to enable Trace Flag 1117 for equally sized data files?

What I can't understand is if data files are filling proportionally, won't they auto-grow proportionally either? In that case we can't omit using TF1117.

The proportional fill algorithm states that if you have multiple files in a file group and all have different free space, SQL Server will fill the space in the files such that it would try to fill more in the file which has larger space as compared to one having lower space. Example if you have 2 files in FG with 10 and 20 GB size respectively, with proportional fill for every 1 extent allocated to 10GB file 2 extent will be allocated to 20 GB file.

While TF 1117 says that if I am enabled and autogrowth happens I would force all files to expand equivalent to amount of data entered. For example if 100 MB of data is entered all files in a file group with TF 1117 enables will increase by 100MB this will not happen with proportional fill as described above.

More about Proportional Fill Algorithm

In most cases adding TF 1117 helps in reducing contention in tempdb but please note once enabled it applies to all the databases running on the system.

PS: If you have SQL Server 2016 and above you do not need to turn on TF 1117 and 1118.


Why/when does SQL Server evaluate the probe side of an inner hash join when the build side was empty?

Not running the probe side of the join when the build is empty is an optimization. It is not available for parallel row mode hash join when the probe side has a child branch i.e. when there is an exchange operator.

There was a similar report many years ago by Adam Machanic on the now-defunct Connect feedback site. The scenario was a start-up Filter on the probe side, which ran its child operators unexpectedly. The answer from Microsoft was that the engine requires a guarantee that certain structures are initialized, and the only sane way to enforce that was to ensure the probe side operators are opened.

My own recollection of the details is that not initializing a sub-tree led to hard-to-fix parallel timing bugs. Ensuring the child branch started up was a work around for those problems.

Batch mode hash join does not have this side effect because the way threads are managed is different.

In your particular case, the effect is more pronounced because the hash aggregate is blocking; it consumes its entire input during the iterator's Open() call. When there are only streaming operators on the probe side, the performance impact will often be more limited, depending on how much work is required to return the first row to the probe side of the hash join.


psql has three different prompts, source:

The prompts psql issues can be customized to your preference. The three variables PROMPT1, PROMPT2, and PROMPT3 contain strings and special escape sequences that describe the appearance of the prompt.

Prompt 1 is the normal prompt that is issued when psql requests a new command.

Prompt 2 is issued when more input is expected during command entry, for example because the command was not terminated with a semicolon or a quote was not closed.

Prompt 3 is issued when you are running an SQL COPY FROM STDIN command and you need to type in a row value on the terminal.

You can see the defaults with \set in PSQL, or looking here in the source code
#define DEFAULT_PROMPT1 "%/%R%# "
#define DEFAULT_PROMPT2 "%/%R%# "
#define DEFAULT_PROMPT3 ">> "

These are all format strings. They render different in different prompt levels, from the source above describing %R,

%R

In prompt 1 normally =, but @ if the session is in an inactive branch of a conditional block, or ^ if in single-line mode, or ! if the session is disconnected from the database (which can happen if \connect fails).
In prompt 2 %R is replaced by a character that depends on why psql expects more input: - if the command simply wasn't terminated yet, but * if there is an unfinished /* ... */ comment, a single quote if there is an unfinished quoted string, a double quote if there is an unfinished quoted identifier, a dollar sign if there is an unfinished dollar-quoted string, or ( if there is an unmatched left parenthesis.
In prompt 3 %R doesn't produce anything.

So there ya have it, %R changes based on the PROMPT level.


Count values from one table using two columns in another table

Assuming no duplicates in the 2nd table, it's straightforward.

ANSI syntax:

select col1, col2, sum(value)
from table_1
natural join table_2
group by col1, col2;

The "old" way:

select t1.col1, t1.col2, sum(value)
from table_1 t1, table_2 t2
where t1.col1 = t2.col1
and   t1.col2 = t2.col2
group by t1.col1, t1.col2;

Could have used WHERE EXISTS too, I guess.

SQL Fiddle for completeness.


SQL Server 2012 Business Intelligence licensing

No, you do not have to buy a VS licence to use SSDT or SSDT-BI, you have the licence for the services (IS, AS, RS) if you have a SQL Server licence. SSDT and SSDT-BI are just tools to use those services.

You can freely download SSDT and SSDT-BI from Microsoft:

SSDT: http://msdn.microsoft.com/en-us/data/hh297027 (for SQL Server Database projects)
SSDT-BI: https://www.microsoft.com/en-us/download/details.aspx?id=36843 (for SSIS, SSAS and SSRS projects)

Edit It is not required to preinstall any kind of VS, the will install the VS Shell if it is not installed.

Note You can install BIDS/SSDT-BI using the SQL Server installer, but it contains the version which was available when the installer built (mostly hardly outdated).


Execution Plan Caching and Reuse lists some of the factors that trigger recompilation:

  Recompiling Execution Plans

  Certain changes in a database can cause an
  execution plan to be either inefficient or invalid, based on the new
  state of the database. SQL Server detects the changes that invalidate
  an execution plan and marks the plan as not valid. A new plan must
  then be recompiled for the next connection that executes the query.
  The conditions that invalidate a plan include the following:

  Changes made to a table or view referenced by the query (ALTER TABLE and ALTER VIEW).
  Changes made to a single procedure, which would drop all plans for that procedure from the cache (ALTER PROCEDURE).
  Changes to any indexes used by the execution plan.
  Updates on statistics used by the execution plan, generated either explicitly from a statement, such as UPDATE STATISTICS, or generated automatically.
  Dropping an index used by the execution plan.
  An explicit call to sp_recompile.
  Large numbers of changes to keys (generated by INSERT or DELETE statements from other users that modify a table referenced by the
  query).
  For tables with triggers, if the number of rows in the inserted or deleted tables grows significantly.
  Executing a stored procedure using the WITH RECOMPILE option.

Since Index Rebuild operations also update the statistics as a side effect, it follows that an index rebuild will trigger a plan invalidation (recompile).


Better to use a RANGE RIGHT or RANGE LEFT Partition Scheme

It's not a question of better or worse, but business requirements will drive that choice. For instance, we're talking about whether or not you want your defined boundary values to live in one partition or the next.

An example of a boundary value of '9/12/2013', would you want that boundary valued data to live in partition x (range left) or partition x + 1 (range right)?  This will especially come into play when you're thinking about things like individual partition management, as well as partition switching for purposes such as archiving.


What is locking order of MySQL on query with join statement

It is quite natural for the Optimizer might pick different query plans for those two queries.  Even if the EXPLAINs look similar today, they could be different tomorrow.

Regardless, you should plan on deadlocks; it is often futile to try to eliminate all of them.  When you get a deadlock, replay the entire transaction; it is very likely to succeed the second time.

Yes, there can be edge cases where two "independent" queries can collide.  This is probably because of edge cases where gap locking collided - or something like that.

There might be cryptic clues in SHOW ENGINE INNODB STATUS;

This index on user might decrease the frequency of deadlocks:

INDEX(status, age)

And it is likely to speed up the queries when you have a lot of rows.


How to insert a Unicode character verbose into a varchar DB?

I need to insert this character '●' into a VARCHAR column ... with collation set as SQL_Latin1_General_CP1_CI_AS ... The reason I am doing this exercise is that our app was inserting this character into DB earlier.

No, the app was not inserting this character (Black Circle U+25CF) into a VARCHAR column using a [SQL_]Latin1_General... Collation. Latin1_General Collations use Code Page 1252 which does not have such a character, nor does there appear to be a "best fit" mapping to convert it to something similar.

That character does exist in some code pages, such as those used by the Korean and Japanese Collations, as the following queries show (both are double-byte character sets, which is why the conversion to VARBINARY shows two bytes instead of one):

SELECT CONVERT(VARCHAR(10), N'●' COLLATE Korean_100_CI_AS),
       CONVERT(VARBINARY(10), CONVERT(VARCHAR(10), N'●' COLLATE Korean_100_CI_AS));
-- ●    0xA1DC

SELECT CONVERT(VARCHAR(10), N'●' COLLATE Japanese_XJIS_100_CI_AS),
       CONVERT(VARBINARY(10), CONVERT(VARCHAR(10), N'●' COLLATE Japanese_XJIS_100_CI_AS));
-- ●    0x819C

SELECT CONVERT(VARCHAR(10), N'●' COLLATE Latin1_General_100_CI_AS),
       CONVERT(VARBINARY(10), CONVERT(VARCHAR(10), N'●' COLLATE Latin1_General_100_CI_AS));
-- ?    0x3F

It is possible, however, that the app was inserting '•' (Bullet U+2022), which looks quite similar, just a little smaller. The "Bullet" character is available in Code Page 1252 (Dec 149 or Hex 0x95).

  our DB Team wrote a script to correct it and apparently it got replaced with "?"

Well, replacing "●" with "?" doesn't sound like fixing it ;-).

  Now I am just trying to reproduce this scenario to ask them why it is getting replaced with "?" because we are replacing it as empty in the App. Since my App already has a fix for this issue (we remove anything greater than 127)

Regardless of technicalities, it seems fairly obvious both statements cannot be true at the same time: it cannot get replaced with "?" and get removed before hitting SQL Server. Clearly the app code does not have a fix for this, and is not removing everything with a value over 127. It is being converted into "?" in SQL Server because that character is being inserted yet does not exist in Code Page 1252.

  I think it is done because it says "COLLATE" in the update query.

No, the forcing of a binary Collation via the COLLATE keyword is not changing this character into "?". Both the column's Collation ( SQL_Latin1_General_CP1_CI_AS ) and the explicit Collation ( Latin1_General_BIN ) use Code Page 1252, so no characters could change due to this.

The "●" character is being replaced with "?" as it is being inserted into the VARCHAR column. And once it is inserted as a question mark, there is nothing to clean up, and no way to identify that question mark as being originally from something else as opposed to an intended question mark.

  Is this a fine script to remove Non-ASCII-Data > 127? Can someone explain this query in plain English?

That query finds any occurrences of characters having an ASCII value of greater than 127 (that is what the PATINDEX does), and then gets any such character (that is what the SUBSTRING does), and then replaces all occurrences of that character in the column with empty string (this is what the REPLACE does). The query does not do any UPDATE if no characters are found having an ASCII value over 127.

This query only works on one character at a time. So, if a column has 2 or more characters with ASCII values above 127 that are not the same ASCII value, then the script would need to be executed multiple times.

That query will not help in this particular case (i.e. of replacing "●" with "?") since that conversion is happening on the way in. This query only handles characters with ASCII values of 128 - 255, yet "●" is not one of those characters as it could never be in this column in the first place.


Data for the latest previous effective date sub query

So I'll provide two answers, with the following caveats:

Certain DB engines are bad with correlated subqueries, either in terms of performance or outright lack of support
The row identifier (id) columns carelessly tacked on as a "primary key" will limit performance of both solutions and will likely force a sort, an expensive operation

Given the lack of proper primary keys, the performance may suffer if you are working on a large set of data.  If you're just returning a few hundred/few thousand rows you should be okay on modern hardware.
Methond 1: Correlated Subquery
The most direct way (from a code perspective) is to use a correlated subquery to select the record from journal with the greatest updated_on less than or equal to spent_on from time_entry:
SELECT
  time_entry.id
 ,issue.subject
 ,time_entry.spent_on
 ,time_entry.hours
 ,journal.done_ratio
FROM
  time_entry time_entry
INNER JOIN
  issue issue
    ON issue.id = time_entry.issue_id
LEFT JOIN
  journal journal
    ON journal.issue_id = time_entry.issue_id
         AND journal.updated_on =
           (
             SELECT
               MAX(updated_on)
             FROM
               journal
             WHERE
               issue_id = time_entry.issue_id
                 AND updated_on <= time_entry.spent_on
           )

Method 2: Window Function
You can also use a windowing function to number the rows according to specified criteria, and selecting only the first row.  This forces a sort in all instances.
SELECT
  id
 ,subject
 ,spent_on
 ,hours
 ,done_ratio
FROM
  (
    SELECT
      time_entry.id
     ,issue.subject
     ,time_entry.spent_on
     ,time_entry.hours
     ,journal.done_ratio
     ,ROW_NUMBER() OVER (PARTITION BY time_entry.id ORDER BY journal.updated_on DESC) AS rownum
    FROM
      time_entry time_entry
    INNER JOIN
      issue issue
        ON issue.id = time_entry.issue_id
    LEFT JOIN
      journal journal
        ON journal.issue_id = time_entry.issue_id
             AND journal.updated_on  <= time_entry.spent_on
  ) x
WHERE
  rownum = 1


Updating SQL table from Excel sheet (using powershell)

Bulk load processes do just that - load data. They are not intended for updating existing data, or even for adding rows that don't already exist. As you surmised, the best method for those things is generally to load the data to a staging table, run a process that moves staging data to the main table (whether that's updating or replacing existing rows adding new rows, or some combination of the above), then clearing the staging table.

I'd set up a stored procedure in SQL to handle the data in the staging table, and run that after loading the staging table. You could use your existing script, but would need to add the code to run the stored procedure (and, of course, would need to create the stored procedure on your SQL Server).


It would be performance suicide to first build a huge jsonb object from joining two tables, and then filter a single record representing an underlying row. You would filter first, of course.

Going out on a limb, this might be what you are trying to do:

SELECT to_jsonb(rr) AS record
FROM  (
   SELECT (SELECT to_jsonb(row) FROM  (
              SELECT c.customer_id
                   , c.first_name
                   , c.last_name
                   , c.email
                   , p3.total
                   , p3.total_amount) h) AS header
        , p3.details
   FROM   customer c
   JOIN   LATERAL (
      SELECT count(*) AS total
           , sum(p2.amount) AS total_amount
           , jsonb_agg(detail) AS details
      FROM  (
         SELECT p.amount
              , (SELECT to_jsonb(d) FROM (SELECT p.amount, p.payment_date) d) AS detail
         FROM   payment p
         WHERE  p.customer_id = c.customer_id
         ) p2
      ) p3 ON p3.total_amount = 2.99   -- here !
   WHERE  customer_id = 2              -- and here !
   ) rr

There is no record->'details'->>'amount' in your object. There are array elements like record->'details'->0->>'amount', but those make no sense with the = operator. So I assumed you are aiming for total_amount.

A little less confusing with jsonb_build_object():

SELECT to_jsonb(rr) AS record
FROM  (
   SELECT jsonb_build_object('customer_id', c.customer_id
                           , 'first_name', c.first_name
                           , 'last_name', c.last_name
                           , 'email', c.email
                           , 'total', p3.total
                           , 'total_amount', p3.total_amount) AS header
        , p3.details
   FROM   customer c
   JOIN   LATERAL (
      SELECT count(*) AS total
           , sum(p2.amount) AS total_amount
           , jsonb_agg(detail) AS details
      FROM  (
         SELECT p.amount
              , jsonb_build_object('amount', p.amount
                                 , 'payment_date', p.payment_date) AS detail
         FROM   payment p
         WHERE  p.customer_id = c.customer_id
         ) p2
      ) p3 ON p3.total_amount = 2.99   -- here !
   WHERE  customer_id = 2              -- and here !
   ) rr;


How does Automatic Seeding work - Always On Availability Group

As you noted, the docs refer to "log stream transport" when performing automatic seeding. Similar to the way that you can stream movies to your device without downloading a specific file, automatic seeding streams data to the secondary without storing an actual backup file.
While the technical implementation details aren't exactly this, it's essentially taking a backup, but rather than sending it to a storage device or file share, it sends it to the secondary server. Rather than writing a file, it immediately begins to restore to the secondary server. The pseudo-backup is buffered and restored in a single step, without a literal backup being written to file.


GRANT permission to access to respective creator

NOTE: When I wrote the answer (below), I did not notice that your question is a MySQL question. Please understand that my answer is for the MS SQL Server platform. I have never used MySQL (and probably never will) so I'm not sure it the permissions model is the same. None-the-less, my "solution" would work in MySQL as long as MySQL provides functions to return the current "user" to match against the saved user information.

Records are not "objects" that can be granted (or denied or revoked) permissions within the SQL Server security system. Only database objects (i.e. tables, views, stored procedures, functions, etc.) can have permissions modified with the GRANT statement. The only way to control access to records created by a given user is to build the database in such a way as to record the "owner" of the records within the data. You could add a column to the table "creator" or some such name and have the user's login saved in the column (you could also save this data in another table. For example (RecordID, UserID) or something to that affect. Then comes your challenge: how to actually control the access to the records. Well, the only way you're going to do that is to ensure that ALL access to the table is given through the use of stored procedures and, within the stored procedures that access the table, you build the logic that checks the current user against the user that saved the record and decide to allow or deny access. Alternatively, you could control the access through the use of a view (that uses a where clause against the user column and a function that returns the current user) but, me personally, I would go the stored procedure route (because I think that all access should be through SPs to begin with)...


You can consider a rule as transforming the command being executed, whereas a trigger is altering the data itself. (Note that this is a simplification! Spend some time reading the documentation for CREATE TRIGGER and CREATE RULE rather than trusting some random internet guy.)

So you can define a rule that is invoked when PostgreSQL sees a certain command, and transforms that command into something that would not invoke the trigger.

Take for instance a rule defined as such:

CREATE OR REPLACE RULE dont_insert AS
ON INSERT TO xyz.items
DO INSTEAD NOTHING;

And a trigger defined as such:

CREATE TRIGGER insert_with_func BEFORE INSERT
ON xyz.items FOR EACH ROW
EXECUTE PROCEDURE xyz.row_update_func();

Attempting to do an insert will not invoke row_update_func(), even if the trigger is defined as BEFORE INSERT, because the rule is modifying the command itself; after the command is re-evaluated, it's no longer doing anything, and the trigger no longer applies.

Obviously your rule does not need to be defined with INSTEAD OF, and in that case, your rule and your trigger could both apply.

With specific regard to your code, it's hard to say without knowing the definition of konto_zwrocone, but it doesn't look like it since your trigger and rule are defined for different operations on different tables.


Subtract values in same column from 2 different dates in same Mysql table

SELECT t1.item, t2.likes - t1.likes difference
FROM table t1, table t2
WHERE t1.item = t2.item
  AND t1.date = @first_date
  AND t2.date = @second_date

If you need all items (ever the data on @first_date or @second_date is not present), rewrite from cartesian to FULL JOIN and use COALESCE to replace NULLs with zeros.


How can we foresee (and configure) B-Tree and R-Tree indexes width and depth in PostgreSQL?

Both structures can have at most m children per node and as other tree structures, children can have other children.

The million dollar question there is what you mean when you say m children. Math and algorithms in the abstract are concerned with purity: the name for that structure is a Binary Search Trees. Databases are concerned with concurrency and performance, they don't use Binary Search Trees (usually). PostgreSQL implementation of a B-tree isn't based on "children" but on index tuple size and fill factors. Simply, if you can fit more items on the page, you do that: it's called the Index Blocking Factor. The structure is called a B-Tree. Every level of the tree requires a block seek, so you want to do the most you can do to minimize the amount of blocks you must retrieve to complete the index operations. Further complicating matters, the size of the index tuple is variable, from the docs,

  Lehman and Yao assume fixed-size keys, but we must deal with
  variable-size keys. Therefore there is not a fixed maximum number of
  keys per page; we just stuff in as many as will fit.  When we split a
  page, we try to equalize the number of bytes, not items, assigned to
  each of the resulting pages.  Note we must include the incoming item in
  this calculation, otherwise it is possible to find that the incoming
  item doesn't fit on the split page where it needs to go!

tldr; you have little control of the depth (outside of fillfactor). The rows being indexed can result in a variable page size entry, and the depth is a factor of your block size and the index entry size.


Is it possible for a node to host multiple cluster in relation to Always On AG

Taking from your last comment

  yeah im asking if each of the primary replica in each cluster can create a secondary replica in a new server. even though each are coming from different clusters. can the server (to the most left) be on each of the cluster?

This is possible but you would have to use Distributed Availability groups and should have Windows Server 2016 or above as OS.

Also note that the third server cannot be part of all WSFC(Cluster 1,2 ,3). If you want to achieve what you need you would have to create new WSFC and add new machine as part of it. Now you can configure distributed AG's to achieve what you need.


Postgresql Cost-Based Vacuum Delay parameters

The postgres docs are the best place to start


postgres 9.6 GRANT requirements for stored procedure

You have three options.  You can grant update on the table to the role.  You can grant update on the table to the user.  Or you can do neither of those and create the function with the "SECURITY DEFINER" option.

In the last case, then the user will be able to update the table via the function, but will not be able to update the table outside of the function.


Show us what you did to (1) use utf8 in the client(s), (2) how you established the charset from client to server, and (3) SHOW CREATE TABLE.

If you left out any one of those, that that is likely to be the problem.

Do not use utf16, only use utf8.  (Unless you have some good argument to the contrary.)

If you have INSERTed some data, let's see SELECT col, HEX(col)... to see if it was garbled on the way in.


Replication or Sharding for a huge table?

Mysql replication won't address performance issues on large tables.  It just allows you to have another copy you might use for failover, backup or reporting in cases of heavy hitting queries.

Your table schema is tiny.  Even with 14 million rows you could fit that in ram on even modest hardware.

All the same, if you want to look at sharding you could read up on mysql partitioning

You say you expect 14 million rows; you're dangerously close to the max value for your medium PK there.  Just go head and make that an int instead of worrying about the extra byte/entry.


How to get information about query plans that are out of cache?

As Dan noted, you can use Query Store on SQL Server 2016+.

If you're on an earlier version, you can try Open Source Query Store, though I don't have any experience with using it.

As a last resort, you could log sp_BlitzCache to a table. There are instructions on doing that in the README. I'm not going to post them here. Fair warning: I contribute to that open source project.


Pre 2012, expressions were limited to 4k. That's not a Variable's limit, you can assign stupid-long values to a Variable, it just can't be done with expressions. Use a Script Task to do the concatenation and assignment to your variable and see if that doesn't clear up #1

The thing you're likely to run into, and that I never found a satisfactory resolution, was the double firing of messages. I know, they say set propagate to false and all of that but I never seemed to get it to work as I wanted. In our world, a package would fail if an error raised so the OnTaskFailed event was more useful for only getting the signal once but your mileage my vary.

A better approach we used at a different job was to have a process sweep the sys.ssiserrorlog table every N minutes looking for failures. Since all packages logged to it, it was a cleaner and more reusable approach for us.

I've seen people make mention of always running packages from the context of a parent package to adhere to the DRY (don't repeat yourself) principle so the parent has the error notification and such built into it.

The final bit to be aware of, don't rely on SSIS's error handling exclusively. We had a deploy that went well, or so we thought. When the package fired though, it failed to validate as the metadata for a table didn't match what the data flow expected. A package failing to validate is like a syntax error and so the OnError/OnTaskFailed event's didn't even get a chance to fire and tell us that we were in an awful state. And we went a few days without noticing. Oopsie


best column type for hashed data in mysql

If you are starting with hex, use UNHEX() when storing and HEX() when fetching.  Use BINARY(32) for the datatype.  This occupies a constant 32 bytes.

If you are starting with binary, then be careful when escaping to store/load.  Probably best to do a double convert each way.

Be sure to have lots of RAM.  Once the index becomes bigger than the buffer_pool, the system will slow down because of the randomness of sha256 (or any other hash or uuid).

If you go with Base64, then use CHAR(43) COLLATE ascii_bin, which will be a constant 43 bytes, and will be case-sensitive.

256 is gross overkill.  If you have about 2^85 entries, there is only one chance in 2^85 of having any hash collisions.  For a mere MD5, those numbers are 9 trillion.


Designing a table with multiple column types and different foreign keys

You can enforce the column nullability based on the template_id by using CHECK CONSTRAINTS, for example:

ALTER TABLE [item] ADD CONSTRAINT CK_Template_Columns
CHECK (
    (template_id = 1 AND usp_1 IS NOT NULL AND icon_1_asset_id IS NOT NULL AND icon_2_asset_id IS NOT NULL AND heading IS NULL AND usp_2 IS NULL)
    OR (template_id = 2 AND heading IS NOT NULL AND icon_1_asset_id IS NOT NULL AND usp_1 IS NULL AND usp_2 IS NULL AND icon_2_asset_id IS NULL)
    OR (template_id = 3 AND usp_1 IS NOT NULL AND usp_2 IS NOT NULL AND heading IS NULL AND icon_asset_1_id IS NULL AND icon_2_asset_id IS NULL)
)

This CHECK CONSTRAINT enforces the columns that cannot be NULL and those that must be NULL based on the chosen template_id. An attempt to fill an invalid column, or leave a valid column blank, for a given template_id would cause the INSERT/UPDATE to fail.

And if you're concerned regarding storage, within SQL Server you have the option of defining SPARSE columns to help reduce storage space for mostly NULL columns. You can also leverage Data Compression and Filtered Indexes to reduce overall space consumption.

For example, a filtered index for each template_id value would allow you to only index the columns that are not null for that template, thereby reducing the number of rows and columns stored in an index.


Clustered Index improvemens for particular queries

Yes, placing a clustered index like you suggest will give you excellent query performance for those two particular types of queries, but it may spell disaster for most other SELECT queries on that table. I would offer an alternative solution:

I would suggest adding a basic clustered index on a single, unique column like idUser:

ALTER TABLE dbo.Table1 ADD
    PRIMARY KEY CLUSTERED (idUser)
        WITH (FILLFACTOR=100);

... and then building a separate, non-clustered indexe specifically for your queries:

CREATE INDEX IX_Table1_SUM_Amount
    ON dbo.Table1 (SUM_Amount DESC, Attempts DESC)
    WITH (FILLFACTOR=100);

The clustered index (the primary key) will not add any space to your table - it's just another way of organizing the storage by presorting the rows in a binary tree, which will make access to the table a lot quicker.

The non-clustered index will take up some space, but it's comparably small: Both SUM_Amount and Attempts are integer values, so they're 4 bytes large each, meaning that your non-clustered index should end up at roughly 12 MB with a million rows (including the "clustering key", which is also an integer).

Your two example queries will use the non-clustered index to find the largest/smallest values, and this will generate a Key lookup operator in the execution plan, but because you're using TOP (@n), I recon you won't even notice the performance cost of that. If you absolutely need even better performance and want to eliminate the Key lookup, you could INCLUDE () all the relevant columns in the non-clustered index to form a so-called covering index, effectively making the index a sorted copy of the original table:

CREATE INDEX IX_Table1_SUM_Amount
    ON dbo.Table1 (SUM_Amount DESC, Attempts DESC)
    INCLUDE (id, idUser, Amount, date)
    WITH (FILLFACTOR=100);

There is no need to rebuild an index if the data hasn't changed, particularly if the entire table is populated in a single batch. If the table has been populated over time, however, you may see some fragmentation.


What are the benefits of using MySQL Cluster?

When I wanted to get information about MySQL Cluster, MySQL (before Sun bought it) would send out a brief questionnaire, which I wish I still had. One of the questions asked if your tables had integer keys only.

Although MySQL Cluster is ACID-complaint, it does not provide a suitable storage engine for data with compound keys. In some respects, you have to plan its usage like that of a MongoDB or Cassandra in terms of data layout and sharding.

You also have to consider the most annoying part of MySQL Cluster: Network Communication. NDB Cluster tends to be chatty among servers. Even Gig-E can be brought to its knees if doing range queries or full table scans. You must be disciplined enough to use primary keys and unique keys, especially if your application thrives on such keys.

Unless you adhere to these simple observations, MySQL Cluster will do way more harm than good.


DISTINCT on one column and return TOP rows

Replace your dbname and schemaName in the following query.

;WITH CTE AS
(
SELECT
       [Order_No]
      ,[Customer_Name]
      ,[Purchase_Cost]
     , ROW_NUMBER() OVER(PARTITION BY [customer Name] ORDER BY [Purchase Cost] DESC) AS "RowNumber"
  FROM [dbname].[schemaName].[PurchaseTable]
  )

  SELECT TOP(3)
       [Order_No]
      ,[Customer_Name]
      ,[Purchase_Cost]
  FROM CTE WHERE RowNumber=1
  ORDER BY [Purchase_Cost] DESC

I am sure there are other ways of doing the same. I suggest you read this.


how to prevent UPDATE of existing rows/record but allow INSERT using PostgreSQL trigger?

You can use GRANT & REVOKE for this purpose:

 CREATE TABLE TEST (ID int, NAME text);

 REVOKE ALL ON TABLE TEST FROM CURRENT_USER;

 GRANT INSERT ON TABLE TEST TO CURRENT_USER;
 GRANT SELECT ON TABLE TEST TO CURRENT_USER;

 INSERT INTO TEST VALUES (1, 'NAME 1'),(2, 'NAME 2');

UPDATE TEST SET NAME='NAME 3' WHERE ID = 1;
ERROR:  permission denied for table test

db<>fiddle here


"Zero" downtime and minimal space requirements for upgrade to PostgreSQL 9.2

The two constraints that you have specified:

Very low downtime
Limited disk space use

conflict rather seriously.

You have (wisely, IMO) excluded the option of using pg_upgrade with a custom rebuilt Pg, which is the only option I see that'd satisfy both those constraints.

I suspect you'll have to drop the disk space constraint. The only way out of this that I see is to configure Slony-I to replicate from your 8.3 database to a new 9.2 instance deployed either on new storage on the same machine, or on a separate machine. Allow Slony-I to bring the replica up to date, then shut the old server down, disable replication and cut over to the new server. "New server" here can be a new 9.2 cluster running on a different port on the existing server hardware, it doesn't have to be a new physical or virtual machine.

At this point you can use streaming replication - or Slony-I again - to replicate the data back to a 9.2 instance on the original server and fail back to it. The temporary server used for migration can be retired, or (preferably, if it's real hardware) configured to operate as a streaming replication slave and WAL archiving repository for PITR and failover purposes.

Slony-I is a bit of an experience to work with and places some constraints on how you can use the DB (mainly: All DDL must go through Slony, not direct DDL commands), so I recommend taking a dump of the 8.3 server, restoring it to a test workstation, and testing the proposed replication setup there before attempting it in production. You will need to test your application on 9.2 anyway, so this is a good chance to do both. Test it on 8.3-with-slony, and on 9.2.

This might be a good opportunity to see professional support from people who have experience dealing with these issues.


Is it possible to reorder tables in phpMyAdmin?

Not exactly, but you might be able to improve things regardless.

For instance, in a database structure tab, you can sort based on any of the columns; table name, row count, collation, etc. In your specific example, you could simply sort the tables in reverse order (by clicking the "Table" header to invert the default sort order), though of course that isn't a universal solution.

From the navigation frame, you can use the favorite tables feature (which puts selected tables in a quick-access dropdown menu), the show/hide tables feature to hide tables you rarely access (sounds like you're already doing this), and the filter dropdown to quickly find and access an arbitrary table by typing a few keystrokes.

I don't it's not really a complete solution to re-ordering the display of tables, but at the moment those are probably the best solutions available.

As a side note, this would make an interesting feature request for the project issue tracker.


SQL Server Rebuild/Reorganize Fragmented Indexes causes CRC error or consistency errors

The operating system returned error 23(Data error (cyclic redundancy check).) to SQL Server during a read

This indicates the operating system detected an issue reading data from the hard disk itself.  When data is written onto the filesystem, the operating system calculates a CRC code for each block written; when that data is subsequently read back the operating system performs the CRC operation again on the read data and compares it with the CRC stored in the filesystem.  If these two CRC codes don't match the OS reports error 23.  I'd be very surprised if there isn't a hardware issue with either the drive itself, or perhaps some other component such as the motherboard or drive controller.

WITH (ONLINE=ON) in the Enterprise Edition of SQL Server will have no effect whatsoever on this issue.  Online index operations vs offline index operations are no different at the operating system layer; data is read and written as necessary to either rebuild or recreate the index.


You probably got an error like Incorrect syntax near the keyword LIKE, because each of the ("NOT LIKE" and "LIKE") conditions needs to specify the column is is working on. If so, this should work:

SELECT  `id` FROM `qualities`.`streams` WHERE quality LIKE '%FHD%' AND quality NOT LIKE '%H265%';


How does the transactions interact if you set a different isolation level per connection?

Every connection will work with its own transaction isolation level. Setting SERIALIZABLE level for one connection doesn't change isolation level of other connections (new or existing).

However interactions between transactions performed on different isolation level are quite complicated. Everything depends on many factors:

If different transactions operate on the same table/tables?
How your database engine implements transaction isolation levels (pessimistics versus optimistics locking)?
What is characteristics of access from your app to particular tables?

If you have to work with SERIALIZABLE level for some connections in your app you should know answers for above questions.

For example. If you have database with pessimistics locking (e.g. IBM DB2) and you have long running transactions with SERIALIZABLE level it is very probable that your system would hang (or slow down).


Different behavior between MySQL 5.5 and 5.6

You just need to stop relying on the non-standard behaviour provided by GROUP BY when the only_full_group_by SQL mode is not enabled.

Normally, when grouping, you cannot retrieve a column that is not specified in the GROUP BY clause unless you wrap it in an aggregate function. That is because that column may have many different values (remember, we are talking about grouped rows) and there is no way to specify which value of the many should be used in the output. There is a recent extension to that behaviour according to which columns that are functionally dependent on the GROUP BY columns may be selected without specifying them in the GROUP BY, but MySQL has so far went the simple road of offering you just two modes: either you cannot retrieve non-GROUP BY columns without aggregation at all or you can retrieve any such columns, regardless of functional dependence.

So, in this case, you are retrieving columns task_id and due_date that clearly can have various values per ticket_id. Which value is ultimately selected is out of your control, and that is the problem. The solution is to define for yourself exactly which row from the other tables you want to get when you are grouping by ticket.id and implement the logic the way it can be followed unambiguously.

For instance, if you want to go by the behaviour you are observing in MySQL 5.6, you could define it as

  the earliest non-NULL due_date and its corresponding task_id per ticket_id.

Based on that, you could define the logic as follows:

SELECT
  ticket.id AS ticket_id,
  (
    SELECT
      MIN(p2.task_id)
    FROM
      task AS t2
      INNER JOIN planing AS p2 ON t2.id = p2.task_id
    WHERE
      p2.date = MIN(planing.date)
      AND t2.ticket_id = ticket.id
  ) AS task_id,
  MIN(planing.date) AS due_date
FROM
  ticket
  LEFT JOIN task ON task.ticket_id = ticket.id
  LEFT JOIN planing ON task.id = planing.task_id
WHERE
  ticket.type = 1
GROUP BY
  ticket.id
ORDER BY
  MIN(planing.date) ASC
;

This is certainly much more coding than your original query, but the extra bit of code is not redundant, because this way you are making your results predictable.

The only simpler alternative that might emulate the MySQL 5.6 behaviour could be to rewrite the query like this:

SELECT
  ticket.id AS ticket_id,
  task_id AS task_id,
  planing.date as due_date
FROM
  ticket
  LEFT JOIN
    task
    INNER JOIN planing ON task.id = planing.task_id
  ON task.ticket_id = ticket.id
WHERE
  ticket.type = 1
GROUP BY
  ticket.id
ORDER BY
  MIN(planing.date) ASC
;

In my tests, the above query returned the same results for 5.5 as the original did for 5.6 for your example. Although the two queries should logically give equivalent results, they are not exactly equivalent: the rewritten query explicitly changes the logical order of joins. Perhaps that prompted MySQL to choose a different path through the data.

But whatever the reason for the different output, please keep in mind that there is no guarantee the behaviour will match for every scenario. I will repeat, you need to stop relying on the behaviour that is not documented, or, actually, documented to be undefined:

  The server is free to choose any value from each group, so unless they are the same, the values chosen are indeterminate.


sql server instance using maximum cpu usage

Try the CPU related DMV queries from Glenn Berry's SQL Diagnostic Queries. In fact, I suggest you run all the diagnostic queries because the high CPU utilization can be a result of some problem with the way the instance is configured, the way queries were written etc.,

Here is another article on SQL Server Performance site, that might help you troubleshoot.


How to calculate column (integer) changes in hourly buckets?

This query is for all the buckets.

with points_enhanced as( -- Add date_id for grouping and ordering, hour for displaying
  select to_char(created_at, 'YYYYMMDDHH24') date_id, extract (hour from created_at) as hour, * from public.points
)
, points_with_lag as ( -- Calcuate lag point by user_id
  select date_id, user_id, hour, points,
    lag(points, 1, 0 ) over w points_before
  from points_enhanced pe
  window w as (partition by user_id order by date_id)
)
, cte_buckets as ( -- What if we have more than one record by bucket and user_id ? Use only max(points) and min(points_before)
  select date_id, hour, user_id, max(points)  - min(points_before) points_bucket
  from points_with_lag
  group by date_id, hour, user_id
)

select date_id, hour, sum (points_bucket) points
from cte_buckets
group by date_id, hour
order by date_id

This query is for min_date / max_date filter.
Adjust the dates to your needs.

with points_enhanced as( -- Add date_id for grouping and ordering, hour for displaying
  select to_char(created_at, 'YYYYMMDDHH24') date_id, extract (hour from created_at) as hour, * from public.points
  where created_at >= '2017-05-30 12:00:00' and created_at < '2017-05-30 14:00:00' -- filter by min_date/max_date
)
, points_with_lag as ( -- Calcuate lag point by user_id
  select date_id, user_id, hour, points,
    lag(points, 1,
                 ( select coalesce(max(points), 0) from public.points p -- This correlated subquery gets the lag point value, or 0, for that user_id before the min_date limit
                   where created_at < '2017-05-30 12:00:00'
                   and p.user_id = pe.user_id
                 )
    ) over w points_before
  from points_enhanced pe
  window w as (partition by user_id order by date_id)
)
, cte_buckets as ( -- What if we have more than one record by bucket and user_id ? Use only max(points) and min(points_before)
  select date_id, hour, user_id, max(points)  - min(points_before) points_bucket
  from points_with_lag
  group by date_id, hour, user_id
)

select date_id, hour, sum (points_bucket) points
from cte_buckets
group by date_id, hour
order by date_id

Regards.


Synchronize MySQL databases between local and hosted servers

Schedule a job to backup one database and apply the backup script to another, that way you never need to worry about the synchronization as it is done automatically on a schedule


How can I speed up a SELECT statement related with a VIEW in MySQL?

VIEWs are handly.  But they are not necessarily efficient.

In this case, you might be able to make it run 3 times as fast by not calling the VIEW 3 times.  Instead, since the SELECT in the VIEW is already doing the GROUP BY year, take advantage of it.

Essentially the view gives you 3 rows, but you want to turn it into 3 columns.  Before continuing with my answer, please confirm that.  That is, do SELECT * FROM invoice_lines and see if you get all the 'right' info, but laid out 'wrong'.

After that we can talk about "pivoting" the 3 rows into 3 columns.  You can get hints of doing that by searching this forum.

If you need to discuss further, provide SHOW CREATE TABLE invoice_lines,

Or, am I confused?  Do you have one table per year?  Bad.


how to list all features installed in a sql server instance?

You can run "Installed SQL Server features discovery report"

It is documented there: Validate a SQL Server Installation

Some examples: how-to-find-out-what-sql-features-are-installed


Consume data from other database motor from stored procedure in Oracle 12c

Database Gateway and Generic Connectivity (DG4ODBC) Licensing Considerations (Doc ID 232482.1)

  The Oracle Database Gateway for ODBC (DG4ODBC) is included in your
  Oracle Database Server license without extra cost.

  ...

  While the
  Database Gateway for ODBC (DG4ODBC) does not require an additional
  license from Oracle, a third party ODBC driver is required, which may
  require a purchase independent of Oracle.

Yes, Database Gateway for Informix requires the purchase of additional license.

No, Database Gateway for ODBC does not require the purchase of additional license.

All you need is a 3rd party ODBC driver which is compatible with Informix, then you can configure DG4ODBC to use that.

I have done this with IBM DB2, PostgreSQL, SQL Server before, it should work with Informix as well.