What's the difference between ALL_TABLES and TABLES?

ALL_TABLES shows tables and views.
TABLES shows only tables:

dbadmin=> create or replace view test as select now() ;
CREATE VIEW
dbadmin=>
dbadmin=> select * from test;
              now
-------------------------------
 2018-08-13 13:04:24.500297-04
(1 row)

dbadmin=>
dbadmin=> select * from all_tables where table_type = 'VIEW';
 schema_name |     table_id      | table_name | table_type | remarks
-------------+-------------------+------------+------------+---------
 public      | 45035996273742356 | test       | VIEW       |
(1 row)

dbadmin=>
dbadmin=>
dbadmin=>
dbadmin=> select table_name, owner_name from tables where table_name = 'test' ;
 table_name | owner_name
------------+------------
(0 rows)

dbadmin=>
dbadmin=>

There is also a system table called views:

dbadmin=>
dbadmin=>
dbadmin=>
dbadmin=> select table_name, owner_name from views ;
 table_name | owner_name
------------+------------
 test       | dbadmin
(1 row)

A user can select from tables, all_tables and views to see which tables and vies he has permissions for.
I believe I got this from
http://www.vertica-forums.com/viewtopic.php?t=726

Here is a tool I use as DBADMIN to summarize permissions:

 SELECT X.object_type,
        X.object_schema,
        X.object_name,
        X.permissions,
        X.grantor,
        X.owner,
        X."grant_sql (for permissions that have '*')"
 FROM ( SELECT 1 AS ordinal,
        g.object_schema,
        g.object_name,
        g.object_type,
        CASE WHEN (g.privileges_description = 'USAGE'::varchar(5)) THEN (g.privileges_description || ' (READONLY access) '::varchar(19)) WHEN (g.privileges_description ~~ '%CREATE%'::varchar(8)) THEN (g.privileges_description || ' (can read, create & drop tables) '::varchar(34)) ELSE NULL END AS permissions,
        g.grantor,
        s.schema_owner AS owner,
        CASE WHEN (g.privileges_description ~~ '%*%'::varchar(3)) THEN (('grant <INSERT, SELECT, UPDATE, DELETE, REFERENCES> on '::varchar(54) || g.object_name) || ' to <user_name> ; '::varchar(18)) ELSE NULL END AS "grant_sql (for permissions that have '*')"
 FROM (v_catalog.grants g JOIN v_catalog.schemata s ON ((g.object_name = s.schema_name)))
 WHERE (g.object_type = 'SCHEMA'::varchar(6)) UNION  SELECT 2 AS ordinal,
        g.object_schema,
        ((t.table_schema || '.'::varchar(1)) || t.table_name) AS object_name,
        g.object_type,
        g.privileges_description AS permissions,
        g.grantor,
        t.owner_name AS owner,
        CASE WHEN (g.privileges_description ~~ '%*%'::varchar(3)) THEN (('grant <usage, create> on '::varchar(25) || g.object_name) || ' to <user_name> ; '::varchar(18)) ELSE NULL END AS "grant_sql (for permissions that have '*')"
 FROM (v_catalog.grants g JOIN v_catalog.tables t ON (((g.object_schema = t.table_schema) AND (g.object_name = t.table_name))))
 WHERE (g.object_type = 'TABLE'::varchar(5))) X
 ORDER BY X.ordinal,
          X.object_name;


How do I efficiently emulate JSON_TABLE in Mariadb?

I was playing around with my query and came up with this. It is much quicker than what I had before. So much so, that I think this might be the best way to do stuff like this.
with recursive array_values as (
    select
        json_extract(pr.record_data, '$.*.A.B.C[*].D.*') val
        , pr.id
        , json_length(json_extract(pr.record_data, '$.*.A.B.C[*].D.*')) n
    from xxx pr
), rec_elems as (
    select av.id, 0 as i, json_value(av.val, '$[0]') val
    from array_values av
    where av.n > 0 is not null
    union all
    select av.id, e.i + 1, json_value(av.val, concat('$[', e.i + 1, ']'))
    from array_values av
        inner join rec_elems e on av.id = e.id
    where (i + 1) < av.n
)
select * from rec_elems

I will leave the answer unaccepted for a while, in case someone else can provide an alternative.


Column containing the word from another column

From your sample data I guess something like:

select * from services x
where not exists (
   select 1 from users y
   where x.service like '%' || y.name || '%'
);

should do


After some processing this boiled down to:

While your predicate d."SettlementPointName" = 'John' is filtering a single value for "SettlementPointName" anyway, simplify to:

SELECT count(                                     d."SettlementPointPrice" < 10.5 OR NULL) AS da_00_10
     , count(d."SettlementPointPrice" >= 10.5 AND d."SettlementPointPrice" < 20.5 OR NULL) AS da_11_20
     , count(d."SettlementPointPrice" >= 20.5 AND d."SettlementPointPrice" < 30.5 OR NULL) AS da_21_30
FROM   public.da d
JOIN   public.rt_aggregate r USING ("DeliveryDate", "SettlementPointName")
WHERE  d."SettlementPointName" = 'John'
AND    d."DeliveryDate" >= '2015-02-01'
AND    d."DeliveryDate" <= '2015-02-20'
AND    r."DeliveryHour" = 14
AND    date_part('hour', d."DeliveryHour") = r."DeliveryHour";

About the counting technique:

For absolute performance, is SUM faster or COUNT?

Or better, yet, use the new aggregate filter technique in pg 9.4:

SELECT d."SettlementPointName"
     , count(*) FILTER (WHERE d."SettlementPointPrice" <  10.5) AS da_00_10
     , count(*) FILTER (WHERE d."SettlementPointPrice" >= 10.5
                        AND   d."SettlementPointPrice" <  20.5) AS da_11_20
     , count(*) FILTER (WHERE d."SettlementPointPrice" >= 20.5
                        AND   d."SettlementPointPrice" <  30.5) AS da_21_30
FROM   public.da d
JOIN   public.rt_aggregate r USING ("DeliveryDate", "SettlementPointName")
WHERE  d."DeliveryDate" >= '2015-02-01'
AND    d."DeliveryDate" <= '2015-02-20'
AND    r."DeliveryHour" = 14
AND    date_part('hour', d."DeliveryHour") = r."DeliveryHour"
GROUP  BY 1;

This time, selecting all names and returning one row per name like you asked in the comment.

Details for FILTER:

Return counts for multiple ranges in a single SELECT statement


How to query MySql using 256 AES decrypt with key and IV

This is not advisable. Having to decrypt the whole data-set will be slow and memory expensive. You will want to find a reasonable way to encrypt the data without using a separate IV for each username, then encrypt the username you want to find and search for the cipher-text. This will allow you to use indexes and search for only the one username you are interested in.

Alternatively you may consider using hashing in addition to the sorting encrypted username. HMAC the username with a password only known to the server and store in the DB. Then when you want to find a user, hash his username as well and find using the hash. Admittedly this allows some information leakage, for example attacker will be able to see users with the same usernames (you rpobably don't want these anyway). However he should not be able to use any hashtables or even guessing without knowing the HMAC key.

PS: A way you may want to deal with the IV may be: During registration (or username change) choose IV common for the entire server. Encrypt using this IV. If the first block (16 bytes for AES) already are in the database, increment the IV by one and try again. Store the highest increment. During search, try all possible IVs (from the basic one up to the highest increment used).


Oracle Application Server 10g Installation Process

A cpio is a file containing other files, similar to tar files. Usually your system (you mentioned Linux but not which distribution) will contain GNU cpio. you can list the file sin an archive by using cpio -it < file.cpio
 and extract it using cpio -i < file.cpio.

I don't know anything about Oracle Application Server (as this is the dba site: mind that Oracle Application Server is not the Oracle Database, that's a different download) but assume the first "CD" contains an installer script and an installation manual.


The service broker is administratively disabled

I got it fixed after reviewing and resetting all broker related database IDs.

First thing I did was to see what we have (on both source and target servers):

--=====================================================================
-- checking what we have and where we point to
--=====================================================================
SELECT @@SERVERNAME
-- my_target_server

USE [master]
GO
SELECT   [name]
 ,[is_broker_enabled]
 ,[service_broker_guid]
FROM [sys].[databases]
WHERE 1=1
  AND is_broker_enabled = 1
ORDER BY  NAME
GO

SELECT name,is_broker_enabled,service_broker_guid, is_trustworthy_on FROM sys.databases
order by 2 desc, 3 desc,  1

I saw that the broker id needed changed:

--CD718202-CB34-4DCD-BADC-7800C5F2FF3D
  ALTER DATABASE ORCASTG SET ENABLE_BROKER WITH ROLLBACK IMMEDIATE
  ALTER DATABASE ORCASTG SET NEW_BROKER with rollback immediate
  ALTER DATABASE ORCASTG SET trustworthy on with rollback immediate

I am sorry for the trustworthy, this is how it has been set up and I never changed it.

on the target server:

on the source server:

so i have changed inside the routers the service broker ids.

--targer server
USE [cola]
GO
IF  EXISTS (SELECT * FROM sys.routes WHERE name = N'rou_ORCARoute')
DROP ROUTE [rou_ORCARoute]

CREATE ROUTE [rou_ORCARoute]
WITH  SERVICE_NAME  = N'svc_SendStatusChangeService' ,
BROKER_INSTANCE  = N'CD718202-CB34-4DCD-BADC-7800C5F2FF3D' ,  --BROKER ID OF ORCASTG
ADDRESS  = N'TCP://mysourceserver.mycompany.com:4022' --IP address of my source server
GO

--source server
USE [ORCASTG]
GO
IF  EXISTS (SELECT * FROM sys.routes WHERE name = N'rou_CAUKRoute')
DROP ROUTE [rou_CAUKRoute]

CREATE ROUTE [rou_CAUKRoute]
       WITH  SERVICE_NAME  = N'svc_receiveStatusChangeService' ,
       BROKER_INSTANCE  = N'452711F3-60CD-47E6-BFA4-3BD8C9D6AB56' ,   --BROKER_ID OF COLA
       ADDRESS  = N'TCP://mytargetserver.mycompany.com:4022' --IP address of my target server
GO

even after putting the correct boker_ids for the databases in the the routers above, still I was not getting any communication going through.

I had to review the the routers, I noticed I was using ip addresses in place of the server names, and that was working fine, but we did some changes on our domains and that was no longer working, so using the test below, I first identified it was not longer working (the connectivity), then changed it accordingly using the server names.

 declare @sql varchar(1008)
 set @sql = 'powershell.exe -command Test-NetConnection 200.1.0.198.:4022'
 EXEC xp_cmdshell @sql

After those changes, all started to work fine.


Does import sql dump wipe out current data?

it is totally dependent on your mysqldump process and which options you have used while dumping. There are options like --add-drop-database,--add-drop-table  etc with mysqldump which results to wipe out your existing database with same name.
see mysql docs for futher details.


This is a case of "relational division". I tagged the question accordingly, you can find basic information in the tag info.

You did not provide details. Assuming a standard many-to-many (n:m) relationship between products and components which is implemented with a product_component table, a basic solution could look like this:

SELECT c1.product_id
FROM   product_component c1
JOIN   product_component c2 ON c2.product_id = c1.product_id
JOIN   product_component c3 ON c3.product_id = c1.product_id
WHERE  c1.component_id = 1
AND    c2.component_id = 2
AND    c3.component_id = 3;

This needs at least an index on product_id and on component_id to be fast, ideally on (component_id, product_id).

There are many other ways. The best query depends on your exact table definition and data distribution. We assembled an arsenal of queries under this related question on SO,  for Postgres / MySQL, but the SQL is (mostly) the same:

How to filter SQL results in a has-many-through relation

The above query finds products that contain at least the components with component_id 1, 2 and 3. To find products that consist of those components exactly (no others components):

SELECT c1.product_id
FROM   product_component c1
JOIN   product_component c2 ON c2.product_id = c1.product_id
JOIN   product_component c3 ON c3.product_id = c1.product_id
LEFT   JOIN product_component c0
                           ON c0.product_id = c1.product_id
                          AND c0.component_id NOT IN (c1.component_id
                                                    , c2.component_id
                                                    , c3.component_id)
WHERE  c1.component_id = 1
AND    c2.component_id = 2
AND    c3.component_id = 3
AND    c0.component_id IS NULL -- no other component

Again, there are various ways exclude products with additional components:

Select rows which are not present in other table


Can the sorting from an ORDER BY statement be saved explicitly with only one UPDATE statement?

UPDATE
   T1
SET
   position = T2.rn
FROM
   myTable T1
   JOIN
   (
   SELECT
      id,
      ROW_NUMBER() OVER (ORDER BY position) AS rn
   FROM
      myTable
   ) T2 ON T1.id = T2.id

Note: the order of "Keyboard" and "USB cable" is arbitrary. They both have position = 0

To tie-break positions based on item, add a secondary sort

      ROW_NUMBER() OVER (ORDER BY position, item) AS rn

If you have duplicate position,item pairs this will be arbitrary too...


You probably created another test_table in the model database, so then TempDB inherits that on startup.


Open Source tools to visualise MySQL/MariaDB performance metrics (show global status with interval)

Get MonYog through MariaDB.  https://www.webyog.com/product/monyog

Note: anything that works with MySQL, MariaDB, or Percona, will probably work well with the others.

I looked at lots of tools, but came away thinking that most graphs were measuring either stuff that never changes.  My favorite tool metric is the number of slowlog entries.  If that goes up, there are problems.  At that point, sometimes the other graphs lead to useful info, but usually fixing a query is the real solution to a performance problem.


Log Shipping Backup Successful Restore Errors

Tricky to answer without more info.

There are many reasons why there may be a disk issue that doesn't actually cause the backup to fail.

Have you got any errors in the log related to disk activity? Have you tested those log backups yourself?
Can you try restoring them all manually to a test environment and checking database inegrity after if it succeeds?
For that matter, do you dbcc checkdb regularly (apologies if this is obvious, just worth mentioning).
It's tricky answering this part of the question without more info. -

Addendum:
Researching that error seems to only ever point to a version mis-match, but that seems implausible if it's only happening once and all the other backups usually work fine. Worth checking this and asking the question - are both primary and secondary on exact same versions? How often does this happen?
This sqlservercentral goes into this.

This earlier stackexchange post is similar, and suggests the same thing. Ruling out the version problem (which from what you've said I'm assuming it's not), then the other points are pointing to corruption.

Just to check again, how often has this happened and how long have you been using this configuration?

If CheckDB is successful on your database, log shipping has been in place a while and this usually runs fine and this is the first such failure, I might be tempted to put it down to a fluke (I know, not ideal) unless you can find anything more out or someone else weighs in.

Short answer is yes.

Take a differential backup and restore that to the secondary. Brent Ozar has described how to do this.

Even shorter answer - no.

A log backup is a very specific thing, once the backup is complete, the log is different (and ever changing). There only way to do anything point-in-time is with log backups. If you need reading material, Paul Randal has a nice article on Technet that goes a bit into how different backups actually work (and it's fairly accessible/not too long).


This can easily be done through an SSIS package and scheduled with SQL Server Agent. See this link for a tutorial/demonstration on the task: http://www.codeproject.com/Articles/402958/SSIS-Package-Export-Data-from-Database-Daily-to-Ne


The error is potentially caused by this database being encrypted previously and had encryption disabled. If portions of the log contain encrypted data, the backup will also have this encrypted data and hence need the certificate.

Try clearing the log (backup the transaction log) then taking a full backup again and you should be able to restore the new backup.


Why won't Oracle alter the size of a column that is used for sub partitioning?

The solution is dbms_redefinition package. Basically redefition will move, online, to a new table (called interim table), then move all dependent objets like indexes and last exchange the interim table with the original one.

What you have to do is:

Create the interim table withe correct column definition
Run redefinition
Drop the old table

Use dbms_redefinition in the following way:

-- DETERMINE IF THE ORIGINAL TABLE CAN BE REDEFINED ONLINE
BEGIN
DBMS_REDEFINITION.CAN_REDEF_TABLE('SCHEMA','YOURTABLE', DBMS_REDEFINITION.CONS_USE_ROWID);
END;
/

-- BEGIN THE REDEFINITION
BEGIN
DBMS_REDEFINITION.START_REDEF_TABLE(
UNAME => 'SCHEMA',
ORIG_TABLE => 'YOURTABLE',
INT_TABLE => 'INTERIM_YOURTABLE'
OPTIONS_FLAG => DBMS_REDEFINITION.CONS_USE_ROWID);
END;
/

-- IF THE TABLE HAD DEPENDENCIES (INDEXES ... CONSTRAINTS ... TRIGGERS)
-- THIS WOULD BE THE POINT AT WHICH THEY WOULD HAVE BEEN COPIED
DECLARE
error_count pls_integer := 0;
BEGIN
DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS('SCHEMA', 'YOURTABLE', 'INTERIM_YOURTABLE',
dbms_redefinition.cons_orig_params, TRUE, TRUE, TRUE, FALSE, error_count);
DBMS_OUTPUT.PUT_LINE('errors := ' || TO_CHAR(error_count));
END;
/

-- FINISH THE REDEFINITION
exec DBMS_REDEFINITION.FINISH_REDEF_TABLE('SCHEMA','YOURTABLE','INTERIM_YOURTABLE');

If you run redefinition with SYSTEM user there is no problem. If you want to execute it with a less privileged user you have to trick some privileges in order to get it working. Privileges required are:

Execute privilege to DBMS_REDEFINITION
Create any table
Alter any table
Drop any table
Lock any table
Select any table

Tables with the following characteristics cannot be redefined online:

[9.0.1]Tables with no primary keys
Tables that have materialized view logs defined on them
[9i] Tables that are materialized view container tables and AQ tables
[10g] Tables that are replicated in an n-way master configuration can be redefined, but horizontal subsetting (subset of rows in the table), vertical subsetting (subset of columns in the table), and column transformations are not allowed.
The overflow table of an IOT table
Tables with fine-grained access control (row-level security)
Tables with BFILE columns
Tables with LONG columns can be redefined online, but those columns must be converted to CLOBS. Also, LONG RAW columns must be converted to BLOBS. Tables with LOB columns are acceptable.
Tables in the SYS and SYSTEM schema
Temporary tables

Other restrictions:

A subset of rows in the table
Only simple deterministic expressions, sequences, and SYSDATE can be used when mapping the columns in the interim table to those of the original table. For example, subqueries are not allowed.
If new columns are being added with no column mappings, then they must not be declared NOT NULL until the redefinition is complete.
There cannot be any referential constraints between the table being redefined and the interim table.
Table redefinition cannot be done NOLOGGING.
[10g] For materialized view logs and queue tables, online redefinition is restricted to changes in physical properties.
You cannot convert a nested table to a VARRAY.


MySQL GRANT requiring additional permissions

The fundamental problem you have is this:

You cannot grant another user a privilege which you yourself do not have; the GRANT OPTION privilege enables you to assign only those privileges which you yourself possess.
— http://dev.mysql.com/doc/refman/5.6/en/grant.html

This makes sense, since if it were otherwise, then you could just grant privileges to yourself.
There is one way around this, depending on how much flexibility you have with the calling code -- stored procedures can run with the credentials of the defining user (as opposed to the invoking user).  If the calling system could call a procedure instead of using GRANT ..., then you can create a procedure that is executable by the restricted user, granting other privileges as appropriate.


Save different account types in a database

You can reasonably use both approaches. The majority of contemporary SQL databases (mySQL, PostgreSQL, Oracle, SQL Server) will recognize JSON as a data type, and will let you store it as such (that is: it is not just a string, but a string following a certain number of rules).

Things to consider to make your choice:

How the different parts of the data are accessed: If, whenever you need to check the main table, you also need to access the surrogate one (to get the remaining information, regardless of which one it might be), then it is a good idea to have all the information together. If most of the accesses to the main table don't need that remaining information, having it in several separate tables makes more sense.
Access permissions (privileges): If anyone having access to the main table doesn't need or shouldn't have access to the reamining information, then, it feels more natural (and is probably easier) to have it in two (or more) different tables, and deal with table permissions, instead of column permissions.
Programming language ease of handling the JSON data: You can access JSON data from JavaScript, Python, C#, Java, Ruby, PERL... and most (if not all) programming languages. But is not equally easy with all of them. So, consider if the programming language for your project handles JSON easily, or you prefer to get always atomic data (columns with ints, floats, texts, dates and times, ...) from database rows. If handling JSON is not straightforward, choose surrogate tables.
Modularity: if you plan to work with different modules (or classes, or ...) for different types of accounts, it may make sense that each module gets a user_id, and it takes care to check/update one table extra_user_info_X by itself (where X is the type of account). This approach would better match the modularity of the application design.

If your database allows for inheritance (like PostgreSQL or Oracle), that might also be a pattern/paradigm that fits your needs, and a complement to using a multi-table approach.


Granting a user account permission to create databases in PostgreSQL

It's done with ALTER USER username CREATEDB;

See ALTER USER in the doc.

To drop a database, either you're superuser (which can be granted with ALTER USER too) or you must own the database.


Inserting into subtype table values from supertype table in oracle

Simply INSERT ... SELECT.

insert into
  medecins
select
  e.*,
  10,
  'Cardiologue'
from
  employees e
where
  e.num_emp = 10
;


mysql read slave to fetch entire database on start

If you have three or more Slaves, I have a nice suggestion: Use one of the Slaves as a Volunteer to be cloned.

Here is a Topology

MAS --+--> SLV1
      |
      +--> SLV2
      |
      +--> SLV0

Say you want to Spawn SLV3. You could use SLV0 as a Clone

STEP01) Install MySQL Binaries on SLV3 (Same version as all other Slaves)
STEP02) On SLV0, STOP SLAVE;
STEP03) On SLV0, service mysql stop
STEP04) scp -r SLV0:/etc/my.cnf SLV3:/etc/my.cnf
STEP05) scp -r SLV0:/var/lib/mysql SLV3:/var/lib/mysql
STEP06) On SLV0, service mysql start
STEP07) On SLV3, chown -R mysql:mysql /var/lib/mysql
STEP08) On SLV3, change server_id in /etc/my.cnf to be unique from all other Slaves
STEP09) On SLV3, service mysql start

That's it.

This is the same paradigm followed by Percona XtraDB Cluster (PXC). When it comes to PXC, introducing a New Slave in PXC is as simple as adding the MasterIP to my.cnf and starting MySQL. All of the above steps are executed internally by PXC using Quorom Selection to choose which Slave becomes the Donor (a.k.a. Volunteer to be Cloned) as well as one of three methods for copying data (xtrabackup, rsync, mysqldump) This copying method is known as SST (State Snapshot Transfer).

If all of the Application-Level Data are stored in InnoDB only, you should look into using PXC. If you have a mix of InnoDB/MyISAM or all MyISAM, the above 9 steps are to be scripted by you.


mongodb - what roles the user to grant access to all databases

I have created mongouser as testuser with below roles. I want to give
  access for new databases for this user. what roles the user to grant
  access to all databases.

  db.createUser({ user: "testuser" , pwd: "testuser", roles:
  ["dbAdminAnyDatabase", "readWriteAnyDatabase", "enableSharding"]});

As per MongoDB documentation here MongoDB grants access to data and commands through role-based authorization and provides built-in roles that provide the different levels of access commonly needed in a database system. You can additionally create User-defined roles.

A role grants privileges to perform sets of actions on defined  resources. A given role applies to the database on which it is defined and can grant access down to a collection level of granularity.

dbAdminAnyDatabase

Provides the same privileges as dbAdmin on all databases except local and config.
The role also provides the listDatabases action on the cluster as a whole.

Changed in version 3.4: Prior to 3.4,  dbAdminAnyDatabase includes local and config databases. To provide dbAdmin privileges on the local database, create a user in the admin database with dbAdmin role in the local database. See also  clusterManager and clusterMonitor role for access to the config and local databases.

For further your ref here, here and here


SQL Server 2008 R2 Express for a single website?

You do not need the SQL Server Agent. It has nothing to do with database connectivity.

http://msdn.microsoft.com/en-us/library/ms189237.aspx

  SQL Server Agent is a Microsoft
  Windows service that executes
  scheduled administrative tasks, which
  are called jobs. SQL Server Agent uses
  SQL Server to store job information.
  Jobs contain one or more job steps.
  Each step contains its own task, for
  example, backing up a database. SQL
  Server Agent can run a job on a
  schedule, in response to a specific
  event, or on demand. For example, if
  you want to back up all the company
  servers every weekday after hours, you
  can automate this task. Schedule the
  backup to run after 22:00 Monday
  through Friday; if the backup
  encounters a problem, SQL Server Agent
  can record the event and notify you.


Unfortunately, for whatever reason, you can't do an inline conversion in that context, and RAISERROR doesn't directly support float, again, for whatever reason.

For completeness of this answer, here is the relevant snippet from MSDN, which I'm sure you've already seen (note: it's the same text in all versions of the documentation from 2005 to 2012):

  Each substitution parameter can be a local variable or any of these data types: tinyint, smallint, int, char, varchar, nchar, nvarchar, binary, or varbinary.

The only reasonable solution I can think of would be to write a stored procedure to wrap the RAISERROR call. Here's a starting point:

CREATE PROCEDURE [dbo].[MyRaiserror]
(
    @message nvarchar(2048),
    @severity tinyint,
    @state tinyint,
    @arg0 sql_variant = NULL
)
AS
BEGIN

    DECLARE @msg nvarchar(MAX) = REPLACE(@message, '%f', '%s');
    DECLARE @sql nvarchar(MAX) = N'RAISERROR(@msg, @severity, @state';

    DECLARE @int0 int, @char0 nvarchar(MAX), @bin0 varbinary(MAX);

    IF (@arg0 IS NOT NULL)
    BEGIN
        SET @sql += N', ';

        IF (SQL_VARIANT_PROPERTY(@arg0, 'BaseType') IN ('tinyint', 'smallint', 'int'))
        BEGIN
            SET @int0 = CONVERT(int, @arg0);
            SET @sql += N'@int0';
        END
        ELSE IF (SQL_VARIANT_PROPERTY(@arg0, 'BaseType') IN ('binary', 'varbinary'))
        BEGIN
            SET @bin0 = CONVERT(varbinary(MAX), @arg0);
            SET @sql += N'@bin0';
        END
        ELSE
        BEGIN
            SET @char0 = CONVERT(nvarchar(MAX), @arg0);
            SET @sql += N'@char0';
        END
    END

    SET @sql += N');';

    EXEC sp_executesql
        @sql,
        N'@msg nvarchar(2048), @severity tinyint, @state tinyint, @int0 int, @bin0 varbinary(MAX), @char0 nvarchar(MAX)',
        @msg, @severity, @state, @int0, @bin0, @char0;

END

Sadly, there's no easy way to scale this for an arbitrary number of parameters... It could probably be done using convoluted nested dynamic SQL, which would be fun to debug. I'll leave that as an exercise for the reader.

I used sql_variant on the assumption that for code uniformity reasons, the same procedure would be used everywhere, even for value types that are supported directly by RAISERROR. Also, this could be created as a temporary stored procedure if that's appropriate.

Here's what using this procedure would look like:

DECLARE @f float = 0.02345;
DECLARE @i int = 234;
DECLARE @s varchar(20) = 'asdfasdf';
DECLARE @b binary(4) = 0xA0B1C2D3;
DECLARE @d decimal(18, 9) = 152.2323;
DECLARE @n int = NULL;

EXEC [dbo].[MyRaiserror] N'Error message with no params.', 10, 1;
EXEC [dbo].[MyRaiserror] N'Float value = %f', 10, 1, @f;
EXEC [dbo].[MyRaiserror] N'Int value = %i', 10, 1, @i;
EXEC [dbo].[MyRaiserror] N'Character value = %s', 10, 1, @s;
EXEC [dbo].[MyRaiserror] N'Binary value = %#x', 10, 1, @b;
EXEC [dbo].[MyRaiserror] N'Decimal value = %f', 10, 1, @d;
EXEC [dbo].[MyRaiserror] N'Null value = %i', 10, 1, @n;

Output:

Error message with no params.
Float value = 0.02345
Int value = 234
Character value = asdfasdf
Binary value = 0xa0b1c2d3
Decimal value = 152.232300000
Null value = (null)

So the net result is that you don't get formatting ability for floats (roll your own), but you do gain the ability to output them (decimal/numeric, too!) while retaining formatting ability for the other types.


Combine the same column from multiple rows into one row

Took the cte code from @Aduguid, another alternative is

;WITH
hr_table
AS
(
    SELECT tbl.* FROM (VALUES
      ( 'Joe', 'Smith', 456654)
    ) tbl ([First Name], [Last Name], [Employee_ID])
)
,
user_defined_table
AS
(
    SELECT tbl.* FROM (VALUES
      ( 1, 'ABCD1234', '31-Aug-2018', 456654)
    , ( 2, '69090', '23-May-2022', 456654)
    ) tbl ([Area], [ud_1_text], [ud_2_text], [Employee_ID])
)

SELECT h.Employee_ID,h.[First Name],h.[Last Name],
MAX(CASE WHEN u.Area=1 THEN u.ud_1_text ELSE '' END) AS [D/L Number],
MAX(CASE WHEN u.Area=1 THEN u.ud_2_text ELSE '' END) AS [expiry],
MAX(CASE WHEN u.Area=2 THEN u.ud_1_text ELSE '' END) AS [First Aid],
MAX(CASE WHEN u.Area=2 THEN u.ud_2_text ELSE '' END) AS [expiry]
 FROM hr_table h
INNER JOIN user_defined_table u
ON h.Employee_ID=u.Employee_ID
GROUP BY h.Employee_ID,h.[First Name],h.[Last Name]


Can I upload database backup made through cPanel into phpmyadmin?

I've just checked and yes, the gzipped file is a standard mysql dump, so you can import it from PhpMyAdmin (given that the MySQL versions are compatible, of course).

The backup doesn't contain the CREATE SCHEMA statement, so you have to create the db first, and then import the backup into it.


Please, review my.cnf files (see oficial documentation for file locations https://dev.mysql.com/doc/refman/5.7/en/option-files.html).

Search under the [client] section if exist an entry called socket, i.e:

[client]
port=3306
socket=/tmp/mysql.sock

Replace it with the right path to mysql.sock and try again.
To get the mysql.sock, use ps -FU mysql -H | more and get the --socket value


Why are execution plans wildly different when selecting 1000 rows vs 2000?

As per other comments, it sounds like the cost threshold for parallelism is too low, and is making queries go parallel even when the process to do so/ gather the streams makes your process take longer.

Try setting that to 50, or experimenting with different numbers.

Also, worth a read: https://www.brentozar.com/archive/2017/03/why-cost-threshold-for-parallelism-shouldnt-be-set-to-5/


Inheritance of unknown type at creation time

Try searching  "single table inheritance" or "class table inheritance".  Especially the web pages from Martin Fowler.

Also, if you use a technique called "shared primary key", you can reference a person by that person's personid regardless of what occupation the person eventually chooses.  In the Plumber table, the primary key is not plumberid, but rather personid, which is a copy of the personid from persons.  You declare plumber.personid to be a primary key, and also to be a foreign key that references person.personid.

This has some rather nice results.  It makes joining plumbers and persons simple, easy, and fast.  It maintains the one-to-one or zero-to-one relationship between plumbers and persons.  And it allows you to reference a child without knowing  whether that child will grow up to be a plumber or not.


“Column does not exist” with INNER JOIN and MAX query in ORDER BY

To sort a given provider_id first, this is cheaper:

SELECT c.*, MAX(o.provider_id) AS provider_id
FROM   customers c
JOIN   orders    o ON o.patient_id = c.id
GROUP  BY c.id
ORDER  BY MAX(o.provider_id) <> 2        -- to list provider_id 2 first
        , MAX(o.provider_id);            -- to sort the rest ascending

Works because FALSE sorts before TRUE.
Add provider_id as 2nd ORDER BY item to sort the rest in ascending order.
(NULL sorts last in ascending order in any case.)

Note that your original expression @(2 - provider_id) also affects the sort of the rest since it folds values above and below the given provider_id. Makes it impossible to sort the rest in ascending order like you requested - and is therefore wrong on principal.

Assuming that referential integrity is enforced by a FOREIGN KEY constraint to providers (the typical setup), you don't need to include that table in the query at all. Faster.

Related:

Select rows for which at least one row per set meets a condition
Sorting null values after all others, except special

If that involves most or all of table orders it's faster to aggregate first and join later:

SELECT c.*, o.provider_id
FROM   customers c
JOIN  (
   SELECT patient_id, MAX(provider_id) AS provider_id
   FROM   orders
   GROUP  BY 1
   ) o ON o.patient_id = c.id
ORDER  BY o.provider_id <> 2, o.provider_id;

Why the error?

column "provider_id" doesn't exist

You tried to reference the output column name provider_id in the expression @(2 - provider_id) But expressions in the ORDER BY clause can only be formed from input columns. The manual:

  Each expression can be the name or ordinal number of an output column (SELECT list item), or it can be an arbitrary expression formed from input-column values.

Bold emphasis mine. Postgres couldn't find provider_id in the list of input column names and responded with the error you quoted.


how about going the OUTER APPLY route. You can modify the inner query to handle nulls differently, i've just disregarded them but if you want to handle them you could use ISNULL or CASE in the future.

SELECT users.name,
       H.join_date
FROM flexlm_user users
    OUTER APPLY
(
    SELECT MIN(history.time_out) AS join_date
    FROM flexlm_history history
    WHERE history.user_id = users.id
          AND history.time_out IS NOT NULL
) H
ORDER BY H.join_date;


Decrease Data IO operation on query to lower the DTU cost on Azure

I see a table scan and no use of any indexes in the execution plan. Create indexes to support your query and consider creating one to eliminate the sort. Apply page compression to your tables and any nonclustered indexes. Make sure you are updating stats and indexes - this maintenance is still required in Azure.


Script out objects that are in a specific schema

There's no automatic way to do that through the UI. You can selectively script just those objects, but it relies on manually checking a bunch of checkboxes:

If you need something better than that, you'll have to let go of the UI, roll up your sleeves, and invest some time in Powershell / SMO.


As JamesZ mentioned, the memory is intended for the SQL Server to use, particularly for the data cache.  Therefore SQL Server will attempt to use everything that has been made available to the SQL Server process.  (Plus some operating overhead.)

If you are concerned about reserving memory for Windows Operating System and any other processes that you run on the server you can adjust your maxservermemory to a lower number.

I have had good success with Jonathan Kehayias's "formula" that he posted here:

How Much Memory Does My SQL Server Actually Need?

The first paragraph has a good outline of how Kehayias would by default  configure the Maximum Memory.  Of course, this is not absolute and you will need to observe your SQL Server and determine whether more or less memory needs to be reserved.

Brent Ozar has a sanity check post at:
Memory Dangerously Low or Max Memory Too High

So, you should not have a script to tell you that there is too little memory available.  You should configure appropriately for what your system needs.

Brent Ozar's post for a 512 GB Server suggests:

EXEC sys.sp_configure ‘max server memory (MB)’, ‘471859’;
RECONFIGURE;


How to Log Debug Information About a Stored Procedure

One solution to the rollback problem is to log information to a table variable (which isn't affected by the transaction), then roll back, then insert into the real logging table. Quick example:

DECLARE @LogRow TABLE
(
  dt DATETIME, objectid INT,
  errornumber INT /* other columns */
);

BEGIN TRANSACTION;

BEGIN TRY

  SELECT 1/0;

  COMMIT TRANSACTION;

END TRY
BEGIN CATCH

  INSERT @LogRow(dt, objectid, errornumber /* , ... */)
    SELECT SYSDATETIME(), @@PROCID, ERROR_NUMBER() /* , ... */;

  ROLLBACK TRANSACTION;

  INSERT dbo.RealLogTable(/*...columns...*/)
    SELECT /*...columns...*/ FROM @LogRow;

END CATCH


NT SERVICE\MSSQL$SQLEXPRESS user cannot be found when added to network drive

When you map that S: drive to a network share, you are using your local Windows account to do it (the user you're logged in as). However, SQL Server Express is running under a different account.

There are a few things you can try to work around this.

Create a network share which has "Everyone" permission to write to, and then use the UNC path (\\server\path) as the target inside SQL Server for backups.
Run the SQL Server service under a different account, which happens to coincide with an account using the same name and password on the network resource.
Back up your databases to a local drive, and use some synchronization method (ROBOCOPY, XCOPY, etc.) to move those files to the network share, running under your normal Windows credentials. You can achieve this using the Windows Task Scheduler, running a PowerShell script or a Windows batch file.


Delete Joined to Derived Table Causes Index Scan and Table Spool

Is this query pattern generated by this library a known query anti-pattern for a delete statement in SQL Server?

This strikes me as a slightly curious question to ask. Why does the answer to that question matter? What would you do with that information? If the library that you're using doesn't meet your business needs, then four options come to mind:

Use a different library.
Implement a workaround, for example creating a stored procedure to do the deletes with more efficient code.
Providing feedback to the author of the library.
Opening a UserVoice feedback item or support ticket with Microsoft to get better optimizer support for this type of query.

With all of that said, I think that most SQL Server people would view that query with suspicion. I also think that it's fair to say that joining to tables that you don't need is an anti-pattern.

All of the relational databases that I've worked with have limitations around the inferences that they can make for eliminating unnecessary joins. One of the reasons that you get that ugly plan in SQL Server is that there isn't optimizer support for eliminating an inner self-join on a table's primary key. This can be observed with the following simple demo:

CREATE TABLE [dbo].[a_staging_table]
(
    [staging_ky] [BIGINT] IDENTITY(1,1) NOT NULL,
    [col_p] [BIGINT] NOT NULL,
    [filler] [VARCHAR](100) NULL,
PRIMARY KEY CLUSTERED ([staging_ky] ASC)
);

INSERT INTO [dbo].[a_staging_table] WITH (TABLOCK)
SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)), REPLICATE('Z', 100)
FROM master..spt_values t1
CROSS JOIN master..spt_values t2
OPTION (MAXDOP 1);

CREATE NONCLUSTERED INDEX [IDX_dbo_a_staging_table_col_p]
    ON [dbo].[a_staging_table]([col_p] ASC)
    WITH (FILLFACTOR = 100);

The following query:

SELECT j0.[staging_ky]
FROM [dbo].[a_staging_table] AS j0
LEFT OUTER JOIN [dbo].[a_staging_table] AS j1
ON j0.[staging_ky] = j1.[staging_ky];

Results in join elimination:

But a similar query with an INNER JOIN does not:

The optimizer doesn't support every possible query transformation. In this example, as programmers we can deduce that a join isn't necessary but the optimizer cannot. This might be unsupported in the product because Microsoft viewed this scenario as not common enough to address, the check was not worth the compile time overhead, or because it would be a difficult check to implement.

For completeness, the spool is present in the query plan due to halloween protection.


Join one-to-many field in a single result without concatenation

One way you can do this is with a PIVOT.

Here is the setup code:

create table grades (
  ID             int,
  Mapped_Heading varchar(10),
  Source_Value   varchar(10)
);
create table students (
  ID   int,
  Name varchar(10),
  Age  int
);
insert grades
values
(1,'English', 'pass'),
(1,'Math',    'pass'),
(1,'History', 'fail');
insert students
values
(1, 'Bob',   12),
(2, 'Harry', 12),
(3, 'Emily', 14);

Here is the PIVOT code:

with temp as (
  select s.*, g.Mapped_Heading, g.Source_Value
  from students s
  join grades g on g.ID = s.ID
)
select *
from (
    select *
    from temp
) a
pivot (
    max(Source_Value)
    for Mapped_Heading in ([English],[Math],[History])
) as b

The output looks like this:

+----+------+-----+---------+------+---------+
| ID | Name | Age | English | Math | History |
+----+------+-----+---------+------+---------+
| 1  | Bob  | 12  | pass    | pass | fail    |
+----+------+-----+---------+------+---------+

db<>fiddle demo


Open Windows Causing Locks After Sproc has Completed

Is this standard behavior?

No.  There was either a transaction started before the stored procedure ran, or the session was running with SET IMPLICIT_TRANSACTIONS ON, which can be set to default for SSMS query windows.
In any case if the session was blocking with no query running, it had an open transaction.


Certain statements report the percent complete in sys.dm_exec_requests percent_complete column:

  Percentage of work completed for the
  following commands:

  ALTER INDEX REORGANIZE
  AUTO_SHRINK option with ALTER DATABASE
  BACKUP DATABASE
  DBCC CHECKDB
  DBCC CHECKFILEGROUP
  DBCC CHECKTABLE
  DBCC INDEXDEFRAG
  DBCC SHRINKDATABASE
  DBCC SHRINKFILE
  RECOVERY
  RESTORE DATABASE,
  ROLLBACK
  TDE ENCRYPTION

For other statement, you'll have to wait it out and see.

First of all you'd have to check whether the 'query' is making any progress or is just sitting idle, blocked b something else. The same DMV mentioned above will immediately reveal this, the wait_time, wait_type and wait_resource are a dead give away: if they change, its making progress, if they stay fixed (and wait_time is growing) the query is stuck and the blocking _session_id is the blocker.

If the query was indeed making progress, and is one single query that is moving the rows, I gotta say: one does not go around and blindly moves 10M rows. If you did this in one single transaction, then your transaction log has been growing over the past 10 hours continuously. Every log growth event is a complete freeze of the database as it waits for the log to expand to zero out the new allocated disk space. Then the activity will resume, only to hit a new growth event in a short bit. By now, your database log must had grown several hundred times likely. This happens under all recovery models, including SIMPLE.

You could abort the query, but you'd have to wait until the 10 hours of the transaction will roll back. A 10h transaction can take another 10h to roll back, or more, or less, it depends on many many factors.

You could shutdown the server in panic and restart it, only to face the recovery of the database that is trying to undo the 10h transaction. Recovery will last, guaranteed, longer than rolling back the transaction.

So this is why one does never move 10M rows in a single 'query'. Batching the move in smaller sets is a must. Of course, this requires a carefully written program to do it.

Some users are lucky, they learn by stumbling on posts like this and making a note to self 'don't move 10M rows in one single operation'. Other learn the hard way... but they (hopefully) know better next time.


As you mentioned the contactNo is INT NOT NULL, in your CASE statement currently you are handling only for the value 8022 and not for the remaining values. If you add the ELSE 0 in the case statement it will solve the issue. It will select 0 for the entries other than contactNo = 8022

So the working code will be:

SELECT id, queueid, CAST((CASE contactNo WHEN 8022 THEN 1234 ELSE 0 END) AS INT) as contactNo, recordtime, hardwareid, easting, northing
FROM [dbo].[GpsPoint]

More explanation:

CASE contactNo WHEN 8022 THEN 1234 END this statement will return 1234 for the contactNo is 8022 and for the remaining entries it return only NULL.
So the CAST(NULL AS INT) also returns NULL only.
So inserting the NULL value into the NOT NULL value causing the error.


SELECT , GROUP AND DISPLAY IN TABULAR FORM

SELECT location,
       AVG(CASE WHEN qtr = 'Q1' THEN achievement END) q1-average,
       AVG(CASE WHEN qtr = 'Q2' THEN achievement END) q2-average
FROM sourcetable
-- WHERE year = '2018'
GROUP BY location;


Remote update/restore of database through SSMS

If you can connect to the server and you have the rights to do so, you can restore a database. The other thing that you'll need is to be able to get the backup files on to the servers hard drives or a network share that the SQL Server instance has rights to read.


SQL Server - Migration script testing - Refresh table definition manually without dropping it

One approach that works (only if there are no foreign keys referring to the table) is to create a backup of the original table with its original data.

Before starting to test the migration:

Grab a copy of the old table definition to define a copy of it, with a unique name (e.g. append "_bak" to the table name) and all named constraints (using sp_rename on just the table name will not automatically update named constrains).
SET IDENTITY_INSERT [bak] ON
Write a simple INSERT INTO [bak] (\every column\) SELECT \every column\ FROM [original] (the column list must be specified, otherwise you will get error 8101 for not explicitly writing out the identity column (why?))
SET IDENTITY_INSERT [bak] OFF

Note 1: Make sure to not enable the advanced SSDT publish option "drop objects in target not in source" or this table will get dropped if you publish.

Note 2: Computed columns must not be included in the \every column\ list.

Then each time you want to revert the table definition and populate it with the preexisting records:

DROP TABLE [target]
Copy the original table definition and run that CREATE TABLE ... code
SET IDENTITY_INSERT [target] ON
INSERT INTO [target](\every column\) SELECT \every column\ FROM [bak]
SET IDENTITY_INSERT [target] OFF

This is still far too manual of a process however (and breaks on foreign keys).


Maybe you need in
SELECT forum.*
FROM forum
JOIN ( SELECT mainthreadid, MAX(`date`) `date`
       FROM forum
       GROUP BY mainthreadid ) lastdates USING (mainthreadid, `date`)

?
fiddle
For each mainthreadid only last row (the row with greatest date) is selected. If there exists more than one such row (the same maximal date) then all of them will be returned.

you dont include checks for approved = 1 and if section != 150

No problems.
SELECT forum.*
FROM forum
JOIN ( SELECT mainthreadid, MAX(`date`) `date`
       FROM forum
       WHERE approved = 1 AND section != 150
       GROUP BY mainthreadid ) lastdates USING (mainthreadid, `date`)

fiddle


Extending fiat transaction log to support cryptocurrency

Any possible impact to original currency amount conversion that i need to look after for when i increase the precision?

Yes.

Carefully review your calculations against the table here: Precision, scale, and length, because you can have loss of precision when multiplying decimals:

  The result precision and scale have an absolute maximum of 38. When a
  result precision is greater than 38, it's reduced to 38, and the
  corresponding scale is reduced to try to prevent truncating the
  integral part of a result.


simple RECOVERY mode, but with not static data

Ultimately, your recovery model is dictated by your business needs.

It all boils down to the question "How much data is it okay to lose if the server goes belly-up?".

With FULL RECOVERY, that time is dictated by the time between log backups (and can be reduced if a tail-log backup is available).  You manage the log backups and how full they get by taking LOG backups.  If you don't take log backups, the log will simply continue to fill until it runs out of disk space.  At that time, transactions will fail due to insufficient disk space.

With SIMPLE RECOVERY, that time is increased to the time between the current time and your last differential or full backup.  The log is managed by CHECKPOINT operations that occur automatically behind the scenes (Recovery Interval).  Unless you are doing transactions whose size exceeds the size of the current log file, it won't grow.

This is also under the assumption that your backups do, in fact, work.  Backups are only valid if they can be restored successfully.

I'd recommend putting these decisions in the hands of your superiors after you present to them what the differences are and what that means in terms of RPO/RTO.


Does LIMIT 1 still have effect on a unique column

if column has unique index (constraint), You do not need to use LIMIT 1

LIMIT 1 - good tools for prevent potential errors when result MUST be single and You not sure about how many rows You have


Service broker not getting messages when enabled

To answer your questions :

Check default trace to see when it got disabled
when you altered the db with NEW_BROKER, it did more damage than you have thought of

  If you created any route with service name and service instance then it will become invalid as the route service instance value no longer reflect the destination service_broker_guid. It goes w/o saying that doing NEW_BROKER on a database that does have running conversations that span isntances (or even DBs in the same conversation), these conversations will have one half 'nuked' and the other half will be left stranded, unable to make any progress (will have to be cleaned up with END ... WITH CLEANUP or the other database will also have to be NEW_BROKER-ed).

Unfortunately, your best bet is to re-setup service broker from scratch.


Slow Full Text When using multiple full text searches

One common work-around for slow OR performance is to separate the parts and UNION ALL them back together:

select *
from dbo.company p with (nolock)
  join dbo.alt_company o  with (nolock) on p.org_id = o.org_id
where CONTAINS (p.description, '"phrase1*" OR "phrase2"')

UNION ALL -- note the "ALL", this will prevent a "DISTINCT"

select *
from dbo.company p with (nolock)
  join dbo.alt_company o  with (nolock) on p.org_id = o.org_id
where CONTAINS (o.description, '"phrase1*" OR "phrase2"')

You may be able to optimise further by referencing dbo.company only once in each part; I'll leave that to you to decide according to your needs and business rules.

Oh, and stop using select * and WITH (NOLOCK).


Calculating total "on row" bytes for each row ... the easy way

How can we access that algorithm directly?

In so far as the answer to this question, specifically, you can't access it directly. There is nothing where you can say SELECT GetMeMaxRowSize(MyTable, MyPartition, MyIndex).

However, as LowlyDBA has pointed out, you can use sys.dm_db_index_physical_stats to give you more information than ShowContig. The quite interesting thing is that if you run DBCC SHOWCONTIG(), capturing deprecated information, you should see a message to use the aforementioned DMV in place of the ShowContig command.


What is best for a primary key? Bigint of 20 numbers or varchar / varbinary of 15 characters?

Bignint is much faster than cvarchar or varbinary. at anbay time..
And so you can have 18446744073709551615 (unsigned) Rows.
You would use uuids, when you enter data in different databases and try to unite them.
but if you only need unique ids, use bigint.


Why can a new user select from any table?

Also addressing the question in the comments.

Role public

Per documentation:

  The key word PUBLIC indicates that the privileges are to be granted
  to all roles, including those that might be created later. PUBLIC
  can be thought of as an implicitly defined group that always includes
  all roles.

Bold emphasis mine. Membership in public cannot be revoked (or granted). You can only revoke privileges from public. In your case, to block out all roles without explicit privileges, like @Robert already provided:

REVOKE ALL ON DATABASE testdb FROM PUBLIC;

Role postgres

The database role postgres is a superuser by default. You can take that away from postgres - but that would be unwise: everybody (including some client programs) expects postgres to be a superuser. You can also create more superusers (careful with that!). Superusers don't need privileges. The manual once more:

  It should be noted that database superusers can access all objects
  regardless of object privilege settings. This is comparable to the
  rights of root in a Unix system. As with root, it's unwise to operate
  as a superuser except when absolutely necessary.

As to your question:

  Is it true that if \l or \dn+ displays empty access privileges, that
  is the same as postgres=UC/postgres, =UC/postgres

Not exactly.

Privileges are granted by the owner or a superuser (or a role that was granted the privilege to do so). postgres=UC/postgres would mean postgres granted it, which is not accurate for empty privileges, but the effect is the same in a default installation.
However, public gets no privileges for new schemas and only CONNECT and TEMP (can create temporary tables) privileges for databases by default, not the CREATE privilege (cannot create schemas). That would be something like =UT/?? according to the list of possible privileges - the manual once more:

        r -- SELECT ("read")
        w -- UPDATE ("write")
        a -- INSERT ("append")
        d -- DELETE
        D -- TRUNCATE
        x -- REFERENCES
        t -- TRIGGER
        X -- EXECUTE
        U -- USAGE
        C -- CREATE
        c -- CONNECT
        T -- TEMPORARY
  arwdDxt -- ALL PRIVILEGES (for tables, varies for other objects)
        * -- grant option for preceding privilege

You can change default privileges. I wrote more about that in the related answer mentioned in the comments:

Grant privileges for a particular database in PostgreSQL

But those default privileges only apply objects created after defaults were changed. So what does an empty ACL entry mean? The manual on the system catalog pg_default_acl:

  Note that when an ACL entry in another catalog is null, it is taken to
  represent the hard-wired default privileges for its object, not
  whatever might be in pg_default_acl at the moment.

Bold emphasis mine.


Integration Services 'All Execution Reports' Loads Forever

It turns out my SSIS package was so ineffective that it heavily consumes memory, causing the sessionID of SSISDB SUSPENDED (figured it out by using sp_who2)

I kill all the problematic suspended sessions, and the SSIS Execution Report works again!


How does the optimizer of Firebird determine join order?

The firebird-devel list on yahoo would be your best bet. Google "firebird query optimizer" and look for Dimitry Yemanov.

I don't think there are any better answer(s) to the question(s) than refer to firebird-devel. The system is developing quite quickly, and if I recall correctly, even fundamentals like how the optimizer works internally may be impossible to find out without either reading the code or checking on the devel list.

Yemanov is a major contributor to Firebird and anything he says might give some clue, but then again, a slideshare may not have enough detail. There is the Firebird Book by Helen Borrie, but again, the question may be beyond its scope.


How are multiple indexes used in a query by MySQL?

MySQL is capable of doing an INDEX MERGE which allows MySQL to search on both indexes and merge the results. Of course, the MySQL Query Optimizer is the arbitrator of this process.

From my perspective, just looking at your query and its WHERE clause, I would create a compound index on your table and an additional index. Which compound index, (a,b) or (b,a) ?

I would next check the cardinality of each column as follows:

SELECT COUNT(DISTINCT a) a_count FROM `table`;
SELECT COUNT(DISTINCT b) b_count FROM `table`;

Based on these counts

If a_count < b_count, then I would create compound index (a,b) and an index on b
If a_count > b_count, then I would create compound index (b,a) and an index on a
If a_count = b_count, then I would create one pair or other

For more information, please read the following links:

http://www.mysqlperformanceblog.com/2009/09/19/multi-column-indexes-vs-index-merge/
http://brian.moonspot.net/2008/04/22/playing-with-mysql-index-merge/
http://use-the-index-luke.com/sql/where-clause/searching-for-ranges/index-merge-performance


Incorrect syntax near the keyword 'Exec'

There are multiple issues with your string @SQLString:

You don't require + at the beginning and at the end
A date will not concatenate with a nvarchar without conversion (CAST or CONVERT)
Because your table will contain spaces you need to put the table name in square brackets: [ and ].
You will be better off with exec sp_executesql @SQLString

So your script might work with this:

DECLARE @SQLString NVARCHAR(MAX)
SET @SQLString = 'Create Table [' + CAST(GETDATE() AS NVARCHAR(30))+ '_Table]' + ' (' + 'Column1' + ' ' + 'Nvarchar(50) Null' + ')'
PRINT @SQLString
-- Exec sp_executesql @SQLString

I'd recommend reading up on the CREATE TABLE statement and on the String Functions. Then carry on to the Data Type Conversion documentation and have a glimpse at the sp_executesql syntax.

Good Luck.


If your database is marked suspect and you are desperate, then I'll advice you to set the status to that database, like:

update master.dbo.sysdatabases
set status = 20
where name = 'YourDatabaseName'
Very sorry that you have no backup.
Also have a look this Recommended       actions http://www.techrepublic.com/forums/discussions/it-seems-our-sql-database-is-corrupted/


MADlib apply `cosine_similarity` on term frequency

I may be wrong, but I don't think that Postgres has built-in TF-IDF functionality. I'd be super happy to learn that I'm wrong. Obviously, you can parse and roll your own counts.

In case you don't already know, there's a package that may be of interest to you called pg_similarity, here's one link:

https://salsa.debian.org/postgresql/pg-similarity

It's got a lot of measures. Many, including cosine similarity, can read data off of an index. The pg_similarity extension doesn't help you with storing or processing your corpus, but at least you get the math in a C function.

In a rare win for RDS, they've got this extension compiled and available. Otherwise, you need your own compile.

Here's hoping that you get a better informed answer...I'm just poking at the Postgres implementations of some of these tools myself.


SQL Server: subscriber as publisher and hierarchical replication

UPDATE: It seems it is with use of merge replication and republishing, what about transactional replication?

The near to same functionality can be achieved using Peer-to-Peer Transactional Replication. This is the only SQL Server replication technology that allows simultaneous writes at multiple nodes.

Peer-to-peer replication is a logical extension to bi-directional transactional replication handling the data layer only. The replication engine is used to replicate like it says "from everyone - to - everyone".

More info can be found at Publication Types for Transactional Replication. Here is a good example of configuring it.

Refer this white paper for a comparison of SQL Server and Oracle features.


To create a certificate for SSL encryption for SQL Server you need a certificate for Server authentciation. That is [EnhancedKeyUsageExtension]
OID = 1.3.6.1.5.5.7.3.1

If you want to manually create a certificate for this using Windows Certificate services you need to do it like this.

(http://blogs.technet.com/b/pki/archive/2009/08/05/how-to-create-a-web-server-ssl-certificate-manually.aspx)

Create a file named cert.inf

[NewRequest]
Subject = "CN=*FQDN*"
HashAlgorithm =  SHA256
KeyLength = 2048
Exportable = TRUE
KeySpec = 1
KeyUsage = 0x20
MachineKeySet = TRUE
ProviderName = "Microsoft RSA SChannel Cryptographic Provider"
ProviderType = 12
[EnhancedKeyUsageExtension]
OID = 1.3.6.1.5.5.7.3.1
[RequestAttributes]
CertificateTemplate= WebServer

And then generate the certificate using CERTREG.EXE

Certreq –new cert.inf cert.req
Certreq –submit cert.reg

To do the same using OpenSSL

# generate key
openssl genrsa -des3 -out server.key 2048

# remove pass
openssl rsa -in server.key -out server.key

# generate sign request, be sure to include the correct FQDN
# (host name followed by primary dns suffix)
openssl req -new -key server.key -out server.csr

# generate self signed certificate
openssl x509 -req -in server.csr -signkey server.key -out server.crt

# include both the certificate and the private key in a PKCS12 keystore
# (leave the export key empty)
openssl pkcs12 -export -out server.p12 -inkey server.key -in server.crt

In both cases you end up with a CRT that you can import to the machine store of the SQL Server and then use the SQL server configuration manager to encrypt the connections. You will have to trust the root certificate in the latter case though and in the former case, if you are not running on a domain you will have to install the root certificate for the windows CSA on both the machines


Is there a performance benefit to placing transaction log files on a separate drive?

Correct. In theory, if you have 100s of DBs you need 100s of drives, one for each log. In practice though one does not care for such case, cause when you have 100s of DBs you obviously don't expect top-notch TPC performance for each DB. You will likely have some DBs with high throughput and stringent SLAs and you could have them each on separate spindles, while the many lower level SLAs cram on a few shared disks.


Unexpected SLAVE STATUS after CHANGE MASTER

So after fiddling more, I've discovered the order of CHANGE MASTER ... is important.  Once I figured this out, I found this in the docs explaining in further detail.

  If you specify the MASTER_HOST or MASTER_PORT option, the slave assumes that the master server is different from before (even if the option value is the same as its current value.) In this case, the old values for the master binary log file name and position are considered no longer applicable, so if you do not specify MASTER_LOG_FILE and MASTER_LOG_POS in the statement, MASTER_LOG_FILE='' and MASTER_LOG_POS=4 are silently appended to it.

MySQL docs - CHANGE MASTER


Sleeping connections are not running queries, sleeping connections represent connections that the client/app failed to close successfully, most of the time due to bad programming practices (developers nos closing connections after querying the DB) or bad connectors configuration (not sending a close connection signal or even using an old/outdated connector).
Most of the cases this is not a big issue, but sometimes connection pool can grow out of control a represent a real hazard for real new requests traying to create new connections to the DB, so the best approach to "fix" this issue is to review the application/client and send a close() to the connection or check the connector.
You can also play with wait_ timeout variables to limit the time sleeping connections can remain active.


Create new table values based on partial column value - access

Since your starting position is fixed (after the 2nd _), you can use an Instr() function to find the position of the 3rd _, and just take the characters between.

Instr() returns the postion of the first instance of a character in a string. If the character is not found, it'll return 0. Note, Access considers the first character position to be 1.

Give this a try:

SELECT Mid(txn_id, 12, InStr(12, txn_id, '_')-12)
FROM MyTable`

Just for reference: https://support.office.com/en-us/article/InStr-Function-85d3392c-3b1c-4232-bb18-77cd0cb8a55b

Edit:

This is pretty gross, but if the 2nd _ position was also variable, this should work:

SELECT Mid(txn_id
           , Instr(Instr(1, txn_id, '_')+1, txn_id, '_')+1
           , Instr(
                   Instr(
                         Instr(1, txn_id, '_')+1
                         , txn_id
                         , '_')+1
                   , txn_id
                   , '_')-1 -  Instr(Instr(1, txn_id, '_')+1, txn_id, '_')
            )
FROM MyTable


i want to show the attendance of a student in percentage

I'm gonna assume couple of things here. For attendance column I'm gonna assume "Yes" if the student is present on that particular date, and "No" if otherwise.

SELECT f.name, f.subject, t2.attendance_percentage
FROM fsignup f
JOIN (SELECT subject, Yes/SUM(Yes+No)*100 AS attendance_percentage FROM (SELECT subject, SUM(CASE WHEN attendance="Yes" THEN 1 ELSE 0) AS Yes, SUM(CASE WHEN attendance="No" THEN 1 ELSE 0) AS No FROM attendance GROUP BY subject)t1)t2
ON f.subject=t2.subject
GROUP BY f.name, f.subject


Using Multi-table Insert for Parent and Child Table

No, you can't depend on this.  SQL is declarative, not procedural, so within a statement you can't guarantee the order of execution.  Since the entire INSERT ALL statement is considered a single statement (doc), you can't guarantee that one INSERT will be before another.

By definition an INSERT FIRST must execute the first INTO passing the evaluated conditions.  We might expect INSERT ALL to behave similarly.  This appears to be the case:

DROP TABLE T1;
CREATE TABLE T1 AS (SELECT 'a' c1, 0 c2, 0 c3 FROM dual WHERE 1=2);
INSERT ALL
   WHEN mod(x,2)<>0 THEN INTO T1 VALUES ('a', x, mod(x,2))
   WHEN mod(x,2)=0 THEN INTO T1 VALUES ('b', x, mod(x,2))
   SELECT Level x FROM dual CONNECT BY Level <=20;
COMMIT;
SELECT rowid, c1, c2, c3 FROM t1;

However, even though we can demonstrate a particular behavior on a particular platform/version/patchset still doesn't make this a guarantee.

Oracle-developer.net says it explicitly:

  the conditions in an INSERT FIRST statement will be evaluated in order
  from top to bottom. Oracle makes no such guarantees with an INSERT ALL
  statement.


Is SQL Server data compression categorically good for read-only databases?

Just my 2cents from my own experiments on 1-2 year old hardware:

Read-only operations (DW-style scans, sorts etc) on page-compressed tables (~80rows/page) I've found to break-even at compression size reduction of ~ 3x.

I.e. if the tables fit into memory anyway, page compression only benefits performance if the data size has shrunk by over 3x. You scan fewer pages in memory, but it takes longer to scan each page.

I guess your mileage may vary if your plans are nested-loop and seek-heavy. Among others, this would also be hardware-dependent (foreign NUMA node access penalties, memory speed etc).

The above is just a rough rule-of-thumb that I follow, based on my own test runs using my own queries on my own hardware (Dell Poweredge 910 and younger). It is not gospel eh!

Edit: Yesterday the excellent SQLBits XI presentation of Thomas Kejser was made available as a video. Quite relevant to this discussion, it shows the 'ugly' face of CPU cost for page compression - updates slowed down by 4x, locks held for quite a bit longer.

However, Thomas is using FusionIO storage and he picked a table that is only 'just' eligible for page compression. If storage was on a typical SAN and the data used compressed 3x-4x then the picture might have been less dramatic.


How about adding an extra column in you Job_Has_Parameter table?

Add a column called DataType and store what type of value the text contains.

When it comes to using the value, you might have to use the combination of case statement and cast/convert function.

You can take it to next step and add a table called datatype and make a foreign with this new column.


Is there slowdown inserting into an InnoDB table that has no index set?

On the contrary, a table with no indexes would be faster to run INSERTs on than a table with indexes.

However, in the case of InnoDB, there is already an internal index in use called the gen_clust_index. It is also better known as the Clustered Index. It exists for all InnoDB tables, even those with no PRIMARY KEY.

I have written about the gen_clust_index before

Sep 20, 2011 : Best of MyISAM and InnoDB
Mar 27, 2013 : Are there implicit indexes in InnoDB like MyISAM?
Feb 24, 2011 : what is gen_clust_index used for in mysql? (ServerFault)

Thus, adding a PRIMARY KEY on an InnoDB table with no PRIMARY KEY buys you nothing but another index.

Another headache in this regard is the fact that all lookups must eventually end up in the gen_clust_index, especially with secondary indexes. In fact, loadding data into an InnoDB table with secondary indexes will pile up changes in the Insert Buffer of the System Tablespace (See Pictorial Representation of ibdata1)


Insert range of value based on SQL where clause count

Wouldn't it just simply be:

select '12358', resource_code, 'N',
  rn = ROW_NUMBER() OVER (ORDER BY resource_code)
from dbo.sch_resource
where resource_desc like '%avid%media%comp%dongle'
ORDER BY rn;

If in your real code you're selecting more than one (whatever column 1 is), and you want the row numbering to start over with each new value in (whatever column 1 is), then you can say:

select [whatever column 1 is], resource_code, 'N',
  rn = ROW_NUMBER() OVER
       (PARTITION BY [whatever column 1 is] ORDER BY resource_code)
from dbo.sch_resource
where resource_desc like '%avid%media%comp%dongle'
ORDER BY [whatever column 1 is], rn;


Adding my comment as an answer since it solved the problem:

Try IS NULL instead of = NULL -- I forget that myself sometimes!

I think someone that knows about mathematics would say the NULL is nothing so an equals comparison does not work as expected.


remove postgres server from local machine

PgAdmin stores preferences and a list of servers in the .pgadmin directory in your HOME directory.
To reset it to a pristine state, you may delete this directory, when the application is not running.


In Data Warehouse Foreign Key on Fact tables Necessary?

Why are you updating keys?  The proper fix isn't to disable/enable referential integrity, but instead use surrogate keys in your data warehouse.  That way if a user ID or some other "key" that has meaning is adjusted, you're not breaking RI to do so.

A summary from the master, Ralph Kimball, himself:

  In a data warehouse, a surrogate key is a necessary generalization of the natural production key and is one of the basic elements of data warehouse design. Let’s be very clear: Every join between dimension tables and fact tables in a data warehouse environment should be based on surrogate keys, not natural keys. It is up to the data extract logic to systematically look up and replace every incoming natural key with a data warehouse surrogate key each time either a dimension record or a fact record is brought into the data warehouse environment.

Check out the linked article as Kimball goes into more detail on why this approach is necessary for data warehousing.


How to reuse an update trigger for multiple tables in postgresql?

Single trigger function for multiple triggers: possible - that's what you do.
Single trigger for multiple tables: not possible.

But you can shorten the code for creating lots of triggers:

DO
$$
BEGIN

EXECUTE (
SELECT string_agg('CREATE TRIGGER touch_users
   BEFORE UPDATE ON ' || quote_ident(t) || '
   FOR EACH ROW
   WHEN (OLD.modification_time IS NOT DISTINCT FROM NEW.modification_time)
   EXECUTE PROCEDURE touch_modification_time();'
 , E'\n')
FROM unnest('{users, company, foo, bar}'::text[]) t -- list your tables here
);

END
$$;

Or gather table names from system catalogs:
Tables accessed during last period


PostgreSQL invalid frontend message type 74

I'm getting this error and I have no idea where it's coming from or
  what it means. I can't find any documentation on frontend message
  types in the PostgreSQL documentation.

They're listed here:

https://www.postgresql.org/docs/current/static/protocol-message-formats.html

and a more general description of the protocol is at:

https://www.postgresql.org/docs/current/static/protocol-overview.html#PROTOCOL-MESSAGE-CONCEPTS

The type of the message is given by the first byte. For example, if a client issues a "Bind", the first byte is the ASCII code for 'B', or 66.

The value 74 from the error message would be 'J', but according to the doc this message type is not used for anything in any version of the protocol, so the client or middleware sending it must be misbehaving.

If the traffic is not encrypted, a packet inspection tool like Wireshark might help to look into these packets. Wireshark knows about the PGSQL protocol. On the postgres server side, I don't think you can get more information beyond the mentioned error, because the backend gives up on the message at the first byte. As far as it's concerned, it's not correlated to any query.


Sure, JSON doesn't support a special date type, (neither does JavaScript), JSONB only supports

string
number
boolean
null

So just use string interpolation, it'll get stored the same way:

SELECT (
  '{"value": 5, "time": "' || now() || '" }'
)::JSONB;

If you're trying to simply get NOW() from the server in one call, you can certainly do that using jsonb_set, or jsonb concatenation (||). Here are the docs

SELECT '{"value": 5}'::JSONB || jsonb_build_object('time', now());

In a third party library this may look like..

INSERT INTO table (json)
VALUES ( ? || jsonb_build_object('time', now()) );

It's still ugly. I wouldn't store a datatime in a JSONB, unless I had to:

It will be slow.
It will take more space.

As an aside, one of the alternatives to JSONB that Pg could have chosen is BSON which doesn't store dates as strings..


Setup a master<>slave configuration, then use the config parameter:

replicate-do-table=db_name.table_name

... to specify the tables that you wish to replicate. Either wildcard the table_name (if possible) or use multiple replicate-do-table= lines.


No, that is not possible. Replication cannot be cyclical.
You seem to be looking for a multi-master solution for PostgreSQL, but there are no open source solutions for that.
If I get that right that you have no unique or primary key constraints on the table, logical replication will not do what you want if the table contains duplicate rows.


Microsoft SQL 2016 database for Malwarebytes Endpoint Protection

If the application required a user name and a password, that means it attempts to connect using SQL Authentication and not using Windows AD authentication.

By default, SQL Server allows only for Windows Authentication, so you you need to enable the "mixed authentication" mode for SQL server. It can be enabled when installing, but if you used the defaults, see this article on how to change the server authentication mode.

Also make sure that the user you are creating on the SQL Server is a SQL login and not a windows login. It should be:

CREATE LOGIN <login_name> WITH PASSWORD = 'Password'

and not

CREATE LOGIN [<domainName>\<login_name>] FROM WINDOWS

HTH


How to remove all hidden parameters from Oracle Database

You'd need to add IS_SPECIFIED (or ISSPECIFIED) to the where clause, as some hidden parameters may be set by... other things.

A parameter can be removed from an spfile by issuing:

ALTER SYSTEM RESET "_some_hidden_parameter" scope = spfile;

You will likely have to stop and start the instance(s) to have the changes take effect.


Database after restore process still display Restoring

By default, a DB is not ready to use. It stays "restoring" ready to receive log restores.

Simply run this to bring the DB on-line

RESTORE DATABASE MyDB WITH RECOVERY

Or using the GUI, this is step 12 in the MSDN SSMS "how to" (a different place to what you mention)


How to List all roles and their members (users/groups) of an Azure Analysis Services tabular model/database

this should get you started (boogles my mind that there isn't a Get-RoleMember cmdlet)

[Reflection.Assembly]::LoadWithPartialName("Microsoft.AnalysisServices")

$ServerName = "asazure://aspaaseastus2.asazure.windows.net/alignpocaas"
$DB = "alignpocaas"

$MyServer = New-Object Microsoft.AnalysisServices.Server
$MyServer.Connect($ServerName)

$myDatabase = $myServer.Databases.Item($DB)
$myDatabase.Roles


Return hours, minutes and seconds left if column wasn't updated

If you want a single query, I suggest a data-modifying CTE like:

WITH cte(bound) AS (SELECT now() AT TIME ZONE 'UTC' - interval '1 day')
   , upd AS (
   UPDATE MyTable
   SET    next_update = now() AT TIME ZONE 'UTC'
   FROM   cte
   WHERE  next_update < cte.bound  -- why cast to timestamp?
   )
SELECT *, next_update - cte.bound AS time_to_update
FROM   MyTable, cte
WHERE  next_update >= cte.bound;

The point being that UPDATE and SELECT see the same snapshot and SELECT won't return the newly updated rows. (The fist CTE named cte is just for convenience, so we don't have to repeat the calculation of the bound.)

time_to_update is an interval of the form 23:53:27.141289 - hours:minutes:seconds:µs. Negative if there can be future timestamps - and possibly with leading days then: '-26 days -23:58:35.25222'. Else it cannot be greater than 24h in this query.

Assuming next_update is type timestamp, though the casts in your UPDATE are inconclusive (contradictory). now() AT TIME ZONE 'UTC' produces a timestamp, makes sense if next_update is of that type. But then why next_update::timestamp? (And why update a column named "next_update" to now()? Does not seem to make sense.)


MS SQL: get permissions of a fixed server role

Maybe this is what you are looking for:
SELECT SYS.SERVER_ROLE_MEMBERS.ROLE_PRINCIPAL_ID,
       ROLE.NAME AS ROLENAME,
       SYS.SERVER_ROLE_MEMBERS.MEMBER_PRINCIPAL_ID,
       MEMBER.NAME AS MEMBERNAME,
       MEMBER.TYPE_DESC,
       PERMISSION_NAME,
       STATE_DESC,
       CLASS_DESC
FROM SYS.SERVER_ROLE_MEMBERS
     FULL JOIN SYS.SERVER_PRINCIPALS AS ROLE ON SYS.SERVER_ROLE_MEMBERS.ROLE_PRINCIPAL_ID = ROLE.PRINCIPAL_ID
     FULL JOIN SYS.SERVER_PRINCIPALS AS MEMBER ON SYS.SERVER_ROLE_MEMBERS.MEMBER_PRINCIPAL_ID = MEMBER.PRINCIPAL_ID
     FULL JOIN SYS.SERVER_PERMISSIONS PERMISSIONS ON PERMISSIONS.GRANTEE_PRINCIPAL_ID = MEMBER.PRINCIPAL_ID


MySQL (mariaDB) trigger syntax always wrong

When trying to compile the original trigger code, we encounter quite a few issues.  As some colleagues have pointed out, the timing ("after") should be adjusted.  There are some syntactical and logical problems, too (eg < instead of >).  Maybe it would be better to use BETWEEN ... AND ... for defining the "levels".

--  test data

create table members (member_id int, experience int, level_ int);

insert into members values (1,2200,1),(2,3800,2),(3,7800,3);

Trigger (MySQL 5.7)

DELIMITER $$
create trigger update_level
  before update on members
  for each row
begin
  if new.experience between 0 and 999 then
    set new.level_ = 1;
  elseif new.experience between 1000 and 2399 then
    set new.level_ = 2;
  elseif new.experience between 2400 and 3999 then
    set new.level_ = 3 ;
  elseif new.experience between 4000 and 7999 then
    set new.level_ = 4 ;
  elseif new.experience between 8000 and 14999 then
    set new.level_ = 5 ;
  elseif new.experience between 15000 and 23999 then
    set new.level_ = 6 ;
  elseif new.experience between 24000 and 34999 then
    set new.level_ = 7 ;
  elseif new.experience between 35000 and 49999 then
    set new.level_ = 8 ;
  elseif new.experience >= 50000 then
    set new.level_ = 9 ;
  end if;
end;
$$
DELIMITER ;

Testing

mysql> select * from members;
+-----------+------------+--------+
| member_id | experience | level_ |
+-----------+------------+--------+
|         1 |       2200 |      1 |
|         2 |       3800 |      2 |
|         3 |       7800 |      3 |
+-----------+------------+--------+
3 rows in set (0.00 sec)

update members set experience = 8500 where member_id = 1 ;
update members set experience = 16000 where member_id = 2 ;
update members set experience = 25000 where member_id = 3 ;

mysql> select * from members;
+-----------+------------+--------+
| member_id | experience | level_ |
+-----------+------------+--------+
|         1 |       8500 |      5 |
|         2 |      16000 |      6 |
|         3 |      25000 |      7 |
+-----------+------------+--------+
3 rows in set (0.00 sec)

update members set experience = 36000 where member_id = 1 ;
update members set experience = 55000 where member_id = 2 ;
update members set experience = 10 where member_id = 3 ;

mysql> select * from members;
+-----------+------------+--------+
| member_id | experience | level_ |
+-----------+------------+--------+
|         1 |      36000 |      8 |
|         2 |      55000 |      9 |
|         3 |         10 |      1 |
+-----------+------------+--------+
3 rows in set (0.01 sec)

ALTERNATIVE

A VIEW may be a better solution for this scenario.  We could use a different table layout for MEMBERS, and avoid storing levels ("level" depends on "experience", not on member_id ...), like so (MariaDB 10.2):

-- test data
create table members (member_id int, experience int);

insert into members values (1,2200),(2,3800),(3,7800);

Now we can find the current level associated with a member_id by SELECTing from the VIEW.  No trigger required.

create or replace view member_level
as
select
  member_id
, experience
, case
    when experience between 0 and 999 then 'level 1'
    when experience between 1000 and  2399 then 'level 2'
    when experience between 2400 and  3999 then 'level 3'
    when experience between 4000 and  7999 then 'level 4'
    when experience between 8000 and 14999 then 'level 5'
    when experience between 15000 and 23999 then 'level 6'
    when experience between 24000 and  34999 then 'level 7'
    when experience between 35000 and  49999 then 'level 8'
    when experience >= 50000 THEN 'level 9'
  end as level_
from members ;

Testing

select * from members;

member_id  experience
1          2200
2          3800
3          7800

update members
set experience = 27500
where member_id = 1 ;

select * from member_level;

member_id  experience  level_
1          27500       level 7
2           3800       level 3
3           7800       level 4

See dbfiddle here.

Another alternative (MariaDB version 5.2+, MySQL 5.7)

Use a virtual column, let the DDL do the work.

create table members (
  member_id int
, experience int
, level_ varchar(32) as (
    case
      when experience between 0 and 999 then 'level 1'
      when experience between 1000 and  2399 then 'level 2'
      when experience between 2400 and  3999 then 'level 3'
      when experience between 4000 and  7999 then 'level 4'
      when experience between 8000 and 14999 then 'level 5'
      when experience between 15000 and 23999 then 'level 6'
      when experience between 24000 and  34999 then 'level 7'
      when experience between 35000 and  49999 then 'level 8'
      when experience >= 50000 THEN 'level 9'
    end ) virtual
);

insert into members (member_id, experience) values (1,2200),(2,3800),(3,7800);

select * from members;

member_id  experience  level_
1          2200        level 2
2          3800        level 3
3          7800        level 4

Dbfiddle here.


NDF are not file groups. NDF files are additional data files. You may achieve better performance by adding additional files or you may not, it depends.

Read Benchmarking: do multiple data files make a difference? by Paul Randal.

What I think you had in mind are additional file groups, whereby you create an object in a new file group and put that group into separate file (NDF). To ease I/O, large, frequently accessed tables will achieve better performance in separate file groups, providing those file groups sit in separate files and those files are on different LUNs, and LUNs are on separate spindles, and so on.

If you only have one disk the difference may not be noticeable.

You may also take a look at Using Files and Filegroups from Microsoft.

With SSDs, it depends on your array config and class of the storage. SSDs have no more throughput but lower latency, so you should do some performance testing first, and see if is good enough to satisfy SLA with your production workload. At the end of the day, only thorough performance tests can clear your doubts.


No, the query optimizer will not create statistics for a view unless it's an indexed view, you can create an XML index on the xml column itself to speed up the view,


The difference between one and two queries may not be as large as you'd think. For example, compare this query:

declare @t table (id int)
insert into @t select id from Table1 where col1 = 7
update Table1 set col2 = 8 where id in (select id from @t)

to this query:

update Table1 set col2 = 8 where id in (select id from Table1 where col1 = 7)

For the second query, SQL Server could run the subquery first, and store the result in a  temporary table.  If it did that, that would be the EXACT SAME way it executes the first query!

So the effect of splitting a query in two is that it limits the query optimizer's choice.  The first query demands that the subquery is executed before the updates.  The second query leaves the optimizer free to fetch one row, then update the table, and then fetch the next row.

Now when you upgrade the hardware, SQL Server version, or if the database changes, you would ideally re-verify that your limitation is in fact beneficial. That's kind of expensive.

So I would prefer not split the query, or enforce a query plan, unless the expense is offset by a considerable advantage.  Going from 0.07 to 0.3 seconds doesn't look like a big difference to me, unless the query is run more than once every minute or so.


What value should I choose as length for timestamp without timezone in PostgreSQL?

It's not the "length", it's the precision of the timestamp, so it controls the fractional seconds (milliseconds, microseconds) you can store.
e.g. '2020-12-14 23:14:10.123456'::timestamp(0) returns 2020-12-14 23:14:10 and '2020-12-14 23:14:10.123456'::timestamp(3) returns 2020-12-14 23:14:10.123
As you get a unix epoch which has no fractional seconds, timestamp(0) should be OK.
But in the end it doesn't matter, as changing the precision doesn't change the storage requirements. It's always 8 byte regardless of the precision you choose.


Best mysql db design to solve timezone issues

Off the top of my head, the simplest thing I could think of would be to store three values:

UTC (DATETIME),
timezone offset for datetime in minutes (SMALLINT) (-1440 to 1440)
DST (daylight saving time), 0 or 1 (TINYINT)

I originally was thinking of timezone offset in hours. @yercube reminded me of other places with 15 min or 30 min additional offsets . For example, these cities have these times:

New York City at 4:00 PM
Bangalore 1:30 AM next day
Katmandhu 1:45 AM next day

You would have to

perform date arithmetic to handle display of datetimes
compare datetimes as pure UTC values


SQL Server: update trigger fires before insert trigger

Considering that a) Triggers are naturally part of the Transaction that is the DML statement that fired the Trigger, and b) an UPDATE cannot happen on a row until the row exists, it is impossible for the actual UPDATE to show up before the actual INSERT. Hence, something else is going on.

Things to consider:

Are the Trigger definitions shown in the question the actual and current definitions? Is it possible that the Comment values of "Insert" and "Update" are switched in the Triggers such that the INSERT Trigger has the comment of "Update" and vice-versa?
Is it possible that the UPDATE Trigger is somehow defined as being AFTER INSERT, UPDATE ? If so, an INSERT operation would fire both triggers making it look like both an INSERT and an UPDATE happened when in fact there was no UPDATE operation (which might also explain why the times are only a few milliseconds apart for the audit entries).
Is it possible that your query to determine "corresponding" records is flawed and that the results are misleading?


A composite index with any order of col1,col2,col3 would work here. The query you have shown in the question would be able to perform three seeks into it for the three or-ed predicates.

To determine the best order out of the 6 possible permutations I would first look at other queries, or alternatively you could look at the missing index DMVs. Quite likely you will find that the choices you make optimising this one query can help optimise others too.

If you have queries that perform equality seeks using just two of the columns then put them first so the same index supports those queries. And similarly if one of those columns is often used on its own in an equality or range predicate then make that the leading one.

Though if any of the columns is not particularly selective you would need to verify that the index is actually used for the query identified by the process above and consider whether it is worth adding included columns to make it covering if not (or considering a different index order that would benefit a different query).


Changing to compatibility level 150 makes SQL Server consume all available memory

You're encountering a known bug with scalar UDF inlining.  You can disable inlining using one of the methods here (or by using a lower compat level, as you've discovered yourself):

Disabling Scalar UDF Inlining without changing the compatibility level

Or install CU2 for the permanent fix.

  ...even running a simple scalar function (which internally uses several Substring and cast calls - no table or view selects) is enough to make it consume all the available memory...

Reading this part of your issue reminded me of this really interesting blog post from sqL_handLe:

SQL Server 2019 Scalar UDF inlining - OOM in some cases

He demonstrates a scalar UDF that doesn't do any data access, but uses up all the memory on the box (even one with almost a TB of RAM):

  Not just crashing itself.  Crashing any other query on the instance that was trying to allocate memory (eg stealing against a query memory grant).

This should be fixed in SQL Server 2019 CU2, it looks like this is the KB for it:

FIX: Scalar UDF Inlining issues in SQL Server 2019

  This cumulative update includes several fixes across the following areas for scenarios where a query that uses Scalar UDF Inlining may return an error or unexpected results:

  ...
  Out of memory conditions and memory leaks due to very large scalar UDFs
  ...

If you're unable to install CU2 for some reason, you can use the newer compatibility level as long as you disable scalar UDF inling at the database level like this:

ALTER DATABASE SCOPED CONFIGURATION SET TSQL_SCALAR_UDF_INLINING = OFF;

Or at the query level, as described in the docs article I linked to earlier.


MySQL is not releasing memory after memory tables are dropped

Per suggestion in a MariaDB server bug Memory not freed on memory table drop, replacing default glibc with jemalloc solves the problem.

Steps below shows how to launch mysqld with jemalloc:

docker run -it -m 4g --shm-size 4g --name mysql_test \
    -e MYSQL_ALLOW_EMPTY_PASSWORD=yes mysql:5.7.25 bash
apt-get update
apt-get install -y libjemalloc-dev
# adhoc preload jemalloc for ease of testing
# updating /etc/mysql/my.cnf is preferred for permanent setting
LD_PRELOAD=/usr/lib/x86_64-linux-gnu/libjemalloc.so.1 docker-entrypoint.sh mysqld

Then we may proceed testing from step 3 in another shell. Note that memory usage of mysqld drops by about 1G for each drop table statement.
At the end, memory usage drops to level similar to initial stage and creating a 3G file in /dev/shm is possible.

Update:

The actual library needed is libjemalloc1 in this case.
libjemalloc-dev includes libjemalloc1 as a dependency and that's why it worked but at the price of installing a bunch of files we don't need. Thanks Michael - sqlbot for pointing this out.


You will need to buy a separate license for the server running SSRS. Unless you have a virtual host where you have bought an enterprise license for every core in the server (see the chapter on Licensing for Maximum Virtualization)


You should use joins to reduce lookups between tables and to make better using of indexes during query the data. As expecting your query on relational tables I suggest a pattern as follows.

SELECT CM.* from ContactMaster CM
INNER JOIN ListContactMaster LCM ON CM.ContactId=LCM.ContactId
LEFT JOIN supresscontacts SC ON CM.ContactId=SC.ContactId
LEFT JOIN DomineSupress DS ON CM.ContactId=DS.ContactId
WHERE SC.emailaddress IS NULL AND DS.contactid IS NULL AND DS.CampaignId=1186 AND LCM.ListMasterID IN (816,975,945)
AND LCM.ListMasterID <> 0 AND (LCM.isactive='true' OR LCM.isactive IS NULL)
AND CM.isactive = 'true' AND (CM.isdeleted='false' or CM.isdeleted is null)

Hope it will help you to make your query better.


I think you are running this in the context of master, when you are creating partitions you need to be in the context of the database, before you create the function and scheme do USE[database_name].

If you check master you will see your partition function is there, not in the database you think it is.


MySQL - Old ibdata file shall I delete safely

Great question, and a very common issue.

When you changed the server configuration, to innodb_file_per_table = 1, the tables still lived on the common tablespace. You still had to recreate those using ALTER TABLE ENGINE=InnoDB (or any other operation that forced a tablespace recreation).

Once you have done that, why does MySQL still have a huge ib_data1? The truth is that InnoDB cannot really shrink (defragment) its tablespace files. That is not a huge issue in most cases, because the free space inside every file can be used by other data, index, etc. pages, and once you start using innodb_file_per_table you can always recreate each individual tablespace individually in order to free filesystem space.

However, the common tablespace is special. Even if it didn't contain any table or index information, it is always compulsory as it stores common data like the data dictionary, change buffer, etc. So if you delete it, you would end up with a fully unusable mysql installation. Answering your questions: 1) Yes it is being still used and 2) you won't be able to restart mysql again if you delete it. But, obviously, most of the ibdata1 file will be empty, it is just that it is very difficult to move page addresses around.

Here is a Pictorial Representation of InnoDB Architeture so you can see the contents of ibdata1

So, do you have to live with the 10GB file forever? Well, it is certainly a possibility, but there are ways to get rid of it, depending on the server version and your need for availability:

One approach, if availability is not a problem, and the database is small, would be to dump the whole database into a .sql or .txt with tools like mysqldump or mydumper,stop the server, delete all the files (or at least, all the innodb files), start mysql again and reload the data making sure that innodb_file_per_table is on. Very slow, but very easy.
If you do not want to suffer such a long process, there are ways to do it fully online, using a slave- you just set it up right from the beginning, and then switch nodes. If you do not have a second box, you can setup a temporary "slave" on the same machine (with the problem of extra resources needed)
If you use MySQL >= 5.6, you can move tablespaces around with IMPORT TABLESPACE. That would be a way to perform a quick backup, stop and wipe, and restore in binary format (way faster than with .sqls). Some tools like Percona XtraBackup may be useful for that.

This is a very common operation I've been asked about and performed a MySQL DBA. Hope this helps.