How to pass collation to UDF How can we pass the collation to a user-defined function?

e.g. I can use a hardcoded collation (e.g. "de-AT-x-icu") like this:

CREATE OR REPLACE FUNCTION test(agg_1 jsonb, agg_2 jsonb) RETURNS jsonb
    LANGUAGE SQL
AS
$$
SELECT  jsonb_build_object(
  'count', (agg_1 -> 'count')::int8 + (agg_2 -> 'count')::int8
, 'max', greatest(agg_1 ->> 'max' collate "de-AT-x-icu", agg_2 ->> 'max' collate "de-AT-x-icu")
)
$$;

and the test works:

select test(
  jsonb_build_object('count', 2 , 'max', 'H'),
  jsonb_build_object('count', 0, 'max', 'i')
);

We tried to use a text-argument,

CREATE OR REPLACE FUNCTION test2(agg_1 jsonb, agg_2 jsonb, text_collation text) RETURNS jsonb
    LANGUAGE SQL
AS
$$
SELECT  jsonb_build_object(
                'count', (agg_1 -> 'count')::int8 + (agg_2 -> 'count')::int8
            , 'max', greatest(agg_1 ->> 'max' collate text_collation, agg_2 ->> 'max' collate text_collation)
            )
$$;

but this fails with:

ERROR: collation "text_collation" for encoding "UTF8" does not exist

What is the best way to pass/use a collation in a UDF in such a case?

Collations are database objects, so their name is an identifier.

You cannot use a parameter for an identifier, you'd have to use dynamic SQL in a PL/pgSQL function:

RETURN QUERY EXECUTE
   format(
      'SELECT ... COLLATE %I ...'
      text_collation
   );


SQL Server Configuration Manager shows no services I have installed Microsoft SQL Server Management Studio 2008 on my system. When I tried to open it for the first time, it asked me for the "Server Name" when I surfed I came to know that I have to check the instance name in Configuration manager-->Sql Services, but I couldn't find any services running in Configuration Manager, it is showing "There are no items to show"

I tried to find out but couldn't.

What am I doing wrong?

You have just downloaded the Management Studio, which is nothing else as an IDE. It seems to me, that you haven't downloaded/installed the SQL Server itself.

You can download it right here. On the website, just hit the download button and check the SQL Server, which is called something like SQLEXPR_x64... or if you running a 32 bit system SQLEXPR_x86....

For those who haven't SSMS downloaded, you can check SSMS on the same download page too and it will be downloaded with the server.


Robust way to build a DWH without downtime I have a process that build a data warehouse using Postgres. The process runs every day at midnight and works in three steps:

Copy the production database (prod) to a temporary database (dwh_tmp) using pg_dump
Generate the fact and dimension tables in the temporary schema using the data from the production database
Rename the current DWH database (dwh -> dwh_old) and move in the temporary database instead (dwh_tmp -> dwh) using ALTER DATABASE dwh_tmp RENAME TO dwh

The problem is that the last step fails if there are still open connections to the dwh database. To get rid of these connections (e.g. using pg_terminate_backend()) I would need to run the DWH generation process with super-user privileges, which I'd like to avoid for security reasons.

Any suggestions on how to implement this in a more robust way? My main goal is to make sure that the DWH is always in a good state and the downtime is as small as possible, hence my idea of first building a temporary DWH and then renaming it. I'm not in direct control of the connecting processes that keep me from renaming the database (as they're openend from a different host).

For reference, here's the solution that I came up with:

Create a new schema dwh
Build the new version of the DWH in that shema
Drop the old public schema
Rename the new dwh schema to public

(this assumes that your main schema is named public, of course)

This method works quite reliably and without interrupting/killing existing database connections, so I prefer the solution over my initial "create and rename database" approach. Here's the SQL that I use for building the DWH (it assumes that you're connected to the DWH database):

--we drop the temporary schema if it already exists
DROP SCHEMA IF EXISTS dwh CASCADE;
--we create the temporary schema
CREATE SCHEMA dwh;

--we modify the search path for this session
SET search_path to dwh;

--create your DWH here...

--now we commit the new version

--make sure you're connected to the correct database when executing this ;)
DROP SCHEMA IF EXISTS public CASCADE;

--we rename the DWH schema to public
ALTER SCHEMA dwh RENAME TO public;

--you might need to restore permissions on the new public schema...
GRANT USAGE ON SCHEMA public TO dwh_user;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO dwh_user;

Apart from needing to manually restore permissions and having to recreate extensions in the new schema, I found this solution to be quite optimal as it does not require superuser privileges for the DWH user, and works without creating/dropping any databases. I have not yet figured out a way to copy permissions from one schema to another, which is my only minor issue with this approach.


MySQL 5.6 OS X 10.8: how tell if my.cnf read? With mysqld running, I expect to see that /etc/my.cnf has been used by at least one of the commands

ps aux | grep mysql | grep -v grep

mysqladmin variables | grep defaults-file

showing the value of --defaults-file. But I see no such item. Why not?

I think /etc/my.cnf is being read, since if I put an erroneous entry in its [mysqld] section, e.g., error = true then when I try to start mysqld it aborts with an ERROR message.

I'm using the installation by running mysql-5.6.10-osx10.7-x86_64.pkg from mysql-5.6.10-osx10.7-x86_64.dmg.

Whenever mysqld is launched, it has four default locations it attempts to locate and read my.cnf
If you run the following:
mysqld --help --verbose | head -20 > junk.txt
cat junk.txt

You will see this:
mysqld  Ver 5.6.10 for Linux on x86_64 (MySQL Community Server (GPL))
Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Starts the MySQL database server.

Usage: mysqld [OPTIONS]

Default options are read from the following files in the given order:
/etc/my.cnf /etc/mysql/my.cnf /usr/etc/my.cnf ~/.my.cnf
The following groups are read: mysqld server mysqld-5.6
The following options may be given as the first argument:
--print-defaults        Print the program argument list and exit.
--no-defaults           Don't read default options from any option file,
                        except for login file.
--defaults-file=#       Only read default options from the given file #.

Note the line that says:
/etc/my.cnf /etc/mysql/my.cnf /usr/etc/my.cnf ~/.my.cnf

These are the four files read (if they exist).
The above displays come from a Linux Box. So, please run
mysqld --help --verbose | head -20 > junk.txt
cat junk.txt

and see what locations are echoed.
UPDATE 2013-03-17 22:35 EDT
The reason you cannot see the --defaults-file option is not visible is simple: Since one or more of the default my.cnf files are present, mysqld_safe does not show it on the command line.
Please note the following:

mysqld uses the four defaults
You cannot set --defaults-file for mysqld. That's the job of mysqld_safe
mysqld_safe needs it explicitly specified in order for it to be visible in the OS

Running SHOW GLOBAL VARIABLES; will not show any defaults file
mysql> show global variables like '%def%';
+------------------------+--------+
| Variable_name          | Value  |
+------------------------+--------+
| default_storage_engine | InnoDB |
| default_week_format    | 0      |
| table_definition_cache | 400    |
+------------------------+--------+
3 rows in set (0.00 sec)

mysql>


Storage does not failover in Windows cluster I created a lab setup for a 2 node cluster using Windows 2012 R2. I first configured the quorum and performed a failover between both the nodes, the quorum would also map to the respective node. But whenever I added the data and the log drives and tried to do a failover, they are not getting mapped to the respective nodes. What am I missing here? These are the steps I followed

1) Create New Virtual SCSI drive from the Server manager
2) Adding both the Nodes as targets with their IP addresses
3) Initialized SCSI target from both the nodes
4) Open Disk management and format the drives to MBR->NTFS-64k allocation
5) In the failover cluster manager -> add the disks to the cluster

Is there something else which I need to do? Please refer to the screenshots below

SCSI creation from Domain controller :

Quorum as part of cluster resources

All disks on SQL1 before failover

Disks on SQL1 after failover to SQL2. Notice that the Data and log drives are still on SQL1. I did not create any Cluster Shared Volumes here

Can you please post screen shots of the storage from Failover Cluster Manager? I'm interested in seeing the data and log drives in Failover Cluster Manager.


Why is MySQL choosing that index? I have a large indexed table with over 35 million rows. Here are the indexes, with a naming convention that shows their sequential column order:

IDX_E93438363DAE128B,
IDX_LIST_ID_EMAIL_ADDRESS_DELETED,
IDX_LIST_ID_SUBSCRIBED_DELETED_EMAIL_ADDRESS,
IDX_LIST_ID_SUBSCRIBED_DELETED_FIRST_NAME,
IDX_LIST_ID_SUBSCRIBED_DELETED_LAST_UPDATED_AT

My query of this table on my medium Amazon RDS instance is taking upwards of 15 minutes:

SELECT * FROM contact
WHERE list_id = '014c7cbe-c124-11e5-b4ea-0a4287b2e8c5'
AND subscribed = 1
AND deleted = 0

Using an EXPLAIN of the query I can see that MySQL is choosing to use the IDX_LIST_ID_EMAIL_ADDRESS_DELETED key, when I'd expect most of the other keys (IDX_LIST_ID_SUBSCRIBED_DELETED_...) to be more optimal.

Should I just trust the engine? Is 17 or so minutes a realistic time to wait for a query on a table this large?

17 minutes is not a reasonable time to wait. Try to provide an index hint:

SELECT *
FROM contact USE INDEX (IDX_LIST_ID_SUBSCRIBED_DELETED_EMAIL_ADDRESS)
WHERE list_id = '014c7cba-c124-11e5-b4ea-0a4287b2e8c5'
AND subscribed = 1
AND deleted = 0


Some queries not logging using RDS's SQL Server with C2 Auditing enabled Similar to a past issue I had with audited queries not showing when using Extended Events, I'm having an issue with queries not logging when using C2 Auditing on an RDS SQL Server instance.  (I would prefer to use Extended Events for my auditing, but this apparently cannot be enabled on RDS SQL Server instances currently.)

In this case, I enabled C2 Auditing via an RDS Parameter Group.  I verified that C2 auditing is enabled by going to my server instance's Properties (via SSMS).  I created a database, mytestdb, and then created a table within it, mytesttable.  I ran a few queries (e.g., INSERT INTO mytesttable ...; SELECT * FROM mytesttable).

I then determined the path to my trace log file (SELECT id, path, is_default FROM sys.traces;) and then retrieved the SELECT queries that have been run: SELECT starttime, textdata FROM fn_trace_gettable('D:\RDSDBDATA\Log\log_2.trc', DEFAULT) WHERE textdata LIKE N'select%' ORDER BY starttime;.

The result is my queries on mytesttable did not show up. My queries on the trace table do show up though, so some queries are being logged.

I also tried a query on one of the master database tables, but that didn't show up as well.  I also tried disconnecting from the database server and reconnecting (in SSMS), hoping that ending the session might clear some buffering going on, but that didn't help with getting the queries to show in the audit results.

I connected to the RDS instance with my "Admin" user account (created during RDS setup) and there is no associated Active Directory. This is an "SQL Server Standard Edition" RDS instance.

Any ideas on why these queries would not be logged?  Any other debugging ideas? Thanks!

You have to query file located on D:\RDSDBDATA\DATA\audittraceXXX.trc, It is the first query you ran to get trace path


Skip_log_bin and innodb_flush_log_at_trx_commit If I have skip_log_bin =1 is there any reason for not having innodb_flush_log_at_trx_commit = 0?

If I have understand correctly, innodb_flush_log_at_trx_commit, reads data from log file and stores them to disk in a way according to its value.

So if I am not using log (skip_log_bin=1) why should I have value other from 0 on  innodb_flush_log_at_trx_commit ?

Those refer to different logs.

The binlog (log_bin) is for replication and other purposes for capturing writes outside of the server.

The other log controls how securely a "pull-the-plug" crash can be recovered from.  =1 performs an extra write to make crashes 100% recoverable.  =0, or slightly better =2 have less overhead but are "good enough" for many applications.


postgresql has more DB connection in staging servers than production server We have postgresql 9.5 where we use it for an application confluence.
in staging and in production both max connection is same.
But we see staging uses more connection than production e.g staging has 170 connection to DB and production has 105.where as staging is very rarely used.
and among 170 connection 169 is idle.

can any body explain this behavior where highly used Db has less used connection and rarely used application has more used connection.

This is a little hard to guess at because it might be something unique to your staging setup, but here are some things to investigate:

Do you have either multiple staging environments that share the same database?
Do your developers attach to the staging database at all in the course of their work? Every open client is going to use database connections.

Many clients will identify themselves, and you query for current clients with this SQL taken from this posting:

SELECT *
FROM pg_stat_activity
WHERE datname = 'mydatabasename'

If you compare the results you might find the culprit.


Normalization Exercise Resources I want to sharpen my database normalization skills.  Where can I find good quality beginner-to-advanced level exercises (with solutions) on the web ?

Following are the resources which I'm using to brush-up my database skills and to teach newbies...

Animated DataBase Courseware

From Database Design to Advanced Concepts

wofford-ecs.org

Database Design - Normalization

db4u

Normalization exercises

http://www.sql-ex.ru/

Tons of sql exercises

http://www.tekstenuitleg.net/en/articles/software/database-design-tutorial/intro.html

Database Design Tutorial


Update oracle sql database from CSV I tried google at first but no luck.
Is it possible to update tables from csv file?
I am using SQL developer and I am using a script to export edited rows to a csv file. I would like to update the edited rows through that csv file from a client. I don't want to import the whole file as the client already has a mirror table, I just would like to update the data from what it is in the csv file.
Is this possible?

If not what would be the best approach?

I would suggest you to make use of External Tables. You can create an external table on your CSV file using ORACLE_LOADER driver and then update your existing table with data in your external table using DML (MERGE for example).

Consult Oracle Utilities Guide for detailed info.

What follows is my sample of how you can update tables from flat files.

First you create the directory where your flat file will be located:

SQL> create directory ext_tab_dir as '/home/oracle/ora_load';
Directory created.

SQL> grant read, write on directory ext_tab_dir to spongebob;
Grant succeeded.

Then you create your external table:

SQL> CREATE TABLE emp_load (empid          number(3),
                            first_name     CHAR(15),
                            last_name      CHAR(20),
                            year_of_birth  CHAR(4))
       ORGANIZATION EXTERNAL (
         TYPE ORACLE_LOADER DEFAULT DIRECTORY ext_tab_dir
         ACCESS PARAMETERS (RECORDS FIXED 21 FIELDS (
           empid          char(1),
           first_name     CHAR(7),
           last_name      CHAR(8),
           year_of_birth  CHAR(4)))
         LOCATION ('info.dat')
       );

We'll assume that you already have the table that you want to update with data from the flat file:

SQL> CREATE TABLE emp (empid         number(3),
                      first_name     CHAR(15),
                      last_name      CHAR(20),
                      year_of_birth  CHAR(4));
SQL> insert into emp values(1, 'SpongeBob', 'SquarePants', '1997');
SQL> insert into emp values(2, 'Patrick',   'Star',        '1997');
SQL> insert into emp values(3, 'Squidward', 'Tentacles',   '1997');

(Here, I just loaded it with sample data.)

Now that's what your info.dat contains, for example:

[oracle@oca ~]$ cat ora_load/info.dat
1Alvin  Tolliver1976
2KennethBaer    1963
3Mary   Dube    1973
4NandiniShastri 1985

At this point you will just update your existing table with above data from the external table:

SQL> merge into emp e1
       using emp_load e2
       on (e1.empid = e2.empid)
       when matched then update set e1.first_name    = e2.first_name,
                                    e1.last_name     = e2.last_name
       when not matched then insert
         values (e2.empid, e2.first_name, e2.last_name, e2.year_of_birth);

Here's what you'll get as a result:

SQL> select * from emp;
     EMPID FIRST_NAME      LAST_NAME            YEAR
---------- --------------- -------------------- ----
         1 Alvin           Tolliver             1997
         2 Kenneth         Baer                 1997
         3 Mary            Dube                 1997
         4 Nandini         Shastri              1985


sp_stop_job reports Success, but the jobs are still running We have some jobs that run via the scheduler. We noticed today that some are still running from the weekend, and we need to kill them.

We have used sp_stop_job and it reports that the job has been stopped sucessfully, but looking at the Job Activity Monitor that status is still Executing: 1 (Subplan_1). I am using various queries to get the jobs that are currently running and the jobs still appear.

SELECT [sjv].[name], [sjv].[description], [sja].[run_requested_date], [sja].[last_executed_step_id]
FROM [msdb].[dbo].[sysjobs_view] sjv
JOIN [msdb].[dbo].[sysjobactivity] sja
ON [sjv].[job_id] = [sja].[job_id]
WHERE [sja].[run_requested_date] is not null
AND [sja].[stop_execution_date] is NULL

How is this possible? Is it just sending an ABORT command or similar? And can I really kill the jobs immediately?

I'm rather new to this side of SQL.

You should keep in mind that aborting a job does not always mean the job is aborted as expected. It could be that the transactions needed to be rolled back and this may also take a while depending on the amount of data and the performance.


Query result in a daily date from a date range selection Postgres 9.3 I need to make a report that will output a result in a daily row result based on the datarange selected.

Example:

Table: sales
->id
->type
->gross
->created_at
->updated_at

For example my query is

SELECT COUNT(case when type = 'Completed Sale' then id end) as complete,
       COUNT(case when disposition = 'Partial Sale' then id end) as partial,
       SUM(gross)as revenue
FROM sales
WHERE created_at >= '2015-09-20 00:43:32' AND created_at <= '2015-09-24 18:43:32';

The range is Sept 20 to Sept 24

So basically the output will be like:

complete | partial | revenue
1          1         2.3

What I want the output to be like this

start date | end date    | complete | partial | revenue
___________|_____________|__________|_________|____________
2015-09-20 | 2015-09-21  | 1        | 1       | 2.3
2015-09-21 | 2015-09-22  | 3        | 4       | 4.3
2015-09-22 | 2015-09-23  | 3        | 4       | 4.3
2015-09-23 | 2015-09-24  | 4        | 4       | 3.3

So as you can see, the output is the daily basis of the start date and end date. If choose 1 month range then there will be 30 or 31. Is that possible?

All that you have to add is the day (be it the start or the end date).  This can be obtained using a cast to date, for example.  When you add it to the query, you'll need a GROUP BY clause, too, on this expression - that will produce you the daily aggregates.
The whole query will look like
SELECT created_at::date AS start_date,
       created_at::date + 1 AS end_date,
       COUNT(case when type = 'Completed Sale' then id end) AS complete,
       COUNT(case when disposition = 'Partial Sale' then id end) AS partial,
       SUM(gross) AS revenue
FROM sales
WHERE created_at >= '2015-09-20 00:43:32' AND created_at <= '2015-09-24 18:43:32'
GROUP BY created_at::date;

Notes:

using start_date and end_date like this can be slightly confusing - in normal life one expects the end date to be inclusive.  With the way you use it might not be clear which data belong to which day - I would simply omit the end date.
in the GROUP BY clause it's enough to mention the cast expression only once - adding one day in another columns does not result in a different grouping.


In Data Warehouse Foreign Key on Fact tables Necessary? In DW, does ForeignKey have advantages other than data integrity?

For ongoing updates to DW, we have to enable and disable FKs and that causes locks and sometimes ends up causing deadlocks against select queries as victim.

Can someone please give some good scenarios of fks usage and how to handle the updates/insert against Datawarehouse ?

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

A summary from the master, Ralph Kimball, himself:

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

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


Copy Permission From Windows Based user To Local user I want to know if it is possible to copy the permissions of a windows user to a locally created user on SQL server?

Yes, it is. I guess you mean "Windows Login" and "SQL Server Login".
You can script permissions using system objects such as sys.database_permissions or using a tool such as Idera SQL permissions extractor.


How can I test a constant boolean expression? Suppose I have a simple boolean expression involving only constants, like

4 + 5 = 9

Maybe (for debugging purposes - maybe the expression is a complicated LIKE expression) I want to just check to see if this expression is true or false. Is there a simple trick for doing this?

Currently I'm just using

SELECT * FROM my_schema.my_table WHERE 4 + 5 = 9;

and seeing if I get anything back. I thought SELECT 1 WHERE expression might work, but no luck (MySQL 5.6.10). To be more precise about what I mean by "simple trick", let's say we want a command which will run without modification on any server. My current command doesn't fit the bill since we need to know the name of a schema and table in the server.

You can use

SELECT (4 + 5 = 9) AS expression_result ;

It will either give you 1 (TRUE), 0 (FALSE) or NULL (UNKNOWN).

If you prefer string output instead of numbers:

SELECT CASE (4 + 5 = 9)
         WHEN TRUE  THEN 'TRUE'
         WHEN FALSE THEN 'FALSE'
         ELSE            'UNKNOWN'
       END AS expression_result ;

Alternatively:

SELECT 1 FROM dual WHERE (4 + 5 = 9) ;

It will give you either 1 (TRUE) or no rows in the results (FALSE or UNKNOWN).

I'd prefer to use the first method as it distinguishes between all three possible results of a boolean expression in SQL (TRUE / FALSE / UNKNOWN).


Create a second Replica set on same 3 nodes as another Replica set Can a second 3 node Replica set be installed on the same 3 nodes with another 3 node Replica set without interference?

Yes, for testing and development. Not for production, obviously. Note below, the different port numbers and data paths for each mongod instance.

If you had the first replica set like this:

mkdir -p /srv/mongodb/rs0-0 /srv/mongodb/rs0-1 /srv/mongodb/rs0-2

mongod --port 27017 --dbpath /srv/mongodb/rs0-0 --replSet rs0 --smallfiles --oplogSize 128 --fork

mongod --port 27018 --dbpath /srv/mongodb/rs0-1 --replSet rs0 --smallfiles --oplogSize 128 --fork

mongod --port 27019 --dbpath /srv/mongodb/rs0-2 --replSet rs0 --smallfiles --oplogSize 128 --fork

You could add a second replica set like this to the same localhost with the following:

mkdir -p /srv/mongodb/rs1-0 /srv/mongodb/rs1-1 /srv/mongodb/rs1-2

mongod --port 37017 --dbpath /srv/mongodb/rs1-0 --replSet rs1 --smallfiles --oplogSize 128 --fork

mongod --port 37018 --dbpath /srv/mongodb/rs1-1 --replSet rs1 --smallfiles --oplogSize 128 --fork

mongod --port 37019 --dbpath /srv/mongodb/rs1-2 --replSet rs1 --smallfiles --oplogSize 128 --fork

Reference: Deploy a Replica Set for Testing and Development


max_allowed_packet in mySQL I tried all three method explained here  to max_allowed_packet. But no one changes its value in my MySQL 5.6.

I use show variables like '%max_allowed_packet%' to see its current value. But it always is 12582912.

Only changing its value in my.ini is effective.

What is wrong?

You have two values of max_allowed_packet in MySQL :

one on the client side : [mysql] section, [mysqldump], [client] and more.
one on the server side : [mysqld] section.

The value of max_allowed_packet you see with the command show variables like 'max_allowed_packet'; is the one on the server side.

In order to increase this value, you must increase both sides : in your server configuration file ([mysqld] section in your my.ini file) and in your client configuration file (whether your [client] or [mysql] section in your my.ini file).

This setting can be changed on the server side without restarting the server if you have the SUPER privilege with this command : mysql> SET GLOBAL max_allowed_packet = numeric;.

Don't forget to change the 'numeric' value by a numeric value. Don't forget to change your configuration file too otherwise this value will be reset at reboot.


Design for archiving a sorted list (with ability for a few INSERT) I have a somewhat static sorted list of words which I want to save in a database table:

-- simplified!
-- Note: the rank/order of each item is NOT calculable from other values!
CREATE table words (
  id INTEGER UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  word NVARCHAR(50),
  -- foreign key
  book_id INTEGER UNSIGNED
);

The order of words in a 'book' have to be retained.

But what is the best way to store the order of all datasets?

I've already read How to design a database for storing a sorted list? on this site. But I don't have many INSERTs (if I will ever have one) between two records and I don't expect my database to grow up to more than 100k.

So I thought I could use the primary key column id for storing the index in the list. But what if I have to insert one record between two ids?

The other possibilty is to add another column. Is it better to store the numerical position of the dataset or the neighbour IDs in there?

I'm using MySQL.

Example:

I have this list from an external resource:

house
dog
browser
database

Now I have to enter these values in same order into the database!

INSERT INTO words (word) VALUES ('house'), ('dog'), ('browser'), ('database')

The order is now described via the id column (which is the primary key at the same time).

But suddenly, I have to insert another word between 'house' and 'dog'.
I can't simply change the PK id because that would break other table relations.

By all means use an unsigned int surrogate key as your primary, clustered index.  However, instead of using sequential values, build some padding into the sequence.  This means that you'll have to assign the id manually instead of using auto_increment.

If you use unsigned int in MySQL, the max value is 4,294,967,295.  If you expect to have at most 100,000 rows that means you could space each word out by more than 42,000.

When you need to insert a word between two existing words, just plug it into the space half way between.  Let's say you use 40,000 as your intitial padding value.  If you have "house" at 800,000 and "dog" at 840,000 you can insert "nouveau" at 820,000.


How to deal with unbounded growth in cassandra shards Let's say we have a website like youtube where we have a comment session per e video. Users want to be able to comment and to see the comments made by other users by comment creation time. This is relatively easy to shard when you know there will be just a small amount of comments per video or you have a limit on the amount of comments per video. But how can we shard these comments when there growth is unbounded?

I have found very few information about this problem.
https://medium.com/@foundev/synthetic-sharding-in-cassandra-to-deal-with-large-partitions-2124b2fd788b

Usually, people add some 'bucket' field to the partition key - it could be simple bucket, with limited number of possible values (1-10, for example), or it could be time-bounded bucket, like, year+month, or even daily bucket. Main thing here is that it should be predictable, so you can send queries against these buckets.

With comments it's becoming trickier, as for really popular videos the number of comments could grow really fast, but this could be solved by lookup table (or as in article, shard table). In this case you need to do a read from this table as a first step, and then send many reads in parallel to fetch necessary partitions - this could be relatively fast, as you don't want to load all comments at once, and you're usually show only recent comments, and then load older on demand.


ALTER TABLE AUTO_INCREMENT number in a Trigger I want to have my auto increment column to increment by step of 3 upon insert.

Can I do something like:

Alter table `mytable` auto_increment = (select max(id) from mytable) + 3;

in a Trigger?

I understand that MySQL has a AUTO_INCREMENT_INCREMENT setting, but since I am on a shared server, I cannot change it.

In MySQL you can insert directly into AUTO_INCREMENT Column. So, take this example from MySQL manual:

CREATE TABLE animals (
     id MEDIUMINT NOT NULL AUTO_INCREMENT,
     name CHAR(30) NOT NULL,
     PRIMARY KEY (id)
) ENGINE=MyISAM;

INSERT INTO animals (name) VALUES
    ('dog'),('cat'),('penguin'),
    ('lax'),('whale'),('ostrich');

SELECT * FROM animals;

Which returns:

+----+---------+
| id | name    |
+----+---------+
|  1 | dog     |
|  2 | cat     |
|  3 | penguin |
|  4 | lax     |
|  5 | whale   |
|  6 | ostrich |
+----+---------+

Let's try this:

TRUNCATE TABLE animals;

CREATE TRIGGER increment_insert BEFORE INSERT ON animals
  FOR EACH ROW
    SET NEW.id = (SELECT MAX(id) + 2 FROM animals);

INSERT INTO animals (name) VALUES
    ('dog'),('cat'),('penguin'),
    ('lax'),('whale'),('ostrich');

SELECT * FROM animals;

Which returns:

| ID |    NAME |
|----|---------|
|  1 |     dog |
|  3 |     cat |
|  5 | penguin |
|  7 |     lax |
|  9 |   whale |
| 11 | ostrich |

(BTW, it is not STEP of 3, it is STEP of 2 you are after)

SQL Fiddle


Merge replication - corrupt row continually trying to INSERT We have "push" SQL replication sent up, with one publisher and three subscribers. Two of the three are working fine, but one is continually causing trouble. I went through the pain and heartache of reinitialising the subscription at the weekend, and just a few days later it's stuck again.

This time, the error I'm seeing in replmerg.log (after turning up the verbosity) is:

  The merge process could not replicate one or more INSERT statements to
  the 'Publisher'. A stored procedure failed to execute. Troubleshoot by
  using SQL Profiler.

I've tracked this down to a single table in the database: for some reason, an obviously corrupt row is continually trying to be INSERTed into the table, with a rowguid of 00000000-0000-0000-0000-000000000000. DBCC CHECKDB shows no sign of corruption.

Here's the row:

If I try to get rid of the bad row on the Publisher:

delete from mytable
where rowguid like '00000000-0000-0000-0000-000000000000'

I get the error:

  Msg 2601, Level 14, State 1, Procedure
  MSmerge_del_590C419D78ED4A36A40292C23CF5F67B, Line 68 Cannot insert
  duplicate key row in object 'dbo.MSmerge_tombstone' with unique index
  'uc1MSmerge_tombstone'. The duplicate key value is (3095029,
  00000000-0000-0000-0000-000000000000). The statement has been
  terminated.

If I manually remove the row from MSmerge_tombstone, it comes back after the next replication cycle (which is continuous, so typically 60 seconds later).

I really don't want to have to reinitialise this subscription again. Is there any way of purging this set of corrupt transactions out of the "queue", or otherwise returning the replication back to normal?

I've managed to hack my way around the issue, so I'll leave this here for posterity and in case anyone else has the problem. I've no doubt this is an extremely bad idea, and will cause my whole server to implode, but it has got replication firing again.

The msmerge_contents table seems to contain the "queued" UPDATEs and INSERTs, and it's one or more of the latter that seems to be causing the problem. So I did

delete from MSmerge_contents
where tablenick=xxx

... where xxx is the integer identity of the table from the other replication tables. This cleared out a couple of thousand rows, I restarted the agent, and after a bit of processing everything was back to normal. Fortunately, the table in question didn't contain anything too vital, so I don't need to worry about integrity too much.

Again, this is undoubtedly Not The Way To Do It, but it's appeared to work for me.


distinguish single-block vs multi-block reads in Oracle explain plan If I am looking at a query plan in Oracle 11g, is there any way I can tell which operations will result in single-block reads and which will result in multi-block?

TABLE ACCESS FULL and INDEX FAST FULL SCAN use multi-block I/O, other kind of access paths use single-block I/O.


protecting network from external attack (SQL server 2012 bruteforce) My SQL server 2012 log indicates multiple bruteforce attempts to logon to the server.

Login failed for user 'sa'. Reason: Password did not match that for the login provided. [CLIENT: xxx.xx.xx.xx]

The account has a very secure password, which buys time for now.

It was recommended that I use a trigger to prevent ip logins. However, this is proving ineffective. How do I put an end to this issue.

CREATE TRIGGER IPtrigger
    ON ALL SERVER
    FOR LOGON
AS
BEGIN
    DECLARE @IP Varchar( 500 )
    SET @IP = EVENTDATA().value( '(/EVENT_INSTANCE/ClientHost)[1]', 'varchar(500)' )

    -- set of the restricted IP addresses
    IF @IP IN ( 'xxx.xx.xx.xx' )
    BEGIN
        Print 'IP BANNED FROM LOGON: ' + @IP
        ROLLBACK TRANSACTION
    END
END
GO

You can disable SA account and wait until attack ends. This is not a solution for the problem, but can buy some time to reconfigure your network.

/* Disable SA Login */
ALTER LOGIN [sa] DISABLE
 GO
/* Enable SA Login */
ALTER LOGIN [sa] ENABLE
 GO


What cases benefit from the Reduce, Replicate, and Redistribute join hints? The From Clause Documentation starting with SQL Server 2008 briefly mention 3 join hints and their basic mechanisms:

Reduce
Replicate
Redistribute

However there does not seem to be much information on when it might become necessary to use them.

It appears that they can be used in conjunction with the hash, loop and merge which are already understood for purpose of this question.

The relevant section from the documentation:

  For SQL Data Warehouse and Parallel Data Warehouse, these join hints apply to INNER joins on two distribution incompatible columns. They can improve query performance by restricting the amount of data movement that occurs during query processing. The allowable join hints for SQL Data Warehouse and Parallel Data Warehouse are as follows:

  REDUCE
  Reduces the number of rows to be moved for the table on the right side of the join in order to make two distribution incompatible tables compatible. The REDUCE hint is also called a semi-join hint.
  REPLICATE
  Causes the values in the joining column from the table on the left side of the join to be replicated to all nodes. The table on the right is joined to the replicated version of those columns.
  REDISTRIBUTE
  Forces two data sources to be distributed on columns specified in the JOIN clause. For a distributed table, Parallel Data Warehouse will perform a shuffle move. For a replicated table, Parallel Data Warehouse will perform a trim move. To understand these move types, see the "DMS Query Plan Operations" section in the "Understanding Query Plans" topic in the Parallel Data Warehouse product documentation. This hint can improve performance when the query plan is using a broadcast move to resolve a distribution incompatible join.

In general terms we use a join hint is to correct a failure of the optimizer to identify a better query plan. This can happen for many reasons which broadly boil down to optimization being a "good enough" approach constrained by a time budget for finding a plan.
Reduce
"also called a semi-join hint". With a semi-join we are only interested in whether or not any matching row exists in the other table. Indeed, it is often because the query has an EXISTS() predicate. If there is one match or one million the result will be the same - the single row from the left (outer) table will be returned.
As an example, we may want to query all Customers who have ever placed an Order. Let's say Customer is a small table but Order is huge and spread over many nodes. It would be wasteful to copy all that data to wherever Customer is held when all we need is to know if at least one row exists for each Customer. The REDUCE hint provides this. Conceptually it is select distinct CustomerId from Order run on each node holding Order, which is relatively fast.
Replicate
This is the converse of REDUCE. Here a table is copied en masse from its "home" node to all nodes holding data from the table on the right-hand side of the join.
For this example we want the total value of all orders, summed by Customer city. We only have a relatively few customers which comfortably fit on a single node. The Orders table, however, is huge and spans many nodes. To satisfy the query we have to get both Customer and Order data onto the same node. It will be more efficient to copy Customer, since it is smaller. The REPLICATE hint forces that behaviour, making a complete copy of the Customer table on each node holding Order rows.
Redistribute
This is another way of stating how rows should be moved from their "home" node to another node so join predicates can be evaluated. This time we are asking the server to copy the data and distribute that copy according to some new distribution function such that the copied rows will end up on the right node to complete the join.
Say now we have a retail e-commerce site with lots of customers and lots of orders. Customers are distributed by CustomerId and Orders distributed by OrderDate. If we now want to join on Customer.CustomerId = Order.CustomerId the most efficient plan may be to copy Order and re-distribute according to CustomerId, then pass each new partition to the corresponding node. This differs from REPLICATE as there the whole table is copied to every node whereas here a portion of each node's data is copied over to other nodes. The specific way the re-distribution is handled will depend in the current distribution and the cardinalities.


Setting variables in SQLCMD mode Using SQL Server 2008 R2 Enterprise Edition

Consider the following statement:

:setvar source_server_name "SERVERNAME\INSTANCENAME"

Is it possible in SQLCMD mode to get that value using TSQL

Something like: :setvar source_server_name = SELECT @@servername

Thank you

Update 7/15/2013

The two answers offered below did not quite give the desired result, so I'm adding a more relevant exampe.

:setvar source_server_name [myserver]

The variable source_server_name is set to the text string [myserver_1]

I'd like to be able to do this:

create table #tmp(
id int identity(1,1),
server sysname
)

insert into #tmp values('myserver_1'),('myserver_2');

:setvar source_server_name = SELECT server FROM #tmp WHERE id = 1

select '$(source_server_name)'

(No column name)
myserver_1

The variable source_server_name would be set to the value in server for id 1.

I think you need to output your results to a file and bring them back in. Something like this should help get you close to what you are looking for:

:setvar MyDir "C:\scripts"
:OUT $(MyDir)\test.txt
PRINT ':SETVAR ServerName ''' + @@SERVERNAME + ''''
GO
:OUT stdout
:r $(MyDir)\test.txt
GO
SELECT $(ServerName)

You can see more examples here.


Exchange rate table and conversion on the fly in query I'm working on a small project and I'm have arrived to the point where I let a user chose in which currency to display items.

I'm working with Java and my first thought was that I would use some kind of API to pull the rates and post process (loop) after I get the the items from the DB. But I'd rather not have to unnecessarily loop if I can directly get the right price form the query.

So I think I'm going to store all the exchanges rate in the DB and have my query do the conversation job here is my first draft:

 CREATE TABLE currency (
      code VARCHAR(3) NOT NULL,
      symbol VARCHAR(3) NOT NULL,
      display_name VARCHAR(4) NOT NULL,
      PRIMARY KEY (code),
      UNIQUE KEY uq_ccode (code)
    );

CREATE TABLE exchange_rate (
  currency_code_from VARCHAR(3) NOT NULL,
  currency_code_to VARCHAR(3) NOT NULL,
  rate numeric(15,4) NOT NULL,
  created timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (currency_code_from, currency_code_to),
  FOREIGN KEY (currency_code_from) REFERENCES currency(code) ON DELETE CASCADE,
  FOREIGN KEY (currency_code_to) REFERENCES currency(code) ON DELETE CASCADE
);

I would then write a query like:

select price as originalPrice, (price * rate) as TargetPrice FROM .....
JOIN exchange_rate er ON er.currency_code_from=(the currency of the item) AND er.currency_code_to=(the user currency)......

Note that on the above tables I would have to have every single combination

GBPEUR
EURGBP
GBPGBP (well i guess i can probably avoid this one if a slighty amend the query to detect this scenario)

Plus.. I would have to have some kind of cron job to daily update this table.

Now I'm far to be a banker nor a SQL genius so I would like some input from people that have already written such tables/query and that have expertise in this kind of stuff

Am I fare off with my design? Would I be better off post processing the results and do the conversation in java  with rates coming from an API?

How are your underlying item prices stored?

If, for example, your items are all priced/stored in EUR, then there is no need for an NxN (duplicative) currency matrix.

In this case, exchange_rate (against EUR or your base currency) can simply be a field in the currency table (vs an independent table).

Then all of your conversions for display (which are all then simply EUR->XXX) can be calculated with a single JOIN.


How to convert a datefield from string to date? Here is the query that I am using. What I need to extract is the data for the last 10 minutes. But no records found is the output for this query, I think this is due to the type of data that were inserted to created_at field, it should be date field but string were inserted. How to convert this field so I can use it on the $match

db.collectionName.aggregate([{
   $match : {
        created_at : {
               $gte : new Date(ISODate().getTime() - 1000 * 60 * 10)
                     }
            }
      }]);

You'll need to run a script to detect for integer/string based values and replace them with the standard Mongo/ISO Date standard.

https://docs.mongodb.com/manual/reference/method/Date/


Combining two similar queries into one (PostgreSQL) I have 2 queries like this:

SELECT c.id FROM component c, base_vee bv
    WHERE
        c.id IN (SELECT component_id FROM registry_row WHERE registry_id = 199)
        AND
        c.id = bv.component_id
        AND
        (bv.affidavit IS NULL OR bv.affidavit = '')

SELECT c.id FROM component c, base_vee bv
    WHERE
        c.id IN (SELECT component_id FROM registry_row WHERE registry_id = 199)
        AND
        c.id = bv.component_id
        AND
        (bv.affidavit IS NOT NULL AND bv.affidavit != '')

What I need to get is a list of component IDs with False if bv.affidavit is emptyish or True if it's not emptyish.

Right now I'm obviously getting two lists of component IDs for each case.

It works, but one query like described above would be nicer.

Use the CASE function:

SELECT c.id,
       CASE WHEN COALESCE(bv.affidavit, '') = '' THEN False
            ELSE True
       END
FROM   component c, base_vee bv
WHERE  c.id IN (SELECT component_id FROM registry_row WHERE registry_id = 199)
AND    c.id = bv.component_id;


SQL Server Error Log - Is It / Can It Be Encrypted? Is it possible for the SQL Server Error Logs to be encrypted?

Our SQL Server recently had a memory dump which it threw into the Error Log.  While the dump was definitely useful to identify the issue, it got me wondering about the visibility of that dump to users who may have access to the server, but not necessarily SQL Server.

I know TDE protects Data, Log, and Backup files at rest, but potentially sensitive information can be exposed via a memory dump - especially if it is intentionally engineered by a malicious source.

The contents of the dump looks similar to this:

02f59f10  70b50b54 00000101 000000a2 00650073  T..p........s.e.
02f59f20  00760072 00720065 0027003d 006f006c  r.v.e.r.=.'.l.o.
02f59f30  00610063 0068006c 0073006f 00270074  c.a.l.h.o.s.t.'.
02f59f40  0054003b 00750072 00740073 00640065  ;.T.r.u.s.t.e.d.
02f59f50  0043005f 006e006f 0065006e 00740063  _.C.o.n.n.e.c.t.
02f59f60  006f0069 003d006e 00720074 00650075  i.o.n.=.t.r.u.e.
02f59f70  0041003b 00700070 0069006c 00610063  ;.A.p.p.l.i.c.a.
02f59f80  00690074 006e006f 004e0020 006d0061  t.i.o.n. .N.a.m.

I want to be proactive and protect against possible data breaches by encrypting the SQL Server Error Log if possible to reduce the chance of intentional memory dumps exposing sensitive data to non-authorized users.

Is it possible for the SQL Server Error Logs to be encrypted?

There is no option that "turns on errorlog encryption", so you'll need to use encryption at a lower layer, such as bitlocker or similar disk encryption utilities.

  Our SQL Server recently had a memory dump which it threw into the Error Log. While the dump was definitely useful to identify the issue, it got me wondering about the visibility of that dump to users who may have access to the server, but not necessarily SQL Server.

If they have access to the server then they have access to SQL Server. No two ways about it.

  I know TDE protects Data, Log, and Backup files at rest, but potentially sensitive information can be exposed via a memory dump - especially if it is intentionally engineered by a malicious source.

TDE wouldn't help here, and in general full disk encryption won't help either if the sources can already log into the server - regardless of having access to SQL Server or not.

Anyone that can log into the server can potentially read memory. Some permissions might not be locked down and debuggers can be attached. Other avenues also exist which I won't get into here but if they have access, it's already too late.

  I want to be proactive and protect against possible data breaches by encrypting the SQL Server Error Log if possible to reduce the chance of intentional memory dumps exposing sensitive data to non-authorized users.

I completely understand and you should be proactive, kudos! Sadly, there really isn't anything native inside of SQL Server to do this. Encrypting it through 3rd party solutions may cause other issues.

I would say the best thing is to make sure no one has access to the server and who has access to SQL Server. Anecdotal evidence: I once was able to see a bunch of data on a "locked down" server because there was a share that had "everyone" as read permissions.


determine redo log size, number of redo log files Having 2 node RAC instance using ASM
Log switches happening are too high about 100 per hour causing db to hang for few minutes sometimes

   GROUP#  THREAD#    MEMBER                                    ARC STATUS   FSIZE(MB)
---------- ---------- ----------------------------------------- --- -------- ----------
1          1 +CCFA_DATA01/ccfada/group_1.327.956874159          YES ACTIVE      50

2          1 +CCFA_DATA01/ccfada/group_2.329.956874159          NO  CURRENT     50

3          2 +CCFA_DATA01/ccfada/group_3.328.956874142          YES ACTIVE      50

4          2 +CCFA_DATA01/ccfada/group_4.326.956874161          NO  CURRENT     50

Could you help determine the right size of redo log files, right number of redo log files and steps to resize redo log files in ASM (DB size is approx. 6.5Gb)

The recommendation is to have log switches occur a few times an hour. If you have a 100 log switches an hour with 50MB redo logs, you need e.g 1GB redo logs for 5 log switches/hour.
100*50 MB redo/hour for a 6.5GB database is unusually much though.

You can add the new logs, e.g:

ALTER DATABASE ADD LOGFILE THREAD 1 SIZE 1G;
ALTER DATABASE ADD LOGFILE THREAD 1 SIZE 1G;
ALTER DATABASE ADD LOGFILE THREAD 2 SIZE 1G;
ALTER DATABASE ADD LOGFILE THREAD 2 SIZE 1G;

Then force log switches until the new logs are used (CURRENT status) and old logs become ACTIVE/INACTIVE by:

ALTER SYSTEM SWITCH LOGFILE;

Then drop the old logs:

ALTER DATABASE DROP LOGFILE GROUP 1;
ALTER DATABASE DROP LOGFILE GROUP 2;
ALTER DATABASE DROP LOGFILE GROUP 3;
ALTER DATABASE DROP LOGFILE GROUP 4;

If you can't drop a logfile group because it is ACTIVE, do

ALTER SYSTEM CHECKPOINT;

so it becomes INACTIVE and try again.


Get name column instead of ids from two foreign keys I need to get both group names as a result, although I have no idea how to do this query.

Table 1 (groups)

+---------+--------+
| id      | name   |
+---------+--------+
| 1       | newb   |
| 2       | user   |
| 3       | editor |
| 4       | boss   |
+---------+--------+

Table 2 (inheritance)

gid = group id; pid = parent id

+---------+--------+
| gid     | pid    |
+---------+--------+
| 3       | 1      |
| 3       | 2      |
| 4       | 1      |
| 4       | 2      |
| 4       | 3      |
+---------+--------+

Expected Results

Now I want the names of the groups as a result, not the ids, so I need to get this result:

+---------+--------+
| gname   | pname  |
+---------+--------+
| editor  | newb   |
| editor  | user   |
| boss    | newb   |
| boss    | user   |
| boss    | editor |
+---------+--------+

Thanks!

You need to use 2 copies of names table:

SELECT groups_1.name AS gname, groups_2.name AS pname
FROM inheritance
JOIN groups AS groups_1 ON inheritance.gid = groups_1.id
JOIN groups AS groups_2 ON inheritance.pid = groups_2.id


School database Design Diagram I want to design a database for school the output of the system have to be like this :

So I design this Database

Is there any tips or  notes that may make it better .
Thanks in advance

From your comments, Person represents the generic information about any person tied to this system; students, teachers, other school employees.  Information that's in Person wouldn't need to be repeated in Student (name, address, DOB).

I would add the student's current grade/class (kindergarten through high school) to Student. Let's call that StudentClass; we'll use it later.

Also - you may need an intermediary table to go with MarkType and Mark.  I would think you'd need something to indicate the total available points for each item (as shown in the headers of your output). MarkType makes sense as is. I would have Mark as a table indicating a particular exam, quiz, homework assignment (etc.). So, it would need:

MarkID (primary key)
MarkType
SubjectId
MarkValue - the points this specific item is worth (for example 10 points).

Then, I would have StudentMarks:

StudentMarkId (primary key)
MarkId
StudentId
Mark - the points earned on this specific item, by this student (for example one student might get 9 points out of the possible 10, other might only get 5).

I would also be inclined to decouple Term and Subject - there may be subjects that are only offered for one term. Similarly, different instructors may provide a different mix of assignments, and assign different values to a given assignment than another instructor. I would create two new tables:

Instructor:
 - InstructorId (primary key)
 - PersonID (foreign key to Person, where name, address, DOB would be)
 - ... (other information specific to a given instructor, regardless of the subject they're teaching, or the term/year they're teaching it in)

Course:
 - CourseId (primary key)
 - SubjectId
 - TermId
 - InstructorId

TermId would be removed from Subject, and CourseId would replace SubjectId in Mark.

Another note: it might make sense to specifically tie a student to a course. that could be called....

CourseRoster:
 - StudentCourseId (primary key)
 - StudentId
 - CourseId
 - StudentCurrentClass (the value of StudentClass at the time this student was in this class).

In that case, replace StudentId with StudentCourseId in StudentMarks.

This would even let you identify if there were exams/quizzes/assignments that a particular student in a particular course had no mark for.

UPDATE: Based on your comments, I added some fields to track the student's current "class" (K through high school), and their class when they took a specific course. consists of hard-coded values (in the US, there's kindergarten plus 12 years of education before university, so "K" and "1" - "12" would work here), the Class table shouldn't actually be needed.If you need to establish explicit classes for some reason, then the Class table would still be needed. I would reverse it, though - one class has multiple students, so put ClassID in the Student table. Note: you may want to consider tying each Class to a specific Term, as well.


Optimize duplicated but negated subquery I have what I call a 'Hamlet' query in the form:
select * from stuff a
left join otherStuff b on (a.x=b.x) -- lots of joins ommited
where
 (b.ToBe = 1 and a.Being in (select Being from stuffThatIs where stuffThatIs.Name = b.Name)
 or
 (b.ToBe <> 1 and a.Being not in (select Being from stuffThatIs where stuffThatIs.Name = b.Name)

The most-nested queries being identical, so I thought the database would reuse the result instead of doing 2 queries.
But in the execution plan the query shows twice with the same cost of 19%.
Can this be optimized manually? Or a hint added?
I'd prefer a portable solution instead of a maximum perf one.
There are indexes on all named columns, sometimes even unique. The example is simplified, the whole where clause is in a join condition itself etc. So please optimize just the pattern, not the whole query.

Maybe this query would be better?
We would check stuffThatIs for every row, and then validate if

ToBe=1 and there is a.Being in stuffThatIs  OR
ToBe<>1 and there isn't a.Being in stuffThatIs

SELECT *
FROM someStuff a
LEFT JOIN otherStuff b ON (a.x = b.x)
OUTER APPLY (
  SELECT 1 [is_inside]
  WHERE EXISTS(SELECT *
               FROM stuffThatIs x
               WHERE x.Name = b.Name
                 AND x.Being = b.Being) -- instead of "...a.Being in (select Being..."
) x
WHERE ((a.ToBe = 1 AND x.is_inside IS NOT NULL)
    OR (a.ToBe <> 1 AND x.is_inside IS NULL))


Should I snowflake or duplicate it across my facts? I'm building up a data warehouse to be used by SSAS to create cubes on, and I'm debating between two possible schemas.
In my warehouse, I've got two different fact tables that tracking daily changes in dollar values. Each entity in these fact tables have an underlying Sales Order and Line to which they relate. These SOs and Lines then have other related dimensions, such as customer, product, etc. About 12 sub-dimensions total so far.

My question is if I should be rolling all these sub dimensions up directly into the fact tables, or if I should use a little snowflaking in my warehouse, and have them branching off the Sales Order and Lines dimension instead.

The first option obviously follows a star-schema model better. However, if changes are made such as adding additional dimensions, it becomes more maintenance, basically having to do the ETL twice for each fact table, rather than just the once on the SO dimension. As well, if a new fact is added that relates to Sales Orders, I'd have to go through the whole process again.

As this is my first DW/OLAP project, I'm not familiar on where the line should be drawn on snowflaking, and other people's thoughts would be highly appreciated.

The "header - detail" pattern is very common the domain of sales transactions.
To answer your question, there are so many factors that will come into play which you've not discussed. For example:

If your DW infrastructure has a great deal of RAM and is on SSD storage, reads in this case are cheap, so it might make sense to denormalize some dimensions in the interest of usability.
What are the use-cases of the data?  In this case I can probably make assumptions - it is sales data. It'll be used for accounting, executive reporting, predictive analysis, customer service, and for just about every possible ad-hoc query you can imagine.

One general principle I use when deciding whether to snowflake a dimension or simply include it's value in the fact table is this:

If the dimension has many attributes which might be useful for reporting (or if there will be report(s) solely on that dimension), I create a dimension for it.

Example: Consider the CUSTOMER dimension. A sales order has a customer, but there are other attributes which belong with the CUSTOMER dimension which you might want to report on, like customer location, customer age/sex/marital status, customer type, customer create date, etc., and many other customer-related attributes. I wouldn't put all of these in a fact table, so in this case I "snowflake" to a customer dimension as there are many more attributes related to CUSTOMER which might be relevant to your sales fact data. There would likely also be reports that solely rely on the CUSTOMER dimension - like a "new customer by month" report. You wouldn't expect this to be in the fact data. The PRODUCT dimension is another I would almost always put in it's own dimension.

If the dimension is a single value with no other useful attributes connected to it, I may consider it for inclusion in a fact table.

Example: We might have an attribute called "Order Source Channel" - which might be a single value describing where the sales order came from e.g. it might have values like eCommerce, Kiosk, Point-of-Sale, Phone-In, etc. It's a single value, and no other related attributes exist for this entity. In this case, I am tempted to leave it in the fact table, rather than create a single-attribute dimension and require my users to do an additional join, etc.

Remember:

The above is a generality, and I don't treat is as a hard-fast rule
Data modeling is as much an art as it is a science. There are many scenarios which can go either way, and only experience will help you decide which way to go.
Usability of your DWH structure should be as much a consideration as is performance. I try to never create a data model that requires my power-users to write SQL queries with 15+ or more joins just to get at sales data. This will lead to someone writing incorrect SQL (it always does). This will sometimes be mistaken as "bad data". This is what you, as a DWH developer, don't want happening.


SQL Sever Transactional Replication on DB with deadlocks and blocking issues Is it feasible to try and start Transactional Replication on a SQL Server DB with deadlocks and blocking issues. Are there any other options for a close to hot backup?

Probably not a good idea to add transactional replication to an already struggling db, have you enabled read committed snapshot isolation on the database ? Assuming you have already added appropriate indexes this is normally this is a good place to start with blocking and deadlocking issues (assumes your application uses read committed the default transaction isolation level).


Postgres 12: declare a function as constant or immutable ignoring parameters? In a trigger function I want to conditionally do pg_notify() calls, depending on an application setup option. In order to do that I created a wrapper notify_conditionally(). If the application is started with the notifications disabled, a second SQL function will create this wrapper that doesn't do anything and is defined as IMMUTABLE:

CREATE OR REPLACE FUNCTION notify_conditionally(channel text, payload text) RETURNS int
LANGUAGE plpgsql IMMUTABLE AS
$inner$
BEGIN
    PERFORM 1 WHERE 1 = 0;
    RETURN 0;
END;
$inner$;

If notifications are enabled, this function is defined as a call to pg_notify() with the parameters of the function:

CREATE OR REPLACE FUNCTION notify_conditionally(channel text, payload text) RETURNS int
LANGUAGE plpgsql STABLE AS
$inner$
BEGIN
    PERFORM pg_notify(channel, payload);
    RETURN 0;
END;
$inner$;

Since the parameters to conditionally_notify() (and hence pg_notify()) change with every call, I suppose the IMMUTABLE keyword doesn't help much in terms of result value caching (in fact, it might even be counter-productive?). Therefore I am wondering is there a way that I can define this function as "constant" and have the planner be clever enough to not call this function or only call it once and reuse the first result for all other calls? This would basically be like an IMMUTABLE that ignores all parameters. This would be useful to avoid calling this function if notifications are disabled. On bigger queries this makes a difference from ~500ms in my case and ideally I could lower this in the case of disabled notifications.

While it is a possibility to also update the code that uses notify_conditionally() function dynamically based on whether notifications are enabled and in case they aren't just not use notify_conditionally(), I would prefer to not do this. The reason is that this happens in a rather large trigger function that currently should only be changed through a full migration.

Declare the no-op version as an SQL function then it will be in-lined as a constant value by the optimiser.

CREATE OR REPLACE FUNCTION notify_conditionally(channel text, payload text) RETURNS int
LANGUAGE sql AS
$inner$
SELECT 0;
$inner$;


Permission required to view a database diagram I have recently set up SSDT for our developers to use. We enforce changes to our dev databases are made through SSDT by limiting the permissions that each developers has when connected to the server (db_datareader, db_datawriter). Within SSDT we publish our changes to the database using a deployment script which connects using a log on with elevated permissions.

My question. Given that we have gone to this length to lock down the database (to stop schema drift); is there any way that the developers can view the diagrams on this database without having to have db_owner permission? I know that each developer can create and view his or her own diagrams but i want them to be able to view all of the diagrams, which have been created by many different developers.

I don't think this will help but we are running sql server 2012

Any help will be greatly received.

From the documentation:

  Although any user with access to a database can create a diagram, once the diagram has been created, the only users who can see it are the diagram's creator and any member of the db_owner role.
  Ownership of diagrams can only be transferred to members of the
  db_owner role. This is only possible if the previous owner of the
  diagram has been removed from the database.
  If the owner of a diagram has been removed from the database, the diagram will remain in the database until a member of the db_owner role attempts to open it. At that point the db_owner member can choose to take over ownership of the diagram.

So it seems that you won't be able to do it with lower roles like db_datareader.

Behind the scenes, here is what Management Studio is calling to drive the list:

CREATE PROCEDURE dbo.sp_helpdiagrams
(
    @diagramname sysname = NULL,
    @owner_id int = NULL
)
WITH EXECUTE AS N'dbo'
AS
BEGIN
    DECLARE @user sysname
    DECLARE @dboLogin bit
    EXECUTE AS CALLER;
        SET @user = USER_NAME();
        SET @dboLogin = CONVERT(bit,IS_MEMBER('db_owner'));
    REVERT;
    SELECT
        [Database] = DB_NAME(),
        [Name] = name,
        [ID] = diagram_id,
        [Owner] = USER_NAME(principal_id),
        [OwnerID] = principal_id
    FROM
        sysdiagrams
    WHERE
        (@dboLogin = 1 OR USER_NAME(principal_id) = @user) AND
        (@diagramname IS NULL OR name = @diagramname) AND
        (@owner_id IS NULL OR principal_id = @owner_id)
    ORDER BY
        4, 5, 1
END

So you can see this matches the documentation.

Now a couple of workaround ideas:

In a Logon trigger, update the principal_id of all diagrams to be the current login. This means they will have access to all diagrams until the next person logs in. Not optimal.
Use a trigger on the sysdiagrams table itself (it's not really a system table), and whenever a diagram is created or updated, add / update a copy for each principal (with their user name appended). Not optimal either, and you could have people overwriting each other's diagrams all day long.

Here is an idea of the second workaround - all you really have to maintain here is a list of the database principals that you want to be able to access the diagrams (you'll also want to have something to clean up diagrams that have been deleted, and also some periodic maintenance that deletes diagrams for principals that have been deleted):

CREATE TRIGGER dbo.sysdiagrams_distribute
ON dbo.sysdiagrams
WITH EXECUTE AS N'dbo'
FOR INSERT, UPDATE
AS
BEGIN
  SET NOCOUNT ON;

  DECLARE @p TABLE(principal_id INT, name SYSNAME);

  INSERT @p SELECT principal_id, name
    FROM sys.database_principals
    -- change this list:
    WHERE name IN (N'test_blat_user', N'test_blat_user2', N'dbo');

  UPDATE d
    SET [version] = i.version, definition = i.definition
  FROM inserted AS i
  CROSS JOIN @p AS p
  INNER JOIN dbo.sysdiagrams AS d
  ON d.name = i.name
  AND d.principal_id = p.principal_id;

  INSERT dbo.sysdiagrams(name, principal_id, version, definition)
    SELECT i.name, p.principal_id, i.version, i.definition
    FROM inserted AS i
    CROSS JOIN @p AS p
    WHERE NOT EXISTS
    (
      SELECT 1 FROM dbo.sysdiagrams WHERE name = i.name
      AND principal_id = p.principal_id
    );
END
GO

After creating a couple of diagrams, here is what an abridged version of Object Explorer looked like for these users:

Now, dbo will collect a whole bunch of copies of diagrams, which maybe isn't necessary, but you probably want those to be the "master" in most circumstances.


When choosing which structural table design for one-to-many relations I am facing a problem. I have to design a database schema table structure. Generally I have a small hierachical tree structure with one-to-many relations:
A
|-B1
|  |-C1
|  |-C2
|
|-B2
   |-C3
   |-C4

Now, from my point of view, I have two options to design this:

For A and B, I could create two tables, whereas table_a references table_a directly via a foreign key. I thinks, this could be the better approach because you'll need less join operations on queries.
CREATE TABLE table_a (
    id int PRIMARY KEY,
    some_specific_data text
);

CREATE TABLE table_b (
    id int PRIMARY KEY,
    some_other_specific_data text,
    id_parent int REFERENCES table_a(id)
);

For A and B I could create two independent tables and an additional relation table which connects both tables (as I would do for many-to-many relations). Here both tables are more independent, which, I believe, should be desirable in general.
CREATE TABLE table_a (
    id int PRIMARY KEY,
    some_specific_data text
);

CREATE TABLE table_b (
    id int PRIMARY KEY,
    some_other_specific_data text
);

CREATE TABLE a_to_b (
    id int PRIMARY KEY,
    id_a int REFERENCES table_a(id),
    id_b int REFERENCES table_b(id)
);

From my point of view, both are valid designs. However, I am not able to decide which one should be chosen. Are there any arguments for choosing either option 1 or option 2 or even a third option?
Edit:
To be more specific:
A and B are not of the same type. You can think of it as:

A is a house
B is a door
So, a house has many doors
a door is contained by one house

I think some of the confusion is in how you labeled things. Object A and Object B are different from Table A and Table B, because as bbaird points out, Object A and Object B are the same Object (or entity in the context of databases).
From my understanding of your problem description you can think of your data as if it were actual Parents and Children for a second to help conceptualize. Parents could go into one Table and Children in a separate Table but they share close enough characteristics because both are People and probably should live in the same primary Table. Then you can have a second Table that represents the direct relationship of a Child to its Parent. After thinking of it this way, your first design choice makes more sense (which is a pretty common design choice for a Parent-Child relationship set of data.)
Note: The above answer was prior to OP updating his question. Please see the accepted answer for further clarification.


Server roles in SQL Server 2016 There are 3 new server roles in SQL Server 2016:

mediumrc
largerc
xlargerc

Any idea what these are for? Google doesn't give any information about these.

These terms are not new to SQL 2016, but they are also not server roles as far as I know.

For all i know these are pre-built Parallel Data Warehouse resource classes.

There isn't much documentation on that except some blog posts.

Have a look at these

PDW, Integration Services and Resource Classes
A teched presentation from Channel 9

I only heard about these in some presentation though, I have never worked with PDW so I can't help you any more than this.

Edit

Just adding in one additional resource (direct link to download PPT)
SQL Server 2012 Parallel Data Warehouse. You are basically controlling who gets more "speed" with processing by what role they are members of, at least from what I gather. The last slide in the deck:

Edit

In the meantime an article on workload management has been posted in the Azure documentation: Concurrency and workload management in SQL Data Warehouse where the following is mentioned (thanks Bob Klimes):

  SQL Data Warehouse exposes four different resource classes in the form
  of database roles as part of its workload management implementation.

  The roles are:

smallrc
mediumrc
largerc
xlargerc


Postgresql size of Slave is bigger than Master size I have a Postgresql in master slave streaming replication. Master is located in four partitions (tablespaces).
df -h on Master shows me

Filesystem      Size  Used Avail Use% Mounted on
***
/dev/md121      880G  490G  346G  59% /mydb/1
/dev/md122      880G  613G  223G  74% /mydb/2
/dev/md123      880G  322G  514G  39% /mydb/3
/dev/md124      880G  506G  330G  61% /mydb/4

but on Slave it takes more disk space on /mydb/4 partition

Filesystem      Size  Used Avail Use% Mounted on
***
/dev/sdb        880G  613G  223G  74% /mydb/2
/dev/sda        880G  448G  388G  54% /mydb/1
/dev/sdc        880G  322G  513G  39% /mydb/3
/dev/sdd        880G  773G   63G  93% /mydb/4

And it grows. WAL files are located in /mydb/1. Where I was mistaken?

Config of Slave

wal_compression = on
autovacuum_naptime = 2s
autovacuum_analyze_scale_factor = 0
autovacuum_vacuum_scale_factor = 0
max_wal_senders = 5
autovacuum_analyze_threshold = 1000
checkpoint_timeout = 40min
temp_buffers = 3000MB
autovacuum_vacuum_threshold = 1000
autovacuum_vacuum_cost_delay = 100ms
wal_keep_segments = 1000
wal_level = hot_standby
autovacuum_vacuum_cost_limit = 5000
autovacuum_max_workers = 6
listen_addresses = '192.168.1.4'
max_wal_size = 100GB
hot_standby = on

Recovery.conf on Slave

standby_mode = 'on'
primary_conninfo = 'user=replication password=mysecretpassword host=master.mydomain.local port=5432 sslmode=prefer sslcompression=1 krbsrvname=postgres'

Config on Master

wal_compression = on
autovacuum_naptime = 2s
autovacuum_analyze_scale_factor = 0
autovacuum_vacuum_scale_factor = 0
max_wal_senders = 5
autovacuum_analyze_threshold = 1000
checkpoint_timeout = 40min
temp_buffers = 1GB
autovacuum_vacuum_threshold = 1000
autovacuum_vacuum_cost_delay = 100ms
wal_keep_segments = 6000
wal_level = hot_standby
autovacuum_vacuum_cost_limit = 5000
autovacuum_max_workers = 6
listen_addresses = '192.168.1.5'
cpu_index_tuple_cost = '0.0005'
wal_buffers = 16MB
checkpoint_completion_target = '0.9'
random_page_cost = 2
maintenance_work_mem = 32GB
max_wal_size = 60GB
synchronous_commit = false
work_mem = 2GB
cpu_tuple_cost = '0.001'
default_statistics_target = 500
effective_cache_size = 96GB

SOLVED. It was not DB problem. du command showed correct size of DB. Problem was in my BackUP system. It helds descriptors of deleted files. Restart of backup service solved problem

lsof | grep '(deleted)'  shows deleted files process ID in second column.


file sql.gz give error 1064 when import in phpmyadmin I tried to import a .sql.gz file that I exported before another server in phpmyadmin in this new phpmyadmin, the phpmyadmin version is the same, the sql is being imported the same way it was exported (GZIP), but the 1064 error and does not conclude

I was opening the .gz file in the text editor, it includes the delimiter - additional to see if it goes, but the result is the same

In the text editor, the .gzip file is not completely visible to the SQL text, after the thousand line is compressed, small sample text

  q@WG°LSÔµ
  Ä]0Scˆc»†8¼ëaˆ#üp¢–GwuyqdW—ïX¤^ù¡†Çumqd×…±Ä±]mXcˆãº†8¦ëaˆã93†£É`óóe’e/œ8º·àHÙ\
  OìÛx¶2jò6o‚ŸgÛü¼Œ£ÕñEšÍ±ëU¦>Ç…ò‡ †æ!

I need to import this database in phpmyadmin and I can not, I exported another one to see if I could import, no, does phpmyadmin have any configuration that prevents importing the gzip file itself?

extract and load sql:
gzip -d filename

or edit the phpmyadmin code to fix this permanently as shown here.


List databases in Maintenance Plan I'm trying to list all databases in existing Maintenance Plans. (or, more importantly those NOT in a plan...)

I'm using the followed code, based on this blog:

Select DISTINCT p.name as 'Maintenance_Plan'
, RIGHT(smpld.line4,LEN(smpld.line4)-6) as 'Backup_Type'
, sp.subplan_name as 'Subplan_Name'
, smpld.line3 as 'Database_Names'
, job.name As 'Job_Name'
From msdb.dbo.sysjobs As job
join  msdb.dbo.sysmaintplan_subplans sp
on sp.job_id = job.job_id
inner join msdb.dbo.sysmaintplan_plans p
on p.id = sp.plan_id
join msdb.dbo.sysmaintplan_log smpl
on p.id = smpl.plan_id
and sp.subplan_id =smpl.subplan_id
join msdb.dbo.sysmaintplan_logdetail smpld
on smpl.task_detail_id=smpld.task_detail_id
where job.[enabled] = 1
and smpld.line3<>''
and smpld.line4 like '%Full%'

However, sysmaintplan_logdetail.line3 is only a nvarchar(256) so doesn't store the complete list and I'm not sure what use this column is!  It's also comma separated and not easy to interrogate.
There is a Command column in sysmaintplan_logdetail.  This is nvarchar(max) and does indeed contain all the detail of the maintenance plan.  However it would take a lot of text manipulation to extract just a list of databases. Typical contents:

BACKUP DATABASE [SP2013_Enterprise_Search_LinksStore] TO  DISK = N''\\uk-sba-sql02\backups\SP2013_Enterprise_Search_LinksStore_backup_2015_11_16_113747_3942470.bak'' WITH  COPY_ONLY, NOFORMAT, NOINIT,  NAME = N''SP2013_Enterprise_Search_LinksStore_backup_2015_11_16_113747_3942470'', SKIP, REWIND, NOUNLOAD,  STATS = 10

Can anyone suggest a simple robust way of extracting database details from maintenance plans?  I'm on SQL Server 2012.  I normally use the Ola Hallengren Maintenance Solution, but I've inherited these.

Thanks

Ian

-- This seems to be close to I think what you are needing
-- shows dbs not in a maint plan (run pointing to msdb)
select
t1.name as 'db_name',
--t2.database_name,
t1.crdate as 'date_created'
FROM [master].[dbo].[sysdatabases] as t1
left outer join
dbo.sysdbmaintplan_databases as t2
on t1.name = t2.database_name
where t2.database_name is null
and t1.name not in ('tempdb','msdb','model','master')
order by crdate desc


Is there any performance difference between a group by statement and nested select statements in sql? I am curious to know about the differences between using group by statement and nested selects to achieve the same result.
for example
consider I have two tables such as the following:

tblPurchase
ID
SellerID
BuyerID
Date
Price

tblPerson
ID
Name
Mail
Mobile

Now i want to show the list of purchases along with the associated names of the seller and the buyer
Since sellerID and BuyerID refers to the same table, i need two nested selects to fetch their names, so i would write:

Select tblPurchase.ID, tblPurchase.Date,
       select (tblPerson.Name from tblPerson where tblPerson.ID = tblPurchase.SellerID ) as [Seller's Name],
       select (tblPerson.Name from tblPerson where tblPerson.ID = tblPurchase.BuyerID ) as [Buyer's Name]
from tblPurchase

this is the first method, and i guess it can be done using group by statement as well ( though myself couldn't do it, since both fields refer to one able, i got stuck at the join section!)

Is there any difference between these two statements ? since i think group by itself is actually translated to the same nested selects later and then executed.

You're mixing apples and oranges, my friend. Your query is equivalent to:

Select p.ID, p.Date,
       s.Name AS [Seller's Name],
       b.Name AS [Buyer's Name]
FROM tblPurchase AS p
LEFT JOIN tblPerson AS s ON p.SellerID=s.ID
LEFT JOIN tblPerson AS b ON p.BuyerID=b.ID;

.. with one exception: Your subquery won't work if it returns more than one record (i.e. if ID isn't unique in the tblPerson table).

GROUP BY, on the other hand, is used to aggregate data. That is, to calculate sums, counts, and so on. If you want to know how many orders each buyer placed, you would use GROUP BY along with an aggregate function (SUM(), MAX(), COUNT(), etc), like this:

SELECT BuyerID, COUNT(*) AS [Number or orders per buyer]
FROM tblPurchase
GROUP BY BuyerID;

If you want to see the number or purchases per buyer and date, you would add the Date column to the SELECT as well as the GROUP BY:

SELECT BuyerID, Date, COUNT(*) AS [Number or orders per buyer]
FROM tblPurchase
GROUP BY BuyerID, Date;


differences between hot standby vs warm standby postgresql？ I am confused about the Differences BETWEEN the database replication methods mentioned in a wiki page of postgres, which is best for normal situation?

warm-standby/Continuous archiving/log shipping

offers high availability

http://www.postgresql.org/docs/current/static/warm-standby.html
hot-standby/binary-replication/Streaming Replication

used for read-only querie

https://wiki.postgresql.org/wiki/Hot_Standby

https://wiki.postgresql.org/wiki/Binary_Replication_Tutorial
PITR

Those words are often misunderstood and notions are often mixed up together because they arrived in the same version of PostgreSQL.

Warm-standby is simply a replica that's not open for read-only SQL statements.
In opposition, Hot-standby is a replica that is open for read-only SQL statements.
Before that, we had a "cold standby". It was simply a restored backup, ready to be started in case of the primary node went down.
Log-shipping is the old school technic used to send the WALs (write Ahead logs) to a replica so it can replay transactions that took place on the primary node. The file is sent once the primary node isn't writing in it anymore, so lag between primary and secondary depends on how fast the primary will need another file(you can specify it needs a new file after x minutes though).
Streaming replication is the upgraded technic that allows to send modified data blocks without waiting for the WAL file to be closed by the primary. It's send as soon as that block is writtentin the WAL file. So we can reduce the lag between primary and secondary nodes.
Continuous archiving is what allows Postgres to archive WAL files when they're not needed anymore.
Binary replication is a synonym for physical replication (which warm and hot standby are).It simply means both nodes are binary identical.

I hope this will help!


How to add `--sslAllowInvalidCertificates` on mongodb uri? I have a mongodb server requires x.509 certificate. So I have to use --sslPEMKeyFile and --sslCAFile on mongo shell command to connect to that server. It works fine. In my dev environment, I am using --sslAllowInvalidCertificates parameter. It works fine. But I wonder how I can use --sslAllowInvalidCertificates through nodejs driver. I have checked this link http://mongodb.github.io/node-mongodb-native/2.2/reference/connecting/connection-settings/ but it doesn't have the option for invalid certificates.

There's a detailed post that might answer this question here:

Connecting to MongoDB over SSL with Node.js


How present the result table below the last result query table, SSMS 2017 os: window 10
SSMS 2017, v17.9.1

I watched youtube videos of SSMS running two different queries and the last result was displayed below the last table.
Example that I am referring in youtube.
Now when I run a new query It does not conserve the last table there, I only can see the last result table.
I want to see the last two or three tables there in the same windows to compare them, Is it possible?
Thanks in advance.

Yes, actually if you run two queries at the same time it should automatically display to separate windows with the result.


I think you are looking something like this.
This will list all products in the same topic id..

SELECT
   productClassification.asin
 , productClassification.topic
 , productClassification.weight
FROM (
 SELECT
   productClassification.topic
 FROM
   productClassification
 WHERE
   productClassification.asin = 'B01LYQ7HHQ'
)
 AS
 productClassification_filter
INNER JOIN
 productClassification
ON
 productClassification.topic = productClassification_filter.topic
AND
  productClassification.asin != 'B01LYQ7HHQ'
ORDER BY
   productClassification.topic ASC
 , productClassification.weight ASC

Result

asin         topic         weight
----------  ------  ---------------
B01MATR1ZN       1    99.1049586790
B01M7MPM53       1    99.1240145475
B01KWK92UQ       1    99.5800318809
B01LXSHRCW       1    99.6681413232
B01LZF3T03       1    99.6694739441
B01M11J5C5       1    99.6694741630
B01M11HUG0       1    99.6694747525
B01M1DBCJR       1    99.6747018048
B01M1DAC7T       1    99.6759819038
B01M1DAN1S       1    99.6759829421
B01M1DCIFY       1    99.6759830134

But if you want to limit per topic id you need to use user variables.
This query will show the 3 best matching products per topic id

SELECT
   productClassification_ordered_ranked_limited.ASIN
 , productClassification_ordered_ranked_limited.topic
 , productClassification_ordered_ranked_limited.weight
FROM (
  SELECT
    @topic := NULL
  , @rank := 0
)
 AS
  init_user_vars
CROSS JOIN (

SELECT
   productClassification_ordered_ranked.asin
 , productClassification_ordered_ranked.topic
 , productClassification_ordered_ranked.weight
 , productClassification_ordered_ranked.rank
FROM (
  SELECT
     productClassification_ordered.asin
   , productClassification_ordered.topic
   , productClassification_ordered.weight
   , @rank := (
    CASE
     WHEN @topic := productClassification_ordered.topic
     THEN @rank + 1
     ELSE @rank := 0
    END
     ) AS rank
   , (@topic := productClassification_ordered.topic)
  FROM (
    SELECT
      productClassification.asin
    , productClassification.topic
    , productClassification.weight
    FROM (
      SELECT
        productClassification.topic
      FROM
        productClassification
      WHERE
        productClassification.asin = 'B01LYQ7HHQ'
      )
      AS
        productClassification_filter
      INNER JOIN
        productClassification
      ON
        productClassification.topic = productClassification_filter.topic
      AND
        productClassification.asin != 'B01LYQ7HHQ'
      ORDER BY
        productClassification.topic ASC
      , productClassification.weight ASC

    )
    AS
      productClassification_ordered
  )
   AS
     productClassification_ordered_ranked
  )
   AS
     productClassification_ordered_ranked_limited
 WHERE
   productClassification_ordered_ranked_limited.rank <= 3 -- limit 3 per topic id

Result

ASIN         topic         weight
----------  ------  ---------------
B01MATR1ZN       1    99.1049586790
B01M7MPM53       1    99.1240145475
B01KWK92UQ       1    99.5800318809


Using COLLATE with UNION How to use COLLATE with UNION? I want to union 2 tables (both have the same columns and the same types: varchar,int, int, decimal).

I got the following error:

  sg 468, Level 16, State 9, Line 1
  Cannot resolve the collation conflict between "Serbian_Latin_100_CI_AS" and "Croatian_CI_AS" in the UNION operation.

My SQL statement:

select * from #IA_BIH
union
select * from #IA_MNE

Where should I insert collate database_default? I tried different combinations, but it didn't work.

Based on the collation names I assume that you are using Microsoft SQL Server.

COLLATE can be used at the database level or the column level.   Since you are trying to UNION two tables, using the column collation on the needed columns will resolve your query.

Here is a sample bit of code to help you:

use testdb
GO
CREATE TABLE dbo.Serbian  (Name VARCHAR(20) COLLATE Serbian_Latin_100_CI_AS);
CREATE TABLE dbo.Croatian (Name VARCHAR(20) COLLATE  Croatian_CI_AS);
GO
INSERT INTO dbo.Serbian VALUES ('serbian');
INSERT INTO dbo.Croatian VALUES ('croation');
GO

-- Collate to a particular named collation
SELECT Name COLLATE Serbian_Latin_100_CI_AS as CollatedNameSerbian from dbo.Serbian
UNION ALL
SELECT Name COLLATE Serbian_Latin_100_CI_AS from dbo.Croatian
GO
-- Collate to the database default collation
SELECT Name  COLLATE database_default as CollatedNameDBDefault from dbo.Serbian
UNION ALL
SELECT Name COLLATE database_default from dbo.Croatian
GO

DROP TABLE dbo.Serbian;
DROP TABLE dbo.Croatian;
GO

Of course, if you have several columns with conflicting collations you will need to define their collations as well.


How to structure different types of a model in MySQL? I am building an application where users can apply for small business loans. There are 4 types of loans (A,B,C,D). There are 10 commons questions no matter which type they choose, but then based on which type of loan they want, there are 3-4 more questions specific for that type.

What is the best way to represent this in the database?

Thanks in advanced!

Consider having all questions in a single table.  Have a column that is datatype SET with (currently) 4 "bits":  'A','B','C','D'.  10 questions would have all 4 bits set; the others would have one bit each.

(There are certainly other ways to solve this.)


How can I update values based on the value from the previous day? I have a MySQL database temporary table with a bunch of meter readings in it against a system ID and a date (which combined make up the primary key).
Some entries have NULL for the meter reading, in which case they need to be updated to have the same meter reading as the previous day.
There is also a "diff" column which needs to have the difference between the reading for that day, and for the previous day.
I know how to update the tables using code, I am wondering if there is a good way to do it with SQL. Multiple queries is fine (I would guess that at a minimum the NULL readings would need to be updated before the diffs are calculated).
Here is an example schema with fake data, also in this sqlfiddle:
CREATE TABLE Readings (`SystemID` INT, `ReadingDate` DATE, `Reading` INT NULL, `Diff` INT NULL, PRIMARY KEY(`SystemID`,`ReadingDate`));

INSERT INTO Readings VALUES
(1, '2021-02-01', 24, NULL),
(1, '2021-02-02', 26, NULL),
(1, '2021-02-03', 23, NULL),
(1, '2021-02-04', NULL, NULL),
(1, '2021-02-05', NULL, NULL),
(1, '2021-02-06', 28, NULL),
(1, '2021-02-07', 21, NULL),
(2, '2021-02-01', 124, NULL),
(2, '2021-02-02', 126, NULL),
(2, '2021-02-03', 123, NULL),
(2, '2021-02-04', NULL, NULL),
(2, '2021-02-05', NULL, NULL),
(2, '2021-02-06', 128, NULL),
(2, '2021-02-07', 121, NULL);

After running the updates, the values would be (INSERT format kept to make it easier to see):
(1, '2021-02-01', 24, NULL),
(1, '2021-02-02', 26, 2),
(1, '2021-02-03', 23, -3),
(1, '2021-02-04', 23, 0),
(1, '2021-02-05', 23, 0),
(1, '2021-02-06', 28, 5),
(1, '2021-02-07', 21, -7),
(2, '2021-02-01', 124, NULL),
(2, '2021-02-02', 126, 2),
(2, '2021-02-03', 123, -3),
(2, '2021-02-04', 123, 0),
(2, '2021-02-05', 123, 0),
(2, '2021-02-06', 128, 5),
(2, '2021-02-07', 121, -7);

EDIT: To further complicate things, this is a temporary table in the real application.

If you are on MySQL >= 8.0
You can take advantage of LATERAL derived tables.
SELECT
    Act.SystemID,
    Act.ReadingDate,
    Act.Reading,
    Act.Diff,
    Prev.Reading as PrevReading
FROM
    Readings AS Act,
LATERAL
    (SELECT   Reading
     FROM     Readings
     WHERE    SystemID = Act.SystemID
              AND ReadingDate < Act.ReadingDate
              AND Reading IS NOT NULL
     ORDER BY SystemID, ReadingDate DESC
     LIMIT 1) Prev

ORDER BY
    Act.SystemID, Act.ReadingDate;

In this case it returns the previous reading that is not null.

SystemID | ReadingDate | Reading | Diff | PrevReading
-------: | :---------- | ------: | ---: | ----------:
       1 | 2021-02-02  |      26 | null |          24
       1 | 2021-02-03  |      23 | null |          26
       1 | 2021-02-04  |    null | null |          23
       1 | 2021-02-05  |    null | null |          23
       1 | 2021-02-06  |      28 | null |          23
       1 | 2021-02-07  |      21 | null |          28
       2 | 2021-02-02  |     126 | null |         124
       2 | 2021-02-03  |     123 | null |         126
       2 | 2021-02-04  |    null | null |         123
       2 | 2021-02-05  |    null | null |         123
       2 | 2021-02-06  |     128 | null |         123
       2 | 2021-02-07  |     121 | null |         128

Now you can use this syntax to update all your rows in a single query:
UPDATE
    Readings Act,
    LATERAL
    (SELECT   Reading
     FROM     Readings
     WHERE    SystemID = Act.SystemID
              AND ReadingDate < Act.ReadingDate
              AND Reading IS NOT NULL
     ORDER BY SystemID, ReadingDate DESC
     LIMIT 1) Prev
SET
    Act.Reading = IF(Act.Reading IS NULL, Prev.Reading, Act.Reading),

    Act.Diff = CASE
                   WHEN Act.Reading IS NULL THEN 0
                   ELSE Act.Reading - Prev.Reading
               END;

SELECT * FROM Readings ORDER BY SystemID, ReadingDate;

And that is the result:

SystemID | ReadingDate | Reading | Diff
-------: | :---------- | ------: | ---:
       1 | 2021-02-01  |      24 | null
       1 | 2021-02-02  |      26 |    2
       1 | 2021-02-03  |      23 |   -3
       1 | 2021-02-04  |      23 |    0
       1 | 2021-02-05  |      23 |    0
       1 | 2021-02-06  |      28 |    5
       1 | 2021-02-07  |      21 |   -7
       2 | 2021-02-01  |     124 | null
       2 | 2021-02-02  |     126 |    2
       2 | 2021-02-03  |     123 |   -3
       2 | 2021-02-04  |     123 |    0
       2 | 2021-02-05  |     123 |    0
       2 | 2021-02-06  |     128 |    5
       2 | 2021-02-07  |     121 |   -7

db<>fiddle here
If you are on MySQL 5.7
You can create a function that returns the corresponding previous Reading:
CREATE FUNCTION PrevReading(sID int, rDate date)
RETURNS INT

BEGIN

   DECLARE val INT;

   SET val := (SELECT Reading
               FROM   Readings
               WHERE  SystemID = sID
                      AND ReadingDate < rDate
                      AND Reading IS NOT NULL
               ORDER BY
                      SystemID, ReadingDate DESC
               LIMIT 1);

   RETURN val;

END;

And then update your table:
UPDATE
    Readings
SET
    Reading = IF(Reading IS NULL, PrevReading(SystemID, ReadingDate), Reading),
    Diff =    IF(Reading IS NULL, 0, Reading - PrevReading(SystemID, ReadingDate))

db<>fiddle here


Storing a 3D point array representing a flight path? I need to store an array of 3D points in a column: latitude, longitude, altitude

What datatype would be required for this?

Also, if I wanted to extend this in the future to include time (ie an array of 4D points), how could I do so?

I have gone for a custom datatype, but I am not sure if this is wise:

CREATE TYPE position3d AS (f1 DOUBLE PRECISION, f2 DOUBLE PRECISION, f3 DOUBLE PRECISION)

I then created a new column with type position3d[]

This is clearly geospatial. My advice to you is to install PostGIS. Store them as a point. Points can have z-cordinates. Creating a custom type is a bad idea.

CREATE TABLE foo (
  geog geography(POINT),
  alt  int
);

INSERT INTO foo(geog,alt) VALUES
  (ST_MakePoint(long,lat), alt);

If you want to extend to include time, just add a timestamp column.

I'm not sure what you mean when you say "an array of 3D points", but typically that either means you need to normalize into a point table, or store a MULTIPOINT, or by array you mean LINESTRING.

Flight path

If you're trying to store a flight path you want a LINESTRING you can create one with ST_MakeLine.

CREATE TABLE foo (
  id   serial                  PRIMARY KEY,
  path geography(LINESTRINGZ)
);
INSERT INTO foo(path) VALUES
  (
    ST_MakeLine(ARRAY[
      ST_MakePoint(1,2,3),
      ST_MakePoint(5,5,4),
      ST_MakePoint(10,10,5)
    ])
  );

# SELECT id, ST_AsText(path) FROM foo;
 id |             st_astext
----+------------------------------------
  1 | LINESTRING Z (1 2 3,5 5 4,10 10 5)
(1 row)


List certain user ids and drop them in Oracle How do I list certain user ids and drop them?

For instance, I want to list user ids directly connected to database, in Oracle, and not any functional id.

I need to write a script for the automation

I want to write a script to list such direct ids and drop them automaticaly.

How can I do it?

To select a list if users connected to the database:

select distinct USERNAME
from v$session;

Note that some will be internal Oracle sessions.

To drop a user in Oracle, you use the DROP USER command:

DROP USER USERNAME;

Scripting it is easy, you can run in a loop and use EXECUTE IMMEDIATE, or spool a query from SQL*Plus to create the script for you:

  select 'drop user '||USERNAME||';'
  from v$session
  where username not in (... LIST OF USERS YOU DONT WANT TO DROP ...);

I do wonder why on earth you'd want to automate this though. Seems like a very bizarre business case.


What transaction logs are for I'm confused about tranasaction logs for SQL Server. I was under the impression they can be used to roll the database forward. For example say I made a software release on Sunday, I added three new columns. But four days down the line we discover a bug, could I roll the database back to the Sunday's backup before I did the release and then use the transaction logs to insert any data that's been inserted since Sunday?

Yes, you can do what you're asking.  You should have your database in full recovery mode and then be performing log backups (via jobs/maintenance plans).

https://technet.microsoft.com/en-us/library/ms190982(v=sql.90).aspx


What's MongoDB's default behaviour when no sharded collection is specified When we deploy a Sharded Cluster, without having a sharded collection in our database, what is the default behaviour of MongoDB? How does MongoDB, distributes the data across the shards?

The answer is for existing data NO. But when you create new DB, different databases have different primary shard (system use round robin selection criteria), but as long as the collection (at that DB) is not sharded, all data stays at that primary shard.