What are the ramifications of setting ARITHABORT ON for all connections in SQL Server? So I've determined that the erratic behavior of my SQL Server is because of .Net SqlClient Data Provider's default setting of SET ARITHABORT OFF. With that said, I've read various articles that debate the best way to implement this. For me, I just want an easy way because SQL Server is suffering and my query tuning hasn't fully transcended across the app (and obviously adding the SET in a sp DOES NOT WORK).

In Erland Sommarskog's brilliant article about the topic, he basically suggests taking the safe approach by altering the app to issue SET ARITHABORT ON for the connection. However, in this answer from a dba.stackexchange question, Solomon Rutzky offers both an instance-wide and database-wide approach.

What ramifications am I missing here with setting this instance-wide? As I see it ... since SSMS has this set ON by default, I see no harm in setting this ON server-wide for all connections. At the end of the day, I just need this SQL Server to perform above all else.

There are some defaults that exist merely because nobody really knows what the effect of changing them would be. For example, the default instance-level collation when installing on a system that uses "US English" as the OS language is SQL_Latin1_General_CP1_CI_AS. This makes no sense since the SQL_* collations are for pre-SQL Server 2000 compatibility. Starting in SQL Server 2000 you could actually choose a Windows collation, and so the default for US English systems should have been changed to Latin1_General_CI_AS. BUT, I guess nobody at Microsoft really knows what the impact will be to all of the various potential sub-systems and system stored procedures, etc.

So, I am not aware of any specific negative impact of setting it to ON as either a database default or even instance-wide. At the same time, I have not tested it. But even if I had tested it, I might still not use the same code paths as your application, so this is something that you really need to test in your environment. Set it to ON at the instance level in your Dev and QA environments and see how that works for a month or two. Then enable it in Staging / UAT. If all continues to go well for several weeks, roll that config change to Production. The key is to give as much time as possible for testing various code paths that are not hit daily. Some are hit weekly or months or annually. Some code paths are only hit by support, or some ad hoc report or maintenance proc that someone created years ago and never told you about and only gets used at random intervals (nah, that never happens ;-).

So, I did some testing on an instance that still has the default "user options" setting as I have never changed it.

Please note:

@@OPTIONS / 'user options' is a bitmasked value
64 is the bit for ARITHABORT ON

SETUP

I tested with both SQLCMD (which uses ODBC) and LINQPad (which uses .NET SqlClient):

SQLCMD -W -S (local) ^
-Q"SELECT CONCAT(DB_NAME(), N': ', @@OPTIONS & 64, N' (', ses.[client_interface_name], N')') FROM sys.dm_exec_sessions ses WHERE ses.[session_id] = @@SPID;"
echo .

(the ^ is the DOS line continuation character; the . on the last line is just to force the extra line to make it easier to copy-and-paste)

In LINQPad:

using (SqlConnection connection =
    new SqlConnection(@"Server=(local);Trusted_Connection=true;Database=tempdb;"))
{
  using (SqlCommand command = connection.CreateCommand())
  {
    command.CommandText = @"SELECT @RetVal =
CONCAT(DB_NAME(), N': ', @@OPTIONS & 64, N' (', ses.[client_interface_name], N')')
FROM  sys.dm_exec_sessions ses
WHERE ses.[session_id] = @@SPID;";
    SqlParameter paramRetVal = new SqlParameter("@RetVal", SqlDbType.NVarChar, 500);
    paramRetVal.Direction = ParameterDirection.Output;
    command.Parameters.Add(paramRetVal);

    connection.Open();
    command.ExecuteNonQuery();

    Console.WriteLine(paramRetVal.Value.ToString());
  }
}

TEST 1: Before

SQLCMD returns:

master: 0 (ODBC)

LINQPad returns:

tempdb: 0 (.Net SqlClient Data Provider)

CHANGE DEFAULT CONNECTION OPTION:

The following T-SQL enables ARITHABORT without removing any other options that might be set, and without changing anything if ARITHABORT is already set in the bitmasked value.

DECLARE @UserOptions INT;

-- Get current bitmasked value and ensure ARITHABORT is enabled:
SELECT @UserOptions = CONVERT(INT, cnf.[value_in_use]) | 64 -- enable "ARITHABORT"
FROM   sys.configurations cnf
WHERE  cnf.[configuration_id] = 1534 -- user options

-- Apply new default connection options:
EXEC sys.sp_configure N'user options', @UserOptions;
RECONFIGURE;

TEST 2: After

SQLCMD returns:

master: 64 (ODBC)

LINQPad returns:

tempdb: 64 (.Net SqlClient Data Provider)

Conclusion

Given that:

There does not seem to be any benefit to having ARITHABORT OFF
There is benefit to having ARITHABORT ON
The default connection setting (unless overridden by the connection) = OFF
It does not appear that either ODBC or OLEDB / .NET SqlClient attempt to set ARITHABORT, thus they accept the default setting

I would suggest changing the instance-wide default connection options (as shown above). This would be less obtrusive than updating the application. I would only update the app if you find a problem with changing the instance-wide setting.

P.S. I did a simple test with changing tempdb and not changing the instance-wide setting and it did not seem to work.


SQL SERVER Virtual Log Files I am new to SQL SERVER, could anyone please explain that why do we have VLFs' in T-LOG and why not have 1 T-log itself. I am aware that VLFs' are logical but my question is why VLFs'? what is the benefit that we get from having VLFs'.

SQL Server uses VLF to fragment the log file. This fragmentation of the log file makes log management easier for the engine, as it adds granularity to the file.

As some of the links that Aaron Bertrand has posted in the comment explain (I would like to add this one), SQL Server writes sequentially to the log file. It starts with the first VLF and, once it is full, continues with the next one and so on. Once all VLFs have been used, it starts over again from the first one. This mechanism requires the log file to free up space that is not being used anymore.

The log file keeps all active transactions in SIMPLE recovery model, and all transactions since the last log backup in FULL (or BULK_LOGGED) recovery model. Once a CHECKPOINT or a log backup happens, the transactions that have been either commited or rolled back are no longer active transactions, and the space in the log file they were using can be reused (log truncation).

SQL server will just truncate VLFs that do not contain any active transaction. It won't free up space in VLFs that contain any active transaction. This way, SQL can just keep track of which VLFs contain active transactions or not, and just make the ones without active transactions available for reuse. I guess -this should be better answered by Microsoft's developers and engineers- that this is far easier than having to check a full logical file, that could be physically fragmented, and having to know where exactly is the lower limit of the log in the file (MinLSN).

The number of VLFs in our log file is important. If we have too few, it is very unlikely that log truncation can happen, because VLFs will be so big that they usually will "host" an active transaction. Too may can lead us to log fragmentation. You can read this excellent blog post by Kimberly Tripp on the topic.


What is the latest version of SQL Server native client that is compatible with Sql Server 2008 r2 Title says it all, what is the most recent version of Sql Server native client that is compatible with Sql Server 2008 R2

Microsoft SQL Server Native Client 11.0 is the latest and is backward compatible with 2008 and 2008R2 (as of July 9 2019, both windows and sql server 2008 and 2008r2 are fully unsupported).

BOL warns you :

  The SQL Server Native Client (SQLNCLI) remains deprecated and it is not recommended to use it for new development work. Instead, use the new Microsoft OLE DB Driver for SQL Server (MSOLEDBSQL) which will be updated with the most recent server features.


Postgresql - Do Foreign Key Constraints Automatically Create Indexes? Per the PGSQL 9.3 docs:

  Because this is not always needed, and there are many choices available on how to index, declaration of a foreign key constraint does not automatically create an index on the referencing columns.

Yet after I create such a constraint, when I run \d <table> I see that an index does in fact get created:

Indexes:
    "table_primary_key" PRIMARY KEY, btree (id)
    "fki_table_product_foreign_key" btree (product)

What could explain why this gets created? Do I still need to create another index on my product column to improve performance of queries on it?

I discovered that pgAdmin III does this by default. When you create a new foreign key constraint using the GUI, there is a checkbox "Auto FK Index" on the Definition tab. I hadn't noticed this until just now so it explains why the index was being created.


MySQL: Change compression on an existing InnoDB table Is there any way to turn on compression for an existing table? I don't care if there's downtime for this.

The docs only mention setting compression when the table is created.


How do I start over? (newbie goof: now I need to start fresh with MySQL) I'm just starting with MySQL (version 5.7.20 on Xubuntu 16.04), and in playing around with it I changed the root password.  Now I can't log in as root -- I either forgot the password or didn't set it to what I thought I did.  This is my home hobby system, so I'm the only user.

I may as well start over.  But it's not clear to me how to do that.  On Ubuntu there are about 9 or 10 mySQL packages involved, and I'm not sure I can uninstall all of them cleanly.  I'm happy to just trash the database if I only knew the commands to start with a new one.

I could use some advice, preferably from someone who knows how it works on Ubuntu and it's variants, as these packages are managed by Canonical and are not necessarily like even the Debian ones.

See the manual.  There is a 'simple' procedure for starting mysqld with --skip-grant-tables.  This lets you in.  Change the root password once you are in.


Side effects of using updlock lock hint? I was just exploring UPDLOCK hint in SQL-Server 2008 and I found it is very useful in avoiding certain deadlock scenarios. There are always some side effects when using these types of lock hints but I could not find any for this one.

So what happens if I use it in all my update queries? I am not talking about select, just update. How will it affect me negatively?

So what happens if I use it in all my update queries?

Like all hints, UPDLOCK should only be used where there is a clear benefit.

  How will it affect me negatively?

It depends on the query and the execution plan. Assuming you are only proposing to add UPDLOCK to the target table, there may be no negative effects at all in very simple cases.

A "very simple case" is where all decisions about which rows qualify are made in a single plan operator. In this situation, update locks are only taken on rows that will definitely qualify, so no harm is done.

SQL Server automatically takes U locks when searching for qualifying rows even without an UPDLOCK hint, but only for the primary access method (e.g. an index seek and any associated RID or Key Lookup). These automatic U locks are special, in that SQL Server can still release them early if later operators in the plan determine that the row does not qualify after all.

When UPDLOCK is specified, any update locks taken cannot be released before the end of the transaction, even if other operators in the plan determine that the row does not actually qualify for the update. These extra U locks reduce concurrency and may cause lock escalation to a table-exclusive lock.

For these reasons, it is generally not a good idea to specify UPLOCK routinely on UPDATE queries.


Moved database (percona mysql) to larger disk, now everything is missing (though the files exist) I dont know anything about databases or MySQL beyond the absolute basics so lets start with that. I moved the /var/lib/mysql from the / to a mounted volume which I remounted as /var/lib/mysql after moving the files to temp storage.

Ok, now when I go to my sites, its like they dont have any database attached to them. Wordpress wants to reinstall, other sites cant connect to their databases either.

When I go to the file system directly (/var/lib/mysql) and look for the files, I see that they are in their respective directories (I even restored from a backup just to be extra sure the files were there).

I have tried to repair and check the tables. But it keeps saying that some of the tables dont exist.

I am totally lost and frustrated.

Ok, after about a billion headaches I finally figured out what was wrong. Here it is: Some tables are MyISAM and some are InnoDB. I didnt know that when I started, which would have totally helped. So, what ended up happening is that when I moved the files to a temp directory, I guess I messed up the InnoDB tables alignment or whatever you call it, anyway the results where that all the sites that were InnoDB didnt work because I didnt do proper InnoDB moving and it couldnt find those tables.

So what I eventually realized is that I had been making backups of the database and that I could probably restore the database and get the InnoDB tables back inline where they should be. So what I ended up doing was mounting a new disk at /var/lib/mysql and then restoring the databases from a backup onto the new filesystem that was mounted for /var/lib/mysql.

Once that was done, I restarted MySQL and it is now running like nothing ever changed. Geesh.. what an adventure!


LEFT JOIN same table - Postgres 9.2 I've got this table:

CREATE TABLE clients (
  id       BIGSERIAL NOT NULL,
  company  CHARACTER VARYING(255) DEFAULT '' NOT NULL,
  email    CHARACTER VARYING(50) DEFAULT '' NOT NULL,
  city     CHARACTER VARYING(80),
  postcode CHARACTER VARYING(20),
);

I got a list of cities and postcode. I need to find if any row matches with the data I have:

City,Zipcode
Lansdowne,2163
Villawood,2163
Smithfield,2164

I'm using this query; how can I achieve the expected result?

SELECT
    t1.id,
    t1.city,
    t1.company,
    t1.postcode
FROM
    clients t1
LEFT JOIN
    clients t2 ON t2.city = t1.city
LEFT JOIN
    clients t3 ON t3.postcode = t1.postcode
WHERE t2.city IN ('Lansdowne','Villawood','Smithfield',)
AND t3.postcode IN ('2164','2163','2163',)

Would the query be right?

Would the query be right?

No. It would find any combination of given cities and postcodes (like  ('Smithfield', '2163'). This Cartesian product would become increasingly expensive and nonsensical for more combinations in one query.

And the query itself is needlessly expensive and confusing on top of that. LEFT JOIN is misplaced and joins would not be needed.

The shortest, cleanest and fastest way to match on multiple rows (combination of multiple column values) is to join to a VALUES expression (which is an ad-hoc table expression):

SELECT id, city, company, postcode
FROM   clients
JOIN  (
   VALUES
   ('Lansdowne' , '2163')
 , ('Villawood' , '2163')
 , ('Smithfield', '2164')
   ) v(city, postcode) USING (city, postcode);

For a big table, to make this fast, you should have an index on (city, postcode) or vice versa or at least an index with these two as leading columns.


Ram consumed by single query in Postgres using SqlAlchemy I have a list of 36 queries ranging from low complexity(No joins) to high complexity(joins with subquery). I want to find the RAM Consumed by each query.

This is required for performance testing for the application which I am building.

Can anyone please help with the same?

I am creating a SQLAlchemy session object and executing raw queries directly.

You can turn on "log_statement_stats", and look at the 'max resident size' line from the log file (or set client_min_messages TO log;).

Is is a pretty crummy interface, and the number it gives is the high water mark for the connection, so you would have to run each statement in a separate connection while testing it.


PgTap, unit test a foreign key is deferrable I am using PgTap to unit test a Postgres (v9.4) database structure in a continuous migration process. It works great.

Here is my question: « Is there a way to control that a foreign key constraint is deferrable (initially immediate) ? »

I do already check foreign keys but I want to ensure some of them are deferrable.

Thank you for your help.

Ok, it appears there are no methods to test such thing with PgTap for now so I ended up with

SELECT results_eq(
    $$select condeferrable, condeferred from pg_catalog.pg_constraint where conname = 'default_article_id_fk'$$,
    $$values ('t'::bool, 'f'::bool)$$,
    '"default_article_id_fk" is deferrable initially immediate.'
);

That does the job.


Distribution statistics on VARCHAR / VARGRAPHIC columns? It appears that only the first n characters is used as colvalue for varchar/vargraphic columns. From my test below it appears to be 33/16. Is the length documented somewhere? I tried to search the documentation, but can't find anything (probably searching for the wrong thing)

Simple test case:

DROP TABLE LEJO0004.T;
CREATE TABLE LEJO0004.T (
        ID BIGINT NOT NULL GENERATED ALWAYS AS IDENTITY
            ( START WITH 1 INCREMENT BY 1 MINVALUE 1 MAXVALUE 9223372036854775807 NO CYCLE CACHE 20 NO ORDER ),
        TCOL_C VARCHAR(64) NOT NULL,
        TCOL_G VARGRAPHIC(64) NOT NULL
);

insert into LEJO0004.T (tcol_c, tcol_g)
with n100 (n) as ( values 0 union all select n+1 from n100 where n<100 )
   , n10  (n) as ( values 0 union all select n+1 from n10 where n<10 )
   , n1   (n) as ( values 0 )
select 'commons.domain.regel.avbrottsregel'
     , 'commons.domain.regel.avbrottsregel'
from n100
union all
select 'commons.domain.regel.grupp.overfors.nya.endast.som.merit'
     , 'commons.domain.regel.grupp.overfors.nya.endast.som.merit'
from n10
union all
select 'commons.domain.regel.ingar.i.grupp.overfors.till.nya'
    ,  'commons.domain.regel.ingar.i.grupp.overfors.till.nya'
from n1;

runstats on table lejo0004.T with distribution on all columns;

select cast(colname as varchar(15))
    ,  type
    ,  seqno
    ,  cast(colvalue as varchar(40))
    ,  valcount
from sysstat.COLDIST
where tabschema = 'LEJO0004'
  and tabname = 'T'
  and colname in ('TCOL_C', 'TCOL_G')
  and colvalue is not null
  and type = 'F'
order by type, colname, seqno;

Result is:

TCOL_C  F 1 'commons.domain.regel.avbrottsrege' 101
TCOL_C  F 2 'commons.domain.regel.grupp.overfo'  11
TCOL_G  F 1 g'commons.domain.r'                 113

Appears to be the same for Q

values char_length(g'commons.domain.r' using codeunits32)
16

values char_length('commons.domain.regel.avbrottsrege' using codeunits32)
33

Tested on:

db2level
DB21085I  This instance or install (instance name, where applicable:
"lejo0004") uses "64" bits and DB2 code release "SQL11050" with level
identifier "0601010F".
Informational tokens are "DB2 v11.5.0.0", "s1906101300", "DYN1906101300AMD64",
and Fix Pack "0".
Product is installed at "/home/lejo0004/sqllib".

It is "partially documented"/"not really documented" AFAIK. More specifically, there is APAR:

IT13369: SUB-OPTIMAL QUERY PERFORMANCE WHEN DISTRIBUTION STATS COLLECTED ON STRING COLUMN WITH A COMMON PREFIX LARGER THAN 32 BYTES

that mentions it. I assume what you actually care about are cardinality estimates for a query that has predicate on a column that has a common prefix in which case you might try the workaround from it (i.e. NOT collect distribution stats for that column)


Oracle-xe on Ubuntu 18.04 - Installation and impdp Setup:

10 GB partition ext3 mounted at /u01
5 GB swap
Oracle-xe oracle-xe-11.2.0-1.0.x86_64.rpm - installed with alien and then dpkg
Followed instructions from https://community.oracle.com/thread/2229554

Problem:

After installation I do /etc/init.d/oracle-xe configure all default values. All went well.

And what comes next?

I have to import db.dmp.

But there isn't any db mounted.

So I tried createdb.sh from /u01/app/oracle/product/11.2.0/xe/config/scripts

It goes well.

Next I execute sqls that creates scheme.

Next I would like to import dump file.
But the impdp \"/ as sysdba\" schemas=DBV remap_schema=DBV:application directory=DATA_PUMP_DIR dumpfile=db.dmp logfile=db-import-$(date +%d-%b-%H_%M).log EXCLUDE=ROLE_GRANT
 reports the following:

UDI-01034: operation generated ORACLE error 1034
ORA-01034: ORACLE not available
ORA-27101: shared memory realm does not exist
Linux-x86_64 Error: 2: No such file or directory
UDI-00003: all allowable logon attempts failed

When I execute createdb.sh and then startdb.sh impdp reports:

Import: Release 11.2.0.2.0 - Production on So Lut 23 17:55:32 2019

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production
ORA-39006: internal error
ORA-39213: Metadata processing is not available

I can connect to DB via sqldeveloper. The dump file is verified to be working.

My question is, what steps should I take after installation with dkpg to create empty oracle-xe db ready to use and import dumps?

Below I provide outputs for some commands suggested in comments:

$ ps -ef | grep smon
oracle   16930  2196  0 17:04 ?        00:00:00 xe_smon_XE
oracle   18847 14970  0 17:56 pts/0    00:00:00 grep --color=auto smon

$ env | grep ORA
ORACLE_SID=XE
ORACLE_HOME=/u01/app/oracle/product/11.2.0/xe

SQL> exec dbms_metadata_util.load_stylesheets;
BEGIN dbms_metadata_util.load_stylesheets; END;

*
ERROR at line 1:
ORA-31609: error loading file "kualter.xsl" from file system directory
"/u01/app/oracle/product/11.2.0/xe/rdbms/xml/xsl"
ORA-06512: at "SYS.DBMS_METADATA_UTIL", line 2397
ORA-06512: at line 1

SQL> Select comp_id, comp_name, version from dba_registry ;

COMP_ID
--------------------------------------------------------------------------------
COMP_NAME
--------------------------------------------------------------------------------
VERSION
--------------------------------------------------------------------------------
XDB
Oracle XML Database
11.2.0.2.0

CONTEXT
Oracle Text
11.2.0.2.0

COMP_ID
--------------------------------------------------------------------------------
COMP_NAME
--------------------------------------------------------------------------------
VERSION
--------------------------------------------------------------------------------

CATALOG
Oracle Database Catalog Views
11.2.0.2.0

CATPROC
Oracle Database Packages and Types

COMP_ID
--------------------------------------------------------------------------------
COMP_NAME
--------------------------------------------------------------------------------
VERSION
--------------------------------------------------------------------------------
11.2.0.2.0

Ubuntu is not a supported distribution for running Oracle databases. Just use a supported distribution.

Operating System Requirements

The database should be configured and created with /etc/init.d/oracle-xe configure. If you do not have a working databases after that, that is an environmental issue: use a supported environment.
I had to check and run createdb.sh.  It created an incomplete database that gave the same error. Instead of it use the above method for creating the database. If it does not work: use a supported environment.

At this point you can start over and try to get the official steps working by folowing random blog posts and forum comments like above. Maybe it will work if you do not use createdb.sh. Or you can save a lot of time by using a supported environment.


Why do LocalTemp table showing with lengthy underscore under Temporary Tables of tempdb in SSMS 2017 Express Edition? In my Microsoft SQL Server 2017 Express Edition, I have created a local temp table using below create statement,

create table #localtemptable
(tempid int,tempname varchar(20) )

After creating #table, I  checked the tempdb -> Temporary Table , it is showing the table name along with a lengthy underscore and 0000000004 number. What is it meaning and why it is showing like this?

The lengthy underscore followed by the number were added by the engine to your requested table name in order to make the table name globally unique in the tempdb database. You can query your local table in the session that created it using the #localtemptable name that you gave it. What you are seeing is perfectly normal behavior and there's nothing to be alarmed about.


Proper Use of Lookup Tables I'm having trouble figuring out exactly how to place good boundaries for when and where to use lookup tables in a database. Most sources I've looked at say that I can never have too many but, at some point, it seems like the database would be broken down into so many pieces that, while it may be efficient, it is no longer manageable. Here's a thrown together example of what I'm working with:

Let's say I have a table called Employees:

ID  LName   FName   Gender  Position
1   Doe     John    Male    Manager
2   Doe     Jane    Female  Sales
3   Smith   John    Male    Sales

Pretend for a moment that the data is more complex and contains hundreds of rows. The most obvious thing I see that could be moved to a lookup table would be Position. I could create a table called Positions and stick the foreign keys from the Positions table into the Employees table in the Position column.

ID  Position
1   Manager
2   Sales

But how far can I continue to break the information down into smaller lookup tables before it becomes unmanageable? I could create a Gender table and have a 1 correspond to Male and a 2 correspond to Female in a separate lookup table. I could even put LNames and FNames into tables. All "John" entries are replaced with a foreign key of 1 that points to the FName table that says an ID of 1 corresponds to John. If you go down this rabbit hole too far like this, though, your Employees table is then reduced to a mess of foreign keys:

ID  LName   FName   Gender  Position
1   1       1       1       1
2   1       2       2       2
3   2       1       1       2

While this might or might not be more efficient for a server to process, this is certainly unreadable to a normal person who may be trying to maintain it and makes it more difficult for an application developer trying to access it. So, my real question is how far is too far? Are there "best practices" for this sort of thing or a good set of guidelines somewhere? I can't find any information online that really nails down a good, useable set of guidelines for this particular issue I'm having. Database design is old hat to me but GOOD database design is very new so overly technical answers may be over my head. Any help would be appreciated!

But how far can I continue to break the information down into smaller
  lookup tables before it becomes unmanageable? I could create a Gender
  table and have a 1 correspond to Male and a 2 correspond to Female in
  a separate lookup table.

You're mixing two different issues. One issue is the use of a "lookup" table; the other is the use of surrogate keys (id numbers).

Start with this table.

ID  LName   FName   Gender  Position
1   Doe     John    Male    Manager
2   Doe     Jane    Female  Sales
3   Smith   John    Male    Sales

You can create a "lookup" table for positions like this.

create table positions (
  pos_name varchar(10) primary key
);

insert into positions
select distinct position
from employees;

alter table employees
add constraint emp_fk1
foreign key (position)
  references positions (pos_name);

Your original table looks exactly like it did before creating the "lookup" table. And the table of employees requires no additional joins to get useful, human-readable data out of it.

Using a "lookup" table boils down to this: Does your application need the control over input values that a foreign key reference provides? If so, then you can always use a "lookup" table. (Regardless of whether it uses a surrogate key.)

In some cases, you'll be able to completely populate that table at design time. In other cases, users need to be able to add rows to that table at run time. (And you'll probably need to include some administrative processes to review new data.) Gender, which actually has an ISO standard, can be completely populated at design time. Street names for international online product orders probably have to be added at run time.


Performance issues with partial join query I have two tables:

dbo.allowedURLs (a unique list of all permitted domains)

*** DomainName ***
google.com
facebook.com
etc...

dbo.loggedURLs (a list of URLs processed by a proxy server)

*** Username ***      *** URL ****
fred.smith            www.google.com/something-else-here
joe.black             www.othersite.net/search/query

I need to be able to return a list of all the domain names in the 'allowedURLs' table and a count of how many different users accessed the service. The 'url' field in the 'loggedURLs' table contains the domain name within the full URL string.

Output

*** DomainName ***      *** Count of Username ****
google.com              1

While the query below gives me the results I'm after, I've been having significant performance issues running this and I was just wondering if anyone has advice on a better way to achieve this same outcome?

SELECT
dbo.allowedURLs.DomainName,
COUNT(DISTINCT dbo.loggedURLs.username)

FROM dbo.loggedURLs

INNER JOIN dbo.allowedURLs ON
dbo.loggedURLs.URL LIKE '%' + dbo.allowedURLs.DomainName + '%'

GROUP BY dbo.allowedURLs.DomainName

Thanks for any advice!

Your query will always result in a scan of dbo.loggedURLs because of the leading wildcard in the LIKE statement.  There are a couple of possible solutions I can think of.

Add a column to LoggedURLs for a reference to the allowedURL and at the time of insert into LoggedURLs, determine which allowedURL applies, and supply that column (Then Index that column)
If you can come up with an algorithm that will allow you to parse out the allowedURL in a consistent manner, you could create a computed column with the stripped down url, then index that (Example below)

declare @str varchar(100)
set @str = 'www.othersite.net/search/query'
select substring(@str, charindex('www', @str, 1) + 4,(charindex('/', @str, 1)-5))

This example may not work for all potential situations, but works for the examples given.


ADD Column ON UPDATE TIMESTAMP I want to alter a table to include modified and created columns. However, I am having trouble with adding the modified column. I get the following error:

  SQL Error: ORA-00907: missing right parenthesis
  00907. 00000 -  "missing right parenthesis"

I was reading this Oracle document (http://docs.oracle.com/cd/E17952_01/refman-5.5-en/timestamp-initialization.html) to get an example on how to properly write the ON UPDATE syntax

Here is my SQL.

ALTER TABLE FOOBAR
ADD (
  created TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL,
  modified TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

Why am I getting a missing right parenthesis error?

That is the MySQL documentation, not the Oracle Database documentation.
There is no such clause in Oracle Datababase.
Use a trigger. Here is an example: link


Pull entire row based on MAX(Column) MySQL I am trying to pull whole record from database for the highest "Score" column.

TestTable structure

------------------
ID | Name | Score|
------------------
1  | john | 10
2  | mark | 20
3  | ted  | 15

So when I run

SELECT ID, Name, MAX(Score)
FROM TestTable

So I want the query to return entire row 2 because this is the highest value.

This is what I am expecting:

2  | mark | 20

But it does not necessarily return correct row. It always return MAX(Score) but other values are not necessarily from the same row.

This is what I am getting:

3  | ted  | 20

Is there a better way of pulling this data?
I am using MySQL.

Also I am not interested in sub-query such as

SELECT ID, Name, MAX(Score)
FROM TestTable
WHERE ID = (
    SELECT ID From TestTable WHERE Score = MAX(Score);
)

Because I need it for big query using a lot of data, and it needs to perform good.

Please help :)

If you don't care about ties and want one row only and always one row, then the following could be used. With an index on (Score), should be as efficient as possible:

SELECT ID, Name, Score
FROM TestTable
ORDER BY Score DESC
LIMIT 1 ;

If you care about ties but still want only one row, you'll have to decide how ties should be resolved and modify the ORDER BY accordingly. If, for example, the row with the first name in lexicographic order is wanted in case of ties, then use the following - and an index on (Score, Name):

ORDER BY Score DESC, Name ASC

If you want all tied results, then the query by @Dan in the other answer should be the best. The index on (Score) would be sufficient for that.


Should I set up a password for mariadb.sys@localhost? I'm not clear what this user is for, but mysqltuner suggests I create a password for it.  I'm unclear if that's actually good practice, or if this is a limitation of mysqltuner simply because it's seen a user that it's not familiar with, that no exception has been coded for?
Running MariaDB 10.5.8
Thanks

Here's a great article that explains the purpose of that account.
This section of the article in particular explains why MySQLTuner is complaining:

It uses an invalid password (see the output of SHOW CREATE USER
above). This ensures that should the account get unlocked by mistake,
it is still impossible to login. It is thus recommended not to reset
the password.


How can I alter an existing Primary Key on SQL Azure? I want to modify an existing primary key on a SQL Azure table.
It currently has one column, and I want to add another.

Now, on SQL Server 2008 this was a piece of cake, just did it in SSMS, poof. Done.
This is how the PK looks like if I script it from SQL Server:

ALTER TABLE [dbo].[Friend] ADD  CONSTRAINT [PK_Friend] PRIMARY KEY CLUSTERED
(
  [UserId] ASC,
  [Id] ASC
)

However, on SQL Azure, when I try to execute the above, it will of course fail:

Table 'Friend' already has a primary key defined on it.

Fine, so I try to drop the key:

Tables without a clustered index are not supported in this version of SQL Server. Please create a clustered index and try again.

Ok, so I try to create a temporary clustered index in order to drop the PK:

CREATE CLUSTERED INDEX IX_Test ON [Friend] ([UserId],[Id])

Which results in:
Cannot create more than one clustered index on table 'Friend'. Drop the existing clustered index 'PK_Friend' before creating another.

Great, a catch22 moment.

How do I add the UserId column to my existing PK?

Note: as of Azure SQL Database v12, these restrictions no longer apply.

The is no such thing as a 'primary index'. There is such a thing as a 'primary key' and also there is such a thing as a 'clustered index'. Distinct concepts, often confused. With this distinction in mind, lets revisit the question:

Q1) Can the clustered index in a SQL Azure table be modified?
A: Yes. Use  WITH (DROP_EXISTING=ON):

create table Friend (
    UserId int not null,
    Id int not null);
go
create clustered index cdxFriend on Friend (UserId, Id);
go
create clustered index cdxFriend on Friend (Id, UserId) with (drop_existing=on);
go

Q2) Can the clustered index of a table that has a primary key constraint be modified?
A: Yes, same as above, as long as the primary key constraint is not enforced via the clustered index:

create table Friend (
    UserId int not null,
    Id int not null identity(1,1),
    constraint pk_Friend primary key nonclustered (Id));
create clustered index cdxFriend on Friend (UserId, Id);
go
create clustered index cdxFriend on Friend (Id, UserId) with (drop_existing=on);
go

Q3) Can the primary key constraint of a table be modified?
A: Yes, as long as the primary constraint is not enforced via the clustered index:

create table Friend (
    UserId int not null,
    Id int not null identity(1,1),
    constraint pk_Friend primary key nonclustered (Id));
go
create clustered index cdxFriend on Friend (UserId, Id);
go
alter table Friend drop constraint pk_Friend;
alter table Friend add constraint pk_Friend primary key nonclustered (UserId)
go

Q4) Can the primary key of a table be modified when is enforced via the clustered index?
A: Yes, if the table never had any rows:

create table Friend (
    UserId int not null,
    Id int not null identity(1,1),
    constraint pk_Friend primary key clustered (UserId, Id));
go
alter table Friend drop constraint pk_Friend;
alter table Friend add constraint pk_Friend primary key clustered (Id, UserId)
go

Q5) Can the primary key of a table be modified when is enforced via the clustered index if the table is populated?
A: No. Any operation that converts a populated clustered index into a heap will be blocked in SQL Azure, even if the table is empty:

create table Friend (
    UserId int not null,
    Id int not null identity(1,1),
    constraint pk_Friend primary key clustered (UserId, Id));
go
insert into Friend (UserId) values (1);
delete from Friend;
go
alter table Friend drop constraint pk_Friend;

As a side note: the constraint can be modified if the table is truncated.

The workaround to change the PK constraint of a populated table is to do the good old sp_rename trick:

create table Friend (
    UserId int not null,
    Id int not null identity(1,1),
    constraint pk_Friend primary key clustered (UserId, Id));
go
insert into Friend (UserId) values (1);
go

create table FriendNew (
    UserId int not null,
    Id int not null identity(1,1),
    constraint pk_Friend_New primary key clustered (Id, UserId));
go

set identity_insert FriendNew on;
insert into FriendNew (UserId, Id)
select UserId, Id
from Friend;
set identity_insert FriendNew off;
go

begin transaction
exec sp_rename 'Friend', 'FriendOld';
exec sp_rename 'FriendNew', 'Friend';
commit;
go

sp_help 'Friend';

The sp_rename approach has some issues, most importantly being that permissions on the table do not carry over during the rename, as well as foreign key constraints.


Oracle Application Express Listener PDF Report We need our reports to be available in PDF format on our Oracle 11g, APEX:4.2.6

Followed this tutorial and selected "Oracle APEX Listener".

But the generated PDF report is empty.

We are using EPG.

Do we have to install/configure anything else other than the mentioned tutorial.

Here's an answer from an Oracle Employee on Oracle Community:

"The problem is when the default page size is set to A4, which translates into points with decimals when rendering the PDF. Please, change the Units to Points, and round up your page width and height."

Reference:

https://community.oracle.com/message/1793888


Joining dba_users & v$database I need to generate a report that displays the account status of all schemas, which I've got.

SELECT
''''|| USERNAME ||''''     USERNAME,
''''|| ACCOUNT_STATUS||'''' ACCOUNT_STATUS,
''''|| LOCK_DATE ||'''' LOCK_DATE,
''''|| PROFILE ||''''     PROFILE
FROM DBA_USERS
WHERE PROFILE='DEFAULT'
ORDER BY 1,2;

However the requirement also states that they want the database name on every row in the report, so...

SELECT
''''|| NAME ||''''
FROM V$DATABASE

My SQL is pretty poor so I tried some sort of mismashed join, but realised that won't work... any ideas?

SELECT
''''|| USERNAME ||''''     USERNAME,
''''|| ACCOUNT_STATUS||'''' ACCOUNT_STATUS,
''''|| LOCK_DATE ||'''' LOCK_DATE,
''''|| PROFILE ||''''     PROFILE,
(SELECT ''''|| NAME ||'''' FROM V$DATABASE) DB_NAME
FROM DBA_USERS
WHERE PROFILE='DEFAULT'
ORDER BY 1,2;

or

SELECT
(SELECT ''''|| NAME ||'''' FROM V$DATABASE) DB_NAME,
''''|| USERNAME ||''''     USERNAME,
''''|| ACCOUNT_STATUS||'''' ACCOUNT_STATUS,
''''|| LOCK_DATE ||'''' LOCK_DATE,
''''|| PROFILE ||''''     PROFILE
FROM DBA_USERS
WHERE PROFILE='DEFAULT'
ORDER BY 2,3;


SQL Server Backup Job failed multiple times I am getting the same error as mentioned by Mr.Ali

Sql Server Backup Job failed

Can anyone please explain the possible ways to fix this error as the backup is failing since 1 month.

Here is the Job history log

Executed as user: SAP\sqladmin.
Microsoft (R) SQL Server Execute Package Utility
Version 10.50.1600.1 for 64-bit
Copyright (C) Microsoft Corporation 2010. All rights reserved.
Started:  21:00:00
Progress: 2014-10-03 21:00:05.83
Source: {E7436186-D15A-4485-92B4-7623DA7DDF4D}
Executing query "DECLARE @Guid UNIQUEIDENTIFIER EXECUTE msdb..sp...".: 100% complete
End
Progress
Error: 2014-10-03 23:32:25.53
Code: 0xC002F210
Source: Back Up Database Task Execute SQL Task
Description:
Executing the query "BACKUP DATABASE [P01] TO  DISK =N'\\cmgsap31.sap...."
failed with the following error:
"Write on "\\cmgsap31.sap.logica.com\p$\Central-MS-SQL-Backup\SAP0233_P01_Full.bkp"
failed: 64(failed to retrieve text for this error. Reason: 15105)
BACKUP DATABASE is terminating abnormally.
10 percent processed.
20 percent processed.
30 percent processed.
40 percent processed.
50 percent processed.
60 percent processed.
70 percent processed.
80 percent processed.".
Possible failure reasons: Problems with the query,
"ResultSet" property not set correctly, parameters not set correctly,
or connection not established correctly.
End Error
DTExec: The package execution returned DTSER_FAILURE (1).
Started: 21:00:00
Finished: 23:32:25
Elapsed:  9145.29 seconds.
The package execution failed.  The step failed.

Well based on Reason:15105 I think that SQL Server doesn't have permission to access to the path you check the permissions of the account that is assigned to the SQL service account? or other reason would be that there is not enough free space to create backup.


MySQL Group Replication - Offline Node with cluster Metadata 1.0.1 I have a production MySQL Group Replication setup out of three Nodes. The cluster has Metadata Version 1.0.1. Today, one node went down an during rejoin phase, it says, it cannot connect to the cluster.
When trying to dba.rejoinInstance('');, the MySQL Shell says, it won't do it, because it will only make changes to a cluster with Metadata 2.0.0.
ERROR: Unable to start Group Replication for instance 'O18-SQL-NBG:3306'. Please check the MySQL server error log for more information. Cluster.rejoinInstance: Group Replication failed to start: MySQL Error 3092 (HY000): The server is not configured properly to be an active member of the group. Please see more details on error log. (RuntimeError)
The logfile says:
[MY-011521] [Repl] Plugin group_replication reported: 'Member version is incompatible with the group.'
When trying to upgrade Metadata on the cluster to 2.0.0, it says, it won't do it either, because one Member of the cluster is not available.
Dba.upgradeMetadata: This operation requires all the cluster members to be ONLINE (RuntimeError)
When trying to rescan() the cluster, it says, it won't it, because my metadata is out of date and it decided to only do read-only operations on this cluster:
Cluster.rescan: Operation not allowed. No cluster change operations can be executed because the installed metadata version 1.0.1 is lower than the version required by Shell which is version 2.0.0. Upgrade the metadata to remove this restriction. See \? dba.upgradeMetadata for additional details. (RuntimeError)
I have several versions of MySQL Shell available (8.0.17, 8.0.19 and 8.0.20). My primary objective is - guess what - to get the faulty node back up into the cluster. What are my options?

First of all, you are mixing some concepts. You are using MySQL InnoDB cluster which uses MySQL Group Replication as the replication technology.
I see that you have upgraded MySQL Shell version's and most probably you also upgraded the version of the MySQL servers of your cluster. The error you see when running cluster.rejoinInstance() is not related to Metadata but rather related to the incompatibilities of different MySQL versions in the Cluster. More info here: https://dev.mysql.com/doc/refman/8.0/en/group-replication-online-upgrade-combining-versions.html
How did you upgrade your instances? There's a procedure to follow. I'd recommend you to read the documentation regarding rolling upgrades: https://dev.mysql.com/doc/refman/8.0/en/mysql-innodb-cluster-upgrade.html
Regarding your scenario, my advice is to:

Use an older Shell version to remove the instance you're trying to remove, using cluster.removeInstance(). The version must be 8.0.18 or lower.

Using the latest MySQL Shell (8.0.21), upgrade the Metadata schema using dba.upgradeMetadata().

You will then have an operational cluster with the latest metadata schema version and you can then proceed to do the rolling upgrade of your instances and add them to the cluster as intended.
Cheers,
Miguel


Does Query Plan cache gets cleared by itself? I have been working on query plan stored in plan cache and every time i search for a particular query with the below sql query,

SELECT cp.objtype AS ObjectType,
OBJECT_NAME(st.objectid,st.dbid) AS ObjectName,
cp.usecounts AS ExecutionCount,cp.plan_handle,
st.TEXT AS QueryText,
qp.query_plan AS QueryPlan
FROM sys.dm_exec_cached_plans AS cp
CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) AS qp
CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) AS st
where
st.text like '%SearchModel%'
order by ExecutionCount desc

I see different counts for the result.I am not clearing the plan cache and no other developers.
I am working on SQL Server 2016 and i have turned on Optimize for Adhoc option.The queries are generated from application using LINQ-SQL.So i hope the queries generated are adhoc.Please correct me if i am wrong.

Why is that the cached plan counts varies each time.?

Does anyone has any insight from the below screenshot regarding plan cache usage.It has more memory allocated for adhoch queries as queries in my application comes from LINQ-SQL.
I guess prepared queries are compiled queries?

It's very unlikely, but you could be experiencing the bug described in the first comment to this user voice item: Procedure Cache empties spontaneously even on an idle machine

  This seems to happen when USERSTORE_TOKENPERM hits a certain value.
  ...
  Under this configuration, the plan cache empties spontaneously when the USERSTORE_TOKENPERM size grows to around 5 GB

You can check for that with this query:

select pages_kb
from sys.dm_os_memory_clerks
where [type] = 'USERSTORE_TOKENPERM' and [name] = 'TokenAndPermUserStore';

There are several other factors that can cause your plan cache to be emptied.  A non-exhaustive list of these factors includes:

someone ran the DBCC FREEPROCCACHE command to manually clear it
The server is under memory pressure, and old or single-use plans are being evicted from the cache


Trap Exception in POSTGRESQL script I want to run a script inside a transaction in postgresql. So I suround the sql with begin , commit statements. But I want to rollback on error. I dont see how to do that.
BEGIN;
UPDATE public.tablename
SET blah = 'xxx'
WHERE thing= '123';

COMMIT;

You set a savepoint and after the comand a Rollback

CREATE tABLE tablename (blah varchar(3), thing varchar(3))

BEGIN;
SAVEPOINT my_savepoint;
   UPDATE tablename
   SET blah = 'xxx'
   WHERE thing= '123';
ROLLBACK TO my_savepoint;
COMMIT;

db<>fiddle here


How to execute multiple statement in PgAdmin 4 like we do in Sql Server/Oracle? How to execute multiple statement in PgAdmin 4 like we do in Sql Server/Oracle (press F5)?

select * from employees;

select * from department;

As much I worked with PgAdmin, it allows us to execute only one selected statement or last statement per time, is there any way or any setting to execute multiple dml statement together?

pgAdmin (both III and 4) execute both statements. For the first one, it just discards the output, unless there is an error.

You can actually chain as many statements as you want:

-- We create one table
CREATE TABLE digit_names
(
    digit integer PRIMARY KEY,
    digit_name text,
    UNIQUE (digit_name)
) ;

-- Fill it with values
INSERT INTO digit_names
VALUES
    (0, 'zero'),
    (1, 'one'),
    (2, 'two'),
    (3, 'three'),
    (4, 'four'),
    (5, 'five'),
    (6, 'six'),
    (7, 'seven'),
    (8, 'eight'),
    (9, 'nine') ;

-- And perform a SELECT query
SELECT
    digit_name
FROM
    digit_names
    JOIN (VALUES (1), (2), (3) ) AS three_digits(digit) USING (digit) ;

-- All in just one statement.

But you'll get only the output from the last one.

If you're using pgAdmin 4, you just type everything on the Query Tool pane, and then press the button with a [lightning bolt] (or press F5).


Why Snapshots of the model, master, and tempdb databases are prohibited I have been researching on snapshots and found at many places including Microsoft

  Snapshots of the model, master, and tempdb databases are prohibited.

My question is why?

Edit:

Actually because at one of our client's server I saw backup statements with Snapshot every hour for each DB at server, I am just wondering if this is useful and what will be the purpose of this

Backup statement

BACKUP DATABASE [master] TO VIRTUAL_DEVICE='{886}4' WITH SNAPSHOT,BUFFERCOUNT=1,BLOCKSIZE=1024,COPY_ONLY

BACKUP DATABASE [msdb] TO VIRTUAL_DEVICE='{886}4' WITH SNAPSHOT,BUFFERCOUNT=1,BLOCKSIZE=1024,COPY_ONLY

taking snapshot on hourly manner is very useful for active databases and based on RTO (Recovery Time Objective) and RPO (Recovery Point Objective) which is less than 1 hour.

Model, master, and tempdb is passive default MSSQL databases.These database doesn't required to snapshot hourly as there wont be much changes occur every hour.One time daily backup is sufficient enough to rescue the database in the event of disaster.

Moreover, snapshopt will use up spaces and available resource (RAM and CPU).


Why did my UPDATE statement not complete? Running SQL Server 2008; Mgmt Studio 2008. I am working with a MS SQL job that we have created for our MS CRM 4.0 implementation.  For anyone familiar with CRM, this job purges the aSyncOperationBase table nightly to save on DB size.  Here is the job code:

    Begin Transaction T1

Declare @p30Days as DateTime
Declare @p3Years as DateTime

Set @p30Days = DateAdd(d, -30, GetDate())
Set @p3Years = DateAdd(d, -1095, GetDate())

update AsyncOperationBase
set deletionstatecode=2
where deletionstatecode = 0 and
statecode = 3 and
completedon is not null and
completedon < @p30Days
and OperationType <> 10

update AsyncOperationBase
set deletionstatecode=2
where deletionstatecode = 0 and
statecode = 3 and
completedon is not null and
completedon < @p3Years
and OperationType = 10

delete from workflowlogbase
where AsyncOperationid in (select AsyncOperationid from AsyncOperationBase where deletionstatecode=2)

update DuplicateRecordBase
set DeletionStateCode = 2
where asyncoperationid in
(select DuplicateRecordBase.asyncoperationid
    from DuplicateRecordBase
        left join asyncoperationbase on (DuplicateRecordBase.asyncoperationid=asyncoperationbase.asyncoperationid and
        asyncoperationbase.deletionstatecode = 0)
    where asyncoperationbase.asyncoperationid is null)

update BulkDeleteOperationBase
set DeletionStateCode = 2
where asyncoperationid in
(select BulkDeleteOperationBase.asyncoperationid
    from BulkDeleteOperationBase
        left join asyncoperationbase on (BulkDeleteOperationBase.asyncoperationid=asyncoperationbase.asyncoperationid and
        asyncoperationbase.deletionstatecode = 0)
    where asyncoperationbase.asyncoperationid is null)

delete from asyncoperationbase
where deletionstatecode = 2 and
completedon < @p30Days

commit transaction T1

The problem that I recently noticed is that this job, which starts at 4am, runs for at least 4 hours and does not complete.  As I tried run the first update statement against 1 record, the SQL process did not complete and became the lead blocker in the process chain.  Does anyone have any ideas on what could be causing this behavior?  I have looked at the Activity Monitor and there are no other processes which are causing major blockages.

Check to see if these statements are blocking each other.  Since they are in the same transaction.  Do they somehow lock conflicting rows?

update AsyncOperationBase
set deletionstatecode=2
where deletionstatecode = 0 and
statecode = 3 and
completedon is not null and
completedon < @p30Days
and OperationType <> 10

update AsyncOperationBase
set deletionstatecode=2
where deletionstatecode = 0 and
statecode = 3 and
completedon is not null and
completedon < @p3Years
and OperationType = 10


Can declaring function volatility IMMUTABLE harm performance? Postgres functions are declared with volatility classification VOLATILE, STABLE or IMMUTABLE. The project is known to be very strict with these labels for built-in functions. And with good reason. Prominent example: expression indexes only allow IMMUTABLE functions and those have to be truly immutable to avoid incorrect results.

User-defined functions are still free to be declared as the owner chooses. The manual advises:

  For best optimization results, you should label your functions with the strictest volatility category that is valid for them.

... and adds an extensive list of things that can go wrong with an incorrect volatility label.

Still, there are cases where faking immutability makes sense. Mostly when you know the function is, in fact, immutable within your scope. Example:

Does PostgreSQL support “accent insensitive” collations?

All possible implications on data integrity aside, what is the effect on performance? One might assume declaring a function IMMUTABLE can only be beneficial to performance . Is that so?

Can declaring function volatility IMMUTABLE harm performance?

Let's assume current Postgres 10 to narrow it down, but all recent versions are of interest.

Yes, it can harm performance.

Simple SQL functions can be "inlined" in the calling query. Quoting the Postgres Wiki:

  SQL functions (i.e. LANGUAGE SQL) will, under certain conditions, have
  their function bodies inlined into the calling query rather than being
  invoked directly. This can have substantial performance advantages
  since the function body becomes exposed to the planner of the calling
  query, which can apply optimizations such as constant-folding, qual
  pushdown, and so on.

Bold emphasis mine.

To enforce correctness, there are a number of preconditions. One of them:

  if the function is declared IMMUTABLE, then the expression must not invoke any non-immutable function or operator

Meaning, SQL functions using any non-immutable functions but still being declared IMMTUTABLE are excluded from this optimization. Triggered by these related answers on SO, I have been running extensive tests:

How to aggregate data from multiple years on MM-DD, ignoring year
How do you do date math that ignores the year?

Basically comparing these two variants of a simple SQL function (mapping dates to an integer, ignoring the year which doesn't matter for the purpose):

CREATE FUNCTION f_mmdd_tc_s(date) RETURNS int LANGUAGE sql STABLE    AS
$$SELECT to_char($1, 'MMDD')::int$$;

CREATE FUNCTION f_mmdd_tc_i(date) RETURNS int LANGUAGE sql IMMUTABLE AS
$$SELECT to_char($1, 'MMDD')::int$$;  -- cannot be inlined!

The Postgres function to_char() is only STABLE, not IMMUTABLE (all overloaded instances of it - for reasons beyond the scope of this answer). So the second one is fake IMMUTABLE and turns out to be 5x slower in a simple test:

db<>fiddle here

This specific example can be replaced with the equivalent:

CREATE FUNCTION f_mmdd(date) RETURNS int LANGUAGE sql IMMUTABLE AS
$$SELECT (EXTRACT(month FROM $1) * 100 + EXTRACT(day FROM $1))::int$$;

Would seem more expensive with two function calls and more computations. But the IMMUTABLE label is true (plus, the used function is faster and coercing text to integer is more expensive, too).

2x as fast as the faster variant above (10x as fast as the slower). The point being: Use IMMUTABLE functions where possible, then you don't have to "cheat" to begin with.


How to get exact year difference between two dates in SQL Server? I have a date of birth column to which I need to compare the current date and return the difference as age. Currently I am using the following query :

DECLARE @DateOfBirth date = '2000-07-27'
DECLARE @CurrentDate date = '2015-07-25'

SELECT
    @Age = DATEDIFF(YEAR, @DateOfBirth, @CurrentDate)

I am getting a difference as 15, but the desired result should be 14. This is because it compares the year and gets the difference between 2015 and 2000. So I used the following query :

select
    @Age = CAST(datediff(day, @DOB, @CurrentDate) / 365.2425 AS INT)
RESULT : 14

But when I change the Date Of Birth value to '2000-07-25', I still get the value of 14, but it should be 15 since the date difference is exactly 15 years.

How can I get the exact year difference from this?

Subtract one from each other in YYYYMMDD format, and divide by 10000 using integer division (which rounds down).

So:

(YEAR(@CurrentDate) * 10000 + MONTH(@CurrentDate) * 100 + DAY(@CurrentDate)
- YEAR(@DateOfBirth) * 10000 - MONTH(@DateOfBirth) * 100 - DAY(@DateOfBirth)
) / 10000

20150725-20000727 = 149998, and 149998 / 10000 = 14

You can also use CONVERT(int,CONVERT(char(8),@CurrentDate,112)), because style 112 is YYYYMMDD, but that's a little obfuscated in my opinion.


Query that only get one result per field I'm developing with CodeIgniter and I have the following problem:

Let's suposse that I have the following table:

| id |   employee   |     job     |    date    |
|----+--------------+-------------+------------|
|  1 |  John        | sysadmin    | 2017-12-01 |
|  2 |  Charles     | programmer  | 2017-11-24 |
|  3 |  Peter       | programmer  | 2017-11-29 |
|  4 |  Chris       | g.designer  | 2017-06-30 |
|  5 |  Paul        | web devel.  | 2017-10-08 |
|  6 |  Maria       | g.designer  | 2017-11-05 |
|  7 |  Abigail     | programmer  | 2017-09-24 |

I want to make a query that selects all fields, showing only 3 rows where the employees won't repeat the same job and the results must be ordered by date desc, so it would be:

| id |   employee   |     job     |    date    |
|----+--------------+-------------+------------|
|  1 |  John        | sysadmin    | 2017-12-01 |
|  3 |  Peter       | programmer  | 2017-11-29 |
|  6 |  Maria       | g.designer  | 2017-11-05 |

I have found the word DISTINCT, but after many tries, I couldn't make it to work as expected, so I made the following workaround:

for($i=0; $i<$limit; $i++)
{
    $this->db->where('employee NOT IN("'. implode($employees, '", "') .'")');
    $this->db->order_by('date DESC');
    $q = $this->db->get('employees', 1);
    $r = !empty($q) ? $q->result() : 0;
    $employees[] = $r[0]->employee;
    $result[] = $r[0];
}

Which makes the following query and stack the result into an array

SELECT * FROM employees WHERE employee NOT IN (implode($employees)) ORDER BY date DESC LIMIT 1

it works perfectly, but quite dirty, since if I want 3 rows, It will make 3 queries, that is not so bad, but when I change it to 18 rows, it takes too long to load the webpage.

I have tried the functions group_by() and distinct() from CodeIgniter, but it neither worked as I expected.
Any way to achieve it with only 1 query?

Assumptions:

if multiple employees have the same job and date, display the one with the
lowest id
an entry for web devel. was accidentally left out of the sample desired output

Sample data:

create table employees
(id          int
,employee    varchar(30)
,job         varchar(30)
,`date`      date);

insert into employees values
(1,'John',   'sysadmin',  '2017-12-01'),
(2,'Charles','programmer','2017-11-24'),
(3,'Peter',  'programmer','2017-11-29'),
(4,'Chris',  'g.designer','2017-06-30'),
(5,'Paul',   'web devel.','2017-10-08'),
(6,'Maria',  'g.designer','2017-11-05'),  <=== same job/date as 8/Bob
(7,'Abigail','programmer','2017-09-24'),
(8,'Bob'    ,'g.designer','2017-11-05');  <=== same job/date as 6/Maria

We'll use a derived table to order the data by job, date and id, and then with the help of some variables (@jname, @jcount) we'll find a single employee for each unique job who has the max(date):

select e.id,
       e.employee,
       e.job,
       e.`date`

      /* initialize our variables

          @jname  = keep track of previous job name
          @jcount = keep count of # records with same job name */

from  (select @jname  := '',
              @jcount := 0 ) v

join  (select /* if we've got a new job name, set @jcount = 1, else increment by 1,
                 and since we're ordering by 'date desc', @jcount = 1 = max(date)  */

              @jcount := if(job != @jname, 1, @jcount + 1) as jcount,

              /* save current job name */
              @jname  := job,

              id,
              employee,
              job,
              `date`

       from   employees

       order by job,
                `date` desc,
                id) e           /* determine which employee to display if tie for job/max(date) */

where  e.jcount = 1  /* only display our records where jcount = 1 = max(date) for a given job name */

order by id;

id employee job        date
-- -------- ---------- ----------
1  John     sysadmin   2017-12-01
3  Peter    programmer 2017-11-29
5  Paul     web devel. 2017-10-08
6  Maria    g.designer 2017-11-05

NOTE: While Bob and Maria have the same job/date values (g.designer/2017-11-05), Maria is displayed because she has the lowest id value.

Here's a db-fiddle


SQL Server query disk write/sec limit I am facing an issue which it looks like the server is hitting disk write limit. But after some test, I realize it wasn't. I was running the following query:

DECLARE @MyCounter int;
SET @MyCounter = 0;

SET NOCOUNT ON;

WHILE (@MyCounter < 10000)
BEGIN
   INSERT INTO TestTable VALUES ('testing text', 99)
   SET @MyCounter = @MyCounter + 1;
END

The following shows some print screen and explanation of my problem:

At 1st, I opened 1 query window and run the query, then I checked Performance Monitor:

It seems like I'm hitting limit of around 120 Disk Writes/sec.

Then, I opened another query window and run the same query, now I got the following Performance Monitor screen:

Ok, Disk Writes/sec limit seems increased to 240.

And again, I opened 1 more query window and run the same query again, I got the following Performance Monitor screen:

As expected, Disk Writes/sec increased to around 360.

My question is: Is SQL Server / Windows server / any other thing limiting this Disk Writes/sec ? Thanks in advance.

I'm using SQL Server 2014 in Windows Server 2012 R2 64-bit running on Dell PowerEdge 2900 server. This server has just been formatted.

You don't say but I'm guessing that your log files are on your D: drive and what you're seeing here is writes to the transaction log files, not to the database files i.e. to ldf not to mdf.  You won't see the mdf files written until the DB hits a CHECKPOINT, which could be some minutes later.

The linear increase is good and what you'd hope for.  That you can get from 120 to 240 and 360 shows that the disk subsystem is capable of these rates.  Whether the absolute number is as good as you would hope for or not, that's another matter.  I would suggest that the nature of the test you've chosen is itself limiting the whole system throughput.  Now you have populated tables you can try

INSERT INTO TestTable
SELECT TOP 10000 * FROM TestTable;

and see what rate that gives you.


I'm trying to restore single database from mysqldump all-databases with different name I'm running mysql-server-5.1.73-3.el6_5.x86_64:

mysql> SELECT VERSION();
+------------+
| VERSION()  |
+------------+
| 5.1.73-log |
+------------+
1 row in set (0.00 sec)

mysql>

I created mysqldump with option --all-databases, to create database backup of all databases located on that server.

I need to restore SINGLE database out of this whole mysqldump and restore it with different (from original) database name.

I usually use something like this: mysql --one-database --database=db1 but that's assuming I'm restoring under SAME name.

Could someone give me a hand with this please?

Thanks in advance!

After much Googling and messing about :-) I succeeded in getting it done ultimately due to this post here. It's quite simple and logical really - I just went down a few blind allies. Check out the answer by user1201232 and not the accepted answer - with a slight modification from me (experimenting!).

You get your dump of your entire server as you have done.

./bin/mysqldump -S ./mysql.sock -u root -pdba --all-databases > serverdump.sql

Take a backup of this original dump (see why later).

Then, you perform a sed on the file. I have a schema called gnarly which I renamed to xyzg in the dump file. This is a good reason to take a backup, otherwise, if your schema name is some common word, it might mess up other restores that you may wish to perform.

sed -i 's/gnarly/xyzg/g' serverdump.sql

Then create a schema called xyzg on the server

./bin/mysqladmin -S ./mysql.sock -u root -pdba create xyzg

Then restore the backup "fooling" mysql into thinking it's restoring an already made one.

./bin/mysql -S ./mysql.sock -u root -pdba xyzg < serverdump.sql

Connect and check your tables.

+--------------------+
| Tables_in_xyzg     |
+--------------------+
| customer_addresses |
| customers          |
| order_items        |
| order_payments     |
| order_status       |
| orders             |
| recurring_orders   |
| status_types       |
+--------------------+

A couple of selects showed that my data was indeed present. Et voilà!

Forgot to mention that MySQL Workbench has a way of doing this - go through the Data Import/Restore and there's a "New" button you can use to select a different schema name. My setup is custom and I wasn't able to get this to work, but if you have a default setup, it should work (I believe).

Of course, there's always the good old manual editing of the dump file. I did some further reading and found this is not as difficult as it might appear.

Using the example above, changing schema gnarly to xyzg.

Get your dump (serverdump.sql) of all schemas as per above.

From the shell prompt

$> grep "USE \`gnarly\`"
USE `gnarly`;
USE `gnarly`;
$>

only occurs twice. Use vi (or the editor of your choice) to change gnarly to the string of your choice, i.e. xyzg.

[EDIT]

Better way again - no need to edit anything.

Just do

sed -i 's/USE \`gnarly\`/USE \`xyzg\`/g' serverdump.sql

No need to use vi - or worry about changing anything else in the file.

Create a schema xyzg as per above and follow the other steps.

Might be useful if sed is overkill. From here.


In SQL Server, what is "Partitioned Table Parallelism" that is only available in Enterprise Edition? I am reviewing the differences between Standard and Enterprise editions of SQL Server and can't reproduce the differences advertised in this demo that explains the differences - the performance I observe when running a query across Standard and Enterprise are comparable, and queries run in parallel across partitioned tables in the execution plan.
I have verified that:

The queries produce identical query plans
The queries produce similar runtime with set statistics time on

It seems that in SQL Server 2016 this difference is not reproducible. Is there something else this feature affects - maybe I am not testing for the right thing but the queries are comparable to that in the demo.
This is the script I have used to test:
-- MAXDOP is 10

--  structure of table
--Column                type
--testData.PKcolumn1    bigint
--testData.PKcolumn2    int
--date                  datetime
--testData.PKcolumn3    bigint
--metric1               float
--metric2               float
--metric3               float
--metric4               float

--index_description                                                 index_keys
--clustered, unique, primary key located on ps_testData             categoryId, transactionId, date

--pf_testData/ps_testData is a range right datetime partition scheme,  fanout 368
GO

-- Actual partition count: 368
-- all 10 threads participate in the scan, 12MM to 13MM rows / thread
--      each partition likely read by a single thread
SELECT COUNT(*), MIN(date), MAX(date)
FROM testData
GO

-- Actual partitions with data count: 31
-- all 10 threads participate in the scan, 3MM to 4MM rows / thread
--      each partition likely read by a single thread
SELECT COUNT(*), MIN(date), MAX(date)
FROM testData
WHERE date >= '2020-01-01' AND date < '2020-02-01'
GO

-- Actual partitions with data count: 7
-- all 10 threads participate in the scan, with a range of 0.5MM to 1MM rows per thread
-- some partitions were read by multiple threads
SELECT COUNT(*), MIN(date), MAX(date)
FROM testData
WHERE date >= '2020-01-01' AND date < '2020-01-08'
GO

-- Actual partitions with data count: 1
-- all 10 threads participate in the scan, 60K to 100K rows / thread (all threads read from a single partition)
SELECT COUNT(*), MIN(date), MAX(date)
FROM testData
WHERE date >= '2020-01-01' AND date < '2020-01-02'
GO

SQL Standard Query Plan
SQL Enterprise Query Plan

n.b. This query has a group by clause - both show parallelism across partitions and within the same partition.

TMWFR
This was a documentation error. I blogged about the results of many tests comparing parallel queries against partitioned tables here:

Partitioning And Parallelism

That resulted in the documentation getting updated to reflect that there is no difference

RDBMS scalability and performance


REORGANIZE INDEX blocks other queries (SQL Server 12.0.2000.8 running in Azure)

It has been my understanding that REORGANIZEing an index should not interfere with other operations (that is, it should not block queries on the table undergoing index reorganization, and it certainly should not block queries on other tables).  However, I have a nightly index maintenance job that appears to be blocking other queries while it runs.

The query causing the block is in the format:

ALTER INDEX [indexName] ON tableName REORGANIZE

It is causing other queries to wait, even simple ones like:

SELECT * FROM tableName WHERE indexedColumn = @value

I used the sp_who2 procedure to see which queries were waiting, and which other query they were blocked by.  And again, the table undergoing index maintenance and the table in the SELECT are completely unrelated (they're even in different schemas; FWIW the table being reorganized is in dbo).

The table being reorganized has almost 500 million rows.  The index being affected is a non-clustered, non-unique index on a single bigint column used by a foreign-key.  The table itself consists of two bigint columns, one tinyint, and a couple small nvarchars.

Doesn't seem like anything too extraordinary, but I cannot figure out why it's blocking other queries.  Is there some hidden dependency that I'm missing?

Unfortunately, your understanding is a misunderstanding, and REORGANIZE requires locks for its operation. It just doesn't have as severe locking as an offline REBUILD.

Since REORGANIZE takes X locks, it has the potential to block readers.

Here's a quick demo to prove that it takes locks.

DROP TABLE IF EXISTS dbo.whyreorg

CREATE TABLE dbo.whyreorg
(ID INT IDENTITY PRIMARY KEY, junk INT)

INSERT dbo.whyreorg
SELECT TOP 1000 FLOOR(RAND(CONVERT(VARBINARY,NEWID()))*1000)
FROM sys.all_objects a
CROSS JOIN sys.all_objects b

CREATE INDEX IX_whyreorg_this ON dbo.whyreorg (junk)

BEGIN TRAN
ALTER INDEX IX_whyreorg_this ON dbo.whyreorg REORGANIZE

SELECT *
FROM sys.dm_tran_locks
WHERE request_session_id = @@SPID

COMMIT

Sample output (trimmed for space):

REORGANIZE shouldn't take locks on other tables, so I would hypothesize something in the blocked transactions in dependent on the table undergoing reorganization. Without details, it's impossible to confirm this.

Thankfully, the solution here is simple: stop reorganizing.


Which one is created first? Database or Instance? Forgive me I just learned Oracle. There exist so many terminologies in the Oracle world.

Regarding the concept of database and instance. I already know some differences between them.

The Oracle database includes all the physical files belonging to the server while the Oracle instance will provide the interface between the user and the data he wants to manipulate.

But I want to know which one is created first?

I had thought the database should be created first, before the instance. But after I read this post Creating a Database with the CREATE DATABASE Statement I think I am.

Because the manual shows theses instructions:

Step 5: (Windows Only) Create an Instance
Step 9: Issue the CREATE DATABASE Statement

Please help me to clarify it.

Introduction to the Oracle Database Instance

A database instance is a set of memory structures that manage database
files. A database is a set of physical files on disk created by the
CREATE DATABASE statement. The instance manages its associated data
and serves the users of the database.
Every running Oracle database is associated with at least one Oracle
database instance. Because an instance exists in memory and a database
exists on disk, an instance can exist without a database and a
database can exist without an instance.

A database can exist without an instance, but to use that database, you need to start an instance, then mount and open the database in it.
You can "create" a database by simply copying the files of an existing database. You do not need an instance for that. But to use that database, you need an instance.
If you want to create a database from scratch, you need a running instance first.


Is It Okay to Leave the Connection Open and Set CommandTimeout for Long Durations? I have to process daily files and it's a long process that deletes, imports and updates processes.  Like literally 20 to 30 minutes per file for several files a day.

** EDIT:  I also have some other processes that look like they are going to be going for 90+ minutes each and I'm worried about them too.   Like, is this the best strategy?

I have a console application running with something like this:

For each file:

  using (SqlConnection cx = new SqlConnection(connectionString))
        {
           SqlCommand cmd = new SqlCommand("delete from " + tableName + " where fileName = '" + fileName + "' and date > @date", cx);
           cmd.AddWithParameters("@date", fileDate);
           cmd.CommandTimeout = 1280;
           cx.Open();
           cmd.ExecuteNonQuery();
          }

And then:

    using (var sbc = new SqlBulkCopy(connectionString))
      {
               sbc.DestinationTableName = tableName;
               sbc.BatchSize = 750;
               sbc.BulkCopyTimeout = 640;
               sbc.WriteToServer(dt);
       }

And finally:

   System.Data.DataTable dt = new System.Data.DataTable();
   System.Data.SqlClient.SqlDataAdapter da = new System.Data.SqlClient.SqlDataAdapter();

   using (SqlConnection cx = new SqlConnection(connectionString))
          {
               SqlCommand cmd = = new SqlCommand("SELECT * from " + tableName + " where fileName = '" + fileName + "'", cx);
               cx.Open();
               da.SelectCommand.CommandTimeout = 1280;
               da.Fill(dt);
          }

           // this is followed by reporting on the data

So this is a low priority task but it has to get done and each file has to get processed sequentially.   Is there any problem with setting the connection timeout for so long?   We are on an internal network.   I've run DTA and taken all their indexing advice.

Any other thoughts, advice and ideas welcome.

I solved this by creating a list of tasks and then starting all the long running tasks at the end of the processing and did not wait for them to finish.  This way, the program is able to exit.


Adding a local user account into SQL Server I have a (developer) instance of SQL Server 2014 running in my machine. For quick background I'm on a small network like this:

Workgroup: HOME
Users

HOME\PCA
HOME\PCB
HOME\PCC

I'm HOME\PCA. Now I was trying to see if I can make the other machines connect to my instance using an application I'm working on. Port 1433 is working fine and if I go to PCB or PCC and test to create a new ODBC connection I successfully reach the instance.

When in SSMS trying to add HOME\PCB or HOME\PCC It will just deny and tell: Windows NT user or group 'HOME\PCB' not found. Check the name again. Checking in my Security/Logins I see that I am PCA\DevMachine (DevMachine) being my Windows login. If I try yo 'Search' in the 'New Login' dialog the only Location available is PCA.

I don't have a lot of experience in SQL Server permissions but my assumption is that it just wont let the other computers be added to my SQL Server instance because it takes that PCA is the server and so PCB and PCC should lay under PCA\PCB sort of scheme.

Is this anything close to the cause of this behavior? or, is there anything I'm missing that is out of the obvious (All network cables are plugged)?

Regards

This has nothing to do with SQL Server, is just basic Windows authentication. In a workgroup you can only authenticate using NTLM mirrored accounts. Mirrored accounts are local accounts that have identical name and and password on different machines.

So to use Windows authentication between a process running on machine A and a process running on machine B, in a workgroup, you must:

create a local user A\foo on A
create a mirrored user B\foo, on B, that has the same password as A\foo
run the client process as A\foo on A
grant permissions to B\foo on B

I strongly suggest you deploy a proper domain and use AD instead.


The equivalent of Common Table Expressions (CTEs) in MySQL I have a query which I was running in Microsoft SQL server and it worked just fine. It made use of CTEs because I joined three tables. The first two in an inner query then an outer one. However when I tried this on my web server it only has MySQL installed and I could not run the query. Is there any way to run a query with that structure? Here is a sample:

USE UNAMupdates;
with CTE as
( SELECT tbl_modules.module_code
FROM tbl_student
 JOIN tbl_modules ON tbl_student.registration_year = tbl_modules.registration_year
WHERE tbl_student.student_number='1002')

SELECT tbl_notifications.module_name,tbl_notifications.message,tbl_notifications.staff_username,tbl_notifications.date_time
FROM CTE JOIN tbl_notifications
ON cte.module_code = tbl_notifications.module_code
ORDER BY tbl_notifications.date_time DESC

What I am trying to achieve is to query all modules/subjects taken by a single student and then finding out if any notifications/news items are available for that subject.

In this particular case the CTE could be replaced with a normal subselect (a derived table):

SELECT
  tbl_notifications.module_name,
  tbl_notifications.message,
  tbl_notifications.staff_username,
  tbl_notifications.date_time
FROM
  (
    SELECT
      tbl_modules.module_code
    FROM
      tbl_student
      INNER JOIN tbl_modules ON tbl_student.registration_year = tbl_modules.registration_year
    WHERE
      tbl_student.student_number = '1002'
  ) AS CTE
  INNER JOIN tbl_notifications ON cte.module_code = tbl_notifications.module_code
ORDER BY
  tbl_notifications.date_time DESC
;

And that query would work both in SQL Server and MySQL (in any major SQL product, in fact).

Moreover, nesting is unnecessary here at all, and so you could have the same result with the following:

SELECT
  tbl_notifications.module_name,
  tbl_notifications.message,
  tbl_notifications.staff_username,
  tbl_notifications.date_time
FROM
  tbl_student
  INNER JOIN tbl_modules ON tbl_student.registration_year = tbl_modules.registration_year
  INNER JOIN tbl_notifications ON tbl_modules.module_code = tbl_notifications.module_code
WHERE
  tbl_student.student_number = '1002'
ORDER BY
  tbl_notifications.date_time DESC
;

And by the way, please consider using short table aliases in your queries. Here is what the same query looks like with short aliases:

SELECT
  n.module_name,
  n.message,
  n.staff_username,
  n.date_time
FROM
  tbl_student AS s
  INNER JOIN tbl_modules AS m ON s.registration_year = m.registration_year
  INNER JOIN tbl_notifications AS n ON m.module_code = n.module_code
WHERE
  s.student_number = '1002'
ORDER BY
  n.date_time DESC
;

It may be subjective sometimes, but I do believe that in this case readability improves greatly.


Reasons for disabling statistics auto update? I just learned that a client company I work for has decided to keep  the auto update statistics options off for some of their SQL Servers, and the DBAs manually troubleshooting performance issues when they arise.

However, this kind of does of not make sense to me. Why would you want to prevent the statistics from being updated?

My advice:

Leave 'auto update stats' on (until you run into a very good reason not to) - you don't want a big delete in middle of a day to throw off query plans until the next time you run maintenance.
However, schedule index maintenance/update statistics at a quiet time. sp_updatestats will update all stats for all tables in a database for you, but that will also cause stored proc recompiles, so choose your time carefully (i.e. sp_updatestats right before your end-of-month reporting is probably suboptimal).

How often you need to run index maintenance/rebuild stats depends on your database load, specifically how often your data is modified (i.e. INSERT/UPDATE/DELETE). If you're modifying data all over the show (i.e. a staging table for a weekly batch process), you probably want to update stats/reorganize indexes nightly. If your data is rather more static you can probably make it a weekly or fortnightly schedule.


SQL Server Audit Specification - filter DML by all but one user I'm setting up a database audit.  I'd like to capture any DML change that is issued by database user other than the service account related to the application.

In short, this is to prove to our external auditors that no users are tampering with data outside of the application.

This following script sets up a DML audit that is global to all principles.

CREATE DATABASE AUDIT SPECIFICATION [OurDatabaseAuditSpec]
FOR SERVER AUDIT [OurAudit]
ADD (DELETE ON OBJECT::[dbo].[SensitiveData] BY [public]),
ADD (INSERT ON OBJECT::[dbo].[SensitiveData] BY [public]),
ADD (UPDATE ON OBJECT::[dbo].[SensitiveData] BY [public])
WITH (STATE = ON)
GO

How do I go about modifying this audit specification to capture everything EXCEPT what is being done by the application service account?

Am I limited to specifying each principle individually or can I specify an exception?  Reading through BOL it seems I can only list principles to audit - not principles to ignore.

You can't do this at the audit specification level, but you can do it at the server audit level. So, if this audit does other things too, and you don't want to filter out those events by login, you will need to create a separate audit. Assuming you want to filter out all activity of any kind by this service account, you can do this:

ALTER SERVER AUDIT OurAudit WITH (STATE = OFF);
GO
ALTER SERVER AUDIT OurAudit WHERE server_principal_name <> N'service account name';
GO
ALTER SERVER AUDIT OurAudit WITH (STATE = ON);
GO

Depending on how they connect, authenticate, perhaps impersonate, and how the login is mapped to the database user, you may need to experiment with this and filter against database_principal_name or session_server_principal_name instead of server_principal_name. I just tested this on SQL Server 2012 and it seems to work as you would expect. I don't believe it will work on 2008 or 2008 R2 (not relevant for you, but for future readers).

I still think that filtering a large portion of your activity seems suspect and not really in the spirit of auditing. You must have pretty laissez-faire auditors if they just trust that you protect your passwords for service accounts. Someone must know them, right? An auditor's job is to not inherently trust anyone.


Too many columns, how to reduce it? User events, ratings, scores storing for each lesson I am working on a eLearning project. It has:

Courses

Courses (n courses)
-Modules (at least 3 modules in one course = n x 3)
--Lessons (at least 8 lessons in one module = n x 8 x 3 = n x 24 items)
---Quizzes (at least 5 quizzes in one lesson = n x 24 x 5 = n x 120 items)
---Training (1 training per course = n x 24 x 1 = n x 24 items)

I need to give rating for each lesson overall score (1, 2 or 3 stars rating)
I need to record users quizzes data (wrong answers, right answers etc.)

The table will grow too large if I were to store values in individual lessons as columns and quiz itmes as columns. Let's say we have 5000 users, 500 quiz items, 100 lessons.  It means, there are 500 columns across 5000 rows.

Help me reducing the columns and make the databased optimised by avoiding NULL entries.

You should turn each of those repeated columns into a separate row. Instead of a table design

QuizId
Item1
Item2
...
Item500

The table should be

QuizId
ItemNumber   -- values are 1 through 500
ItemValue

Then instead of having hundreds of columns there will be thousands, or even millions, of rows. That's fine. RDBMS is designed to handled that many without concern.


MySQL Partition based on month-year Is there a way that I can create a month-year partition on the below table?  Without having to define all the permuations of month/year combos ?

CREATE TABLE  visitor_visits (
  id int(10) UNSIGNED NOT NULL auto_increment,
  visitor_id int(10) UNSIGNED NOT NULL,
  source varchar(64) NULL DEFAULT NULL DEFAULT NULL,
  medium varchar(64) NULL DEFAULT NULL,
  campaign varchar(256) NULL DEFAULT NULL,
  page varchar(32) NULL DEFAULT NULL,
  landing varchar(32) NULL DEFAULT NULL,
  type enum('fundraiser_view') NULL DEFAULT NULL,
  visit_date date NOT NULL default '0000-00-00',
  created_at datetime NOT NULL default '0000-00-00 00:00:00',
  PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

ALTER TABLE visitor_visits ADD UNIQUE INDEX (visitor_id,type,visit_date);
ALTER TABLE visitor_visits ADD CONSTRAINT FK_visits_visitor_id FOREIGN KEY (visitor_id) REFERENCES visitors(id);

Sorry, you must create the PARTITIONs yourself.  I would do it in Perl; do you know that language, or some other?


MySQL Error 1329 - Empty Cursor Issue I am experiencing an issue with a stored procedure I wrote some time ago that has recently stopped working. The SP populates a cursor and runs through a loop processing the data in another SP.

The issue is that since recreating the SP with a simple cosmetic change, the NOT FOUND Handler for the cursor always fires immediately as if the cursor were empty, and the SP returns a 1329 No data processed error.

However, running the select query that populates the cursor succesfully returns the data fine.

Here is the code:

DELIMITER //

     CREATE PROCEDURE `Import_Leave`()
        BEGIN

        DECLARE AbsenceID INT;
        DECLARE PersonID INT;
        DECLARE EmpID INT;
        DECLARE Start_Date DATETIME;
        DECLARE Numdays INT;
        DECLARE Category VARCHAR(50);
        DECLARE WorkFlow INT;
        DECLARE exit_loop BOOLEAN ;
        DECLARE absence CURSOR FOR SELECT AbsenceID,PersonID,EmpID,Start_Date,Numdays,Category,Workflow FROM staging_holidays WHERE EmpID is NOT NULL;
        DECLARE CONTINUE HANDLER FOR NOT FOUND SET exit_loop = TRUE;

        OPEN absence;

        shoop_da_woop: LOOP

          #SELECT exit_loop;

          FETCH absence into AbsenceID,PersonID,EmpID,Start_Date,Numdays,Category,Workflow;

          #SELECT exit_loop;

          IF exit_loop THEN
            CLOSE absence;
            LEAVE shoop_da_woop;
          END IF;

    #Code to process the data goes here, but is never executed asthe cursor is coming back empty

        END LOOP shoop_da_woop;

        END//

        DELIMITER ;

I cannot figure out why this cursor is not fetching the data succesfuly when the underlying SELECT statement works fine. This SP used to work but has stopped working since the last time it was recreated. The commented out SELECT statements before and after the FETCH statement are in for debugging and they confirm that the NOT FOUND HANDLER is firing as soon as the FETCH completes.

Any help would be appreciated!

i made 3 changes to your SP:

initialize exit_loop to FALSE
change the name of each variable by adding "V_" to make them different to the column name:
close the cursor outside the loop

below the end result hopefully that solved your issue:

DELIMITER //
CREATE PROCEDURE `Import_Leave`()
BEGIN
 DECLARE V_AbsenceID INT;
 DECLARE V_PersonID INT;
 DECLARE V_EmpID INT;
 DECLARE V_Start_Date DATETIME;
 DECLARE V_Numdays INT;
 DECLARE V_Category VARCHAR(50);
 DECLARE V_WorkFlow INT;

 DECLARE exit_loop BOOLEAN DEFAULT FALSE ;

 DECLARE absence CURSOR FOR SELECT AbsenceID,PersonID,EmpID,Start_Date,Numdays,Category,Workflow FROM staging_holidays WHERE EmpID is NOT NULL;
 DECLARE CONTINUE HANDLER FOR NOT FOUND SET exit_loop = TRUE;

 OPEN absence;

 shoop_da_woop: LOOP
    FETCH absence into V_AbsenceID,V_PersonID,V_EmpID,V_Start_Date,V_Numdays,V_Category,V_Workflow;

    IF exit_loop THEN
        LEAVE shoop_da_woop;
    END IF;
    #Code to process the data goes here, but is never executed asthe cursor is coming back empty
 END LOOP shoop_da_woop;

 CLOSE absence;

 END//
 DELIMITER ;


Can drbd be used for mongodb replication or active/passive setting? From this guide we know DRBD can used for PostgreSQL Active/Passive setting:

https://www.howtoforge.com/how-to-set-up-an-active-passive-postgresql-cluster-with-pacemaker-corosync-and-drbd-centos-5.5-p4

It use /dev/drbd0 to mount PostgreSQL data path /var/lib/psql:

mount -t ext3 /dev/drbd0 /var/lib/pgsql

So is it possible to use this way to mount a MongoDB data path and where is that path?（Maybe this on CentOS 7.3: /var/lib/mongo）

If use this way, the multiple nodes will use the synced same file data but not master-slave replication. Maybe this is called master-master replication. Is it good?

Answer is NO, you cannot use it with MongoDB. Result would be corrupted data, sooner or later. MongoDB keeps active data in memory and don't know how to react if data is changed "on-the-fly" at disk.


SSIS foreach loop xlsx files Hello I see similar questions has been ask before but I am having a bit of trouble with this issue, this is using excel 2013 data files and sql server 2012.

I have a foreach loop container

This for each has a enumerator of "Foreach file enumerator" for "*.xlsx"

On the Data Flow

In the ExcelFilePath I have a base file path

And in the Expression the one of the foreach loop, but if the base file is missing the package fail, unless it finds this starting "point". what I am doing wrong?.

I've run into this before and have used 2 different approaches.
If the process should only run if files exist, I've written a c# script to set a flag 'DataExists' so I can evaluate and take different paths within the SSIS package.  The other is I have a dummy file on the directory and when I build the expression in the foreach container I use variables that will use that dummy file name/location if nothing else exists.   Hope that helps.


Count between dates and operate between rows - SQL I try to count elements between dates and operate between rows agnostically. As an example, suppose the month starts from day 2 until day 2 of next month. The table has fields {timestamp, status} and the status field has 3 different types. I do not know how to make it count between dates. Also, I would like to have the percentage of statuses.
Thank you for the help sincerely.

WHERE timestamp >= '2021-04-02'
  AND timestamp  < '2021-04-02' + INTERVAL 1 MONTH
  AND status = "done"

(If you need to discuss further, please provide SHOW CREATE TABLE and more details about the desired query.)
And this index may help with performance:
INDEX(status, timestamp)


Report Builder cannot publish to report server This is currently in a cloud lab environment. We're not allowed to join any AD domains.

We have a linux server (RHEL 6) running MySQL 5.3 and a Windows SQL Server 2012 on Win 2k8 R2.

Our laptop is Win 7 64bit running Report Builder 3.0. We created an OBDC connection to the MySQL database and are able to build reports in Report Builder. However, if we add the connection to the report server (http:// IP_ADDRESS/reportserver) it gives us the following error when trying to publish:

  For more information about this error navigate to the report server on
  the local server machine, or enable remote errors.

  Cannot create a connection to data source 'DataSource1'. (rsErrorOpeningConnection)

  An error has occurred during report processing. (rsProcessingAborted)

I am able to open SQL Management Studio locally on my laptop and connect to the remote SQL server without issue.

So what exactly is "DataSource1"? You need to make sure that the datasource for the report you're executing is configured correctly. Here is some info for that: http://technet.microsoft.com/en-us/library/ms159736.aspx


Are there any way to execute a query inside a string value in PostgreSQL? I wanted to pivot a table using cross tab function which have dynamic number of category names. By digging out the web I have found a solution.

Solution -  http://www.rummandba.com/2013/03/postgresql-dynamic-crosstab-function.html

Function Used

   DROP FUNCTION crosstab_dynamic_sql_gen_fn (source_sql text, category_sql
   text, v_matrix_col_type varchar(100), v_matrix_rows_name_and_type
   varchar(100),debug bool);
   CREATE OR REPLACE FUNCTION crosstab_dynamic_sql_gen_fn (source_sql text,
   category_sql text, v_matrix_col_type varchar(100),
   v_matrix_rows_name_and_type varchar(100),debug bool default false)
   RETURNS text AS $$
   DECLARE
   v_sql text;
   curs1 refcursor;
   v_val text;
   BEGIN
   v_sql = v_matrix_rows_name_and_type;
   OPEN curs1 FOR execute category_sql;
   Loop
   FETCH curs1 INTO v_val;
   exit when v_val IS NULL;
   v_sql = v_sql ||' , "'||v_val||'" '||v_matrix_col_type;
   IF debug THEN
   RAISE NOTICE 'v_val = %',v_val;
   END IF;
   END LOOP;
   CLOSE curs1;
   v_sql := 'SELECT * from crosstab(' || chr(10) || E' \''||source_sql ||
   E'\','||chr(10) || E' \''||category_sql || E'\'' || chr(10)|| ' ) AS (' || v_sql ||')';
   IF debug THEN
   RAISE NOTICE 'v_sql = %',v_sql;
   END IF;
   RETURN v_sql;
   END;
   $$ language 'plpgsql';

Query used to get the result of the function

 select crosstab_dynamic_sql_gen_fn('select emp_id, month, qty from sales
 order by 1','select distinct month from sales','int','year text');

The result of the query is string. Can somebody tell me how to execute the query inside the string. Below is the result of query

You pull it down to the client, and send it back to the server. That makes this a really bad idea. Even if you can dynamically generate the types of the return column in a string, which is proven here, you can't dynamically return anything with standards-compliant SQL.

If you're using psql, there is a command to automate that processes. After something returns a string of sql, you can run it with \gexec

As they say in the source above,

  As long as you get the generated sql, you may do whatever you like with that. You may create function, view etc.

So you can do that kind of stuff too. That is, things that don't return a dynamic list of columns, like issue a CREATE FUNCTION or a CREATE SQL command.


Default and temporary tablespaces for installing sample Oracle database schemas? What should I specify as default and temporary tablespaces (users and temp parameters of mksample.sql script) for installing sample Oracle database schemas?

In my environment I call the script like this:

$ORACLE_HOME/bin/sqlplus SYSTEM/systempw@\"localhost:1521/ORCLPDB1\"
SQL>@mksample systempw syspw hrpw oepw pmpw ixpw shpw bipw users temp /home/oracle/log localhost:1521/ORCLPDB1

Do they exist in a clean Oracle 18 instance? Or should I create them?

You can make them any existing temp and permenant TS you want.  I'll bet TEMP and USER already exists. You can answer your own question of 'to they already exist' by simply querying DBA_TABLESPACES.


When to use DISTINCT When is it good to use SELECT DISTINCT .. and when not?

Basically whenever I see DISTINCT keyword I start to get cautious as most of the cases it is ends up finding that actual query has some problems (not selective enough, bad joins, incorrect logic and so on).

Do you have similar experience or is it just me?

SELECT DISTINCT is sometimes an indication that someone has 'fixed' a query that returns duplicate rows in a naive way. This (mis-)use is probably more common among those with relatively little database experience.

That said, some people (Chris Date, for example) argue that DISTINCT should be the default qualifier for SELECT; we should have to write SELECT ALL to return a non-relational result (relations do not contain duplicate tuples by definition).

My view is that there is nothing inherently wrong with SELECT DISTINCT. It can be the most natural way to express a query. If the execution plan and performance is acceptable, I see no sensible reason to mandate that it should never be used.

At the same time, it is clearly wrong to simply add DISTINCT at the top level of a complex query to eliminate duplicates at a late stage, when the query should be simply written more efficiently.


Full text catalog does not allow me to hit on singular forms I have created a full text catalog for a column under database name -> "Storage" -> Full Text Catalogs. It is accent sensitive and "default catalog" is set to false. The language for the column "SearchData" is set to swedish.

I am using a stored procedure that calls the contains function like this:

CREATE PROCEDURE [dbo].[SearchPages]
    @Term varchar(200)
AS
BEGIN
    SELECT pc.SearchData from PageContent pc
    where contains(pc.SearchData, @Term)
END

When I search för "smörgås" (swedish för "sandwich"), I hit on "smörgåsbord", but not on "smörgåsar" (swedish for "sandwishes").

Apparently, it only hits on words that are compositions containing the search term, but not on plural forms.

Our customers want to search putting a singular form in the search box.

Is that possible to obtain?
It would also be nice if you could search for "goose" and find "geese".

You could use either CONTAINS with FORMSOF(INFLECTIONAL, smörgås) or FREETEXT

CONTAINS:

SELECT pc.SearchData from PageContent pc
WHERE CONTAINS(pc.SearchData, 'FORMSOF(INFLECTIONAL, smörgås)', LANGUAGE 'Swedish')

FREETEXT (includes INFLECTIONAL and some other stuff automatically):

SELECT pc.SearchData from PageContent pc
WHERE FREETEXT(pc.SearchData, 'smörgås', LANGUAGE 'Swedish')