What should we call a table that stores changes to another table? At the moment, we have a Persons Table. But we also need another table to store changes made to that Persons Table. But, my question is, what naming convention is used for tables that store changes made to other database tables.

In our company, we use to suffix the table name with _history. So, for the persons table you might have a table named persons_history.


argument 'and' incorrect, sum values of attributes excuse the disturbance, but a comparison does not give me a boolean value, because the sum of the attributes does not return an integer value, but bigint. How do you add up the values of three queries? I tried with 'UNION' and with + but it gives me error.

case when(--for example 8=8
/*8=*/      (select count(*) from voto where new.data=voto.data and
voto_palese='favorevole') =(
/*=8*/       ((
    (select count(*) from voto where new.data=voto.data and
voto_palese='favorevole')
     UNION(select count(*) from voto where new.data=voto.data and
voto_palese='contrario') UNION
     (select count(*) from voto where new.data=voto.data and
voto_palese='astenuto'))/2)+1) and
      (select count(*) from voto where new.data=voto.data
 and(voto.ddl=new.ddl or voto.mozione=new.mozione))
           )
 then new.esito='approvato'
 else new.esito='non approvato'
 end, --esito,

ERRORE:  l'argomento di AND deve essere di tipo booleano, non bigint
LINE 34:           (select count(*) from voto where new.data=voto.dat...
               ^
QUERY:  insert into public.votazione(
       mozione,
       ddl,
       codice,
       favorevoli,
       votanti,
       contrari,
       astenuti,
       esito,
       tipo_assemblea,
       seduta_numero,
       data,
       assenti,
       missione)
 values((select codice from mozione where codice=new.mozione and
 mozione.data=new.data),--mozione,
 (select titolo from ddl where titolo=new.ddl), --ddl,
 (select codice from ddl where new.ddl=ddl.titolo), --codice,
 (select count(*) from voto where new.data=voto.data and
                               voto_palese='favorevole'), --favorevoli,
 ((select count(*) from voto where new.data=voto.data and
                               voto_palese='favorevole')
 UNION(select count(*) from voto where new.data=voto.data and
                               voto_palese='contrario') UNION
       (select count(*) from voto where new.data=voto.data and
                               voto_palese='astenuto')), --votanti,
 (select voto_palese from voto where data=new.data and
 voto_palese='contrario'), --contrari,
 (select voto_palese from voto where data=new.data and
  voto_palese='astenuto'), -- astenuti,
 case when(--for example 8=8
/*8=*/      (select count(*) from voto where new.data=voto.data and
voto_palese='favorevole') =(
 /*=8*/       ((
    (select count(*) from voto where new.data=voto.data and
 voto_palese='favorevole')
     UNION(select count(*) from voto where new.data=voto.data and
 voto_palese='contrario') UNION
     (select count(*) from voto where new.data=voto.data and
 voto_palese='astenuto'))/2)+1) and
      (select count(*) from voto where new.data=voto.data
 and(voto.ddl=new.ddl or voto.mozione=new.mozione))
           )
 then new.esito='approvato'
 else new.esito='non approvato'
 end, --esito,
 (select tipo_assemblea from ordine_del_giorno_ass_cam_e_sen where
 data=voto.data), --tipo_assemblea,
 (select seduta_numero from ordine_del_giorno_ass_cam_e_sen where
 data=voto.data), --seduta_numero,
 (SELECT data from ordine_del_giorno_ass_cam_e_sen where
   new.tipo_assemblea=ordine_del_giorno_ass_cam_e_sen.tipo_assemblea and
 ordine_del_giorno_ass_cam_e_sen.data=voto.data), --data,
 (select count(*) from timbratura_giornaliera where presenze='Assente' and
 new.data=timbratura_giornaliera.data), --assenti,
 (select count(*) from timbratura_giornaliera where presenze='In missione'
 and new.data=timbratura_giornaliera.data) -- missione
 )
  CONTEXT:  funzione PL/pgSQL inserimento_votazione() riga 4 a istruzione SQL

  ********** Error **********

  ERRORE: l'argomento di AND deve essere di tipo booleano, non bigint
   SQL state: 42804
  Context: funzione PL/pgSQL inserimento_votazione() riga 4 a istruzione SQL

You should use a comparison operator like > < = >= <=

create table t (id int);
select case when (select count(*) from t) then 1 else 0 end

  ERROR:  argument of CASE/WHEN must be type boolean, not type bigint
  LINE 1: select case when (select count(*) from t) then 1 else 0 end

select case when (select count(*) from t) > 0 then 1 else 0 end
                                          ^^^

| case |
| ---: |
|    0 |

db<>fiddle here


Why is the value 0 for the Max_data_length of my tables in my database We are using a commercial version 5.6.16 of MySQL database which seems to have a 4GB limit. We have hit 3.96GB and are experiencing performance issues with the database. We came across some documentation that says you can increase the database size by increasing the max data length. Anyway when I use the call

mysql> show table status like 'weather' \G

in CMD to find my tables size limit. I see that the Max_data_length is 0 and not 4294967295 which is the typical size of the database. So why is my Max_data_length 0?

Table size limits are affected by the OS and the file system.

From MySQL (5.6) documentation, Limits on Table Size:

  The effective maximum table size for MySQL databases is usually determined by operating system constraints on file sizes, not by MySQL internal limits. The following table lists some examples of operating system file-size limits. This is only a rough guide and is not intended to be definitive. For the most up-to-date information, be sure to check the documentation specific to your operating system.

Operating System                      File-size Limit
-------------------------------------------------------------
Win32 w/ FAT/FAT32                    2GB/4GB
Win32 w/ NTFS                         2TB (possibly larger)
Linux 2.2-Intel 32-bit                2GB (LFS: 4GB)
Linux 2.4+  (using ext3 file system)  4TB
Solaris 9/10                          16TB
OS X w/ HFS+                          2TB

So there the problem lies.

Solution:

Take a backup.
Get another machine / VM with a 64-bit OS.
Install MySQL.
Restore your database there.
Profit ;)


Help needed in creating a SQL statement containing four joins Please help. Given a studentID and a TagNumber i want to get the UserID related to that TagNumber. The difficult condition is that TagNumbers can be duplicate within a Users table. So we can pinpoint the exact UserID using the UserCities table as a student is linked to a school and a school is linked to a city and UserCities table have linking information for users and cities. You can assume that the given studentID:45 and TagNumber:99.

Users:

UserID, TagNumber

Students:

StudentID, SchoolID

Schools:

SchoolID, CityID

UserCities:

UserID, CityID

I have tried this so far:

select
    u.UserID
  from
    UserCities uc  inner join School sc on uc.CityID = sc.CityID
    inner join Student st on sc.SchoolID = st.SchoolID
  where
    st.StudentID = 9

I have done it myself.

select
    userID
from
    UserCities
where
    CityID IN (select CityID
               from school sc, student st
               where sc.SchoolID = st.SchoolID and StudentID = 382)
    and userID IN (select userID
                   from users
                   where tagNumber = 4521) ;


Which index type is best suited for exact-match against a list of values? I have this table:

CREATE TABLE public.foos (
    id integer NOT NULL,
    bar_string character varying
);

I want to do this query:

SELECT id from foos where bar_string in ('one', 'two', 'three')

The size of the list will be something like 5-100.

If I know I'll only ever be doing this query, what's the best index to put on bar_string? I've read that Hash indexes are better than btree for exact matches, is this the way to go?

TL;DR: BTREE looks like it is a bit more efficient than HASH. You should read and adapt this test to your situation

Benchmark

I tried the two solutions for a geohash id column. Here is the script I used:

require 'set'

def random_geohash(precision)
  letters = %w(0 1 2 3 4 5 6 7 8 9 b c d e f g h j k m n p q r s t u v w x y z)

  letters.sample(precision).join('')
end

geohashes = Set.new

loop do
  geohashes << random_geohash(9)
  break if geohashes.length == 50_000
end

sample = geohashes.to_a.sample(1_000).map { |h| "'%s'" % h }.join(", ")

IO.write("perfs.init.sql", <<~SQL)
  DROP TABLE IF EXISTS "tests";

  CREATE TABLE "tests" (
  "id_btree" text NOT NULL,
  "id_hash" text NOT NULL
  );

  CREATE INDEX "index_id_btree" ON tests USING btree (id_btree);
  CREATE INDEX "index_id_hash" ON tests USING hash (id_hash);

  INSERT INTO tests (id_btree, id_hash) VALUES #{geohashes.map { |h| "('%s', '%s')" % [h,h] }.join(", ")};
  VACUUM ANALYZE;
SQL

IO.write("perfs.btree.sql", <<~SQL)
  EXPLAIN (ANALYZE, COSTS, VERBOSE, BUFFERS, FORMAT JSON)
  SELECT * FROM tests WHERE id_btree IN (#{sample});
SQL

IO.write("perfs.hash.sql", <<~SQL)
  EXPLAIN (ANALYZE, COSTS, VERBOSE, BUFFERS, FORMAT JSON)
  SELECT * FROM tests WHERE id_hash IN (#{sample});
SQL

IO.write("perfs.end.sql", <<~SQL)
  DROP TABLE IF EXISTS "tests";
SQL

`psql --user=john_doe -qAt -f perfs.init.sql my_db`
`psql --user=john_doe -qAt -f perfs.btree.sql my_db > perfs.btree.json`
`psql --user=john_doe -qAt -f perfs.hash.sql my_db > perfs.hash.json`
`psql --user=john_doe -qAt -f perfs.end.sql my_db`

This will give you two explain analyze (perfs.<index>.json) that you can now read. I suggest using Pev for a comfortable lecture. Here are the text results for PG11 on my mac:

BTREE

Index Scan using index_id_btree on public.tests  (cost=0.29..2611.50 rows=1000 width=20) (actual time=0.988..4.347 rows=1000 loops=1)
  Output: id_btree, id_hash
  Index Cond: (tests.id_btree = ANY ('{e6g8nf3kv,...,3r9n5udye}'::text[]))
  Buffers: shared hit=3014
Planning Time: 1.475 ms
Execution Time: 4.424 ms

HASH

Bitmap Heap Scan on public.tests  (cost=1179.75..2758.75 rows=1000 width=20) (actual time=1.784..15.275 rows=1000 loops=1)
  Output: id_btree, id_hash
  Recheck Cond: (tests.id_hash = ANY ('{e6g8nf3kv,...,3r9n5udye}'::text[]))
  Heap Blocks: exact=304
  Buffers: shared hit=1463
  ->  Bitmap Index Scan on index_id_hash  (cost=0.00..1179.50 rows=1000 width=0) (actual time=1.714..1.714 rows=1000 loops=1)
        Index Cond: (tests.id_hash = ANY ('{e6g8nf3kv,...,3r9n5udye}'::text[]))
        Buffers: shared hit=1159
Planning Time: 1.495 ms
Execution Time: 15.446 ms


SQL Memory Usage Cumulative for each Database The following query shows how to find Current view of Buffer Memory used by each database.
How do I find Average/Cumulative Memory buffer usage of each database since the last SQL server restart, not just a current time snapshot?

We have 5 microservice databases on one server, no cross database joins, do not relate. Stored procedures are called from appropriate db. We are trying to separate into different servers, and find baseline Ram and core processor requirements, not sure best way to do this

Thanks,

Determine SQL Memory usage by database

-- Note: querying sys.dm_os_buffer_descriptors
-- requires the VIEW_SERVER_STATE permission.

DECLARE @total_buffer INT;

SELECT @total_buffer = cntr_value
FROM sys.dm_os_performance_counters
WHERE RTRIM([object_name]) LIKE '%Buffer Manager'
AND counter_name = 'Database Pages';

;WITH src AS
(
SELECT
database_id, db_buffer_pages = COUNT_BIG(*)
FROM sys.dm_os_buffer_descriptors
--WHERE database_id BETWEEN 5 AND 32766
GROUP BY database_id
)
SELECT
[db_name] = CASE [database_id] WHEN 32767
THEN 'Resource DB'
ELSE DB_NAME([database_id]) END,
db_buffer_pages,
db_buffer_MB = db_buffer_pages / 128,
db_buffer_percent = CONVERT(DECIMAL(6,3),
db_buffer_pages * 100.0 / @total_buffer)
FROM src
ORDER BY db_buffer_MB DESC;

I don't believe there is any cumulative / average metric for this. Nor do I think it makes sense to look at it this way. I don't think you can derive any useful conclusion from such a view of things. This information is too volatile to use in that way. Some stuff ages out faster than others. It greatly depends on which pages are being accessed frequently, how much memory is available on the system, etc. Also, the Buffer Pool is not the only use of memory. There is also permissions cache, plan cache, DB Metadata cache, and others. Hence, such an approach will most likely be misleading.

In addition to looking in sys.dm_os_buffer_descriptors, there are also the following resources to check:

DBCC MEMORYSTATUS;
SELECT * FROM sys.dm_os_memory_clerks;
SELECT * FROM sys.dm_os_performance_counters;

Regarding those three resources, please note:

there is some duplication between the DBCC and the others (and even some other DMVs not listed here)
in each of those resources, there are some entries that cover all databases and cannot easily be broken down, such as Connection Pool cache, plan cache, etc


SQL Database Structure for RESTful API I am creating a RESTful API. I am struggling to decide on the best way to design my database tables around my resources.

Initially, I though a table per resource would be a good way to go, but I'm now worried that this will result in exponentially bigger tables the further down the resource chain you go.

For example, imagine I have three resources - users, clients, sales. Users are subscribers to my api, clients are the users customers, and sales are purchases made by each client to the users account.

A sale resource is accessed as follows

GET /users/{userID}/clients/{clientID}/sales/{salesID}

So if there are 10 users, each with 10 customers, and for each customer there are 10 sales, the table size gets larger the further down the resource chain we go.

Im fairly confident that SQL can cope with large tables, but I'm not sure how read and writes will slow things down. The example above maybe doesn't illustrate it, but my api will have progressively more writes and reads the further down the resource chain we go. I therefore have the scenario where the biggest tables in my database, will be read and written to more times than smaller tables.

It will also be necessary to join tables before running queries. The reason is that I allow each user to have a client with the same name. To avoid getting the wrong client data, the users table and clients tables are joined by {userID}. This is also the case for sales. Will joining large tables and running reads and writes slow things down further?

I am struggling to decide on the best way to design my database tables
  around my resources.

Don't.

Design your API according to RESTful principes, design your database according to normalisation principles. One does not need to impact upon the other.

Your database should not contain a SaleResource table, it should contain a Sale (or purchase/order) table. That table will include a primary key that uniquely identifies a Sale and foreign keys to related User and Customer tables.

You REST api will translate a request for the resource identified by GET /users/{userID}/clients/{clientID}/sales/{salesID} to the appropriate database query, retrieve the row, construct the resource that represents a Sale and return it to the client.

Be mindful that you are currently exposing what appears to be internal database identifiers (UserID/ClientId/SalesID) to the outside world. It may be appropriate in your case but generally <entity>ID feels off in a RESTful API.


Mysql privileges and replication I want to recreate the whole privilege system in my database, and that includes the slave user on the master. It means i have to stop the slave, run a set master command again and than start it. My problem is that im afraid that the slave might be in the middle of reading a binlog and then when i'll start the slave with the new account (with a new binlog and master position ofcourse) there will be a data gap between the slave and the master. Someone knows maybe if i have to worry about this case and how to avoid it? Thank you very much!

You are over-thinking this.  Stopping the slave doesn't cause you to lose your binlog position, and changing the replication username and password doesn't, either, as long as that is all you change.

On the slave:

mysql> STOP SLAVE;

This won't stop in the middle of a binlog read.

Create the new account in the master, then reconfigure the slave to use them:

mysql> CHANGE MASTER TO MASTER_USER='foo', MASTER_PASSWORD='bar';
mysql> START SLAVE;

Unless you change other parameters with CHANGE MASTER TO, the slave will reconnect with the new credentials and resume where it left off.  You do not need to specify host, port, file, position, or anything else, and you should not specify them, unless they are changing too... in which case, yes, you'll need to restart with new coordinates.

https://dev.mysql.com/doc/refman/5.6/en/change-master-to.html


How to track only the tables used by app I have a very messed up database with lot of tables that are really not used by the app so I'm tracking all the used tables with the general_log.

I'm making the request to the app and with tail -f /path/to/general_log.log following the connection id in the general log and getting all the tables requested, but this is going to be a very frustrating activity.

I want to know if there's a tool or an easy way to do this

There are two major things you can try
METHOD #1 : General Log as a Table
Convert the General Log into a Table. I already have 13 posts on how to create and rotate it.
You have the general log table open as a MyISAM table, you can query it like any other table.
In you case, you can do select against it by the event_time (if you query by the hour or by the day)
METHOD #2 : Compare Open Tables to INFORMATION_SCHEMA
STEP 01 :
If you run SHOW OPEN TABLES;, you will get a list of every table with open file handles.
It will look something like this:
+---------------------+----------------------------------------------------+--------+-------------+
| Database            | Table                                              | In_use | Name_locked |
+---------------------+----------------------------------------------------+--------+-------------+
.
.
.
+---------------------+----------------------------------------------------+--------+-------------+

Simply dump the output of this command to one text file (call it tables1.txt)
STEP 02 :
SELECT CONCAT(table_schema,'.',table_name) FROM information_schema.tables
WHERE table_schema NOT IN ('information_schema','performance_schema','mysql','sys');

Send the output of this query to a text file (call it tables2.txt)
STEP 03 : Filter out MySQL system tables
Remove 'information_schema','performance_schema','mysql','sys' from tables1.txt
STEP 04 : Compare the text files
Any table in table2.txt not in tables1.txt has not been used for reading or writing.
CAVEAT
You would have to leave MySQL running a long time to determine which table has open file handles. Please do not do the following:

Restart MySQL
FLUSH TABLES;

As long as you do not do these, the file handle will be open. If you do, you'll have to start over accessing the tables over time.
If you just need to find which tables have been written, you do not need to the INFORMATION_SCHEMA. I think INFORMATION_SCHEMA.TABLES.UPDATE_TIME is usable in MySQL 5.7. If you are using MySQL 5.6 or earlier, you can go to the OS.
EXAMPLE: Suppose your datadir is /var/lib/mysql and you want to see what tables have been written in the database reporting. You go to the OS and do this:
cd /var/lib/mysql/reporting
ls -lt ls -lt | grep "\.[iM][bY][DId]"

This will show you every table in the reportingdatabase in descending order by datetime. You can judge from there which tables have recent writes and which do not.


In what order MySQL permissions are applied on database? I want to understand the priority of executing MySQL grants privileges.

For e.g.:

I want to grant two different types of privileges on a single db Demo.

1.GRANT SELECT ON Demo.table1 TO abc@123;

2.GRANT ALL PRIVILEGES ON Demo.* TO abc@123;

As per my assumption, no 2 grant will overwrite the limitations of no 1 grant, therefore, ends up giving access to all tables including table1 to abc user.

And if I want to give SELECT privilege for table1 then I should change the order of executing permissions so that they don't get overwritten by *.

I'm not sure if this is how MySQL manages such permissions.
Anyone with a different opinion?

Thanks in advance.

According to MySQL 5.7 Documentation under Privileges Provided by MySQL

  The privileges granted to a MySQL account determine which operations
  the account can perform. MySQL privileges differ in the contexts in
  which they apply and at different levels of operation:

  Administrative privileges enable users to manage operation of the
  MySQL server. These privileges are global because they are not
  specific to a particular database.
  Database privileges apply to a database and to all objects within it.
  These privileges can be granted for specific databases, or globally so
  that they apply to all databases.
  Privileges for database objects such as tables, indexes, views, and
  stored routines can be granted for specific objects within a database,
  for all objects of a given type within a database (for example, all
  tables in a database), or globally for all objects of a given type in
  all databases.

From this, the order is:

Global Privileges
Database Privileges
Table Privileges

The tables that drive the order are:

mysql.user
mysql.db
mysql.tables_priv
mysql.columns_priv

With regard to an identical username @ host, please note what pages 486,487 state about mysql's authentication algorithm from MySQL 5.0 Certification Study Guide

  There are two stages of client access control:

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

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

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

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

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

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

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

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

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

When you look at these two perspectives, mysqld should always go top down when evaluating grants. Keep in mind that

GRANT SELECT ON Demo.table1 TO abc@123; is stored in mysql.tables_priv
GRANT ALL PRIVILEGES ON Demo.* TO abc@123; is stored in mysql.db
For more information, please see my older post Unable to remove permission for mysql.user

AND THE OSCAR GOES TO ...

GRANT ALL PRIVILEGES ON Demo.* TO abc@123;


mysql db backup excluding specific tables Are there any mysql scripts for taking a database backup by excluding specified tables?

Also, what is the best way to take backup of a running mysql system?

You can backup databases and tables using the mysqldump command.  There are parameters for excluding certain tables.

mysqldump -u username -ppasswword -h servername --ignore-table=dbname.tabletoskip dbname

If you need to get fast backups of MyISAM or ARCHIVE tables, you can also use mysqlhotcopy.  That has more restrictions though.


How to figure out the latest version of a table in the 'recyclebin' section of PL-SQL developer There are three tables with the same name Pantea in my recyclebin  section of pl-sql developer and I don't know how to find the one that had been dropped the last (I mean the last version of the table).

I can not restore all of them cause there can not be tables with the same name in tables section and I can not see the properties of the tables in recyclebin either . What is the best way to find the latest version of a table in recyclebin?
Important thing is that think about a situation in which the table has nearly more than 300 columns and it's not a good solution to restore the tables one by one to check whether it's the latest one . I want to know if there is a solution that for one hundred percent returns the latest version of the table from the recyclebin.
Thanks in advance

The DROPTIME and DROPSCN columns in the DBA_RECYCLEBIN view clearly show when the object was dropped. Stop relying on a simplified GUI.


Are CREATE FUNCTION and DROP FUNCTION locking when used inside different transactions? I'm having a locking issue on PostgreSQL 9.4.5. I run two or more transactions simultaneously (generally there are 2 running at the same time, but 2+ may happen sometimes) reading from the same tables but writing on different output tables. Inside my transactions I have several DROP FUNCTION IF EXISTS and CREATE OR REPLACE FUNCTION statements that declare some functions.

My guess is that DROP FUNCTION IF EXISTS and CREATE OR REPLACE FUNCTION are locking. Am I right? I can solve the problem by moving the statement outside the transaction.

I've observed the following: two transactions A and B contains some DROP FUNCTION and CREATE OR REPLACE FUNCTION; if I execute transaction B while transaction A is still running, transaction B waits on the first DROP FUNCTION statements it encounters.

Community wiki answer:

Yes of course the other transaction will wait. (Nearly) Every DDL statement takes an exclusive lock on the object being modified (dropped, created). See Explicit Locking in the documentation.


missing FROM-clause entry for table in trigger function in postgresql I have created an audit trigger for table "product" that should insert data into "log_product".

CREATE FUNCTION public.insert_log_product()
RETURNS trigger
LANGUAGE 'plpgsql'
COST 100
VOLATILE NOT LEAKPROOF
ROWS 0
AS $BODY$
BEGIN
IF (TG_OP = 'UPDATE')
THEN
    INSERT INTO log_table(
      operation,
      product_type_new,
      product_type_old,
      product_name_new,
      product_name_old,
      vendor_id,
      available_quantity_unit_new,
      available_quantity_unit_old,
      delivery_time_in_days_new,
      delivery_time_in_days_old,
      change_date
    )
    VALUES(
      'UPDATE',
      new.product_type,
      old.product_type,
      new.product_name,
      old.product.name,
      new.vendor_id,
      old.vendor_id
      new.available_quantity_unity,
      old.available_quantity_unity,
      new.delivery_time_in_days,
      old.delivery_time_in_days,
      now()
    );

END IF;
RETURN NULL;
END;

 $BODY$;

But I get an error message:

org.postgresql.util.PSQLException: ERROR: missing FROM-clause entry for table "product"

The function is created using PGadmin 4.2.

CREATE TRIGGER insert_log_product_trigger
AFTER INSERT OR DELETE OR UPDATE
ON public.table
FOR EACH ROW
EXECUTE PROCEDURE public.insert_log_product();

The requirement for our audit table is to have each column separately, old and new.

You are trying to insert 42 values into 41 columns.  Check vendor_legal_id.


Should I defragment InnoDB table with fixed-length fields? I am running MariaDB 10.2.13 on Linux (Debian).

I am setting up an InnoDB table that will record a lot of data but I will keep only the last one-hour rows. Thus, the number of rows will remain constant.

I should expect on this table:

many INSERTs
many UPDATEs
some DELETE (rows > 1 hour) from time to time

Example:

Table is defined with fixed-length fields only and some indexes.

CREATE TABLE `tbl_log` (
  `ip` int(4) unsigned NOT NULL,
  `date` datetime NOT NULL,
  `external_id` smallint(6) unsigned NOT NULL,
  `counter` smallint(6) unsigned NOT NULL DEFAULT 0,
  PRIMARY KEY (`ip`,`external_id`),
  KEY `external_id` (`external_id`),
  KEY `counter` (`counter`),
  KEY `date_idx` (`date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Inserts (and updates, through on duplicate key) may look like this (ip and external_id will vary):

INSERT INTO tbl_log
  SET ip = INET_ATON('192.168.1.1'),
      date = now(),
      external_id = 123,
      counter = 0
  ON DUPLICATE KEY UPDATE counter=counter+1;

Finally, deleting old rows will be done with a query:

DELETE FROM tbl_log WHERE date < DATE_SUB(NOW(), INTERVAL 1 HOUR);

Question:

Will such a table fragment over time?

If so, I think I should defragment it. If necessary, I planned to run OPTIMIZE TABLE tbl_log; (with option innodb-defragment=1) right after delete...

I would say yes for four(4) major reasons

Page Boundaries / Index Pages
Index Layout
Index Statistics
Bulk DML

PAGE BOUNDARIES / INDEX PAGES
According to the MySQL Documentation on Limits on InnoDB tables

The maximum row length, except for variable-length columns (VARBINARY, VARCHAR, BLOB and TEXT), is slightly less than half of a page for 4KB, 8KB, 16KB, and 32KB page sizes. For example, the maximum row length for the default innodb_page_size of 16KB is about 8000 bytes. For an InnoDB page size of 64KB, the maximum row length is about 16000 bytes. LONGBLOB and LONGTEXT columns must be less than 4GB, and the total row length, including BLOB and TEXT columns, must be less than 4GB.

Your table uses INT UNSIGNED (4 bytes), DATETIME (8 bytes), 2 SMALLINTs (4 bytes) per row. That's a total of 16 bytes per row. This means you can fit 1000 rows into a single InnoDB page, but there is a very small piece of fragmentation. Using the default innodb_page_size (16K or 16384), there will be 384 bytes of fragmentation with a page. It is most likely used for mapping the data within the one page.
Doing mass INSERTs, DELETEs, and UPDATEs will generate many pages with empty space for whole rows that are marked as unused. This is in addition to the 384 bytes region of the data page.
INDEX LAYOUT
You will generate many index pages

You have a 6-byte PRIMARY KEY
Each secondary index carries a copy of the PRIMARY KEY
KEY external_id (external_id), will be 8 bytes (SMALLINT + PRIMARY KEY)
KEY counter (counter), will be 8 bytes (SMALLINT + PRIMARY KEY)
KEY date_idx (date) will be 14 bytes (DATE + PRIMARY KEY)
36 bytes for indexing a single row
Indexes are represented as BTREEs, so you are looking at O(n log n) space utilization. If you account for non-leaf page splits, many index pages will be 50% empty (without question).

INDEX STATISTICS
Running INSERTs, DELETEs, and UPDATEs will skew index stats since index cardinalities will frequently change. You need to run ANALYZE TABLE tbl_log;. Running OPTIMIZE TABLE tbl_log; will defrag the tables and run ANALYZE TABLE tbl_log; as the last step (See my post from Feb 27, 2013 : What is the difference between optimize table and analyze table in mysql)
BULK DML
The DELETE query you will be doing by the hour is a bulk operation. Many rows will need a ton of housecleaning to keep up with marking rows deleted.


Same user cannot 'see' view on remote oracle database athough dblink is valid I am running an impdp job that I have extracted the DDL from to determine why the job hangs.  It consistently stops on the first object created using a dblink. I am running the impdp job as system as recommended by Oracle.  The job must be run on two separate, different instances on the same database using a unique dump file for each instance. The impdp job only imports one schema from the dump file, the same schema name, in both instances. The two impdp jobs are interdependent on dblinks. At the point where the job is indefinitely waiting on either impdp instance job, it is unable to retrieve data from the specified dblink, as confirmed per DDL extract run on SQL Developer. The links have been sucessfully created in each database instance and SELECT * FROM DUAL@DB_LINK returns a value but SELECT * FROM 'view_name'@DB_LINK returns 'ORA-00942: table or view does not exist', although I have confirmed the view exists in the remote schema. I have researched creating and fetching data via dblinks and understand the dblink querying user must be granted access to the remote object(s). In this case the user is the same with identical passwords on both database instances. How can I grant permission on all schema objects to the user of same name in separate instance so that the DDL can complete?
UPDATE: I have granted access to all views in instance1 to the connect as user for the dblink utilized by the user instance2 and vice versa. I still cannot select views from either instance using the dblinks from respective remote users.This is the script:
BEGIN
   FOR objects IN
   (
         SELECT 'GRANT ALL ON "'||owner||'"."'||object_name||'" TO <DB_LINK_NAME>' grantSQL
           FROM all_objects
          WHERE owner = 'OWNER_NAME'
            AND object_type = 'VIEW'
            AND object_type IN
                (
                  select view_name
                  from sys.dba_views
                  where owner = 'OWNER_NAME'
                )
       ORDER BY object_type, object_name
   ) LOOP
      BEGIN
         EXECUTE IMMEDIATE objects.grantSQL;
      EXCEPTION WHEN OTHERS THEN
         --Ignore ORA-04063: view "X.Y" has errors.
         --(You could potentially workaround this by creating an empty view,
         -- granting access to it, and then recreat the original view.)
         IF SQLCODE IN (-4063) THEN
            NULL;
         --Raise exception along with the statement that failed.
         ELSE
            raise_application_error(-20000, 'Problem with this statement: ' ||
               objects.grantSQL || CHR(10) || SQLERRM);
         END IF;
      END;
   END LOOP;
END;
/

Do not quote schema or object names in your SQL. If forces case-sensitivity in the names where none exists by default in Oracle. i.e. TABLE_NAME is the same as table_name, but "TABLE_NAME" is not the same as table_name. Even if you were going to quote the object names to enforce case-sensitivity, you should use double-quotes and not single quotes.
Also, be careful what you're granting: "GRANT ALL" is not appropriate if all the user needs to do is select data.
     SELECT 'GRANT ALL ON "'||owner||'"."'||object_name||'" TO <DB_LINK_NAME>' grantSQL
       FROM all_objects
      WHERE owner = 'OWNER_NAME'
        AND object_type = 'VIEW'
        AND object_type IN
            (
              select view_name
              from sys.dba_views
              where owner = 'OWNER_NAME'
            )
   ORDER BY object_type, object_name

produces a SQL statement like this:
GRANT ALL ON 'owner'.'object_name' TO DB_LINK_USER_NAME

When what you most likely want is this:
GRANT SELECT ON owner.object_name TO DB_LINK_USER_NAME

Your query also seems a little convoluted. DBA_VIEWS gives you everything you need, so why join with ALL_OBJECTS which may not contain the matching view names? The order of the grants are made is also not important, so why include an ORDER BY clause that will force extra I/O to sort the results in the TEMP tablespace?
Try this:
     SELECT 'GRANT SELECT ON "||owner||"."||object_name||" TO <DB_LINK_USER_NAME>' grantSQL
       FROM dba_views
      WHERE owner = 'OWNER_NAME'


Merge multiple table output but indicate which tables the records came from I have an application with a function that uses a stored procedure (SQL Server 2008 R2) to pull in and arrange data from one table.  Now, I’ve been asked to make it pull from two tables, but it’s not just a straight join.  The tables have overlapping records (a common ID field between them), so my end result needs to have a field that indicates whether that row had a record from Table A only, from Table B only, or if it had records in both Tables A and B.

I am not a SQL guru (my queries are usually just selects with simple joins), so I’m having a hard time figuring this out.  My current thought is to create three temp tables (one for A-only records, one for B-only records, and one for A+B records) then union them together for the output, but I’m sure there’s a better, more elegant/proper way to do this.  Any thoughts on how this could be accomplished?

You can use a full outer join between your tables and check against null values to figure out where the data comes from.

select isnull(A.ID, B.ID) as ID,
       isnull(A.Name, B.Name) as Name,
       case
         when A.ID is null then 'B'
         when B.ID is null then 'A'
         else 'A+B'
       end as Tables
from TableA as A
  full outer join TableB as B
    on A.ID = B.ID


More table or more database for better performance? Lets say 1 website need 10 database table to work and i want a database pool that 40.000+ website will be inside.

Which option is better for server performance?

1) 400 databases each have 1000 table?

2) 4000 databases each have 100 table?

Update: I see i need to put some more details, sorry for trying to ask simple as much as i can.
Site will based on wordpress multisite with http://wordpress.org/extend/plugins/hyperdb/

1 website with 10 database table generally wont pass 6-7 mb as total database info. There will be 9-10 heavy website that will have serious database info, i will put them in a special database. ( lets say 4096 database, 96 reserve for vip,heavy sites and split all other sites in 4000 database )

Server is:
http://www.wiredtree.com/managedhybrid/
After reaching some point, i am planning move to pure dedicated server which will based on raid10 15k RPM SAS mysql discs and different media discs

So 40.000 sites but all of them sharing same php files, just different databases and different upload folders. This is how wordpress.com working. Same rules but my sites wont be a blog.

Please ask what other info do you want, i will try to explain more. I am very sorry if i break any rules or something.

( Sorry for bad english too :P )

Performance shouldn't be a concern: security, consistency, data integrity should be

Security: If each web site has a database this implies that the data for that web site is
private and separate. So 40000 databases with 10 tables each.
Consistency: managing 40k websites requires utter consistency. Why pick 400x1000 or 4000x100? How will you separate tables? How do you know which site uses which set of tables in which DB?
Data integrity: If you restore one database, do you really want to reset the data for another 399 or 3999 websites too?

So: 40k databases, one per web site.

Finally, do you have 40k web sites or is this premature optimisation?


Is it ever useful to SELECT DISTINCT on the primary key column(s)? From my understanding, primary keys are unique by definition. If that's true, would one ever want to SELECT DISTINCT on the primary key? Is this ever practical/useful?

If you join with another m:n table that duplicates the PK value in your result set, then it would be useful.


Mysql triggers for auditing We have a requirement to fulfill  - we need an auditing table for all of our entities.
We wonder if creating the following triggers will be a good practice:

CREATE DEFINER=`root`@`localhost` TRIGGER `admin`.`company_AFTER_UPDATE` AFTER UPDATE ON `company` FOR EACH ROW
 BEGIN
  INSERT INTO AuditCompany
   select *, now() from Company where id = NEW.id;
 END

1) Is this a good practice?

2) Is there a better way of doing this?

3) We wanted to avoid the 'insert into XXX values (NEW.id, NEW.col1, NEW.col2) thats why this trigger is doing a subselect on its own - this will remove the need to update the trigger on every new column in the entity. But is it ok?

That will work fine - however you need to add a delimiter to your trigger:

DELIMITER $$
CREATE DEFINER=`root`@`localhost` TRIGGER `admin`.`company_AFTER_UPDATE` AFTER UPDATE ON `company` FOR EACH ROW
 BEGIN
  INSERT INTO AuditCompany
   select *, now() from Company where id = NEW.id;
 END $$
DELIMITER ;

You might gain some performance benefit if you kept the fields in your audit table to a minimum. You would also save space, although these days, that's less of an issue.

And don't forget to have an ON_INSERT and an ON_DELETE trigger also.

[EDIT].

Two further issues have occurred to me:

1) You say that "We wanted to avoid the 'insert into XXX values (NEW.id,....", but you're going to have to keep track of maintenance of the AuditCompany table anyway, which is perhaps a reason to be selective about the fields that you audit.

2) There are Open Source audit plugins in which you might be interested - Percona and McAfee. MariaDB do one - but I don't think that the source is available. Perhaps you can automate some of this process which appears to be what you would like to do?


Tempdb files don't exist I have a strange behaviour on one of my SQL Server's tempdb.
It has six tempdb files, according to SSMS and queries, all pointing to S directory. Each of them have ~5gb of initial size.
The weird thing is S is quorum actually with only 2gb of free space. Tempdb files simply don't exist in S nor in its sub directories.
The question is where are the tempdb files? :)  How can I physically see them?
Thank you.

Remember that S: is relative to the server, not necessarily where you're running SSMS from. I've seen plenty of people query the location of files and find they're not actually accessible from where SSMS is running. If S: is part of a user profile, then it could be associated with the service account even.


Columnstore Index in read_only filegroup prevents CheckDB It appears setting a filegroup to read_only prevents dbcc checkdb for the entire database if the filegroup contains a columnstore index. When attempting to run checkdb or checkfilegroup (for any filegroup in the database, including read-write secondaries and [PRIMARY]), the below error is returned...

Msg 8921, Level 16, State 1, Line 24
Check terminated. A failure was detected while collecting facts.
Possibly tempdb out of space or a system table is inconsistent. Check previous errors.

Is there a supported method for having columnstore data in a read-only filegroup? Or am I precluded from integrity checks in this scenario?

Repro

create database check_fg_ro
go
use check_fg_ro
go
exec sp_changedbowner 'sa';
go
alter database check_fg_ro add filegroup check_fg_ro_2;
alter database check_fg_ro
    add file (
         name='check_fg_ro_2'
        ,filename='C:\check_fg_ro_2.ndf'
    ) to filegroup check_fg_ro_2;
go
create table foo (
    i int not null primary key
) on check_fg_ro_2;
go
create columnstore index ccix_foo on foo(i);
go
use master
go
alter database check_fg_ro modify filegroup check_fg_ro_2 read_only;
go
dbcc checkdb( check_fg_ro ) with no_infomsgs, all_errormsgs, extended_logical_checks;
/*
Msg 8921, Level 16, State 1, Line 24
Check terminated. A failure was detected while collecting facts.
Possibly tempdb out of space or a system table is inconsistent. Check previous errors.
*/
go

Disclaimer: cross-posted to technet forums

The problem occurs when DBCC tries to verify a deleted bitmap for a read-only columnstore table.

Deleted bitmaps are stored on the same filegroup as the columnstore table. They track rows logically deleted from compressed row groups.

As far as I can tell, everything is organized correctly in the internal system tables (on SQL Server 2017 CU3), and most of the DBCC code correctly accounts for the hidden rowsets that hold the columnstore deleted bitmaps.

For some reason, a check for offline or read-only file groups results in an unhandled exception:

Msg 8921, Level 16, State 1, Line 69
Check terminated. A failure was detected while collecting facts.
Possibly tempdb out of space or a system table is inconsistent.
Check previous errors.

The same offline/read-only check is performed several times earlier in DBCC processing (when facts are being collected) without issue.

The problem occurs when DBCC CHECKDB or DBCC FILEGROUP is run (on any filegroup), or DBCC CHECKTABLE is asked to check a specific read-only columnstore table. None of these should produce a fatal error condition that prevents the rest of the DBCC checks running, so this must be a bug.

  Or am I precluded from integrity checks in this scenario?

As a workaround, run DBCC CHECKFILEGROUP on the columnstore filegroup immediately before it is made read-only (or run DBCC CHECKDB at that time) then:

DBCC CHECKALLOC on the database
Run DBCC CHECKCATALOG
Run DBCC CHECKTABLE for each table (excluding columnstore tables on a read-only filegroup)
You may also want to run DBCC CHECKCONSTRAINTS.

See Consistency Checking Options for a VLDB by Paul Randal and the Q & A Dividing DBCC CHECKDB over multiple days.


How to optimize a slow query? I work as an administrator and software developer for a company that provides services to the car selling business. In essence we develop the software that they use when selling cars and such. This means that we have an ever growing database. With regular intervals our customers do searches for different things, for example a certain material used in a car. These queries have started to take really long time to execute and we dont really know how to fix them. So my question is there anything we can do to optimize this query? I'll provide the EXPLAIN and then the query itself.

You have three potential areas you could address: LIKE operators, subqueries, and OR operators.

Regarding LIKE operators:

See https://stackoverflow.com/questions/6142235/sql-like-vs-performance and http://myitforum.com/cs2/blogs/jnelson/archive/2007/11/16/108354.aspx

  If your filter criteria uses equals = and the field is indexed, then
  most likely it will use an INDEX/CLUSTERED INDEX SEEK
  If your filter criteria uses LIKE, with no wildcards (like if you had
  a parameter in a web report that COULD have a % but you instead use
  the full string), it is about as likely as #1 to use the index. The
  increased cost is almost nothing.
  If your filter criteria uses LIKE, but with a wildcard at the
  beginning (as in Name0 LIKE '%UTER') it's much less likely to use the
  index, but it still may at least perform an INDEX SCAN on a full or
  partial range of the index.
  HOWEVER, if your filter criteria uses LIKE, but starts with a STRING
  FIRST and has wildcards somewhere AFTER that (as in Name0 LIKE
  'COMP%ER'), then SQL may just use an INDEX SEEK to quickly find rows
  that have the same first starting characters, and then look through
  those rows for an exact match.

Regarding Subqueries

I would recommend inserting all of your subquery data into a temporary table first. Then, join the temporary table to your main select query.

See https://stackoverflow.com/questions/921931/why-is-inserting-into-and-joining-temp-tables-faster

Regarding OR operators:

Regarding performance, a question you should ask yourself is if you should use the OR operator or UNION operator as they both can have the same result.

See http://www.sql-server-performance.com/2011/union-or-sql-server-queries/

  Selecting all columns: OR is faster than UNION
  Non-Clustered and Clustered Index Columns only: no difference
  Selecting all columns for different fields: no difference
  Selecting Clustered index columns for different fields: UNION is faster than OR


Does SQL Server guarantee accurate data storage? If so, how? This question is specific to SQL Server, but other DB replies welcome.

Read below for some history. My short question is, how does SQL Server guarantee that when I request "ABCDE" to be written to the database, that "ABCDE" is written, not "ABECD"? It has been suggested that data be re-read and compared to guarantee the same results, with the obvious performance penalty.

The company I work for is launch a high-volume product for us (~35,000 pieces each day) for which we will collect data, stored in SQL Server.

Previous, lower-volume products had data stored at the line, locally in SQL Server Express databases, then periodically copied to the server (SQL Server Standard) using cross-server transactions. On occasion, these desktop databases became corrupted, often requiring restoration.

Our new product's process will write to the server directly, bypassing local / desktop storage.

Our management is carrying forward this "desktop corruption" experience into the new product.

Other than appeals to authority (Microsoft would not be in the database business if it didn't store data accurately) or anecdotal experience as evidence (in my five years managing SQL Server databases, have never seen a server [Standard / Enterprise] database become corrupted), is there any Microsoft documentation referencing SQL Server "guarantees"?

This is such a basic truth that is rarely questioned, especially with the standard players (DB2, Oracle, SQL Server).

Thanks!

All the major SQL databases provide this; it is called being ACID compliant. This is probably the key phrase you need to be looking up in the documentation (and/or Google).

As for how they do it, MS SQL Server is closed-source, as is Oracle, so no-one outside the company can really answer that question, but as you say, if they didn't manage it for large-scale systems, their products would be out of the market very rapidly.


NT SERVICE\MSSQL$SQLEXPRESS user cannot be found when added to network drive For SQL Server 2016 Express, I would like to backup/restore directly to/from network storage I have mapped to drive S:. SQL Server Express runs under the (virtual?) account NT SERVICE\MSSQL$SQLEXPRESS as it should, which of course does not have the permissions to access S:.

As explained in
Broken NTFS Permissions for MSSQL$SQLEXPRESS
I have tried to add NT SERVICE\MSSQL$SQLEXPRESS to the users of S:. This fails with a "Name Not Found" message-box:

  An object named "NT SERVICE\MSSQL$SQLEXPRESS" cannot be found.

My machine is not on a domain and has been restarted since S: and SQL Server Express were installed.

Why does it not find that user? Is there another way to make individual folders accessible to the default SQL Server user?

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.


How to restrict sequence like ID type to C type like int64_t, uint64_t, int32_t? In C99 I can be pretty clear about the size/domain of an integer type.

When interfacing a SQL database like Oracle or Postgresql from a C program I want to create a table with integer types that match the client ones.

For example to match int64_t, uint64_t or uint32_t etc. for a sequence like artificial primary key column.

Just using NUMBER would allow for data that yields an over-/underflow or are not representable in the client language, right?

How to correctly define such a column?

Motivation

Why do you want to such a thing? Because you want to write reliable software. Say you design a database client application and you assume that you only ever need less than 2^32 IDs. Thus, to save space (e.g. with client side arrays etc.) you use uint32_t (or just int). But to be safe the database should enforce that restriction such that your system fails in a well-defined, gracious and noticeable way (e.g. rejecting violating inserts) in case 20 years later some user want to insert tons of IDs or something like that.

Database system

Ideally, the solution should be portable between databases (after all, SQL is an international standard) - but an answer which is Oracle/Postgres specific would be instructive as well - often if you know how to do it on one database system, you can lookup the abstract concept in the documentation of the other one.

Types vs. triggers

It does not have to be a type-only solution - triggers are other mechanism that enforce constraints are fine as well. I just imagined using types could be a convenient way.

I don't think you can restrict this by the choice of the datatype.

However you can create a check constraint that ensures that no value is inserted that could not be represented in your programming language:

alter table foo
   add constraint check_type_range check (int_column < 32767);

You will have to replace the actual value with the one that matches the chosen datatype (I don't do C, so I don't know what those limits are).


WHILE Loop for data migration/merge with multiple fields for matching Ok so I have two identical databases in two parts of the world. I need to merge everything into the one I will call North (the other being South). North data trumps South data. I started thinking about a WHILE loop but am not sure that is the best/easiest way to go about this. My plan was to restore a copy of the South database onto the Instance where North lives and then do my work.

The table in question has ID, FirstName, LastName, Email and Login fields. And my rules are:

Any Login fields that exist in both are logged to a logging table with a status field saying it was not migrated and another for why it did not (because has identical Login).
If the Login field is NULL in South or does not match with that field in North we must check for a match on the Email field (logging similarly as above if we find a match).
If neither Login nor Email match the final attempt is FirstName + LastName (has to be both) and logged as above.
Any Login fields that don't exist in North (destination) but do in South (source) are logged in that same logging table and then inserted into the North table.

So my initial setup was something like this (which pretty sure I screwed up the Loop):

DECLARE @TempID INT
DECLARE @TempCount INT

SET @TempID = 0
SET @TempCount = 1

WHILE @TempCount = 1
BEGIN

DECLARE @SourceID nvarchar(255)
DECLARE @SourceLogin nvarchar(255)
DECLARE @SourceFirstName nvarchar(255)
DECLARE @SourceLastName nvarchar(255)
DECLARE @SourceEmail nvarchar(255)

DECLARE @TargetID nvarchar(255)
DECLARE @TargetLogin nvarchar(255)
DECLARE @TargetFirstName nvarchar(255)
DECLARE @TargetLastName nvarchar(255)
DECLARE @TargetEmail nvarchar(255)

SET @SourceID = (SELECT TOP 1 id FROM #TempExistence);
SET @SourceLogin = (SELECT TOP 1 Login FROM #TempExistence);
SET @SourceFirstName = (SELECT TOP 1 firstname FROM #TempExistence);
SET @SourceLastName = (SELECT TOP 1 lastname FROM #TempExistence);
SET @SourceEmail = (SELECT TOP 1 email FROM #TempExistence);

SET @TargetID = @SourceID (SELECT TOP 1 id FROM [North].dbo.PersonID]);
SET @TargetLogin = @SourceLogin (SELECT TOP 1 Login FROM #TempExistence);
SET @TargetFirstName = @SourceFirstName (SELECT TOP 1 firstname FROM #TempExistence);
SET @TargetLastName = @SourceLastName (SELECT TOP 1 lastname FROM #TempExistence);
SET @TargetEmail = @SourceEmail (SELECT TOP 1 Email FROM #TempExistence);

    INSERT INTO [MigratePersonLog]

    (
    [SourceWfoPersonID]
    ,[SourceFirstName]
    ,[SourceLastName]
    ,[SourceEmail]
    ,[SourceLogin]

    ,[TargetWfoPersonID]
    ,[TargetFirstName]
    ,[TargetLastName]
    ,[TargetEmail]
    ,[TargetLogin]
    ,[MatchStatus] --Matched or not
    ,[MatchArtifact] --What was matched or not matched
    )

VALUES
    (
    @SourceID
    ,@SourceFirstName
    ,@SourceLastName
    ,@SourceEmail
    ,@SourceLogin

    ,@TargetID
    ,@TargetFirstName
    ,@TargetLastName
    ,@TargetEmail
    ,@TargetLogin
    ,1
    ,'NONE'
    )

    --WHERE ID > @TempID
    --ORDER BY ID

    IF @@ROWCOUNT > 0
        SELECT @TempID = MAX(ID)
        FROM #TempExistence
    ELSE
        SET @TempCount = 0
END

Not sure this is the right path to take or if I am doing something wrong/less-optimal for the Loop stuff. Thanks in advance for any help!

So I am assuming the tables look something like this (for my easy I am using table variables instead of physical/temp tables):

DECLARE @North TABLE
(
    ID INT NOT NULL,
    FirstName VarChar(20) NULL,
    LastName VarChar(50) NULL,
    Email VarChar(100) NULL,
    [Login] VarChar(20) NOT NULL
)

DECLARE @South TABLE
(
    ID INT NOT NULL,
    FirstName VarChar(20) NULL,
    LastName VarChar(50) NULL,
    Email VarChar(100) NULL,
    [Login] VarChar(20) NOT NULL
)

DECLARE @MigrationPersonLog TABLE
(
    [SourceWfoPersonID] INT NULL
    ,[SourceFirstName] VarChar(20) NULL
    ,[SourceLastName] VarChar(50) NULL
    ,[SourceEmail] VarChar(100) NULL
    ,[SourceLogin] VarChar(20) NULL

    ,[TargetWfoPersonID] INT NULL
    ,[TargetFirstName] VarChar(20) NULL
    ,[TargetLastName] VarChar(50) NULL
    ,[TargetEmail] VarChar(100) NULL
    ,[TargetLogin] VarChar(20) NULL
    ,[Matched] BIT --Matched (= 1) or not (=0)
    ,[MatchArtifact] VarCHar(50) --What was matched or not matched
)

To scsimon's point, SQL really prefers to work in sets instead of iterations.  So anything you can do to avoid loops is preferred in almost all situations.   I think this should solve basically everything you need.  I may not have understood everything you wanted to do with the logging table when it came to inserting new rows into the North table.  But I definitely think this should be a framework you can work with.  I tried to make the code self-documenting but I can always explain something if that is needed.

--Check for all instances where the Login's Match
INSERT INTO @MigrationPersonLog
(
    [SourceWfoPersonID]
    ,[SourceFirstName]
    ,[SourceLastName]
    ,[SourceEmail]
    ,[SourceLogin]
    ,[TargetWfoPersonID]
    ,[TargetFirstName]
    ,[TargetLastName]
    ,[TargetEmail]
    ,[TargetLogin]
    ,[Matched]
    ,[MatchArtifact]
)
SELECT
S.ID,   --[SourceWfoPersonID]
S.FirstName,    --[SourceFirstName]
S.LastName, --[SourceLastName]
S.Email,    --[SourceEmail]
S.[Login],  --[SourceLogin]
N.ID,   --[TargetWfoPersonID]
N.FirstName,    --[TargetFirstName]
N.LastName, --[TargetLastName]
N.Email,    --[TargetEmail]
N.[Login],  --[TargetLogin]
1,  --[Matched]
'Login' --[MatchArtifact]
FROM @South S
    INNER JOIN @North N
        ON S.[Login] = N.[Login]

--Check for All Instances where the Email's Match
INSERT INTO @MigrationPersonLog
(
    [SourceWfoPersonID]
    ,[SourceFirstName]
    ,[SourceLastName]
    ,[SourceEmail]
    ,[SourceLogin]
    ,[TargetWfoPersonID]
    ,[TargetFirstName]
    ,[TargetLastName]
    ,[TargetEmail]
    ,[TargetLogin]
    ,[Matched]
    ,[MatchArtifact]
)
SELECT
S.ID,   --[SourceWfoPersonID]
S.FirstName,    --[SourceFirstName]
S.LastName, --[SourceLastName]
S.Email,    --[SourceEmail]
S.[Login],  --[SourceLogin]
N.ID,   --[TargetWfoPersonID]
N.FirstName,    --[TargetFirstName]
N.LastName, --[TargetLastName]
N.Email,    --[TargetEmail]
N.[Login],  --[TargetLogin]
1,  --[Matched]
'Email' --[MatchArtifact]
FROM @South S
    INNER JOIN @North N
        ON S.Email = N.Email
    LEFT OUTER JOIN @MigrationPersonLog L --This LEFT OUTER JOIN makes sure we don't double log people who match on LoginID and Email
        ON L.[SourceWfoPersonID] = S.ID
WHERE L.[SourceWfoPersonID] IS NULL

--Check for All Instances where the First Name + Last Name Matches
INSERT INTO @MigrationPersonLog
(
    [SourceWfoPersonID]
    ,[SourceFirstName]
    ,[SourceLastName]
    ,[SourceEmail]
    ,[SourceLogin]
    ,[TargetWfoPersonID]
    ,[TargetFirstName]
    ,[TargetLastName]
    ,[TargetEmail]
    ,[TargetLogin]
    ,[Matched]
    ,[MatchArtifact]
)
SELECT
S.ID,   --[SourceWfoPersonID]
S.FirstName,    --[SourceFirstName]
S.LastName, --[SourceLastName]
S.Email,    --[SourceEmail]
S.[Login],  --[SourceLogin]
N.ID,   --[TargetWfoPersonID]
N.FirstName,    --[TargetFirstName]
N.LastName, --[TargetLastName]
N.Email,    --[TargetEmail]
N.[Login],  --[TargetLogin]
1,  --[Matched]
'FirstName+LastName'    --[MatchArtifact]
FROM @South S
    INNER JOIN @North N
        ON (S.FirstName + S.LastName) = (N.FirstName + N.LastName)
    LEFT OUTER JOIN @MigrationPersonLog L --This LEFT OUTER JOIN makes sure we don't double log people who match on First Name + Last Name AND (LoginID and/or Email)
        ON L.[SourceWfoPersonID] = S.ID
WHERE L.[SourceWfoPersonID] IS NULL

--Log all instances which failed previous 3 checks that they failed the checks
INSERT INTO @MigrationPersonLog
(
    [SourceWfoPersonID]
    ,[SourceFirstName]
    ,[SourceLastName]
    ,[SourceEmail]
    ,[SourceLogin]
    ,[TargetWfoPersonID]
    ,[TargetFirstName]
    ,[TargetLastName]
    ,[TargetEmail]
    ,[TargetLogin]
    ,[Matched]
    ,[MatchArtifact]
)
SELECT
S.ID,   --[SourceWfoPersonID]
S.FirstName,    --[SourceFirstName]
S.LastName, --[SourceLastName]
S.Email,    --[SourceEmail]
S.[Login],  --[SourceLogin]
NULL,   --[TargetWfoPersonID]
NULL,   --[TargetFirstName]
NULL,   --[TargetLastName]
NULL,   --[TargetEmail]
NULL,   --[TargetLogin]
0,  --[Matched]
NULL    --[MatchArtifact]
FROM @South S
    LEFT OUTER JOIN @MigrationPersonLog L --This is used to quickly catch anyone who didn't get logged previously.
        ON L.[SourceWfoPersonID] = S.ID
WHERE L.[SourceWfoPersonID] IS NULL

INSERT INTO @North
(
    ID, --Unless this is and Identity and you can ignore it
    FirstName,
    LastName,
    Email,
    [Login]
)
S.ID,   --ID,
S.FirstName,    --FirstName,
S.LastName, --LastName,
S.Email,    --Email,
S.[Login]   --[Login]
FROM @South S
    LEFT OUTER JOIN @MigrationPersonLog L --This is used to quickly catch anyone who didn't get logged previously.
        ON L.[SourceWfoPersonID] = S.ID
WHERE L.[SourceWfoPersonID] IS NULL

Hope this give you what you are looking for.


MySQL - Call a View inside a Stored Procedure Let's say, I have a table as follows
tbl_1
id             name                 status               usertype                         flag
================================================================================================
1              Name 1                  1                    2                               0
2              Name 2                  1                    3                               0
3              Name 3                  1                    1                               0
4              Name 4                  1                    4                               0
5              Name 5                  1                    5                               0

HTML PAGE
<select id="type" name="type">

  <option value="1">1</option>
<option value="2">2</option>
<option value="3">3</option>
<option value="4">4</option>
<option value="5">5</option>

</select>

My requirement is that I need to get all the data from table where the usertype is selected from the html page by the user (could be 1, 2, 3,4,5, etc.)
I want to create a view, and call that inside stored procedure
I cannot pass an argument inside of a View. So, I was told to create a stored procedure, and call the view inside it. The problem is, I don't know how to do it.

... I was told to create a stored procedure, and call the view inside it.

You don't need a View.
You don't need a Stored Procedure.
Just select the data that you want to display, based on the criteria that you have.
select id, name, status, usertype, flag
from tbl_1
where usertype = ?
order by id ;

The usertype argument will come from [the value of the selected "option" element within] the "select" element.
Depending on how your page(s) are structured, you might need an AJAX request to get the data or submitting a form containing the "select" element and processing that.


List all users and permission I found this script and find it is very helpful to me. I am new here and not good in coding. Can someone explain to me the below script the **'{All Users}'means? represent of? Appreciate your answer and reply. Thank you very much!
--List all access provisioned to the public role, which everyone gets by default
SELECT
    [UserName] = **'{All Users}',**
    [UserType] = **'{All Users}'**,
    [DatabaseUserName] = '{All Users}',
    [Role] = roleprinc.[name],
    [PermissionType] = perm.[permission_name],
    [PermissionState] = perm.[state_desc],
    [ObjectType] = obj.type_desc,--perm.[class_desc],
    [ObjectName] = OBJECT_NAME(perm.major_id),
    [ColumnName] = col.[name]
FROM
    --Roles
    sys.database_principals roleprinc
LEFT JOIN

This refers to the role public.
every user gets this role, and so the rights that the role public has, are valid for all users, so there is no singular username shown in the result only the text `{All Users}´


In the SQL Server RESTORE statement, in what time zone is the STOPAT argument? SQL Server can perform point-in-time restores using the STOPAT argument. In what time zone is that date and time value?

Is it UTC?
Is it local for the server the backup was taken?
Is it local for the server the backup is restored?

Short Answer: From when the backup was taken

I actually wasn't sure of the answer, so I just made a database, put it in full recovery model, took a full backup, did some work (create a couple tables named after the time I created them) and then started restores. Restored the full and then attempted to apply the log backups.

When I did that I had to specify the time zone from when the changes were made - from when the backup was taken. If I tried to use the new time zone's setting, it errored - bad timing.

So the answer to your question in my experience with SQL Server 2012 and 2008R2 - appears to be "The local time from when the backup was taken"

This backs up my expectation before testing. The way the log records are written and the way the backups are taken - that makes sense.

That said - I can't imagine a ton of situations where the time zone is changing with the need to worry about point in time recovery?


PostgreSQL: remaining connection slots are reserved for non-replication superuser connections, too many clients already We have web application using Golang, PostgreSQL, and sqlx (adapter, connection pooler), that each request requires 1 to 8 queries, sometimes 1 transaction with 5-8 select and 5-8 insert queries.

We already set our PostgreSQL's max_connections from 1024, then 4096, but it start swapping, so we reduce them back to 64 (this is the limit before swapping).

Our RAM is 2GB, we configure the PostgreSQL's working_mem to 16MB and temp_buffers to 8MB, shared_buffers to 800MB, effective_cache_size                to 1536MB (this configuration doesn't swap at all).

There are about 18 request per second everyday at 00:00 to 02:35, and there's about 8 errors per second, at that point only 10 transaction per hours that succeed, others are failed, with error message either:

remaining connection slots are reserved for non-replication superuser connections
too many clients already

How to overcome this problem other than caching the read-only page (since the page can be updated about 10 times per minutes) or upgrading the machine?

max_connections = 1024 ?! You need a connection pool.

Use PgBouncer in transaction-pooling mode if your app doesn't support built-in pooling.

You're running PostgreSQL on a tiny toy server. Keep the active connection count low and queue work up in series.

High max_connections introduces significant inefficiency, and having lots of actively working connections adds more.


How can I find the VLF count for a SQL 2005 database without running dbcc loginfo? Is it possible to find the number of VLFs in a database's transaction log without running DBCC LOGINFO? DBCC LOGINFO seems a little heavy and verbose when count is the only information my t-sql script needs from it.

I went and checked the POSH script I had that tied to an Excel spreadsheet:

http://www.simple-talk.com/sql/database-administration/monitoring-sql-server-virtual-log-file-fragmentation/

And you mentioned that DBCC LOGINFO was 'verbose', which makes me want to ask, have you tried using the WITH NO_INFOMSGS option?


Login to SQL Server using Windows Authentication I have an (.Net Console App) EXE which connects to various SQL databases using SQL server Authentication with appropriate credentials.
Now there is change required in the EXE to use windows authentication and use the Network Credentials to log in SQL server. (All SQL servers are on diffrent locations)
I have the network credentials with me, but am confused how to connect to remote SQL server using these and retrive data from that.
Please help.

The only change required is in the connection string: remove the User ID=...; and Password=...;, and add Integrated Security=SSPI; instead.

More information can be found here.


Updating table basing on composite primary keys from derived table I have two tables TableA and TableB defined as followings:

TableA has 4 columns id1 (Primary key), id2 (Primary key), code, enable.

TableB has 3 columns id (Primary key), code, loc.

TableB.code corresponds to TableA.code.

I would like to update TableA.enable for the entry with TableB.loc equals to foo in MySQL. Under general conditions this should do the work:

UPDATE `TableA`
JOIN `TableB` ON `TableA`.`code` = `TableB`.`code`
SET `TableA`.`enable` = 1
WHERE `TableB`.`loc` = 'foo'

But in MySQL Workbench I got the following error: (since TableB.loc is not a primary key)

  Error Code: 1175. You are using safe update mode and you tried to
  update a table without a WHERE that uses a KEY column.  To disable
  safe mode, toggle the option in Preferences -> SQL Editor and
  reconnect.

So I attempted to nest a query to fetch the two primary key values from TableA and update TableA:

UPDATE `TableA` SET `TableA`.`enable` = 1
WHERE EXISTS (
    SELECT * FROM (
        SELECT
            `TableA`.`id1` AS `id1`,
            `TableA`.`id2` AS `id2`
        FROM `TableA`
        LEFT JOIN `TableB`
        ON
            `TableA`.`code` = `TableB`.`code`
        WHERE
            `TableB`.`loc` = 'foo'
    ) t
    WHERE
        t.`id1` = `TableA`.`id1` AND
        t.`id2` = `TableA`.`id2`
);

But it throws out the same error, so I tried to see if I can use the primary key column in my WHERE statement.

I also tried this query:

UPDATE `TableA`
SET `TableA`.`enable` = 1
WHERE (
    (`TableA`.`id1`, `TableA`.`id2`) = (
        SELECT `dummy`.`id1`, `dummy`.`id2` FROM (
            SELECT
                `TableA`.`id1` AS `id1`,
                `TableA`.`id2` AS `id2`
            FROM `TableA`
            LEFT JOIN `TableB`
            ON
                `TableA`.`code` = `TableB`.`code`
            WHERE
                `TableB`.`loc` = 'foo'
        ) AS `dummy`
    )
);

But it also throws the error 1175.

On the other hand, I tried something like this:

UPDATE `TableA`
SET `TableA`.`enable` = 1
WHERE (
    `TableA`.`id1` = (
        SELECT `dummy`.`id1` FROM (
            SELECT
                `TableA`.`id1` AS `id1`,
                `TableA`.`id2` AS `id2`
            FROM `TableA`
            LEFT JOIN `TableB`
            ON
                `TableA`.`code` = `TableB`.`code`
            WHERE
                `TableB`.`loc` = 'foo'
        ) AS `dummy`
    ) AND
    `TableA`.`id2` = (
        SELECT `dummy`.`id2` FROM (
            SELECT
                `TableA`.`id1` AS `id1`,
                `TableA`.`id2` AS `id2`
            FROM `TableA`
            LEFT JOIN `TableB`
            ON
                `TableA`.`code` = `TableB`.`code`
            WHERE
                `TableB`.`loc` = 'foo'
        ) AS `dummy`
    )
);

It works, but by repeating the subquery for dummy twice the code would be very clumsy. It is obvious to me that I can disable the safe update mode to perform this query, but I just hope to know if there are any methods for matching the rows using the two primary keys (TableA.id1 and TableA.id2) without repeating the dummy derived table and without disabling the safe update mode?

Use correlated subquery

UPDATE `TableA`
SET `TableA`.`enable` = 1
WHERE EXISTS ( SELECT 1
               FROM `TableB`
               WHERE `TableA`.`code` = `TableB`.`code`
                 AND `TableB`.`loc` = 'foo' )


What is the default 'window' an aggregate function is applied to? The SQL Server documentation on the OVER clause HERE, mentions that the OVER clause:

  Determines the partitioning and ordering of a rowset before the associated window function is applied

By this I assume that aggregate functions are 'window functions'? While I understand what a window is (or at least how it works) when using the OVER clause, what is the default window for an aggregation function when no OVER clause is used?

i.e. for the SQL statement:

SELECT
  someCol,
  someCol2,
  AVG(someCol3) [avg]
FROM
  tbl
GROUP BY
  someCol,
  someCol2

What is the Window that the SUM function is being applied to?

Is it the grouped table prior to the SUM function being applied? In this case is it correct to assume that all window functions are the last operations to be applied to a result set? Since FROM / WHERE / GROUP BY / HAVING / SELECT / ORDER BY must all have been worked out to produce the final window on which to apply aggregation functions?

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

In response to the answer below by Max Vernon:

If I write this query:

SELECT
    CP.iYear,
    AVG(CP.mUpgradeCost) OVER(PARTITION BY iYear) AS [avg]
FROM ProForma.dbo.CapitalProject CP

I get the results you refer to, with a row for each year in CapitalProject, and since there are many repeated years I get the same average for each year. I.e.

+-------+------------+
| iYear | avg        |
+-------+------------+
| 0     | 18636.3636 |
+-------+------------+
| 0     | 18636.3636 |
+-------+------------+
| 1     | 46741.5151 |
+-------+------------+
| 1     | 46741.5151 |
+-------+------------+

I then changed the SQL to group on years so that I got a single line per year. the SQL is:

SELECT
    CP.iYear,
    AVG(AVG(CP.mUpgradeCost)) OVER(PARTITION BY iYear) AS [avg]
FROM ProForma.dbo.CapitalProject CP
GROUP BY CP.iYear

HOWEVER, when I altered the function to this:

SELECT
    CP.iYear,
    SUM(AVG(CP.mUpgradeCost)) OVER(PARTITION BY iYear) AS [avg]
FROM ProForma.dbo.CapitalProject CP
GROUP BY CP.iYear

I was expecting the averages for each row over the partitioned table to be added together. But they didn't. Why is that?

Clearly the window that the SUM is being applied to is not a nested query as i thought it might be

Your example code:

SELECT
  someCol,
  someCol2,
  AVG(someCol3) [avg]
FROM
  tbl
GROUP BY
  someCol,
  someCol2

The "window" for the AVG(someCol3) column is the combination of unique values for each someCol, someCol2 tuple, by virtue of the GROUP BY clause.

You could rewrite the above query as:

SELECT
  someCol,
  someCol2,
  AVG(someCol3) OVER (PARTITION BY someCol, someCol2)
FROM
  tbl

The results would be similar, although not identical.  The 2nd variant returns a result row for every row in the tbl table, whereas the 1st variant returns a result row for every unique combination of someCol, someCol2.  In the 2nd result set, each row would contain the average value of someCol3 for each unique combination of someCol, someCol2 by virtue of the PARTITION BY clause.

You can see this by creating a test-bed:

IF OBJECT_ID('dbo.TestAgg') IS NOT NULL
DROP TABLE dbo.TestAgg;
CREATE TABLE dbo.TestAgg
(
    someCol int NOT NULL
    , someCol2 int NOT NULL
    , someCol3 decimal(10,5) NOT NULL
);

INSERT INTO dbo.TestAgg (someCol, someCol2, someCol3)
VALUES (1, 1, 1)
    , (2, 2, 2)
    , (2, 2, 3)
    , (4, 1, 4);

SELECT ta.someCol
    , ta.someCol2
    , AVG(ta.someCol3)
FROM dbo.TestAgg ta
GROUP BY ta.someCol
    , ta.someCol2
ORDER BY ta.someCol
    , ta.someCol2;

SELECT ta.someCol
    , ta.someCol2
    , AVG(ta.someCol3) OVER (PARTITION BY ta.someCol, ta.someCol2)
FROM dbo.TestAgg ta
ORDER BY ta.someCol
    , ta.someCol2;

The results:

+---------+----------+------------------+
| someCol | someCol2 | (No column name) |
+---------+----------+------------------+
|       1 |        1 |         1.000000 |
|       2 |        2 |         2.500000 |
|       4 |        1 |         4.000000 |
+---------+----------+------------------+

+---------+----------+------------------+
| someCol | someCol2 | (No column name) |
+---------+----------+------------------+
|       1 |        1 |         1.000000 |
|       2 |        2 |         2.500000 |
|       2 |        2 |         2.500000 |
|       4 |        1 |         4.000000 |
+---------+----------+------------------+

If you specify a blank OVER () clause, the "window" is the entire resultset:

SELECT ta.someCol
    , ta.someCol2
    , COUNT(ta.someCol3) OVER ()
    , COUNT(ta.someCol3) OVER (PARTITION BY ta.someCol, ta.someCol2)
    , AVG(ta.someCol3) OVER ()
    , AVG(ta.someCol3) OVER (PARTITION BY ta.someCol, ta.someCol2)
FROM dbo.TestAgg ta

+---------+----------+------------------+------------------+------------------+------------------+
| someCol | someCol2 | (No column name) | (No column name) | (No column name) | (No column name) |
+---------+----------+------------------+------------------+------------------+------------------+
|       1 |        1 |                4 |                1 |         2.500000 |         1.000000 |
|       2 |        2 |                4 |                2 |         2.500000 |         2.500000 |
|       2 |        2 |                4 |                2 |         2.500000 |         2.500000 |
|       4 |        1 |                4 |                1 |         2.500000 |         4.000000 |
+---------+----------+------------------+------------------+------------------+------------------+

You can perform multiple aggregates on a single column however, you cannot do it in a single SELECT ... statement; you must use a subquery or CTE to do so:

SELECT s.someCol
    , s.someCol2
    , SUM(avg_someCol3)
FROM (
    SELECT ta.someCol
        , ta.someCol2
        , avg_someCol3 = AVG(ta.someCol3) OVER (PARTITION BY ta.someCol, ta.someCol2)
    FROM dbo.TestAgg ta
    ) s
GROUP BY s.someCol
    , s.someCol2

Returns:

+---------+----------+------------------+
| someCol | someCol2 | (No column name) |
+---------+----------+------------------+
|       1 |        1 |         1.000000 |
|       2 |        2 |         5.000000 |
|       4 |        1 |         4.000000 |
+---------+----------+------------------+

However, in this contrived example, the value of 5 makes no sense that I can understand as the sum of an average.  Perhaps your real problem makes more sense.  Perhaps you really want this:

SELECT s.someCol
    , s.someCol2
    , avg_someCol3
FROM (
    SELECT ta.someCol
        , ta.someCol2
        , avg_someCol3 = AVG(ta.someCol3) OVER (PARTITION BY ta.someCol, ta.someCol2)
    FROM dbo.TestAgg ta
    ) s
GROUP BY s.someCol
    , s.someCol2
    , s.avg_someCol3

Results:

+---------+----------+--------------+
| someCol | someCol2 | avg_someCol3 |
+---------+----------+--------------+
|       1 |        1 |     1.000000 |
|       2 |        2 |     2.500000 |
|       4 |        1 |     4.000000 |
+---------+----------+--------------+

This is more easily obtained by using the DISTINCT keyword, although it make me shudder to think I just suggested using it:

SELECT DISTINCT ta.someCol
    , ta.someCol2
    , AVG(ta.someCol3) OVER (PARTITION BY ta.someCol, ta.someCol2)
FROM dbo.TestAgg ta


maintaining full text index on large table I'm on SQL Server 2008 and have a table, for reporting purposes, with 500,000 records that will easily reach the millions. The table will employ a full text index for rapid searching
on a handful of columns.
As this is a reporting table and not the source transactional table, sometimes new records will be added, and other times existing records will have to be removed due to changes going on in the source table.
My question is in regards to the best way to build (ongoing) the reporting table and maintain the full text index for this table.
Once the full text index is added, should I:

leave the index alone, and delete/add records as appropriate
leave the index alone, truncate the reporting table, and then insert all appropriate records
other?

I've come across these articles so far while researching, but best practice for this scenario is not readily apparent.

https://docs.microsoft.com/en-us/sql/relational-databases/search/get-started-with-full-text-search?redirectedfrom=MSDN&view=sql-server-ver15
https://techcommunity.microsoft.com/t5/datacat/best-practices-for-integrated-full-text-search-ifts-in-sql-2008/ba-p/305000
https://social.msdn.microsoft.com/Forums/en-US/c743f6e4-88c6-478b-954b-ba8207e391c5/sql-2005-full-text-index-rebuild-nightly-best-approach?forum=sqldatabaseengine

Full text in SQL Server should have no trouble keeping up with your workload.  I currently support a full text index in SQL Sever that is  approximately 500,000  rows with 125,000  rows being inserted and deleted everyday.  Query load peaks at around 200 full text searches/sec. Response time it's fairly consistent in the .5 to 1.5 sec range.

Recommendations for full text

Use auto change tracking.  It just works and you don't have to schedule anything.
Create sql agent alerts for full text population errors.  Populations can fail (they rarely do).  When they do fail you have to resume them manually.  Having a sql agent job kick off an "ALTER FULLTEXT INDEX ON myTable RESUME POPULATION"  makes it a non-event.
Consider trace flag 7646  if your query load and the volume of updates are both really high.  This flag is applicable to 2008/2008 R2 (not 2012).  It reduces some blocking on internal data structures and was documented in a SharePoint best practices document. (You probably won't need this)
Otherwise, you probably won't need to do any additional tuning.  It typically just works.

Sql 2012 introduced substantial improvements in scalability and performance  so upgrade if you can.

As for how to manage the inserts and updates, just update the rows add you would normally.  I wouldn't recommend truncate/reload, but otherwise don't worry about it.


Cannot delete MySQL trigger. Appears in information_schema, but no .TRN file I have a MySQL database with a few tables and triggers. A process was trying to drop one of the triggers and it failed with the following error:

Malformed file type header in file 'trigger_name.TRN'

I tried to drop the trigger manually, but obviously got the same error. So I went ahead and deleted the .TRN file itself (was that a bad idea?).

After deleting the .TRN file, the trigger still appears in information_schema.TRIGGERS. If I try to drop it I get error "Trigger does not exist". If I try to create another trigger with the same name, table and event manipulation I get error "Trigger already exists".

I don't know what else to do. How can I really delete this trigger so that it can be created again?

MySQL version is 5.0.22 and all tables are MyISAM.

SHOW CREATE TABLE outputs:

CREATE TABLE `cash_operation` (
  `id` int(11) NOT NULL auto_increment,
  `amount` decimal(16,8) NOT NULL default '0.00000000',
  `comment` varchar(500) NOT NULL default '',
  `reason_id` int(11) NOT NULL default '-1',
  `date` datetime NOT NULL default '1980-01-01 00:00:00',
  `reconciliation_number` int(11) NOT NULL default '-1',
  `reason_desc` varchar(250) NOT NULL default '',
  `automatic` tinyint(1) NOT NULL default '0',
  `employee_id` int(11) NOT NULL default '-1',
  `employee_code` varchar(250) NOT NULL default '',
  `employee_name` varchar(250) NOT NULL default '',
  `payed_employee_id` int(11) NOT NULL default '-1',
  PRIMARY KEY  (`id`),
  KEY `cash_operation_reason_id` USING BTREE (`reason_id`),
  KEY `cash_operation_reconciliation_number` USING BTREE (`reconciliation_number`),
  KEY `cash_operation_automatic` USING BTREE (`automatic`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8

SELECT @@global.datadir; results in error "Unkown system variable 'datadir'"

Based on the suggestion I gave in the chat room, here is my answer

CREATE TABLE cash_op LIKE cash_operation;
Goto the datadir in your Windows server
rename cash_op.MYD cash_op.MYX
copy cash_operation.MYD cash_op.MYD
login to mysql and run

REPAIR TABLE cash_op;
ALTER TABLE cash_operation RENAME cash_op_old;
ALTER TABLE cash_op RENAME cash_operation;

Go create the two triggers on the new cash_operation table

You said in the chat room : Awesome, that worked! You can post it as the answer.

I am glad you gave it a try and it worked for you !!!

BTW you can drop the old table with DROP TABLE cash_op_old;


Restore SQL Server 2008 database onto SQL Server 2014? I have a backup file that I need to restore daily on schedule based.

But the  backup file is from SQL Server 2008 and restore destination is SQL Server 2014. And also the backup file is from cross network. I have copied to local shared folder and trying to restore it.

The code I used:

USE [master]
RESTORE DATABASE MyDB FROM
DISK = N'\\Mylocation\DBRestores\MyBackupFile'
WITH
    FILE = 1, -- 1 = .bak, 2 = .trn type backup
    MOVE N'MyDB_data' TO N'S:\MSSQLsql\mssql\DATA\MyDB.mdf',
    MOVE N'MyDB_log' TO N'L:\MSSQLsql\mssql\DATA\MyDB_log.ldf',
    NOUNLOAD,
    STATS = 5
go

but I get an error

  Msg 3241, Level 16, State 0, Line 3
  The media family on device '\Mylocation\DBRestores\MyBackupFile' is incorrectly formed.
  SQL Server cannot process this media family.

  Msg 3013, Level 16, State 1, Line 3
  RESTORE DATABASE is terminating abnormally.

Any help? thanks.

Check if the SQL Server user account is able to read from "\Mylocation\DBRestores\", the security permissions from file share and file system.

To really test that it works, copy the backup to local server and run it from there.


Database Mail Stop Working SQL Server 2016 Enterprise Edition SP1

Database Mail was working fine with no problem until a server patching was performed. I tried to do all the troubleshooting available but still all mails got queued and no records in the log.

Finally when I checked the database mail program I  didn't find the executable file in the Binn folder or any other folders!

My questions are how this happened and how to solve it ?

I certainly can't explain why any Windows patching would remove executables from your Binn folder; that sounds more like the work of "security" software or manual intervention.

In any case, there are a couple of things you can try:

run setup and try a repair against the SQL Server installation. Maybe start with just the shared components.
copy the missing files from the Binn folder of another installation that works and is the exact same edition and @@VERSION. If you don't have one of these readily available, you could probably install Evaluation Edition on your workstation or a VM. But really you should have a mirrored installation somewhere that matches production so that you can test Windows patching and any other updates there first before you "try it out" in production.


SQL Server Data Tools and partition functions I'm creating a sliding window loading scenario and the partition functions will be changing their boundaries through time.

I have created some partition functions in my SQL Server Data Tools (SSDT) database project with some initial boundaries hardcoded.

However, as time goes on and the partition functions' boundaries change, future SSDT database publishes will revert the boundaries to the original ones.

Is there some way to handle this scenario gracefully, possibly by disabling the publishing of the partition functions?

I've tried changing the Build Action property of the partition functions in SSDT, from the default Build, to None, but then the project fails to build due to a missing reference in the depending objects.

I was able to find a solution to my problem — hope this helps someone else.

To avoid each database publish recreating the partition function, you can check the Ignore partition schemes option in the Advanced Publish Settings dialog (Advanced... button in the Database Publish dialog).

From the description of the option (emphasis mine):

  Specifies whether differences in partition schemes and functions should be ignored or updated when you publish to a database.

However, if you have defined a partitioned object (table or index) with page or row compression, although the Ignore partition schemes option no longer recreates the partition function, the partitioned object will get recreated anyway.

This happens because the partitioned object gets scripted with the compression defined per partition, and since the object has a different number of partitions than it was originally defined, SSDT recreates the object on publish. E.g. (formatted):

WITH ( DATA_COMPRESSION = PAGE ON PARTITIONS (1)
     , DATA_COMPRESSION = PAGE ON PARTITIONS (2)
     , ...

In order for this not to happen, you can also check the Ignore table options option, in the same Advanced Publish Settings dialog — just keep in mind that you will be ignoring other options, such as ALLOW_ROW_LOCKS and ALLOW_PAGE_LOCKS (reference of table options).


SQLite3 - remove duplicates by multiple columns I'm looking for the most efficient way to remove duplicates from a table where id's are unique but there are equal rows when you check other columns.

Example:

id   name   age   x
1   peter   25    II
2   peter   25    II

The table has tens of thousands of rows.

Supposing you have no foreign keys referencing that table, you could do something like

create table some_table as
select min(id), name, age, x
from t
group by name, age, x

then you can drop the old table, rename the new table so that it has the same name as the old one before, and create indexes and other things you need on that table.


Oracle error : Shutdown immediate error echo $ORACLE_SID
YBRD
conn sys/hello as sysdba
SQL>shutdown immediate

Then I was waiting for it to shutdown for a couple of minutes. It didn't, so I closed my window.

Then I again logged in and it displayed:

ORACLE IMMEDIATE SHUTDOWN IN PROGRESS

Will it shutdown automatically by itself after a while?

Yes, it will -- of course if it persistently refuses to you might have to kill the processes, and face instance recovery on restart.


Permission error replica set I'm having some trouble with intentionally removing a secondary from a replica set. I'm trying to simulate unexpected shutdown.

My replica set consists of one primary and 2 secondaries. When I shut down the mongo instance on one of the secondaries and try to restart the service it fails with the message from the log which states:

Tue Dec 24 12:20:08.882 [initandlisten] couldn't open /var/lib/mongo/maars.ns errno:13 Permission denied

Simple chown -R mongod /var/lib/mongo solves the problem, but this shouldn't happend. Am I doing something wrong?

Ask for more info if you need something.

Usually the cause for this is that you are restart the mongod process as the root user (using sudo, or perhaps invoking directly as root) or some other user besides the appropriate MongoDB user (in your case mongod).  That is then screwing up the permissions in your data folder and giving you the error you are seeing.

If you examine how you are starting/stopping the process and correct it to only start with the correct user (or use the service commands instead) then this problem should stop happening.


Rebuilding large columnstore indexed tables - am I doing it right? We are planning to move the database from SQL 2014 ENT into 2016 ENT.
We realized the data currently in columnstore table are not aligned in time when they are loaded, so we need to re-align the data.

Stats:

3 big tables (columnstore indexed)
the big tables has 60+ billion rows
4 data files  (round robin)
partitioned by monthly
SQL Server 2014 ENT, 128 GB RAM
High Performance VM platform, 32 CPUs

File groups/datafiles:

PRIMARY
DATA (4 data files, each data file on 2TB disk)
LOG

Parition details:

Monthly partition function
All paritiona scheme on DATA FG

Table sizes (#rows):

T1   34,807,580,311
T2   16,458,306,369
T3   10,170,792,290

What I plan to do:

drop the columnstore index, create row-store index (this is to align the data in logtime)
then drop the row store index, convert the table into column store index (better compression and queries later)

I am concern this might took extremely long time to rebuild. Am I taking the right approach?

DDL:

CREATE TABLE [dbo].[T1](
[Id] [int] NOT NULL,
[C1] [smallint] NOT NULL,
[C2] [int] NOT NULL,
[C3] [int] NOT NULL,
[C4] [int] NOT NULL,
[C5] [datetime2](0) NOT NULL,
[C6] [real] NULL,
[C7] [real] NULL,
[C8] [real] NULL,
[C9] [real] NULL,
[C10] [real] NULL,
[C11] [datetime2](3) NULL,
[C12] [tinyint] NULL
) ON [DATA]

CREATE CLUSTERED COLUMNSTORE INDEX [T1_ColumnStoreIndex] ON [T1]

CREATE TABLE [dbo].[T2](
    [C1] [int] NOT NULL,
    [C2] [smallint] NOT NULL,
    [C3] [int] NOT NULL,
    [C4] [int] NOT NULL,
    [C5] [int] NOT NULL,
    [C6] [datetime2](0) NOT NULL,
    [C7] [real] NULL,
    [C8] [int] NULL,
    [C9] [int] NULL,
    [C10] [tinyint] NULL,
    [C11] [tinyint] NULL,
    [C12] [tinyint] NULL
) ON [DATA]

CREATE CLUSTERED COLUMNSTORE INDEX [T2_ColumnStoreIndex] ON [T2]

CREATE TABLE [dbo].[T3](
    [C1] [int] NOT NULL,
    [C2] [smallint] NOT NULL,
    [C3] [int] NOT NULL,
    [C4] [int] NOT NULL,
    [C5] [int] NOT NULL,
    [C6] [datetime2](0) NOT NULL,
    [C7] [real] NULL,
    [C8] [real] NULL,
    [C9] [real] NULL,
    [C10] [real] NULL,
    [C11] [real] NULL,
    [C12] [real] NULL,
    [C13] [real] NULL,
    [C14] [varchar](50) NULL,
    [C15] [datetime] NULL,
    [C16] [int] NULL,
    [C17] [int] NULL,
    [C18] [float] NULL,
    [C19] [tinyint] NULL,
    [C20] [tinyint] NULL,
    [C21] [datetime2](3) NULL,
    [C22] [tinyint] NULL
) ON [DATA]

CREATE CLUSTERED COLUMNSTORE INDEX [T3_ColumnStoreIndex] ON [T3]

This may not be Q&A but please I believe I can gain valuable inputs here.

What I plan to do: •drop the columnstore index, create row-store index
  (this is to align the data in logtime) •then drop the row store index,
  convert the table into column store index (better compression and
  queries later)

You can use CREATE CLUSTERED INDEX...WITH (DROP_EXISTING=ON) to change the existing columnstore clustered index to a b-tree and then CREATE CLUSTERED COLUMNSTORE INDEX...WITH (DROP_EXISTING=ON) to change back to columnstore. This will eliminate the drop index step that changes the columnstore to a heap.

I still expect this will take quite some time with 60 billions rows. Although source data might not have been loaded in C6 order, data overall are typically loaded in roughly chronological order so there will still be a temporal relationship for data in the same proximity. I suggest you review the min and max values in sys.column_store_segments to see if this effort is justified, considering that segment elimination is done after partition elimination. The performance benefit might not be a great as you think.


How to left join intermediate result table after filter ta(subquery)? +---------+----------------------------------------------------------------------------------------------------------------------------------------+
| Table   | Create Table                                                                                |
+---------+----------------------------------------------------------------------------------------------------------------------------------------+
| stuInfo | CREATE TABLE `stuInfo` (
  `stu_name` varchar(20) DEFAULT NULL,
  `stu_no` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+---------+----------------------------------------------------------------------------------------------------------------------------------------+

+----------+--------+
| stu_name | stu_no |
+----------+--------+
| Abcd     |      1 |
| Acmn     |      1 |
| Borne    |      1 |
| Bence    |      1 |
| Lucy     |      1 |
| Jone     |      2 |
| Lucy     |      2 |
| Lily     |      2 |
+----------+--------+

I want to get the number of (A*, 1), (B*, 1) as below

+----------+--------+
| A        | B      |
+----------+--------+
| 2        |      2 |
+----------+--------+

I consider of using left join.

select count(ta.stu_name) as A, count(tb.stu_name) as B
from (
    select stu_name, stu_no
    from stuInfo
    where stu_no=1
) filtered_table
left join filtered_table ta
    on stu_name like 'A%'
left join filtered_table tb
    on stu_name like 'B%';

However this doesn't work.
What I want to know is how can I first base on  some condition (my example, the condition is 'stu_no=1'), and then use the filtered table to do left join.
 I think the get the filtered table first can improve the performance as there are multiple left join tables(same table).

The worked sql is as below:

select count(ta.stu_name) as A, count(tb.stu_name) as B
from (
    select stu_name, stu_no
    from stuInfo
    where stu_no = 1
      and stu_name like 'A%'
) ta left join (
    select stu_name, stu_no
    from stuInfo
    where stu_no = 1
      and stu_name like 'B%'
) tb on 1=1;

I'm not sure I understand your problem, but judging from your sample data you can use a conditional count:

SELECT COUNT(CASE WHEN stu_name like 'A%' then 1 end) AS A
     , COUNT(CASE WHEN stu_name like 'B%' then 1 end) AS B
FROM stuInfo
WHERE stu_no = 1;

CASE WHEN stu_name like 'A%' then 1 end evaluates to 1 if stu_name starts with A and null otherwise. COUNT counts all non-null expressions.