Postgresql Rename Table without Updating View Definitions I've found out today that renaming tables in Postgresql also renames the VIEW definitions for Views that use the table I renamed automatically? Is there any way to turn this off?

So I rename the table with:

ALTER TABLE xyz RENAME TO abc;

And my VIEW defition for the sake of example is:

CREATE VIEW foo AS SELECT * FROM xyz;

Next thing I know after I rename the table, the definition for foo has changed to point to table abc.

I'm using Postgresql 8.4

--Updated: Dec 30, 2014 at 12:41pm--

Here is a test case to illustrate what I mean:

CREATE TABLE tmp_test_a (num integer);
CREATE VIEW v_tmp_test_a AS SELECT * FROM tmp_test_a;

-- Now look at what the VIEW is using (now is using tmp_test_a, which is what it should be using):
SELECT pg_get_viewdef('v_tmp_test_a'::regclass, false);

-- Now create a second table, and swap them.
CREATE TABLE tmp_test_b (num integer);
ALTER TABLE tmp_test_a RENAME TO tmp_test_c;
ALTER TABLE tmp_test_b RENAME TO tmp_test_a;
ALTER TABLE tmp_test_c RENAME TO tmp_test_b;

-- Now look at what the VIEW is using again (now is using tmp_test_b with an alias of tmp_test_a)
SELECT pg_get_viewdef('v_tmp_test_a'::regclass, false);

-- Cleanup
DROP VIEW v_tmp_test_a;
DROP TABLE tmp_test_a;
DROP TABLE tmp_test_b;

The simple answer - because views reference the OID and not the object name. The name is translated back in the internal function.

A view in PostgreSQL can be thought of as an empty table with a select rewrite rule. Using your example, the internal query tree for the rewrite rule is located in the pg_rewrite.ev_action table/column and there you'll see the references to the individual table and column OIDs.

What I'd recommend doing is enclose the table rename, and a CREATE OR REPLACE VIEW statement within a single transaction, that way everything becomes relatively seamless and the view is recreated pointing to the new table's OID.


Move extended property comments to create table comment I want to convert / import extended properties of table columns of a lot of tables to simple comments next to the table definition scripts columns in the table definition of a new SSDT database project in order to get rid of the extended properties in future but keep the column description information.

The usage of SSDT with source control makes the extended properties in the deployed version of the database obsolete.

So the description info of this:

EXECUTE sp_addextendedproperty @name = N'MS_Description', @value = N'Customer article number.', @level0type = N'SCHEMA', @level0name = N'dbo', @level1type = N'TABLE', @level1name = N'OrdPos', @level2type = N'COLUMN', @level2name = N'ArticleNo';

should get added to the columns definition like to this:

CREATE TABLE [dbo].[OrdPos] (
    [ArticleNo] VARCHAR(10) NOT NULL  -- Customer article number.
...

The Create Table scripts have already been created. The extended properties are all included in those scripts below the create table statements (as imported into the project). It is way too much to do it manually.

Did by chance anyone already have a similar task to solve with a good approach for some kind of automation?

There is no need to change from extended props to plain comments. Since I figured out that the descriptions are displayed in the visual designer part of the table in the SSDT project's tables, this is not really required to change.

This article by Jamie Thomson explains the details and that the descriptions of the columns are getting visible there as soon the column "Description" in the designer is enabled. Also, they are automatically created into the create script if entered in the designer what is pretty cool.


Syntax help in SQLPLUS Does Oracle have any builtin SQL syntax help.

I find I may have forgotten a few statements and sometimes have no Internet access.

I am using Oracle 11g.

No

You can get a list of reserved words by typing help reserved words, but that's about it.


SSPI error and DotNetMethod exception while connecting to the SQL DB using powershell script I wrote the following script with the original connection names and credentials.

$SQLServer = "servername"
$SQLDBName = "dbname"
$SqlQuery = "select * from tbl_Employee "

$SqlConnection = New-Object System.Data.SqlClient.SqlConnection
$SqlConnection.ConnectionString = "Server = $SQLServer; Database = $SQLDBName;
User ID= xyz; Password= abc; trusted_connection=true;"

$SqlCmd = New-Object System.Data.SqlClient.SqlCommand
$SqlCmd.CommandText = $SqlQuery
$SqlCmd.Connection = $SqlConnection

$SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
$SqlAdapter.SelectCommand = $SqlCmd

$DataSet = New-Object System.Data.DataSet
$SqlAdapter.Fill($DataSet)

After hitting enter I get the following error:

Exception calling "Fill" with "1" argument(s) :"Cannot generate SSPI context."
At line:1 char:17
+$SqlAdapter.Fill <<<< ($DataSet)
+CategoryInfo: NotSpecified: (:)[],MethodInvocationException
+FullyQualifiedErrorId: DotNetMethodException

I am trying to connect to the client's database using the given credentials but with the above code, its taking the windows credentials by default.

Connection String should instead read:

DRIVER=SQL Server;Server=$SQLServer;Database=$SQLDBName;UID=xyz;PWD=abc;


What are hypothetical indexes? They seem to be created by tools like DTA.

For practical purposes they could be deleted, ignored or chosen to be created, but until then, they have no direct influence on the database.

The hypothetical indexes are not actually there and can be ignored.  They don't contain any data and can be safely deleted.


Does Postgres automatically rotate WAL files out (pg_xlog) if archive_mode is off? I'm relatively new to PostgreSQL administration and I'm attempting to pick up the nitty gritty details as I go. My question is, assuming I have no need for disaster recovery and I don't turn on archiving by setting up archive_mode = on, does Postgres have an internal mechanism to rotate WAL files out of pg_xlog? if so, what is the frequency?

On a slightly different note, what is the impact of setting archive_mode = on but not configuring an archive_command - is there a default location Postgres will attempt to archive to?

If archive_mode = off, PostgreSQL will delete old WAL files as soon as they are older than the latest checkpoint. These checkpoints occur by default at least every 5 minutes, so there should never be many old WAL files around.

If you set archive_mode = on, a WAL file are only deleted once archive_command has returned success for that file. An empty archive_command should always do that immediately. It is a tradition to set archive_command = '/bin/true' to indicate that you temporarily disabled archiving.


need to replace the blank value as 0 need to replace the blank column  into 0
for example the table is

i have tried this query to bring the output
but it is taking more number of time because i have put this table in to procedure

instead of using 'case' can we bring the output with any other concept

select
case when hail_n like '' then '0' else hail_n end as Hail_N,
case when hail_s like '' then '0' else hail_s end as Hail_S,
case when hail_e like '' then '0' else hail_e end as Hail_E,
case when hail_w like '' then '0' else hail_w end as Hail_W,
case when wind_n like '' then '0' else wind_n end as Wind_N,
case when wind_s like '' then '0' else wind_s end as Wind_S,
case when wind_e like '' then '0' else wind_e end as Wind_E,
case when wind_w like '' then '0' else wind_w end as Wind_W
from vw_hail_wind_directions

thanks

There is no magic; if you have 8 columns to output then you'll need to have 8 expressions to change the output in any way. (Have you considered normalization?)

A slightly less verbose approach would be:

SELECT
  Hail_N = COALESCE(NULLIF(hail_n, ''),'0'),
  Hail_S = COALESCE(NULLIF(hail_s, ''),'0'),
  ...
FROM dbo.vw_hail_wind_directions; -- always use schema prefix and statement terminators


SQL Server job command line I have created couple of jobs on SQL SERVER out of SSIS packages , i can see command line for some of them start with /DTS and for some /SQL , even though all jobs creates in same version and perform data load operation only.
What is the difference between these options?

These options indicate the location where the package is stored. /SQL indicates the packages is stored in the msdb database and /DTS indicates the package is stored in the SSIS Package Store.

See here for more information.


ProxySQL in PXC and failing all nodes I'm going to install Percona XtraDB Cluster, and I have two questions about it:

1- Is it the last step?  https://www.percona.com/doc/percona-xtradb-cluster/LATEST/verify.html

Or installing ProxySQL is mandatory? I have only 3 nodes and not planning to add an extra node only for ProxySQL! And I think I don't even need it, my round-robin load-balancer is sending the requests to these servers randomly (without health controlling).

So if a node is down it can't even read/write to the DB.
in a nutshell, my application nodes are the same as my DB nodes, and a load-balancer in front of them, So I think I don't need ProxySQL, Am I?

2- What happens if all 3 nodes are down together, I think if this situation happens in a common Group Replication of MySQL, all the cluster collapses and all nodes are being removed from the cluster and we need to reconfigure the cluster manually again, Is the same problem exists in PXC or not?

No, installing ProxySQL is not mandatory
Do you mean all three nodes close down suddenly rather than close down gracefully? What version of Percona XtraDB Cluster are you using? If it is a version 5.6.19 or later, then the nodes will read the last state on startup and will try to restore primary component once all the members again start to see each other. This makes the PXC cluster to automatically recover from being powered down without any manual intervention. This information came from https://www.percona.com/blog/2014/09/01/galera-replication-how-to-recover-a-pxc-cluster/ and you may find other useful blogs around this subject and/or webinars on the Percona site.

Disclosure: I am from Percona


Need help optimizing mysql on a busy server I am managing a server for a very busy news website with lots of select queries (read-heavy), concurrent about 4000 users online at peak time. Around 1.4Mil pages views / day

The server has 42G RAM, running dual intel CPU 6 core 2.6G ( HT tech) = 2x6x2.6Ghz ( x2 in HT )
I have issues of the server overloading randomly up to +300 server load

Most of that time I see that SQL has taken all resources.. I'm trying to tweak it, but I'm out of my knowledge .

There are 3 databases, two just small 200mb but one main database for news is +4G in volume.
( web runs fast at load under 200 but it got sstuck, or error 502/504 if load increase )

Here is my.cnf

socket          = /var/lib/mysql/mysql.sock
skip-locking
key_buffer = 768M
max_allowed_packet = 16M
max_connections = 2000
wait_timeout = 20
connect_timeout = 10
tmpdir = /dev/shm/mysqlcache
tmp_table_size = 512M
table_cache = 2948
sort_buffer_size = 256M
read_buffer_size = 16M
read_rnd_buffer_size = 256M
myisam_sort_buffer_size = 256M
thread_cache_size = 768
query_cache_type = 1
query_cache_size = 2048M
query_cache_limit = 16M

# Try number of CPU's*2 for thread_concurrency
thread_concurrency = 24

Any suggestions very well come..

If you have frequent updates, then you should be using InnoDB as with MyISAM the whole table gets locked.

If you have so much RAM, you could set up large buffers to keep your tables completely in memory, which should give you a big improvement in performance.

Try turning on the slow query log to capture slow queries and queries not using indexes. You could use Percona's pt-query-digest tool to analyze the slow query log. Try tuning the slowest queries first and adding any indexes if necessary.


SQL Server merge replication across domains I'm getting the following message when trying to set up merge replication on SQL Server 2008 R2:

  The Merge Agent failed to locate the partitioned snapshot for this
  subscription in the expected location. If the publication does not
  support Subscriber-requested snapshot generation, ensure that the
  partitioned snapshot for this subscription has been generated.

I've tried adding the following SQL to the run agent step of the snapshot agent

-Output C:\Temp\OUTPUTFILE.txt -Outputverboselevel 2

but that just seems to make the agent crash with the following message:

  The replication agent has not logged a progress message in 10 minutes.
  This might indicate an unresponsive agent or high system activity.
  Verify that records are being replicated to the destination and that
  connections to the Subscriber, Publisher, and Distributor are still
  active.

All ideas gratefully received.

This turned out to be to do with the permissions on the snapshot folder. I added a user to each server with the same credentials and ran the subscription under that account.

Still don't know why I couldn't get verbose logging to happen, but in the end I didn't need it.


MariaDB on Windows Server 2012 R2 - out of memory I have two servers, one with Windows Small Business Server 2008 and the other runs Windows Server 2012 R2. The servers have running multi master replication. Both uses MariaDB 10.2.6. On the old 2008 everything works fine, even that the database is 32-bits. But on the 2012 I get frequently "Out of memory", the database uses only ~450MB and the system have 6GB free memory when the error shows up. Here is my complete my.ini, I tried already everything:

[client]
# password       = your_password
port            = 3306
socket          = "D:/OCS/MariaDB/mysql.sock"
plugin-dir      = D:/OCS/MariaDB/lib/plugin

# Here follows entries for some specific programs

# The MySQL server

[mysqld]
tmp_table_size=128M
performance_schema = off
default-storage-engine = myisam
skip-innodb
skip-performance_schema
local-infile
query_cache_type = 1
query_cache_limit = 10M
query_cache_size = 128M
port= 3306
socket = "D:/OCS/MariaDB/mysql.sock"
basedir = "D:/OCS/MariaDB"
tmpdir = "D:/OCS/MariaDB/tmp"
datadir = "D:/OCS/MariaDB/data"
pid_file = "mysql.pid"
# enable-named-pipe
key_buffer = 16M
max_allowed_packet = 1M
sort_buffer_size = 512K
net_buffer_length = 8K
read_buffer_size = 256K
read_rnd_buffer_size = 512K
myisam_sort_buffer_size = 8M
log_error = "mysql_error.log"
event_scheduler = ON
server_id=1
log-basename=2
log_bin=mysql-bin
max_binlog_size=64M
max_relay_log_size=64M
relay_log_space_limit=64M
auto-increment-increment = 2
auto-increment-offset = 2
slave-skip-errors=1062
# Change here for bind listening
# bind-address="127.0.0.1"
# bind-address = ::1          # for ipv6

# Where do all the plugins live
plugin_dir = "D:/OCS/MariaDB/lib/plugin/"

innodb_data_home_dir = "D:/OCS/MariaDB/data"
innodb_data_file_path = ibdata1:10M:autoextend
innodb_log_group_home_dir = "D:/OCS/MariaDB/data"
#innodb_log_arch_dir = "D:/OCS/MariaDB/data"
## You can set .._buffer_pool_size up to 50 - 80 %
## of RAM but beware of setting memory usage too high
innodb_buffer_pool_size = 16M
#innodb_additional_mem_pool_size = 2M
## Set .._log_file_size to 25 % of buffer pool size
innodb_log_file_size = 5M
innodb_log_buffer_size = 8M
innodb_flush_log_at_trx_commit = 1
innodb_lock_wait_timeout = 50

## UTF 8 Settings
#init-connect=\'SET NAMES utf8\'
#collation_server=utf8_unicode_ci
#character_set_server=utf8
#skip-character-set-client-handshake
#character_sets-dir="D:/OCS/MariaDB/share/charsets"

[mysqldump]
quick
max_allowed_packet = 16M

[mysql]
max_execution_time = 5000
local-infile
no-auto-rehash
# Remove the next comment character if you are not familiar with SQL
#safe-updates

[isamchk]
key_buffer = 20M
sort_buffer_size = 20M
read_buffer = 2M
write_buffer = 2M

[myisamchk]
key_buffer = 20M
sort_buffer_size = 20M
read_buffer = 2M
write_buffer = 2M

[mysqlhotcopy]
interactive-timeout

I am running out of ideas why this happens.. I have tested if the system have some limitations but a small python program allocates over 2GB of RAM with no problems. Both servers are running the same config - on the 2008 the memory usage goes up to 1.5GB with no problems.

The 32bit is the culprit. Due to a strange rounding up when calculating buffers, the server would allocate almost twice as much memory (3GB instead of 1.5GB), leaving only 1GB address space for anything else .It is fixed in 10.2.7 and later, the bug report is here https://jira.mariadb.org/browse/MDEV-12097


nest SELECT in INSERT I'm trying to do something like:

INSERT INTO stories
   (vid, ip, body, timestamp)
VALUES (
   (SELECT vid+1 FROM stories ORDER BY vid DESC LIMIT 1),
   INET_ATON('127.0.0.1'),
   'test',
   NOW()
)

but I'm having a problem figuring out how to nest a SELECT statement's returned data into an INSERT statement. I need to figure out how to get the highest existing vid and increment it by 1.

Any ideas?

use an explicit SELECT, not nested

INSERT INTO stories
   (vid, ip, body, timestamp)
SELECT
   COALESCE(MAX(vid),0)+1,    --max always returns one row if no group by
   INET_ATON('127.0.0.1'),
   'test',
   NOW()
FROM stories
ORDER BY vid DESC LIMIT 1


Moving Partitioning Files To Another Disk Drive In SQL Server There is a production table which was partitioned by someone in our company. Some partitioning files was wrongly located to different disk drive.

I have to move those files to the disk where they have to be. Those partitioned files for 2020 and 2021 are on production. How can I move those files to the main disk drive without stopping the database or services?

To move a data file, if you don't have a maintain window, you should perform several steps:

Add a new file to the filegroup (Add Data or Log Files to a
   Database):

ALTER DATABASE [YourDatabaseName]
ADD FILE (
      NAME = N'NewFileLogicalName'
    , FILENAME = N'C:\Data\NewFileName.ndf'
    , SIZE = 8MB
    , FILEGROWTH = 128MB
) TO FILEGROUP [YourFilegroupName];

Empty the old file (DBCC SHRINKFILE):

DBCC SHRINKFILE (N'OldFileLogicalName' , EMPTYFILE);

Remove the old file (Delete Data or Log Files from a Database):

ALTER DATABASE [YourDatabaseName]
REMOVE FILE OldFileLogicalName;

If a logical name matters (ALTER DATABASE File and Filegroup Options):

ALTER DATABASE [YourDatabaseName]
MODIFY FILE (
    NAME = N'NewFileLogicalName'
    , NEWNAME = N'OldFileLogicalName'
);


sp_BlitzIndex v 4.5 (2016-11-15) failing with collation error Posting this in case anyone else runs into this issue... (fix below)
After updating to SQL 2016 SP1, I also updated to the latest version of the sp_Blitz procs. Unfortunately, sp_BlitzIndex now blows up in the 'Gathering Computed Column Info.' section around line 1586.

Here is the error text:

Gathering Computed Column Info.
Failure populating temp tables.
Msg 2787, Level 16, State 1, Procedure sp_BlitzIndex, Line 1620 [Batch Start Line 4]

Invalid format specification:
    '%.%' THEN 1 ELSE 0 END AS is_function,
    'ALTER TABLE ' + QUOTENAME(s.name) + '.' + QUOTENAME(t.name) +
    ' ADD ' + QUOTENAME(c.name) + ' AS ' + cc.definition  +
    CASE WHEN is_persisted = 1 THEN ' PERSISTED' ELSE '' END + ';' AS [column_definition]
    FROM    [Production-db].sys.computed_columns AS cc
    JOIN    [Production-db].sys.columns AS c
    ON      cc.object_id = c.object_id
    AND cc.column_id = c.column_id
    JOIN    [Production-db].sys.tables AS t
    ON      t.object_id = cc.object_id
    JOIN    [Production-db].sys.schemas AS s
    ON      s.schema_id = t.schema_id
    OPTION (RECOMPILE);'.

Msg 50000, Level 16, State 1, Procedure sp_BlitzIndex, Line 1624 [Batch Start Line 4]

Production-db database failed to process.
Cannot resolve collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "Latin1_General_100_CI_AS_KS_WS_SC" in add operator occurring in SELECT statement column 11.

The solution was to add a COLLATE statement to line 1599 (line 1605 in the delivered script file):

CASE WHEN is_persisted = 1 THEN '' PERSISTED'' ELSE '''' END + '';''  collate SQL_Latin1_General_CP1_CI_AS AS [column_definition]

We've been working together on this error over here:

https://github.com/BrentOzarULTD/SQL-Server-First-Responder-Kit/issues/607

Turns out that the problem is contained databases. If you have a contained database, sp_BlitzIndex fails. (I've never actually seen a contained database in the wild before, so that's kinda amazing - wonder how long this has been broken.)


Inserting Serbian Latin and Cyrillic characters I am using SQL Server 2012 Express as part of my asp.net development workstation. Database and table collation is set to Latin1_General_CI_AI. Attribute types are nvarchar(max) and varchar(max).

When I pass a string containing Serbian Latin characters žđšćč žđćčžđšćčŽĐŠĆČ, the model from my asp.net application passes the string as it is, but the database is not accepting it, writing ždšcc ždccždšccŽÐŠCC into the table instead.

I also tried with Cyrillic string, but ????? ????? is written into database.

I am using MVC5 and EF6. I don't have parameters or datareader. I am just passing the model into the framework, and EF is doing everything else.

You have to specify that you are inserting wide character data:

CREATE TABLE #t (id INT,c1 VARCHAR(MAX),c2 NVARCHAR(MAX));
INSERT INTO #t VALUES(1,'žđšćč žđćčžđšćčŽĐŠĆČ','žđšćč žđćčžđšćčŽĐŠĆČ');
INSERT INTO #t VALUES(2,N'žđšćč žđćčžđšćčŽĐŠĆČ',N'žđšćč žđćčžđšćčŽĐŠĆČ');
SELECT * FROM #t;
DROP TABLE #t;

Result:


Where to install or use SQL Server? New to the database side of IT and I have a few questions...

I'm trying to figure out what servers and databases I can access through SSMS.

What is the best practice for installing/using SQL Server Management Studio?
Should I install it on my laptop, then connect to servers remotely?
Or install it on one of our SQL Servers and access with a remote desktop connection to that server from my laptop?

Also, do all servers have databases that can be accessed or do they have to be "SQL Servers"?

Thank you for replies in advance!

What is the best practice for installing/using SQL Server Management Studio?

I recommend installing SSMS on your laptop/desktop, and connecting to servers remotely. In general, I like to minimize the number of situations that require me to RDP into a server.
(That being said, I generally install SSMS on the server at the same time I install a SQL instance. But I don't use the server installation of SSMS except for emergencies.)

Do they have to be SQL Servers?

SSMS can connect to a variety of SQL products, including the Database Engine, Analysis Services, integration Services, Reporting Services, and Azure versions of SQL. But it can't connect directly to other database systems, like Oracle, DB2, or Access. You can, however, create Linked Servers within SQL that will allow a SQL instance to talk to non-SQL databases.


Can I Set The Value of App_Name() AFTER Login? I've got an older application that is connecting via ODBC to my database server.  I want to discover what the Application Name is inside a stored procedure.  I'm not able to set the Application Name (or the ProgramName value in xp_who2) at login due to the ODBC ware not supporting it.  Is it possible to set that value after login via some "set" command?

I understand there are other ways around this (like calling my stored procedure with the application name) but I'm hoping to avoid that as it would require more than one coding change, and rather do something for this session just after login.

What about using SET CONTEXT_INFO? It takes a varbinary(128) value, but you can easily cast that to/from varchar or whatever is appropriate. Read it back from sysprocesses.

http://msdn.microsoft.com/en-us/library/aa259199(v=sql.80).aspx


Deleting rows from a table with a very small amount of expansion space I have multiple tables that I have backed up the data in, and now am deleting the old data for the time, archiving is not an option due to space constraints at this time.  I have tried running the following:

WHILE 1 = 1
BEGIN
    DELETE TOP ( 4000 )
    FROM    [OLD_TABLE] WITH (TABLOCKX)
    WHERE   [Date] < '2014-01-01 00:00:00'
    IF @@ROWCOUNT = 0
        BREAK
END

this seems to be working, but VERY SLOWLY...   like 17+ hours, and only ~140,000 rows deleted.

The kicker here is this table and the others that need to be reduced are very large, around 50-60 million rows, and ~16-17 GB of space.  There is only 20 GB of space left on the physical disk.  I have looked at putting the data that I want to keep in an intermediate table, truncating and repopulating, but I am afraid that we will run out of space on the disk while these operations are completing.  This is a production database.  I have inherited this mess and I am trying to clean this up and am trying to get this DB cleaned up so we can stay functional until we can get new hardware in.

Are there any methods that I (a relative newcomer to database management) have not come across that may allow me to do this in a faster, more efficient manner?

*The table and column names have been anonymized.

EDIT

As things stand, the query:

DELETE TOP (4000)
FROM   [OLD_TABLE] WITH (TABLOCKX)
WHERE  [Date] < '2014-01-01 00:00:00'

...has to scan the whole heap table, testing each row it finds, until it eventually finds 4000 to delete. On the next iteration, the whole business starts again from square one. Assuming the scanning process is performed in the same (allocation unit) order each time, these scans will take longer and longer to find 4000 rows as time goes by.

Creating an index on the [Date] column will allow SQL Server to find the 4000 rows much more efficiently. It does add a small overhead to each delete (as the new nonclustered index needs to be maintained as well) but this is nothing compared to the effort that will be saved by not performing a scan each time. The index will also require a certain amount of space, but it should not be too large. You should create the following index before resuming your data removal process:

CREATE NONCLUSTERED INDEX <index_name>
ON dbo.OLD_TABLE ([Date]);

By the way, if the database has snapshot isolation or read committed snapshot isolation enabled (even if not actively used!), the TABLOCKX hint will not be enough to ensure that empty heap pages are deallocated. Your heap table may therefore contain many empty pages - a concern since you are so low on space.

The standard way to address this space management issue is to create a clustered index (or issue an ALTER TABLE REBUILD statement, but that requires SQL Server 2008). It seems that creating a clustered index might also not be an option for you, due to space constraints. There isn't an obvious way to resolve this right now, given the space issue.

One thing to keep an eye on while the delete is progressing is the space used by the transaction log. If the database is in FULL or BULK_LOGGED recovery, you will need to keep on top of backing up the log. If the database is using SIMPLE recovery, you may need to issue a manual CHECKPOINT from time to time to release transaction log space for reuse (otherwise the physical file might grow).

If you can get hold of some additional temporary storage, a better way might be to bulk export the data you want to keep to a safe location, drop the table, recreate it (preferably with a clustered index!) and reload the saved data. This is generally faster than the incremental-delete process, but it depends on your objectives and priorities.


How to subtract an object from another in PHP/MySQL? I am printing totalbooks with this code:

$query ="select sum(copies) as totalbooks from booksdetail";
$result=mysqli_query($con, $query);
while($row=mysqli_fetch_array($result)){
echo $row['totalbooks'];

and printing issued books with this code:

$query ="select count(id) as issuedbooks from issue";
$result=mysqli_query($con, $query);
while($row=mysqli_fetch_array($result)){
echo $row['issuedbooks'];

Now, I want to subtract issuedbooks from totalbooks.

Which code I should use?

$query ="select sum(copies) as totalbooks from booksdetail";
$result=mysqli_query($con, $query);
while($row=mysqli_fetch_array($result)){
$totalbooks = $row['totalbooks'];

$query ="select count(id) as issuedbooks from issue";
$result=mysqli_query($con, $query);
while($row=mysqli_fetch_array($result)){
$issuedbooks = $row['issuedbooks'];

$remainingbooks = $totalbooks - $issuedbooks;

PHP is off-topic for this site though, really.


Can I use older MySQL workbench with newest MySQL server? MySQL newbie here.

I'm using Ubuntu 16.04 (64-bit).
I've successfully installed MySQL Community Server 5.7 and MySQL Community Workbench 6.3.

Unable to find these answers in the MySQL forums. I've asked this on Stack Exchange and it was suggested that I ask here instead. I've searched on Ubuntu Forum and here also but did not find an answer.
One would think they are common questions but maybe not. Additionally, I cannot find a way to ask a new question on the MySQL Forum.
I've spent a ton of time reading/researching/installing/uninstalling and reinstalling MySQL and community workbench on Linux Ubuntu 16.04 (64-bit) and do not want to lose the momentum I've gained. I'm actually making forward progress after a week or so of trial and error working on this up to a few hours per day.
It appears that I cannot load MySQL Community Workbench 8.0 without Linux server installed. That seems to be one of the requirements if I understood correctly.

Can anyone tell me if MySQL Community Workbench 6.3 will work with MySQL Community Server 8.0 on Ubuntu 16.04 (64-bit)?
I would still like to upgrade and use MySQL community server 8.0 if I can.

Can I use MySQL Community Server 8.0 on Linux Ubuntu 16.04 32-bit?

I've spent many hours googling it and looking on MySQL Forums but couldn't find the answer.
Thanks much. ...Rick

In  my experience workbench 6.3 works only bad with mysql server 8.0.(it can not use the new authorisation)
The same goes for workbench 8.0 and the old Server version, some things work other not. You get also a warning when you connect.
So i have installed a Mysql 8 version and have a 6.3 in a vm, which i use to access 5.x server.


SQL Replication groups in Windows failover clusters RRS feed I have a scenario where there is a Cluster of 2 Windows servers that have SQL Standard. Between those servers is replication, and we need to use replication groups for each database, what is the best scenario and is it better to use DFS replication or SQL replication?

Thank you in advance

I believe, DFS replication is meant for files synchronization not for the databases. consider following options as database solutions/features in SQL Server.

SQL FCI
Since you already have SQL FCI in-place, the databases and data (program files) related to SQL Server are already in shared location which can be accessible to both nodes.

Availability Groups
Unlike FCI, availability groups hosted by stand-alone SQL instances, each instances called as replica, every replica maintains their own copy of databases and every change in the database replicated all database copies across replicas within a group. you can have 8 replicas, required enterprise license. WSFC required for auto-fail-over purpose.

Log Shipping
Log shipping works based on scheduled backups, which periodically backups the changes on primary server, secondary server copies backup files and restores regularly as per defined schedule.

SQL Replication
Above options considered as high availability solutions, but replication is something used for copying and distributing the database's objects


Use timestamp(or datetime) as part of primary key (or part of clustered index) I use following query frequently:

SELECT * FROM table WHERE Timestamp > [SomeTime] AND Timestamp < [SomeOtherTime] and publish = 1 and type = 2 order by Timestamp

I would like to optimize this query, and I am thinking about put timestamp as part of primary key for clustered index, I think if timestamp is part of primary key , data inserted  in table has write to disk sequentially by timestamp field.Also I think this improve my query a lot, but am not sure if this would help.

table has 3-4 million+ rows.
timestamp field never changed.
I use mysql 5.6.11

Anothet point is : if this is improve my query , it is better to use timestamp(4 byte in mysql 5.6) or datetime(5 byte in mysql 5.6)?

Consider the following:

An ideal index for this query would be KEY (publish, type, Timestamp). By your choice of query I get that filtering by publish and type is common.
If the above is correct, then data inserted into table will not be sequential by Timestamp anyhow
A PRIMARY KEY is unique. Are you certain you are not allowed to have two identical Timestamp values? Remember that resolution is 1 second, so it is very common to have same TIMESTMAP values for different rows.
I usually like an AUTO_INCREMENT columns as PRIMARY KEY (see Reasons to use AUTO_INCREMENT columns on InnoDB). Just use a normal index on (publish, type, Timestamp) is my general opinion.


SQL Server NULL Index I am new here and have a little question.

In a customer environment after checking his index fragmentation we see some "NULL" named index that we couldn't rebuilt or reorganize.

This was a SQL Server 2008 R2 Sp1. I have try looking on the net for this unnamed index but have no luck finding anything about it, can anyone please enlighten me on what are those.

These "indexes" are actually not indexes at all. They're called heaps, which is an internal structure that holds the table data without using any ordering mechanism like an index does.

Since there's no order, there's nothing to do as far as reorganizing or rebuilding goes, so these can be safely ignored as far as consideration for index maintenance. I would recommend using a 3rd-party solution for index maintenance though.

If you're new to index structures in SQL Server, I have a video here that explains them.


how to avoid duplicate record with certain conditon Heading ##I have a table with data  like below

+----+--------+-----------+----------------+
| id |  name  | testfield |     status     |
+----+--------+-----------+----------------+
|  1 | raju   |        11 |         import |
|  1 | raju   |        11 |         ticket |
|  2 | ravi   |        22 |         import |
|  2 | ravi   |        33 |         ticket |
|  3 | ramesh |        44 |         import |
|  3 | ramesh |        44 |         ticket |
|  4 | rahul  |        55 |         import |
|  5 | rudra  |        66 |         import |
+----+--------+-----------+----------------+

now i want output like below

+----+--------+-----------+----------------+
| id |  name  | testfield |     status     |
+----+--------+-----------+----------------+
|  1 | raju   |        11 |         ticket |
|  2 | ravi   |        33 |         ticket |
|  3 | ramesh |        44 |         ticket |
|  4 | rahul  |        55 |         import |
|  5 | rudra  |        66 |         import |
+----+--------+-----------+----------------+

explanation: If I have a row of status 'ticket' for a (id,name)-pair, this row should be selected. If I have a row of status 'import' for a (id,name)-pairbut no row with status 'ticket' for such a (id,name)-pair, the row should be selected.

For example the rows id 1,2 and 3  have status 'ticket' so they should be displayed. The rows with id 4 and 5 do not have a corresponding 'ticket'-row (with same (id,name) values) so 'import' should be displayed.

A possible solution based on set operations. From my understanding you have to display all rows that contain a 'ticket' in the status fields and all rows with an 'import' in the status field where there is not a similar row with a 'ticket' in the status field.

select id,name,testfield,status
from mytable
where status='ticket'
union
select id,name,testfield,status
from mytable where
(id,name) in
((select id,name
from mytable
where status='import'
)
minus
(select id,name
from mytable
where status='ticket'
)) ;

But sqlfiddle does not like it.
It says

  Oops! Something went wrong. Try it again [...]


MariaDB 10.1.38 - Specified key was too long; max key length is 767 bytes I have a table that has 3 columns:

id(int 11)  | user_id(int 4) | title(varchar 512)
____________|________________|___________________
     1      |        3       | Thing X
     2      |        3       | Something Else
     3      |        5       | Thing X

And i need to make a unique combination between user_id and title. For that i'm using this simple query:

ALTER TABLE posts ADD UNIQUE `unique_post`(`user_id`, `title`)

The charset for the title column is utf8mb4_unicode_ci. The database was created using the charset utf8mb4:

CREATE DATABASE learning_db CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

And the table type is InnoDB.

I've tried to set a few globals:

SET GLOBAL innodb_file_format=Barracuda;
SET GLOBAL innodb_file_per_table=on;
SET GLOBAL innodb_large_prefix=on;

But i'm getting the same error 1071:

Specified key was too long; max key length is 767 bytes

Is there anything else that i might be able to do?

P.S: if i set to index only the first 191 characters of the title i don't get that error:

ALTER TABLE posts ADD UNIQUE `unique_post`(`user_id`, `title`(191))

...but i have titles related to the same user that might be different only at the very last letter(aka last 4 bytes considering my charset)

P.S.S: i'm on localhost (using xampp).

Ok so after a weekend long of research the answer is very simple and i'm going to leave it here for others facing this same problem.

This answer is tested on MariaDB 10.1.38, db charset utf8mb4, table type InnoDB and table charset utf8mb4_unicode_ci:

Open my.ini and add this lines(if they already exist just edit everything after =) right after [mysqld]:

innodb_file_format = Barracuda
innodb_file_per_table = on
innodb_default_row_format = dynamic
innodb_large_prefix = 1
innodb_file_format_max = Barracuda

You can also set them trough the cmd/terminal:

On windows - if your XAMPP is located at c:\xampp\:

Open up a cmd and navigate to mysql's bin - in this case:

cd c:\xampp\mysql\bin

Autenticate:

mysql -h localhost -u root

Once you're authenticated run this queries(one at a time):

SET GLOBAL innodb_file_format = Barracuda;
SET GLOBAL innodb_file_per_table = on;
SET GLOBAL innodb_default_row_format = dynamic;
SET GLOBAL innodb_large_prefix = 1;
SET GLOBAL innodb_file_format_max = Barracuda;

But i recommend sticking to editing the my.ini(or .cnf) since if you are going for queries your options might by rewritten every time you restart your Apache.

You can read more on the InnoDB storage format: here

Also this article is very interesting in helping you to optimize your varchars brefore creating the unique keys: here


Postgresql 9.4.1 stuck all queries when making multi updates Before i used 9.3.5 and servers with ubuntu 12 with 32GB memory.

After upgrade to 9.4.1, with more power server 60GB memory on each in wall replication and ubuntu 14, started get db stucks when run multi update from resque/sidekiq background workers. resque/sidekiq work via pgpool 3.3

Queries can be as simple, update column where primary id = id. and complex, doesn't metter. usually average connections about less 300, but when queries stucks, count connections grow to maximum and i get

LOG:  process 16121 still waiting for ShareLock on transaction 2448707428 after 1000.121 ms
DETAIL:  Process holding the lock: 16139. Wait queue: 16121.
LOG:  process 16146 still waiting for ExclusiveLock on tuple (665346,11) of relation 997395 of database 16455 after 1000.102 ms
DETAIL:  Process holding the lock: 16121. Wait queue: 16146.
ERROR:  canceling statement due to lock timeout

then

FATAL:  sorry, too many clients already

then only restart help, when run restart got this

WARNING:  terminating connection because of crash of another server process
DETAIL:  The postmaster has commanded this server process to roll back the current transaction and exit, because another server process exited abnormally and possibly corrupted shared memory.
HINT:  In a moment you should be able to reconnect to the database and repeat your command.

Current settings,

Database size 190GB
max_connections = 400
shared_buffers = 13GB
work_mem = 19660kB
maintenance_work_mem = 2GB
effective_cache_size = 40GB

But i played with different, didn't help. On 9.3 all was fine. Nothing more in logs.

Also in one day i did pg_repack, on next day db was down with usual queries.

P.S.

I found problem, issue was in

commit_delay = 50

After set to 0, issue was solved

There was a bug with commit_delay in PostgreSQL 9.4.X. It was just fixed, and the fix will appear in version 9.4.5. See the thread on the PostgreSQL mailing list: http://www.postgresql.org/message-id/CACKh8C-tDjQUceMQeG3BUSHS5AA35SjvCD6HgRm-Vj7x5rXmeQ@mail.gmail.com


Select columns inside json_agg I have a query like:

SELECT a.id, a.name, json_agg(b.*) as "item"
  FROM a
  JOIN b ON b.item_id = a.id
 GROUP BY a.id, a.name;

How can I select the columns in b so I don't have b.item_id in the JSON object?

I have read about ROW, but it returns a JSON object like:

{"f1": "Foo", "f2": "Bar"}

I would need to remap the JSON object once it is fetched to match the proper column keys. I'd like to avoid that and keep original column names.

Unfortunately, there is no provision in SQL syntax to say "all columns except this one column". You can achieve your goal by spelling out the remaining list of columns in a row-type expression:
SELECT a.id, a.name
     , json_agg((b.col1, b.col2, b.col3)) AS item
FROM   a
JOIN   b ON b.item_id = a.id
GROUP  BY a.id, a.name;
That's short for the more explicit form: ROW(b.col1, b.col2, b.col3).
However, columns names are not preserved in row-type expressions. You get generic key names in the JSON object this way. I see 3 options to preserve original column names:
1. Cast to registered type
Cast to a well-known (registered) row type. A type is registered for every existing table or view or with an explicit CREATE TYPE statement. You might use a temporary table for an ad-hoc solution (lives for the duration of the session):
CREATE TEMP TABLE x (col1 int, col2 text, col3 date);  -- use adequate data types!

SELECT a.id, a.name
     , json_agg((b.col1, b.col2, b.col3)::x) AS item
FROM   a
JOIN   b ON b.item_id = a.id
GROUP  BY a.id, a.name;
2. Use a subselect
Use a subselect to construct a derived table and reference the table as a whole. This also carries column names. It is more verbose, but you don't need a registered type:
SELECT a.id, a.name
     , json_agg((SELECT x FROM (SELECT b.col1, b.col2, b.col3) AS x)) AS item
FROM   a
JOIN   b ON b.item_id = a.id
GROUP  BY a.id, a.name;
3. json_build_object() in Postgres 9.4 or later
SELECT a.id, a.name
     , json_agg(json_build_object('col1', b.col1, 'col2', b.col2, 'col3', b.col3)) AS item
FROM   a
JOIN   b ON b.item_id = a.id
GROUP  BY a.id, a.name;
Related:

Return as array of JSON objects in SQL (Postgres)

Similar for jsonb with the respective functions jsonb_agg() and jsonb_build_object().
For Postgres 9.5 or later also see a_horse's answer with a new shorter syntax variant: Postgres added the minus operator - for jsonb to say "all keys except this one key".
Since Postgres 10 "except several keys" is implemented with the same operator taking text[] as 2nd operand - like mlt commented.


How can I deny a user the ability to Alter DB files and options but still allow schema and data modifications? I'm establishing a shared development instance for multiple single-database applications.

I'm envisioning granting authorized users permissions to allow any schema and data modifications they need to perform, but want to prevent the same users from issuing ALTER DATABASE commands to ADD or MODIFY FILEs. This is to prevent any one developer from consuming additional disk space resources without consent from the DBA.

Looking at an approach using the least administrative effort, I'm thinking I should grant db_owner to the authorized users and add a database or server trigger to block ALTER DATABASE commands unless they come from DBA sessions.

Would this work or is there a better/safer approach?

You can put the users in the db_ddladmin, db_datawriter, and db_datareader roles.  This would prevent them from modifying permissions on the DB but either the db_securityadmin or db_accessadmin roles could be granted as appropriate keeping in mind that it's possible for db_securityadmin to be used for privilege escalation but db_accessadmin may not meet your needs.  As always, test the setup before deploying it to production to make sure it restricts what you want it to while allowing what you want it to.

If the goal is to prevent DB growth make sure autogrow is disabled on these databases.


SELECT statement with an explicit list of columns and then an asterisk (*) SQL Server allows to use SELECT statements similar to:

SELECT FirstName, LastName, City, *
FROM dbo.Customers

When I try to execute such a query on Oracle database, I get the following error:

  ORA-00936: missing expression
  00936. 00000 -  "missing expression"

Doesn't Oracle support such queries?

Only if you prefix the asterisk with the table name:

select dummy, dual.* from dual;

DUMMY DUMMY
----- -----
X     X

Which limits it's use to tables not joined with the using syntax:

select dummy, d1.* from dual d1 join dual d2 using(dummy)

SQL Error: ORA-25154: column part of USING clause cannot have qualifier
25154. 00000 -  "column part of USING clause cannot have qualifier"
*Cause:    Columns that are used for a named-join (either a NATURAL join
           or a join with a USING clause) cannot have an explicit qualifier.
*Action:   Remove the qualifier.

select d2.dummy, d1.* from dual d1 join dual d2 on(d1.dummy=d2.dummy);

DUMMY DUMMY
----- -----
X     X


SQL Server AFTER INSERT trigger I want this trigger to be fired after an insert is made with the text married on the marital_status column, this is what I have so far

ALTER TRIGGER [dbo].[marriage]
ON  [dbo].[applicant_personal_info]
AFTER INSERT
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
IF (SELECT [marital_status] FROM inserted) = 'married'
 BEGIN
     INSERT INTO [dbo].[applicant_marriage_info]([dom])
     VALUES('abc')
 END
END

The issue you'll run into here comes from the fact that SQL Server doesn't have the "FOR EACH ROW" triggers that Oracle does. You have to write your triggers to handle multi-row changes, i.e. the INSERTED or DELETED virtual tables can have more than one row in them.

If such an update were to happen, your trigger would fail, since (SELECT [marital_status] FROM inserted) would return multiple rows, and subqueries have to return a single value to be used in a direct comparison.

The trigger would probably have to look something like this:

CREATE TRIGGER [dbo].[marriage] ON  [dbo].[applicant_personal_info] FOR INSERT
AS
BEGIN
    SET NOCOUNT ON
    IF EXISTS (SELECT [marital_status] FROM INSERTED WHERE marital_status = 'married')
    BEGIN
        INSERT INTO [dbo].[applicant_marriage_info] ([dom])
        SELECT
            'abc' --Replace with whatever you're really inserting
        FROM INSERTED
        WHERE marital_status = 'married'
    END
END


Percona xtrabackup Prepare Fails I made a backup and according to Percona it completed successfully.  Now I'm trying to prepare it and having the following issue:

./xtrabackup_55  --defaults-file="/etc/my.cnf"  --defaults-group="mysqld" --prepare --target-dir=/backup/unprepared/2013_05_21_21_31_41_full
xtrabackup: cd to /backup/unprepared/2013_05_21_21_31_41_full
xtrabackup: This target seems to be not prepared yet.
xtrabackup: No valid checkpoint found.
xtrabackup: Error: xtrabackup_init_temp_log() failed.

I've been searching for answers for quite a while and not finding anything conclusive.  Any advice on how to resolve this?

Edit: per comment below, here is requested info:

xtrabackup_55 version 2.0.3 for Percona Server 5.5.16 Linux (x86_64) (revision id: 470)

cat /etc/my.cnf|grep innodb
innodb_data_home_dir = /data/mysql_data/
innodb_data_file_path = ibdata1:2000M;ibdata2:10M:autoextend
innodb_log_group_home_dir = /var/mysql/data/
innodb_file_per_table
innodb_buffer_pool_size = 5G
innodb_additional_mem_pool_size = 40M
innodb_log_file_size = 1G
innodb_log_buffer_size = 16M
innodb_log_block_size = 512
innodb_flush_log_at_trx_commit = 1
innodb_lock_wait_timeout = 50
innodb_thread_concurrency = 8
innodb_doublewrite_file = /var/mysql/innodb_doublewrite.buff
innodb_flush_method = ALL_O_DIRECT

Percona xtrabackup documentation indicates that you should point xtrabackup_55 to the backup-my.cnf file taken with your backup, rather than your live my.cnf file.

If you're doing the prep on the same server as the backup was taken, I'm not sure if it will make a difference.


Return Distinct Records by Category Based Multiple Column Values ID    MODEL    MODELSTATUS    CONTROL
11     100      GOOD           XN
24     100      TRENDING       BF
33     101      GOOD           XN
46     102      BAD            BF
50     103      BAD            XN
64     103      BAD            BF
77     104      PENDING        XN
89     104      TRENDING       BF
92     105      TRENDING       BF
93     105      TRENDING       XN

Given the data above, how would I return the results below.  There can either be 1 or 2 (at most) records with the same MODEL.  If there are 2 records for a MODEL the CONTROL will be different.

If MODESTATUS is 'Trending' return that record.  Else if MODELSTATUS is 'Pending' or 'Bad' return that record.  Else if there are 2 records for a MODEL where MODELSTATUS are the same return record with CONTROL is 'XN'

ID    MODEL    MODELSTATUS    CONTROL
24    100      TRENDING       BF
33    101      GOOD           XN
46    102      BAD            BF
50    103      BAD            XN
77    104      PENDING        BF
93    105      TRENDING       XN

This query gives the required output:

WITH data AS(
    SELECT [ID], [MODEL], [MODELSTATUS], [CONTROL]
        , r = ROW_NUMBER() OVER(PARTITION BY [MODEL] ORDER BY
            CASE WHEN MODELSTATUS = 'TRENDING' THEN 1 ELSE 0 END DESC
            , CASE WHEN MODELSTATUS IN ('PENDING', 'BAD') THEN 1 ELSE 0 END DESC
            , CASE WHEN [CONTROL] = 'XN' THEN 1 ELSE 0 END DESC
            , [ID] DESC
        )
    FROM @Table1
)
SELECT [ID], [MODEL], [MODELSTATUS], [CONTROL]
FROM data
WHERE r = 1;

See this SQL Fiddle.

I assume that this is only a sample table with easy to understand char values and real data is using int instead of varchar for status and control.

In term of performance and IO in real life, if this type of table gets bigger, it is wise to improve the design and replace varchar by tinyint ids for each Control and Status.

Output

ID  MODEL   MODELSTATUS CONTROL
24  100     TRENDING    BF
33  101     GOOD        XN
46  102     BAD         BF
50  103     BAD         XN
89  104     TRENDING    BF
93  105     TRENDING    XN


SQL Server Backup File Size I am regularly backing up the database using the SQL Server UI (Tasks --> Backup) with Backup Type:Full. Each time I backup, the .bak file gets bloated. If I delete the .bak file and do the backup, then 50 MB .bak file is created instead of the 500 MB file that I deleted.

What is the reason behind this?

You are appending the backup into same file so it is actually accumulating the backups. You need to give a new name each time you trigger backup from Tasks->Backup.

Use maintenance plan as it has timestamp for each backup file or else go with Ola Hellengren's solution.

https://ola.hallengren.com/


SQL Server Table Move Stalled I'm running a data migration. The process is moving data from a query on a SQL Server 2005 database to a SQL Server 2008 R2 database single table destination.

The source query is a join between 2 tables. I cannot change the query or the app that is running the data migration. The process is moving around 70 million rows from the source server to the destination.

The process runs very well for 2 hours, moving around 400,000 rows per minute and looks like the total processing time will be about 2 hours. At around 46 million rows the process slows to around 5,000 rows per minute (almost 100 times slower).

Why would this happen?

I've looked at the query plan and I don't see anything that I can tune. I have been seeing CXPACKET waits (using Adam Mechanics sp_WhoIsActive).

(2x: 9078ms) CXPACKET:1,
(1x: 1078ms) ASYNC_NETWORK_IO).

I changed the MaxDOP of the server to 1 in order to resolve the CXPacket but I haven't seen any change.

Shot in the dark: It means you client has reached the configured data/log file size and is now auto-growing them as a snail pace. Make sure you properly pregrow the data and the log files before your data transfer.


Simple PL/SQL code to throw NO_DATA_FOUND exception In a recent interview I was asked to write a simple PL/SQL code to throw NO_DATA_FOUND exception without actually raising it from the code. I am wondering what could be a simple pl/sql snippet to do so. Probably using dual?

Many ways, but as you suggested, you can use DUAL:

CREATE PROCEDURE NODATAFOUND
AS
   nowt VARCHAR(10);
BEGIN
   SELECT * INTO nowt FROM DUAL WHERE 1=0;
END;
/

It'll produce a ORA-01403: no data found when executed.


Temporarily disable checking of constraints I'm bulk inserting rows into a database and I'm finding (through the query analyser) that the foreign key constraints are taking about 20% of the insertion time. I've tried creating a separate index on the tables containing just the key so that the check could hopefully just do an index seek rather than a clustered index seek but this didn't seem to work.

The next thing I was planning on trying was to disable to check constraints temporarily so I was wondering if there is a way to do this on a per transaction basis.

Failing that I will probably try the performance with the constraints removed as they are also enforced in our DataAccess layer so in theory are redundant.

This SO question contains a possible solution, but it's more permanent than temporary.

In order to get your new narrower index used for foreign key constraint validation you will need to drop and re-create the foreign keys.

You will then see in sys.foreign_keys that they are bound to the better index and it will use that for validation.

NB: This isn't documented anywhere but is definitely the way it appears to work!


Logging security events in MSSQL I need to log events of admin changing someone's password, and user changing his password. How do I do that?
I've found a nice solution based on Service Broker, however the resulting table only contains the login of account that made the change, not the affected account.
Could anyone tell me how to alter this part

INSERT dbo.PasswordChangeLog(LoginName)
SELECT @message_body.value('(/EVENT_INSTANCE/LoginName)[1]', 'sysname');

So that I'd insert the object of password change, too? I know I will have to ALTER dbo.PasswordChangeLog to add an extra column, of course.

You can extract much more data from the event instance in the Service Broker message to store in the table. Specifically, TargetLoginName should bring back the affected user for you, something like this should work:

SELECT @message_body.value('(/EVENT_INSTANCE/LoginName)[1]', 'sysname') AS [ActioningLogin],
  @message_body.value('(/EVENT_INSTANCE/TargetLoginName)[1]', 'sysname') AS [AffectedLogin]

More info here.


Removing a string prefix I have a string of numbers that I need to trim a portion from using TSQL.

The string of numbers will always start with 101 then it will have a set of 0s and a set of random numbers. Example:

1010000123456

I need to trim the 101 and the set of zeros.  This is probably simple but I'm having all kinds of issues because I don't have a specific character to reference to using a CHARINDEX and the possible combination of a 001 when the random numbers start that I need to keep is giving me issues using a PATINDEX with a SUBSTRING.

There are other ways to do it, eg STUFF or just some simple integer maths, but these do make certain assumptions about the string, eg

DECLARE @t TABLE ( rowId INT IDENTITY PRIMARY KEY, yourString CHAR(13) )

INSERT INTO @t VALUES
    ( 1010000123456 ),
    ( 1010001234567 ),
    ( 1010012345678 ),
    ( 1010123456789 ),
    ( 1011234567890 )

SELECT
    CAST( STUFF( yourString, 1, 3, 0 )  AS INT ) [stuff],   -- 2 functions
    CAST( yourString  AS BIGINT ) - 1010000000000 do_math,  -- 1 function
    SUBSTRING(yourString, 3+PATINDEX('%[^0]%', SUBSTRING(yourString, 4, LEN(yourString))), LEN(yourString)) AS ypercube     -- 5 functions
FROM @t


How do I change table used in from clause of a select based on udf parameter? I have two tables A and B (with exactly the same columns and overall structure) and I'm trying to create a TVF with a select inside it that chooses to use A or B based on a parameter passed to the TVF:

create function dbo.testing(@switch varchar(1))
returns @SomeData table (data int)
  begin
    insert into @SomeData
    select
      number
    from
      [[either A or B depending on the value of @switch]] <===== ???
  return
 end;

The arrow coming in from the right indicates the line I"m not sure how to handle.

What's the right way to do this?  I know I can't just slap a string in there so what's the best way to proceed?

You don't need a multi statement TVF here.

You can use an inline one

CREATE FUNCTION dbo.testing(@switch VARCHAR(1))
RETURNS TABLE
AS
    RETURN
      (SELECT number
       FROM   A
       WHERE  @switch = 'A'
       UNION ALL
       SELECT number
       FROM   B
       WHERE  @switch = 'B')

If the value passed for @switch is a variable or parameter (rather than a constant) you may find you get better plans if you use OPTION (RECOMPILE) with this. Particularly if the cardinality of A and B is very different (likely even without the hint only the relevant one will be accessed as the table access will be under a filter with a startup expression predicate).


Partial rollback doesn't decrement trancount Suppose I have an open SQL Server session, and do the following:

begin tran

insert into People (Id) values (1)

select @@TRANCOUNT -- Prints 1

save transaction tt

begin tran

select @@TRANCOUNT -- Prints 2

insert into People (Id) values (2)

Ok, so now I make a:

rollback tran tt

select @@TRANCOUNT -- Prints 2!

My question is maybe obvious:

Why partial rollback doesn't decrement @@TRANCOUNT increased by the inner transaction?

To commit changes I should do two commit commands. IMHO doesn't seems natural.

@@TRANCOUNT reports a count of BEGIN TRANSACTION statements, not active transactions. From a different perspective, it is reporting the depth of a nested transaction.

  @@TRANCOUNT (Transact-SQL)  Returns the number of BEGIN TRANSACTION
  statements that have occurred on the current connection. [source]

Misunderstandings of nested transactions and savepoint usage arise because nested transactions are not what we would expect them to be. To all intents and purposes there is no such thing as a nested transaction. The actions of a set of nested transactions are not committed until you issue the outer most COMMIT and a ROLLBACK will undo the actions of all.

Nested transactions exist to support transactions in procedures which could be called from external processes that may have started a parent transaction, from parent procedures that may have done the same, or where no prior transaction exists. They do not provide nested commit and rollback behaviour.


Export structure and data without a wizard Using Sql Server 2008 R2 (but I could possibly upgrade if required).

I want to generate scripts for the database structure as well as the data for a schema.

The Generate Scripts wizard does what I want but I need to be able to automate it (so needs to be called from T-SQL or alternatively external program).

How can I do this?

I know that Red Gate SQL compare has a command line interface so you could write and schedule a batch file to generate the scripts. Details here
You could then use SQLcmd to execute the scripts.


Help to decrese query time in huge table of 100M+ rows on Ms-SQL Server I am trying to get data using offset limit concept as follows:

CREATE PROCEDURE BaseUserIndex
    @Offset int,
    @Limit int,
    @SortCol int,
    @SortDir nvarchar(10),
    @Search nvarchar(255) = NULL
AS BEGIN
    SET NOCOUNT ON;
    SELECT id,
    name,
    address,
    city,
    FROM users
    WHERE deleted_at IS NULL
    ORDER BY
        case when (@SortCol = 0 and @SortDir='asc')
        then name
        end asc,
        case when (@SortCol = 0 and @SortDir='desc')
        then name
        end desc,

        case when (@SortCol = 1 and @SortDir='asc')
        then address
        end asc,
        case when (@SortCol = 1 and @SortDir='desc')
        then address
        end desc,

        case when (@SortCol = 2 and @SortDir='asc')
        then city
        end asc,
        case when (@SortCol = 2 and @SortDir='desc')
        then city
        end desc
    OFFSET @Offset ROWS
    FETCH NEXT @Limit ROWS ONLY
END

But, this code snippet is taking around 15 sec, to fetch data. Is there any way to improve this?

As I am new to Ms SQL. Is there any way to decrease this?

To make this work well you need to create a separate query for each sort order, and have indexes that support the OFFSET .. FETCH without needing to sort the whole table first.  For the query part you can use IF statements with static SQL, or dynamic SQL.  Here's how to do this with some filtered indexes and dynamic SQL:

--drop table if exists users
go
create table users(id int primary key, name nvarchar(200), address nvarchar(200), city nvarchar(200), deleted_at datetime2 null)
go
create index ix_users_city on users(city) where deleted_at is null
create index ix_users_name on users(name) where deleted_at is null
create index ix_users_address on users(address) where deleted_at is null
go

CREATE OR ALTER PROCEDURE BaseUserIndex
    @Offset int,
    @Limit int,
    @SortCol int,
    @SortDir nvarchar(10),
    @Search nvarchar(255) = NULL
AS BEGIN
/*
  exec BaseUserIndex 0,100,2,'asc'
*/
    SET NOCOUNT ON;

    declare @sql nvarchar(max) = concat(N'
    SELECT id,
    name,
    address,
    city
    FROM users
    WHERE deleted_at IS NULL
    ORDER BY ',
        case
        when (@SortCol = 0 and @SortDir='asc')  then N'name asc'
        when (@SortCol = 0 and @SortDir='desc') then N'name desc'
        when (@SortCol = 1 and @SortDir='asc')  then N'address asc'
        when (@SortCol = 1 and @SortDir='desc') then N'address desc'
        when (@SortCol = 2 and @SortDir='asc')  then N'city asc'
        when (@SortCol = 2 and @SortDir='desc') then N'city desc'
        end
        ,N'
    OFFSET ', @Offset, N' ROWS
    FETCH NEXT ', @Limit, N' ROWS ONLY'
    )

    --print @sql
    exec ( @sql )

END


MSSQL Stored procedure becomes slow after few days I am facing an issue on SQL Server in which my stored procedure becomes slow after couple of days.

Below is the sample of my stored procedure.

Could this be a caching issue on the server side? Can I increase the server's cache size to resolve the problem?

Normally the stored procedure returns data in one second

@START_VALUE int=null,
@END_VALUE int=null
@UID NVARCHAR(MAX)=null,
AS
BEGIN

 SELECT    dbo.TABLE1.ID,
ROW_NUMBER()  OVER (ORDER BY TABLE1.UPDATED_ON desc) AS RN,
CONVERT(VARCHAR(10), dbo.TABLE1.DATE, 101) AS TDATE,
CATEGORY = (
        SELECT TOP 1 COLUMN1
        FROM TABLE5 CT1
        WHERE TABLE1.CATEGORY = CT1.CATEGORY_ID
   ),
TYPETEXT = (
        SELECT TOP 1 COLUMN1
        FROM TABLE6 CT1
        WHERE TABLE1.TYPE = CT1.TYPE_ID
   ),
IMAGE = STUFF(( SELECT DISTINCT ',' + CAST(pm.C1 AS varchar(12))
                FROM TABLE2 pm
                WHERE pm.ID = TABLE1.ID AND pm.C1 IS NOT NULL AND pm.C1 <> ''
                FOR XML PATH('')),
              1, 1, '' ) INTO #tempRecords
 FROM dbo.TABLE1
 WHERE ((@UID is null OR  dbo.TABLE1.ID = @UID )
 ORDER BY TABLE1.UPDATED DESC

 SELECT @count = COUNT(*) FROM #tempRecords;

 SELECT *, CONVERT([int],@count) AS 'TOTAL_RECORDS'
 FROM #tempRecords
 WHERE #tempRecords.RN BETWEEN CONVERT([bigint], @START_VALUE) AND      CONVERT([bigint], @END_VALUE)

END

   GO

I would look at your plan cache to see what's happening there. Could be a bad plan, could be multiple single use plans are being generated? Look closer at these DMVs, sys.dm_exec_cached_plans and sys.dm_exec_query_stats, they will give you more insight. This is a small query that I use:

USE master
GO
SELECT
    t1.objtype AS ObjectType,
    t1.cacheobjtype AS CacheType,
    COUNT_BIG(*) AS TotalPlans,
    SUM(CAST(t1.size_in_bytes AS DECIMAL(18, 2))) / 1024 / 1024 AS TotalSizeInMB,
    AVG(CONVERT(bigint, t1.usecounts)) AS AvgUseCount,
    SUM(CAST((CASE WHEN t1.usecounts = 1 THEN t1.size_in_bytes
    ELSE 0
    END) AS DECIMAL(18, 2))) / 1024 / 1024 AS SingleUseSizeInMB,
    SUM(CASE WHEN t1.usecounts = 1 THEN 1
        ELSE 0
        END) AS CountOfSingleUsePlans
FROM sys.dm_exec_cached_plans t1
GROUP BY
    t1.objtype,
    t1.cacheobjtype
ORDER BY 3 DESC


Can you optimize a view with many UNION ALLs to external databases? Given these views in a Sql Server 2012 database:

create view vManyUnions as
    select 1 as p, a from vSubQuery_1
    union all
    select 2 as p, a from vSubQuery_2
    union all
    select 3 as p, a from vSubQuery_3
    ...
    union all
    select n as p, a from vSubQuery_n

create view vSubQuery_1 as
    select a from [externalDb].[dbo].[someTable] where b = something

create view vSubQuery_2 as
    select a from [externalDb].[dbo].[someTable] where b = somethingElse

create view vSubQuery_3 as
    select a from [externalDb].[dbo].[someTable] where b = somethingElseStill

...

create view vSubQuery_n as
    select a from [externalDb].[dbo].[someTable] where b = youGetTheIdea

And given this query:

select * from vManyUnions where p = 1

What optimizations, if any, can be made such that Sql Server 2012 only evaluates vSubQuery_1 when running the query?

*Note again, the sub query views call external databases.

FOLLOW UP: This was an example I contrived from a proprietary, much more complicated case (~1K subviews) where the execution planner includes more subqueries than the one subquery I expect. Thanks to those that replied and comfirmed that Sql should optimize queries against this type of view. It made me realize I was being lazy and hadn't actually tested my basic assumptions on how the optimizer works.

So I did that. I built a simple proof of concept and confirmed very fast that Sql optimizes the query. Following that, I worked more with the real-world view that still doesn't optimize. I found that, strangely, the planner is optimizing out many of the subviews. There are particular subviews that the planner always includes though. At this point I don't know why the planner always includes these particular subviews, any ideas? ...otherwise I'll post something if we figure it out.

Using select * from vManyUnions where p = 1 will already avoid having to access the other tables, because of the contradiction optimisation.

If you look at the actual execution plan, you should see that only one of the external tables is actually accessed. Any others would have the NumberOfExecutions property set to zero.

You can see me demonstrate this in a talk that was recorded in 2009 for SQLBits, at http://bit.ly/Simplification


Postgres view column references Using the Postgres information_schema or system catalogs, is it possible to determine what table columns are referenced by the columns in a view?

information_schema.view_column_usage provides information on columns that are used in a view, not necessarily selected as a column.

pg_class and pg_attribute provide some info, but there doesn't seem to be a way to map columns from a view where an alias has been used.

Is is it possible to get the column references of the selected columns in a view?

PostgreSQL doesn't track which view column depends on which object. It is the view as a whole that is tracked.

But PostgreSQL tracks on which table columns (and other objects) a view depends: Each view is related to its query rewrite rule in pg_rewrite, and the query rewrite rule has a dependency on the table columns (and other objects) used in the view. These dependencies are tracked in pg_depend.

Putting the pieces of the puzzle together is straightforward, but takes a while. I have written a blog about this recently.


How to link different databases? We are a company doing surveys with different kind of questionnaires. There is a company in Mumbai (MySQL) specialized in Type 1 questionnaire and one in Peru (SQL-Server) specialized in Type 2. I also work with a CRM system as Salesforce and want to connect these data as well.

I thought about getting all these data into one in-house database using ETL. I was curious if it is possible to virtually connect these data warehouses so I do not need a DW in-house and can query the data where they are stored but can connect to them. Is this possible? I could not find anything but maybe I am looking with the wrong vocabulary.

If this were me, I'd use SSIS and import everything into a DW locally. Then you can do whatever you want/need with minimal impact to all of those remote systems.


pg_start_backup and small max_wal_size What will happen, if max_wal_size gets exceeded during backup?

Specifically, let's consider the following scenario on master:

Set max_wal_size to a very low number.
Run SELECT pg_start_backup('label', true); to create a checkpoint.
Perform lots of transactions, so that max_wal_size is exceeded.

If I am right, PostgreSQL cannot process WAL files, because doing so would pollute the checkpoint. On the other hand, no new WAL files can be created, because of the limit.

max_wal_size is a soft limit - under many scenarios it gets crossed.
Specifically as for pg_start_backup it just commits and outputs the wal location - if you have archiving in place than you have nothing to worry about.

Starting the backup will create more wal files but doesn't prevent rolling them - that's why you need the archiving as all wal files generated in the duration of the backup are needed for a successful restore.

See: max_wal_size documentation
Also: backup documentation


Restore an oracle dmp file I've been given an oracle DMP file that was created on a solaris system which I need to restore to a windows system.

I've created the DB with the correct blocksize, but when I try and do the restore it complains that it can't create the folders.

I've created the folder in Windows and in my imp command i've specified the directory, but it still fails.

How can I resolve this?

Thanks

Here is the full error message

IMP-00003: ORACLE error 1119 encountered
ORA-01119: error in creating database file '/home/oradata/d_test01/tools01.dbf'
ORA-27040: file create error, unable to create file

What do i need to specify in the restore command to change the restore location.  As I said previously the backup was created on a solaris based system and I have trying to restore it on Windows

All i've been given is the dmp file.  It was created by a consultant who no longer does work for us.

Hope someone can help.

This is a full database import which also includes tablespace definitions. Find out from the original Solaris database what are all the target tablespace names and sizes. Pre-create the tablespaces manually using the datafile paths of your choice (C:\data\) before performing the import. You will see IMP-00015: following statement failed because the object already exists: your tablespace which you can ignore.


Problems with Listener in AlwaysOn AG in AWS I have a 2 node AlwaysOn AG cluster with a single database being replicated between the 2 nodes. Windows Failover Clustering Services are installed on each node.

The AG is configured to allow connections for either node when it is primary but not to allow connections if the node is the secondary.

When I connect using the listener name, I randomly connect to the secondary node (which I would not expected to happen).

I am reviewing the IP addresses and subnets with my Infrastructure team to confirm that I have configured the AG correctly.

Is this a known problem with SQL Server 2016 SP2 CU6?

When I connect using the listener name, I randomly connect to the secondary node (which I would not expected to happen).

It really shouldn't happen, so it tells me something is not compliant in the environment. Given this is networking related, it could be to do with the DNS/cache settings or the way AWS does their internal networking and updates.

  Is this a known problem with SQL Server 2016 SP2 CU6?

No and I highly doubt it would even fall under SQL Server's purview. You'll want to take a network trace, at a minimum, from the client.

  AG connection resolution functions at the database level, not the server level.

Not quite. When using read only routing a database name is required, but to just connect to an availability group it's not needed. Connection "resolution" is not at the database level, in fact the listener is backed by your typical Network Name + IP resources in Windows Server Failover Clustering, so technically most of the listener functionality isn't even within SQL Server (since the DNS/IP point to the server).

  [...] imagine if you had two AG's set up with the same two nodes - how would the driver know which server to connect to?

The drivers asks DNS what addresses it has for the name, then depending on the driver and version of the driver it will attempt to connect to one or more of those addresses.


Table with 4 million records - different size and performance in development and production At my company we use SQL Express 2008 R2, which supports databases up to 10GB.

We have 2 databases with almost the same number of rows in all tables.
But, one of them is 4.5 GB in size (which is in the development department) and the other (the one in production) is 9.87GB.

The database under development was auto generated by inserting rows in bulk for testing purposes. While the database in production, slow almost 1 year to generate that amount of data.

One table in particular, has 4,700,000 rows, and make the following query:

SELECT colum1,column2 FROM table WHERE column1 = 2

Takes about 2 minutes to respond on the production database and 20 seconds in the development database.

I did a shrink in production database and it took about 20 minutes to complete, but after that, the size of the database was reduced to 7 GB.
I tried to rebuild the indexes, with right click, but it gives timeout and fails to finish the reconstruction.

My question is:
Why would 2 databases with the same amount of records be so different in size and what can I do to improve performance on production database?

Thanks.-

Never do shrink database as it increases fragmentation even more. And yes, it looks like sizes are different because of fragmentation, so you have lots of space that stores nothing (half filled pages). This is probably because of incorrect schema design and wrongly chosen clustered indexes. But fragmentation happens on production system and it's good idea to check for it and do index rebuild on a regular basis.

The error you get might appear of not enough memory so you could try SORT_IN_TEMPDB = ON option. Also consider REORGANIZE option as it is online and even if it fails, it will save all work done prior it fails. But REORGANIZE option just defragments leaf level of an index, not the entire tree.

If it doesn't help, the only way is to recreate database with scripts and fill it with production data but I hope it won't be necessary.


Query to select 'Customer_Num' with digit characters using regular expression I have a table with below structure:
Example_table(Customer_Num    varchar2(50))

Some Example data in this table :
        Example_table
----------------------------
        Customer_Num
           12445
           12345
           12ttd
           2376y
           23%%*
           23467

I want to select custoemr_num records with digit characters , so these records 12ttd , 2376y , 23%%*
should not appear in the final result . Considering the fact that I can not change the table structure , which one is the correct query ?
Q-1:
  select customer_num
  from Example_table
  where REGEXP_LIKE(customer_num, '[[:digit:]]')

Q-2:
  select customer_num
  from Example_table
  where REGEXP_LIKE(customer_num, '[0-9]')

Are there any special condition in which these queries might end up producing wrong results?
Thanks in advance.

Neither is correct because both only check for a single digit that can appear anywhere in the string.
A regex that only selects digits in the complete string would be:
where REGEXP_LIKE(customer_num, '^[0-9]+$')

^ anchors the expression at the beginning of the string (to avoid leading non-digits)
[0-9]+ matches multiple digits but at least one
$ anchors the expression at the end of the string (to avoid trailing non-digits)

There is no difference between using [0-9] and using [[:digit:]]


DB2 - how to call a stored procedure that returns a result set in another user defined table function I have a db2 stored procedure that takes in some parameters, gets some data from somewhere and then returns a result set through a cursor.

Now I want to write a table function in db2, that will call this stored procedure, read from the result set and return the data in the result set as a table (eventually I want to use this table function in a join).

I would like to know if this is permitted in db2 (we're using DB2 v10.5), i.e. execute a stored procedure in a table function and fetch and read from the result set from the stored procedure.
If so, what is the right syntax for calling the stored procedure and reading the result set inside a table function in db2?
Thanks!

What you want to accomplish is certainly possible by using a pipelined table function,
but it isn't necessarily a wise thing to do. For any non-trivial result set row-based
processing is always significantly slower than set-based processing. In addition to that,
by hiding your data access logic in a table function you deprive the query optimizer
of any information about the statistics and other physical characteristics of the underlying
tables and indexes. As a result, performance of such a chimera will lie anywhere  between
suboptimal and unacceptable.

Consider rethinking your approach; in most cases you should be able to implement your data retrieval and manipulation logic in a single query, may be wrapped in a view for convenience.

If you, however, insist on shooting yourself and those who come after you in your
collective feet, here's how you might go about doing that.

CREATE OR REPLACE PROCEDURE inner
LANGUAGE SQL
DYNAMIC RESULT SETS 1
READS SQL DATA -- (1)
BEGIN
DECLARE c1 CURSOR
WITH RETURN -- (2)
FOR SELECT tabschema, tabname FROM syscat.tables;
OPEN c1;
END

Footnotes:

The READS SQL DATA here is important; a table function cannot have side effects, and so
shouldn't the stored procedure it calls. We enforce this contract at compilation time.
Make sure Db2 knows we want data in the caller, otherwise it will optimize it away.

Then the function itself:

CREATE OR REPLACE FUNCTION smitf()
RETURNS TABLE (
    out_schema VARCHAR(128), out_name VARCHAR(128)
)
READS SQL DATA
BEGIN
DECLARE has_rows INT DEFAULT 1;
DECLARE schema_name VARCHAR(128);
DECLARE table_name VARCHAR(128);
DECLARE res1 RESULT_SET_LOCATOR VARYING;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET has_rows = 0;
CALL inner();
ASSOCIATE RESULT SET LOCATORS (res1) WITH PROCEDURE inner;
ALLOCATE cur1 CURSOR FOR RESULT SET res1;
FETCH cur1 INTO schema_name, table_name;
WHILE has_rows > 0 DO -- some bogus processing
    IF table_name NOT LIKE 'A%' THEN
        PIPE (LOWER(schema_name), LOWER(table_name)); -- magic is here
    END IF;
    FETCH cur1 INTO schema_name, table_name;
END WHILE;
RETURN;
END

and call it:

SELECT * FROM TABLE (smitf() CARDINALITY 100) WHERE ...

The CARDINALITY clause lets you tell the optimizer how many rows you expect the function to return, but it may not take this information into account, and even if it does, it may not help.


How does SQL Server know predicates are correlated? While diagnosing SQL Server 2008 R2 queries with poor cardinality estimation (despite simple indexing, up-to-date statistics, etc.) and hence poor query plans, I found a perhaps-related KB article:
FIX: Poor performance when you run a query that contains correlated AND predicates in SQL Server 2008 or in SQL Server 2008 R2 or in SQL Server 2012

I can guess what the KB article means by "correlated", e.g. predicate #2 and predicate #1 largely target the same rows.

But I don't know how SQL Server knows about these correlations. Does a table need a multi-column index containing columns from both predicates? Does SQL use statistics to check whether values from one column are correlated to another? Or is some other method used?

I'm asking this for two reasons:

to determine which of my tables and queries might be improved using this hotfix
to know what I should do in indexing, statistics, etc. to affect #1

Consider the simple AdventureWorks query and execution plan shown below. The query contains predicates connected with AND. The optimizer's cardinality estimate is 41,211 rows:
-- Estimate 41,211 rows
SELECT COUNT_BIG(*)
FROM Production.TransactionHistory AS TH
WHERE
    TH.TransactionID BETWEEN 100000 AND 168336
    AND TH.TransactionDate BETWEEN '2007-09-01' AND '2008-03-13';

Using default statistics
Given only single-column statistics the optimizer produces this estimate by estimating the cardinality for each predicate separately, and multiplying the resulting selectivities together. This heuristic assumes that the predicates are completely independent.
Splitting the query into two parts makes the calculation easier to see:
-- Estimate 68,336.4 rows
SELECT COUNT_BIG(*)
FROM Production.TransactionHistory AS TH
WHERE
    TH.TransactionID BETWEEN 100000 AND 168336;

The Transaction History table contains 113,443 rows in total, so the 68,336.4 estimate represents a selectivity of 68336.4 / 113443 = 0.60238533 for this predicate. This estimate is obtained using the histogram information for the TransactionID column, and the constant values specified in the query.
-- Estimate 68,413 rows
SELECT COUNT_BIG(*)
FROM Production.TransactionHistory AS TH
WHERE
    TH.TransactionDate BETWEEN '2007-09-01' AND '2008-03-13';

This predicate has an estimated selectivity of 68413.0 / 113443 = 0.60306056. Again, it is calculated from the predicate's constant values and the histogram of the TransactionDate statistics object.
Assuming the predicates are completely independent, we can estimate the selectivity of the two predicates together by multiplying them together. The final cardinality estimate is obtained by multiplying the resulting selectivity by the 113,443 rows in the base table:

0.60238533 * 0.60306056 * 113443 = 41210.987

After rounding, this is the 41,211 estimate seen in the original query (the optimizer also uses floating point math internally).
Not a great estimate
The TransactionID and TransactionDate columns have a close correlation in the AdventureWorks data set (as monotonically increasing keys and date columns often do). This correlation means that the independence assumption is violated. As a consequence, the post-execution query plan shows 68,095 rows rather than the estimated 41,211:

Trace flag 4137
Enabling this trace flag changes the heuristics used to combine predicates. Instead of assuming complete independence, the optimizer considers that the selectivities of the two predicates are close enough that they are likely to be correlated:
-- Estimate 68,336.4
SELECT COUNT_BIG(*)
FROM Production.TransactionHistory AS TH
WHERE
    TH.TransactionID BETWEEN 100000 AND 168336
    AND TH.TransactionDate BETWEEN '2007-09-01' AND '2008-03-13'
OPTION (QUERYTRACEON 4137);

Recall that the TransactionID predicate alone estimated 68,336.4 rows and the TransactionDate predicate alone estimated 68,413 rows. The optimizer has chosen the lower of these two estimates rather than multiplying selectivities.
This is just a different heuristic, of course, but one that can help improve estimates for queries with correlated AND predicates. Each predicate is considered for possible correlation, and there are other adjustments made when many AND clauses are involved, but that example serves to show the basics of it.
Multi-column statistics
These can help in queries with correlations, but the histogram information is still based solely on the leading column of the statistics. The following candidate multi-column statistics therefore differ in an important way:
CREATE STATISTICS
    [stats Production.TransactionHistory TransactionID TransactionDate]
ON Production.TransactionHistory
    (TransactionID, TransactionDate);

CREATE STATISTICS
    [stats Production.TransactionHistory TransactionDate TransactionID]
ON Production.TransactionHistory
    (TransactionDate, TransactionID);

Taking just one of those, we can see that the only extra information is the extra levels of the 'all' density. The histogram still only contains detailed information about the TransactionDate column.
DBCC SHOW_STATISTICS
    (
        'Production.TransactionHistory',
        'stats Production.TransactionHistory TransactionDate TransactionID'
    );

With these multi-column statistics in place...
SELECT COUNT_BIG(*)
FROM Production.TransactionHistory AS TH
WHERE
    TH.TransactionID BETWEEN 100000 AND 168336
    AND TH.TransactionDate BETWEEN '2007-09-01' AND '2008-03-13';

...the execution plan shows an estimate that is exactly the same as when only single-column statistics were available:


Getting the primary key columns off a table I have a postgresql 11 database and I am trying to follow this official post on getting the primary key column from a table. However I get an error saying that my table is not an existing column.

ERROR:  column "tablename" does not exist
LINE 10: ...* from pg_index WHERE indisprimary AND indrelid = "tablename"...
                                                              ^

The code looks like this:

SELECT a.attname, format_type(a.atttypid, a.atttypmod) AS data_type
FROM   pg_index i
JOIN   pg_attribute a ON a.attrelid = i.indrelid
                     AND a.attnum = ANY(i.indkey)
WHERE  i.indrelid = "tablename"::regclass -- <--- This is the problem
AND    i.indisprimary;

I believe the issue has something t do with the ::regclass cast as looking at the full table and replacing "tablename"::regclass with the indrelid number works fine, in my case it was 16490.

SELECT a.attname, format_type(a.atttypid, a.atttypmod) AS data_type
FROM   pg_index i
JOIN   pg_attribute a ON a.attrelid = i.indrelid
                     AND a.attnum = ANY(i.indkey)
WHERE  i.indrelid = 16490 -- "tablename"::regclass <--- replace with the number
AND    i.indisprimary;

Any ideas on why the official example is not working and how I can fix it?

https://stackoverflow.com/questions/1214576/how-do-i-get-the-primary-keys-of-a-table-from-postgres-via-plpgsql ...

if schema is needed please modify slightgly to

SELECT
  pg_attribute.attname,
  format_type(pg_attribute.atttypid, pg_attribute.atttypmod)
FROM pg_index, pg_class, pg_attribute, pg_namespace
WHERE
  pg_class.oid = 'MY TABLE'::regclass AND
  indrelid = pg_class.oid AND
  nspname = 'MY CLASS' AND
  pg_class.relnamespace = pg_namespace.oid AND
  pg_attribute.attrelid = pg_class.oid AND
  pg_attribute.attnum = any(pg_index.indkey)
  AND indisprimary


When and Who created linked server? I came across a linked server that is pointing to production from test environment with full access. Is there a way to find out who and when this linked server was created? Based on last modified date, I came to know this was created long back so but with least hope I took a look into default trace - as expected there is no luck.

My test server version is 2008R2.

The sys.servers data dictionary view will give you the date that the Linked Server entry was modified/created (modify_date column), but not who created it.


When should the IGNORE_DUP_KEY option be used on an index? Someone said it's preferable to craft your queries to avoid duplicate key exceptions, but I'm not convinced that's more performant that just setting IGNORE_DUP_KEY = ON for the index.

My goal is to ensure a row or set of rows exists for one or more users, before attempting to update those rows. I do this, so that when I attempt to update the row with an update statement like the one below, and no rows are affected, it's because the [Count] portion of predicate wasn't satisfied, as opposed to the row not existing at all (i.e. the [ID] portion of the predicate not being satisfied):

UPDATE [Inventory]
SET [Count] = [Count] + 1
WHERE [ID] = 3
AND ([Count] + 1) <= @MaxInventory

I could run EXISTS(SELECT 1 From [Inventory] WHERE [ID] = 3 to check for that single row, and only insert the row if it does not exist. That simply avoids unnecessary inserts. The insert, if necessary, would still have to contend with concurrent transactions, so duplicate key exceptions can still occur.

I'm curious whether it's more performant to just turn on IGNORE_DUP_KEY in this scenario, rather than allowing the error to be thrown and caught. Specifically, I'm curious if it's as-fast or possibly even faster than running an exists check, to just attempt to insert the record and let it ignore duplicate keys.

This becomes even more important, when I'm checking for and initializing multiple records at once. For example, if I need to ensure records for thousands of users exist in a single update statement, the logic would be much simpler if I just ran that insert statement up-front, letting it ignore duplicate keys. Avoiding duplicates would be more complex, because I'd have to first query the table for which records don't exist, then attempt to add just those records (again, ignoring duplicate keys). Just inserting may be faster, even if all the records exist.

I could meet it halfway and check whether any of the records are missing, such as with a left join or a COUNT comparison, but why bother if the insert ignoring duplicate keys is just faster?

Is is a good idea to use IGNORE_DUP_KEY and just attempt inserts instead of bothering with checking for row existence ahead of time? If not, why?

It's definitely an atypical setting to enable for an index. I don't think you're going to find many people who jump up and talk about using it.
Of course, there are a couple helpful posts on the subject by Paul White:

IGNORE_DUP_KEY slower on clustered indexes
A creative use of IGNORE_DUP_KEY

If your concern is around upsert patterns (or something similar), this article by Michael Swart is really informative:

SQL Server UPSERT Patterns and Antipatterns


LOAD_FILE() works on SELECT, but returns NULL on insert I'm having troubles using the LOAD_FILE() function in my environment.

I'm trying to add a file to a BLOB field but whenever I try to insert or update this field with LOAD_FILE(), this field is being set as NULL.

I already checked all common problems which includes system variables, file size and max packet Size and permission problems (Both DB and Linux permissions), even some workaround that included moving the file to a Mysql Owned directory, the problem persists.

Do note that when using LOAD_FILE in a statement similar to

SELECT LOAD_FILE('/foo/bar.jpg');

does work and the file raw data gets displayed, either via CLI or using a DBA tool like DBeaver.

Dump of the table:

CREATE TABLE `table` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `string` varchar(3) NOT NULL,
  `blob` blob NOT NULL,
  PRIMARY KEY (`id`),
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

Each file size I want to insert is around 8kb in size.

I'd appreciate any help with this issue.

LOAD_FILE, only reads the contents from the file and returns the contents from itself as a string, you try to add an image into field and using this function isn't gonna be possible. Besides you should have FILE privilege to you could do this and allocating the file into /var/lib/mysql-files. Being this a bad practice, the best is add the route relative or absolute from this file.


Incorrect syntax near the keyword 'WHERE' - SQL Server 2014 The error I get is

  Incorrect syntax near the keyword 'WHERE'.

Script:

SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[msp3_account_login]
     @LOGINID nvarchar(50),
     @PASSWORD nvarchar(50),
     @Status NVARCHAR(50),  -- A status symbol, 1/0. Don't know what this represents
     @IPAddress NVARCHAR(50)  --Either our server IP, or their remote IP
AS
BEGIN
    DECLARE @COUNT INT;
    SET @COUNT = (SELECT COUNT(FLD_LOGINID) FROM TBL_ACCOUNT WHERE [FLD_LOGINID] = @LoginID AND [FLD_PASSWORD] = @PASSWORD) --AND GETDATE() > (SELECT * FROM fc_ConvertToDate([FLD_PASSFAILTIME])));

    IF (@COUNT > 0)
    BEGIN
        SELECT *
        FROM TBL_ACCOUNT
        WHERE FLD_LOGINID = @LoginID
    END

    IF (@COUNT = 0)
    BEGIN
        DECLARE @TotalTry SMALLINT;
        SET @TotalTry = (SELECT ISNULL([FLD_PASSFAILCOUNT], 0) FROM [TBL_ACCOUNT] WHERE [FLD_LOGINID] = @LoginID);

        IF @TotalTry > 3
        BEGIN
            DECLARE @TryAgainDate DATETIME;
            SET @TryAgainDate = DATEADD(MINUTE, 5, GETDATE());

            UPDATE [TBL_ACCOUNT]
            SET [FLD_PASSFAILTIME] = (SELECT *
                                      FROM [fc_ConvertDate2](@TryAgainDate))
        END
        ELSE
        BEGIN
            UPDATE [TBL_ACCOUNT]
            SET [FLD_PASSFAILCOUNT] = [FLD_PASSFAILCOUNT] + 1
            WHERE [FLD_LOGINID] = @LoginID;
        END
     END

     --UPDATE TBL_ACCOUNT SET [FLD_VER] = '1'
     --SELECT * FROM TBL_ACCOUNT
     RETURN 0;
END

We can't hash the passwords, we do not have the source code for the applications, it's an old game.

You can confirm whether a given value contains a capital letter or special character with a function.  Using a function will allow you to fully customize exactly which characters you want to reject/accept.

Then just use the CheckPassword function in your stored proc, and it will return a BIT/INT value that indicates whether the password is valid or not.

Or if you want to keep it in the procedure, just use REGEX, something like:

WHERE @PASSWORD COLLATE sql_latin1_general_cp1_cs_as NOT LIKE '%[ABCDEFGHIJKLMNOPQRZTUVWXYZ]%'
AND @PASSWORD NOT LIKE '%[!@#$%^&*();[[]],./]%'

The COLLATE is necessary to make the value Case Sensitive, which is important in your case.

You should add the above either as an initial check, or as a new function which you call within the procedure.  This check/function will essentially not allow the rest of the procedure to run if it finds that the password does not meet the criteria.


Set default value for all columns with specific name within a database I've got a database that includes an int column project_id on many tables, and I want to script setting the default value to a specific value for all columns with that name in the database. Note that the column will already have a default value set, and I want to change it.

I know how to do this for individual tables, but am unsure how to script doing it for all tables containing the column project_id without manually specifying the names of the tables.

** UPDATE **

Based on Aaron's answer, this is what I finally came up with:

DECLARE @sql NVARCHAR(MAX) = N'';
DECLARE @new_id INT = 9;

SELECT
    @sql += Query
FROM
  (
    SELECT
      'IF (EXISTS(SELECT 1 FROM sys.default_constraints WHERE name = ''DF_' + OBJECT_NAME(parent_object_id) + '_project_id'' AND type = ''D'')) BEGIN ALTER TABLE [' + OBJECT_NAME(parent_object_id) + '] DROP CONSTRAINT [' + name + '] END;' + CHAR(13) + CHAR(10) AS Query
    FROM
      sys.default_constraints
    WHERE
      name = 'DF_' + OBJECT_NAME(parent_object_id) + '_project_id'

    UNION ALL

    SELECT
       'ALTER TABLE [' + OBJECT_NAME(object_id) + '] ' +
       'ADD CONSTRAINT [DF_' + OBJECT_NAME(object_id) + '_project_id] ' +
       'DEFAULT ((' + CAST(@new_id AS NVARCHAR) + ')) ' +
       'FOR [project_id];' + CHAR(13) + CHAR(10) AS Query
    FROM
      sys.columns
    WHERE
      name = 'project_id' AND
      system_type_id = 56
  ) AS Queries

PRINT @sql
EXEC sp_executesql @sql;

Let's say the new default you want is 5:

DECLARE @sql NVARCHAR(MAX) = N'';

SELECT @sql += N'ALTER TABLE ' + t + ' DROP CONSTRAINT ' + c + ';
  ALTER TABLE ' + t + ' ADD CONSTRAINT ' + c
  + ' DEFAULT (5) FOR [project_id];'
FROM
(
  SELECT t = QUOTENAME(s.name) + '.' + QUOTENAME(t.name),
    c = QUOTENAME(d.name)
  FROM sys.tables AS t
  INNER JOIN sys.schemas AS s
  ON t.[schema_id] = s.[schema_id]
  INNER JOIN sys.columns AS c
  ON t.[object_id] = c.[object_id]
  INNER JOIN sys.default_constraints AS d
  ON d.parent_object_id = t.[object_id]
  AND d.parent_column_id = c.column_id
  WHERE c.name = N'project_id'
) AS x;

PRINT @sql;
--EXEC sp_executesql @sql;

Notes:

print is limited to 8K, so if you have a lot of tables with this column, you might not actually see the entire command output in SSMS, and it may look truncated.
This will only add the constraint to project_id columns that already have a default constraint. If you have other tables that have this column but don't currently have a constraint, you'll need a separate query.
This will not change the values already generated based on the old defaults.

Here is why I recommend against INFORMATION_SCHEMA for this type of problem:

https://sqlblog.org/2011/11/03/the-case-against-information_schema-views


MySql MBRCONTAINS not accurate I have a table that has 3 column. city_id, city_name and city_geo. city_geo type is geometry. and i have a another table that has a vehiclelog. I want to query if the latitude and longitude from vehiclelog table is inside the city_geo. I can get the result but not so accurate it's also get the Point that near in city_geo. this is my query

        SELECT  v.`City`,v.`Time`,c.`city_name`,v.`Latitude`,v.`Longitude`
        FROM `city` c, `vehiclelog` v
        WHERE c.`city_name`='$c' AND v.`plate`='$p' AND v.`Date` = '$d'
        AND  MBRCONTAINS(c.`city_geo`, POINT(v.`Latitude`, v.`Longitude`))

I plot it on google map to verify the result and i get

as you can see there are markers outside the polygon that are not suppose to include in result I want only the markers inside the polygon or city_geo. Im using MySql v5.0 I also try MBRCONTAINS, MBRCrosses, Contains and Crosses all same result.

MySQL's result seems correct... MBRCONTAINS() tests whether the Minimum Bounding Rectangle of one object contains the other object.

Does the smallest possible rectangle you can draw around the polygon... contain those points?  Visually, it certainly appears that it does.

To test whether the polygon, itself (not the MBR) contains the point, the function you want is ST_Contains(), which was introduced in MySQL 5.6.

http://dev.mysql.com/doc/refman/5.6/en/spatial-relation-functions-object-shapes.html