Which is faster? Multiple update queries in a cursor loop, or a single query? Which is faster? Multiple update queries in a cursor loop, or a single query?
For example, in this case, which is faster? This query is in a trigger.

Multiple Query in loop

DECLARE done INT DEFAULT FALSE;
DECLARE record_id INT;
DECLARE cur CURSOR FOR SELECT id FROM table1 WHERE column = 0;

OPEN cur;

  users_loop: LOOP

     FETCH cur INTO id;

     IF done THEN
         LEAVE users_loop;
     END IF;

     UPDATE table2 SET column = 0 WHERE id = id;

  END LOOP;

CLOSE cur;

Single Query

 CREATE TEMPORARY TABLE tmp_table (id int(10));

 INSERT INTO tmp_table SELECT id FROM table1 WHERE column = 0;

 UPDATE table2 SET column = 0 WHERE id IN(SELECT id FROM temp_table);

 DROP TABLE IF EXISTS tmp_table;

Neither -- You need to present a 3rd option.

Option 1 is slow because it is going back and forth between the Stored Routine and the server.  (This is not as bad as between the Client and Server, but it is still extra effort.)

Option 2 is slow because of the tmp table and subquery.

Option 3 is something like

UPDATE   table2
    JOIN table1  ON table1.id = table2.id
    SET   table2.column = 0
    WHERE table1.column = 0;

(I assume id is the PRIMARY KEY in each table?)

Important:  table1 needs INDEX(column).

See "multi-table update" in the online reference manual.


Database archive solutions In continuation to a question posted by me on Is it a good idea to move high-volume and highly-accessed tables to a separate database?, am looking out for different techniques/solutions available for database archiving in PostgreSQL.

Few solutions I can think of are:

Table partitioning
Separate tablespace and/or schema
Moving archived records/tables to a different harddisk

Any other suggestions/pointers/solutions are really welcome and appreciated.

NOTE: We're running PostgreSQL v9.1.3 on CentOS5.2

My suggestion about archiving:

Create archive_tablespace (if you want you can separate hardware on archive)
Create tables. For example we want to archive table posts.

create table  posts_all ( LIKE public.posts)  ;
create table  posts_archive () inherits  ( public.posts_all)  ;
alter table  public.posts  inherits ( public.posts_all ) ;

After that we will have 2 new tables: public.posts_all (with the same columns like in posts) to query all posts (archive and production) and public.posts_archive to query all archive posts. Public.posts will inherits from posts_all.
Inserts should go in a old way (to table public.posts) unless you will write triggers on posts_all to redirect inserts to posts table. If you have partitioning it will be more complicated. With working application and before old data migration you don't have to change anything in application code to work with this approach.
Create schema archive for logical separation. My suggestion will be to separate archive data by some time period (year or month) if possible (archive_2005).
Create archive tables in archive_year schema

create table archive_2005.posts (
  check(record_date >= '2005-01-01 00:00:00'::timestamp
    and record_date <  '2006-01-01 00:00:00'::timestamp)
) inherits (posts_archive) tablespace archive_tablesapce;

After that you will have new table posts in schema archive_2005 and postgresql planer will know that data there is only in designed time period. If you query by another time period postgresql will not search in this table.
Create functions/procedures/triggers to move data to archive tables.
Do archive once for a time period (year here) and vacuum old table or do it automatically by triggers (heavier on autovacuum). There are many advantages and disadvantages in both techniques.

If implemented:

Can query archive (select * from posts_archive), all (select * from posts_all) and production (select * from public.posts) data separately
Can do dump archive schemas separately and drop cascade on them in easy way.
pg_dump -s archive_2005 datase_name
drop schema archive_2005 cascade; --be careful because it removes all related tables
Old data separated physically by tablespace and logically by schema.
Quite complicated structure to manage archiving process
Can create different indexes on production and archive tables to optimize queries to both (smaller and specialized indexes = faster queries and less space required)
If you have partitioned tables (by year or month) archive process will be just to move whole table to archive_tablespace or just alter it to inherit from posts_archive (I didn't test this)
If you don't want to access old (archived) data you don't have to change anything in application.

This is general technique and you should adapt it to your needs.
Any suggestions to improve this?

Further reading: PostgreSQL inheritance, partitioning


when to open and close symmetric keys I am adding column encryption to a few tables using symmetric key encryption. In each one of the procs, I write code to open and close a keys. It seems a bit verbose to open and close them in each proc. (example below )

Can I leave a key open permanently?
MSDN says keys close when a session ends. Is it possible to open a key at the beginning of a session and leave it open for all queries?

CREATE PROCEDURE [dbo].[person_sel]
AS
BEGIN

open symmetric key demoKey decryption by certificate demoCert;

select
    personId,
    name,
    email,
    cast( DECRYPTBYKEY( pin_enc) as varchar(MAX)) as pin
     from person;

close SYMMETRIC KEY demoKey;
END

Yes.  Create (database) MASTER KEY first, then  BACKUP, DROP, and CREATE the certificate (importing from earlier BACKUP), but this time WITHOUT a password.  This will encrypt the certificate's private key for the certificate using the database master key, which will automatically open the private key as needed.

Realize by doing this, you are exposing your data (via the private key for the cert) to anyone that has access to the database and sysadmin to the server.  In reality, all you have done is kept the data-at-rest encrypted on disk.  Anyone with access to your backups and the service master key can decrypt your data.

Make sure you follow best practices for keys (and certificates) in SQL and backup the database master key.

You can view the private key encryption type by looking at the pvt_key_encryption_type_desc column in sys.certificates database dmv.


Postgres extremely slow after upgrade from Version 10 to 11 Related to this:PostgreSQL extremely slow after upgrade from 9.3 to 9.4
but trying to understand better...

My queries was slow after upgrading to Postgres Version 11.5 from 10.7
I first ran ANALYZE on the DB but still was slow!
so I can VACUUM ANALYZE on the DB, and was still slow...

as a last resort, I am running VACUUM FULL ANALYZE on a table by table basis using psycopg2 with autocommit on (http://initd.org/psycopg/docs/connection.html#connection.autocommit) to be able to run it in psycopg2

one table is taking hours to do ...
while I wait, I can't help but wonder what went wrong.
The reduction of the query performance was mainly from UPSERT.

The output of pg_upgrade includes this:

...

Upgrade Complete
----------------
Optimizer statistics are not transferred by pg_upgrade so,
once you start the new server, consider running:
    ./analyze_new_cluster.sh

...

We found horrible performance straight after the upgrade until we ran this, lesson learned to read the output :)


Weed out certain rows where timestamp is within interval Given a table structure:

create table contacts (
  contact_id int(8) not null auto increment, #primary key
  pid int(8) not null,                       #personal id
  contakt_time datetime,                     #time of contact
)

Where each person can have multiple contacts with the system at any point in time.

What is the best way to filter out contacts by a given person in a certain time frame?

E.g. if:

SELECT * FROM contacts where pid = 1201;

yields:

contact pid     contact_time
-----------------------------------
10530   1201    2014-01-04 14:33:00
11475   1201    2014-01-13 18:16:00
13460   1201    2014-02-03 12:23:00
14220   1201    2014-02-10 16:55:10
15599   1201    2014-02-24 21:03:34
16488   1201    2014-03-06 12:45:00
16499   1201    2014-03-06 14:55:00
16900   1201    2014-03-10 11:18:00
19108   1201    2014-04-02 10:40:00
20954   1201    2014-04-22 17:24:00

I would need to remove all contacts except the first one in a given time window. For instance, all contacts happening within a 5 hours window after each contact. In the example above, the result would no longer include contact #16499 as this is too close to contact #16488 (< 5 hours apart).

Thank you for any help


sql server concurrency performance We have encountered some performance issue on our production environment.

we found that when active sessions go up above 25, the usage of CPU reach to 100%
and it takes long time to go down.

The environment we have:

Product Microsoft SQL Server Enterprise Edition 9.3(sp2)

CPUs    2(Xeon 2.13)

Memory  7G

snapshot of session detail1

Active Sessions  25

Active Transactions  496

Idle Sessions    289

Blocked Transactions 29

snapshot of session detail2

Active Sessions  59

Active Transactions  885

Idle Sessions    267

Blocked Transactions 49

I'd like to know:

whether 2CPUs can handler 25 active sessions (500 active transactions ) well.PS:
we have test that without concurrency request,one transaction ,which read/write 5 tables, takes about 1sec in application level.
whether the blocked transactions takes more CPU usage.PS:the blocked transactions is mainly because of locks on 2 tables.

what's the solution:
Add CPUs ?or tuning application(java/hibernate) to shorten this transaction and to decrease blocks on the table?

Your options to have a good picture of the situation when everything is crawling:

use server side traces to see the longest and most heavy sessions
use the Who is Active stored procedure by Adam Machanic - to save the information of the moments when the server is loaded - fantastic free script
use the Activity Monitor from Management Studio just to get a visual glance (Cpu and IO are the ones that I found the most useful) - pretty good tool included in Management Studio
use a 3rd party free tool - Confio Ignite Free - that's very good for moments when everything is slow and need to see details about the wait stats, blocking..etc. - fantastic free tool
check statistics to be up to date
check if indexes are fragmented and, if so, defrag them
follow the other advices of checking missing indexes, design
check the possibility of using the snapshot isolation level in your database (you have high blocking so it's good to see if you really need your current isolation level)

And good luck investigating the problems :-).


Running the DBT2 test results in 0.00 NOTPM I'm trying to run the MySQL DBT2 Benchmark Tool, I have unzipped the archive and run the following commands:

./configure --with-mysql
make
make install
mkdir /tmp/dbt
datagen -w 3 -d /tmp/dbt --mysql
scripts/mysql/mysql_load_db.sh --path /tmp/dbt --mysql-path /usr/bin/mysql
scripts/run_mysql.sh --connections 20 --time 300 --warehouses 3

But got the following output:

...

Stage 3. Starting of the test. Duration of the test 300 sec
STARTING DRIVER COMMAND:   /root/dbt2-0.37.50.3/src/driver -d 127.0.0.1 -l 300 -wmin 1 -wmax 3 -spread 1 -w 3 -sleep 300 -tpw 10 -outdir /root/output/14/driver -p 30000 -warmup 0 -cooldown 20 -intermediate_timer_resolution 0
Shutdown clients. Send TERM signal to 5154.
Shutdown driver. Send TERM signal to 5190.

Stage 4. Processing of results...
                         Response Time (s)
 Transaction      %    Average :    90th %        Total        Rollbacks      %
------------  -----  ---------------------  -----------  ---------------  -----
    Delivery   0.00          N/A                      0                0  100.00
   New Order   0.00          N/A                      0                0  100.00
Order Status   0.00          N/A                      0                0  100.00
     Payment   0.00          N/A                      0                0  100.00
 Stock Level   0.00          N/A                      0                0  100.00

0.00 new-order transactions per minute (NOTPM)
0.0 minute duration
0 total unknown errors
0.00 rollback transactions
0 second(s) ramping up

scripts/run_mysql.sh: line 632:  5154 Killed                  nohup $CLIENT_COMMAND > $OUTPUT_DIR/client.out 2>&1
scripts/run_mysql.sh: line 632:  5190 Killed                  nohup $DRIVER_COMMAND > $OUTPUT_DIR/driver.out 2>&1
Test completed.

During the execution, SHOW PROCESSLIST did show that 20 clients were connected, all sleeping.

Any idea why no transactions are executed?

I have done the following to reproduce the output you are seeing from dbt2.

Installed Centos6.4
Installed MySQL 5.1.69 via YUM
Installed Innotop to view in real time all queries and open transactions

Ran the bench as you did:

[root@centos-cef dbt2-0.37.50.3]# scripts/run_mysql.sh --connections 20 --terminals 5 --time 300 --warehouses 3 --verbose

Innotop only showed 20 active clients but no open queries or transactions

DBT2 provides several logs. On the system I used for testing, there was a main directory /root/output and then a number for each execution.

/root/output/N where N is the execution number.

In the file /root/output/3/client/error.log, I found the following information

Tue Jul 30 16:57:23 2013
Microseconds : 998724
tid:-1511446784 mysql/dbc_payment.c:32
mysql reports SQL STMT: stmt ERROR: 1305 PROCEDURE dbt2.payment does not exist
Tue Jul 30 16:57:23 2013
Microseconds : 998753
tid:-1511446784 mysql/dbc_common.c:97
ROLLBACK INITIATED

Tue Jul 30 16:57:28 2013
Microseconds : 859693
tid:-1511713024 mysql/dbc_new_order.c:88
mysql reports: SQL: call new_order(2, 5, 984, 1, 9, 90056, 2, 8, 47872, 2, 3, 48592, 2, 7, 21925, 2,                                 7, 96574, 2, 5, 53239, 2, 8, 30640, 2, 4, 39619, 2, 10, 86015, 2, 8,                                 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0,                          0, 0, @rc),  ERROR: 1305 PROCEDURE dbt2.new_order does not exist
Tue Jul 30 16:57:28 2013
Microseconds : 859735
tid:-1511713024 mysql/dbc_common.c:97
ROLLBACK INITIATED

I'm seeing ROLLBACK INITIATED throughout the log. I am also seeing that certain stored procedures are not installed such as dbt2.payment

So I looked a little further and there is another script that has to be run in order to create the stored procedures in the dbt2 database.

scripts/mysql/mysql_load_sp.sh --database dbt2 --client-path /usr/bin/

Which gives the following output:

Load SP: delivery
Load SP: new_order
Load SP: new_order_2
Load SP: order_status
Load SP: payment
Load SP: stock_level

Have you run

scripts/mysql/mysql_load_sp.sh --database dbt2 --client-path /usr/bin

on your server to load in the stored procedures?

After loading in DBT2's stored procedures, I ran DBT2 again and this time I did see transactions in Innotop's output

[RO] InnoDB Txns (? for help)                             localhost, 2h7m, InnoDB 1s :-), 765.68 QPS, 22/0/0 con/run/cac thds, 5.1.69

History  Versions  Undo  Dirty Buf  Used Bufs  Txns  MaxTxnTime  LStrcts
      6       102  0 0       1.56%     99.80%    21       00:00

ID   User  Host       Txn Status  Time   Undo  Query Text
217  root  localhost  COMMITTED   00:00     0  COMMIT

I noticed though as you did, that DBT2's output was still showing O's everywhere, so I increased the number of warehouses to 30. My thinking was that there wasn't enough transactions or activity to generate useful results

scripts/run_mysql.sh --connections 20 --time 300 --warehouses 30 --verbose

Stage 4. Processing of results...
                         Response Time (s)
 Transaction      %    Average :    90th %        Total        Rollbacks      %
------------  -----  ---------------------  -----------  ---------------  -----
    Delivery   3.32      0.004 :     0.001          136                0   0.00
   New Order  44.59      0.027 :     0.095         1826             1654  90.58
Order Status   4.20      0.002 :     0.001          172                0   0.00
     Payment  42.86      0.007 :     0.014         1755                0   0.00
 Stock Level   3.57      0.010 :     0.000          146                0   0.00

And as you can see, DBT2 sent back appropriate results this time.


Postgres/PostGIS is returning data from a table that doesn't exist (I'm a newbie -- show me why I am wrong) I am following a tutorial for PostGIS. I have uploaded all the example datasets (all shapefiles) into a PostgreSQL database, although I have modified the names so they do not all match the names of those in the tutorial. I then ran the following code from the tutorial:

df = pd.read_sql_query('''
  SELECT
  subways.name AS subway_name,
  neighborhoods.name AS neighborhood_name,
  neighborhoods.boroname AS borough
  FROM neighborhoods AS neighborhoods
  JOIN stations AS subways
  ON ST_Contains(neighborhoods.geom, subways.geom);''', engine)

This worked and returned the expected output. But when I went back through this query I noticed that the table "subways" was referenced (maybe this is where I am confused) but I don't have a table named "subways" because I had titled this table "stations". Indeed, here is the list of available tables in the db:

pd.read_sql_query('''SELECT table_name FROM information_schema.tables
   WHERE table_schema = 'public';''',engine)

       table_name
   0   geography_columns
   1    geometry_columns
   2     spatial_ref_sys
   3       neighborhoods
   4      raster_columns
   5    raster_overviews
   6             streets
   7               solar
   8            stations
   9              census
   10           vehicles

So, why is this query working?

I think I am asking for rows from a table named subways with the subway.name in the SELECT portion of the query, isn't it true?

FROM ...
     JOIN stations AS subways
    ...

This is called an alias. You aliased stations as subways. So for the rest of the query (ON, SELECT part) the table is called subways and you can only reference it as subways.


Getting pg_dump and pg_restore right for postgresl (9.3.10) I'm to transfer a postgresql 9.3.10 DB (~55GB) from Azure VM to an AWS EC2. Being a novice, this question is about getting the process right. I've seen this post, and need to go a bit beyond that:

Is dumping the live production database on a busy server perfectly safe? Docs explain how pg_dump is non-blocking, so I'm inclined to think it is. In your experience, does pg_dump always go off without a hitch on production, or are there exceptions? My other (unpleasant) option is to shut down the server, creating a "hard" maintenance window.
The docs mention using --format to control dump format. c (custom) looks promising, but I found the description vague in terms of how to use it. Should I simply try the pg_dump command like so:  pg_dump -U postgres -Fc db_name > db.dump? Any other practically useful flags would be of interest as well.
A simple guide I've read recommends wrapping the whole restore into a single transaction: psql -1 restored_database < backup_file. That gives a binary outcome by failing in entirety if there are restoration errors. i) What could cause restoration errors? and ii) the restoration command I originally planned on using is pg_restore --verbose --clean --no-acl --no-owner -U myuser -d mydb latest.dump. That looks right?

It's perfectly safe with especially with --serializable-deferrable however you're not answering the real question which is what do you do with data inserted while you're dumping and moving the database. The best way, in my opinion is the hard maintenance window.
I would use -c.
an inconstant backup or a failure to load because of a missing extension causes restoration errors, though I don't think you'll have that problem.

Since you know Azure supports 9.6 (they say so here), I would

back up the data directory
back up the database
upgrade to 9.6 locally
run it and make sure it works.

If it works, pg_dump and move move to Azure.


Is dirty-read a problem in an always-commit database? Is this statement true:

  If a DB engine disallows rollback and always uses a redo log to enforce commits, then dirty-reads never happen (because all changes will be commited finally).

If so, are there any use cases where dirty reads can be avoided by disallowing rollback?

The only obstacle I can think of is for a transaction to violate some constraints so that commits always fail. But there may be some use cases where such constraints don't exist at all.

If you still allow transactions that span multiple statements, you would still potentially have dirty reads.

Imagine that I have a simple banking application where session 1 is trying to transfer $100 from account A to account B.  Session 1 starts a transaction and subtracts $100 from A.  Now session 2 runs a report that tries to make sure that money hasn't gone missing (which is somewhat useful if you are running a bank).  If session 2 is allowed to read the updated balance from A and the un-updated balance from B because session 1 is in the middle of a transaction, it has done a dirty read.  The fact that session 1 is going to eventually commit the change does not change the fact that session 2 was able to get an inconsistent view of data.

Of course, a database that disallowed rollbacks but allowed transactions that spanned multiple statements would be rather problematic.  If I subtract $100 from A and then try to add $100 to B but I encounter an error trying to update B (perhaps I discover that there is a hold on B or that B has been closed), I'm in a pretty unfortunate position.  Assuming I don't want $100 to disappear into thin air (which will make A rather unhappy), my application has to catch the exception and manually implement a rollback by putting the $100 back.  In real applications where transactions involve changes to a bunch of different tables, some of which are done by triggers that the application may not be aware of, that is a non-trivial challenge.


Have a look at MS Docs about POWER function:

  Return Types
  Returns the same type as submitted in float_expression. For example, if a decimal(2,0) is submitted as float_expression, the result returned is decimal(2,0).

CAST left operand as DECIMAL(18, NumDecimals)

-- without CAST

SELECT POWER(1 + (52/100.0) * 0.23, 0.268);
GO

| (No column name) |
| :--------------- |
| 1.03073935       |

-- CAST as DECIMAL(18,12)

SELECT POWER(CAST(1 + (52/100.0) * 0.23 AS DECIMAL(18,12)), 0.268);
GO

| (No column name) |
| :--------------- |
| 1.030739345889   |

dbfiddle here


Proper Design for Datamart Invoice I'm trying to design a set of fact tables for a datamart that will work in an ssas cube for one of our clients.

One of the challenges I'm facing is that there appears to be some disperencies between order item pricing and order pricing such that the summation of all of the order items does not equal the pricing of the order.  This occurs because admins are aloud to go in after the fact and change an order price inorder to discount the customer.  However no record of the discount exists.

In this case would I want to create a fact table for the order and then a fact table for the order item?  If so I'm assuming I would need to create a key for the fact tables to maintain the relationships.

Is this an acceptable practice or will I run into problems with this type os solution when I go to build the cube.  Any thoughts on a better way to model this would be appreciated.

Thanks in advancce

The simplest approach I can think of is to have one fact table for order items, and have two price columns in that fact table: original price, and discounted price. Original price would be the price as listed on the line item. Discounted price would be calculated by totaling the price of all line items on the order, calculating the percent difference between that and the total stated on the order header, then scaling down the prices of the line items using that percentage.


How to index of filter extended event files in Sql Server I am collecting errors data using the error_reported event and saving the data on XEL files. The problem is that there are hundreds of thousands of errors over there and querying them can take a lot of time.

One option is to prevent errors from being logged twice if they happened on the same object in the same timestamp. I really don't think the second option is possible.

Second option is to index the file in a way that it will be quick to filter errors by the error text and the object that caused them.

So far I thought of creating a separate process that will run in the background and writes filtered values from the files to an indexed table, but its a bad solution. That process would need maintenance and could be expensive on IO resources.

So far I didn't find a way to solve it on the extended event level.

Does anyone have a better idea?

You could potentially filter the events using a custom program, but that would require a fair amount of work (mostly in testing) to ensure that you don't adversely impact the SQL Server.

You're probably best off simply logging all the errors, and sorting through them later (possibly on a different computer).  You can read the XEvent files with the function sys.fn_xe_file_target_read_file .


ODBC connection for Active Directory user who doesn't log in to Windows Do you think it is possible to create a SQL Server ODBC Connection for an Active Directory user who doesn't log in to Windows.

Ideally this type of users will be used in a batch process. So, another person logs in and creates a batch process and runs it with another user.

Note: I don't want to enable SQL Server authentication. Instead would like to use Active Directory.

Although you cannot login to SQL Server unless you use either a SQL Server Login or a Windows Account with permissions to connect to the SQL Server, you could create a low-permission user that can EXEC certain stored procedures.  Those stored procedures could then contain code that switches into the context of another, more highly privileged account.

EXECUTE AS Login = 'DOMAIN\User';
SELECT * FROM sys.databases;
REVERT

Read more about EXECUTE AS on MSDN.


How to implement many-to-many relation between three table? I have these tables

StudentInfo:

  subjectId(primary key)
  name
  location

SubjectInfo:

  subjectId(primary key)
  subjectName
  totalmarks

TeacherInfo:

  teacherId(primary key)
  teacherName

Grades:

  studentId(foreign key from studentInfo)
  subjectId(foreign key from subjectInfo)
  marks
  attendence

Junctiontable:

  studentId(foreign key from studentInfo)
  subjectId(foreign key from subjectInfo)
  professorId(foreign key from professorInfo)

There is a many-to-many relation between student, professor, subject.

That is

a student can have many subject to enroll with and can be taught by many
a teacher can teach many subject and many student
a subject can be enroll by many student and can be taught by many teachers

Questions:

Is that junction table correct or should I have 2 junction tables?
What is the SQL query to find the report for student on the basis of roll no? The detail in report should be subjectname, subjectmarks, subject, attendence, professor name who taught this subject

The query that I tried returns redundant data.

select
    teacherInfo.teacherName,
    grade.marks,
    grades.attendence,
    subjectinfo.subjectName
from
    teacherinfo
join
    junctiontable on junctiontable.teacherId = teacherInfo.teacherId
join
    studentinfo on studentinfo.studentId = junction.studentId
join
    grades on grades.studentId = studentinfo.studentId
where
    studentinfo.studentId = 1;

A teacher can teach the same class several times in the same day and many times over years. A student can re-take a class. But, a teacher can only teach 1 class at a time and a student can only take one class at a time. I would add class instance and class enrollment tables as your junction tables. Your initial attempt isn't that far off from this:

1.studentInfo
  studentId(primary key)
  name
  location

2.subjectInfo
  subjectId(primary key)
  subjectName
  totalmarks

3.teacherInfo
  teacherId(primary key)
  teacherName

4.classInstance
  classId(primary key)
  subjectId(foreign key from subjectInfo)
  teacherId(foreign key from teacherInfo)
  year, semester, time, building, room nr, etc

5.classEnrollment
  studentId(foreign key from studentInfo)
  classId(foreign key from classInstance)
  marks
  attendance


Cross Cluster Migration 2012 to 2016 I've tried researching cross-cluster migrations but other than the one white paper Microsoft put out I can't find any information.  Can this be used to upgrade from SQL 2012 to SQL 2016 and if so would there be any issues with replication?  If somebody has experience doing this can you provide any tips?

Going from SQL Server 2012 (Windows 2008 R2) to SQL Server 2016 (Windows Server 2012 R2)

Cross-Cluster Migration

I have a largely automated process to upgrade versions and replicate data back to the old version using transaction replication without a snapshot init.  I need minimal downtime in this environment so we don't have time to make the databases generate a snapshot and send it back just in case we need to roll back.  You might allow yourself downtime but in that case if you will use a snapshot make sure to script out the 'create database' section with all the proper filegroups and options.  You'll need to snapshot back to a database.  It can create it for you but you're better off doing it in advance.

Be careful of PK constraints if you are not going to init.  If you get even 1 row in your old table your PKs which doesn't match, you will get PK conflicts and errors.

Have a backup plan handy so script out snapshot replication or something else like Red Gate Data Compare just in case things go sour.

Test this process repeatedly in staging and automate as much of it as you can.  That way it's consistent and easy in the day off.  Preferably check your code in.

Ensure you make the databases not writable and that all of your data is fully synced up, while ensuring nothing can write to the old db before you cut over clusters.

You can also look at switching your cluster IP address if you have tons of clients that you can't keep track of, but if you can, it's better to get a new IP.  If you change IPs make sure to automate that as well and ensure you update DNS/RDNS and add a CNAME if you need to the new machine with the old name just in case clients connect by IP.

If you have enough downtime capability you can roll back at your leisure.  Good luck!


Backup / Restore Distributor I have a database architecture with:
=> 1 distributor running on a dedicated instance of SQL Server 2005
=> 2 publishers running each on a dedicated instance of SQL Server 2000 and SQL Server 2005
=> A dozen subscriber running SQL Server Express / Sql Server 2000

I need to move the distributor instance to an other server with an other IP address, and I would like to have to re-publish-synchronise the whole architecture.
How can I can Backup / Restore the distributor on a new server and have the publishers and subscribers connect to its new ip address?

Replication is tied to the machine name of the distributor.  If the new instance will have the same machine name then stop the agents, backup the distributor, and restore it using the keep_replication setting as on.  Then restart the agents.

If the hostname of the distributor is changing you'll want to tare down the replication and rebuild it.


PostgreSQL: cache, disk, replication. What order? I am thinking of using two servers in Master/Slave replication
I want to add following condition: RAM size = dataset size * x, where x > 2.
After that, I plan to force PGSQL to keep all data in RAM cache as long as possible
Question
A write request comes. Which of the following will happen ?

Will it be first cached, then replied, then replicated
Will writing precede replication, and replication precedes caching

PostgreSQL doesn't have a result cache. So the only caching that happens is in shared_buffers and the kernel buffer cache - both of which must inhertently be filled in the process of generating the query results, so it must happen before  (or while) results are sent.

WAL is written as a transaction executes, and WAL archives get replicated as they're filled. WAL records also get streamed over the streaming replication protocol as they're generated. So replication occurs during the execution of a query, but it's not as simple as "before" or "after" results are sent.

Results can be sent incrementally to the client. Replication can occur incrementally. Really, both happen at the same time.

The only guarantee is that a commit will not be replicated and applied to replicas before it commits on the master.

If you're using synchronous replication there is the additional guarantee that a committed transaction is replicated to all nodes before a successful commit is reported to the client.

I really think you're asking the wrong question here, though. You're making a bunch of assumptions and asking questions based on those assumptions. It would be helpful if you'd take a step back, to the why behind these questions, and explain what you are trying to achieve with this. What's your goal? What're your requirements? What outcome do you seek?


Is it correct to use IP addresses (instead of hostnames) while configuring Mongo DB cluster? Does it cause any issues? I have been using Mongo DB Cluster for some data analysis (Mongo DB version 2.4.5).

My configuration is as follows:

1) Node1 - mongoconfig server, mongod shard

2) Node2 - mongoconfig server, mongod shard

3) Node3 - mongoconfig server, mongos

4) Node4 - mongos

All the nodes above have a unique hostname at OS level (output of shell command "hostname")

Is it correct to use IP addresses instead of hostnames while configuring this Mongo DB cluster. There are issues that I am facing due to DNS server and I would prefer to configure the entire cluster (addresses of config servers, shards, etc.) using IP addresses instead of hostnames.

Is this the correct way to configure the cluster or will it cause any issues?

Generally it is not recommended to use IP addresses to configure a cluster. You have not mentioned your environment, but it is common for IP addresses to change in many environments with a reboot, or perhaps you will need to move one or more of the nodes to a different host in the future.

Should that happen, your IP address will change but if you use hostnames (even those in the hosts file rather than DNS) it gives you a layer of abstraction and means you can have the hostname remain the same, not have to alter your database configuration for such a move.

This becomes particularly important for config servers in a sharded environment. An altered IP address is procedurally the same as an altered host name, and as you can see from this procedure, that means that moving a config server will require downtime for your cluster.

With all that said, for testing or for an environment where you can tear everything down and start again (or similar), these concerns do not really apply and you can use IP addresses in your configuration. For any system that will run long term, or any system that will run in production and require minimal interruption, then the use of hostnames over IP addresses is certainly recommended.


Remove all Null values from Sql table at once I am very new to Teradata and SQL environment. I am trying to remove all the null values from sql table in teradata using
ISNULL(COLUMNNAME,0). But it gives an error that "the data type does not match the defined data type name". Also, would like to know if there is any way of removing all NULL at once rather than column wise writing the above query again and again.

I'm not sure I completely understand what you are trying to do. ISNULL(COLUMNNAME,0) is a function that returns 0 if COLUMNNAME contains a null (it does not change anything in the table, you will have to do an update to modify the table).

The error message you get is likely because COLUMNNAME is a different type than a number. If COLUMNNAME is of type string you can use:

ISNULL(COLUMNNAME, '')

if it's of type date you need a date:

ISNULL(COLUMNNAME, DATE('1900-01-01') ) -- I just invented one here, use one that makes sense in your context

and so forth. As you noted, applying a function to a column does an implicit rename of the resulting column. Most DBMS generates a rather cryptic name so it makes sense to do an explicit rename as in:

ISNULL(COLUMNNAME, '') AS COLUMNNAME

if you want to preserve the name.


Forced Parameterization generating duplicate plans https://www.brentozar.com/pastetheplan/?id=H1qoS7BSu
I am using Azure SQL 18 vCore db with Forced Parameterization, and MAXDOP 8.  The vCore count may change much lower for other dbs but for the same query.  Running sp_blitzcache has shown a query with the following warnings:
Compilation Timeout, Forced Parameterization, Multiple Plans (11), Plan created last 4hrs, Long Running With Low CPU, Many Rows Table Spool, non-SARGables

The main table has about 600Krows, and the two supporting tables have about 200 rows.
I'm a newbie curious about a number of these things.  There are about 2700 plans in the cache currently for this query_hash, all using the same query_plan_hash.  Compilation times out a very large portion of the time (can't confirm that it's every time).  I'm about to turn on traceflag 4199 in hopes of getting rid of the timeouts, and the performance of the query is acceptable within the app context that I know of, but I'd like to understand what is stumping the compiler.  Thanks for any help on this!

You will not be able to turn on Trace Flag 4199 in Azure SQL Server. You will get an error message. In fact, you cannot turn on any trace flags (globally or session scope) in the Azure SQL database. At the time of writing this, there were 24 trace flags enabled with Global Scope for Azure SQL database. You can check the list by running DBCC TRACESTATUS.If you want to mimic the same behavior of TF4199 run the below T-SQL statement.
ALTER DATABASE SCOPED CONFIGURATION SET QUERY_OPTIMIZER_HOTFIXES = ON ;
Reference : ALTER DATABASE SCOPED CONFIGURATION (Transact-SQL)
I suggest you read this article:
Understanding Optimizer Timeout and how Complex queries can be Affected in SQL Server by Joseph Pilov
Plus look at the other hints you are getting from running sp_blitzcache and try to resolve those.

Many Rows Table Spool

Table Spool by Hugo Kornelis

Nested Loops Joins and Performance Spools by Paul White

non-SARGables

Non-SARGable Predicates by Brent Ozar

The Two Ways to Fix Non-Sargable Queries by Brent Ozar


Zeroing WAL segments in Postgres We have a relatively low-volume Postgres database with continuous archiving set up to compress each WAL segment and send it to S3. Because it's a low-volume system, it hits an archive_timeout every 10 minutes or so and archives the mostly-unused WAL segment, which used to compress very well as it was mostly just zeroes.

However, Postgres recycles its WAL segments to avoid the cost of allocating new files at each WAL switch, which is useful in a high-load situation but it means that after a burst of heavier-than-normal activity our WAL segment files are now full of junk from previous segments and do not compress very well at all. We're storing a lot of copies of all of this junk.

Is there a way to reduce the amount of space we're using to keep our WAL archive? Some suboptimal possibilities:

Prevent Postgres from recycling the WAL segments somehow, so it starts out with a zeroed file each time. The docs do not indicate that there is an option for doing this but I might have missed it.
Have Postgres zero the WAL segment file when it starts/finishes using it. Again, the docs do not seem to suggest this is possible.
Externally zero or remove some of the WAL segment files while they're not in use. Is there a safe way to determine which files this is?
Zero the unused portion of the segment before archiving it using the output from pg_xlogdump to find where the junk starts. Possible, although I don't fancy it. At least by doing this in the archive command you can be sure that Postgres isn't going to reuse the file.
Only archive the used portion of the segment file, again by interpreting the output of pg_xlogdump somehow, and then pad it with zeroes during restore. Also sounds possible although I don't really fancy it.

Starting in version 9.4, it now automatically zeroes the tail end of the WAL file.  (Actually it is just mostly zero, there are some block headers that don't get zeroed, but still the result is very compressible).

In version 9.2, there is a program named pg_clearxlogtail you can use.  You can add it into your archive_command before the compression step.

If you are using 9.3, you are out of luck.

Note that checkpoints do not inherently cause log file switches. It is probably archive_timeout which is causing the switches.


View all 32 possible Binary combinations for 5 digits I have 5 digits

A,B,C,D,E

Is it possible in SQL to view all the possible Binary combinations for them?
(Not the list of 120 possible permutations, just the 32 possible combinations in SQL http://www.mathsisfun.com/binary-combinations.html)

Thus

A   B   C   D   E
1   1   1   1   1
1   1   1   1   0
1   1   1   0   0
1   1   0   0   0
1   1   0   0   0 etc

Thank you


There is no export as SQL option As CMS I use Wordpress. I want to export a whole category (windows 8) so I run the SQL command :

SELECT * FROM wp_posts
INNER JOIN wp_term_relationships ON (wp_posts.ID = wp_term_relationships.object_id)
INNER JOIN wp_term_taxonomy ON (wp_term_relationships.term_taxonomy_id = wp_term_taxonomy.term_taxonomy_id)
WHERE (wp_term_taxonomy.term_id = 15
   AND wp_term_taxonomy.taxonomy = 'category'
   AND wp_posts.post_type = 'post'
   AND wp_posts.post_status = 'publish');

I get the tables but then I cannot export as SQL (there is no option) - strange. Is there a way to export only selected tables as SQL?

I get this effect only when applying a query.

A table, view, or other direct structure seems to export into SQL fine.

The SQL option seems is not available to a query result set, very sadly.


Size of Binary log file in Mysql Replication I have a master - server replication setup in Linux servers.

Master Configuration details :

 MySQL version is 5.1 and replication mode is MIXED type.

Slave Configuration details :

 MySQL version is 5.6

If i insert single record , then it is getting replicated to slave.

When i am running a script to insert thousand of records at master , it is not getting replicated.

When i see the size of binary log file , it is of 908 MB
and when i see the size of relay log file , it is 560 KB.

Does , size of a binary log file matters ?
or
what is the probable cause of this.

Below is master cnf details :

  [mysqld]

  datadir=/var/lib/mysql
  socket=/var/lib/mysql/mysql.sock
  user=mysql
  # Disabling symbolic-links is recommended to prevent assorted security risks
    symbolic-links=0
    bind-address = #master IP# ( not showing here )
    port=3306
    wait_timeout=90
    net_read_timeout=90
    connect_timeout=90
    max_connections=100
    lower_case_table_names=1
    log-bin=mysql-bin
    binlog_format=MIXED
    server-id=1

[mysqld_safe]
     log-error=/var/log/mysqld.log
     pid-file=/var/run/mysqld/mysqld.pid

I have resolved the issue myself only using following process :

Change the replication mode to ROW.
Restrict the size of max_binlog_size=30M

In this way binary log file cant be of huge size and easy to replicate.


Fastest way to do a mysqldump restore on Amazon RDS? We need to restore a database from a 7-Gb mysqldump file to a RDS Amazon instance.
The full restore takes between 3 and 4 hours (probably because of network latency since the dump has to reside on a EC2), and it's too long for us. For reference, a restore to a local mysqld would take less than 30 minutes.

Is there a Amazon-related solution that would allow us to reduce the import time?

Questions about optimizing a mysqldump restore time have been asked before, such as:

Speeding up mysqldump / reload
MySQL any way to import a huge (32 GB) sql dump faster?
Reasonable times for a mysqldump restore?
MySQL settings useful to speed up a mysqldump import

However, this question is different because relates to a RDS instance, so:

Additional constraints are applied, for instance some MySQL variables (e.g. innodb_doublewrite) cannot be changed to improve performances
There may be some Amazon-native solution or product that would solve this issue easily and nicely

You must now think in terms of the RDS Instance

Provisoning more IOPs
Is it safe to use innodb_flush_log_at_trx_commit = 2

Set innodb_flush_log_at_trx_commit = 2
Set sync_binlog=0

Since max_allowed_packet = 1M by default in Amazon RDS, this could cause mysqldump's multirow inserts (extended inserts) could fail. You may want to mysqldump the database with --skip-extended-insert. This will guarantee the data being inserted one-by-one, but the dump will be much larger and take longer to import.

Please read What I learned while migrating a customer MySQL installation to Amazon RDS from Percona's Blog for even deeper insights.


Grouping by more than 1 column using Partion By or any other method - Sql Server I have the following tables in my SQL Server database:

Product
-Id
-Name
-Price
-TypeId (Foreign Key)
-SubTypeId (Foreign Key)

Type
-Id
-Name

SubType
-Id
-Name
-TypeId (Foreign Key)

I currently have a screen which displays products grouped by Type and SubType like this:

+------------+----------+-----------+
|    Type    | Sub Type |  Product  |
+------------+----------+-----------+
| Electrical | TV       | TV1       |
|            | TV       | TV2       |
|            | TV       | TV3       |
|            | TV       | TV4       |
|            | Hoovers  | Hoover1   |
|            | Hoovers  | Hoover2   |
| Furniture  | Bedroom  | Wardrobe1 |
|            | Bedroom  | Wardrobe2 |
|            | Dining   | Table1    |
|            | Dining   | Table2    |
+------------+----------+-----------+

Using windowing function i have managed to display the Type only once, my query:

    SELECT CASE WHEN RowNum = 1 THEN TypeName ELSE NULL END AS TypeName, SubTypeName, ProductName
    FROM (
    SELECT Type.Name AS TypeName, SubType.Name AS SubTypeName, Product.Name AS ProductName, ROW_NUMBER() OVER(PARTITION BY Type.Name ORDER BY Product.Name) AS Rownum
    FROM Product
        INNER JOIN Type ON Type.Id = Product.TypeId
        INNER JOIN SubType ON SubType.Id = Product.SubTypeId
    GROUP BY Type.Name, SubType.Name Product.Name
    ) X

What i want to do is have something similar for the sub type column so sub types are only displayed once like this:

+------------+----------+-----------+
|    Type    | Sub Type |  Product  |
+------------+----------+-----------+
| Electrical | TV       | TV1       |
|            |          | TV2       |
|            |          | TV3       |
|            |          | TV4       |
|            | Hoovers  | Hoover1   |
|            |          | Hoover2   |
| Furniture  | Bedroom  | Wardrobe1 |
|            |          | Wardrobe2 |
|            | Dining   | Table1    |
|            |          | Table2    |
+------------+----------+-----------+

How can i adjust or change my query to make this work?

Just create 2 separate row numbers for change of Type and SubType. I used Id field since it looks like you don't want it to be sorted by Name:

Select
  case row_number() over (partition by T.Id order by T.Id, S.Id, P.Id)
    when 1 then T.Name else '' end as Type,
  case row_number() over (partition by T.Id, S.Id order by T.Id, S.Id, P.Id)
    when 1 then S.Name else '' end as SubType,
  P.Name
FROM
  Product P
  INNER JOIN Type T ON T.Id = P.TypeId
  INNER JOIN SubType S ON S.Id = P.SubTypeId
 order by T.Id, S.Id, P.Id

Example in SQL Fiddle


MySql timeouts waiting for table lock, no apparent deadlocks I started having the following error on two different applications that write to the same database and table:

  Lock wait timeout exceeded; try restarting transaction

At the beginning I thought it was caused by some slow queries creating deadlocks, but I haven't been able to find any evidence of that.

This is the output of the transactions section of show engine innodb status;

------------
TRANSACTIONS
------------
Trx id counter 1031368
Purge done for trx's n:o < 1031258 undo n:o < 0 state: running but idle
History list length 907
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 1031348, not started
MySQL thread id 120, OS thread handle 0x7f5e4c148700, query id 1068 localhost db2_prod cleaning up
---TRANSACTION 0, not started
MySQL thread id 53, OS thread handle 0x7f5e3a209700, query id 1136 localhost root init
show engine innodb status
---TRANSACTION 1031286, not started
MySQL thread id 63, OS thread handle 0x7f5e4c0b5700, query id 509 localhost db1_prod cleaning up
---TRANSACTION 1031367, ACTIVE 16 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 2 lock struct(s), heap size 360, 1 row lock(s), undo log entries 24
MySQL thread id 121, OS thread handle 0x7f5e3a26b700, query id 1127 localhost db1_prod update
insert into `my_table` (`col_1`, `col_2`, `col_3`, `col_4`, `col_5`, `col_6`, `col_7`) values (?, ?, ?, ?, ?, ?, ?), (?, ?, ?, ?, ?, ?, ?), (?, ?, ?, ?, ?, ?, ?), (?, ?, ?, ?, ?, ?, ?), (?, ?, ?, ?, ?, ?, ?), (?, ?, ?, ?, ?, ?, ?), (?, ?, ?, ?, ?, ?, ?), (?, ?, ?, ?, ?, ?, ?), (?, ?, ?, ?, ?, ?, ?), (?, ?, ?, ?, ?, ?, ?), (?, ?, ?, ?, ?, ?, ?), (?, ?, ?, ?, ?, ?, ?), (?, ?, ?, ?, ?, ?, ?), (?, ?, ?, ?, ?, ?, ?), (?, ?, ?, ?, ?, ?, ?), (?, ?, ?, ?, ?, ?, ?), (?, ?, ?, ?, ?, ?, ?), (?, ?, ?, ?, ?, ?, ?), (?, ?, ?, ?, ?, ?, ?), (?, ?,
------- TRX HAS BEEN WAITING 16 SEC FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 234 page no 1310 n bits 384 index `PRIMARY` of table `db1`.`my_table` trx id 1031367 lock mode S locks rec but not gap waiting
Record lock, heap no 230 PHYSICAL RECORD: n_fields 9; compact format; info bits 32
 0: len 4; hex 0000125b; asc    [;;
 1: len 4; hex 00002a4c; asc   *L;;
 2: len 4; hex 00000eeb; asc     ;;
 3: len 6; hex 0000000fbc8c; asc       ;;
 4: len 7; hex 390000014c1434; asc 9   L 4;;
 5: len 8; hex 0000000000001c40; asc        @;;
 6: len 5; hex 999968ce0c; asc   h  ;;
 7: len 4; hex 00000000; asc     ;;
 8: SQL NULL;

------------------
---TRANSACTION 1031308, ACTIVE 837 sec
2 lock struct(s), heap size 360, 1 row lock(s), undo log entries 1
MySQL thread id 60, OS thread handle 0x7f5e3a23a700, query id 1059 localhost 127.0.0.1 db1_prod cleaning up
Trx read view will not see trx with id >= 1031309, sees < 1031309

Any idea what could be causing this? Let me know if you need more info. Thanks in advance.

If a transaction has been running 837 seconds, I suggest that its is poorly designed.  I don't like a transaction to last even 5 seconds, much less the default innodb_lock_wait_timeout=50.

Can't wait for user - A transaction should not include any user interaction.  Waiting for a user might mean waiting for him/her to take a phone call.  If you need something locked for some kind of user 'session', BEGIN...COMMIT is not the way to do it.

autocommit=0 is bad - Another possible mistake is autocommit=0 then forgetting to COMMIT.  For that reason, I recommend never using that setting.  Instead, be explicit about including BEGIN and let it be a reminder that you need a COMMIT.

If you would like to discuss this further, let's see your whole 'transaction', either at a high level, or with specific SQL statements.


ZFS Tunings for PostgreSQL What are the recommended ZFS tunings for PostgreSQL 11 (with TimescaleDB 1.6 extension) running on striped mirrored vdevs of SATA3 SSDs?

I am considering:

ashift=12
atime=off
relatime=on (or off?)

But am unsure about the following:

xattr=sa
logbias=throughput
redundant_metadata=most

Data compression is pretty important, so I am thinking of keeping the default setting of recordsize=128K instead of changing it to 8K which will likely cause data compression to be much less effective.

Any advice greatly appreciated!

You should always use ashift=12 today unless you have an overwhelmingly good reason not to.

relatime=off might save you a vanishingly small amount. Don't expect it to be measurable.

xattr=sa is important if you use extended attributes (e.g. SELinux) and you have a lot of tables and file opening/closing operations.

logbias=throughput should reduce your disk I/O volume, and it will help with SSD storage where there is no huge disconnect between bandwidth and latency like there is on spinning media.

recordsize=8k will ensure you don't have any RMW overheads. You'll have to decide whether the extra compression ratio achievable with larger recordsize is a net victory in your use case.

You may find the slides from a lecture I gave on the subject useful.


MariaDB: point in time backup recovery with binary log using position Following the below sequence of commands i expected the recovery of backup and subsequent point in time recovery using binary log. However, the database was restored to backup only:

1)

CREATE TABLE `CUSTOMERS` (
  `ID` int(11) NOT NULL,
  `NAME` varchar(20) NOT NULL,
  `AGE` int(11) NOT NULL,
  `ADDRESS` char(25) DEFAULT NULL,
  `SALARY` decimal(18,2) DEFAULT NULL,
  PRIMARY KEY (`ID`))

2) INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY) VALUES (70, 'Hardik70', 27, 'Bhopal', 8500.00 );

3) mysqldump db -uroot -ppass --master-data=2 > out.sql

4) INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY) VALUES (80, 'Hardik80', 27, 'Bhopal', 8500.00 );

5) mysql -uroot -ppass < out.sql

6)

mysqlbinlog --start-position=368315 /var/log/mysql/bin.123456 \
             | mysql -u root -p

/var/log/mysql/bin.123456 and 368315 was taken from out.sql because the dump was done using --master-data=2 option

i expected    select * from CUSTOMERS; to return

| 70 | Hardik70 |  27 | Bhopal    | 8500.00 |
| 80 | Hardik80 |  27 | Bhopal    | 8500.00 |

but only | 70 | Hardik70 |  27 | Bhopal    | 8500.00 | is returned

How to do point-in-time backup recovery correctly?

The point in time recovery actually works, but you forget, that your restoration of the dump also gets written to the binary log and this undoes your point in time recovery.

What you should actually do, is to also note the binary log position before you restore your dump. Then you limit the point in time recovery with --end-position. Your workflow therefore is

create table
insert into table
mysqldump --master-data=2
insert into table
show master status; /* note log file and position */
mysql < out.sql
mysqlbinlog --start-position=368315 --end-position=<note from above> binlog | mysql

Alternatively you can also not write the mysqldump to the binary log. The 6th step would then be

mysql > set session sql_log_bin=0;
mysql > source out.sql

You have to re-setup slaves then of course.


Is commit necessary after DML operation in Function/Procedure? I wonder to know if it is necessary to write commit after insert/delete/update in function/procedure?

Example:

create or replace function test_fun
return number is
begin
   delete from a;
   return 0;
end;

or procedure

create or replace procedure aud_clear_pro
as
begin
   delete from a;
end;

does it need commit after delete?

Cannot understand the following situation:

If I call the function/procedure from SQL window then it requires commit

but
If I schedule  function/procedure using dbms_scheduler and run the job, delete statement is automatically committed.

WHY?

In general, procedures should not commit.  Those sorts of transaction control decisions should be left to higher-level code that knows when a logical transaction is actually complete.  If you commit inside of a stored procedure, you are limiting its reusability because a caller that wants the changes the procedure makes to be part of a larger transaction cannot simply call the procedure directly.

If you call a procedure interactively, you will have to explicitly commit or rollback the transaction because Oracle has no idea if you intend the procedure call to be a logical transaction or if you intend to compose a larger transaction involving multiple procedure calls.  If you use dbms_scheduler, dbms_scheduler assumes that a job is a logical transaction and commits at the end of the job assuming it was successful (dbms_job does the same thing).

Functions should not manipulate data in the first place.  A function that manipulates data cannot be called from a SQL statement (barring the corner case where the function itself is declared to use an autonomous transaction which is almost never appropriate).  The whole point of having both functions and procedures is that functions can be embedded in SQL statements and can be more freely granted to users because they do not change any data.


Use trigger to populate another column I have two counters in a mysql table. I would like to have another column that contains the difference between counter column 1 and counter column 2. Could I use a trigger after counter column 2 is updated which will subtract the number in counter 2 from the number in counter 1 and put the results in count column 3? I would want to index count column 3 for queries.

You can use a generated column in your CREATE TABLE statement to always have an up-to-date value:

CREATE TABLE TestTable(
    id INT AUTO_INCREMENT PRIMARY KEY,
    col1 INT NOT NULL,
    col2 INT NOT NULL,
    col3 INT AS (col1 - col2)
);

The column can be virtual or stored (virtual is default) and each has their own use cases as detailed in the link above.

Note: only applicable for MySQL 5.7.6 & higher.


SSL connection to MySQL suddenly not working anymore SSL connections where running smoothly til last week, now I can't connect anymore.
I have a MySQL installation on a virtual machine running Ubuntu. The machine is hosted on a Windows 2012 R2 Server. MySQL is configured for SSL, we have generated keys and everything I see on the side of MySQL looks fine:

+---------------+----------------------------+
| Variable_name | Value                      |
+---------------+----------------------------+
| have_openssl  | YES                        |
| have_ssl      | YES                        |
| ssl_ca        | /etc/mysql/ca-cert.pem     |
| ssl_capath    |                            |
| ssl_cert      | /etc/mysql/server-cert.pem |
| ssl_cipher    |                            |
| ssl_key       | /etc/mysql/server-key.pem  |
+---------------+----------------------------+

On the client side I use FileMaker Pro and a PHP plugin to make a connection. Checking the server from the client with telnet 192.168.87.30 3306 I get the usual answer

5.5.54-0ubuntu0.14.04.1¯`%2nS4IQ└;tZC}o5YBzb\mysql_native_password

As long as I deactivate SSL I can still connect to and work with the database. With SSL active all I get is an error

  Connection to database failed: 2026 (SSL connection error)

The exact same configuration on the client works perfectly well with another MySQL installation on a different server, so the client is certainly not the problem. Something must have changed either on the Ubuntu machine or on the Windows server, but I'm totally lost what this could be.

Checking the expiration date of the certificates helped a lot :)
Case closed.


Auditing - sp_send_dbmail from server-level triggers failed I have a server-level trigger to send a mail when a database is created. I have a  few users in the db_creator server role. The trigger fails for these users because they don't have permissions to execute sp_send_dbmail. Is there a better option here than adding the logins to the DatabaseMailUserRole in MSDB or adding "WITH EXECUTE AS 'sa'"  (or similar with a lower-privileged account) to the trigger?

CREATE TRIGGER [ddl_trg_Create_Database]
ON ALL SERVER
FOR CREATE_DATABASE
AS
declare @results varchar(max)
declare @subjectText varchar(max)
declare @databaseName VARCHAR(255)
SET @subjectText = 'New database ceated - ' + @@SERVERNAME + ' by ' + SUSER_SNAME()
SET @results =
  (SELECT EVENTDATA().value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]','nvarchar(max)'))
SET @databaseName = (SELECT EVENTDATA().value('(/EVENT_INSTANCE/DatabaseName)[1]', 'VARCHAR(255)'))

EXEC msdb.dbo.sp_send_dbmail
 @recipients = 'DBA_GROUP@SomeCompanyName.com',
 @body = @results,
 @subject = @subjectText,
 @exclude_query_output = 1
GO

ENABLE TRIGGER [ddl_trg_Create_Database] ON ALL SERVER
GO

Well, the ideal solution would be to implement module signing along the lines of:

Create a Certificate in master
Sign the DDL Trigger with the Certificate
Create that same Certificate in msdb
Create a User in msdb from that Certificate
Add that Certificate-based User to the DatabaseMailUserRole Database Role

Unfortunately, however, it seems that Database-scoped Triggers (DDL) and Server-scoped Triggers (DDL and Logon) cannot be signed (so says Microsoft in this MSDN Forum thread: Error Signing a DDL Trigger).

But, I did find that simply moving the EXEC msdb.dbo.sp_send_dbmail logic into a stored procedure in master allows this to work. Apparently, both master and msdb are both, by default, enabled for Cross-Database Ownership Chaining, and a stored procedure is Schema-scoped, unlike a DDL Trigger. So, as long as the stored procedure in master is in the dbo Schema, it should work. You will just need to GRANT EXECUTE on this stored procedure to those same folks who you made members of the dbcreator fixed server role.

I have created a stored procedure and modified the DDL trigger to call the stored procedure as follows. Please note that I changed the variable datatypes to NVARCHAR and sysname as all of the values are truly NVARCHAR.

Stored Procedure

USE [master];
GO
CREATE PROCEDURE dbo.SendDdlEmail
(
  @EventData XML
)
AS
SET NOCOUNT ON;
DECLARE @Results NVARCHAR(MAX),
        @SubjectText NVARCHAR(MAX),
        @DatabaseName sysname;

SET @SubjectText = N'New database created - ' + @@SERVERNAME + N' by ' + ORIGINAL_LOGIN();

SET @Results =
  @EventData.value(N'(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]', N'NVARCHAR(MAX)');

SET @DatabaseName = @EventData.value(N'(/EVENT_INSTANCE/DatabaseName)[1]', N'sysname');

EXEC msdb.dbo.sp_send_dbmail
  @recipients = 'DBA_GROUP@SomeCompanyName.com',
  @body = @Results,
  @subject = @SubjectText,
  @exclude_query_output = 1;
GO

GRANT EXECUTE ON [dbo].[SendDdlEmail] TO [xxxxxxxxxxxxx];
GO

Trigger

CREATE TRIGGER [ddl_trg_Create_Database]
ON ALL SERVER
FOR CREATE_DATABASE
AS
SET NOCOUNT ON;

DECLARE @EventData XML;
SET @EventData = EVENTDATA();

EXEC [master].[dbo].[SendDdlEmail] @EventData;
GO

FYI: I just added a Microsoft Connect Suggestion requesting the ability to sign non-Schema-scoped Triggers:

Allow signing Database DDL Triggers and Server DDL and Logon Triggers - ADD SIGNATURE


How to validate and upgrade my knowledge of SQL Server with MCSA My story is that I am working now almost 10 years as an administrator, I deal with SQL Server and other IT stuff, most of my knowledge is self taught by reading books, dealings with problems and asking and reading sites like this. Now I want to circularize and certificate that by going to, learning and try to pass Microsoft official exams and programs. Where I should start and what kind of curses and exams I need to look for. I am pretty confused whit so many choices the way I can go.

At one local Microsoft Certified Partner they offered me 4 courses for label of Microsoft Certified IT Professional Server Administrator (equivalent MCSA) Also they are offering "MSCSE" as Enterprise Administrator.

Do I need both of this one so I can continue learning for SQL Server Administrator?
Do I need this ones at all ?
What is the correct name in MS terminology for SQL Server Administrator?
Does there exist a SQL Server developer calling in MS world or is this only extension for already developers of common MS languages such as C#, C++ ?

Do I need this one at all ?

No. The MCSA (and MCSE) are aimed more at administering networks, not SQL Server. These particular 2 are aimed at administering Windows 2003 networks. I think you would be wasting your money taking them as I think these 2 certs will be retiring soon. The Windows 2008 versions use the newer MCTS/MCITP naming convention.

In general, the current naming convention progression is MCP (MC = "Microsoft Certified", P = "professional", one exam, of anything), MCTS (TS = "technical specialist", one introductory technical exam), MCITP (ITP = "IT Professional", the relevant MCTS plus one or more "pro" exams). There are MCM (M = "master") certifications, but those will cost between $10k and $20k, so only if your company will pay for them would they even remotely be practical.

  What is correct name in MS terminology for SQL Server Administrator?

SQL Server certs. Start with the relevant exam for the MCTS, that would most likely be 70-432. Then when you know more, take the 70-450 exam. That will give you the MCITP: Database Administrator SQL Server 2008 certification. You cannot get the equivalent SQL Server 2005 certs because they were retired in June.


Updating to CU8 on SQL Server 2012 SP3 with CU2 Our management has decided to install SQL Server 2012 SP3 CU8.
Current running version has SQL Server 2012 SP3 CU2 installed.

This is like jumping 6 CU's ahead.
Currently there are no AG setup.

Question:
Does the 6 CU jump likely to cause any kind of issues?

Could you please guide me to a checklist to install CU's?

Sql Server updates are incremental. As long as the service pack level isn't changing, there is no need to install any intermediate updates.

In your case, SP2 CU2 can be patched directly to SP2 CU8. There is no need - or use - to install CU3 - CU7 before the CU8. Release notes are not cumulative. This means that to see all the changes between CU2 and CU8, you need to read the release notes for CUs 3-8.

Service packs are similar, RTM can be upgraded to the desired SP without installing other SP version(s) before.

Edit: As per the comment, a replication setup needs special steps for update installation. This is documented in Microsoft's support documentation and depends on topology.


How Large Table before Using Clustered Columnstore Index At what size should we consider applying Columnstore Index on a table? Is there a SQL Server storage or design guidance?

Our Data Warehouse is 5TB total. Additionally, we have tables which are 100GB to 1TB which compose the database. Just curious if SQL Server has guidance on how large should table before a columnstore index is applied?

For Fact Tables: Eg, should we have columnstore index on table that is 300MB, 1GB, 1TB. Or should we have on all tables, and size does not matter?

For Dimension Tables: Additionally, should we have columnstore indexes on lookup tables? These are small, generally 2-50 MB

This article never stated

https://docs.microsoft.com/en-us/sql/relational-databases/indexes/columnstore-indexes-design-guidance?view=sql-server-2017

The article you link to gives this guidance:

  Consider using a clustered columnstore index when:

  Each partition has at least a million rows. Columnstore indexes have
  rowgroups within each partition. If the table is too small to fill a
  rowgroup within each partition, you won't get the benefits of
  columnstore compression and query performance.

The "minimum size" for columnstore isn't really any size on disk, but number of rows. Each table (or partition if the table is partitioned) should be at least 1 million rows before considering it for columnstore.

A very narrow multi-million row table might be only a few megabytes and still be an excellent candidate for columnstore. A wider table with fewer rows may take up more space on disk, and not be a good columnstore candidate.

I'd recommend revisiting the sections in that article titled "Consider using a clustered columnstore index when..." & "Don't use a clustered columnstore index when..."


How to decrese MySQL Innodb crash recovery time (force MySQL to do it faster) I'm running MySQL 5.7 with 1GB of innodb_log_file_size on CentOS 7 x64 and I can't change the value of that parameter right now.
In the case of crashes, it takes about 20 minutes to recover, but within this 20 minutes mysql process doesn't seem to use system resources efficiently (i.e. it has a very mild CPU usage).
I need to know is there any way to force MySQL to do this process a little bit faster using configuration parameters? (for example by increasing number of recovery threads or allocating more RAM space)

First of all there is a documented bug that has seen the recovery performance of 5.7 degrade, this is discussed here https://bugs.mysql.com/bug.php?id=80788 which seems to have been fixed in 5.7.19

Otherwise, these suggestions might help:

https://www.percona.com/blog/2016/06/07/severe-performance-regression-mysql-5-7-crash-recovery/

https://www.percona.com/blog/2014/12/24/innodb-crash-recovery-speed-mysql-5-6/

Although these are on the Percona blog it is not specific to Percona. There are other performance suggestions on the blog, but as you already realise these largely relate to the setting of innodb_log_file_size

Disclosure: I work for Percona.


Based on what criteria voting take place in MongoDB? Suppose a replication set R1 contains one primary, 2 secondary and one arbiter node. Voting will happen if the primery node goes down. Based on what criteria secondary members will vote to select the next primary node? Is the voting random process ? Whether the members will check the health / efficiency of the other members before voting?

Consider all the nodes have same priority and no salve delay

Replica Set Elections

The MongoDB manual describes Replica Set Elections and the associated election mechanics such as Vetoes in detail.

Broadly speaking, the criteria for electing a node as primary includes:

Is that node currently in PRIMARY or SECONDARY state?
Is that node eligible to become primary (priority > 0)?
Do a majority of the nodes of the replica set see that memory as available (by way of replica set heartbeat)?
Does that node have the least replication lag in relation to the current primary?
Is that node the highest priority eligible node?
Would the node veto itself if elected primary? For example, if you step down a primary it will veto itself for election for 60 seconds by default.

The first node to receive a majority of votes subject to the election mechanics will become primary.

There are some further subtleties to the process depending what triggered the election (i.e. a unplanned failover versus stepping down the current primary).

  Suppose a replication set R1 contains one primary, 2 secondary and one arbiter node.

In order to ensure consensus can be reached by a strict majority of voting nodes, the general recommendation is to have an odd number of voting nodes.  With your example configuration an arbiter node is not required, and you should instead only have the primary and two secondaries.  An arbiter node does not have any special role in the election process aside from being available to cast a vote.

With all things being equal (no replication lag, default priorities, no network issues, etc) your two secondaries in this scenario are equally likely to be elected primary in the event the current primary unexpectedly fails.


How to query a server linked via ODBC I've looked at multiple other questions about querying linked servers, but nothing has worked for my setup.

Notes:

I'm not using localdb at all.  I just wanted to use SQLSMS for a remote cache database that is only accessible via the Intersystems ODBC driver.
I edited out the actual table names, but they all have periods in them.
MyDSN is not the actual DSN name.

Here is the structure and below the image are some of the things I've tried:

SELECT * FROM [MyDSN].[default].[Table.name]

  Invalid object name 'MyDSN.default.Table.name'.

SELECT * FROM [localdb].[MyDSN].[default].[Table.name]

  Could not find server 'localdb' in sys.servers.

SELECT * FROM [MyDSN].[default].dbo.[Table.name]

  The OLE DB provider "MSDASQL" for linked server "MyDSN" does not contain the table ""default"."dbo"."Table.name"".

SELECT * FROM [MyDSN]..[default].[Table.Name]

  The OLE DB provider "MSDASQL" for linked server "MyDSN" does not contain the table ""default"."Table.Name"".

SELECT * FROM [MyDSN].[default].[Table].[Name]

  Invalid object name 'MyDSN.default.Table.name'.

Also, if I right-click on one of the tables and pick "Script table as SELECT" I get the following error:

Try Open query

BEGIN TRANSACTION;

select top 10  *
INTO #t
from openquery ("YOUR LINKED SERVER NAME",'SELECT   * FROM [SOURCE TABLE NAME]);

COMMIT


Speeding up Count(*) on large tables We're using a Vendor App running on SQL Server Enterprise, and it has a rather annoying quirk of executing COUNT statements on the Items table while processing most financial documents (orders, invoices, etc.).

E.g. SELECT COUNT('A')  FROM [dbo].[Items] T0

I'm sure that would normally be fine, but there's over 6 million records, and it takes ~400ms to count them all. This can constitute a substantial portion of the overall processing time.

The table already has an extremely narrow NonClustered Index (tinyint, plus Clustered Key) on it which is what SQL is using when it does the Table Scan, so I don't think we can do any better in that regard.

There's a few solutions I'm aware of, which we'd like to avoid if possible:

Indexed View using COUNT_BIG(*)
Use faster Processors - we're using Cloud Servers, and there's very limited options in this regard.
Deleting Records - not really an option in this case.

Do we have any other options to speed this up?

Here's a Gist showing the setup: https://gist.github.com/elvishfiend/5094f120b14f8ecfb325623edcb5f3eb

The indexed view should be among the fastest options, with the lowest maintenance overhead, when implemented optimally.

Modifications are incremental (deltas) as I explain in detail in Indexed View Maintenance in Execution Plans (a full recount is not performed on every base table update); however, you do need to ensure that the delta update parts of the execution plan have efficient access methods (like any query).

It is typically quite simple to identify a missing index from the INSERT/UPDATE/DELETE execution plan. Perhaps you could add an illustrative post-execution (actual) execution plan to your question.

Automatic matching of query text to an indexed view is only available in Enterprise Edition (and equivalents). In other editions, you must use the WITH (NOEXPAND) table hint. There are also good reasons to use NOEXPAND even on Enterprise Edition.

Regarding the demo code: Make sure you specify the hint using WITH (NOEXPAND). The way you have written it, NOEXPAND is parsed as an alias. Note also that only materialized (indexed) views can have a NOEXPAND hint.

If you are unable to add a hint directly, this would be an excellent use of a Plan Guide. A plan guide can also be used to ensure that a query that matches an indexed view (without naming it explicitly) actually uses the indexed view.

Remember that without NOEXPAND on a materialized (indexed) view, SQL Server always expands the view definition at the start of plan compilation. Enterprise Edition may (or may not) match (parts of) a query to an indexed view depending on its assessment of the costs of each option.

Related Q & A:

How can I make sure the SQL Server query optimizer uses the exact tables in the query
Why does the READPAST hint cause indexed views to be ignored?
What factors go into an Indexed View's Clustered Index being selected?


Unexpected Unique Constraint Violation Given the following CREATE TABLE statement:

CREATE TABLE [dbo].[Unit]
(
    [id]                SMALLINT       NOT NULL IDENTITY(1,1),
    [name]              VARCHAR(100)   NOT NULL,

    CONSTRAINT [PK_Unit] PRIMARY KEY CLUSTERED ([id]),
    CONSTRAINT [UQ_Unit_Name] UNIQUE ([name])
)

How to I allow the following two strings to co-exist without violating the UNIQUE constraint?

INSERT INTO dbo.Unit (Name) VALUES('e³m³/day')
INSERT INTO dbo.Unit (Name) VALUES('e3m3/day')

Any insight appreciated.

EDIT

Current Collation is Latin1_General_CI_AS

EDIT

Unless someone has a better option, it appears that I may have to use Latin1_General_BIN and manually handle CI in the few places where I need it... will convert this to an answer if no better solutions are offered.

Why not just collate that column as case-sensitive? I don't think you need to resort to binary. This seems to work fine, just note that it may affect sorting, comparisons, unions etc:

CREATE TABLE [dbo].[Unit]
(
    [id]                SMALLINT       NOT NULL IDENTITY(1,1),
    [name]              VARCHAR(100)   COLLATE Latin1_General_CS_AS NOT NULL ,

    CONSTRAINT [PK_Unit] PRIMARY KEY CLUSTERED ([id]),
    CONSTRAINT [UQ_Unit_Name] UNIQUE ([name])
);
GO

INSERT INTO dbo.Unit (Name) VALUES('e³m³/day')
INSERT INTO dbo.Unit (Name) VALUES('e3m3/day')

Results:

1 row(s) affected.
1 row(s) affected.

Now, this works for the example in your question, but whether it works for your entire set of requirements, is hard to say. You may have characters that are different to you but aren't treated differently in terms of case-sensitivity, such as:

INSERT INTO dbo.Unit (Name) VALUES('ß')
INSERT INTO dbo.Unit (Name) VALUES('ss')

Results:

1 row(s) affected.

And then:

  Msg 2627, Level 14, State 1, Line 2
  Violation of UNIQUE KEY constraint 'UQ_Unit_Name'. Cannot insert duplicate key in object 'dbo.Unit'. The duplicate key value is (ss).
  The statement has been terminated.

In that case you may need to use binary after all.


Mysql replication, allow inserts while database is disconnected I've tried getting information on this elsewhere but can't really find what I'm looking for. I'm making a system to handle sales, etc for a company that has different branches. They want to have the system both on the cloud as well as locally so in case internet goes down they can continue making sales with the local database instead of the database in the cloud. So all databases will have to be masters.

I'm making sure that all race conditions are taken care of in the database (updating stock table, etc) and in the case that one database is disconnected and must continue making sales I am using two columns as index in my sales table and journal_entries table (one column is a character representing the branch and the other column is just an incrementing number), so as far as I know this should allow the databases to sync when the disconnected database comes back online.

Is there anything I'm forgetting though? Any particular scenario where my database design would run into problems by allowing them to run without being able to replicate for a while?

For Master-Master, use Galera or InnoDB Cluster.  The pitfalls of M-M are many and subtle.


Implicit conversion does not affect performance I have read about implicit conversion on indexes affect performance so by that mean in the following query

select count(*)
from fpc
where SKey in (201701, 201702)

as SKey is of type int, if I change above query to

select count(*)
from fpc
where SKey in ('201701', '201702')

performance will degrade.

I tested this on a table (which has millions of rows). question is why I did not see any difference on execution plan and time.

I have non-clustered columnstore index on SKey.

There are about 20 million rows per SKey and I have about 100 different SKey

The issue with implicit conversion is that it can prevent the efficient use of an index. If a function has to be applied to a column to get the correct comparison value, then an index seek on that column cannot be used in the search.

However, SQL Server has to decide which value to convert when it has to compare values of different types. It does this based on its Data Type Precedence rules.

If you look at that page, you'll see that integer values have a higher precedence that varchar values. So, the varchar value is the one that gets converted.

In the example you present, the indexed column is an integer column, and the hard-coded values are varchar (or char, or nvarchar - all have a lower precedence than integer).

So, the hard-coded values are converted to integers. Since your column does not undergo a transformation, the index can still be used.

NOTE: this is not the only factor that goes into this. For example, conversions between different data types that are in the same "family" (for example, int and bigint) will still allow an index seek, regardless of which datatype is the indexed column.

This is why one should match datatypes whenever possible, or use explicit conversions on non-indexed data where needed; by controlling the conversion process, you can usually avoid the problem.

On a personal note - this precedence order has long been an annoyance. If you have a character value that you need to compare to a numeric value, unless you explicitly perform the conversion, SQL Server will try to convert the character value (which will not always have a valid numeric representation) to a numeric, instead of converting the numeric value (which should always have a valid character representation). Sigh.

Bob Klimes pointed out a couple of related articles:

Implicit Conversions that cause Index Scans, by Jonathan Kehayias, which conducts a test to see which data types result in an index scan when converted implicitly; and
Data Type Mismatches Don’t Always Cause a “Bad” Implicit Conversion and Index Scan, by Kendra Little, which covers the fact that implicit conversions between different types in the same "family" still allow an index seek instead of an index scan.


Why is it a bad practice to allow everyone to use the sa login? Even Microsoft discourages the use of SQL Server authentication mode, but our applications require it.

I've read that it's a best practice to not let users use the sa login directly, instead using Windows Authentication and allowing those accounts (or account groups) sysadmin privileges.

Isn't that essentially the same thing? What are the advantages/disadvantages?
How does the best practice increase the security of my SQL Server instances?
Does this only apply to production instances, or to our internal development instances, too?

You have several different questions in here, so I'll knock 'em out individually:

"I've read that it's a best practice to not let users use the sa login directly, instead using Windows Authentication"

You're mixing two things here: the concept of SA, and the concept of SQL authentication and Windows authentication.

SQL authentication is a list of usernames and passwords that are stored in each and every SQL Server.  The fact that it's stored in SQL is the first problem.  If you need to change a login's password, you have to change it on every server (or maintain different passwords on different servers).  With Windows authentication, you can centrally disable logins, change passwords, set policies, etc.

If you choose to use SQL authentication, then SA is just one SQL authentication login.  It's the default administrator username, just like Administrator is in Windows authentication.  It has local superpowers on that one instance, but not global superpowers across all instances.

"...and allowing those accounts (or account groups) sysadmin privileges."

Whatever authentication method you choose, ideally you want to follow the principle of least privilege: granting people the bare minimum rights they need to get their job done, and no more.

Don't think of them as just logins - they're people who can get you fired.  If they drop the database or disable your backup jobs accidentally, they're not going to get fired, because by default, SQL doesn't track who did what.  You're the one who will get fired because it's going to happen, and you're not going to be able to say which person did it.

"How does the best practice increase the security of my SQL Server instances?"

You want to do two things:

Stop people from breaking the server
When they do break the server, be able to identify exactly who did it

The first one is accomplished with the principle of least privilege: giving folks only the permissions they need, and nothing more.

The second one is accomplished by giving each person their own login, not allowing shared logins (like letting everyone use the same username/password), and ideally, auditing the logins.  You probably won't do that last part right away, because it's kind of painful, but let's put the pieces in place first so you can add auditing later after somebody drops a database and your boss wants to know why.

I know what you're thinking: "But we're coding apps, and the app needs a login."  Yes, give the application its own login, and the developers need to know that password, but that login should be so stripped of permissions that nobody in their right mind would want to use it.  For example, it might need to be in the db_datareader and db_datawriter roles alone, nothing else.  That way it can insert, update, delete, and select data, but not necessarily change schemas, add indexes, change stored procedures, etc.

"Does this only apply to production instances, or to our internal development instances, too?"

I think it applies just as much to development instances because I'm usually worried about people breaking things.  People just love to break servers in development.  And of course, when it's time to bundle up the list of changes to migrate to production, I need to know whether a particular index really is vital to the app or whether some bonehead just ran the Database Tuning Advisor and told it to apply all changes.  Proper permissions helps lessen that pain.


Using a Replication Subscription Database for Publication Hopefully, there's a very "simple" answer to this question:

Can a SQL Server 2012 subscription database in a transactional replication setup also be used for publication?

Yes you can but not easy to maintain.  If your ever get out of sync and you have to reinitialize, resnap of the first set of publication and subscription; you will need to remove the publication property of the subscription. Otherwise you cannot do a full snapshot.


Why do we need to compare keys from different table? can you please help me make sense of this code?

The question: is to find the pair of students who have registered for the same course in the same semester.

why do I need to check the s1.sid = r1.sid ?
Why can't I just the SID in registration table? I know this gives me duplicates, but I don't understand the reason behind.

My understanding behind querying tables to get results is relatively weak, please pardon me if I am asking a fundamental questions :) thanks for helping!

    select s1.sname, s2.sname, cname, r1.semester
    from student s1, student s2, registration r1, registration r2, course c
    where s1.sid = r1.sid and s2.sid = r2.sid
    and r1.cid = r2.cid and r1.semester = r2.semester
    and r1.cid = c.cid and s1.sid < s2.sid;

What is the student and registration? Are these tables? I used to plural 's': student_s_, table_s_, to mean a set of objects, which the tables actually are. As of not using s.id = r.sid, it gives you two records (objects), where one refers the other. I guess that r.sid is a foreign key, referencing a student. You then check if two registrations are for the same course at the same semester, then you select that course. By s1.sid < s2.sid, you exclude the same student (which obviously always takes courses with himself) and also exclude duplicates. The duplicates appear because select s1 from students and select s2 from studentds gives you all pairs of students, which means that both ('Joe Bull', 'Sarah The Terrible') and ('Sarah The Terrible', 'Joe Bull') show up. Why duplication in this case?

As of why to check s1.sid = r1.sid look at the CROSS JOIN (aka Cartesian product) filtered with WHERE clause and INNER JOIN. It turns out to be the same thing.

I also believed that there is a computational difference between taking Cartesian product and then filtering it with where but it turns out that modern SQL servers realize that it is just and a join operation and accomplish it in linear N+M time rather than N*M time.


Missing execution plans for stored procedures What are the reasons for a plan to be missing from cache for stored procedures?

WITH RECOMPILE
Dynamic SQL
Encrypted code
Significant data changes
Update statistics
What else?

I've worked on 2 servers (SQL Server 2008 R2 and SQL Server 2012) recently that didn't have plans in cache for very resource-intensive stored procedures. Many, maybe all, of the statements inside the stored procedures also didn't have plans in cache. Some of the stored procedures execute pretty frequently like a few times per second.

No memory pressure whatsoever. One of the servers has far more hardware than is needed.

I thought the missing plans were due to temporary table creations in the middle of the stored procedures, but that appears to be old information from SQL Server 2000 or earlier. Starting with SQL Server 2005, the recompiles happen at the statement level for the statements after the DDL. Is that true in all cases or can it still happen on newer versions?

What else could be the culprit for the missing plans? I've skimmed a few articles on this topic, but nothing seems to fit.

Optimize for adhoc workloads is enabled on the server I am looking at this week. One of the stored procedures is only executed once a day. I do have the code for that one. I don't have the code for the one that's executing over 100 times per minute, but I can get it. I won't be able to post the code, but I can describe it in relation to my question.

I don't believe anyone is freeing the procedure cache or dropping clean buffers. This client is using Solarwinds DPA as one of their monitoring tools. DPA did capture one of the execution plans for the statement in the stored proc that gets called once a day. That statement has a massive amount of reads due to non-sargable WHERE clause. If DPA captured the statement, then it's an estimated plan and was in the plan cache at one time. Just isn't there when we are troubleshooting. I'll have them start logging sp_WhoIsActive to a table.

I'm using sp_BlitzCache. (I work for Brent Ozar Unlimited) This will show the plan for the entire stored procedure as well as the plans for the individual statements, if they exist. If they don't exist, it has this for a warning "We couldn't find a plan for this query. Possible reasons for this include dynamic SQL, RECOMPILE hints, and encrypted code." And that warning is on the statements too.

TF 2371 isn't in place. I'm looking at the wait stats. The server is pretty bored. PLE is over 130,000.

I now have the code for 2 more stored procedures. One of them is using dynamic SQL with exec (@sql) so that one we know why there isn't a plan for it. But the other one, and this is the one that's running over 100 times per minute, doesn't have anything out of the ordinary. The only thing that stands out in that one is that the temporary tables are being created in the middle of over 1000 lines of code. It does call a bunch of child stored procedures too.

Regarding Plan Caching in SQL Server 2008, I'm not seeing any literals >= 8k, but one of the stored procedures has a comment about a bulk insert right before it calls another stored procedure. But the bulk insert doesn't appear in the outer stored procedure that I am looking at. The "Recompilation Threshold" section of the article is interesting. What I'm seeing for the temporary tables is tons of INSERTs (that could result in millions of rows), some updates and deletes. So lots of data changes to the temporary tables. Millions.

There are two plan cache DMFs:

sys.dm_exec_query_plan - returns cached plans in XML format, but only up to a certain size (and only as long as they can be formatted as XML in SQL Server, which means up to 128 nested levels.)

sys.dm_exec_text_query_plan - returns cached plans in text format, of any size. But the drawback is that when plans are large, you can't convert them to XML inside SQL Server, and even TRY_CONVERT as XML returns a null.

sp_BlitzCache only hits the former DMV (because it needs to analyze query plans as XML to do all kinds of slicing and dicing.) I made Github issue #838 to improve this so we could at least alert users to go check sys.dm_exec_text_query_plan for their bigger queries. We still won't be able to do XML analysis on it, though.


With Clause vs View of the same name In my database, there is a view named VAL_DATES_V which is used in many other views in the same schema.  For example, the SQL code in the view SECTOR_V uses  VAL_DATES_V.

CREATE OR REPLACE VIEW schema1.sector_v AS
SELECT s.*
FROM   schema1.sector      s,
       schema1.val_dates_v d
WHERE  s.date_id   = d.date_id
AND    d.open_flag = 1;

In some use cases, we want to allow a user to override the VAL_DATES_V view with a "with clause" of the same name:

WITH val_dates_v AS (
    SELECT 1234 AS date_id,
           1    AS open_flag
    FROM   dual
) SELECT * FROM schema1.sector_v;

In our Dev database this works perfectly fine, and SECTOR_V evaluates VAL_DATES_V as what is defined in the "with clause". However, in our QA database, the optimizer uses the schema defined view SCHEMA1.VAL_DATES_V instead of the "with clause" view of the same name.

Even more confusing, if I copy the SQL code for SECTOR_V in place of "sector_v" it works just fine in QA.

Let me know if anyone can explain why the view is taking precedent over the "with clause" view of the same name.

Real World Dev Explain Plan (partially formatted):

WITH val_dates AS
  (SELECT /*+ materialize */
          val_date,
          id,
          period_month,
          period_year,
          '1' AS open_flag,
          val_date_type
   FROM   client_rep.val_dates_table a
   WHERE  a.period_year= 2014
   AND    a.period_month = 9
  )
SELECT * FROM client_rep.xamin_sleeve_attribution_v

OPERATION    OBJECT_NAME     CARDINALITY     COST

SELECT STATEMENT         14      42
 **TEMP TABLE TRANSFORMATION
  LOAD AS SELECT    SYS_TEMP_0FD9D66E3_A916354E
   TABLE ACCESS VAL_DATES_TABLE 2   3
    INDEX   VAL_DATES_IDX2   2   1
     Access Predicates
      AND
       A.PERIOD_MONTH=9
       A.PERIOD_YEAR=2014**

 SORT   14   39
 HASH JOIN  14   38
 Access Predicates
 XSA.VARIABLE_COMP_FUND_CODE=F.VARIABLE_COMP_FUND_CODE
 VIEW
 index$_join$_005    105     2
 HASH JOIN
 Access Predicates
 ROWID=ROWID
 INDEX
 FUND_CONTROL_PK     105     1
 INDEX
 FUND_CONTROL_UK1    105     1
 HASH JOIN 14    36
 Access Predicates
 XSA.BATCH_ID=B.ID
 JOIN FILTER
 :BF0000     93      29
 NESTED LOOPS   93   29
 NESTED LOOPS   190      29
 VIEW
 VW_NSO_1    2   2
 HASH 2
 VIEW   2    2
 Filter Predicates
 AND
 TO_NUMBER(V.OPEN_FLAG)=1
 VAL_DATE_TYPE='Calendar End'
 **TABLE ACCESS
 SYS_TEMP_0FD9D66E3_A916354E     2   2**
 INDEX
 BATCHES_VAL_DATES_FK    95      1
 Access Predicates
 B.VAL_DATE_ID=ID
 TABLE ACCESS
 BATCHES     47      13
 Filter Predicates
 B.ACTIVE=1
 JOIN FILTER
 :BF0000     548     7
 TABLE ACCESS
 XAMIN_SLEEVE_ATTRIBUTION    548     7
 Access Predicates
 SYS_OP_BLOOM_FILTER(:BF0000,XSA.BATCH_ID)
 Filter Predicates
 SYS_OP_BLOOM_FILTER(:BF0000,XSA.BATCH_ID)

Create view statement:

CREATE OR REPLACE FORCE VIEW "CLIENT_REP"."XAMIN_SLEEVE_ATTRIBUTION_V" AS
SELECT XSA.*
     FROM CLIENT_REP.XAMIN_SLEEVE_ATTRIBUTION XSA,
          CLIENT_REP.BATCHES B,
          CLIENT_REP.FUND_CONTROL F
    WHERE     XSA.BATCH_ID = B.ID
          AND B.ACTIVE = 1
          AND XSA.VARIABLE_COMP_FUND_CODE = F.VARIABLE_COMP_FUND_CODE
          AND B.VAL_DATE_ID IN
                 (SELECT v.id
                    FROM client_rep.val_dates v
                   WHERE v.open_flag = 1 AND VAL_DATE_TYPE = 'Calendar End')
 ORDER BY VAL_DATE,
          FIXED_COMP_FUND_CODE,
          TIME_PERIOD,
          SORT_KEY;

Just trust me, the QA explain plan does not even materialize the "with clause". It ignores it and just uses the "val_dates_v"

This is a bug, fixed in the July 2014 CPU - See Oracle Support document 1666884.1.

Easily reproduced with the following:

create view v1 as ( select 'view' as vvv from dual );

create view v2 as ( select * from v1 );

with v1 as ( select 'cte' as vvv from dual) select * from v2;


How to replace foreign reference in PostgreSQL? Suppose I have a foreign reference column which is pointing to an item A, how do I change it to item B? I would like to modify all records whose foreign reference column is A to B.

Sample data,

CREATE TABLE foo ( a int UNIQUE, b int UNIQUE );
CREATE TABLE bar ( c int REFERENCES foo(a) );

Simple ALTER statement,

ALTER TABLE bar
  DROP CONSTRAINT bar_c_fkey,
  ADD FOREIGN KEY (c) REFERENCES foo(b);

And, that worked!

                Table "public.bar"
 Column |  Type   | Collation | Nullable | Default
--------+---------+-----------+----------+---------
 c      | integer |           |          |
Foreign-key constraints:
    "bar_c_fkey" FOREIGN KEY (c) REFERENCES foo(b)