DVWA SQLi: How to get column names from table “users” only?

This query select column_name from database information_schema, specifically in table columns where the database name is the one selected by the programmer which is also similar to function database()

UNION SELECT GROUP_CONCAT(column_name),2 FROM information_schema.columns WHERE table_schema=DATABASE()

How it looks like on MySQL shell. Only column column_name in database information_schema, column TABLE_SCHEMA where did you get i from function DATABASE() will appear.

However, you did not see column TABLE_SCHEMA as you did not select it on the query.

mysql> SELECT column_name FROM information_schema.columns WHERE table_schema=DATABASE();
+-------------+
| column_name |
+-------------+
| comment_id  |
| comment     |
| name        |
| user_id     |
| first_name  |
| last_name   |
| user        |
| password    |
| avatar      |
+-------------+
9 rows in set (0.01 sec)

mysql>

This is the whole column in database information_schema, table columns WHERE table_name='users'. You should get the idea by now.

mysql> SELECT * FROM information_schema.columns WHERE table_name='users';
+---------------+--------------+------------+-------------+------------------+----------------+-------------+-----------+--------------------------+------------------------+-------------------+---------------+--------------------+-------------------+-------------+------------+----------------+---------------------------------+----------------+
| TABLE_CATALOG | TABLE_SCHEMA | TABLE_NAME | COLUMN_NAME | ORDINAL_POSITION | COLUMN_DEFAULT | IS_NULLABLE | DATA_TYPE | CHARACTER_MAXIMUM_LENGTH | CHARACTER_OCTET_LENGTH | NUMERIC_PRECISION | NUMERIC_SCALE | CHARACTER_SET_NAME | COLLATION_NAME    | COLUMN_TYPE | COLUMN_KEY | EXTRA          | PRIVILEGES                      | COLUMN_COMMENT |
+---------------+--------------+------------+-------------+------------------+----------------+-------------+-----------+--------------------------+------------------------+-------------------+---------------+--------------------+-------------------+-------------+------------+----------------+---------------------------------+----------------+
| NULL          | dvwa         | users      | user_id     |                1 | 0              | NO          | int       |                     NULL |                   NULL |                10 |             0 | NULL               | NULL              | int(6)      | PRI        |                | select,insert,update,references |                |
| NULL          | dvwa         | users      | first_name  |                2 | NULL           | YES         | varchar   |                       15 |                     15 |              NULL |          NULL | latin1             | latin1_swedish_ci | varchar(15) |            |                | select,insert,update,references |                |
| NULL          | dvwa         | users      | last_name   |                3 | NULL           | YES         | varchar   |                       15 |                     15 |              NULL |          NULL | latin1             | latin1_swedish_ci | varchar(15) |            |                | select,insert,update,references |                |
| NULL          | dvwa         | users      | user        |                4 | NULL           | YES         | varchar   |                       15 |                     15 |              NULL |          NULL | latin1             | latin1_swedish_ci | varchar(15) |            |                | select,insert,update,references |                |
| NULL          | dvwa         | users      | password    |                5 | NULL           | YES         | varchar   |                       32 |                     32 |              NULL |          NULL | latin1             | latin1_swedish_ci | varchar(32) |            |                | select,insert,update,references |                |
| NULL          | dvwa         | users      | avatar      |                6 | NULL           | YES         | varchar   |                       70 |                     70 |              NULL |          NULL | latin1             | latin1_swedish_ci | varchar(70) |            |                | select,insert,update,references |                |
+---------------+--------------+------------+-------------+------------------+----------------+-------------+-----------+--------------------------+------------------------+-------------------+---------------+--------------------+-------------------+-------------+------------+----------------+---------------------------------+----------------+
9 rows in set (0.01 sec)

mysql>

Look at columns such as TABLE_SCHEMA and COLUMN_NAME. These are the one you used in the previous query.

Also, there is TABLE_NAME in it so you can actually limit the output with where command.

To make it easier to understand, let's modify the SQL query again.

mysql> SELECT table_schema,table_name,column_name FROM information_schema.columns WHERE table_schema=DATABASE();
+--------------+------------+-------------+
| table_schema | table_name | column_name |
+--------------+------------+-------------+
| dvwa         | guestbook  | comment_id  |
| dvwa         | guestbook  | comment     |
| dvwa         | guestbook  | name        |
| dvwa         | users      | user_id     |
| dvwa         | users      | first_name  |
| dvwa         | users      | last_name   |
| dvwa         | users      | user        |
| dvwa         | users      | password    |
| dvwa         | users      | avatar      |
+--------------+------------+-------------+
9 rows in set (0.01 sec)

mysql>

By now, you should understand why your query produced something out of users table.

Instead of querying the column_name from the current database (WHERE table_schema=DATABASE()), you should get it from the table you wanted which is users in this case.

mysql> SELECT column_name FROM information_schema.columns WHERE table_name='users';
+-------------+
| column_name |
+-------------+
| user_id     |
| first_name  |
| last_name   |
| user        |
| password    |
| avatar      |
| id          |
| username    |
| password    |
+-------------+
9 rows in set (0.01 sec)

mysql>

So the query from the web should looks like this

UNION SELECT GROUP_CONCAT(column_name),2 FROM information_schema.columns WHERE table_name='users'


This sql select must be optimized...how?

Why on earth all those inner queries?

Transform all (I just give example)

SELECT DISTINCT
    SM.sales_date,
    (SELECT
            SUM(gross_sale)
        FROM
            sales_main AS INTERNAL
        WHERE
            INTERNAL.sales_date = SM.sales_date AND INTERNAL.vehicle_name=SM.vehicle_name) AS TGROSS,
    (SELECT
            SUM(net_sale)
        FROM
            sales_main AS INTERNAL
        WHERE
            INTERNAL.sales_date = SM.sales_date AND INTERNAL.vehicle_name=SM.vehicle_name) AS TNET,

To:

SELECT
       sales_date,
       SUM(gross_sale) AS TGROSS,
       SUM(net_sale) AS TNET
FROM
            sales_main
        WHERE
            sales_date BETWEEN X AND Y
         AND
             vehicle_name=vehicle_name
GROUP BY YEAR(sales_date), MONTH(sales_date), DAY(sales_date)

Once you have that working, we can further optimize it.

The GROUP BY can also be just GROUP BY sales_date if sales_date is of DATE type, and DATE(sales_date) if it is DATETIME type.
I used that group by for the WITH ROLLUP summary of groups I am used to do in my reporting service...


pg_restore fails with drop default on identity column

That looks like an unfortunate coincidence of --clean and an existing table.

The dump contains a column default definition, so --clean tries to remove the default before restoring it. This does not work for identity columns.

It would be interesting to know how the corresponding column in the database you dumped is defined. Does it have a DEFAULT value?

One could argue that “dropping the default” should include getting rid of identity columns; if you feel that way, you could take up that complaint with the PostgreSQL hackers mailing list. It should be possible to modify pg_restore accordingly, but I wonder if it is worth the effort.


Difficulty with Postgis extension on Ubuntu 14.04

But that is 9.1. - Is pgadmin connected to the wrong postgresql?

Yes.

It appears that you're connected to your 9.1 server, suggesting that 9.1 is still running.

Do pg_lsclusters to see what PostgreSQL installs you have and their status.

Each runs on a different port. If you want to connect to 9.3 you need to check what port it runs on and connect PgAdmin to that port.


Use NOLOCK hint when calling table valued function

You cannot use a WITH (NOLOCK) on a Table-Valued Function, unless you put it on every single table in the code inside the function.

Your best bet would be, like you said, to SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED.

To change this back to the default, you need to find out what isolation level is currently set (before changing it above).

This can be done by running DBCC USEROPTIONS and checking the value for isolation level.

The default tends to be READ COMMITTED, so to change it to that, you'll want to write a statement like the following:

SET TRANSACTION ISOLATION LEVEL READ COMMITTED


Syncronize mysql databases between local and hosted servers automatically

use replication master=> slave ? be carefull of conflict


SP2-0768 Illegal SPOOL command

  Cause: An invalid option was used in the SPOOL command.
  Action: Check the syntax of the SPOOL command for the correct options.

  SP2-0769 Usage: SPOOL { <file> | OFF | OUT }

  where <file> is file_name[.ext] [CRE[ATE]|REP[LACE]|APP[END]]
  Cause: Incorrect syntax for the SPOOL command was entered.
  Action: Check the syntax of the SPOOL command for the correct usage.

Exact same command that you have shown in the question is working fine for me as shown below.
OS: Oracle Linux 6
Oracle Version: 11.2.0.4.0

[oracle@testsrv Desktop]$ sqlplus / as sysdba

SQL> SPOOL EXPLAIN_PLAN_1.txt
SQL> set heading off
SQL> set echo off
SQL> set pages 999
SQL> set long 90000
SQL> explain plan for select * from hr.employees;

Explained.

SQL> select plan_table_output from
table(dbms_xplan.display('plan_table',null,'typical'));  2

Plan hash value: 1445457117

-------------------------------------------------------------------------------
| Id  | Operation     | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |       |   107 |  7383 |     3   (0)| 00:00:01 |
|   1 |  TABLE ACCESS FULL| EMPLOYEES |   107 |  7383 |     3   (0)| 00:00:01 |
-------------------------------------------------------------------------------

8 rows selected.

SQL> SPOOL OFF;

[oracle@testsrv Desktop]$ cat EXPLAIN_PLAN_1.txt
SQL>
SQL> explain plan for select * from hr.employees;

Explained.

SQL> select plan_table_output from
  2  table(dbms_xplan.display('plan_table',null,'typical'));

Plan hash value: 1445457117

-------------------------------------------------------------------------------
| Id  | Operation         | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |           |   107 |  7383 |     3   (0)| 00:00:01 |
|   1 |  TABLE ACCESS FULL| EMPLOYEES |   107 |  7383 |     3   (0)| 00:00:01 |
-------------------------------------------------------------------------------

8 rows selected.

SQL> SPOOL OFF;
[oracle@testsrv Desktop]$


MySQL: My table size using a query does not match the IBD's file size on disk

Of course, it never does.
In your case, you have about 66.67% fragmentation
I have wickedly good script you can run to calculate the fragmentation based on the difference between the .ibd filesize and the (data_length+index_length) of the any table:
MYSQL_AUTH="-h... -u... -p..."

DATADIR=`mysql -ANe"SELECT @@global.datadir"`

TAG_LIST_FILE=/tmp/list_of_tables.txt
TAG_LIST_ERRS=/tmp/list_of_tables.err
DAT_LIST_FILE=/tmp/list_of_databases.txt
FULL_RPT_FILE=Full_FragReport.txt

MB=${1}

if [ "${MB}" == "" ] ; then exit ; fi

NUM_OK=0
if [ "${MB}" ==  "128" ] ; then NUM_OK=1 ; fi
if [ "${MB}" ==  "256" ] ; then NUM_OK=1 ; fi
if [ "${MB}" ==  "512" ] ; then NUM_OK=1 ; fi
if [ "${MB}" == "1024" ] ; then NUM_OK=1 ; fi
if [ "${MB}" == "2048" ] ; then NUM_OK=1 ; fi
if [ ${NUM_OK} -eq 0 ] ; then exit ; fi

MINIMUM_SIZE="(1024 * 1024 * ${MB})"
(( MIN_SIZE = MB * 1048576 ))

SQL="SELECT CONCAT(table_schema,':',table_name,':',data_length+index_length,':',data_free) FROM information_schema.tables"
SQL="${SQL} WHERE table_schema NOT IN ('information_schema','performance_schema','mysql') AND engine IN ('InnoDB','MyISAM')"
SQL="${SQL} AND data_length+index_length > ${MIN_SIZE}"
mysql ${MYSQL_AUTH} -ANe"${SQL}" >${TAG_LIST_FILE} 2>${TAG_LIST_ERRS}
TAG_FILESIZE=`ls -l ${TAG_LIST_FILE} | awk '{print $5}'`
ERR_FILESIZE=`ls -l ${TAG_LIST_ERRS} | awk '{print $5}'`
if [ ${ERR_FILESIZE} -gt 0 ]
then
        echo "Errors"
        echo "======"
        cat ${ERR_FILESIZE}
        echo
        exit
fi
if [ ${TAG_FILESIZE} -eq 0 ]
then
        echo "No Databases with Tables > ${MB}M (${MIN_SIZE})"
        exit
fi

SPC=""
TAGS=""
for TAG in `cat ${TAG_LIST_FILE}`
do
        NEWTAG=${TAG}
        DB=`echo ${TAG} | sed 's/:/ /g' | awk '{print $1}'`
        TB=`echo ${TAG} | sed 's/:/ /g' | awk '{print $2}'`
        IS=`echo ${TAG} | sed 's/:/ /g' | awk '{print $3}'`
        FR=`echo ${TAG} | sed 's/:/ /g' | awk '{print $4}'`
        IBD=${DATADIR}/${DB}/${TB}.ibd
        MYD=${DATADIR}/${DB}/${TB}.MYD
        MYI=${DATADIR}/${DB}/${TB}.MYI
        if [ -f ${IBD} ]
        then
                (( N1 = IS + FR ))
                N2=`ls -l ${IBD} | awk '{print $5}'`
                if [ ${N1} -lt ${N2} ]
                then
                        (( DF = N2 - N1 ))
                        NEWTAG="${DB}:${TB}:${IS}:${DF}"
                fi
        else
                ND=`ls -l ${MYD} | awk '{print $5}'`
                NI=`ls -l ${MYI} | awk '{print $5}'`
                (( N1 = IS + FR ))
                (( N2 = ND + NI ))
                if [ ${N1} -lt ${N2} ]
                then
                        (( DF = N2 - N1 ))
                        NEWTAG="${DB}:${TB}:${IS}:${DF}"
                fi
        fi
        TAGS="${TAGS}${SPC}${NEWTAG}"
        SPC=" "
done

# Collect List of DBs
DBLIST=""
SPC=""
for DB in `cat ${TAG_LIST_FILE} | sed 's/:/ /' | awk '{print $1}' | sort -u`
do
        DBLIST="${DBLIST}${SPC}${DB}"
        SPC=" "
done

for D2 in ${DBLIST}
do
        SUM=0
        SUM_DF=0
        SQL=""
        SELECT="SELECT"
        for TAG in ${TAGS}
        do
                DB=`echo ${TAG} | sed 's/:/ /g' | awk '{print $1}'`
                if [ "${DB}" == "${D2}" ]
                then
                        TB=`echo ${TAG} | sed 's/:/ /g' | awk '{print $2}'`
                        IS=`echo ${TAG} | sed 's/:/ /g' | awk '{print $3}'`
                        FR=`echo ${TAG} | sed 's/:/ /g' | awk '{print $4}'`
                        (( OS = IS + FR ))
                        SQL="${SQL} ${SELECT} '${TB}' TableName,LPAD(FORMAT(${OS},0),18,' ') FileSize,LPAD(FORMAT(${OS}/POWER(1024,3),2),10,' ') FileSizeGB,LPAD(FORMAT(${IS},0),18,' ') TableSize,LPAD(FORMAT(${IS}/POWER(1024,3),2),11,' ') TableSizeGB,LPAD(FORMAT(${FR},0),18,' ') Difference,LPAD(FORMAT(${FR}/POWER(1024,3),2),12,' ') DifferenceGB,LPAD(FORMAT(${FR}*100.0/${OS},2),7,' ') FragPct"
                        SELECT="UNION SELECT"
                        (( SUM    += OS ))
                        (( SUM_DF += FR ))
                fi
        done
        FRAG_RPT=FragReport_${D2}.txt
        DT=`date +"%Y-%m-%d %H:%M:%S"`
        echo "Fragmentation for Database ${D2} (${DT})" > ${FRAG_RPT}
        echo "Minimum Table Size (${MB} MB = ${MIN_SIZE})" >> ${FRAG_RPT}
        echo >> ${FRAG_RPT}
        BIGSQL="SELECT * FROM (${SQL}) A ORDER BY Difference DESC;"
        BIGSQL="${BIGSQL} SELECT FORMAT(${SUM}/POWER(1024,3),2) DiskSpaceUsed,FORMAT(${SUM_DF}/POWER(1024,3),2) SumDiff;"
        mysql ${MYSQL_AUTH} --table -Ae"${BIGSQL}" 2>&1 >> ${FRAG_RPT}
done

rm -f ${FULL_RPT_FILE}
for TXT in `ls FragReport*.txt`
do
        if [ -f ${FULL_RPT_FILE} ]
        then
                echo -e "\n================================================================================\n" >> ${FULL_RPT_FILE}
        fi
        cat ${TXT} >> ${FULL_RPT_FILE}
done
less ${FULL_RPT_FILE}

You will have too run this script as the OS root user or another OS that has rights to read the datadir
GIVE IT A TRY !!!
I wrote How to monitor mysql percentage disk utilization? like 6 years ago based on the same principles


Update table with values from another table runs really slow

Don't repeat the target table in the FROM clause:

UPDATE tab_A
   SET field1 = t2.field1, field2 = t2.field2, field3 = t2.field3
FROM tab_B t2
WHERE tab_A.uprn = t2.uprn;

Quote from the manual

  Note that the target table must not appear in the from_list, unless you intend a self-join

(emphasis mine)


SQL Server Logical IO Block Sizes and Physical Reads

if something is doing 400K logical IOs, how much memory is streamed across (IO block size * logical IOs)

In SQL Server, 1 "logical I/O" refers to reading 1 8 KB page from the buffer pool (memory).  It's possible that a physical I/O will be incurred first (to get the page from disk to memory).  So 400K logical reads is 400,000 * 8 KB = 3,200,000 KB of data being read (~3 GB).

A reference for this, with a lot more detailed information, can be found on the Microsoft Docs site: Pages and Extents Architecture Guide

You mention block size, which I imagine is in reference to the OS level block size.  While this matters at the OS level, and can potentially impact SQL Server performance, it's not relevant to logical I/O in terms of the number of pages - which is fixed at 8KB.

  Also, I am trying to understand how to take the Report on top Logical IO SQL statements and get the physical IO that these incurred

You can find this in sys.dm_exec_query_stats.  This includes columns for:

max_logical_reads,
total_logical_reads,
last_logical_reads,
max_physical_reads,
total_physical_reads,
last_physical_reads

...etc.


How should I handle measurement errors in a timeseries database?

I would keep the data, if at any point in the future you want to find out what was wrong with it you still have it.

It can provide you with some information later on about how often measurements fail, where they originate from, what the offset is and things like that.

Deleting the data is unrecoverable, handling them in some way can always be changed later on. For example you could handle them in your client application for now and if you have some issue with that store them physically in a table_cleaned table.

If you deleted them or replaced them with NULL the information is gone for good.


Settings for a MySQL backup only slave instance

Just a suggestion, but maybe you could look at Percona's hot backup solution here? It's free and open source. It allows for the hot backing up of InnoDB tables. HTH, Paul...


We have around 500 schemas, so granting permission to each schema is difficult.

No.  It's tedious to do by hand, but it's also a prime candidate for automation, through scripting.

  ... dynamically created or dropped on a daily basis, so giving permission at each time a schema is created is also a difficult task.

I disagree.  You're doing all the "difficult" stuff every day - dropping, recreating and repopulating all these schemas.  I would suggest that adding this single statement to the end of that process is relatively trivial!

  Is there any way to give the read-only permission for a whole database instead of schema?

Even if there is, you should not do so.

It sounds to me like these schemas are discrete entities (otherwise why not combine them into one?).  Granting access across all of them strikes me as a Bad Idea.


MySQL profile on query "Creating Sort Index" using 75% of the total time

We had similar problem for huge queries. Often queries ran for hours (upto 7-8) depending upon the load on DB for 400M rows. However, our goal was to achieve group results, such as select col1, col2, col3, count(1), count(distinct col4) from table group by 1,2,3.

Underlying problem is same as yours though, as in both case DB sorts(orders) the results internally.

How creating sort index work.
On mysql website it says "The thread is processing a SELECT that is resolved using an internal temporary table." Per my algorithm understanding, system is most likely splitting data into chunks, read from disk this chunks one by one,  sorting individual chunks, putting back into some temporary disk space and so on. System does this for all chunks and eventually performs merge sort. This involves extensive read/writes.

A possible solution can be to increase your memory for DB (so that it can create bigger chunks that can stay in memory) or if you have a bigger memory somewhere else, you can program the solution by streaming from DB. This can be achieved in nlogn time.

Programmatically, I could reduce time from on an average of 2 hrs to a consistent 7.5 minutes.


Can I build relationships in existing system without breaking it?

I would not make modifications like adding primary/unique or foreign key constraints to a legacy database that you haven't built yourself. Chances are that the original developer, given the problems and design issues you mention, may have built logic that breaks with such constraints.

For instance, if the app uses a 0 in a key column instead of NULL to represent "nothing", a foreign key constraint would fail that UPDATE/INSERT, even if the value is just used temporarily in a procedure.

The best place to start is probably to identify specific queries that show performance issues or cause locking, and deal with those. Just adding proper indexes on strategic tables may prove a huge improvement for you.

As for cursors.. some devs just don't know better. :)


SQLServer Row level security - Enforce from within database

I think you should use a login trigger and SET CONTEXT_INFO which quoting BoL

  Associates up to 128 bytes of binary information with the current session or connection.

Then you can retrieve the data by calling the function with a select

i.e. SELECT CONTEXT_INFO()

You can then use the data recalled from the connection with Row Level Security to implement the filtering you desire.

If you are running SQL Server 2016 or later then you should use the more modern session_context() function instead - see Phase out CONTEXT_INFO() in SQL Server 2016 with SESSION_CONTEXT() by Aaron Bertrand.


What server configuration and database should I use?

This site, is a question and answer site, not a mentoring site.

You are already taking a Database Design class, which is much closer to the mentoring that you are seeking.  Also, you must have instruction materials that will explain at least the basics.

Questions are welcome, but you will get better results by:

Working on the problem yourself first.
Describe briefly your overall project.
Focus on one or two points, not a sweeping discussion of your project.
Clearly describing the problem that you are currently trying to resolve and what you have already tried.

General questions about databases, costs, et cetera are things that you need to study up on in advance. You must know the basics or your questions will get little useful response.

Search DBA Stackexchange and other sites to see what answers are already available to you.  You often just need to search.

Specific responses:

SQL Server Express 2014 can be a fine, fully featured database server.  It does not have all the auxiliary tools that the more expensive editions.  There are limitations: only uses one CPU, no single database can be bigger than 10 GB, the use of memory is limited, and so forth.
It is better to keep the domain controller and the SQL Server on separate machines, even if virtual, because of the increased exposure to security problems.

Your goal to create a simple system for a small group of users is a fine place to start.  You will learn a lot in the process that will help prepare you for bigger tasks.


Package mariadb-server-5.5 is not configured yet

It seems to be the result of a muddled setup between installing mariadb and mysql. Rebooting the machine and running apt-get again solved the issue for me.


How can it take longer to replay a captured trace than to capture it

SQL Server can handle multiple concurrent requests simultaneously from multiple machines and threads. Unfortunately, replay can only spit them out one at a time, serially, from a single machine - introducing multiple layers of bottlenecks. There is probably also some overhead in replay to read and prepare the statements to send, that doesn't exist in your application.

If you want the replay to be more realistic, you should look into SQL Server 2012's Distributed Replay, which is able to distribute the replay across multiple machines. That said, you're not likely to ever match the original load time perfectly, and there is likely still some inherent overhead in the preparation of each statement during replay, even if you are able to eliminate some of the bottlenecks on concurrency.


"Lock request time out period exceeded" when publishing SqlServer databases in parallel

Looking at the MSDN documentation for sp_fulltext_database we see the following note:

  Has no effect on full-text catalogs in SQL Server 2008 and later versions and is supported for backward compatibility only. sp_fulltext_database does not disable the Full-Text Engine for a given database. All user-created databases in SQL Server 20xx are always enabled for full-text indexing.

The "xx" in the "20xx" above changes based on what version of the documentation you are looking at, but starting with SQL Server 2008, that xx will be: "08", "12", or "16".

You are on SQL Server 2014, so I am questioning why EXECUTE sp_fulltext_database 'disable'; is even showing up in your SSDT-generated deploy scripts. I just did some testing and it seems that no matter what the project's "Target platform" is set to, the deploy script always generates lines for:

IF fulltextserviceproperty(N'IsFulltextInstalled') = 1
    EXECUTE sp_fulltext_database 'enable';

The "enable" or "disable" is controlled by the "Database Setting..." on the "Project Settings" tab of Project Properties, in the "Miscellaneous" tab.

The only way to get rid of these 2 lines is to uncheck the "Deploy database properties" option, under "Deployment Options" on the "Debug" tab of Project Properties. But if you uncheck that option, then it won't enforce any of the database properties. So:

If you are not using the SSDT deployment to enforce the database properties then go ahead and uncheck this option. If you haven't made any other changes since the last build, this change alone will not update the deploy script, in which case you need to do a "rebuild".
If you are using the SSDT deployment to enforce the database properties, then either remove those two lines manually or find a way to do it programmatically.

I don't know why there is a call to an unused system stored procedure outside of it probably being a low priority to remove since it doesn't break anything and most people aren't doing parallel deployments ;-).


Shouldn't Windows Server Failover Clustering already take care of SQLServer Failover Clustering?

Let me start by saying there is no "AlwaysON", it was marketing term used by Microsoft to make sure Availability groups technology gets good amount of "talk". They have succeeded and this terminology/technology is hit. It is not called "alwayson availability groups" or "always on" but simply Availability Groups..

MS has confused the terms "Failover cluster" with "AlwaysON failover cluster". In books online they both mean the same but let me point out fine difference. When you deploy SQL Server failover cluster instance (FCI) on Windows Server failover cluster (WSFC), this is called failover clustering, while when you have this but database on failover cluster instance (FCI) is participating in Availability groups (AG) then it is called AlwaysOn failover clustering. I do not prefer using alwayson failover clustering for FCI, simply because AG can be configured without FCI.

  Windows Server Failover Clustering is installed, why would it not allow failover of SQL server?

As matter of fact it is WSFC which control failover not the FCI or AG.

  What would be the effects of having Windows Server Failover Clustering, without SQL AlwaysOn FailoverClustering? Meaning in case of failover, would the second Sql server node would not operate?

Nothing, Availability groups can be configured without FCI on a simple standalone nodes which are part of WSFC. The failover will be controlled by WSFC and after failover the other node becomes primary and the old one becomes new secondary.

  Does this mean AlwaysOn Failover Clustering is an additional Installation package which needs to be installed, because Windows Server Failover Clustering did not do its job correctly?

No. Everything works because of WSFC it is the base on which clustering technology is based. FCI and AG are different things: They both are installed "upon" WSFC. Although starting from SQl Server 2017 and Windows Server 2016 you can have Cluster Less Availability groups.

  Does this mean FCI is an additional Installation package which needs to be installed, because Windows Server Failover Clustering (WSFC) did not do its job correctly?

Yes you are correct. To create FCI you need to run SQL Server installation and install SQL Server in cluster aware mode. FCI is used to provide failovers to databases which in turn leverages WSFC feature for failover. It's not SQL Server which does failover, it's WSFC SQL Server which just brings instance online on other node. So saying that WSFC does not do its job is wrong. Like I said WSFC is the base on which FCI and AG run. SQL Server FCI without WSFC cannot do failover.


Is there a parser for Postgres' auto_explain to make it easier to read and understand?

SUGGESTION #1
If you are just looking to take an EXPLAIN plan and instantly present it, here is a SQL-Fiddle like site called explain.depesz.com. Just copy-and-paste the explain and hit Submit.
SUGGESTION #2
You could try pgAdminIII which comes with Graphical Explains
See the following links to how to use pgAdmin and other methods

PostgreSQL : Up and Running (Chapter ob pgAdmin)
PostgreSQL Wiki's Using EXPLAIN
READING PGADMIN GRAPHICAL EXPLAIN PLANS
pgAdmin 1.4 online documentation


Need separate columns that calculate percentage

Possible with a single SELECT:

SELECT name, count(*), to_char((count(*) * 100.0
                          / sum(count(*)) OVER ()), 'FM990.00" %"') AS percent
FROM   t
GROUP  BY 1
ORDER  BY 1;

count(*) is a separate form of the function  and slightly faster than count(<expression>). Assuming all columns to be NOT NULL, else you may have to use the latter.

You can run the window aggregate function sum(count(*)) OVER () to get the total in the same SELECT. Consider the sequence of event in a SELECT:

How will the order of operations in this query affect my results?

This uses one sequential scan instead of two and is typically twice as fast as using a subquery for the total count.

to_char() prettifies the output:

name  count  percent
-----------------------
Alex  2      33.33 %
Bob   2      33.33 %
Roger 1      16.67 %
Tim   1      16.67 %


I found this 'running totals' CTE example here (you should be able to adapt it to your needs). Note the MAXRECURSION
https://stackoverflow.com/questions/11310877/calculate-running-total-running-balance

DECLARE @Tbl TABLE (RowNum INT,Columnx INT)

INSERT INTO @Tbl (RowNum,Columnx)
VALUES (1,100)

INSERT INTO @Tbl (RowNum,Columnx)
VALUES (2,200)

INSERT INTO @Tbl (RowNum,Columnx)
VALUES (3,300)
;
WITH x
AS (
    SELECT RowNum
        ,Columnx
        ,RunningTotal = Columnx
    FROM @tbl
    WHERE RowNum = 1

    UNION ALL

    SELECT y.RowNum
        ,y.Columnx
        ,x.RunningTotal + y.Columnx
    FROM x
    INNER JOIN @tbl AS y ON y.RowNum = x.RowNum + 1
    )
SELECT RowNum
    ,columnx
    ,RunningTotal
FROM x
ORDER BY rownum
OPTION (MAXRECURSION 10000);


Suppose you set up AlwaysOn but something goes wrong with your partition switching process and you accidentally delete all of the data. With your current proposal you'll automatically delete all of the data from your archive database as well. So even with AlwaysOn you'll still need to take backups, right?

Now consider the state of your current database immediately after you perform the partition switching. That's what you want on the archive database, right? So if you still need to perform backups anyway why not skip AlwaysOn and just back up and restore that database immediately after the switching?

You might have other reasons for using AlwaysOn so I can cover a few pointers but I'm not really an expert. At a high level, SQL Server keeps the databases in sync by replaying the transaction log so the secondary database is a byte-for byte-copy. A decent approximation of how an operation will affect redo traffic can be found by looking at how much data is logged to the transaction log. Partition switching doesn't require data to be moved so very little information should be logged. This means that you should not expect it to cause issues with AlwaysOn traffic.

One problem that you might run into is that you can't pick and choose which tables or filegroups get synced. It must be the entire database. So you'll be sending the transaction log activity for the "large amount of inserts per day" and anything else that you do through redo to the secondary. Your active database will also continue to grow in size over time switch you can't partition switch across databases as Sean Gallardy pointed out. So right now if your active database is 100 GB and your archive database is 1 TB you'll need to move all of that data from the archive database to the active database if you want to put them in an availability group.


how to add attachment(text file) to database mail?

I was looking of for the same issue myself, and gave up. It seems that attachments can only be sent using TSQL, such as this

EXEC sp_send_dbmail
  @profile_name='MailProfile',
  @recipients='me@gmail.com',
  @subject='TestEmail',
  @body='I'm sending you this document...',
  @file_attachments='D:\TEST\file.doc'


Use CASE statement as following

SELECT
CASE WHEN ref_id = 'RIDERS' and subcod = 'DISABILITY' THEN   description ELSE NULL END AS DISABILITY,
CASE WHEN ref_id = 'RIDERS' and subcod = 'DISMEMBERMENT' THEN   description ELSE NULL END AS DISMEMBERMENT,
CASE WHEN ref_id = 'RIDERS' and subcod = 'MEDICAL' THEN   description ELSE NULL END AS MEDICAL,
CASE WHEN ref_id = 'RIDERS' and subcod = 'OTHERS' THEN   description ELSE NULL END AS OTHERS
FROM grp_appl_ref

If at any stage need to sort out records while putting NULL values at the end use CASE statement in ORDER BY clause

ORDER BY CASE columnName WHEN NULL THEN 1 ELSE 0 END


You can use a data modifying common table expression:
with deleted as (
  DELETE FROM some_table
  WHERE id = 42
  returning user_id
)
INSERT INTO other_table(user_id, "timestamp")
select user_id, timestamp '2020-01-04 21:32:34'
from deleted;


Enforcing "one-to-one-or-more" vs. "one-to-zero-or-more"

It seems to me that the second half of rule #2 is readily enforced with a foreign-key constraint, as you describe for rule #1.

But the first half of rule #2 seems hard to enforce, in part due to a chicken-vs-egg conflict:  Are you going to make 'em add a C before there's an A, so that when you add the A, it will have its (one) C?

But you can't:  that new C needs a valid pointer to an A, which doesn't exist yet, and so can't be added.


Special characters in Oracle utl_http request

The problem is length or lengthb functions treat these special characters as 1 byte but write_text function (correctly) considers these as 2 bytes. Hence a mal-formed XML is sent to the server.

To resolve this error please use the following instead of the length / lengthb function.

lv_clob_length_bytes := utl_raw.length(utl_raw.convert(utl_raw.cast_to_raw(lv_request_body),'american_america.al32utf8','ENGLISH_UNITED KINGDOM.WE8ISO8859P15'));

The above casts the XML to raw and then converts it from our characterset to UTF8 before finding its length.


Set simple recovery mode and shrink log files for all user created databases

Use Script to Shrink Log files of all databases other than the system DBs.

USE MASTER
GO
SET QUOTED_IDENTIFIER ON
GO
SET ARITHABORT ON
GO

DECLARE @DBName NVARCHAR(255),@LogicalFileName NVARCHAR(255),@DBRecoveryDesc Varchar(200)

DECLARE DatabaseList CURSOR
FOR
SELECT name,recovery_model_desc
FROM sys.databases
WHERE state_desc = 'ONLINE'
AND is_read_only = 0
and database_id>4
ORDER BY name

OPEN DatabaseList
FETCH NEXT FROM DatabaseList INTO @DBName,@DBRecoveryDesc
WHILE @@FETCH_STATUS = 0
BEGIN

SET @LogicalFileName=(SELECT top 1 name FROM sys.master_files AS mf WHERE DB_NAME(database_id)=@DBName and type_desc='LOG')

If @DBRecoveryDesc='Full'
Begin
     Print('Use ['+@DBName+']
            GO

           ALTER DATABASE ['+@DBName+'] SET RECOVERY SIMPLE WITH NO_WAIT
           GO

            DBCC SHRINKFILE ('''+@LogicalFileName+''',10)
            GO

            ALTER DATABASE ['+@DBName+'] SET RECOVERY FULL WITH  NO_WAIT
            GO ')
Print '----------------------------------------------------------- '
END

If @DBRecoveryDesc='Simple'
Begin
     Print('Use ['+@DBName+']
            GO

            DBCC SHRINKFILE ('''+@LogicalFileName+''',10)
            GO
 ')
Print '----------------------------------------------------------- '

END

         FETCH NEXT FROM DatabaseList INTO @DBName,@DBRecoveryDesc
      END
CLOSE DatabaseList
DEALLOCATE DatabaseList


SQL Server select columns which contain data

You can use this stored procedure to the trick
You need to provide the table name you wish to query
note that if you'll pass to procedure the @exec parameter = 1 it will execute the select query

  SET ANSI_NULLS ON
   GO
  SET QUOTED_IDENTIFIER ON
  GO
 CREATE PROCEDURE [dbo].[SP_SELECT_NON_NULL_COLUMNS] ( @tablename varchar    (100)=null, @exec int =0)
  AS BEGIN
  SET NOCOUNT ON
        IF @tablename IS NULL
              RAISERROR('CANT EXECUTE THE PROC, TABLE NAME IS MISSING',16 ,1)
                          ELSE
        BEGIN
              IF OBJECT_ID('tempdb..#table') IS NOT NULL DROP TABLE #table
              DECLARE @i VARCHAR (max)=''
              DECLARE @sentence VARCHAR (max)=''
              DECLARE @SELECT VARCHAR (max)
              DECLARE @LocalTableName VARCHAR(50) = '['+@tablename+']'
              CREATE TABLE  #table  (ColumnName VARCHAR (max))
              SELECT @i+=
              ' IF EXISTS ( SELECT TOP 1 '+column_name+' FROM '  +@LocalTableName+' WHERE ' +column_name+
                   ' '+'IS NOT NULL) INSERT INTO #table VALUES ('''+column_name+''');'
                    FROM INFORMATION_SCHEMA.COLUMNS WHERE table_name=@tablename
                    INSERT INTO #table
                    EXEC (@i)
                     SELECT @sentence = @sentence+' '+columnname+' ,' FROM #table
             DROP TABLE #table
                    IF @exec=0
                          BEGIN
                                SELECT 'SELECT '+ LTRIM (left (@sentence,NULLIF(LEN (@sentence)-1,-1)))+
                                            +' FROM ' +@LocalTableName
                    END
                    ELSE
                          BEGIN
                                SELECT @SELECT=  'SELECT '+ LTRIM (left (@sentence,NULLIF(LEN (@sentence)-1,-1)))+
                                                  +' FROM '+@LocalTableName
                        EXEC (@SELECT)
                    END
  END
  END


If I assign a NVL constraint to a nullable column will it display an error or it will retrieve the records?

So to answer your question.
"So will the command retrieve it normally or give an error?"
Yes it will work and won't return an error.

@update
You forgot your ; after the query.


Any reason for declaring a view with cross-database SELECTs for the same database?

There is no need to specify the database if the view is in the same database.
And the view will work even if it is referenced by a different database.

So no no good reason of specifying the database when you are in the same database.


How to run ALTER TABLE on a list of tables sequentially in a function

The problem is that you cannot use parameters as object names (tables, columns, etc.) in straight SQL statements, like your ALTER TABLE above.

I use 'straight' here as an opposite of 'dynamic' - the thing you need:

...
loop
    EXECUTE format($$ALTER TABLE %I SET SCHEMA TO data_archived$$,
                   table_rec.table_name);
END loop;
...

Notes:

here I am using dollar quoting when building the dynamic query.  This allows me to use 'normal' syntax, as opposed to multiplicating single quotes, for example (not present in this example).  This way most editors will highlight the statements nicely.
I also use format() with the %I format specifier to have the object name properly quoted if necessary.  This approach is far more readable than building the query with concatenation of string constants and some quote_ident() calls.  It is present in versions 9.1 and newer.
I have a feeling that you compare stuff like quote_ident('This_table') IN ('This_table'), which won't be true.  Either use quote_ident() on both sides - or it is easier to just omit quoting and compare the string values.


Short of restarting the SQL Server service or the box this is enough. If you want to be absolutely fresh you might want to drop user created statistics from your last run.

SELECT stats.name, objects.name, schemas.name,objects.create_date
FROM sys.stats
JOIN sys.objects ON stats.OBJECT_ID = objects.OBJECT_ID
JOIN sys.schemas ON objects.schema_id = schemas.schema_id
WHERE stats.stats_id > 0
  AND stats.stats_id < 255
  AND objects.is_ms_shipped = 0
  AND auto_created = 1
  order by objects.create_date desc


Specify file target filename dynamically

You need to create the event session in dynamic SQL (or build the whole command in C#).

DECLARE @file nvarchar(500), @path nvarchar(500), @db sysname = DB_NAME();

SET @file = RTRIM(N'D:\XE\ ') + @db;
SET @path = @file + N'day';

EXEC master.dbo.xp_create_subdir @file;

DECLARE @sql nvarchar(max) = N'CREATE EVENT SESSION ' + @db + N'_Session1
  ON SERVER
  ADD EVENT sqlserver.sql_statement_completed
  (
    ACTION (sqlserver.sql_text, sqlserver.tsql_stack, sqlserver.client_app_name,
            sqlserver.client_hostname, sqlserver.username
  ) WHERE ( sqlserver.database_name = N''' + @db + N''' ) ),
  ADD EVENT sqlserver.sql_statement_starting
  (
    ACTION (sqlserver.sql_text, sqlserver.tsql_stack, sqlserver.client_app_name,
            sqlserver.client_hostname, sqlserver.username
  ) WHERE ( sqlserver.database_name = N''' + @db + N''')
)
ADD target package0.event_file
(
  SET filename = N''' + @path + N'''
)
WITH (MAX_MEMORY=4096 KB,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,
      MAX_DISPATCH_LATENCY=30 SECONDS,MAX_EVENT_SIZE=0 KB,
      MEMORY_PARTITION_MODE=NONE,TRACK_CAUSALITY=OFF,STARTUP_STATE=OFF);';

EXEC master.sys.sp_executesql @sql;


MySQL permits setting a foreign key constraint that references only one column of a composite primary key

Quote from the documentation:

  InnoDB allows a foreign key constraint to reference a non-unique key. This is an InnoDB extension to standard SQL.

Although this may come as a surprise, it is "documented behaviour".  This is probably one of these cases where you/we must just accept that things have been implemented in a certain manner ie InnoDB have implemented foreign keys this way.

Whether the design (in question) makes sense or not depends on the underlying business rules.  There may well be situations that require such a constraint.


db_owner is supposed to have all permissions within given db, so you should be able to update that sequence without any problem - if your app really runs under your UserID

1) Check the connection string that app is using

If connection string does not contain Integrated Security = True or Trusted Connection = True, then you have to add those to connection string

2) if app is running from within some web server (IIS Web server etc.), and its included into some Application Pool, you need to make sure this Application Pool runs under security context of your Windows User...

3) to verify that your application is really connecting under your Windows credentials to SQL Server, while the app is connected to DB, check out following DMVs at SQL Server:

  select * from sys.dm_exec_sessions
  select * from sys.dm_exec_connections

(those can be linked by session_id, find session/connection of your application and make sure it runs under your Windows credentials (login_name "YourDomain\YourUserName")

4) if App is running and connecting to DB using your Windows creds, and you still have this permission issue, then you have to make sure your Windows user if really is in AD Group, and AD Group is really added to db_owner role at given database...


"current security context is not trusted" for job running as a SQL login accessing linked server

I fixed this by 1) removing the WITH EXECUTE AS 'user1' clause from the stored procedure and 2) removing user1 from the job -- it is no longer the owner or the "Run as user" of the job's step.

My final configuration is:

SQL Server Agent job owner is a sysadmin, not user1
The job's step "Run as user" is left blank
The step calls a stored procedure which user1 has rights to
execute
The linked server has a local/remote mapping for user1 with the
remote user and remote password entered. The username and password
are the same on both servers. The "For a login not defined in the
list above, connections will" option is set to "Be made using the
login's current security context".
user1 has the appropriate permissions to query on
the remote server.
user1 does not need to have the sysadmin role or any such administrative role


"Connection to host lost" using telnet to MySQL

What you're seeing with the telnet test looks like normal behavior, since mysql doesn't "speak telnet," so the result may not be all that helpful... all it really tells us is that a TCP connection to the remote machine is at least possible.

sqlbot@dev:~$ telnet 10.20.30.40 3306
Trying 10.20.30.40...
Connected to 10.20.30.40.
Escape character is '^]'.
N
5.5.15-log|     \Q_l"`>SÀh4e$~rA/b$'9mysql_native_passwordConnection closed by foreign host.

sqlbot@dev:~$

A better test would be to try the MySQL command line client -- from the same machine where you're getting the error from on workbench.  Whether that succeeds or fails will probably be more informative.

How long does it take for the message to appear?


How do you kick users out of a SQL Server 2008 database?

I always use the following:

USE master; -- get out of dbname myself
GO
-- kick all other users out:
ALTER DATABASE [dbname] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
GO
-- prevent sessions from re-establishing connection:
ALTER DATABASE [dbname] SET OFFLINE;

Sometimes this may take a while, and sometimes it is blocked because you're the one running it, and you have an active connection to the database. Check for other query windows that might have the same database context - this can include open dialogs, Object Explorer, IntelliSense, long-running jobs, etc.

When I'm done making my changes to that database's config, I simply:

ALTER DATABASE [dbname] SET ONLINE;
ALTER DATABASE [dbname] SET MULTI_USER;

Though, sometimes, the thing I need to do to that database requires the database to be online, so sometimes I have to leave it in single-user mode and do this:

ALTER DATABASE [dbname] SET ONLINE;
GO
USE [dbname];

Now I can make my changes, and then when I'm ready for other users to connect, simply:

ALTER DATABASE [dbname] SET MULTI_USER;


Does SQLCMD "!!" command work in Azure Data Studio

This is a known open issue with SQLCMD in ADS, where SQLCMD mode fails to parse some sqlcmd keywords. The issue is currently on the backlog.
ADS includes a terminal window (View-->Terminal) so  you can easily run OS commands on your client under the same query tab without SQLCMD.


How merge multi statement into one Select statement in sql

I Tried

You should include what you tried in the question - any detail you can give helps us help you understand.

  How to merge multi statement into one select statement.

You don't say what output you actually expect so I'm only guessing here. I suspect you want the rows from each of those four queries to be output in one resultset, in which case you need the UNION set operator, see https://docs.microsoft.com/en-us/sql/t-sql/language-elements/set-operators-union-transact-sql for details.

For a generic example:

SELECT <columns> FROM <tables_or_views>
UNION ALL
SELECT <columns> FROM <tables_or_views>
UNION ALL
-- and so on...
UNION ALL
SELECT <columns> FROM <tables_or_views>
-- note: order by applied at the end to all outputs, not in individual SELECTs
ORDER BY <columns>

If you use UNION instead of UNION ALL and the queries output any duplicate rows, those duplicates will be filtered and only one output. This is basically done the same way as DISTINCT which involves a sort operation (which may not match the sort you have applied via ORDER BY) that for results of any considerable size will involve a dump to tempdb, so unless you know duplicates may be present and want them filtered out it is more efficient to use UNION ALL instead of just plain UNION.

Also unless you need a specific ordering, avoid specifying an ORDER BY clause as that will likely result in dropping the results to tempdb too.

More specifically for the queries you included above I suspect that there is a simpler way (from the query planners PoV so likely more efficient) using single SELECT with appropriate GROUPing and/or window functions instead of several statements UNIONed. You should describe more fully what you are actually trying to get out of this code, and neaten up the included code so it is more readable as I've tried to do a bit with the first example, and someone might help you do that.


Nulling a BLOB column in Oracle 11 impdp

Another option would be to do a datapump export of a view that has only the columns and data that you want. If you want a column to be null have the view make the column null. Then when you do the import you are only importing the data that you want.

expdp VIEWS_AS_TABLES


Make a relation of a table with three different category of id's table

If I have understood what you need is:

TASK TABLE: ID, NAME, DESCR and other attributes

ROUTING TABLE: *ID, IDTASK, INFO and other attributes**

ANALYSER TABLE: ID, IDROUTING and other attributes

SUBCONTRACTOR TABLE: ID, IDROUTING and other attributes

INTERNE USER TALBE: ID, IDROUTING and other attributes

if you want to trace better the relations, you can add in ROUTING TABLE also 3 foreign key like IDANALYSER, IDSUBCONTRACTOR and IDINTERNEUSER. This is not very efficient, because you will have always 2 values null and only one with a value.

But, you can go straight from ROUTING TABLE to the right detailed table.


Selecting multiple columns from multiple rows in one column (subquery?)

You need to use GROUP_CONCAT to aggregate the citizen names first. Then use CONCAT

SELECT CONCAT(id,' # ',name,' # ',citizens) listing FROM
(
    SELECT A.id,A.name,GROUP_CONCAT(B.name,' ',B.surname) citizens
    FROM city A INNER JOIN citizen B
    ON A.id = B.city_id GROUP BY A.id,A.name
) AA;

Here is the SQL Fiddle to prove it : http://sqlfiddle.com/#!2/9f4b3/1

If the names become too long due to truncation, you will have to extend GROUP_CONCAT's maximum length. To set it to 16K, run the following:

SET group_concat_max_len = 1024 * 16;
SET GLOBAL group_concat_max_len = 1024 * 16;

First line is for the session, the second is for all new DB Connections.


What files are safe to delete after upgrade?

That is simple: remove the old data directory, and the garbage should be gone.
You have to do that anyway, because the old data directory cannot be used any more once you have started the new server.
This will of course not remove bloat. To get rid of that, identify affected tables and indexes and use VACUUM (FULL) or REINDEX to rebuild them.


You need to change your subquery approach slightly - moving the condition from the WHERE clause to a join, to bypass MySQL limitations.

If the id and creation_time always define the same ordering, you can use this:

DELETE p
FROM
       pixels AS p
   JOIN
       ( SELECT id
         FROM pixels
         ORDER BY id
           LIMIT 1 OFFSET 4
       ) AS lim
     ON p.id < lim.id ;

Since that may not be true, and the two orderings may sometimes differ, resulting in deleting more or fewer rows than 5, you could use this, which will be more accurate but probably slower with a big table:

DELETE p
FROM
       pixels AS p
   JOIN
       ( SELECT creation_time, id
         FROM pixels
         ORDER BY creation_time DESC, id DESC
           LIMIT 1 OFFSET 4
       ) AS lim
     ON p.creation_time < lim.creation_time
     OR p.creation_time = lim.creation_time AND p.id < lim.id ;


I would suggest the following:

Allow null foreign keys in the main table.
Insert rows minus their foreign keys in the import.
Add a table ImportRelationships that stores relationships during importing.
Rectify the foreign keys in the main table at the end of the import based on ImportRelationships.
Delete the rows from ImportRelationships.


SQL Server 2000 Hitting 8000 Byte Limit Exporting BLOB

You should be using the Bulk Copy Program (BCP), a command-line utility that ships
with SQL Server 2000, for importing and exporting large amounts of data in
SQL Server 2000 databases.

For more information see the Database Journal article
Utilize BCP with SQL Server 2000.


Why does SQL Server 2012 use 6x as much space for same DB?

The backup size will be around the same size as the used space in the data file if you aren't using backup compression.

The database files total 60GB on the source server, so that's what's needed on the server where you are doing a restore. While only 9GB is in use, the file size is 60GB.

You could shrink the files down on the source server and then back it up, but shrinks are bad for performance reasons. Instead, free up some space on the restore server or add more space. Disks are cheap.


Adding array element to JSON column in PostgreSQL

Just use || to concatenate jsonb, if you're using 9.5+.

SELECT lhs::jsonb || rhs::jsonb
FROM ( VALUES
  (
    '[{"iddata":"1","valor":"18"},{"iddata":"2","valor":"10"},{"iddata":"3","valor":"N/A"},{"iddata":"4","valor":"53"},{"iddata":"5","valor":"Agencia Vespucio Oeste"}]',
    '{"iddata":"6","valor":"ACTIVO"}'
  )
) AS t(lhs,rhs);

Outputs:

[{"valor": "18", "iddata": "1"}, {"valor": "10", "iddata": "2"}, {"valor": "N/A", "iddata": "3"}, {"valor": "53", "iddata": "4"}, {"valor": "Agencia Vespucio Oeste", "iddata": "5"}, {"valor": "ACTIVO", "iddata": "6"}]

Note the explicit cast should not be required. I suggest storing your types as JSONB to start out with, unless you have good reason not to.


How to display all entries that have had a box ticked since a particlar date

Select customer_field_names from database WHERE date_column >= '2017-05-07' AND tick_mark_field is NOT null;

That should show all listed fields where the date is at least 05/07/2017 and the check mark box is filled in, in some way. This assumes that they are in the same table.


Mongo takes too much memory when insert data

see this link.   https://groups.google.com/forum/#!topic/mongodb-user/w7G1xRy3TZQ

the bottom line is Mongo caches indexes and data in available RAM


User can see Oracle tables via ODBC that her account does not have access to

It turns out the user had been granted other privileges by a different account as well as the one we use, so we were unable to see those privileges in ALL_TAB_PRIVS. We had to look in DBA_TAB_PRIVS, and sure enough, there were the other permissions. Thanks to all for chiming in!


Generally accounting systems have a large General Ledger table. Each row on the table is a transaction, functioning more like a star schema rather than a traditional 3NF relational database. In this way they are normally a hybrid of both star/snowflake and relational combining the best of both worlds where possible. They are generally not strict 3NF databases, with some duplication in the GL table in terms of project codes or business unit codes. For instance they will often look like this:

Records are not usually updated in an accounting systems. What happens instead is additional transaction rows are added which reflect the amendment in the previous entered value. For instance, if you wanted to change the Transaction Amount for BU Code A100 above to 50 instead of its current value 100, you would add a line:

If you wanted to delete that line then you would add a row similar to the above, but with -100 in the Transaction Amount (and then mark this as a reversal in the transaction types).

Generally computers are pretty fast at the adding part of this, there are various partitioning tricks you can use to improve performance. All vendors are very fixated on improving the performance, so you usually don't have an issue. With modern DBs indexing, they usually update quite fast though there are often lags.

For real performance the accounting system data is usually put into an OLAP cube, where further speed efficiencies can be obtained and more analytic type queries can be answered.


How to restore Network backup to Network Path in SQL Server 2005?

Microsoft has documented their "Description of support for network database files in SQL Server" at: http://support.microsoft.com/kb/304261

Trace flag 1807 bypasses the check and allows you to configure SQL Server with network-based database files. However, this is not without risk and you can fairly easily wind up with a corrupt database if the device does not fully support what SQL Server needs to achieve consistency.

If you want to use this approach in a manner that Microsoft will fully support, they have a set of standards describing what they support.  Following is a quote from the link:

Windows Hardware Quality Lab (WHQL) qualified devices
Microsoft Windows servers and networked servers or NAS storage servers that are Windows Hardware Quality Lab (WHQL) certified automatically meet the data write ordering and write-through guarantees required to support a SQL Server storage device. Microsoft supports both application and storage-related issues in these configurations.

Note To be supported by SQL Server, the NAS storage solution should also meet all the requirements that are listed on the following Microsoft website:
http://download.microsoft.com/download/6/E/8/6E882A06-B71B-4642-9EB4-D1EA0D6223C8/SQL%20Server%20IO%20Reliability%20Program%20Requirements%20Document.docx


You need to first normalize the JSON data into a result that can be aggregated:

with data (doc) as (
  values
    ('{"a": 1, "b": 4}'::jsonb),
    ('{"b": 7, "c": 3}'::jsonb)
)
select k, v::int as val
from data, jsonb_each_text(doc) as t(k,v);

returns

k | v
--+--
a | 1
b | 4
b | 7
c | 3

This can now be aggregated per key:

select k, sum(v::int) s
from data, jsonb_each_text(doc) as t(k,v)
group by k;

returns:

k | s
--+----
c |  3
a |  1
b | 11

This can now be converted back and aggregated into a JSON:

select jsonb_object_agg(k,s) as doc
from (
  select k, sum(v::int) s
  from data, jsonb_each_text(doc) as t(k,v)
  group by k
) t

returns

doc
-------------------------
{"a": 1, "b": 11, "c": 3}


Why does this query, missing a FROM clause, not error out?

This statement is legal (in other words, no FROM is required):

SELECT x = 1;
SELECT x = 1 WHERE 1 = 1; -- also try WHERE 1 = 0;

The trick is when you introduce a column name that clearly can't exist. So these fail:

SELECT name WHERE 1 = 1;

SELECT x = 1 WHERE id > 0;

  Msg 207, Level 16, State 1
  Invalid column name 'name'.
  Msg 207, Level 16, State 1
  Invalid column name 'id'.

But when the invalid column is introduced in something like a subquery, what SQL Server does when it can't find that column in the inner scope of the subquery, is traverse to an outer scope, and make the subquery correlated to that outer scope. This will return all rows, for example:

SELECT * FROM sys.columns WHERE name IN (SELECT name WHERE 1 = 1);

Because it's essentially saying:

SELECT * FROM sys.columns WHERE name IN (SELECT sys.columns.name WHERE 1 = 1); /*
              ^^^^^^^^^^^                       -----------
                   |                                 |
                   -----------------------------------    */

You don't even need a WHERE clause in the subquery:

SELECT * FROM sys.columns WHERE name IN (SELECT name);

You can see that it's really looking at the outer scoped table, because this:

SELECT * FROM sys.columns WHERE name IN (SELECT name WHERE name > N'x');

Returns far fewer rows (11 on my system).

This involves adherence to the standard about scoping. You can see similar things when you have two #temp tables:

CREATE TABLE #foo(foo int);
CREATE TABLE #bar(bar int);

SELECT foo FROM #foo WHERE foo IN (SELECT foo FROM #bar);

Obviously, this should error, right, since there is no foo in #bar? Nope. What happens is that SQL Server says, "oh, I didn't find a foo here, you must have meant the other one."

Also, in general, I would avoid NOT IN. NOT EXISTS has the potential to be more efficient in some scenarios, but more importantly, its behavior doesn't change when it is possible that the target column could be NULL. See this post for more info.


It's still in beta.  It won't be released till the 12.1.0.2 patchset.  As such, unless you're in the beta program, not much information is available yet.

I think the NDA has been lifted, as I saw Tom Kyte speak about it at Collaborate 14 in Las Vegas a couple of weeks ago, but, you can't actually use it yet.

Hope that helps.


Oracle Automatic Memory Management - SGA_MAX_SIZE

The docs for sga_max_size say:

  On 64-bit platforms and non-Windows 32-bit platforms, when either MEMORY_TARGET or MEMORY_MAX_TARGET is specified, the default value of SGA_MAX_SIZE is set to the larger of the two parameters.

And according to Oracle document Automatic Memory Management (AMM) on 11g (Doc ID 443746.1), if you have both sga_target and pga_aggregate_target unset, then:

  If neither is set, they will be auto-tuned without any minimum or default values. We will have a policy of distributing the total memory set by MEMORY_TARGET parameter in a fixed ratio to the the SGA and PGA during initialization. The policy is to give 60% to the SGA and 40% to the PGA at startup.

So the simplest thing to do if you want Oracle to completely control the SGA is to reset it and let the default apply:

SQL> alter system reset sga_max_size scope=spfile;

(And restart.)

If you want AMM but would like minimum values for the SGA or the PGA (or both), do so by setting the corresponding _target parameter:

  The preceding steps [to set up AMM] instruct you to set SGA_TARGET and PGA_AGGREGATE_TARGET to zero so that the sizes of the SGA and instance PGA are tuned up and down as required, without restrictions. You can omit the statements that set these parameter values to zero and leave either or both of the values as positive numbers. In this case, the values act as minimum values for the sizes of the SGA or instance PGA.

Whatever you do, don't set the PGA or SGA target or max size to greater than the memory target. This prevents instance startup.


Which MySQL table to use for read-only, bulk-loaded tables?

This is definitely a loaded question. Why ???

InnoDB caches data and index pages
InnoDB may lock Clustered Indexes during Inserts/Updates
InnoDB is multithreaded and can be tuned for accessing CPUs/Cores
MyISAM only caches index pages
MyISAM must always be read data from disk
MyISAM can have dedicated caches per tables

If you have plenty of RAM and intend to read the same data throughout a business day, some would go with InnoDB. If you are looking for high-speed reads from varying ranges of data, some would go with MyISAM. Even still, there are simply too many mitigating factors to pick one fully over the other such as

VARCHAR field processing
Row Format
Foreign Keys
Index Cardinality
Physical Disk (SSD, SATA, SAN, NAS)
Application Processing Needs

Here are some posts that compare and contrast MyISAM and InnoDB

Sep 20, 2011 : Best of MyISAM and InnoDB
April 12, 2012 : Mysql: Insert performance INNODB vs MYISAM (Not My Post, but good answers are given)
May 02, 2012 : Which mysql storage engine to choose?
May 03, 2012 : Which is faster, InnoDB or MyISAM?
Aug 17, 2012 : When to switch from MyISAM to InnoDB?

Please read these before deciding ...


Can we set the fixed size for both plan cache and data cache?

No.

You can set Minimum & Maximum server memory to control the range of total memory utilization by the Instance, but there is not fine grained control for the constituent parts that make up "server memory"


SQL 2000: change collation of model database only- not master

I was restoring database using management studio and not enterprise manager.
I could restore model using enterprise manager without any problem.


Conditional where for postgres stored procedure

You can use Boolean expressions, that return true, if a parameter is null or empty, otherwise the result of a LIKE operation.

...
SELECT DISTINCT
       t.serialnumber as sn,
       t.productid as tid,
       t.merchantid as mid
       FROM tbterminal as t
       WHERE (coalesce(p_mid, '') = ''
               OR t.merchantid LIKE '%' || p_mid || '%')
             AND (coalesce(p_tid, '') = ''
                   OR t.productid LIKE '%' || p_tid || '%')
             AND (coalesce(p_sn, '') = ''
                   OR t.serialnumber LIKE '%' || p_sn || '%')
       ORDER BY idterminal ASC
       LIMIT p_limit
       OFFSET p_offset;
...


I have solved this problem with Port Forwarding.
I've seen no tutorial for Remote Access to SQL Server with this part.
So, setup 1433 TCP Port forwarding in your Router Settings and everything will work as it should.


will this script delete all tables with the prefix of Test_ on the database I execute it on?

Yes, it will DROP tables. DELETE is normally used with deletion of data. Your script will physically drop the tables.
Also, please dont use INFORMATION_SCHEMA and there is no need for a cursor. Your script can easily be written using dynamic sql.

EDIT : In comments, @MartinSmith pointed to one of his excellent answer - that I was not aware ... but is an eye opener when concatenating. (To be honest, I have never faced, but now will be aware of the gotcha ! Thanks again Martin !)

The ONLY guaranteed mechanism are the following:

Use cursor to loop through the rows in specific order and concatenate the values

Use for xml query with ORDER BY to generate the concatenated values

Use CLR aggregate (this will not work with ORDER BY clause)

DECLARE @sql nvarchar(max) = N'';

SELECT @sql = (select N'drop table ' + QUOTENAME(t.name) + N';'
FROM sys.tables AS t
WHERE t.name LIKE N'Test_%'
order by object_id
 FOR XML PATH(''), TYPE
).value('.','nvarchar(max)')

print @sql ;
-- caution below will ACTUALLY drop the tables.
-- review the output and then uncomment below line
EXEC sys.sp_executesql @sql;


How to determine cost/benefit of adding an index?

Index usage depends on the structure of you data and the selectivity of you data. If you make queries that you don't use them they are expensive. If you use them effectively they are great tool. There is a query planner and query planner analytics(EXPLAIN) of just planning or planning and executuion of commands (EXPLAIN ANALYZE).

And there is a great online book: use the index luke about recommended usage of indexes


What kind of changes does snapshot file store?

My 1st question is, what kind of changes does snapshot file store? Is
  it acting like t-log (storing every single changes made to [DBS])?

Database snapshots operate at the data-page level and not at log level. Before a page of the source database is modified for the first time, the original page is copied from the source database to the snapshot. The snapshot stores the original page, preserving the data records as they existed when the snapshot was created. The same process is repeated for every page that is being modified for the first time. To the user, a database snapshot appears never to change, because read operations on a database snapshot always access the original data pages, regardless of where they reside.

This is the point where log and data will change because log contains committed as well as uncommitted transactions.

  The 2nd question is, the part where it states "...and changes in the
  source since the snapshot was created" reminds me the working
  principle of Differential backup where latter takes all t-logs since
  the full backup was taken. Is their working principle similar somehow?

Differential backup and snapshot is not same and are not related. Snapshot doesn't consider last full backup and is a static view of source database.

Backup:
During a typical Full backup, all the pages of database are copied to a different location and this backup can be moved to a different Server, SAN, Tape, any other media. This can be used even after decades if required.
Back strategy can be designed and scheduled even for a point in time recovery of any time frame using Full, Differential, and transactional backup. This is a complete package with no gaps.

Snapshot:
A database snapshot is a read-only, static view of a SQL Server database (the source database). The database snapshot is transactionally consistent with the source database as of the moment of the snapshot’s creation.

Major advantage of Snapshot over Backups is the time it takes to backup and restore especially when you want to take the backup and restore quickly in a short span of time. Referring to below image, Snapshot takes only seconds in the first step as there is no data change. In a typical deployment less than 1% of the data pages will be changed and restoration will also be very quick.

We also need to understand purpose of these two:

Snapshots are typically useful for purposes like audits and reporting. Another use for snapshot backups is that multiple snapshots can be created for a database, and these can be taken at different points in time. This helps with period-over-period analyses.

It is important to understand that database snapshots are directly dependent on the source database. Therefore, snapshots can never substitute your regular backup and restore strategy. For instance, if an entire database is lost, it would mean its source files are inconsistent. If the source files are unavailable, snapshots cannot refer to them, and so, snapshot restoration would be impossible.

You may refer more details from below links:

https://www.sqlshack.com/understanding-database-snapshots-vs-database-backups-in-sql-server/

https://vijredblog.wordpress.com/2015/01/28/sql-snapshot-vs-backup-when-to-choose-snapshot-over-backup/

https://docs.microsoft.com/en-us/sql/relational-databases/databases/database-snapshots-sql-server?view=sql-server-2017

Hope above helps.


Using NAS device as SQL server backup target

Drive mapped iSCSI targets work perfectly for SQL Server backup destinations.

Map the iSCSI target into your VM as a drive, assign a drive letter inside the VM, and just do normal backups as you would to any other target.

I use that exact process for my clustered SQL Servers.


Elapsed time usually shows how long the request was running for, and CPU - a cumulative time of all processes. For example,  I usually see CPU x4 times higher than the elapsed time when the request's DOP is 4. When DOP is 1, then elapsed time matches CPU time.


Is it safe to move big table to another tablespace while working with it?

The table will be AccessExclusive locked for the duration of its move to a new tablespace, so your application will seize up during that time.

You haven't really described what your workload is, so it is hard to recommend settings to accommodate it.  Surely it wasn't 15 separate one row inserts and ~7 select operations per minute which lead to the existing bloat?

Generally it is best to delete rows once you become aware that they are eligible for deletion.  Creating a deletion-queue table is just another table which can get bloated, needs to be maintained, have deletions done on it itself, be vacuumed, have code written to accommodate it, etc.


Yes, stay away from the sp_OA* OLE Automation procedures.

If you want  / need to handle this purely within SQL Server then you can do so using SQLCLR, which replaces the sp_OA* OLE Automation procedures. And if you aren't using xp_cmdshell for anything else, then no need to enable it just for this (although to be fair, if using SQL Agent, then a CMD step would be able to execute BCP and/or 7zip without the need for enabling xp_cmdshell).

With SQLCLR you can:

do the export from within T-SQL as well and not need to mess with BCP which is an external process (see FileStream).
zip the file, as a separate step, since. NET has methods to gzip and gunzip (see GZipStream).
encrypt with a password (see RijndaelManaged and AesManaged). While you did specify AES, the MSDN documentation for AesManaged states:

  The AES algorithm is essentially the Rijndael symmetric algorithm with a fixed block size and iteration count. This class functions the same way as the RijndaelManaged class but limits blocks to 128 bits and does not allow feedback modes.

The difference is if you are using SQL Server 2005, 2008, or 2008 R2. Those versions are limited to using .NET 3.5, and the MSDN documentation for AesManaged (for that version) states that it has a Host Protection Attribute (HPA) which means that any assembly using that class has to be marked as PERMISSION_SET = UNSAFE, whereas RijndaelManaged does not have that issue and can be used in an EXTERNAL_ACCESS assembly. However, it seems that the documentation for AesManaged shows that, starting in .NET Framework version 4.0 (SQL Server 2012, 2014, and 2016 use .NET Framework 4.0 and newer--currently up to 4.6), the note about the HPA has been removed. Even if UNSAFE is acceptable, please note that the specific HPA is MayLeakOnAbort, which means "might leak memory if the operation is terminated".

In this setup, the password is part of the encryption, not the compression. Hence you either have a zip file containing an encrypted file, or you have an encrypted file containing the zip file. The former method gives you a file that can be unzipped by anyone but then only read by someone with the correct password. The latter method requires knowing the password to get the compressed file, and also allows for better compression since the compression will be done on a text file  (with a greater likelihood of compressible patterns).

While I have not implemented the encryption algorithms yet, I have coded both the file export (of any random query) and GZip/GZunzip in SQLCLR and can state that they do run efficiently, or at least can be made to be efficient (and can also be made to be inefficient). By this I mean I have used them to extract queries producing 5 GB text files, and GZipping those 5 GB text files, and memory was never adversely affected since both operations are streamed. Please note that the .NET-provided GZipStream class does not support the Zip64 format, and so is limited to 4 GB of source data.

For anyone who is interested in pre-built SQLCLR functions to do the extract and compression (the ones I mentioned above), they are available in the SQL# library (which I am the author of). Please note that:

while there is a Free version of SQL#, the two objects mentioned here, DB_BulkExport and File_GZip, are only available in the Full version.
in order to overcome the 4 GB limitation of the GZipStream class, I had to incorporate a 3rd-party library that does support Zip64.
soon enough there will be functions to handle file encryption and decryption via Rijndael (available for SQL Server 2005 - 2016 at least) and Aes (available for SQL Server 2012 - 2016 at least).


Setup MySQL Master to Master replication/automatic failover when each Master also has a Slave

MHA orchestrates the reconnection of slaves as needed.
Orchestrator orchestrates the reconnection of slaves as needed.
Galera (PXC, MariaDB) eliminates the need for failover by having all nodes writable, plus automatic repair of nodes that were offline.
Fabric is another way.
Doing the task yourself is possible, but complex, risky, etc, etc.


Why do these concurrent MySQL inserts deadlock?

I failed to understand the extent of the transactions.

Before I do the INSERT, I do a write-locking select on the external index value in both transactions. I would have expected T2 to block T1 at that point, but that is not the case, both transactions successfully acquire a lock on the gap. Neither transaction can promote its lock to insert intention; T1 will block when it tries, then T2 will deadlock when it tries, thereby letting T1 succeed.

The behavior I found, and continue to find surprising, is that write locks on index gap values aren't exclusive. While that's certainly a correct way to implement serializability, it carries an attendant risk of deadlock that you would forego if write locks on gap values were exclusive as they are for record values. I wonder if I continue to miss something fundamental.


Insert value into table without knowin column name

You can not do that, you need dynamic SQL, and be careful with that. Below is a simple example (without any error handling, for educational purpose only).

declare
  l_col varchar2(128);
begin
  select word1 into l_col from TableOfDocuments WHERE ID=1;
  execute immediate 'UPDATE BagOfWords SET ' || l_col || ' = 1 WHERE USER_ID=1';
end;
/


Enable SSMS in SQL Server 2005 Developer Edition

Did you search your system for SqlWb.exe or ssms.exe? If those aren't there then run setup again and make sure you choose "Workstation Components, Books Online and Development Tools."


Trying to filter msdb..sysjobs by job_id

Stop using CAST and use CONVERT. Specifically because, in this case, you need to use a certain style parameter in order to get the right string comparison (otherwise it is just converting 0xwhatever to the string value represented by 0xwhatever, which is not 0xwhatever. Compare:

SELECT
  CONVERT(VARCHAR(32), 0x48656C6C6F21),
  CONVERT(VARCHAR(32), 0x48656C6C6F21, 1);
  ----- only difference -------------^^^

Results:

------   --------------
Hello!   0x48656C6C6F21

Hello! is actually the string represented by the binary value. The second column is just the binary value displayed as a string. Big difference!

With that in mind, change the join criteria on your last query to:

INNER JOIN msdb.dbo.sysjobsteps sj -- why leave out dbo?
  ON SUBSTRING(s.[program_name],30,34) = -- prefix your columns
  CONVERT(VARCHAR(34), CONVERT(VARBINARY(32), sj.job_id), 1) -- no need for MAX types here
  ------- this is the important piece ------------------^^^


create sql server login for own domain account

This requires stopping and starting the SQL Service, but will get you access to the SQL Server Instance if you are a local admin on the server.

Run this from an elevated command prompt.

Substitute the service name for mssqlserver if you have a named instance or SQL Express i.e. mssql$sqlexpress

Substitute your domain and account name for BUILTIN\administrators if you do not want to grant sysadmin for all local administrators and want just your account.

Note: the /m SQLCMD switch is single user mode and only a SQLCMD connection will be accepted. This is to keep from being locked out by a service that connects before I can get connected.

I have put these commands in a .bat file and ran it from an elevated command prompt. That makes the outage very short.

As always, test on a dev instance before you go running against production.

net stop mssqlserver

net start mssqlserver /mSQLCMD

sqlcmd -Q "if not exists(select * from sys.server_principals where name='BUILTIN\administrators') CREATE LOGIN [BUILTIN\administrators] FROM WINDOWS;EXEC master..sp_addsrvrolemember @loginame = N'BUILTIN\administrators', @rolename = N'sysadmin'"

net stop mssqlserver

net start mssqlserver

sqlcmd -Q "if exists( select * from fn_my_permissions(NULL, 'SERVER') where permission_name = 'CONTROL SERVER') print 'You are a sysadmin.'"


At least exists three way to do this:

1. REPLICATION + VIEW / FDW

You can create ON MASTER VIEW or FDW for table and VIEW / FDW will be replicate to slave.

Something like :

CREATE VIEW schema1.table_name AS SELECT * FROM public.table_name;

Or

CREATE SERVER srv FOREIGN DATA WRAPPER postgres_fdw OPTIONS (dbname 'db', host 'localhost');
CREATE USER MAPPING FOR postgres SERVER dc OPTIONS (password 'pwd', "user" 'postgres');
CREATE FOREIGN TABLE schema1.table_name (..table description..) SERVER srv OPTIONS (schema_name 'public', table_name 'table_name');

2. TRIGGER + SAVE TO REMOTE

You can create trigger on public.table_name on master to write changes to remote db via dblink or FDW.

3. LOGICAL REPLICATION

The most hard path as for me. You can write your own decoder and/or add trigger to write also/instead data to schema1.table_name


Query to get list of busiest schemas in Oracle 12c database

DBA_HIST_SEG_STAT stores segment level statistics about reads, writes and others.

Example output from one of my sandbox environments:

select
  o.owner,
  sum(physical_reads_delta) as physical_reads,
  sum(physical_reads_direct_delta) as physical_reads_direct,
  sum(physical_writes_delta) as physical_writes,
  sum(physical_writes_direct_delta) as physical_writes_direct,
  sum(db_block_changes_delta) as db_block_changes
from
  dba_hist_seg_stat ss
  join dba_objects o on (o.object_id = ss.obj#)
group by
  o.owner
order by
  o.owner
;

OWNER  PHYSICAL_READS PHYSICAL_READS_DIRECT PHYSICAL_WRITES PHYSICAL_WRITES_DIRECT DB_BLOCK_CHANGES
------ -------------- --------------------- --------------- ---------------------- ----------------
AUDSYS              0                     0              42                      0              112
BP            7917707               7917685          387803                 386932             4224
DBSNMP              0                     0               0                      0             1264
SYS            192271                 13873          290452                   5753          8182176


Equality in JOIN or WHERE clause? Efficiency and style

Speaking of style, you can improve in several places:

In addition to what Craig already wrote.

It's inconsistent to have one condition that only involves the doctor table in the JOIN clause, while the other one is in the WHERE clause. Be consistent, both or none. Best to put these in the WHERE clause, while the condition that links both tables goes into the JOIN clause - possibly simplified using USING. See below.
It's also inconsistent to table-qualify some columns (doctor.name, doctor.type), but not others (code). In bigger queries its best to always table-qualify all columns to avoid naming collisions.
"name" is not a good name. You should rarely use it. Use meaningful identifiers that you don't repeat in every table. Once you join a couple of tables (which is what you do in SQL all the time), you end up with multiple columns named "name" and have to deal out column aliases to even work with it. "id" or "code" are similarly bad choices.
Use descriptive identifiers as suggested below.
It's an antipattern to use doctor.code and practice.doctor_code. Some not so smart ORMs are in the habit of using this - doesn't make it a good idea. In addition to "code" being a bad identifier, it's good practice to use the same name for columns with identical data. Use doctor_code in both places. Or better yet: doctor_id, because what's in the column is probably an "identifier" not a "code" (wrong term). Such a naming convention also allows to use the convenient USING construct in joins.
Use the SQL standard <> instead of the equivalent !=.
Use table aliases to shorten code and maintain readability.

Everything put together, it could look like this:

SELECT p.practice, d.name  -- for a person, "name" is halfway sensible
FROM   doctor d
JOIN   practice p USING (doctor_id)
WHERE  d.name LIKE '%son'
AND    d.doc_type <> 'surgeon';


MySQL Query on bad designed table with multiple Selfjoins

Looking at your Query, I have a few suggestions:

Get rid of the "OR"s.  you can do this by checking if the
addition of all the fields > 0... OR's are a killer.
as Jkavalik mentioned, use a distinct  instead of a group by... better perf.
The "AND"s of Values 2 to 6 can also be an addition = 0
Do you absolutely need the Left joins? Can they be replaced with inner joins?

As for the logic, I'm not sure I understand the whole concept.


SQL Server Mirroring kicks in before cluster has time to fail over

Database mirroring questions from TechEd: failovers and partner timeouts

  ... when the cluster is failing over and the principal instance is
  unavailable for 60-90 seconds, the other instances in the mirroring
  session will time-out after ten seconds and initiate a mirroring
  failover... The solution to the problem is to change the partner time-out value - a facility that isn't well known. To change the partner time-out value for a mirroring session, use the following code:

ALTER DATABASE mydatabase SET PARTNER TIMEOUT 90;


MySQL conversion from float (10,2) to fixed(10,2)

BAD NEWS : I do not think the value will be preserved !!!
I wrote this post (July 25, 2011) showing how sensitive floating point numbers are to conversions.
GOOD NEWS : Here is how you can safely compare the data conversion:
If your table has these characteristics

table is called moneytable
field you wish to convert is called moneyfield
primary key is called moneyid

Then run these commands:
DROP TABLE IF EXISTS moneytabletest;
CREATE TABLE moneytabletest LIKE moneytable;
ALTER TABLE moneytabletest MODIFY moneyfield fixed(10,2);
INSERT INTO moneytabletest SELECT * FROM moneytable;
SELECT A.moneyid,A.moneyfield olddata,B.moneyfield newdata
FROM moneytable A LEFT JOIN moneytabletest B USING (moneyid)
WHERE A.moneyfield <> B.moneyfield;

Here is the bottom line: If any rows come back from the LEFT JOIN query, the conversion will be bad. You will have to modify the definition of moneyfield, reload  the moneytabletest table, and run the LEFT JOIN query again and again until no rows come back from the LEFT JOIN query. Once zero(0) rows come back, you will know what conversion will be safe.
Give it a Try !!!


Query Plan Different When Using Compiled Values

Most likely, either:

The state of the database (e.g. statistics) changed since the cached plan was first compiled, causing a recompilation; or
Your SSMS connection uses different settings (e.g. ANSI_NULLS) from those used by the connection that cached the plan (so it could not be reused); or
It was an ad-hoc batch and the text of your query did not exactly match the cached version
You used literal values where the original plan was parameterized


Table size without indexes in sql server

There are multiple ways of finding size of a table in the database. First and easiest way is to right click on Table and check the storage tab and this gives you Data space and Index Space. Data space is nothing but size of data in the table and similarly index space gives you size of index in the mentioned table.

The above information would exactly match with the command sp_spaceused 'schema_name.table_name' We can cross check the same for the Users Table(in dbo schema and StackOverFlow Database).

Here, if you add data, index size and unused(in KB), you will get reserved space by table.
You may also check the size of table and index by running command sp_blitzindex(from Mr. Brent Ozar). Command would be as below:
exec sp_BlitzIndex  @SchemaName = 'dbo', @TableName = 'Users'

Here, you may check the Size column. Clustered index size will give you Table size and you can also find size of each and every index as well as their usage(if interested).
Moreover you can also check table size of all of them by getting into Reports as below:

You may also run below select statement to get full size and index size of all the tables:
;with cte as (
SELECT
t.name as TableName,
SUM (s.used_page_count) as used_pages_count,
SUM (CASE
            WHEN (i.index_id < 2) THEN (in_row_data_page_count + lob_used_page_count + row_overflow_used_page_count)
            ELSE lob_used_page_count + row_overflow_used_page_count
        END) as pages
FROM sys.dm_db_partition_stats  AS s
JOIN sys.tables AS t ON s.object_id = t.object_id
JOIN sys.indexes AS i ON i.[object_id] = t.[object_id] AND s.index_id = i.index_id
GROUP BY t.name
)
select
    cte.TableName,
    cast((cte.pages * 8.)/1024 as decimal(10,3)) as TableSizeInMB,
    cast(((CASE WHEN cte.used_pages_count > cte.pages
                THEN cte.used_pages_count - cte.pages
                ELSE 0
          END) * 8./1024) as decimal(10,3)) as IndexSizeInMB
from cte
order by 2 desc

If you want only the table sizes then execute the following:
SELECT
t.name as TableName
, SUM (CASE WHEN (i.index_id < 2)
          THEN (in_row_data_page_count + lob_used_page_count + row_overflow_used_page_count)
          ELSE lob_used_page_count + row_overflow_used_page_count
     END) * 8 as table_size_kb
FROM sys.dm_db_partition_stats  AS s
JOIN sys.tables AS t ON s.object_id = t.object_id
JOIN sys.indexes AS i ON i.[object_id] = t.[object_id] AND s.index_id = i.index_id
where t.object_id = OBJECT_ID('schema.table')
GROUP BY t.name

Hope this helps.


Overwriting old data in Azure SQL database

You can use a MERGE statement to perform a conditional INSERT/UPDATE (sometimes called UPSERT) of data in your table.

Example:

MERGE INTO dbo.Output AS Target
USING (SELECT DeviceId, ParkingStatus FROM IoInput) AS Source (Id, ParkingStatus)
    ON Target.Id = Source.Id
WHEN MATCHED THEN
    UPDATE SET ParkingStatus = Source.ParkingStatus
WHEN NOT MATCHED BY TARGET THEN
    INSERT (Id, ParkingStatus) VALUES (Id, ParkingStatus)

This statement will match records in IoInput with existing records in Output. Any matched records will update the ParkingStatus in Output with the value in IoInput. Any unmatched records in IoInput will be inserted into Output.


Sessions, Processes, Object Instances, Sequential and Parallel Processing Theory

The SPs will be queued and run based on available resources. Depending on the isolation level used in defining the SP and how the transactions are defined, the SPs will wait accordingly. By design, the SP will not always wait for the first SP to finish, and then move onto the second one etc...

This design make is possible for multiple unrelated users to call the SP and work concurrently.


Dropping a table with a non-printable character in its name

Since you already know which character is hidden, you can easily construct the current name using a variable, pass that into sp_rename, and then drop the table using the newly acquired name:

DECLARE @t nvarchar(128) = N'dbo.Table' + NCHAR(31) + N'_Name';
EXEC sys.sp_rename @t, N'NewTableName', N'OBJECT';
DROP TABLE dbo.NewTableName;

(Always be careful about using the proper schema identifier.)

Even easier would be to take your string that you know has the character in it, and wrap it in square brackets. This worked for me just fine:

DROP TABLE dbo.[Table_Name];
---------------------^
-- There is an NCHAR(31) right before the underscore

If you didn't know the trouble character(s), you could pull the name from the catalog view, identifying it by surrounding characters, then loop through the characters to determine which are problematic. Here's a full repro:

USE tempdb;
GO

DECLARE @sql NVARCHAR(MAX);
SET @sql = N'CREATE TABLE dbo.[Table' + NCHAR(31) + N'_Name](ID INT);';
EXEC sys.sp_executesql @sql;
GO

DECLARE @var NVARCHAR(128);
SELECT @var = name
  FROM sys.tables
  WHERE name LIKE N'Table%Name';

-- is it the right one?
PRINT @var;

DECLARE @i INT;
SET @i = 1;
WHILE @i <= LEN(@var)
BEGIN
  PRINT SUBSTRING(@var, @i, 1) + N' => ' + RTRIM(UNICODE(SUBSTRING(@var, @i, 1)));
  SET @i = @i + 1;
END

Results:

Table_Name
T => 84
a => 97
b => 98
l => 108
e => 101
 => 31
_ => 95
N => 78
a => 97
m => 109
e => 101

And then don't forget to chide or smack the person responsible for a table getting into the system that way in the first place, if they're still employed in the industry.


Is it bad to have M:M relationships defined where 1:M is being used?

It would work - but it would be relying on the application logic to maintain relational integrity. You probably want to limit the relationship to 1:1 initially at the database level - which can be done without sacrificing flexibility.

Normally, the following relationships can be handled in the following ways:

1:1 - data can be maintained in the same table
1:many - "many" data is in a separate table, and uses the primary key from the "1" table to link the many back to the one
many:many - a "bridge" table is used, with the primary keys from both of the "many" tables, so each row from table A can be tied to many rows from table B, and each row from table B can be tied to many rows form table A.

You can "downgrade" from 1:many to 1:1, or from many:many to 1:many or to 1:1, by using unique constraints.

Let's take your example: You've got user, image, and user_profile_image. Currently, you want to have a 1:1 relationship - each user has (at most) one profile image, and each profile image is tied to (at most) one user.

Let's assume that user_id is the primary key of user, and image_id is the primary key of image. In user_profile_image, as you note, those fields are foreign keys, linking each row to a specific user and image.

The table would look something like this:

CREATE TABLE user_profile_image
     ( upi_id int AUTO_INCREMENT PRIMARY KEY -- or whatever works in your DBMS
      ,user_id int
      ,image_id int
      ,CONSTRAINT FK_user_profile_image__user
         FOREIGN KEY (user_id) REFERENCES user (user_id)
      ,CONSTRAINT FK_user_profile_image__image
         FOREIGN KEY (image_id) REFERENCES image (image_id)
     )

Therefore, for a many-to-many relationship, there could be many rows with the same user_id, connecting that user to many different images, and many rows with the same image_id, tying that image to many different users.

If we want to change this many-to-many set-up, restricting it so it only allows a 1:1 relationship, we change our table, adding unique constraints to both of our foreign keys:

CREATE TABLE user_profile_image
     ( upi_id int AUTO_INCREMENT PRIMARY KEY -- or whatever works in your DBMS
      ,user_id int
      ,image_id int
      ,CONSTRAINT FK_user_profile_image__user
         FOREIGN KEY (user_id) REFERENCES user (user_id)
      ,CONSTRAINT UNIQUE INDEX UX_user_profile_image__user_id (user_id)
      ,CONSTRAINT FK_user_profile_image__image
         FOREIGN KEY (image_id) REFERENCES image (image_id)
      ,CONSTRAINT UNIQUE INDEX UX_user_profile_image__iamge_id (image_id)
     )

(Actual syntax may vary for your DBMS)

With these constraints in place, each user_id can only appear in one user_profile_image row, as can each image_id - a 1:1 relationship. So, each user can have only one image, and each image can be tied to only one user.

Note that you must have both UNIQUE constraints for this to work. I you had one UNIQUE constraint that covered both user_id and image_id, then users could have multiple image and images could be tied to multiple users. Such a constraint would only prevent the same user/image relationship from appearing twice in the table. That can be a useful constraint to have, admittedly; but it won't do what you want.

Now, if you get to a point where you want a user to be able to have multiple profile images (a 1:many relationship), you would drop the UNIQUE constraint on the user_id. At that point, user_id could appear in multiple user_profile_image rows, but each image could still only be tied to one user.

If, on the other hand, you still want each user to have one profile image, but you find that 90% of your users are using the same three profile images, you may want to keep the UNIQUE constraint on user_id, and remove the UNIQUE constraint on image_id. Then, each user would still have one image - but you could just keep one copy of each of those 3 images, and tie all the users who want one to one instance of the image, saving a fair amount of space. you've flipped which table is the one and which is the many, but this is still a 1:many relationship.

Finally, if you want users to be able to have multiple images, and images to be used by more than one user (a true many:many relationship), drop both the user_id and the image_id UNIQUE constraints.

Note: I would only do things this way if you genuinely see a possibility that the relationship could change. Someone looking at the basic table layout might assume that the relationship is many-to-many (since you have a bridging table), and miss the UNIQUE constraints, leading them to write code that won't work. It's even possible that someone will assume the UNIQUE constraints are a mistake created by someone in the past, and remove them without understanding the full implications.

Also, you do add somewhat to the overhead required to store your DB on disk, having additional indexes that you wouldn't need if you used the "normal" construct for a 1:1 or 1:many relationship. Also, queries will be a little bit more expensive. In most cases, this wouldn't be a big deal; but, if you're dealing with billions of data rows, it may be worth consideration.

I wouldn't say it's actively wrong to do things this way, but it can be over-engineering your solution, planning for eventualities that never appear. Like I said above - unless you can see a real likelihood that you will need to "upgrade" a 1:1 or 1:many relationship in the future, I'd stick with the simple solutions.


SQL INSERT INTO SELECT query for updating on same table with some modification

So you're close you just need to add the data modifications you want to each field in your SELECT list like so:
INSERT INTO
    Student (Name, Department)
SELECT CONCAT('Mr. ', Name), CONCAT('SCIENCE(', Department, ')') FROM Student;

You can use the CONCAT() function to concatenate strings in MySQL