Track database name in anemometer queries Hello i have installed anemometer on our server in order to see the queries that need optimization.

From the website hosted a few have a wordpress blog on them so it is hard to guess witch one is affecting the most in the query bellow:

select  wp_posts.*
    from  wp_posts
    inner join  wp_postmeta on ( wp_posts.id = wp_postmeta.post_id )
    where  ?=?
      and  ( ( wp_postmeta.meta_key = ?
               and  cast(wp_postmeta.meta_value as char) = ? )
           )
      and  wp_posts.post_type = ?
      and  ((wp_posts.post_status = ?))
    group by  wp_posts.id
    order by  wp_posts.post_date desc

Is there a way to track the database as well in anemometer?

(Until I get answers to my comment, here is all I can suggest...)

1. LEFT and the two tests on wp_postmeta have no impact on the outcome; get rid of them.

Get rid of wp_postmeta all together.  And get rid of the GROUP BY, which was probably a byproduct of the JOIN.
INDEX(post_type, post_status, post_date)

That leaves you with just

select  *
    from  wp_posts
    where  post_type = ?
      and  post_status = ?
    order by  post_date desc

Does that give you the right answer?


Array of template type in PL/pgSQL function using %TYPE I have a PostgreSQL database and a PL/pgSQL function that currently has arguments with copied types (for example IN arg_1 table_1.column_1%TYPE).

I want to update it so one of those arguments is actually an array of elements with that copied type. If it were a "normal" type, I'd just add [] after the type, like IN arg_1 integer[]. Following that logic, I tried adding the square brackets right after %TYPE, but I get a syntax error.

I couldn't find anything about in the Postgres docs, nor did my google fu yield useful answers.
How can I achieve that?

The %TYPE construct can only copy the exact type. But there is a simple workaround to register the according array type: Create a (temporary) table or view with the desired array type.
Template table:
CREATE TABLE foo (i int);

(Temporary) view to register the array type, optionally empty (LIMIT 0):
CREATE TEMP VIEW v_foo_i_arr AS
SELECT ARRAY (SELECT i FROM foo LIMIT 0) AS i_arr;

Or a (temporary) table:
CREATE TEMP TABLE foo_i_arr AS
SELECT ARRAY (SELECT i FROM foo LIMIT 0) AS i_arr;

Now your function works:
CREATE OR REPLACE FUNCTION f_arr_test(v_foo_i_arr.i_arr%TYPE)
  RETURNS foo.i%TYPE AS  -- example: return element type
$func$
BEGIN
   RETURN $1[2];
END
$func$  LANGUAGE plpgsql;
You might as well make that a temporary view or table, since the type in the function signature is converted to the underlying type immediately. No persisted connection to the used template. So it's no problem that temp objects are dropped at the end of the session.
Polymorphic type
You might be better off with a polymorphic type to begin with. This works for any array type:
CREATE OR REPLACE FUNCTION f_arr_test_polymorphic(ANYARRAY)
  RETURNS ANYELEMENT AS  -- derived from input type
$func$
BEGIN
   RETURN $1[2];
END
$func$  LANGUAGE plpgsql;
SQL Fiddle.
Note that using a %TYPE declaration inside the function body actually works dynamically in that it looks up the type in the system catalog at the first call of every session.
Related question on SO:

PL/pgSQL - %TYPE and ARRAY


Initializing values to a custom data type I have defined a custom data type which I need to use in my procedure. Give below is my data type.

CREATE or REPLACE TYPE CUSTOM_DATATYPE AS object(
chkinvpre    VARCHAR2(100),
orderid      NUMBER(10),
deldate      DATE,
fowno        VARCHAR2(40),
fowname      VARCHAR2(40),
fowadd1      VARCHAR2(40),
fowadd2      VARCHAR2(40),
fowadd3      VARCHAR2(40),
fowadd4      VARCHAR2(40),
packtype     VARCHAR2(40),
noofpacktype VARCHAR2(40),
gweight      VARCHAR2(40),
dimension    VARCHAR2(40),
hawb         VARCHAR2(40)
)
NOT final;

Now, I am trying to initialize this data type to call my  procedure . Given below is the code.

declare
rajesh CUSTOM_DATATYPE;
begin
 rajesh:=('xyz',123,'13-02-14','12','fdfd','add1','add2','add3','add4','packtype','noofpacktype','1234','dfs','dde');
  DN_Generated(rajesh);
end;

/

And here is the error that I am getting.

Error starting at line 1 in command:
declare
rajesh CUSTOM_DATATYPE;
begin
 rajesh:=('xyz',123,'13-02-14','12','fdfd','add1','add2','add3','add4','packtype','noofpacktype','1234','dfs','dde');
  DN_Generated(rajesh);
end;
Error report:
ORA-06550: line 4, column 10:
PLS-00320: the declaration of the type of this expression is incomplete or malformed
ORA-06550: line 4, column 2:
PL/SQL: Statement ignored
06550. 00000 -  "line %s, column %s:\n%s"
*Cause:    Usually a PL/SQL compilation error.
*Action:

Please let me know the error.
Thanks in advance.

I believe you are missing the datatype before the initialization of the type/object.  See the updated (but untested code below).

Oracle Documentation

declare
    rajesh CUSTOM_DATATYPE;
begin
    rajesh := CUSTOM_DATATYPE('xyz',123,'13-02-14','12','fdfd','add1','add2','add3','add4','packtype','noofpacktype','1234','dfs','dde');
      DN_Generated(rajesh);
end;


About transactions on mongodb To what extent can the atomic storage of MongoDB leave your database unsafe after inserts or updates on my data?

In other words: What are the possibilities of my data not been (inserted or updated) on collection?

Imagine a big application running, many inserts/updates and other things on the same time and asynchronous, I want know if that are a problem? What the risks to my data?

Copying Joao's answer (from question comments) so this Q can have an A.

No, there is no problem with that. Single inserts/updates are atomic and reads are always consistent. However, you don't have transaction blocks in MongoDB (executing multiple inserts/updates atomically). – joao Feb 24 '14 at 13:26


Restore data from tar file I have a tar archive with full backup of my CentOS filesystem with PostgreSQL 9.1 installed.

How can I restore one database from it?

If I can only restore a full database cluster, how can I do that? Just copy-paste all the files?

You will need a locally installed PostgreSQL of the same major as the data directory archive. For example, if the archive came from 9.1.5 you would need some 9.1.x on the local machine, it doesn't matter if it's 9.1.0 or 9.1.9, but it must be 9.1 not 9.2 or 9.0.

If you don't have 9.1, you can install it from packages or just download the source and compile it locally.

Then, if you just want to dump the data to reload into an existing local PostgreSQL install:

Unpack the archive
If necessary set your PATH so that PostgreSQL 9.1's bin directory is first on the PATH
PGPORT=5435 pg_ctl -D /path/to/unpacked/archive start
PGPORT=5435 pg_dumpall --globals-only
Dump each database with pg_dump

If you instead want to restore the database so it runs instead of whatever's locally running, you must:

Ensure the current install is of the same major version
Stop the current PostgreSQL: service postgresql stop
Rename the data directory. On CentOS it'll be in /var/lib/pgsql/9.1/data/, so something like sudo mv /var/lib/pgsql/9.1/data /var/lib/pgsql/9.1/data.old. Do not delete it; if it turns out there is data in there you forgot you needed, this way you can still get it back.
Move the unpacked archive to the old data directory location, e.g sudo mv /path/to/unpacked/archive/folder /var/lib/pgsql/9.1/data
Set the permissions: sudo chown postgres:postgres /var/lib/pgsql/9.1/data; sudo chmod 700 /var/lib/pgsql/9.1/data
Edit postgresql.conf to reflect any required changes for the local install
Start PostgreSQL: service postgresql start

Some details like service name, path, etc will vary depending on whether you're running the original Red Hat packages of PostgreSQL, or whether you installed from http://yum.postgresql.org/ .


Get first login-Hit on huge table I have table like this in my database :

ID (pk,int,not null)

UserID (int,null)

HitDate (datetime,null)

Every time a user has any transacation in my application one hit inserted in this table.

Now my client want to find out how  many new users I have per day from the beginning of application.

As I find out I have to group user by "UserID"  and get the first HitDate, then remove other hit dates for that user. The first one will be the first login into system. Then I count using this method of all user per day.

My problem is this table has a huge row number, near 1,800,000,000 rows !!!!

The only solution I find at the moment is to break it into few day and insert it into a temporary table, then remove duplicates from it.

This is my code : (ID is  Autoincrement)

INSERT INTO  temptable
(UserID , HitDate )
Select vs.UserID ,
        vs.HitDate
        From UserHitTbl vs where
 LEN(vs.UserID ) > 1 and
vs.HitDate  > '2013-06-12 14:32:59.783' and
vs.HitDate < '2013-07-12 14:32:59.783'
  DELETE
FROM temptable
WHERE ID NOT IN
(
SELECT MAX(ID)
FROM t6
GROUP BY UserID )

The above gives the following error:

  Could not allocate space for object '' in database 'tempdb' because the 'PRIMARY' filegroup is full. Create disk space by deleting unneeded files, dropping objects in the filegroup, adding additional files to the filegroup, or setting autogrowth on for existing files in the filegroup

That error is simply telling you that the volume(s) you have tempdb on is/are full. Unless you have explicitly altered the file layout of tempdb it will all be on C:. You must be pretty low on space where tempdb is unless the amount of rows for that 24 hour period is massive.

Anyway, spooling large amounts of data into a temporary table onyl to delete most of them immediately is pretty much always the wrong way to go: with decent indexes on that table you should be able to extract the relevant data with a SELECT query like so:

SELECT user_id, MIN(action_date)
FROM   your_table
WHERE  action_date BETWEEN <start> AND <end>
GROUP BY user_id

The above will list every user who touched the system (so has entries in that table) between the dates/times specified along with the first time they touch the system in that time period.

It would be useful to list in your question what keys and indexes you currently have on that table. An index with action_date is going to be pretty much essential. A compound index that covers action_date and other columns involved may be even better, though will take more space to store, depending on the balance of data. How any unique users do you have and over how many days has this data been recorded? (again: add such details to your question so everyone sees them easily, not just in a comment added to this answer)

If you just want to list users who have never logged an action before this time, then you need something like:

SELECT user_id, action_date
FROM   (
       SELECT user_id, action_date=MIN(action_date)
       FROM   your_table
       WHERE  action_date BETWEEN <start> AND <end>
       GROUP BY user_id
       ) derived_table
WHERE NOT EXISTS (SELECT user_id FROM your_table WHERE action_date < <start>
                  AND derived_table.user_id = your_table.user_id)

I'm using the original query as a derived table here to avoid the chance the the query planner will try to apply the NOT EXISTS check to every one of those 1,800,000,000 rows, instead this way is should definitely find the users who were active today and run the sub-query once for each of them once only (so the sub-query will run at most once per user). For this to work you are going to need an index covering user_id (one covering user_id, action_date would most likely be better still).

Of course if you don't have sufficient indexes already adding them is going to be a very I/O intensive operation as for each SQL Server will need to read all those 1.8 billion rows and write all the index rows to go with them.


How do I configure SQL Profiler to show me queries to the views sys.databases and sys.tables? I have a simple C# app that iterates through all databases on a server examining their schema. As such it performs a SELECT name from sys.database, iterated through those and does a SELECT name from sys.tables WHERE name='Something'. Then if it finds the table its looking for it does some queries, and does stuff with the data.

The connection string has an application name. If I setup a trace with a column filter on that application name looking for start/complete transaction, start/complete stored procedure, and complete statement. I see the queries I perform on the actual database tables, but not the queries to sys.databases and sys.tables.

There's nothing inherent in Profiler that would prevent those queries from being displayed. I think you're just not catching the right events. It sounds like you're filtering for Stored Procedure events. Try also filtering for TSQL events.


Transactional replication of Date column from Sql Server 2012 SP4 to Azure Sql Database We have a Sql Server 2012 SP4 where we are trying to setup transactional replication to Azure Sql Database. When starting replication is says

  Column mycolumn in object mytable contains type Date, which is not supported in the target server version, SQL Server 2005.

Since Azure Sql Database should support Date datatype Data types the problem could be that Sql Server identifies Azure Sql Database as version 2005.

Is there a way to get this working?

converting my comment to answer ...

Since you confirmed that the db compatiblity is 90 which is sql 2005

Run

SELECT name, compatibility_level
FROM sys.databases WHERE name = 'YOURDBName'

and then you can use ALTER DATABASE [YOURDBName] SET compatibility_level = 110 -- 2012 version


Can you set the output configuration at Query time? Is there a TSQL statement to configure the output to be Text-Column aligned?

I want to use SQL templates for my coworkers to run some templated UPDATE before/after statements. But when they run the query it needs to output to text and Column delimited

So contrived example say I have this query:

USE master
GO

SELECT *
FROM sys.dm_os_ring_buffers AS dorb
WHERE dorb.timestamp = N'<time, bigint, NULL>'

Is there a some SET statement to configure the output to be Text-Column aligned? This is only for these specific queries otherwise my coworker would want their default settings which is grid. I also can't rely on them to know to change it and that is important because unlike my contrived example they are print commands of sql update before/after statements.

Thus far I think my best bet would be to create a SSMS Add-in but I REALLY do not want to spend time doing that!

No, there is no programmatic way (outside of what you suggested: writing a custom SSMS plug-in) to control SSMS (for the most part). Like most commands, SET commands are executed by SQL Server. The only control you could have locally is a subset of the SQLCMD commands -- :r, :setvar, :connect, and so on -- but none of those allow for configuring the UI / SSMS.

BUT, all hope might not be lost yet. Since you are giving your coworkers T-SQL templates to copy and paste and fill out, and since there are already short-cut keys to toggle between "Results to Grid" and "Results to Text", just add in a few helpful comments to direct them on how to execute the script. Something along the lines of:

/*    INSTRUCTIONS:

1) Control-Shift-M    (to fill out parameter values)
2) Control-T          (for Results To Text)
3) Control-E (or F5)  (to execute query)
4) Control-D          (for Results to Grid)
*/


Multi-Subnet Always On with Availability Group for DR I researched a lot of mutli-geographical DR - Disaster Recoververy solution architectures for MSSQL and most of the proposed solutions I came across recommends HA using 2 nodes Failover clustering in one site and DR node in another location through the use of Availability Group such as the image below shows:

We currently have three physical machines and I came up with the following architectural.  It employs multi-subnet clustering for fail over and Availability Group for data replication between two sites.

My logic of doing this is that I can achieve DR solution with two nodes while I still can use my DR node for failover.  My third node - NODE3 is a read only used for Reporting so it is not suitable for DR nor a fail-over node.

My question is, is it okay or is it a best practice to have two nodes in a fail over cluster with one is a DR nodes?

Usually, if you want to use a node for "failover" (high availability), you want to have the AG set for synchronous replication (to enable the auto-failover).

That being said, if you set it to "sync" and it's in another site, you may have performance issue caused by network latency.

Also, if your "read only" node is not part of the AG, then you will have to implement something else to keep the data sync (or close to sync)

This is why we usually see 2 nodes on the same site (for High Availability) and a 3rd node on a remote site (for DR) that usually is an async replica.

I hope it will help you to make the right design.


SQL server windows authentication through network I am using sql server 2008 r2.There are about 50 pc in my office connected in network by using LAN.In a sql server 2008 r2 express(installed in windows 8) i have created all users windows account with same name as their name in their PC and then created windows authentication user in sql server. Now all the users are able to connect to sql server using windows authentication through SSMS.

But now i am trying implement same for the another SQL server 2008 r2 express which is installed in windows xp sp3. So i created one windows authentication user of network PC. But it is not working when i try to connect to sql server using windows authentication from network pc  message comes like "Login failed for user 'PC91\Guest'. " It is not recognizing the windows account of my pc. why it happens? Please tell me a solution.

UPDATE:

SQL server and browser is running and sql server is already configured to use remote connections.Also i can ping the PC.

My doubt is is it allowed to connect sql server in another pc by creating same account as in sql server installed PC?

Let me explain what i did below:

In sql server installed pc(windows XP) i created windows login 'test'.Then i created sql windows authentication login using 'test' windows account.

In another PC i created windows user account 'test'(same as in pc having sql server,with same password) so that i have same user account in both PC. Now from this PC ,i connect to sql server of another PC by using windows authentication('test'). But it worked properly for sql server which is installed in windows 8 and windows 7.But for the sql server installed in XP it didn't work(I tried in two instance in two different windows XP machine). So i doubt if this feature is allowed in windows XP or i am going wrong in anyway(may be any settings missing)?

Do the username and password match between the network pc and the sql machine?  I also found this link regarding, xp home. I am not sure what version of XP you are using.

https://serverfault.com/questions/54761/pass-through-authentication-does-not-work-with-a-specific-account-in-windows-xp


Unable to save a stored procedure which contains a temporary table creation and update query I am unable to save my procedure in Postgres. The procedure contains a temporary table creation and update query.
Here is my query:
CREATE OR REPLACE PROCEDURE account_report_v2()
LANGUAGE SQL
AS $$
    BEGIN;

        CREATE TEMP TABLE report_data on commit drop AS (select * from tests);

        update report_data set
            df_name = dfn,
            dl_name = dln
        From
        (
            select  id  ,first_name dfn,last_name dln from df_info
        )AS subquery
        where (report_data.provider_id :: varchar(50)) = (subquery.id :: varchar(50));

    COMMIT;
$$;

CALL account_report_v2();

When I try to save the procedure, postgres gives the following error:

relation "report_data" does not exist.

Here is how it looks like in pgAdmin 4:

Please advise

You have to specify LANGUAGE plpgsql rather than LANGUAGE sql.
The function does not consist of only SQL statements, and other than for PL/pgSQL, the SQL language validator parses the SQL statements and checks them for validity.


Count the number of occurences in a query result I am running the below query on my sql server 2012 database.

select Appointment.AppointmentDate, Appointment.AppointmentTime,   Auctioneer.AuctioneerName, Auctioneer.AuctioneerSurname, Buyer.BuyerName,   Buyer.BuyerSurname
from Appointment
inner join Auctioneer on Appointment.AuctioneerId=Auctioneer.AuctioneerId
inner join Buyer on Appointment.BuyerId=Buyer.BuyerId
where AppointmentDate between '2017-01-01' and '2017-12-31'

The Result of this is the below:

+-----------------+-----------------+----------------+-------------------+-----------+----------------+
| AppointmentDate | AppointmentTime | AuctioneerName | AuctioneerSurname | BuyerName | BuyerSurname   |
+-----------------+-----------------+----------------+-------------------+-----------+----------------+
| 2017-10-23      | 13:00:00        | Mary           | Borg              | David     | Borg           |
| 2017-10-24      | 15:30:00        | Mary           | Borg              | Joseph    | Sammut         |
| 2017-11-03      | 09:30:00        | Joseph         | Smith             | Mark      | Psaila         |
| 2017-11-03      | 10:45:00        | Joseph         | Smith             | David     | Borg           |
| 2017-11-15      | 10:10:00        | Mary           | Borg              | David     | Borg           |
| 2017-08-02      | 08:30:00        | Daisy          | Webb              | Josephine | Grima          |
| 2017-04-15      | 14:00:00        | Sam            | King              | Mary      | Santucci       |
+-----------------+-----------------+----------------+-------------------+-----------+----------------+

I need to add a column which counts the number of occurrences of the 'AuctioneerName' & AuctioneeSurname' in the result.

Example:  Mary Borg   Count 3, Joseph Smith Count 2 etc...

I think you just need a window count():

COUNT(*) OVER (PARTITION BY AuctioneerSurname, AuctioneerName)

The query with the added computation - and using aliases for the long table names:

select
    app.AppointmentDate,  app.AppointmentTime,
    auc.AuctioneerName,  auc.AuctioneerSurname,
    buy.BuyerName,  buy.BuyerSurname,
    AuctioneerCount = count(*) over
                         (partition by auc.AuctioneerSurname, auc.AuctioneerName)
from
    Appointment as app
    inner join Auctioneer as auc
        on app.AuctioneerId = auc.AuctioneerId
    inner join Buyer as buy
        on app.BuyerId = buy.BuyerId
where
    app.AppointmentDate between '2017-01-01' and '2017-12-31' ;


What is the difference between a connection and a session? What is the difference between a connection and a session and how they are related?

The connection is the physical communication channel between SQL Server and the application: the TCP socket, the named pipe, the shared memory region. The session in SQL Server corresponds to the Wikipedia definition of a session: a semi-permanent container of state for an information exchange. In other words the sessions stores settings like cache of your login information, current transaction isolation level, session level SET values etc etc.

Normally there is one session on each connection, but there could be multiple session on a single connection (Multiple Active Result Sets, MARS) and there are sessions that have no connection (SSB activated procedures, system sessions). There are also connections w/o sessions, namely connections used for non-TDS purposes, like database mirroring sys.dm_db_mirroring_connections or Service Broker connections sys.dm_broker_connections.


retrun multiple functions from dynamic query I need to return multiple fields from dynamic query.I am referring this link below code what I am doing so far

CREATE OR REPLACE FUNCTION get_names(ind_zone text,dst text,nmind text,ind text,implst text,rsvr text,n34b text,prjapr text)
RETURNS   SETOF RECORD AS
$BODY$
DECLARE
sql text;
BEGIN
sql:='select indzone,dstr,nmindar,plno,pltar,nmalt from plotboundary where indzone='|| quote_literal(ind_zone)||' AND dstr='||quote_literal(dst)||' AND nmindar='||quote_literal(nmind)||' AND indx='|| quote_literal(ind);
IF implst IS NOT NULL THEN
 sql := sql || ' AND ';
 sql := sql ||' implst = ' || quote_literal(implst);
END IF;
IF rsvr IS NOT NULL THEN
 sql := sql || ' AND ';
 sql := sql ||' rsvr = ' || quote_literal(rsvr);
END IF;
IF n34b IS NOT NULL THEN
 sql := sql || ' AND ';
 sql := sql ||' n34b = ' || quote_literal(n34b);
END IF;
IF prjapr IS NOT NULL THEN
 sql := sql || ' AND ';
 sql := sql ||' prjapr = ' || quote_literal(prjapr);
END IF;
RETURN QUERY EXECUTE sql;
END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
ALTER FUNCTION get_names( text,
 text, text, text, text, text, text, text)
  OWNER TO postgres;

calling function like

 select * from get_names('Tumakuru Zone','Tumakuru','Vasanthanarasapura 2nd Phase','Allotted','Yet to Start',null,null,null)
    AS t(indzone text,dstr text,nmindar text,plno text,pltar text,nmalt text);

I am getting error like

ERROR:  structure of query does not match function result type
DETAIL:  Returned type character varying(20) does not match expected type text in column 1.
CONTEXT:  PL/pgSQL function get_names(text,text,text,text,text,text,text,text) line 22 at RETURN QUERY

It looks like indzone is a varchar(20), not a text - you could either specify the returned record using the former, or cast indzone inside the function.  (This applies to the solution suggested below, too.)

But:

As it looks like, you don't need any dynamic SQL in your function, as you never use any object (table, column, etc.) name specified on the fly.  Your query could look like

SELECT indzone, dstr, nmindar, plno, pltar, nmalt
  FROM plotboundary
 WHERE indzone = ind_zone AND [...]
   AND implst = COALESCE(_implst, implst) -- changed the `IN` parameter name to avoid confusion
   AND rsvr = COALESCE(_rsvr, rsvr)
   AND [...]

In certain (I'd say rare) cases, this can be less efficient than you version, but that you can find out easily on your data.  Most probably a well chosen index on the 'fix' columns (that are always compared to the incoming parameters) saves you worrying about it.


SQL Server Create Database File Error I get the below error running a CREATE DATABASE script...

CREATE FILE encountered operating system error 5(failed to retrieve text for this
error. Reason: 15105) while attempting to open or create the physical file
'H:\TestDatabase.mdf'

I am logged in as sa, which is a sysadmin.

What am I missing?

CREATE DATABASE TestDatabase ON PRIMARY
(
    NAME = N'TestDatabase',
    FILENAME = N'H:\TestDatabase.mdf' ,
    SIZE = 51200000KB ,
    FILEGROWTH = 1024KB
)

Don't put your database files on the root of any drive. All kinds of things go wrong here, especially if you have UAC enabled.

Create a subfolder, like H:\Data or H:\Log, and put the files there, making sure the SQL Server service account has read/write permissions. Or use the actual instance data/log folders, which should already be set up permissions-wise. You can change those, too, if they're currently in a not-so-optimal place (like somewhere on C: <shudder>).

(Also, your CREATE DATABASE command is missing a LOG ON specification, dictating where you should put your log file.)


Why does `pg_lsclusters` not list my Postgres cluster? I apt-get installed Postgres 9.2.4 (using PPA).
I used pg_dropcluster --stop 9.2 main to remove the default database cluster, because I intended on creating a new cluster on a separate SSD volume (a Rackspace block storage volume) that was just mounted.
I created that new cluster whose data resides on my SSD volume and  started it (ps shows all the normal Postgres processes running for that new cluster and I'm able to open a Postgres shell and run SQL - i.e., it's running fine). Now when I run pg_lsclusters there are none listed.

The main cluster listed just fine before I deleted it. Why won't my new cluster show up, even when it's running? I've even rebooted the server (just in case).

In order to use these postgres common tools with a cluster, they need to be used consistently. You would use pg_createcluster -d /path/to/data, instead of initdb. pg_createcluster adds the cluster to postgres-common in order to facilitate these handy operations. It is a wrapper, just like pg_ctlcluster, around the lower-level tools (e.g., initdb and pg_ctl), providing access from one place to operating on all of your clusters. It also has some defaults, such as an automatically configured log location based on the cluster name. this means you don't have to use -l /var/log/my-custom.log when you're creating or starting the server.


Use surrogate or natural PK if a row is uniquely identified by two or more columns? I am having a hard time choosing between natural and surrogate PK for my database table.

The database is for a ranking system for an MOBA game. Here is more or less what I would do if I were to go the natural route.

Player table

 - PlayerID Int AI NN UQ
 - PlayerName Varchar PK
 - ServerName Varchar PK
 - Registered date, level, hero etc misc columns

Rank table

 - PlayerName FK references player
 - ServerName FK references player
 - RankID Int AI NN UQ
 - PlayerRank Int NN UQ

The thing is, each row in the player table is uniquely identified by the pair of PlayerName and ServerName. I thought using a surrogate key in this case is not really appropriate but I would like to hear suggestions on this.

That is part of one of computing's big philosophical debates. Sometimes it gets as heated amongst information theorists as the vi/emacs thing does amongst Linux developers...

I always avoid using a value that might change as a key or part of a key, in this case player name or server name unless your system has an explicit rule that those properties can not change and must be unique. While you can in some databases use ON UPDATE CASCADE to remove problems due to the value of a PK needing to be updated, this feels unclean to me. If the player name must be unique then you do not really need a separate player ID as playername+servername is a candidate key in the player table too.

BTW: Your player table feels incorrect to me.I would keep servers as a separate entity so the same player record can be linked to multiple servers, so one player who plays in many places does not have to have different records for each server. Something like:

    Player             Ranking               Server
    --------------     -----------------     --------------
    PlayerID (PK)  <-- PlayerID (FK, PK)     ServerName
    PlayerName         ServerID (FK, PK) --> ServerID (PK)
    {more columns}     PlayerRank            {more columns}

or

    Player             Ranking           Server
    --------------     -------------     --------------
    PlayerID (PK)  <-- PlayerID (FK)     ServerName
    PlayerName         ServerID (FK) --> ServerID (PK)
    {more columns}     PlayerRank        {more columns}
                       RankID (PK)

(probably the former as PlayerID+ServerID is a candidate key and not having the extra ID saves space in both not having the extra data and needing an extra index)

One further point: PlayerRank can not be a unique index on its own as you will have a "rank 1" player on each server that has at least one player, so the unique index will need to be over ServerID+PlayerRank.


Microsoft sql server management studio (SSMS) shortcut for changing size of sql/result panes In the good old days there was a shortcut ctrl-B for changing the size of the Query & Result panes in the Query Analyzer sql editor.

In Query Analyzer, just press ctrl-B and the screen splitter handle was in focus. Then repeatedly press up/down arrows until the size was right.

Is there a shortcut for that nowadays, in SSMS?

SSMS has no equivalent shortcut to the CTRL+B shortcut from Query Analyzer.

In my opinion, the best alternate feature is the ability to have query & results in separate tabs. In this way, I can flip between full-screen query & results easily, and maximize screen real estate for both.

To enable this in SSMS:

Go to Tools-->Options...
In the left navigation tree, go to Query Results-->SQL Server-->Results to [Grid/Text] (The option exists for both grid & texts)
Mark the checkbox for "Display Results in a separate tab"

Optionally mark the checkbox for "Switch to results tab after the query executes"

You will need to open a new SSMS query tab for the change in setting to take effect.

You can then use F6 (or Shift+F6) to cycle your focus forward (or backwards) between Query, Results, and Messages tabs. If you have multiple result sets on the Results tab, each press of F6 will bring your query focus to the next result set.


SQL Server 2008 R2 Query Last Successful Database Backup For All DBs Is it possible to query the last successful backup date (and perhaps the type of backup) of each database in SQL Sever 2008 R2?

Yes, as long as the history data is still in msdb database. The following block of code will get your the last 100 backups including full, differential and log backups.

SELECT TOP 100
    s.database_name,
    m.physical_device_name,
    CAST(DATEDIFF(second, s.backup_start_date,
    s.backup_finish_date) AS VARCHAR(100)) + ' ' + 'Seconds' TimeTaken,
    s.backup_start_date,
    CASE s.[type]
    WHEN 'D' THEN 'Full'
    WHEN 'I' THEN 'Differential'
    WHEN 'L' THEN 'Transaction Log'
    END AS BackupType,
    s.server_name,
    s.recovery_model
    FROM msdb.dbo.backupset s
    INNER JOIN msdb.dbo.backupmediafamily m ON s.media_set_id = m.media_set_id
    WHERE s.database_name = DB_NAME() -- Remove this line for all the database
    ORDER BY backup_start_date DESC, backup_finish_date
    GO


Disable MySQL replication I'm running following:

# cat /etc/redhat-release
Red Hat Enterprise Linux Server release 6.6 (Santiago)
# uname -a
Linux X 2.6.32-504.3.3.el6.x86_64 #1 SMP Fri Dec 12 16:05:43 EST 2014 x86_64 x86_64 x86_64 GNU/Linux
# rpm -q mysql-server
mysql-server-5.1.73-3.el6_5.x86_64
#

I issued STOP SLAVE; and deleted master.info and now /var/log/mysqld-error.log is producing following:

[ERROR] Error reading master configuration
[ERROR] Failed to initialize the master info structure

What's the proper way to disable MySQL replication?

I believe, I found it)

mysql> RESET SLAVE;
Query OK, 0 rows affected (0.01 sec)

mysql>


Why different users from same login group can not access the database objects? I have SQL login group as "SC_NT\xxxx" in server. There are multiple users using this login group to access database objects. User "A" can able to access db objects from .net application. But when user "B" tried to access same db objects, its showing like

Error: The EXECUTE permission was denied on the object 'stored_procedures',
database 'test',schema 'dbo'.

Both the users are using windows authentication for access the objects.  Kindly give me some clear idea to fix this issue??

2 possible reasons:

User A receives necessary permissions via his membership in some other group,
or explicitly being a member of server / database role, or has
permissions granted directly.
User B is denied some permissions, again - either via his membership in some other group, or explicitly being a member of server / database role, or has these permissions denied to him directly.

You can compare their role memberships on the database / server level, but membership in Windows groups cannot be checked via SQL Server - you would have to look in local groups AND Active Directory, if the machine is a member of a domain.

EDIT: here is a set of queries that may help you track down actual origins of permissions. Just make sure that you run them under the user with appropriate credentials, otherwise you will never see anything. Membership in sysadmin fixed server role is preferable.

declare @ObjectName sysname = N'YourObjectName',
    @UserName sysname = N'YourUserName';

-- Grants on database securable
select schema_name(ao.schema_id) as [SchemaName], ao.name, ao.type_desc,
    pr.type_desc, pr.name as [Principal], dp.permission_name, dp.state_desc
from sys.database_permissions dp
    inner join sys.all_objects ao on ao.object_id = dp.major_id
    inner join sys.database_principals pr on pr.principal_id = dp.grantee_principal_id
where dp.class_desc = 'OBJECT_OR_COLUMN'
    and ao.name = @ObjectName;

-- Direct permissions of database principal
select schema_name(ao.schema_id) as [SchemaName], ao.name, ao.type_desc,
    pr.type_desc, pr.name as [Principal], dp.permission_name, dp.state_desc
from sys.database_permissions dp
    left join sys.all_objects ao on ao.object_id = dp.major_id
    inner join sys.database_principals pr on pr.principal_id = dp.grantee_principal_id
where pr.name = @UserName;

-- Membership in database roles
select rl.name as [DatabaseRole]
from sys.database_role_members rm
    inner join sys.database_principals rl on rl.principal_id = rm.role_principal_id
    inner join sys.database_principals pr on pr.principal_id = rm.member_principal_id
where pr.name = @UserName;

-- Membership in server roles
select rl.name as [ServerRole]
from sys.server_role_members rm
    inner join sys.server_principals rl on rl.principal_id = rm.role_principal_id
    inner join sys.server_principals pr on pr.principal_id = rm.member_principal_id
where pr.name = @UserName;

The only thing not covered here is a membership in Windows / Active Directory security groups, but, as I've said, it is impossible to track it within SQL Server.


Any Reason to have AlwaysOn Failover Clustering in Azure Cloud? Is there a reason to have AlwaysOn Failover Clustering when a database is in Azure Cloud?

I understand the benefits having AlwaysOn Availability Groups in Azure Cloud for Database-Level protection. People can transition heavy read queries to the secondary replica (to prevent locking-blocking, tempdb issues), offload maintenance tasks, etc.

However why would someone require Failover Clustering in Azure Cloud for server-level protection? Don't cloud services (both PaaS and IaaS) inherently offer server level protection by itself?

If you are using Azure VMs to host your SQL server, in my personal experience there is one major factor for building an AlwaysOn FCI - the failover process happens much more quickly than the migration of an entire VM.

From what I've seen, you're looking at a difference of tens of seconds - minutes for a VM failover, to 1-2 seconds for an AlwaysOn FCI failover running in synchronous commit mode.

In the organisation I work in, that difference is critical, and an extra 60 seconds of down time can cost the company a significant amount of business and money.

I think it's also important to understand what causes these kind of issues - what will you do if an entire locally redundant system goes down, and immediate failover isn't possible? This actually happened to us, after which we realised that both halves of our two node in AlwaysOn FCI  were actually running on the same rack! (Eek) sufficed to say that this is no longer the case. We now replicate between two completely separate regions.

As for Azure SQL databases, this comes down to DR-scale events usually - entire data centre outages etc.

In summary, don't put all your eggs in one basket, and don't assume the word 'cloud' means that you don't need to consider DR and HA.


Azure compatible client tools I'm building a DB for someone as a favor and whilst I'm perfectly OK to create a DB in Azure SQL for them and do the requisite TSQL coding they require I do also need to give them a client based way to access the data that involves no non-SQL coding from me.

Ideally this would be a light weight alternative to SSMS that is compatibile with Azure. That way I can give them a series of scripts and paramtised SPs to run. Can someone recommend something that works please?

Thanks for reading

Edit: Put on hold for "will likely solicit debate, arguments, polling, or extended discussion" but this came after the question had been answered and had not caused such.

Perhaps SQL Azure Database Manager? Requires a browser, internet connection, and Silverlight. http://www.mssqltips.com/sqlservertip/2295/getting-started-with-the-sql-azure-database-manager/


Can I alter ENUM value from lowercase to studlyCase without reindex? I have a several big tables, and enum in each of them (example):

ENUM ('webmasters', 'stackexchange', 'stackoverflow')

Documenation says, that return value is in lettercase of ENUM definition, so I have a problem now, because I need return values in studlyCase:

ENUM ('webMasters', 'stackExchange', 'stackOverflow')

Actually I need to change only one value from big list of defined enum values, also there is yet no data in the tables with this value. And if it is matter - the value is almost in the end of enum definition.

The tables are very big - tens millions of data. So here is my question - Can I alter the tables so the index wil not be recalculated?

Enumerations are physically stored in the table has a number which maps to the positional value of the enumeration.

You can modify your column to change the enumeration and shouldn't experience issues with stored data values (it is always better to be safe than sorry - make a backup of the table just in case).

Enumerations come with disadvantages that you might not be aware of.  The following is a stackexchange.com link discussing the merits of enumerations.

Advantages and Disadvantages to using ENUM vs Integer types?

Here we create a table with one column of enumeration.

mysql> create table mathisr.test (c1 enum  ('webmasters', 'stackexchange', 'stackoverflow'));
Query OK, 0 rows affected (0.00 sec)

mysql> insert into test values (1),(2),(3);
Query OK, 3 rows affected (0.01 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select * from test;
+---------------+
| c1            |
+---------------+
| webmasters    |
| stackexchange |
| stackoverflow |
+---------------+
3 rows in set (0.00 sec)

Notice, the enumeration presents new values after we modify the column to express the value with different case.

mysql> alter table test modify c1 enum   ('webMasters', 'stackExchange', 'stackOverflow');
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> select * from test;
+---------------+
| c1            |
+---------------+
| webMasters    |
| stackExchange |
| stackOverflow |
+---------------+
3 rows in set (0.00 sec)

The following command demonstrates adding a value within the enumeration...

mysql mathisr> alter table test modify c1 ENUM  ('webMasters', 'stackExchange', 'somethingNew', 'stackOverflow');
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select * from test;
+---------------+
| c1            |
+---------------+
| webMasters    |
| stackExchange |
| stackOverflow |
+---------------+
3 rows in set (0.00 sec)

When we look at the data we will see MySQL actually changed the stored number to accommodate the new value inserted into the middle of the enumeration.  The introduction of the new value would have had indexing implications.

mysql> alter table test modify c1 tinyint;
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select * from test;
+------+
| c1   |
+------+
|    1 |
|    2 |
|    4 |
+------+
3 rows in set (0.00 sec)


Error exp oracle XE on centos i have a problem when i try to export ...

Connected to: Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production
Export done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set

About to export specified users ...
. exporting pre-schema procedural objects and actions
. exporting foreign function library names for user SEMESU
. exporting PUBLIC type synonyms
. exporting private type synonyms
. exporting object type definitions for user SEMESU
About to export SEMESU's objects ...
. exporting database links
. exporting sequence numbers
. exporting cluster definitions
EXP-00056: ORACLE error 12704 encountered
ORA-12704: character set mismatch
EXP-00000: Export terminated unsuccessfully

anyone? (i'll apreciatte)

You will have to export NLS_LANG env variable like mentioned here
http://docs.oracle.com/cd/E11882_01/server.112/e16604/ch_eleven.htm#sthref1049
If you use expdp, you don't need to do this...


SQL Server SELECT column not in Group By clause? As you can see my query, this is the working one

SELECT m.MerchandiseId, m.MerchandiseName, createdAt =  MAX(i.CreatedAt) FROM Inbox i
INNER JOIN [User] u
ON i.FromUserId = u.UserId
LEFT OUTER JOIN Merchandise m
ON u.MerchandiseId = m.MerchandiseId  OR i.ToMerchantId = m.MerchandiseId
WHERE i.ToCompanyId = 10 OR i.FromCompanyId = 10
GROUP BY m.MerchandiseId, m.MerchandiseName

What if i want to SELECT one more field as i.InboxMessage with the Last record of each group?

SELECT m.MerchandiseId, m.MerchandiseName, i.InboxMessage, createdAt =  MAX(i.CreatedAt) FROM Inbox i
INNER JOIN [User] u
ON i.FromUserId = u.UserId
LEFT OUTER JOIN Merchandise m
ON u.MerchandiseId = m.MerchandiseId  OR i.ToMerchantId = m.MerchandiseId
WHERE i.ToCompanyId = 10 OR i.FromCompanyId = 10
GROUP BY m.MerchandiseId, m.MerchandiseName

I've tried some ways that I found in stackoverflow, but I still not manage to do it. Thousand of thanks if anyone would help on this.

FYI, I just solve my issue by using the subquery

SELECT m.MerchandiseId, m.MerchandiseName, (SELECT TOP 1 e.InboxMessage FROM Inbox e WHERE (e.ToMerchantId = m.MerchandiseId AND e.FromCompanyId = 10) OR (ToCompanyId = 10 AND FromMerchantId = m.MerchandiseId) ORDER BY e.CreatedBy ASC) AS InboxMessage, createdAt =  MAX(i.CreatedAt) FROM Inbox i
INNER JOIN [User] u
ON i.FromUserId = u.UserId
LEFT OUTER JOIN Merchandise m
ON u.MerchandiseId = m.MerchandiseId  OR i.ToMerchantId = m.MerchandiseId
WHERE i.ToCompanyId = 10 OR i.FromCompanyId = 10
GROUP BY m.MerchandiseId, m.MerchandiseName

Here's the code that I worked on


Oracle golden gate to BigQuery Im trying to setting up the Golden gate to sync the data to BigQuery. When I start pushing the initial load, my extractor exported all the data and even from the replicat stats Im able to see the records are received but its not pushed to Bigquery.

Extractor

GGSCI (bhuvi-oracle) 12> info extract load1

EXTRACT    LOAD1     Last Started 2020-05-07 16:52   Status STOPPED
Checkpoint Lag       Not Available
Log Read Checkpoint  Table HR.TEST
                     2020-05-07 16:52:41  Record 32768
Task                 SOURCEISTABLE

Replicat stats

GGSCI (bhuvi-1) 1> stats bq

Sending STATS request to REPLICAT BQ ...
gg.handlerlist=bigquery

Start of Statistics at 2020-05-07 16:53:33.

Replicating from HR.TEST to bhuvi_orcl.test:

*** Total statistics since 2020-05-07 16:52:42 ***
        Total inserts                                  32768.00
        Total updates                                      0.00
        Total deletes                                      0.00
        Total discards                                     0.00
        Total operations                               32768.00

Log file on BigData Golen Gate:

2020-05-07T16:59:04.895+0000  INFO    OGG-00987  Oracle GoldenGate Command Interpreter:  GGSCI command (root): start bq.
2020-05-07T16:59:04.904+0000  INFO    OGG-00963  Oracle GoldenGate Manager, mgr.prm:  Command received from GGSCI on host [10.18.0.61]:42130 (START REPLICAT BQ ).
2020-05-07T16:59:04.905+0000  INFO    OGG-00975  Oracle GoldenGate Manager, mgr.prm:  REPLICAT BQ starting.
2020-05-07T16:59:04.964+0000  INFO    OGG-00995  Oracle GoldenGate Delivery, bq.prm:  REPLICAT BQ starting.
2020-05-07T16:59:04.967+0000  INFO    OGG-03059  Oracle GoldenGate Delivery, bq.prm:  Operating system character set identified as UTF-8.
2020-05-07T16:59:04.967+0000  INFO    OGG-02695  Oracle GoldenGate Delivery, bq.prm:  ANSI SQL parameter syntax is used for parameter parsing.
2020-05-07T16:59:05.173+0000  INFO    OGG-15052  Oracle GoldenGate Delivery, bq.prm:  Using Java class path: .:ggjava/ggjava.jar:ggjava/resources/lib/optional/log4j-api-2.9.1.jar:ggjava/resources/lib/optional/log4j-core-2.9.1.jar:ggjava/resources/lib/optional/log4j-slf4j-impl-2.9.1.jar.
2020-05-07T16:59:10.243+0000  INFO    OGG-01815  Oracle GoldenGate Delivery, bq.prm:  Virtual Memory Facilities for: COM
                                  anon alloc: mmap(MAP_ANON)  anon free: munmap
                                  file alloc: mmap(MAP_SHARED)  file free: munmap
                                  target directories:
                                  /opt/ogg/dirtmp.
2020-05-07T16:59:10.243+0000  INFO    OGG-00996  Oracle GoldenGate Delivery, bq.prm:  REPLICAT BQ started.
2020-05-07T16:59:10.251+0000  INFO    OGG-02243  Oracle GoldenGate Delivery, bq.prm:  Opened trail file /opt/ogg/dirdat/oo000000 at 2020-05-07 16:59:10.249063.
2020-05-07T16:59:10.251+0000  INFO    OGG-03506  Oracle GoldenGate Delivery, bq.prm:  The source database character set, as determined from the trail file, is UTF-8.
2020-05-07T16:59:10.253+0000  INFO    OGG-06505  Oracle GoldenGate Delivery, bq.prm:  MAP resolved (entry HR.TEST): MAP "HR"."TEST", TARGET bhuvi_orcl.test.
2020-05-07T16:59:10.253+0000  INFO    OGG-02756  Oracle GoldenGate Delivery, bq.prm:  The definition for table HR.TEST is obtained from the trail file.
2020-05-07T16:59:10.253+0000  INFO    OGG-06511  Oracle GoldenGate Delivery, bq.prm:  Using following columns in default map by name: ID.
2020-05-07T16:59:10.253+0000  INFO    OGG-06510  Oracle GoldenGate Delivery, bq.prm:  Using the following key columns for target table bhuvi_orcl.test: ID.

I have figured out what's wrong in this, actually nothing wrong. The glitch here is the mapping.

I was mapped my table names in prm file like below.

MAP HR.TEST, TARGET bqtest.TEST

I was thinking in bqtest dataset, I can see the Test table, But its not true. Goldengate will convert your mapping as bqtest_test. This is the actual table name. So you can see that this table got created automatically.


Get the underlying foreign key constrains based upon a view I have the following relations:

CREATE TABLE cities (
    city VARCHAR (80) PRIMARY KEY,
    LOCATION point
);

CREATE TABLE weather (
    city VARCHAR (80) REFERENCES cities (city),
    temp_lo INT,
    temp_hi INT,
    prcp REAL,
    DATE DATE
);

CREATE VIEW test AS SELECT
    *
FROM
    cities;

Now I would like to utilize the test view or any other view in a GUI which simply presents an editable table. This is possible with the recently introduced automatically updatable views. I want the table to contain drop-downs containing all possible values every time there is a column with constrains on it, like the column with a foreign key in this case.

How can this be achieved? I know that I could utilize enums and I do that today, but on Amazon RDS they are unreasonably hard to modify.

I had an idea of (ab)using the "EXPLAIN SELECT" query for each column and check which table and column is being queried.

You can check which tables are used by a view using this statement:

select vtu.table_schema,
       vtu.table_name
from information_schema.view_table_usage vtu
  join pg_class cl on cl.oid = (quote_ident(vtu.table_schema)||'.'||quote_ident(vtu.table_name))::regclass
where (view_schema, view_name) = ('public', 'test');

If that returns only a single table, you can query the system for foreign keys referencing that (single) table.


Count in an other table and show only theese records in query which has more than they used in other table I have for example 2 tables:

used(us_id, code_id);
codes(code_id, amount)

I want something like this:

If for example I have 5 eggs, and egg code is 1, then if the used table have reached 5 of ID 1 then in a from, the egg won't show up as choseable. (listbox, which get it's source from codes table)

I'm using Mysql server and Access connected by odbc.

If I get it right something like:

select c.code_id
from codes c
join used u
    on c.code_id = u.code_id
group by c.code_id, c.amount
having count(1) < c.amount;

should do (Not sure why MySQL requires c.amount in the group by clause for it to be used in having)

If you want to show the remaining amount:

select c.code_id, c.amount - count(1) as remains
from codes c
join used u
    on c.code_id = u.code_id
group by c.code_id, c.amount
having count(1) < c.amount

I assumed here that amount is functionally dependent of code_id. Another option is to add a "fake" aggregate to c.amount:

select c.code_id, max(c.amount) - count(1) as remains
from codes c
join used u
    on c.code_id = u.code_id
group by c.code_id
having count(1) < max(c.amount);

There is only one unique amount per code_id so the purpose of max is to "legalize" the group by.

SQL92 demanded that all columns in the select beside aggregates where included in the group by clause. SQL99 loosened this restriction and only requires that all columns in the select clause is functionally dependent of the group by.

I haven't tested it my self, but I hear that the latest version of MySQL (or was it MariaDB?) implements it this way. I've also heard that recent versions of postgreSQL does it that way.


Maintaining a production big table I have a very large table: Table1 in a production environment which I need to alter a column in.

What's the best preferred way to do so without down time?

Option 1:

CREATE TABLE Table1_new LIKE Table1;
INSERT INTO Table1_new SELECT * FROM Table1;
ALERT TABLE Table1_new ADD COLUMN a;

ALERT TABLE Table1 RENAME Table1_bu;
ALERT TABLE Table1_new RENAME Table1;

###And then
INSERT IGNORE INTO Table1 SELECT ... FROM Table1 WHERE id > ..;

MariaDB: 5.6.44

InnoDB: 5.6.44

CREATE TABLE Table1_new LIKE Table1;
INSERT INTO Table1_new SELECT * FROM Table1;
ALERT TABLE Table1_new ADD COLUMN a;         --slow because of big table

is not optimal and has a problem.  Better:

CREATE TABLE Table1_new LIKE Table1;
ALERT TABLE Table1_new ADD COLUMN a;          -- fast because table empty
INSERT INTO Table1_new SELECT *, NULL FROM Table1; -- needs value for `a`

The problem is -- What if Table1 is being written to?  Such DML would be lost.

ALERT TABLE Table1 RENAME Table1_bu;
ALERT TABLE Table1_new RENAME Table1;

Should be done as a single statement (atomically):

RENAME TABLE Table1 TO Table1_bu,
             Table1_new TO Table1;

Plan A:  (Your code, after fixes):  Viable only if no modifications will happen.

Plan B:  pt-online-schema-change (assuming you have not Triggers on the table).

Plan C:  oak... -- No; it seems not tok be maintained

Plan D:  Upgrade to MySQL 8.0 (perhaps MariaDB 10.3) so you can get ALTER TABLE .. ADD COLUMN .. ALGORITHM=INSTANT


Grant privileges to all users on a single table Is it possible to grant privileges (such as SELECT, INSERT, UPDATE, etc) to all the users for a specific table at the same time?

Here is what I tried but it didn't work out...

GRANT SELECT ON customer TO * @'localhost';

There is a way to do it in previous versions of MySQL referring to anonymous users with the following query, however I think it does not work for your version of MySQL.

GRANT SELECT ON database_name.CUSTOMER TO '' @ '%';

I recommend you create a ROLE and assign this role to all users, after that you can assign a permission to that ROLE and it will have an effect on all the users to whom it is assigned. Administration in this way is much easier. For example:

CREATE ROLE r1;
GRANT r1 TO 'user1' @ '%', 'user2' @ '%', 'user3' @ '%' .....;
GRANT SELECT, INSERT, UPDATE ON database_name.CUSTOMER TO r1;


Second Instance Installation of PostgreSQL? Relating to original question posted on Stack Overflow here.

Interested in distributing a copy of PostgreSQL with a C# app (for Windows). However, trying to understand how the following scenario should be handled:

PostgreSQL already installed on the user's system

From my understanding, should be possible to install a 2nd instance of PostgreSQL to run at the same time on the same computer system (as a different service, bound to a different TCP port #?). However, the closest answer for how this would be done was found here, which does not really provide a lot of detail.

So far, in my test environment (VM), I have one instance of Ver. 12 PostgreSQL installed in the default directory C:\Program Files\PostgreSQL\12.

Am I correct to understand that I should copy all of the contens of the C:\Program Files\PostgreSQL\12 directory and copy/paste to a different directory (e.g. C:\Program Files\PostgreSQL\Test12?

Then, run pg_ctl register, but with what options?

And, then after, I should run pg_ctl start?

Are there any considerations that should be made before all of this is done?

You don't need the binaries two times, you only need to create a second data directory (aka "cluster").

The following creates a new data directory under c:\ProgramData\pgdata2. I highly recommend to not put the data directory under c:\Program Files (that's one of the reasons I stopped using the EntpriseDB installer because it always suggested that)

"C:\Program Files\PostgreSQL\12\bin\initdb" -D "c:\ProgramData\pgdata2"

Then edit c:\ProgramData\pgdata2\postgresql.conf and change the value for port, e.g.

port = 5335

Then you can register the new Windows service and provide the data directory during registration:

pg_ctl register -N other-postgres -D "c:\ProgramData\pgdata2"

Once the service is registered, you can start it using net start

net start other-postgres


Does the order of columns has a significant effect on INSERT? Do we experience a noticeable difference in performance for these two queries?

INSERT INTO table (col1, col2, col3, col4, col5) ...

and

INSERT INTO table (col5, col3, col1, col2, col4) ...

Do we need to arrange INSERT columns according to the order of columns in the table?

No, you don't need to gave the columns in the same order. Not least, table order may not reflect actual on-disk order (this is 100% true for SQL Server, and I'm sure MySQL is the same)

Unless your OCD itch needs scratched


Does adherence to the DRY principle justify dynamic SQL? Suppose I have two queries that will be frequently run against my database:

SELECT
  UserID,
  UserName,
  UserGender
FROM Users
WHERE UserID = @User

SELECT
  UserID,
  UserName,
  UserGender
FROM Users
WHERE UserName LIKE @Name + '%

Should they be in two separate stored procedures, or just a single one which creates the statements dynamically using sp_executesql?

If they are in two stored procedures, then I will need to modify both procedures if I ever want to add or remove a column in the SELECT statement. And if I use dynamic SQL then presumably I am sacrificing a small amount of performance.

Is this a case where maintainability and adherence to the DRY principle (don't repeat yourself) by using dynamic SQL would take precedence over the performance gain of a stored procedure?

Strict DRY doesn't really apply to databases

I've seen DRY is used as justification to create views for "re-use". Then we have views joining views etc and piss poor performance.

Generally, similar queries will be used in different ways. One may have an aggregate, one may not, filters will be different (as above). The similarity doesn't justify dynamic SQL not does it justify a view.

In your specific case above, I'd consider an IF statement to capture the difference, especially if the code was issued by the same search page or form: you have similarity based on usage, not just the same columns and table being used.

Also, you have security to consider.

Dynamic SQL require EXECUTE AS (escalation of rights) or permissions on the base tables. Using a plain stored procedure does not require such permissions.

You may also have different client code: we have stored procedures per client (schemas control permissions). Do you want to GRANT select rights on all your tables to all clients?


Where can I check my permissions as SQL Server user? Everytime I have to do a Restore from a backup, I have to log out from my personal user account from the SQL Server instanc and log back in using our main group user account, which I manage it too as currently no one else is using the sql server until the next DBA will be hired!
So  how can I compare our privilages and see the permissions allowed for this account so I can ask the IT department to grant me the same.

Thanks for any advice

You're asking the wrong question.  Instead of wanting to mimic the permissions of another server principal, you should be granted the permissions and membership that you need to do in order to fulfill your job requirements (that goes for server logins that applications use as well).

So in other words, instead of copying the permissions from another login, just find out what permissions you need to do your task (sounds like a RESTORE).  Reference:  RESTORE:

  If the database being restored does not exist, the user must have
  CREATE DATABASE permissions to be able to execute RESTORE. If the
  database exists, RESTORE permissions default to members of the
  sysadmin and dbcreator fixed server roles and the owner (dbo) of the
  database (for the FROM DATABASE_SNAPSHOT option, the database always
  exists).

  RESTORE permissions are given to roles in which membership information
  is always readily available to the server. Because fixed database role
  membership can be checked only when the database is accessible and
  undamaged, which is not always the case when RESTORE is executed,
  members of the db_owner fixed database role do not have RESTORE
  permissions.


I want to import tables from one sql server to another server using SSIS with loop I want to import table via SSIS, This package will execute like first it'll import first 10 tables once its imported then it'll start to import 10-20 tables, like a loop.

Can anyone explain me how can I achieve this?

Maybe you just don't even need a foreach loop and can use the Rows per batch setting of the destination :

But, well, I don't know how is your package so I'm not sure it fits...


How can I troubleshoot TNS connection issues? Do I need TRCROUTE executable? I am trying to connect to a particular TNS name; in my tnsnames.ora file I can see that it is defined as being a TCP connection to foo-scan on port 39000. I can tnsping this name OK.

I can successfully telnet foo-scan 39000 but when I try to connect in e.g. sqldeveloper I get the error ora-12545: connect failed because target host or object does not exist

According to google it is likely that the listener on foo-scan is actually trying to forward my client to bar-vip, which no doubt I cannot connect to. Also according to google there is a utility called trcroute that will debug this issue and show me exactly where my client is trying to connect and what step is actually failing. But I cannot find this utility anywhere.

ORA-12545 is typically caused by a scenario you described. The client connects to an address (e.g. SCAN address of a RAC) where its request is forwarded to another address (e.g. VIP address of a RAC node) which the client is unable to reach due to something (e.g. missing name resolution).

In this example situation trcroute would have been no help at all. It does not provide information on this even on a successful attempt and it does not care at all about services (just like tnsping), I can give it a non-existent service and it will still report success.

[oracle@o7ca2 ~]$ tnsping "(DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = o7ca-scan.balazs.vm)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = HYPER_O7CA)))"

TNS Ping Utility for Linux: Version 19.0.0.0.0 - Production on 22-APR-2020 10:48:43

Copyright (c) 1997, 2019, Oracle.  All rights reserved.

Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = o7ca-scan.balazs.vm)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = HYPER_O7CA)))
OK (10 msec)
[oracle@o7ca2 ~]$ trcroute "(DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = o7ca-scan.balazs.vm)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = HYPER_O7CA)))"

Trace Route Utility for Linux: Version 19.0.0.0.0 - Production on 22-APR-2020 10:48:51

Copyright (c) 1995, 2019, Oracle.  All rights reserved.

Route of TrcRoute:
------------------

Node: Client            Time and address of entry into node:
-------------------------------------------------------------
22-APR-2020 10:48:51 DESCRIPTION =  ADDRESS =  PROTOCOL = TCP  HOST = o7ca-scan.balazs.vm  PORT = 1521    CONNECT_DATA =  SERVER = DEDICATED   SERVICE_NAME = HYPER_O7CA

Node: Server            Time and address of entry into node:
-------------------------------------------------------------
22-APR-2020 10:48:51 ADDRESS= PROTOCOL=TCP  HOST=o7ca-scan.balazs.vm  PORT=1521

[oracle@o7ca2 ~]$ lsnrctl services listener_scan1

LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 22-APR-2020 10:49:06

Copyright (c) 1991, 2019, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER_SCAN1)))
Services Summary...
Service "HYPERXDB" has 4 instance(s).
  Instance "HYPER1", status READY, has 1 handler(s) for this service...
    Handler(s):
      "D000" established:0 refused:0 current:0 max:1022 state:ready
         DISPATCHER <machine: o7ca1, pid: 4049>
         (ADDRESS=(PROTOCOL=tcp)(HOST=o7ca1.balazs.vm)(PORT=13707))
  Instance "HYPER2", status READY, has 1 handler(s) for this service...
    Handler(s):
      "D000" established:0 refused:0 current:0 max:1022 state:ready
         DISPATCHER <machine: o7ca2, pid: 6754>
         (ADDRESS=(PROTOCOL=tcp)(HOST=o7ca2.balazs.vm)(PORT=24163))
  Instance "HYPER3", status READY, has 1 handler(s) for this service...
    Handler(s):
      "D000" established:0 refused:0 current:0 max:1022 state:ready
         DISPATCHER <machine: o7ca3, pid: 6526>
         (ADDRESS=(PROTOCOL=tcp)(HOST=o7ca3.balazs.vm)(PORT=15639))
  Instance "HYPER4", status READY, has 1 handler(s) for this service...
    Handler(s):
      "D000" established:0 refused:0 current:0 max:1022 state:ready
         DISPATCHER <machine: o7ca4, pid: 6593>
         (ADDRESS=(PROTOCOL=tcp)(HOST=o7ca4.balazs.vm)(PORT=22765))
Service "HYPER_O7CA" has 4 instance(s).
  Instance "HYPER1", status READY, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:0 refused:0 state:ready
         REMOTE SERVER
         (ADDRESS=(PROTOCOL=TCP)(HOST=192.168.1.25)(PORT=1521))
  Instance "HYPER2", status READY, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:0 refused:0 state:ready
         REMOTE SERVER
         (ADDRESS=(PROTOCOL=TCP)(HOST=192.168.1.26)(PORT=1521))
  Instance "HYPER3", status READY, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:0 refused:0 state:ready
         REMOTE SERVER
         (ADDRESS=(PROTOCOL=TCP)(HOST=192.168.1.27)(PORT=1521))
  Instance "HYPER4", status READY, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:0 refused:0 state:ready
         REMOTE SERVER
         (ADDRESS=(PROTOCOL=TCP)(HOST=192.168.1.28)(PORT=1521))
Service "hyper" has 4 instance(s).
  Instance "HYPER1", status READY, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:0 refused:0 state:ready
         REMOTE SERVER
         (ADDRESS=(PROTOCOL=TCP)(HOST=192.168.1.25)(PORT=1521))
  Instance "HYPER2", status READY, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:0 refused:0 state:ready
         REMOTE SERVER
         (ADDRESS=(PROTOCOL=TCP)(HOST=192.168.1.26)(PORT=1521))
  Instance "HYPER3", status READY, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:0 refused:0 state:ready
         REMOTE SERVER
         (ADDRESS=(PROTOCOL=TCP)(HOST=192.168.1.27)(PORT=1521))
  Instance "HYPER4", status READY, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:0 refused:0 state:ready
         REMOTE SERVER
         (ADDRESS=(PROTOCOL=TCP)(HOST=192.168.1.28)(PORT=1521))
The command completed successfully
[oracle@o7ca2 ~]$ trcroute "(DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = o7ca-scan.balazs.vm)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = XXX)))"

Trace Route Utility for Linux: Version 19.0.0.0.0 - Production on 22-APR-2020 10:51:39

Copyright (c) 1995, 2019, Oracle.  All rights reserved.

Route of TrcRoute:
------------------

Node: Client            Time and address of entry into node:
-------------------------------------------------------------
22-APR-2020 10:51:39 DESCRIPTION =  ADDRESS =  PROTOCOL = TCP  HOST = o7ca-scan.balazs.vm  PORT = 1521    CONNECT_DATA =  SERVER = DEDICATED   SERVICE_NAME = XXX

Node: Server            Time and address of entry into node:
-------------------------------------------------------------
22-APR-2020 10:51:39 ADDRESS= PROTOCOL=TCP  HOST=o7ca-scan.balazs.vm  PORT=1521

[oracle@o7ca2 ~]$

Actually this is all that happens in the listener on a trcroute attempt:

22-APR-2020 10:45:21 * 12508
TNS-12508: TNS:listener could not resolve the COMMAND given

Using the TRCROUTE Utility to Test Connectivity from the Client

  The listener receives and processes the TRCROUTE connect packet. It
  returns the information to the client by putting it into a refuse
  packet.


Efficiency : Query Over Multiple Colomns vs Query over PK What would be better approach or rather in what situation should I select which approach, in below explained situation

    **Table - Wishlist**
    Id PK
    Owner FK
    Recipient FK
    Title
    Description
    Url

Search Rules
Return wishlist item with specific 'Id', if Logged in user's username matches Owner or Recipient.

Approach 1 -
Result = Select * From Wishlist where id == %id% && ( Owner == username || Receipent == username )

This will return only one record if the user is allowed to access it.

Approach 2 -
Result = Select * From Wishlist where id == %id%
if(Result.owner == username || Result.Receipent == username){
  return Result
}

This will return result irrespective of whether the user is an owner or not.
However, we're looping over single column as compared to multiple columns.

Which approach should be preferred and under which condition?

PS - SQL syntax is not accurate, and is only meant as an example.

Since no specific database is given, the below advice is general:

Most database engines try to limit the number of rows they have to look at as quickly as possible. Since both of your queries involve the primary key of you table, that will be used as the first basis for limiting the rows.

Obviously, a small amount of additional work is involved in the further limiting required in your first query, to only include the row in the result set if the Owner or Recipient match a specific value. If the check in your IF statement is done within the database (before, say, returning data to a calling application), then the performance is probably similar.

If the IF check is being done in an application, then you're sending data that may not be needed to the application. If it's as easy to check the data in the database as it is in the application, then I would generally filter on the database side, to minimize the amount of unnecessary data that needs to be sent to the application.

For one row, the difference is probably negligible. However, if you were trying to locate all rows in Wishlist that have the current user as Owner or Recipient and were added in the past 3 weeks, the difference would probably be very big.

Also: In most databases, applying an IF check like that outside the query requires that the data be stored in a staging table of some sort (ideally a temporary table) so a secondary query to filter using the additional criteria can be run, or by using a cursor to check the data row by row.

There are cases where using an intermediate table of some sort can greatly benefit a query. The DB engine may need much longer to figure out the best execution plan for very complex query, and may in fact have to go with a "best so far" plan sue to time constraints on the planning process. This can result in a sub-optimal query plan. Using a two-step process, where each query involved is simpler, can allow the best plan to be located more easily, and thus provide results faster than a single query. Note, however, that this will generally involve writing the intermediate results to disk, and writing to disk is one of the most costly operations most DBMSes perform.

Using some form of cursor, however, can be even worse. All SQL-based DBMSes I know of run best when treating rows as a "bag" of data, and processing them in bulk. In almost every case I can imagine, using a query that can apply a condition to all possible rows at once will have better results than checking the rows individually in a loop.

There are cases where that's appropriate. DBMSes so some thing better than others. Complex string processing is generally considered to be something most databases systems do relatively poorly. If you need to match on a complicated regular expression, and your DBMS doesn't have any native tools to handle regular expressions (or any easily-used add-ins that operate at native speeds), then sending the possible matches to your application and filtering based on the complex regular expression may be much faster (and easier) than cobbling together a series of comparable checks in a WHERE clause (see above notes on very complicated queries).

Another answer notes that MS SQL Server(and possibly other DBMSes) have some sort of row-level security. In the MS SQL Server case, note that this capability restricts access based on the SQL Server logins and users accessing the server. If you are looking at direct querying of the database by users, or if every user in your system has their own unique database-level login, then this could be of use to you. If, however, your application has its own internal users table, and needs to use that to control access (as would be true of most customer-facing applications, like the amazon.com website), then you probably need to manage that yourself.


Algorithm used to perform offset/limit over a union all result set I have two result sets:

rs1:

     id       name
   serial     text
________________
     1        Nick
....................
  1233112     Pete

rs2:

     id       name
   serial     text
________________
  123121      Mike
....................
 221233112   Junior

If we write a query:

SELECT *
FROM
(

    SELECT *
    FROM rs1

    UNION ALL

    SELECT *
    FROM rs2

) as rs
OFFSET 100000 LIMIT 10;

How the result of the query computed? What algorithm is going to be used?

I believe that the server performs lazy evaluation in the sense of not loading the entire union, iterating over it and then returning the required result.

I would be glad if you described algorithms using in other SQL servers as an addition.

UPD: I'm quite new in database internals and don't know if it's possible to ask the sql-server itself about the exectuin plan of the query.

Here is a query plan:

"Limit  (cost=77.11..77.88 rows=10 width=522)"
"  ->  Append  (cost=0.00..140.18 rows=1818 width=522)"
"        ->  Seq Scan on tbl  (cost=0.00..70.09 rows=909 width=522)"
"        ->  Seq Scan on tbl tbl_1  (cost=0.00..70.09 rows=909 width=522)"

The saqme query with order by id:

"Limit  (cost=33.16..36.42 rows=10 width=522)"
"  ->  Merge Append  (cost=0.56..593.16 rows=1818 width=522)"
"        Sort Key: tbl.id"
"        ->  Index Scan Backward using pk_tabl on tbl (cost=0.28..285.21 rows=909 width=522)"
"        ->  Index Scan Backward using pk_tbl on tbl tbl_1  (cost=0.28..285.21 rows=909 width=522)"

When limiting clauses like OFFSET are applied depends on the query, particularly what the result is ordered by.

If you are ordering by columns that are also being used to filter the results (in a WHERE clause for instance) and are indexed then the limitation will often be applied early. The query planner will try to find just the first rows that match the search condition. Otherwise the limit is usually applied after everything else, so the server is likely to spool the results into a temporary store then sort and apply your OFFSET/LIMIT filter.

For a UNION query on its own, it depends on the brains in the query planner so you might have to look at its plan output to know (I'm not a postgres expert), and again it may depend on the rest of the query details. However the work is done (which will affect performance) the output will be the same though: the limitation will be applied to the complete result.

In the example you give the UNION query is actually providing a derived table, named rs. The limitation will definitely be applied to the whole result here so it is functionally the same as if you had done SELECT * FROM <some_table> OFFSET 100000 LIMIT 10. Because they are the same you could rewrite your example as simply:

SELECT * FROM rs1
UNION ALL
SELECT * FROM rs2
OFFSET 100000 LIMIT 10;

and the results would be the same.

One important note: you have not specified any ordering clause here. This may be because you are giving a minimal example so forgive me if I'm telling you something you already know, but without an ordering clause you should assume the output order to be arbitrary, possibly random, and likely to change between runs of the same query. Sometimes this is fine (you just want the data and you don't care about the order) but as soon as you are using things like OFFSET/LIMIT/TOP/... you do care about the order because the query after OFFSET 10000 LIMIT 10 is likely to be OFFSET 10010 LIMIT 10 and you want that to definitely want ten different rows by some ordering (not an arbitrary ten that could in theory end up being the same ten as last time). In practise the ordering of output is usually more stable than this, but it isn't always by any means so you should always provide an ordering clause if a sudden change in output order would cause undesirable behaviour.


SQL Server TempDB behaviour in large memory environment Reading over this question reminded me of a question I had a while ago.

We have a SQL Server that has 512GB of RAM, the main database is 450 GB.  We see quite a lot of action in TempDB (ok, I think it's "quite a lot of action" - it may not be!).  I installed a demo version of RamDisk Plus Server, created a 50GB ramdrive, pointed TempDB at it, and saw no improvement in performance at all.

Do writes to TempDB always result in an actual physical write to disk, or are TempDB writes cached by SQL Server for delayed write like in the Windows file system cache?

Is a ramdisk pointless in this scenario?

I know SQL Server 6.5 had support for TempDB-In-Ram, but I see that was discontinued a long time ago!

Do writes to TempDB always result in an actual physical write to disk,
  or are TempDB writes cached by SQL Server for delayed write like in
  the Windows file system cache?

Do they always? Most definitely not. Do they ever? Yes but not as a result of the typical  mechanism. Reference here is What does checkpoint do for tempdb?.

In a "well behaved" system, writes to a user database file occur on checkpoint. In a badly behaved system, writes will also occur when the lazywriter needs to flush pages from the buffer pool to make room for other pages.

  A checkpoint is only done for tempdb when the tempdb log file reaches
  70% full - this is to prevent the tempdb log from growing if at all
  possible (note that a long-running transaction can still essentially
  hold the log hostage and prevent it from clearing, just like in a user
  database).

But there is no need to flush tempdb to disk, as crash recovery is never run on tempdb, it's always recreated on startup.

  Tempdb is not recovered in the event of a crash, and so there is no
  need to force dirty tempdb pages to disk, except in the case where the
  lazywriter process (part of the buffer pool) has to make space for
  pages from other databases.

This remarkably (it was a surprise to me) is the only mechanism by which tempdb pages will be written to disk. If there is buffer pool pressure, tempdb pages may be flushed to disk. If there isn't, it should not occur.

Edit: Debatable whether "badly behaved" is an appropriate description for a user database that is writing pages outside of checkpoint. Unusual, atypical, or just not ideal maybe?

Additional edit (following comments/chat with @PaulWhite):

The glaring omission above is that temporary tables are not sole source of tempdb traffic. Quoting from Understanding Hash, Sort and Exchange Spill Events:

  Certain SQL Server query execution operations are calibrated to
  perform best by using a (somewhat) large amount of memory as
  intermediate storage. The Query Optimizer will choose a plan and
  estimate the cost based on these operators using this memory
  scratch-pad. But this is, of course, only an estimate. At execution
  the estimates may prove wrong and the plan must continue despite not
  having enough memory. In such an event, these operators spill to disk.

I had incorrectly assumed that the mechanism behind a physical write for a spill operation were exactly the same as described earlier i.e. the lazywriter forcing tempdb pages to disk as a result of pressure on the buffer pool (caused by the spill).

@PaulWhite explained where I was wrong (thanks Paul!):

  I think you are asking why physical tempdb activity occurs when a
  query exceeds its workspace memory grant, rather than just using
  tempdb-in-memory If it did that, the query would be using more memory
  than its grant, defeating the point of restricting memory grants in
  the first place.

  Spills are indeed special in writing through to
  storage. Physical tempdb activity is seen on a spill, even in the face
  of oodles of free memory and zero pressure on tempdb.

Paul also pointed me to his blog post Advanced TSQL Tuning: Why Internals Knowledge Matters which includes example scripts for demonstrating spills, for those that want to delve deeper.


I need to reference the same two columns multiple times in where clause I've got a table that stores survey question information and I'm looking to find out a count based on gender, race, etc My issue is that I have to reference the same columns multiple times in the where clause and I'm not sure the best way to do that.

example:

I need to know the count all of the people that answered yes to survey question 24 AND ALSO yes to questions 4 or 16 or 24.  I have tried messing with Or statements and IN statements but both aren't working for me.  I was looking at using a case statement in the WHERE clause but not sure what that would need to look like.  Any help or advice would be most appreciated!!

  with TheTotal as
(select count(table.name_id)over (partition by
table.other_id) as blah
where sex='M'
 AND table.date between '2019-01-01' and '2019-09-30'
 AND (table.jq_nbr=35 AND table.answer='Y')

 AND (
  (jq_nbr=4 and answer='Y')
  OR (jq_nbr=16 and answer='Y')
  OR (jq_nbr=24 and answer='Y')
      )

Having your schema would be helpful in helping with your specific query. Without the full schema, here are some pointers for how you might write a query to get your results.

Assuming you have a single dbo.SurveyResults table, where there is some ID (survey_id) that is used to identify all the results from a given user's responses, you could use logic that is something like this:

Count all the "Question 35"s that have an answer of Y, and there also exists an answer for questions 4, 16, or 24 that also has an answer of Y.

That query would look something like this:

SELECT COUNT(DISTINCT r.survey_id)
FROM dbo.SurveyResults AS r
WHERE r.jq_nbr = 35
AND r.answer = 'Y'
AND EXISTS (SELECT 1
            FROM dbo.SurveyResults AS r2
            WHERE r2.survey_id = r.survey_id
            AND r2.jq_nbr IN (4,16,24)
            AND r2.answer = 'Y'
            );

With a slightly different schema, perhaps there is a "header" table (dbo.Survey) that has has one row per user-completed survey, and another table (dbo.SurveyResults) that is a child table that has one row per question answered. With this, you can use a slightly different "plain English" description, which might generate slightly different logic:

Count all the user-completed surveys where the respondent has answered "Y" to question 35, and also answered "Y" to at least on of questions 4,16, or 24.

That query might look like this:

SELECT COUNT(s.survey_id)
FROM dbo.Survey AS s
WHERE EXISTS (SELECT 1
            FROM dbo.SurveyResults AS r1
            WHERE r1.survey_id = s.survey_id
            AND r1.jq_nbr = 32
            AND r1.answer = 'Y'
            )
AND EXISTS (SELECT 1
            FROM dbo.SurveyResults AS r2
            WHERE r2.survey_id = s.survey_id
            AND r2.jq_nbr IN (4,16,24)
            AND r2.answer = 'Y'
            );


Transaction Log backup in MSSQL's HADR I'm a resident developer who is voluntold to be a database admin of MSSQL 2016.  Please bear with me as I'm still trying to self taught the art of DB admin.
I recently setup a DR solution using the architecture (Always On High Availability Group) below and things are working well for me.  All databases are synchronizing.

In my primary data center, I have two nodes (active & passive) that share the same disk (Log disk, data, ect..).  Currently I see the following on my active node, they're were setup there by my predecessors.

All databases are all in full recovery mode.
It has a Full Backup maintenance plan where a full DB backup is done at 12am daily.
It has a Log Backup maintenance plan where transaction logs are backed up every 5 minutes.

I understand that Always On High Availability Group use transaction log to sync data between the Primary Data node and the Disaster Recovery Node.  Can I disable the transaction log backup in #3?
Do you see any issue if I do not backup my transaction log at a 5 minutes interval?  Can I use my DR node as a "backup" in case something went wrong instead of restoring my database using our last daily backup and then procedurally  restore the entire transaction log backup from that day?

Can I use my DR node as a "backup" in case something went wrong instead of restoring my database using our last daily backup and then procedurally restore the entire transaction log backup from that day?

No.  All changes, including ones you might like to undo, are automatically written to your AG secondaries.  You should take log backups on exactly one replica and all the backups should go to a single location.  You can configure a Backup Preference to help make sure that 1) backups are being taken, and 2) they are taken on your preferred node if it is available.


Computer restarted and Postgres 9.2 service cannot be started? My firewall is down and when I tried to start the Postgres service it gave me an error that said: the service started and then stopped. Some services stop automatically if they are not in use by other services or programs.

I need to start postgres for pgadmin and when I tried to connect to pgadmin it gave me an error that I've seen before but none of those conditions of the error is true because it worked right before my computer was automatically restarted.

I honestly didn't want to change my conf file and simply do it from the pgadmin postgres.conf gui.

The console also gave me an error about a line that had debug assertions =, which I put a hash mark to the left of which got rid of the error.

When I try to run postgres from the command prompt it gives me that postgresql.conf contains errors? But what errors would they be?

I also tried running from the console a couple of different ways and one of the errors I got was pg_ctl: could not start service "postgresql-9.2": error code 1063

Start by commenting out the offending line in your postgresql.conf and retry.  It is possible that a change made some time ago caused this problem and you are only now seeing it because a reload operation or similar failed, and so PostgreSQL was running on old configuration.  If that works address what you have on that line (maybe get rid of the quotes?)
If that doesn't work, take a look elsewhere in the file.  Maybe you have an unterminated quote somewhere?

It really looks to me like you have a bad postgresql.conf file and that's why it isn't starting.


Conditionally sum a column based on a mod result I have the following query:

SELECT y.datamov,
       y.n_conta,
       y.sinal,
       y.valor,
       y.qtde,
       a.nome,
       '',
       0,
       a.lrdarc
FROM (
    SELECT k.datamov,
        k.n_conta,
        k.sinal,
        k.qtde,
        sum(k.total) AS valor
    FROM (
        SELECT DATE (b.dt_normal) AS datamov,
            b.historico,
            a.obs,
            a.total,
            a.qtde,
            b.sinal,
            c.nome,
            a.n_conta
        FROM caixa.rtitens a
        LEFT JOIN caixa.rtlan b ON (b.numero = a.n_lan)
        LEFT JOIN caixa.rtctares c ON (c.numero = a.n_conta)
        WHERE DATE (b.dt_normal) BETWEEN DATE ('2010-02-13') AND DATE ('2010-02-20')
            AND c.lrdarc != 0
            AND b.sinal = 'E'
        ORDER BY 1, 7, 5
        ) k
    GROUP BY 1, 2, 3, 4
    ORDER BY 1, 2, 3
    ) y
LEFT JOIN caixa.rtctares a ON (a.numero = y.n_conta)
ORDER BY 1, 2, 3

Which returns results like these:

My problem is: I need to conditionally "merge" two rows into one when the mod of the a.lrdarc is equal to 1. If this condition is not filled (ie the mod is 0), then it should show the column without doing anything. I've been trying to do this for a while and until now I have no idea on how to solve this. What can I do?

EDIT: I want something like this:

As my boss stated, all I had to do was edit the inner statements instead of trying to manipulate the results on the outer query. We managed to do what I needed with this:

SELECT  y.datamov,
    y.n_conta,
    y.sinal,
    y.valor,
    a.nome,
    '',
    0,
    y.lrdarc,
    y.quantidade
FROM (
    SELECT k.datamov,
        k.n_conta,
        k.sinal,
        sum(k.qtde_valida) as quantidade,
        k.grupo as lrdarc,
        sum(k.total) AS valor
    FROM (
        SELECT DATE (b.dt_normal) AS datamov,
               b.historico,
               a.obs,
               a.total,
               b.sinal,
               c.nome,
               a.n_conta,
               c.lrdarc,
               case when mod(c.lrdarc, 2) !=0 then
                    a.qtde
               else
                    0
               end as qtde_valida,
               trunc(c.lrdarc / 10) as grupo
        FROM caixa.rtitens a
        LEFT JOIN caixa.rtlan b ON (b.numero = a.n_lan)
        LEFT JOIN caixa.rtctares c ON (c.numero = a.n_conta)
        WHERE DATE (b.dt_normal) BETWEEN DATE ('2010-02-13') AND DATE ('2010-02-20')
        AND c.lrdarc != 0 AND b.sinal = 'E'
        ORDER BY 1, 7, 5
        ) k
    GROUP BY 1, 2, 3, 5
    ORDER BY 1, 2, 3
    ) y
LEFT JOIN caixa.rtctares a ON (a.numero = y.n_conta)
ORDER BY 1, 2, 3

Note that the select from the most inner query, where we've added this:

...
c.lrdarc,
case when mod(c.lrdarc, 2) !=0 then
     a.qtde
else
     0
end as qtde_valida,
trunc(c.lrdarc / 10) as grupo

This solved the problem.