Importing large file while keeping table available for other operations We need to import millions of rows in a quite big table.

Say like 5 million rows into a table with more than 1 billion rows.

After preprocessing the import file we will have update operations and insert operations.

I have a couple of questions:

How the table will be affected? During this import (updates and inserts) what could happen to other processes trying to read or write data to this table?
Definetely we should be using bulk operations right? Insert and updates. I know bulk insert functionality as being something extremely fast compared to normal inserts. Does SQL Server 2008 provides something similar for UPDATES?
Which techniques or strategies should we consider in order to make this process as fast as possible and at the same time keep the underlying table available for other processes to read and write to it while importing?

My ideas are:

All the row to be imported will be of the same UserId, so having this table partitioned by userid will improve the performance right? As all the other queries related to other users which doesnt belong to the affected partition will not be affected... Is this correct?

What about indexes? Should I be droping them and recreating on the affected partition?

I will continue reading and testing on this topic. If anyone has experience on some of the above questions would be great or could point me to some related information would be great!

Holy cow, you've got a lot of questions in here:

  How the table will be affected? During this import (updates and inserts) what could happen to other processes trying to read or write data to this table?

With careful index design and partitioning, you can get away with minimal disruption to other queries.  Discussing how to implement partitioning is a little beyond what we can talk through here - check out Louis Davidson's book on SQL 2008 database design.  It covers concurrency pretty well.

  I know bulk insert functionality as being something extremely fast compared to normal inserts. Does SQL Server 2008 provides something similar for UPDATES?

Not really.  You can't update in bulk because you could be changing rows all over the table.  This is where index design comes in (as explained in Louis's book).

  All the row to be imported will be of the same UserId, so having this table partitioned by userid will improve the performance right? As all the other queries related to other users which doesnt belong to the affected partition will not be affected... Is this correct?

Only if your indexes are also partitioned by UserId, and that's rarely the case.  Usually you'd want indexes arranged in a different manner - but again, that's covered in the book.


Does PostgreSQL allow multiple grantees in GRANT commands? Looking at the PostgreSQL docs it seems that the GRANT command only grants to a single user or role. In fact it doesn't seem to be PostgreSQL alone.

Isn't possible to do something like GRANT SELECT UPDATE ON atable TO mary, bob, sue?

It looks like a major omission to me, or is there some reason, historical or practical for it?

Yes it does, but that will turn into a maintenance headache down the road.

Read http://en.wikipedia.org/wiki/Role-based_access_control for justification for assigning rights to roles over individual users.

Simply create a role and assign users to it, and then grant privileges to that role.

create role some_group;

grant some_group to mary, bob, sue;

grant select, update on a_table to some_group;


How to Restore data from binary log How can I restore my files and photos from binary files?

Do you know from which binlog files you need to restore and how many binlog files there are?

Using mysqlbinlog utility we can view the binlog file content.

If it's a single file you can recover using:

mysqlbinlog /var/lib/mysql-bin.000016 | mysql –uroot –pReset123

If it's multiple files then just extract all content to one .sql file and directly restore it:

mysqlbinlog /var/lib/mysql-bin.000016 > /logs/allbinlog.sql

To append second binlog content to allbinlof.sql file use below command:

mysqlbinlog /var/lib/mysql-bin.000016 >> /logs/allbinlog.sql

You can also exclude certain statements using option of mysqlbinlog. Try this below for options:

mysqlbinlog --help


ORA-02436: date or system variable wrongly specified in CHECK constraint This is the table I have created:

 CREATE TABLE Ticket_Type
 (
  t_type_id  NUMBER(1) PRIMARY KEY,
  CONSTRAINT check_t_type_id CHECK(t_type_id  > 0),
  t_type VARCHAR(20) NOT NULL,
  t_type_price NUMBER(4,2) NOT NULL,
  CONSTRAINT check_t_type_price CHECK(t_type_price > 0),
  t_type_start_date DATE NOT NULL,
  t_type_end_date DATE,
  CONSTRAINT check_t_type_end_date CHECK((t_type_end_date = NULL) OR (t_type_end_date >= t_type_start_date)),
  CONSTRAINT unique_t_type_t_type_start_date UNIQUE(t_type,t_type_start_date)
  );

I tried to create this table but I was getting the following error message:

Error report -
ORA-02436: date or system variable wrongly specified in CHECK constraint
02436. 00000 -  "date or system variable wrongly specified in CHECK constraint"
*Cause:    An attempt was made to use a date constant or system variable,
           such as USER, in a check constraint that was not completely
           specified in a CREATE TABLE or ALTER TABLE statement.  For
           example, a date was specified without the century.
*Action:   Completely specify the date constant or system variable.
           Setting the event 10149 allows constraints like "a1 > '10-MAY-96'",
           which a bug permitted to be created before version 8.

I'm unable to figure out regarding where is the mistake I'm making.

I'm using Oracle version 12c SQL Developer.

Try (for the CHECK constraint) ...

t_type_end_date IS NULL

Also, when using your DDL code in apex.oracle.com (Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production), we get

ORA-00972: identifier is too long

this is caused by the line

CONSTRAINT unique_t_type_t_type_start_date UNIQUE(t_type,t_type_start_date)


Merging table records across TabName for each PersonID Consider the following table,

CREATE TABLE temp (
    [TabName] VARCHAR(255),
    [PersonID] VARCHAR(255),
    [FirstName] VARCHAR(255),
    [Gender] VARCHAR(255),
    [BenefitType] VARCHAR(255),
    [HealthStatus] VARCHAR(255));

  INSERT INTO temp VALUES
    ('TAB1', 'C103','John', 'M', 'Benefit Type 1', NULL),
    ('TAB2', 'C103','John', 'M', NULL, 'Healthy'),
    ('TAB3', 'C103', 'John', 'M', NULL, 'Healthy'),
    ('TAB1', 'C104', 'Mary', 'M', 'Benefit Type 2', NULL),
    ('TAB2', 'C104', 'Mary', 'M', NULL, 'Sick'),
    ('TAB3', 'C104', 'Mary', 'M', NULL, NULL)

TabName     PersonID     FirstName    BenefitType      HealthStatus
TAB1        C103         John         Benefit Type 1   NULL
TAB2        C103         John         NULL             Healthy
TAB3        C103         John         NULL             Healthy
TAB1        C104         Mary         Benefit Type 2   NULL
TAB2        C104         Mary         NULL             Sick
TAB3        C104         Mary         NULL             NULL

I would like to merge the information across TabName in a new table, such that the new table contains all the unique information for each PersonID.

For example, based on the above table, I would like to find the new table

PersonID   FirstName    BenefitType      HealthStatus
C103       John         Benefit Type 1   Healthy
C104       Mary         Benefit Type 2   Sick

I am not too sure where to start with this problem.

Can you have , in  column BenefitType  , multiples unique information, for the same Person ?
if no(only one unique value/status), you can use MAX ,  else (u can have more unique values) - you must provide more information , an algorithm to how to retrieve the desired value .

The same , for for the column HealthStatus.

SELECT
    [PersonID] ,
    [FirstName],
    [Gender],
    MAX([BenefitType]) as BenefitType,
    MAX([HealthStatus]) as HealthStatus
 FROM Temp AS T
 GROUP BY
    [PersonID] ,
    [FirstName],
    [Gender]

Output:

PersonID    FirstName   Gender  BenefitType     HealthStatus
C103        John        M       Benefit Type 1  Healthy
C104        Mary        M       Benefit Type 2  Sick

dbfiddle here


Cannot open database “user_auth.mdf” requested by the login. The login failed. Login failed for user 'web_user' I am trying to deploy my first VB.NET application on GODADDY. I created a SQL server database and was able to create tables using SQL Server Management Studio. I created a user web_user and was able to login to the GODADDY database using that user. However when my website tries to connect with this database using the same user id and password, I get the following error:

  Cannot open database "user_auth.mdf" requested by the login. The login failed. Login failed for user 'web_user'.

My connection string is mentioned below. It is similar to what was recommended by GODADDY:

<add name="ConnectionString"
     connectionString="Server=XYZ;Database=user_auth.mdf;User ID=web_user;Password=zzz;
     Trusted_Connection=False" providerName="System.Data.SqlClient" />

Please help. Its driving me nuts. Stack Trace is:

  [SqlException (0x80131904): Cannot open database "user_auth.mdf" requested by the login. The login failed.
  Login failed for user 'web_user'.]

If you created the database using SSMS, you should use the logical database name and not the file with the .mdf extension in your connection string.

Use

Server=XYZ;Database=user_auth;User ID=web_user;Password=web_user

for your connection string and let SQL Server worry about the file-related details.


while restoring mysql database, views changed into tables I have a full database dump and i restored it. But in my new restored server all the views are appeared as MyISAM tables. In early times dumps were restored perfectly. can anyone help me to trigger out the issue, please?

Regards,
Praveen

In the backup script the views are first created as tables which are then dropped at the end of the script as each view is being created, so it seems that an error occurs while creating the views at the end of the script.

However when a view is created there is a user who is used in the DEFINER clause of the view who may not exist in your database, e.g., DEFINER=user123@192.168.1.%

I usually remove that clause in the backup script and it tends to work.

To verify add the -v option when importing the database


Need to filter SQL database data by time interval (1 sec, 1Min, 10 Min,1Hour, 2Hour) I want filter SQL database data by time interval (1 sec, 1Min, 10 Min,1Hour, 2Hour)
I want to fetch data from "ProDataTable" by in between Start Date and End Date with Interval of (1 Sec, 1Min, 10 Min, 1 Hour , 2 hour)

for Fetching data from Database i am using following query.

Select *
from ProDataTable
Where Date_Time between '2017-11-17 19:05' AND '2017-11-21 19:23'

So kindly let me know how i can filter out data by time interval (1 sec, 1Min, 10 Min,1Hour, 2Hour).

I want result as follows
Exa : for 1 Min

Exa : for 1 1hour

From my understanding of your question, you wish to group the data by date and time based on a specified degree of granularity. There is a good article by Steve Stedman which demonstrates how to round a date in T-SQL.

You can use the technique that he pointed out to group by the date to a specific granularity. Based upon your comments you say that you do not wish to apply any aggregation technique to the non-date/time data - this will give you a lot of duplicates based on your example data. To get the result you are looking for I simply apply a DISTINCT across the data set; however if the values of the tags vary you will still get multiple rows per specific date and time.

Example for minute-level granularity:

SELECT DISTINCT Date_Time, Tag1_Value, Tag2_Value, Tag3_Value, Tag4_Value
  FROM (SELECT DATEADD(minute, DATEDIFF(minute, 0, Date_Time), 0) AS Date_Time
             , Tag1_Value, Tag2_Value, Tag3_Value, Tag4_Value
         FROM ProDataTable
        WHERE Date_Time BETWEEN '2017-11-17 19:05' AND '2017-11-21 19:23') AS a
GROUP BY Date_Time, Tag1_Value, Tag2_Value, Tag3_Value, Tag4_Value
ORDER BY Date_Time


About "pg_ctl promote" of PostgreSQL9.1 Today I test the new feature about "replication features" of PostgreSQL9.1,
First I have installed the Hot-standby of PostgreSQL9.1 successfully. It is not a problem。
What I want to test is that I want to use command "pg_ctl start promote " to active  standby node
instead of creating a trigger file 。 Yes, after I execute the command, the standby node then becomes
primary and allow write/read operations。  But the initial primary node can not switchover to the
standby role automatically. I am not sure If PostgreSQL9.1 is able to use "pg_ctl promote" do  switchover
between primary and standby node 。Can any body explain this ?

There is no 'demote' option for pg_ctl - if the primary was cold when the standby was promoted perhaps it could be converted to a standby manually, but otherwise it will need a new base backup from the new primary.

The normal reason for switchover would be if the primary has failed so this would not be necessary.


What do you call a relationship between two entities without a foreign key? Let's say I have two tables that are related in that one table contains a column that is a key to the other table.  In other words, it would be a 1:1 or 1:* depending on constraints.

Let's call it an Customer/Orders relationship to give it some context.

However, let's say the requirement is that there be no referential integrity because the table needs to be archived without affecting the rest of the system.  In our case, they want to archive the customers, but leave the orders in the system.

Ignoring the fact we now have dangling references, which are just a fact of life when you need to prune systems in this way.  What would you call this kind of "loose" relationship where there is no actual FK?

How might you illustrate this in an ERD without implying there is a FK?  But still wishing to show that there is a relationship?

I realize that the proper method might be to use an FK anyways, and null the reference when archiving the customer, but that adds extra complexity they don't want to deal with.  Further, they don't want to update the orders table after it has been finalized.  They want to be able to find that customer number and go back in the archive and find the customer if need be.

It is still a foreign key, because it's a feature of your data. However, it is not enforced by a referential integrity constraint.

In my view this would mean that the foreign key would be present in the logical model ERD, but absent in the physical model.


Full text search results in a large amount of time spent in 'FULLTEXT initialization' I'm currently trying to run some queries against a data dump of Stack Overflow's comments. Here's what the schema looks like:

CREATE TABLE `socomments` (
  `Id` int(11) NOT NULL,
  `PostId` int(11) NOT NULL,
  `Score` int(11) DEFAULT NULL,
  `Text` varchar(600) NOT NULL,
  `CreationDate` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `UserId` int(11) NOT NULL,
  PRIMARY KEY (`Id`),
  KEY `idx_socomments_PostId` (`PostId`),
  KEY `CreationDate` (`CreationDate`),
  FULLTEXT KEY `Text` (`Text`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

I ran this query against the table, and it ran incredibly slow (It does have 29 million rows, but it has a Full-Text index):

SELECT *
FROM socomments
WHERE MATCH (Text) AGAINST ('"fixed the post"' IN BOOLEAN MODE)

So I profiled it, the results of which are:

|| Status                     || Duration ||
|| starting                   || 0.000058 ||
|| checking permissions       || 0.000006 ||
|| Opening tables             || 0.000014 ||
|| init                       || 0.000019 ||
|| System lock                || 0.000006 ||
|| optimizing                 || 0.000007 ||
|| statistics                 || 0.000013 ||
|| preparing                  || 0.000005 ||
|| FULLTEXT initialization    || 207.1112 ||
|| executing                  || 0.000009 ||
|| Sending data               || 0.000856 ||
|| end                        || 0.000004 ||
|| query end                  || 0.000004 ||
|| closing tables             || 0.000006 ||
|| freeing items              || 0.000059 ||
|| logging slow query         || 0.000037 ||
|| cleaning up                || 0.000046 ||

As you can see, it spends a long time in FULLTEXT initialization. Is this normal? If not, how would I fix it?

Others have found this a troublesome situation

InnoDB Full-text Search in MySQL 5.6: Part 3, Performance
InnoDB Fulltext Search WITH QUERY EXPANSION is very slow

Sep 16, 2010
5.0.51a, 5.0.75, 5.0 bzr
Status : No Feedback

SELECT query hang in state FULLTEXT initialization

Oct 17, 2014
MySQL 5.6.19
Status : Won't Fix

Since the MySQL Documentation is very terse on this thread state

  FULLTEXT initialization

  The server is preparing to perform a natural-language full-text search.

your only recourse would be to make preparation with less data. How ?

SUGGESTION #1

Look at your query again. It is selecting all columns. I would refactor the query to collect only the id columns from socomments. Then, join those retrieved ids back to the socomments table.

SELECT B.* FROM
(SELECT id FROM socomments
WHERE MATCH (Text) AGAINST ('"fixed the post"' IN BOOLEAN MODE)) A
LEFT JOIN socomments B USING (id);

This might produce an uglier EXPLAIN plan but I think profiling will change for the better. The basic idea is : If you have an aggressive FULLTEXT Search, make it gather the least amount of data during that FULLTEXT initialization phase, thus reducing the time.

I have recommended this many times before

May 14, 2012 : slow query with fulltext and left join
Mar 18, 2012 : Why is LIKE more than 4x faster than MATCH...AGAINST on a FULLTEXT index in MySQL?
Jan 26, 2012 : Mysql fulltext search my.cnf optimization :
Oct 25, 2011 : FULLTEXT index ignored in BOOLEAN MODE with 'number of words' conditional

SUGGESTION #2

Please make sure you are setting the InnoDB-based FULLTEXT options, not the ones for MyISAM. The two options you should be concerned are

innodb_ft_cache_size

Def Value 8000000 (7.629M)
Max Value 80000000 (76.29M)

innodb_ft_total_cache_size

Def Value 640000000 (610M)
Max Value 1600000000 (1525M = 1.49G)

Think about it for a moment. The text field is VARCHAR(600). Say the average is 300 bytes. You have 29,000,000 million of them. That would be that would be a little of 8GB. Perhaps increasing innodb_ft_cache_size and innodb_ft_total_cache_size may also help.

Make sure you have enough RAM for larger InnoDB FULLTEXT buffers.

GIVE IT A TRY !!!


Merge left outer join column I have a query that use left outer join.

something like that :

select issues.id, journal.notes from issues left outer join on (issues.id = journal.issue_id and journal.notes != '')

So far it works.

But in fact in the database journal there can be multiple row that reference the issues one.

So for each row of journal, it duplicates the result of issues.

What I would like to do is merge notes row for a same issue.

Example :

issues :
  id
  1
  2

journal :
  issue_id, notes
  1, note 1
  2, note 1 for issue 2
  2, note 2 for issue 2

Actually it returns this result :

1, note 1
 2, note 1 for issue 2
 2, note 2 for issue 2

And I would like it to be :

1, note 1
 2, note 1 for issue 2\nnote 2 for issue 2

I am using postgres.

How can I do this kind of merge ?

Thanks.

Perhaps you are looking for this:

select issues.id, array_agg(journal.notes) from issues
left outer join journal on (issues.id = journal.issue_id and journal.notes != '')
group by issues.id

Please check this http://sqlfiddle.com/#!1/24db9/2


GROUP BY on view queries PostgreSQL 9.5.16

I'm writing a query which contains several aggregate columns and many ungrouped columns. I've condensed the core logic.

SELECT min(table_b.aggregate_column),
table_a1_key,
table_a2_key,
table_a1_column
FROM api.view_a
JOIN api.table_b USING (table_a2_column)
GROUP BY table_a1_key, table_a2_key;

view_a joins table_a1 and table_a2 and exposes table_a1.table_a1_key + table_a2.table_a2_key. But I get this error when executing:

ERROR:  column "view_a.table_a1_column" must appear in the GROUP BY clause or be used in an aggregate function

Am I required to include other columns in the GROUP BY clause, even though they are functionally dependent on my grouped columns?

From docs:

  When GROUP BY is present, or any aggregate functions are present, it is not valid for the SELECT list expressions to refer to ungrouped columns except within aggregate functions or when the ungrouped column is functionally dependent on the grouped columns, since there would otherwise be more than one possible value to return for an ungrouped column. A functional dependency exists if the grouped columns (or a subset thereof) are the primary key of the table containing the ungrouped column.

You are right that table_a1_column is functionally dependent on the other columns, but PostgreSQL isn't smart enough to deduce that.

If view_a were a regular table, PostgreSQL would know that the columns in the GROUP BY clause contain the primary key, but since it is hidden behind the query rewrite rule of a view, it cannot infer that.

For PostgreSQL there are no functional dependencies between the columns of a view. It might be technically possible to improve that, but it is doubtful whether that would be worth the extra effort during query planning, particularly since the query can be fixed by simply adding all columns to the GROUP BY clause.


Take Tail-Log backup before restore option Does the SQL server restore the tail-log that backed up after the restoration operation, if I selected the option "Take Tail-Log backup before restore"?

Thanks In Advance

No, it does not restore the tail.  The tail BEFORE the restore enables you to restore to the previous full backup and then play all transaction logs back and the last thing it will play back is the tail.  This will get you back to the instant just before the bad restore.


Clustered index in SQL Server vs index organized tables in Oracle I am making the transition as a database developer from SQL Server to Oracle and found some fantastic resources here already (How to make a transition from SQL Server DBA to Oracle? and As a DBA, how would I go about transitioning from Oracle to SQL Server?) but I am having a hard time finding good information on the use of index organized tables in Oracle.

In my previous life, we made extensive use of clustered indexes in SQL Server in our OLTP-ish datamart with great success.  Are index organized tables as handy a tool in Oracle?

If you're transitioning from SQL Server to Oracle, I would advise to try heap tables at first because they are the standard form of storing data in Oracle. For most workloads, heap tables with regular indexes in Oracle are the most balanced forms of storage regarding DML and query performance.

If later you find that you have performance problems or bottleneck, you should look into specialized advanced storage methods such as IOT, partitioning, clusters, reversed-key indexes, etc.

Regarding IOT in particular, I would advise against their generalized use because there are lots of "gotchas" that you may not want to get into as a beginner:

IOT don't have real rowid (because there's no table per se).
consequently, secondary indexes on IOT don't have true pointers to the rows but only mere guesses which can lead to inefficient index scans.
Some features are disabled on IOTs such as virtual columns, table compression, composite partitioning.
You have to decide at creation where to store the non-index columns (inline or in an overflow segment), potentially leading to disastrous performance for some queries.


Should a MySQL replication slave be set to read only? I've got replication running on Percona Server 5.5 by following this guide and wondered if I should add read-only=1 to my slave's my.cnf to make it read only?

The guide sets up replication for the mysql table so users are replicated but I am primarily using the slave to take mysqldumps, an in an emergency reconfiguring it to be master, so I don't believe we need (or should have) write enabled on it constantly?

When a Slave is read-only, it is not 100% shielded from the world.
According to MySQL Documentation on read-only

This variable is off by default. When it is enabled, the server permits no updates except from users that have the SUPER privilege or (on a slave server) from updates performed by slave threads. In replication setups, it can be useful to enable read_only on slave servers to ensure that slaves accept updates only from the master server and not from clients.

Thus, anyone with SUPER privilege can read and write at will to such a Slave...
Make sure all non-privileged users do not have the SUPER Privilege.
If you want to revoke all SUPER privileges in one shot, please run this on Master and Slave:
UPDATE mysql.user SET super_priv='N' WHERE user<>'root';
FLUSH PRIVILEGES;

With reference to the Slave, this will reserve SUPER privilege to just root and prevent non-privileged from doing writes they would otherwise be restricted from.
UPDATE 2015-08-28 17:39 EDT
I just learned recently that MySQL 5.7 will introduce super_read_only.
This will stop SUPER users in their tracks because the 5.7 Docs say

If the read_only system variable is enabled, the server permits client updates only from users who have the SUPER privilege. If the super_read_only system variable is also enabled, the server prohibits client updates even from users who have SUPER. See the description of the read_only system variable for a description of read-only mode and information about how read_only and super_read_only interact.
Changes to super_read_only on a master server are not replicated to slave servers. The value can be set on a slave server independent of the setting on the master.
super_read_only was added in MySQL 5.7.8.


Convert columns into rows for use with indexed/materalized view I'd like to convert columns into rows for a given SQL Server table. The table effectively is an audit table; it stores a given old and new value in separate columns for a given user attribute (e.g. first name, middle name, last name, home number).

I've had success doing so with UNPIVOT and CROSS APPLY, respectively. However, I was hoping to improve performance for a large set of records (>500k) and all in one query. The table has a primary clustered index; however, according to the execution plan, for UNPIVOT or CROSS APPLY, both are performing a Nested Loop (Left Outer Join), at a 31% cost. Again, it's not like the query is agonizingly slow, but I'd like to inch out every bit of performance I can achieve.

My thought process was, OK, maybe if I can create an indexed view, the query will run very fast.

The problem is SQL Server indexed views (materialized views in Oracle-speak) have so many restrictions such as UNPIVOT and CROSS APPLY (see the full list here). Since CROSS APPLY is effectively an INNER JOIN and joins are acceptable for indexed views, maybe that's at least one good alternative. Even so, I'm having some difficulty rewriting CROSS APPLY as an INNER JOIN.

Here is the table (I'm showing 6 records to simplify but imagine it's 500K or more records):

UserAuditTbl

RowID     UserID      UserAttribute     OldValue     NewValue
1         ID00184     First Name        John         Jon
2         ID00184     Last Name         NULL         Albert
3         ID00185     Home Phone        555-555-1122 555-555-1212
4         ID00188     Middle Name       Jesse        James
5         ID00188     Cell Phone        555-555-1234 555-555-1555
6         ID19594     Zip Code          00000        90210

With CROSS APPLY, the query would look like:

SELECT
   RowID
  ,UserID
  ,Column
  ,Value
FROM
  UserAuditTbl
CROSS APPLY (
  VALUES ('OldValue', OldValue), 'NewValue', NewValue)
) x (Column, Value)
WHERE
  Value IS NOT NULL

I guess the first question is can I convert columns into rows such that the underlying query can be incorporated into an indexed view? And the second question is if it's best just to avoid indexed views, then what are my options here? ETL to a new table via SSIS or trigger?

Thank you for your time.

Based on your description, I think it's fairly unlikely that an indexed view is going to be your best solution. The indexed view will add overhead each time you write to UserAuditTbl, and a CROSS APPLY that unpivots each row into two rows should be quite efficient if you are careful to first seek and/or filter to only the necessary rows (e.g., only those rows for a (UserID, UserAttribute) tuple you are interested in).

If you post a full example of your exact table (including indexes), a script to fill your table to the expected number of rows with fake data, and the exact T-SQL you need to optimize, we might be able to provide more specific suggestions.

However, if you decide to try the indexed view approach, one fairly easy way to implement your CROSS APPLY as a join is to create a table that contains one row per value in your CROSS APPLY, then join to that table. For example:

Create the necessary tables

/* Your sample data */
CREATE TABLE dbo.UserAuditTbl (
    RowID INT NOT NULL IDENTITY(1,1)
        CONSTRAINT PK_UserAuditTbl PRIMARY KEY,
    UserID VARCHAR(10) NOT NULL,
    UserAttribute VARCHAR(100) NOT NULL,
    OldValue VARCHAR(100) NOT NULL,
    NewValue VARCHAR(100) NOT NULL
)
INSERT INTO dbo.UserAuditTbl (UserID,UserAttribute,OldValue,NewValue)
VALUES  ('ID00184','FirstName','John','Jon'),
        ('ID00184','LastName','NULL','Albert'),
        ('ID00185','HomePhone','555-555-1122','555-555-1212'),
        ('ID00188','MiddleName','Jesse','James'),
        ('ID00188','CellPhone','555-555-1234','555-555-1555'),
        ('ID19594','ZipCode','00000','90210')
GO

/* Create a two-row table to implement the CROSS APPLY as a join */
CREATE TABLE dbo.OldAndNewValues (
    OldOrNewValue CHAR(8) NOT NULL
        CONSTRAINT PK_OldAndNewValues PRIMARY KEY
)
INSERT INTO dbo.OldAndNewValues (OldOrNewValue)
VALUES  ('OldValue'),
        ('NewValue')
GO

Create the indexed view

/* Create a view that implements the cross apply as a join */
CREATE VIEW dbo.UserAuditIndexedView WITH SCHEMABINDING AS
SELECT u.RowID,
    u.UserID,
    u.UserAttribute,
    v.OldOrNewValue AS Col,
    IIF(v.OldOrNewValue = 'OldValue', u.OldValue, u.NewValue) AS Val
FROM dbo.UserAuditTbl u
CROSS JOIN dbo.OldAndNewValues v
WHERE IIF(v.OldOrNewValue = 'OldValue', u.OldValue, u.NewValue) IS NOT NULL
GO

/* Create the view index.
    You can adjust the column order as necessary for your audit queries.
    Note that the first index on a view must be a unique index,
    so the RowID and Col columns must be used. */
CREATE UNIQUE CLUSTERED INDEX UQ_UserAuditIndexedView
ON dbo.UserAuditIndexedView (UserID, UserAttribute, RowID, Col)
GO

Confirm that the results match

/* Use the view index */
SELECT *
FROM dbo.UserAuditIndexedView WITH (NOEXPAND)
ORDER BY RowID, Col

/* An adapted version of your original query (the original does not compile) */
SELECT RowID, UserID, UserAttribute, Col, Val
FROM dbo.UserAuditTbl
CROSS APPLY (
  VALUES ('OldValue', OldValue), ('NewValue', NewValue)
) x (Col, Val)
WHERE Val IS NOT NULL
ORDER BY RowID, Col
GO


SQL Server: calculating memory requirements for Full db backups In 2018, we inherited a production SQL Server 2012 FCI running on Windows 2012 with 32 GB RAM. SQL Server max server memory was set at 23.6 GB, and things were running fine.

However, in 2019, we migrated these databases to a SQL Server 2016 FCI. After this migration, our Full backups began intermittently failing due SQL Server restarts. The log seemed to indicate these restarts were due to low memory.

I noticed all of these SQL Server restarts only happened when a full backup was running for our biggest (~80 GB) db. (Incidentally, in case this matters, this db is set to simple recovery model. I have 4 other dbs in full recovery model on this instance: 10 GB, 110 MB, 100 MB, and 50 MB.)

Each time these "low memory restarts" occur, I have been incrementally increasing RAM and max memory. Currently, I'm at 56 GB RAM and max memory is at 45 GB.

From your experience, does it seem unusual for an 80 GB database to require 45 GB max memory during full backups? Can you please share any ideas how I can better identify how much memory my full backup truly needs? Unfortunately, I don't have a non-production system with similar specs as this one.

At this point, I think this could explain my observations:

https://support.microsoft.com/en-us/help/4347088/fix-out-of-memory-error-when-there-are-free-pages-in-sql-server


Choosing values from multiple columns based on latest value of one column I have an Azure Cosmos DB that contains columns DateTime, userName, temperature, and bloodPressure, plus many other columns. There are several distinct userName values, and each userName has temperature and bloodPressure data collected at different values of DateTime. The temperature and bloodPressure values are different for each of those 15 times.
Here is an example of the data,
userName, DateTime, bloodPressure, temperature, heartBeat, hatSize
Curly, "2021-01-19 18:24:53", 121, 98, 60, 7.25
Larry, "2021-01-14 18:24:53", 125, 99, 80, 7.5
Mo, "2021-01-13 18:24:53", 123, 100, 70, 7.75
Curly, "2021-01-18 18:24:53", 120, 97, 50, 8
Larry, "2021-01-13 18:24:53", 119, 99, 75, 7.15
Larry, "2021-01-15 18:24:53", 115, 98, 85, 7.37
Mo, "2021-01-12 18:24:53", 110, 102, 100, 8.01
Mo, "2021-01-20 18:24:53", 130, 99, 110, 6.97
Larry, "2021-01-18 18:24:53", 127, 98, 72, 7.76
Curly, "2021-01-17 18:24:53", 126, 97, 82, 8.0

For each user, I would like to return the DateTime of their latest measurement, and their temperature and bloodPressure from their latest DateTime:
Larry, "2021-01-18 18:24:53", 127, 98
Mo, "2021-01-20 18:24:53", 130, 99
Curly, "2021-01-19 18:24:53", 121, 98

I tried this,
SELECT
    c.userName,
    MAX(c.DateTime),
    c.bloodPressure,
    c.temperature
FROM CheckupData c
GROUP BY
    c.userName

but got an error that GROUP BY should include the other outputs of SELECT. Since bloodPressure and temperature are always changing, it seems like I shouldn't use them in GROUP BY, because I only want the lastest values. What am I doing wrong?
(If it matters, my real database is larger: 50,000 userNames measured at 50 or so DateTime values each, and 30 or so columns).

EVERY Column in a SELECT has to be in the GROUP BY or use a aggregator function
SELECT
    c.userName,
    MAX(c.DateTime),
    c.bloodPressure,
    c.temperature
FROM CheckupData c
GROUP BY
    c.userName,c.bloodPressure, c.temperature

OR
SELECT
    c.userName,
    MAX(c.DateTime),
    AVG(c.bloodPressure),
    AVG(c.temperature)
FROM CheckupData c
GROUP BY
    c.userName

As we don't know nothing about your data and desired result. You have to choose what values you need.
For big tables you can use

CREATE TABLE CheckupData
    ([userName] varchar(5), [DateTime] varchar(21), [bloodPressure] int, [temperature] int, [heartBeat] int, [hatSize] int)
;

INSERT INTO CheckupData
    ([userName], [DateTime], [bloodPressure], [temperature], [heartBeat], [hatSize])
VALUES
    ('Curly', '"2021-01-19 18:24:53"', 121, 98, 60, 7.25),
    ('Larry', '"2021-01-14 18:24:53"', 125, 99, 80, 7.5),
    ('Mo', '"2021-01-13 18:24:53"', 123, 100, 70, 7.75),
    ('Curly', '"2021-01-18 18:24:53"', 120, 97, 50, 8),
    ('Larry', '"2021-01-13 18:24:53"', 119, 99, 75, 7.15),
    ('Larry', '"2021-01-15 18:24:53"', 115, 98, 85, 7.37),
    ('Mo', '"2021-01-12 18:24:53"', 110, 102, 100, 8.01),
    ('Mo', '"2021-01-20 18:24:53"', 130, 99, 110, 6.97),
    ('Larry', '"2021-01-18 18:24:53"', 127, 98, 72, 7.76),
    ('Curly', '"2021-01-17 18:24:53"', 126, 97, 82, 8.0)
;

GO

SELECT
cd.[userName], cd.[DateTime], cd.[bloodPressure], cd.[temperature], cd.[heartBeat], cd.[hatSize]
FROM CheckupData cd INNER JOIN
(SELECT MAX([DateTime]) lastdate,[userName] FROM CheckupData GROUP BY [userName]) cd1
ON cd.[userName]  = cd1.[userName] AND cd.[DateTime] = cd1.lastdate
GO

userName | DateTime              | bloodPressure | temperature | heartBeat | hatSize
:------- | :-------------------- | ------------: | ----------: | --------: | ------:
Mo       | "2021-01-20 18:24:53" |           130 |          99 |       110 |       6
Larry    | "2021-01-18 18:24:53" |           127 |          98 |        72 |       7
Curly    | "2021-01-19 18:24:53" |           121 |          98 |        60 |       7

db<>fiddle here


How to get rows only contains multiple ID using SQL? Let say I have this table:

id | foo
1  | 100
1  | 200
1  | 200
2  | 300
2  | 300
3  | 200
4  | 100

I am expecting to have this result:

id | foo
1  | 100
1  | 200
1  | 200
2  | 300
2  | 300

I want to get only rows that have multiple id log, regardless of the foo value.

You can try this:

SQL> select * from dummy;

    ID    FOO
---------- ----------
     1    100
     1    200
     1    200
     2    300
     2    300
     3    200
     4    200

7 rows selected.

SQL> select * from dummy where id in (select id from dummy having count(*) > 1 group by id);

    ID    FOO
---------- ----------
     1    100
     1    200
     1    200
     2    300
     2    300

SQL>


Is my implementation of type/subtype design pattern (for mutually exclusive subclasses) correct? Introduction
In order for this question to be useful for future readers I will use the generic data model to illustrate the problems I face.
Our data model consists of two entities, which shall be labeled as A and B. In order to keep things simple, all their attributes will be of int type.
Entity A has following attributes: D and X;
Entity B has following attributes: D and Y;
Problem
Since both entities share common attribute D, I have decided to apply type/subtype design.
I am not sure that my implementation is correct, thus I am asking here for design review.
My Implementation
-- lookup table for discriminator column
CREATE TABLE ClassType
(
  ClassTypeID INT NOT NULL IDENTITY(1, 1) PRIMARY KEY,
  Class_Description VARCHAR(50) NOT NULL
);

-- inserting types A and B from our example
INSERT INTO ClassType (Class_Description)
VALUES ('A'), ('B');

-- creating base class table
CREATE TABLE BaseClass
(
  BaseClass_ID INT NOT NULL IDENTITY(1, 1) PRIMARY KEY,
  ClassTypeID INT NOT NULL, -- FK to Type
  D int
);

ALTER TABLE BaseClass
ADD CONSTRAINT [FK_BaseClass_ClassType]
FOREIGN KEY (ClassTypeID)
REFERENCES ClassType (ClassTypeID);

-- we need this constraint in order for foreign keys in subclasses to work
ALTER TABLE BaseClass
ADD CONSTRAINT [FK_AltKey]
UNIQUE (BaseClass_ID, ClassTypeID);

-- creating subclasses:
CREATE TABLE SubclassA
(
  BaseClass_ID INT NOT NULL PRIMARY KEY,
  X int,
  ClassTypeID AS 1 PERSISTED -- calculated field, ensures integrity
);

ALTER TABLE SubclassA
ADD CONSTRAINT [FK_SubclassA_BaseClass]
FOREIGN KEY (BaseClass_ID, ClassTypeID)
REFERENCES BaseClass (BaseClass_ID, ClassTypeID);

CREATE TABLE SubclassB
(
  BaseClass_ID INT NOT NULL PRIMARY KEY,
  Y int,
  ClassTypeID AS 2 PERSISTED -- calculated field, ensures integrity
);

ALTER TABLE SubclassB
ADD CONSTRAINT [FK_SubclassB_BaseClass]
FOREIGN KEY (BaseClass_ID, ClassTypeID)
REFERENCES BaseClass (BaseClass_ID, ClassTypeID);

This is how the database diagram looks in SQL Server 2012:

Questions

Did I make any mistakes in my implementation?
Apart from making class type (in subclass tables) calculated and persisted, is there anything else I can do to prevent mistakes during INSERT/UPDATE/DELETE?

Yes, the design looks great. Minor notes:

You could use TINYINT, instead of the INT for the ClassTypeID. Or even CHAR(1) and have 'A' and 'B' instead of 1 and 2. 1 byte instead of 4 means you are saving 3 bytes in every row, in all 3 tables and in every index that includes ClassTypeID - which would be every index on these tables, if ClassTypeID is part of the clustered key.
The attributes of both the base and the subtype tables could be NOT NULL. I don't see why you would want them nullable, with this design.
It might be better (but needs thorough testing) if you had both the UNIQUE constraint of the base table and the 2 foreign keys that reference it defined with the reverse order (ClassTypeID, BaseClass_ID). This is more an indexing / physical design suggestion, it doesn't alter the logical design. I would experiment with having the clustered key in the base table using this order, too.


What happens during RCSI while tempdb is being populated? Our database/application utilizes Read Committed Snapshot Isolation so that readers are not blocked by writers, and writers are not blocked by readers. Of course that means when a row/page/table lock occurs, the current value of those rows has to be copied to tempdb.

My question is this - if a lock escalates to a table level lock on a large table, and SQL has to copy all of that data into tempdb, what happens when a SELECT statement happens on that table.

Scenario:

Thread 1 - TABLE_A - UPDATE WHERE X=Y (escalates to a table level lock)
Thread 2 - SELECT X FROM TABLE_A

Obviously if there was only 1 or 2 rows in this table, or if it was just a page lock, the select statement would execute pretty quickly. But what if TABLE_A had a few million records in it, and the table got locked. Would the select statement block while waiting for the UPDATE to populate tempdb for the current version?

Of course that means when a row/page/table lock occurs, the current value of those rows has to be copied to tempdb.

It has nothing to do with locks. The previous version of a modified row is copied to the version store in tempdb just before the actual modification is made. Physical integrity is protected by a latch on the page.

The reading query does not have to wait (in the way you suggest), because the version it needs is guaranteed to be in the version store before the data change is made. Since modifications are made a row at a time, the question of waiting for the whole table to be copied to tempdb does not arise.

It may have to wait to acquire a latch on the page containing the row, while the data modification is being made, but this is not specific to using a versioning isolation level.

In a pathological case, where the update and select process the same pages in the same order, at an equal rate, the select would have to wait (on a latch) for each page while the update changed those rows and copied to the version store, but this is exceedingly unlikely to happen in practice. As soon as the update yields even briefly, the select can easily get ahead, and so no longer have to wait.


Who is "focker" and why does he own my schema? I have a Postgres DB deployment in Compose.io, and I am using the standard "compose" db for my app.  I would like to wipe out the current DB contents so that I can recreate the DB with a new schema.  I have read that the best way to do this is with "drop schema public cascade", but when I try to do this on my db I get

ERROR:  must be owner of schema public

It turns out that the schema is not owned by admin (the user that compose provides for psql access), but by a user named focker.

compose=> \dn
 List of schemas
  Name  | Owner
--------+--------
 public | focker
(1 row)

I tried changing the schema owner but that fails with the same error as above.

Why is my db set up this way?  Is there any way I can change it so that the admin user can wipe out a db?

Unfortunately, I can only answer the question in the title.

Here's an excerpt from an article on Compose.io that might shed some light on your question:

  As LXC {Linux containers} doesn't have a nice command line API, Compose has developed its own internal tool. As Docker started as a nice command line for LXC, Kurt noted that Compose's tools were now named, tongue firmly in cheek, Focker. Focker is where all the tools are bundled up and, unlike Docker which focuses on packaging, it concentrates on the resource allocation and managment. The container instances are then connected via Open vSwitch which allows each customer to have their own private VLAN for their database.

Bottom line - "focker" represents the command line API "user" of Compose.io.

In yet another article, we can see the following:

[snapshot] more README

This snapshot is meant to be run with the same minor version
of Postgresql, which is postgres (PostgreSQL) 9.4.5.

To startup a Postgresql environment with this snapshot, run:
`postgres -D conf`

You can then connect to the db by running: `psql postgres -U focker`

  ...

My best bet would be trying: psql postgres -U focker


How can I detect broken stored procedures after a schema change? I've modified a central table in my database, and sp_depends literally returns hundreds of results, and I'm concerned some of those stored-procedures might not compile anymore after my change.

Checking one single stored procedure is easy (I just rerun the alter script and see whether the operation is successful), but doing that on 100+ procedures is a bit cumbersome.

I know I can use a script like this one to recompile all the objects of my database, but the actual operation will take place next time the stored procedure is executed, not immediately, so that doesn't seem appropriate in my case.

I was also thinking that I could drop all the stored procedures altogether, and resycnhronize my database with my source control system, but that option, although viable, isn't very elegant. Is there a better way of doing this?

I'm using SQLServer 2008 R2 and my database scripts are stored in a VS 2008 database project.

To clarify, I'm not advocating one should solely rely on this approach to test code.
Just exactly like in c# you instantaneously detect syntax error in other dependent files as you code (and then use other strategies to test such as unit tests, which is usually several orders of magnitude slower), I think it would make sense to detect SQL dependencies errors in seconds rather than having to run a full functional test which can typically take a few hours to complete.

How about you run your your unit, functional, integration and performance tests? If you don't have any tests then is serious time to start considering your database schema as code and treat it as such, including version control and testing. Alex Kuznetsov has an entire book dedicated to this subject: Defensive Database Programming with SQL Server.


Why does SUM() return 0 when no rows in table? I've been saying for years that if you are assigning an aggregate value to a variable that you need to handle if there are no rows returned. ie:

DECLARE @MyVar INT

SELECT @MyVar = SUM(t.MyValue) FROM dbo.MyTable t WHERE /* clause */

IF @MyVar IS NULL
    SET @MyVar = 0

However a developer recently pointed out that they don't need to do the @@ROWCOUNT check as the SUM always returns a value (even if there are no rows).  Upon doing some further digging I've found that there seems to be inconsistent behaviour from SQL Server:

So if I run:

DECLARE @MyTable TABLE(ID INT, MyValue INT)

/* you get a value of 0 back */
SELECT ISNULL(SUM(t.MyValue),0) FROM @MyTable t WHERE t.ID = 100

I get a single row with a value of 0 back.

However if I add on a GROUP BY clause:

DECLARE @MyTable TABLE(ID INT, MyValue INT)

/* when you add on a GROUP BY, you no longer get a record back */
SELECT ISNULL(SUM(t.MyValue),0) FROM @MyTable t WHERE t.ID = 100 GROUP BY t.ID

I get no rows back (which is what I expected), I've done some digging into the MS documentation but can find no reference to this difference in behaviour.  Can anyone explain why this is occuring?  Also is this a change in how SQL Server works?

Note: My tests have been in SQL Server 2008R2

** Edit: looked back on some emails I'd sent in the past and corrected my check, I've been saying If the value is still NULL handle it...  (handles no rows as well as assigned NULL)

The result is correct, i.e. according to the standards, as far as I can understand it.

The related question: What is the correct result for this query? contains the paragraph that explains it:

  The following is an excerpt from the General Rules of 7.9 (the definition of the GROUP BY clause)

    1) If no <where clause> is specified, then let T be the result of the preceding <from clause>; otherwise, let T be the result of the preceding <where clause>.

    2) Case:

    a) If there are no grouping columns, then the result of
    the <group by clause> is the grouped table consisting of T as its only group.

So, when there is no GROUP BY - or when we have GROUP BY () - the result should be a single group. It doesn't matter if the table is empty or not. You'll get a single row* in the result.
If the table is empty (and the SUM() function is used), you get a single row with NULL as value - which you convert to 0 using ISNULL().

When you have GROUP BY t.id, you'll have one row for every group. But there are 0 groups in your case, so 0 rows in the result.

*: SQL-Server will however return 0 rows for the query with GROUP BY (), which is contradicting the standard.


Updates since the previous commit point Vs updates prior to the commit point The quote below is from An Introduction to Database Systems, 8th edition, C J Date.

I am confused since I think "all updates since the previous commit point" is the same as "updates Prior to the commit point". Am I wrong?

  ...all database updates made by the executing program since the previous commit point are committed; that is, they are made permanent, in the sense that they are guaranteed to be recorded in the database. Prior to the commit point , all such updates must be regarded as tentative only-tentative...

The text must be talking of two different commits during the same database session.

Say, the last commit that happened in this session was at time A. Now if you make a second commit at B, all updates made since A (the previous commit) are committed. Prior to the commit at B, all such updates are only tentative.


Error with restoring from a split .bak file with password I am trying to restore a backup from a split .bak file[.bak_1/.bak_2/.bak_3]. The backup is password protected. So far this is what I have and I cannot seem to format in a way that does not throw me an error.

USE master
GO
RESTORE DATABASE client_a_database1
  FROM DISK = 'G:\database1\***.bak_1',
       DISK = 'G:\database1\***.bak_2',
       DISK = 'G:\database1\***.bak_3'
  WITH MOVE 'database1_data' TO 'G:\database1\***.mdf'
       MOVE 'database1_log' TO 'G:\database1\***.ldf', REPLACE,
  Password 'password'
  GO

With this format I am getting an error on the Password that I have added.

Below will work, as you had syntax error :

USE master
GO
RESTORE DATABASE client_a_database1
  FROM DISK = 'G:\database1\***.bak_1',
       DISK = 'G:\database1\***.bak_2',
       DISK = 'G:\database1\***.bak_3'
  WITH MOVE 'database1_data' TO 'G:\database1\***.mdf'
       MOVE 'database1_log' TO 'G:\database1\***.ldf', REPLACE,
  Password = 'password'
  GO

Also, a RESTORE HEADERONLY output will help understand why you are getting -

  restore was taken from a different MS SQL version.


Does sys.sql_logins.is_policy_checked mean that the policy has been checked? When I look in sys.sql_logins, I see a column called is_policy_checked. Can I trust that my password policy has been checked for all of the logins where this column value is 1?

No.

While the documentation currently has the following arguably ambiguous statement about what this flag means:

  Password policy is checked.

What it really means, and should say, is that the flag serves two purposes:

  The password policy might have been checked, but only if (a) the password policy was enabled at the time the password was last set, and (b) the password was specified in plain text (not with a hash).
  The password policy will be checked the next time the policy is set, but only if (a) the password policy is enabled at that time, and (b) the password is specified in plain text (not with a hash).

(And note that "the policy" also refers to enforcing expiration and the fact that the user must change the password on next login, but since the complexity is typically the focus of auditing operations, I'm going to focus only on that aspect.)

The is_policy_checked bit is set to 1 if CHECK_POLICY = ON during a CREATE LOGIN or ALTER LOGIN event, even if the policy isn't checked at the time. As you can probably gather from above, this check does not happen in these scenarios:

The password is specified using the HASHED keyword (a very common tactic when migrating logins between servers or copying logins to log shipped / mirrored / AG secondaries). It is obviously not possible to check the complexity of the password if you don't have the pre-hashed value.
The local password complexity policy is not enabled at the time the event occurs.
Not covered in my proposed rewording above, but you can ALTER LOGIN without setting a new password, and still change the flag (thanks to @AMtwo for illustrating this). I suspect this may have been done by clever people trying to fool an auditor.

These problems are all easy to demonstrate.

Since most people I've talked to about this have always assumed that is_policy_checked actually means that the current password meets the current password policy, I think it's important that something changes here so that users have the right expectations and understand that this flag does not necessarily mean all is well. At the very least, the documentation should be updated to reflect reality, somewhat like I have pointed out above. But there are other things that can be done, too.

A warning could be raised if CHECK_POLICY = ON is specified but the policy can't, in fact, be checked (either because the password is specified with a hash, or because the password policy has been disabled, or because the command is a simple attempt to bypass or set the flag, e.g. ALTER LOGIN blat WITH CHECK_POLICY = ON;).
CHECK_POLICY could be deprecated, in favor of ACTIVELY_CHECK_POLICY and perhaps CHECK_POLICY_ON_NEXT_CHANGE. The columns in sys.sql_logins should be policy_has_been_checked and policy_will_be_checked. I am not married to these names, but they are a lot more accurate than the current wording.
If I choose ACTIVELY_CHECK_POLICY = ON and the policy can't be checked during the execution of the command, I should receive an error message and the flag should not be set to 1 (or even the login creation or password change should not succeed).
I don't think it makes sense in this case to continue with the current behavior, where I can specify that I want the policy to be checked, but even if it can't, the password is allowed and the login is created/altered (this is bad, IMHO, regardless of the state of the flag after the fact - but at least if it were set to 0, such bypasses could be identified).

Today there is no reliable way - without manually changing their passwords to something you know is secure - to audit your SQL logins and be confident that they all meet your complexity policy. In this day and age of ever-increasing data, more and more data breaches, and the obvious need to secure systems tighter and tighter, this is a problem that needs to be addressed. I've blogged about this and created a Connect item about it:

Please help me fix a big security loophole
CHECK_POLICY is a no-op with CREATE LOGIN ... PASSWORD ... HASHED

I encourage you to vote on the Connect item and, more importantly, be sure you're not auditing your systems with false perceptions about how this DDL option and metadata work.

Please don't brush this aside as a "non-issue" because you are perfectly comfortable with how it works and already know that the flag can't be trusted - you are not the user I'm worried about; it's everyone else.


Is it necessary to create foreign key in this query? I have the table called investors having as primary key the column id. I have the tables Users, Login_Logs and Accounts. All of these tables contains the foreign key investor_id. Between investor and user we have a one to one relation. between investor and login_logs a one to many relation and between investor and acccount a one to many relation as well. My question is in order to make a query to load easily info contained in the table users, login_logs and accounts do I need to store user id, accounts id and login_logs in the investors table? I mean do I need to create a foreign keys in the investors table?

I am not entirely certain that I understand the semantics of your database design but foreign keys have a special meaning in a database.

A foregin key, investors_id in the Users table,  is a constraint saying that if a value appears in the investors_id column in the Users table, then that value must appear once in the Investor table id column.

My interpretation of that fact is that a user might be an investor as well and if he/she isn't then there must be a null value in the investors_id column in the Users table for that person.

This, in turn, means that as all investors must be users and then it is acceptable that there is the corresponding constraint the other way so you might have a foreign key in the investor table "pointing" at a record in the Useres table but it is not neccessary.

I would, however, think it over more than once. Do I really need to specifically point at this fact?

In the other two cases, foreign keys in the investors table referring to the Login_Logs and Accounts table, the same reasoning tells you that it is wrong.

As several Accounts table records may refer to the same investor in the investors table, there is from a semantic point of view no such constraint in the opposite direction.

The fact that you have a one-to-many relationship from investors to Accounts and from investors to Login_Logs makes it wrong. You would break the obvious semantics, an investor may deal   with many accounts but an account is related to a single investor.

Finally, foreign keys might have performance aspects but I never use them that way. They are the glue that determines the semantics of a database and in my opinion that is the only way to use them.

So, my conclusion is that it is possible to have a foreign key in the investors table referring to the Accounts table but not neccessary and it is wrong in the other two cases.


PostgreSQL: import several .csv files through sql copy statement In the server hosting the PostgreSQL server, there is a very specific directory where a series of .csv files will be loaded regularly to update one of the databases. I want to make the process of uploading the data contained in these files as automatic as possible, and I have thus created a .sh script to do this. It is a simple for loop iterating through the set of .csv files in that directory and passing their names as parameters to a \COPY sentence.

Now, since the server administrator are being a little privy of their server, they would like to give us access only to the SQL server and not to the underlying unix server. So, here goes the question:

Is there a way to accomplish the task described above through a stored procedure executed from inside the database? Can you really read and access a path and its contents in that way from the database? The whole set of .csv files could vary so I don't think a hard-coded solution would work, plus it would look rather dirty (although, if that is the only way I can make it work, so be it).

My guess is that you cannot but... you never know.

Now, since the server administrator are being a little privy of their server, they would like to give us access only to the SQL server and not to the underlying unix server.

I'm not sure how you're accessing the SQL server from that. However, if you're going to keep getting access to psql, perhaps you can work around their security with \! in psql

  \! [COMMAND]           execute command in shell or start interactive shell


Create Linked Server to AlwaysOn Availability Group Listener I have an alwayson availability group setup using SQL 2014 and Windows Server 2012 R2. There are 3 nodes in the group. The SQL Service is running as a Managed Service account on each node (1 MSA for each server).

I am trying to make a linked server on each of the nodes to point to the current active node. So lets say the three servers are called Server1, Server2 and Server3, and the AlwaysOn availability group is called SQLAAG_PROD. I want to create the linked server to SQLAAG_PROD.

I am able to create a linked server to each individual node (e.g. Server1 with a link to server2 and Server3) and it works fine using the security option Be made using the login's current security context.

However when I try to create a linked server to the group listener I get Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON

If I change the security mode to use the SA password for the current active server then everything works, but for obvious security reasons that's not an acceptable solution.

I've read a lot about needing to setup SPN's and allow accounts for delegation. I have turned on Trust this computer for delegation to any service (Kerberos only) on the SQLAAG_PROD object in Active Directory but still have the same problem.

Has anyone seen this before, and/or is there a guide on how to set this up?

===UPDATE===

Not sure if it helps, but when I run

select auth_scheme from sys.dm_exec_connections where session_id=@@SPID

when connected directly to the node I get KERBEROS, however if I connect to SQLAAG_PROD and run the same query then I get NTLM

Finally found a tutorial here: http://www.derekseaman.com/2014/10/sql-2014-always-ag-pt-12-kerberos.html

The problem for me was that I needed to register the SPN for the Availability Group against the Managed Service accounts that are used to run the SQL Database Engine (step 8 in the link). Once I did that and restarted all 3 servers in the cluster everything works.


Why does MySQL say I'm out of memory? I was trying to execute a fairly large INSERT...SELECT in MySQL with JDBC, and I got the following exception:

Exception in thread "main" java.sql.SQLException: Out of memory (Needed 1073741824 bytes)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1073)

Since I'm not actually returning a ResultSet object, I thought the Java heap space shouldn't be an issue.  However, I tried to up it anyway and it did no good.  I then tried to execute the statement in MySQL Workbench and I got essentially the same thing:

Error Code 5: Out of memory (Needed 1073741816 bytes)

I should have plenty of RAM to complete these operations (enough to fit the whole table I'm selecting from), but I'm guessing there are various settings I need to tweak to take advantage of all my memory.  I'm running an Amazon EC2 High Memory Double Extra Large Instance with a Windows Server 2008 AMI.  I've tried fiddling with the my.ini file to use better settings, but for all I know I might have made things worse.  Here's a dump of that file:

[client]
port=3306
[mysql]
default-character-set=latin1
[mysqld]
port=3306
basedir="C:/Program Files/MySQL/MySQL Server 5.5/"
datadir="C:/ProgramData/MySQL/MySQL Server 5.5/Data/"
character-set-server=latin1
default-storage-engine=INNODB
sql-mode="STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"
max_connections=100
query_cache_size=1024M
table_cache=256
tmp_table_size=25G
thread_cache_size=8
myisam_max_sort_file_size=100G
myisam_repair_threads = 2
myisam_sort_buffer_size=10G
key_buffer_size=5000M
bulk_insert_buffer_size = 4000M
read_buffer_size=8000M
read_rnd_buffer_size=8000M
sort_buffer_size=1G
innodb_additional_mem_pool_size=26M
innodb_flush_log_at_trx_commit=2
innodb_log_buffer_size=13M
innodb_buffer_pool_size=23G
innodb_log_file_size=622M
innodb_thread_concurrency=18
innodb_file_per_table=TRUE
join_buffer_size=4G
max_heap_table_size = 10G

So is this just a matter of changing the above settings to work better for my environment?  If so, what settings should I use?  I'm the only one who ever uses this instance; I use it for my personal hobby project that involves statistical analysis of large datasets.  As such, I'm free to let it consume all available resources for my own queries.

If this is not a matter of changing those settings, what is the problem?  Thanks for any help you can offer for how to better configure everything.

Given this is a Windows installation, @DTest still provided the initial proper direction.

Apply the following formula:

Most people use this:

Maximum MySQL Memory Usage = innodb_buffer_pool_size + key_buffer_size + (read_buffer_size + sort_buffer_size) X max_connections

I prefer this:

Maximum MySQL Memory Usage = innodb_buffer_pool_size + key_buffer_size + ((read_buffer_size + read_rnd_buffer_size + sort_buffer_size + join_buffer_size) X max_connections)

These variables are the ones you need to adjust until the formula yields 80% of installed RAM or less.

sort_buffer_size
read_buffer_size
read_rnd_buffer_size
join_buffer_size
max_connections


Postgresql: how to remove a GRANT on multiple tables? I have  a big database with 40 schemas and plenty of tables inside each one. Some tables have a GRANT SELECT ON TABLE schema1.table1 TO user1grp. I no longer need user1grp group role.

If I delete this group role, are the related GRANT going to be removed from related tables?

Or do I need to remove the GRANT on each table before deleting the group role? If this is the good process, is there a way to list all tables related to this group role?

The privileges are not automatically removed.  Let's consult the documentation, as always:

  dropping a role is often not just a matter of a quick DROP ROLE. Any objects owned by the role must first be dropped or reassigned to other owners; and any permissions granted to the role must be revoked.

So, if the role owns any objects, you have to either transfer the ownership to someone else, or drop the object.  These can be done by

REASSIGN OWNED BY doomed_role TO successor_role;
DROP OWNED BY doomed_role;

The important fact is that DROP OWNED does not only the dropping:

  DROP OWNED also takes care of removing any privileges granted to the target role for objects that do not belong to it.  Because REASSIGN OWNED does not touch such objects, it's typically necessary to run both REASSIGN OWNED and DROP OWNED (in that order!) to fully remove the dependencies of a role to be dropped.

A limitation you have to observe is that DROP OWNED works only on the current database, and not on other databases that might also contain objects with privileges granted to the to-be-deleted role.


SQL Server 2008, Sum two columns from different tables I'm trying to return a table with only 3 columns: CMP, CODE and Totalization.

On the first table I need to Sum all the amount data and then group by CODE.

Then I query a second table and get the code and Forecast.

Finally, I need to sum the sum_cash with Forecast and group them by code.

The snippet below works for MySQL, but in SQL server, it keeps giving me this error:

  "Column 'cash.sum_cash' is invalid in the select list because it is not contained in either an aggregate function or group by.

I'm really open to modify the code as long as I can get those 3 columns.

I'm Pretty sure the problem relies on the "Totalization" thing. But I'm no expert on SQL server, or any other SQL language, so I really need help for this one.

SELECT
    cash.CMP as 'Name',
    cash.CODE as 'Code',
    (cash.sum_cash + bal.FORECAST) as 'Totalization'
From(
        Select
            CMP,
            CODE,
            sum(CASE when BUDGET in ('4','25') then AMOUNT else AMOUNT * -1 end) sum_cash
            From TEST1
            where Nature=12
            GROUP BY CODE
        ) cash,
        (
        SELECT
            CODE,
            FORECAST
            FROM TEST2
            where BALANCE_TYPE=-2
        ) bal
GROUP BY cash.CMP, cash.CODE;

You are probably getting a syntax error from:

Select
        CMP,
        CODE,
        sum(CASE when BUDGET in ('4','25') then AMOUNT else AMOUNT * -1 end) sum_cash
From TEST1
where Nature=12
GROUP BY CODE

You probably need a full group by:

Select
        CMP,
        CODE,
        sum(CASE when BUDGET in ('4','25') then AMOUNT else AMOUNT * -1 end) sum_cash
From TEST1
where Nature=12
GROUP BY CMP, CODE

or if CMP is functionally dependent on code you can apply an aggregate for CMP to make it valid:

Select
        MAX(CMP) as CMP,
        CODE,
        sum(CASE when BUDGET in ('4','25') then AMOUNT else AMOUNT * -1 end) sum_cash
From TEST1
where Nature=12
GROUP BY CODE

Next thing I find peculiar is that you are doing a cross join between your two sub-selects, are they guaranteed to return exactly 1 row each? I would guess that what you are trying to do should be something like:

SELECT
    cash.CMP as Name,
    cash.CODE,
    cash.sum_cash + bal.FORECAST as Totalization
From(
    Select MAX(CMP) as CMP, CODE
         , sum(CASE when BUDGET in ('4','25')
                    then AMOUNT
                    else AMOUNT * -1
               end) sum_cash
    From TEST1
    where Nature=12
    GROUP BY CODE
) cash
JOIN (
    SELECT CODE, FORECAST
    FROM TEST2
    where BALANCE_TYPE=-2
) bal
    ON cash.code = bal.code


UPSERT statement never completes, infinite loop? Based on some of the stackoverflow answers I wrote the following UPSERT statement, which I think must be stuck in an infinite loop. I added a statement in there to avoid an infinite loop (as it would be possible in this case for the insert and update statements to be mismatched and therefore enter an infinite loop):

CREATE FUNCTION upsert(sql_insert TEXT, sql_update TEXT) RETURNS VOID AS
$$
DECLARE
    i integer := 0;
BEGIN
    LOOP
        -- terminate if loop reaches 2 runs
        IF i >= 2 THEN
            RETURN;
        END  IF;
        BEGIN
            -- try update
            EXECUTE sql_update;
            -- terminate if update successful
            IF found THEN
                RETURN;
            END IF;
            BEGIN
                -- try insert
                EXECUTE sql_insert;
                RETURN;
            -- insert violated unique constraint, try loop again
            EXCEPTION WHEN unique_violation THEN
            END;
        END;
        i := i+1;
    END LOOP;
END;
$$
LANGUAGE plpgsql;

The function works, tested it by creating a small table. So the problem must be with my input, but I still can't see what the issue is - unless the function is just incredibly slow. It's not a particularly big table with around 11,000 rows.

However, this sample input never finishes running:

SELECT upsert(
$$
INSERT INTO accounts
VALUES (
    '0012000000wa7WzAAI', 'Random company', NULL,
    'Tier 2', 'Joe BLoggs', 'Joe Bloggs', 'US',
    TIMESTAMP '2012-12-14 00:00:00', NULL, 'Ex-Customer')
$$,
$$
UPDATE accounts
SET account_name='Random Companyh', country=NULL, account_tier='Tier 2',
    account_owner='Joe Bloggs', sales_person='Joe Bloggs',
    office_managed='US', date_first_opportunity=TIMESTAMP '2012-12-14 00:00:00',
    industry=NULL, account_type='Ex-Customer'
WHERE account_id='0012000000wa7WzAAI'
$$
)

I can't work out why this isn't working, any help would be appreciated.

The immediate cause of the infinite loop is this. Per documentation:

  Note in particular that EXECUTE changes the output of GET DIAGNOSTICS, but does not change FOUND.

But there's more:

CREATE FUNCTION upsert(_ins text, _upd text)
  RETURNS int AS
$func$
DECLARE
   loop_ct int := 0;
   row_ct  int;
BEGIN
LOOP
   IF loop_ct > 1 THEN   -- terminate on 2nd loop
      RETURN 0;
   END  IF;

   EXECUTE _upd;         -- try update
   GET DIAGNOSTICS row_ct = ROW_COUNT;
   IF row_ct > 0 THEN    -- terminate if update successful
      RETURN row_ct;
   END IF;

   BEGIN                 -- try insert
      EXECUTE _ins;
      GET DIAGNOSTICS row_ct = ROW_COUNT;
      RETURN row_ct;
   EXCEPTION WHEN unique_violation THEN
      -- insert violated unique constraint, keep looping
   END;
   loop_ct := loop_ct + 1;
END LOOP;
END
$func$  LANGUAGE plpgsql;

Major points

GET DIAGNOSTICS instead of FOUND, as explained at the outset.
Don't start another block around the UPDATE. Expensive and no use.
I added a feature: on success, the query returns the number of rows affected - at no additional cost since we get the row count anyway. May or may not be useful.

The rest is unchanged, just reformatted.

I have to add a warning about SQL injection. Passing code as text is very susceptible. The code cannot be sanitized. A function like this must be handled with care. Certainly should not be created with SECURITY DEFINER. Related answer:

Issue with composite type in an UPSERT function


Better approach to optimize query We have a previously designed query that functions fairly well but I am thinking there has got to be a better way of performing the following.  Here is the query...

WITH CTE1 ( COL1, COL2)
AS
  (SELECT TOP 10 COL1, COL2
   FROM TABLE 1
   WHERE CREATEDATE < DATEADD(HOUR, -1, GETDATE())
   ORDER BY COL2
  ),
WITH CTE2 ( COL1, COL2)
AS
  (SELECT COL1,MAX(COL2)
   FROM CTE1
   GROUP BY COL1
  )
SELECT TABLE2.*
FROM TABLE2
WHERE TABLE2.IDCOL IN
   (SELECT COL2
    FROM CTE2)

So CTE1 can have results like this that require the grouping in CTE2.
CTE1 results look like this:

COL1    |   COL22
_________________
10005   |  500
10005   |  501
42344   |  502
12345   |  503

SO CTE2 is designed to consolidate CTE1 down to the single row by grouping to

COL1    |  COL2
_________________
10005   |  501
42344   |  502
12345   |  503
ETC.

I am trying to figure out if I can accomplish this same task without taking CTE1 into CTE2 and then using CTE2 results in the SELECT query.

As I understand your question (and let me know if I have misunderstood your requirements):

Using the TOP 10 rows from Table1 where they fall into some date criteria and are ordered by COL2
Get the MAX(COL2) per COL1 group
Then, select all columns from Table2 where IDCOL = MAX(COL2) from the previous grouping.

I think this solution addresses all of those items.  Basically, CTE2 is encapsulated into CTE1.

--Demo setup
Declare @Table1 TABLE (COL1 INT, COL2 INT)
INSERT INTO @Table1(COL1, COL2) VALUES

(10005,500),
(10005,501),
(10005,521),
(10005,521),

(42344,502),
(42344,550),

(12345,503),
(12345,575),
(12345,575)

Declare @Table2 TABLE (IDCOL INT,OtherStuff varchar(10))
INSERT INTO @Table2(IDCOL,OtherStuff) VALUES
(575,'Other1'),(575,'Other2'),(580,'Other3')

--The solution
;WITH CTE1 (COL1,COL2,Rn)
AS (
    SELECT TOP 10 COL1
        ,COL2
        ,ROW_NUMBER() OVER (
            PARTITION BY COL1 ORDER BY COL2 DESC
            ) AS Rn
    FROM @Table1
    --   WHERE CREATEDATE < DATEADD(HOUR, -1, GETDATE())
    ORDER BY COL2
    )
--select * from CTE1 order by col1, rn
SELECT T2.*
FROM CTE1 c1
JOIN @Table2 T2
    ON t2.IDCOL = c1.COL2
        AND c1.Rn = 1

Rows coming out of CTE1 have an additional rn column.

| COL1  | COL2 | Rn |
|-------|------|----|
| 10005 | 521  | 1  |
| 10005 | 521  | 2  |
| 10005 | 501  | 3  |
| 10005 | 500  | 4  |
| 12345 | 575  | 1  |
| 12345 | 575  | 2  |
| 12345 | 503  | 3  |
| 42344 | 550  | 1  |
| 42344 | 502  | 2  |

The last part of the query joins Table2 with the CTE1 where only the rows from CTE1 have rn = 1 and Table2 (IDCOL) = COL2 value for rn = 1.

| IDCOL | OtherStuff |
|-------|------------|
| 575   | Other1     |
| 575   | Other2     |

Ultimately, you will have to judge whether it is a Better approach to optimize query.


Move mssql db Amazon RDS to EC2 I have my 200 gb database.

I want to move EC2 from RDS.

I work on a research to prevent the problems that you may see,but it has not been done before.

There may be problems;

*Security weaknesses,

*High availability,

*Restore process takes a long time,

*In RDS, the time zone will be UTC, and UTC + 3 in EC2.

What other problems might occur from this problems.

Thank you.

Let's step back a little: you're basically switching from a SQL Server that someone else manages, to managing your own server.

Q: What are the security challenges involved with running your own server?

You can get hacked, or you can incorrectly set permissions to let strangers access your data and publish it. Here are some resources to get you started:

Microsoft's SQL Server 2008 Compliance Guide white paper - it's very much still relevant today
The book Securing SQL Server by Denny Cherry

Q: What are the high availability challenges involved?

Amazon RDS for SQL Server uses database mirroring for high availability. In your own EC2 instances, you can choose whether you'd like a similar level of protection with mirroring or Always On Availability Groups, or if you'd like to save money and get less protection.

You didn't ask about patching, but I'd bundle that in here: the burdens of patching will now be on you rather than Amazon.

Q: Can I make my migration restores go faster?

Right now, Amazon RDS only gives you access to full backups. Your backup/restore migration timeline is:

Shut down your app
Back up RDS to S3
Restore the S3 file backup to EC2 SQL Server
Start your app again

That's going to result in long downtimes for a 200GB database. To shorten it, you can use tools like Red Gate's Data Compare or build your own comparison tool to detect what changes have been made to your database. For example, on one project, the developers added LastUpdated timestamp fields to all the tables, and maintained those with triggers. Then, after our restore, we could shut down the apps, copy across the specific records that had been updated, and go live with only a few minutes of downtime.

Q: What time zone should I use for my EC2 servers?

Just as RDS was configured with UTC, so should your SQL Servers. Just set your EC2 boxes to be in UTC time zone regardless of their location, and then you won't have problems when you fail over from one region to another.


Oracle conditional statements I am making a report, and I am trying to do following:
If the value of the field is NULL, output empty string. Another words, I don't need the word "null" to be output to the user, just empty string. Is that possible to write such a SQL query? How?
I don't even know how to formulate a google search request for this case, so I am sorry for stupid question.

In Oracle, empty string '' when assigned to a varchar2 type is NULL for all intents and purposes. In my SQLdeveloper, on a 12c database, the following query results in a (null) for every column. If you display to user a blank (a space, ' ') it would surely be displayed by all tools as a blank.

SELECT NULL, '', CAST('' as varchar2(1)), CAST('' as char(1)), NVL(null,'') FROM dual;

Why Oracle violates the SQL92 standard


Selection of first and last in non grouped item in SQL Server Here's my table structure

Id  ToolName  ToolKey   ToolStatus
-----------------------------------
1    XXXX      123      Running
2    XXXX      123      Completed
3    yyyy      134      Running
4    yyyy      134      Completed

I have grouped the above statement by toolname and toolkey.Here's my query

SELECT
    TOOLNAME, ToolKey
FROM
    TOOLS
GROUP BY
    TOOLS.TOOLNAME, TOOLS.ToolKey

I need to have columns ToolStatus1 with value Running and ToolStatus2 with value Completed. If not completed means it should have null.

But in SQL if I add toolstatus in grouping, grouping will not be done.

In simple what I expect is like First() and last() as I will be having only two rows.

Let me know if unclear

Here are a couple of options:

   SELECT TOOLNAME,
           TOOLKEY,
           MAX(CASE WHEN Toolstatus = 'Running' THEN 'Running' END) Toolstatus1,
           MAX(CASE WHEN Toolstatus = 'Completed' THEN 'Completed' END) Toolstatus2
    FROM TOOLS
    GROUP BY TOOLNAME,
             TOOLKEY

It is eliminating nulls with the aggregate MAX() function in this code, if that's a concern, you may want this:

SELECT TOOLNAME,
       TOOLKEY,
       [Running] Toolstatus1,
       [Completed] Toolstatus2
FROM (SELECT TOOLNAME,
             TOOLKEY,
             TOOLSTATUS
      FROM TOOLS) AS src
PIVOT
(
MAX(TOOLSTATUS)
FOR TOOLSTATUS IN ([RUNNING], [COMPLETED])
) AS pvt


Why is my update affecting twice the number of records than expected? I have the following query:

update Table1
set id=2
where nbr in (select nbr from Other.dbo.table)

The "select nbr from Other.dbo.table" has 500 records.  Yet when I run the update, it says I am updating 1,000 records.  I have checked table1, and there are only 500 rows that match the filter.

select * from table1 where nbr in (select nbr from Other.dbo.table)

I tried changing the query to be a join and using a temp table to store the filter list, but I receive the same results.

Why am I updating 1000 records when only 500 match?

There is an update trigger on Table1.

An update trigger on table1 causes the UI to return double the amount of records. The 500 records are being updated. However, each update causes a new record to be inserted into an audit table. The UI returns the count of the total rows affected for the query statement, not just the update itself!


TempDB internal space usage slowly growing from service broker Am running SQL Server 2012 (11.0.6540.0) in a AO high availability group with one other server.

tempDB usage usually sits around 819MB  (averaged over a week from 2 weeks ago) but since we did a fail-over and patch cycle the tempDB started growing gradually (no DDL changes have been made in this time).
We've just done a test fail-over and moved the tempDB off the joint SAN drive that both machines have access to, (we don't want one piece of hardware that can bring down both our primary and secondary replica).

The most recent failover and Instance restart (to move the tempDB) saw the tempDB usage jump to 6348MB used and its slowly growing.

The growth is all on the internal objects which can be seen using:

SELECT   top 5 session_id, request_id,
         SUM(internal_objects_alloc_page_count) AS request_internal_objects_alloc_page_count
FROM     sys.dm_db_task_space_usage
GROUP BY session_id, request_id
ORDER BY request_internal_objects_alloc_page_count DESC

Looking into it, it all is running from spid 35 and 32 which are both service broker related.

I've had a look around and there's talk about this being due to having a WITH CLEANUP on an end conversation but the word cleanup only ever appears in comments so I'm confident that this isn't the issue.

When the instance came back up and took over the primary node the service broker wasn't working so I needed to cycle all of the queues to disabled then enabled state (we've had to do this once before a a year ago but didn't see this issue).

You can see here the use on tempDB as it currently stands over the past 4 days. the drop off this morning is the instance restart and fail-overs.

Does anyone know what I'm missing to get this back under control.

TLDR; Check for conversations being left open completely.

In our system we re-use conversations and have a table dedicated to holding these conversations that are usable, however the dev team setup a new service broker without my knowledge ages ago while I was off, didn't set up these conversation points and didn't set any thresholds on the alerting.

When the new system has been turned on the conversations are being opened but not closed properly and since there aren't any in the pool it is just creating a new conversation (we reached 7.1 million conversations for one service broker)

My steps for fixing was to create and record the 20 conversation handlers that I require for that service broker and record them into our table. This stopped the growth of the tempDB to stop the risk of the DB going down.

Then came the long process of closing off all the un-used conversations

select
    se.[conversation_handle]
from
    sys.conversation_endpoints se with (nolock)
    left outer join [queue].[maintained_Conversations] qh with (nolock) on qh.[conversation_handle] = se.[conversation_handle]
where
    qh.service_name is not null
    and se.far_service = @service

Gives the list of all the IDs cursor through those values and simply run END CONVERSATION @id; on each of them.

After the process has finished the temp DB space will loosen up (Note it does not do it as you close them, it seems to do it in large chunks when you're no longer working on creating / ending them (I cant guarantee this is how it works, just what I observed after stopping mid process and tempDB getting some space back)


Fact table foreign keys null? I am new to data mart design and need to clear a few concepts.

I have read up a bit on dimension modelling where I see that fact tables store foreign key references to dimension tables.

Now suppose I have a phonenumber dimension table and a phone_extension dimension table.
(These tables have different details because of which I cannot combine them)

As I understand both these dimension tables will have integer primary keys for better performance, and the fact table will have its own integer primary key and also store foreign key references to these dimension tables.

But suppose I have a situation that not all phone numbers have a  phone_extension related to them. ( some phone numbers need not have an extension)

For phone numbers that have an extension, the fact table would have foreign key references to both the dimension tables, but how do I capture the situation where there are only phone numbers and no extension to them (and vice versa i.e extension with no phonenumbers)?

Should I capture such information with the phonenumber FK in the fact table having a value and phone_extension foreign key null?? Or are such non related objects not recorded in fact tables?

Also I need to generate report of this data mart. So do I start by querying the fact table and retrieving the dimension key values or report straight from the dimension table?

Thanks for your time in reading this!!
Appreciate any help!!

You can leave the FK to some dimension tables as NULL if those dimensions are not known or not applicable.  You just have to remember to use outer joins when you do your reporting query.

Alternatively, some people create a "none" and/or an "n/a" dimension record for data mart dimensions and then populate fact table FKs to point at these rather than using NULLs.  People who do this like this approach because they have an aversion to outer joins.

People who use NULL FKs in fact tables usually have an aversion to people who have an version to outer joins.  ;)  (in other words, this is a stylistic issue which can generate religious wars)

I say do whichever you prefer, but pick one approach and stick to it fervently.


Sql Script takes much longer than estimated In order to clean up a very large table holding binary Files in IMAGE Cols, I created the following sequence (based on this: Article) to get rid of a part of those image Blobs quickly:

Select * into temp table where (filtering a small part I want to keep)
Select the rest of rows WITH a NULL value for the BLOB into same temp table.
Truncate the original blob table
copy all records back from the temp table to the original table.

I did a test run on a local machine (notebook) and Step No. one took about 20 seconds for about 2 Milion records.

The whole script took about 4 minutes while the original table held about 2.5 Milion REcords.

In a staging server env. step No. one meanwhile takes > 25 Minutes to copy 3.2 Million records!!! And SSMS actually stopped interacting with me. I estimated (using calc.exe) it might take about 1 Minute...

Now I wonder what is wrong...?

TY

I figured it was the Revovery Model that was left set to FULL. As the article linked in the question says,  it is required to change it to simple of bulk: "The recovery model SIMPLE and BULK_LOGGED perform "best" contrary to the recovery model FULL."
So, after setting it to simple the process performed within couple of minutes.


Migrate all databases from one SQL Server 2005 to another We have a production SQL Server 2005 server on which we are hosting 200 databases.

We want to host these databases on our new server. On new server SQL Server 2005 is installed and is ready.

I want to know the best and the most convenient way to migrate the whole data includes:

Security and permission
Users and memberships
All of the databases and ..

In one word I want to have exactly the same copy of the current SQL Server 2005 on our new instance on the new server.

I will be thankful if you guide me on this case.

Best regards

Disclaimer : SQL Server 2005 is out of mainstream support. - extended support ends for sp4 on 04/12/2016

Migration always have a downtime. So depending on how much your downtime  is, you can go with setting up either :

Logshipping ( I have used this method and it reduces the downtime considerably)

a. Set up logshipping from the source to destination server as usual. Keep the sync time 1 min (which is minimum).

b. On the day of migration, make sure the logbackups are in sync.

c. Stop all the logshipping jobs and then take the final tail log-backup and put the primary databases in no-recovery mode (this is just in case you need to go back to primary).

d. restore the tail log backups on the secondary with recovery bringing all the secondary databases online.
Database Mirroring : I do not recommend mirroring for migrating 200 databases as you will easily exhaust the worker threads as described in this KB article.. If there were less databases, then this would be a viable option.

If you have enough downtime then I would recommend

backup / restore method (as opposed to detach / attach) as if something goes wrong, you atleast have a good backup to restore back. this script will get you started with moving all the logins and databases. Just modify it to use backup / restore (or take full backups before running the script - as it uses detach/attach method).

Things you can do prior to migration :

Refer to this answer for migration steps. Some may apply to your scenario as well.
script out all the jobs / ssis packages, etc --OR --  As suggested by @shanky, you can restore the system databases from the old server to new server before hand - that will give you all the logins, jobs, etc on the new server.
turn on Instant File Initialization on the new server.
Have multiple tempdb data files with equal size.
Enable Trace Flag 1118 -  to assist in TEMPDB scalability by avoiding SGAM and other allocation contention points.
Configure max and min memory correctly. Especially Max memory away from default.
Properly adjust the MAXDOP settings. Refer to https://dba.stackexchange.com/a/36578/8783 for more details.
Best is to install sp_Blitz from Brent Ozar.  Run it and address the critical and high priority issues reported by it.
You can even use SQL Power Doc from kendalvandyke - SQL Power Doc works with all versions of SQL Server from SQL Server 2000 through 2012, and all versions of Windows Server and consumer Windows Operating Systems from Windows 2000 and Windows XP through Windows Server 2012 and Windows 8. Also useful for Planning upgrades - see what hidden features are in use on an instance.

Edit:

You can use powershell to migrate your all databases from one server to another as described at Use PowerShell to Migrate Entire SQL Server Instance (db, logins, jobs, etc)

  Migrates databases with simple or complex file structures via backup & restore or detach & attach. Also migrates users with passwords, SIDs, server & db permission sets, server & db roles, server configuration settings, SQL Agent objects, user objects in systems databases.


Conditional compilation of SQL Server stored procedure Short version: Is there some way to conditionally compile chunks of TSQL code in a SQL Server Data Project using SQL Server Data Tools for Visual Studio 2010?

I'm using SQL Server Data Tools in Visual Studio 2010 to work on an experimental SQL Server Express database. The eventual destination if things work well would be an enterprise SQL Server platform. I have both a 2008 instance on one box and a 2012 instance on another, because my company is in the process of migrating from 2008 to 2012 for the many enterprise databases as well.

In other programming languages I have used, preprocessor directives make it easy to conditionally compile parts of a code base. The most common uses for this are to have different code for different platforms in restricted sections or to excluded debugging output code from release builds.

Both of these could be very helpful in some store procedures I'm working on. Is there anything like this available? I know I can use sqlcmd variables to swap out specific values during deployment, but I can't figure how to use that to include or exclude subsequent chunks of code.

Example:

#IF $(DebugVersion) = 'True'
    -- A bunch of useful PRINTs and what not
#ELSE
    SET NOCOUNT ON
#ENDIF

#IF $(SSVersion) = '2012'
    SET @pretty_date = FORMAT(@some_date, 'dddd, MMM dd, yyyy')
#ELSE
    SET @pretty_date = CAST(@some_date AS nvarchar(12))
#ENDIF

I'm not aware of this being possible with SSDT unfortunately.

Depending on how big the project is and how many procedures you intend to enhance with 2012 goodies, it may be manageable with Composite Projects.

  SSDT can combine a database project with one or more referenced
  database projects or dacpacs to describe a single composite database
  schema.   Using a composite project allows a large database to be
  broken down into more manageable chunks, allows different people or
  teams to have responsibility for different parts of the overall
  schema, and enables reuse of database object definitions in multiple
  databases.

The notion would be to have a base project, containing the common object definitions and version specific projects for procedures that used new features. The 2012 project would reference the base project and a compile/build would combine objects from both.

The PITA would be that you can't override an object in the base project with an object in a composite, so you would have to maintain base, 2008 & 2012 projects. When you wanted a 2012 version of a particular procedure, you would have to remove it from base and create a version in both 2008 & 2012 projects.


What exactly is metadata? How is metadata different than regular data? As I understand, metadata is data about data- so I think of analytics; is my simple definition correct? How is metadata different than data?  I'm simply asking to broaden my understanding-thanks!

Metadata is data that describes your data.  For example "Kenneth" is my name.  It would be stored in a column called FirstName (for example).  The metadata would be things like

Variable length string with a max length of 50
First name of the user
Last updated on
Last updated by

Analytics is more

How many Kenneth's are there in the data.
What is the average number of questions by someone with the first name of Kenneth.


Is there anyway that I can manually create a PID for the MySQL instance? I am running MySQL on a linux server.  I have taken a copy-back mysql enterprise backup from another linux full backup.  Ever sense then I have had problems getting MySQL to start.  The problem is that MySQL is not updating the PID file, it's not even create a PID file.  Is there anyway that I can manually create a PID for the MySQL instance?

One work around that I have been able to implement is the using the mysql-debugger which creates its own pid, but when I stop the debugger it still won't create/update the PID file.

No, you do not want to go there. Why ?

The mysqld_safe script is responsible for creating and destroying the PID file

You can see it when you grep for it

$ MYSQLD_SAFE_SCRIPT=`which mysqld_safe`
$ grep -n pid_file < ${MYSQLD_SAFE_SCRIPT}
26:pid_file=
198:      --pid-file=*) pid_file="$val" ;;
638:if test -z "$pid_file"
640:  pid_file="$DATADIR/`hostname`.pid"
642:  case "$pid_file" in
644:    * )  pid_file="$DATADIR/$pid_file" ;;
647:append_arg_to_args "--pid-file=$pid_file"
720:if test -f "$pid_file"
722:  PID=`cat "$pid_file"`
731:  rm -f "$pid_file"
732:  if test -f "$pid_file"
735:$pid_file
782:  rm -f $safe_mysql_unix_port "$pid_file"   # Some extra safety
796:  if test ! -f "$pid_file"      # This is removed if normal shutdown
834:    numofproces=`ps xaww | grep -v "grep" | grep "$ledir/$MYSQLD\>" | grep -c "pid-file=$pid_file"`
840:      PROC=`ps xaww | grep "$ledir/$MYSQLD\>" | grep -v "grep" | grep "pid-file=$pid_file" | sed -n '$p'`
859:log_notice "mysqld from pid file $pid_file ended"
$

What you should do is

service mysql stop
... delete the pid file if it is still exists after shutdown
service mysql start

If you have to restore backups from MySQL Enterprise, make sure there is no PID file.

Preferably, you could setup MySQL Replication and do the backups on the Slave. Then, when it is time for backup, run

STOP SLAVE;
SET GLOBAL innodb_fast_shutdown=0;
service mysql stop (The pid file should get deleted by mysqld_safe)
Make your backup
service mysql start (The pid file should get created by mysqld_safe)

CAVEAT

If you ever have a problem with service mysql stop taking forever and still not shutting down mysqld, this quickly indicates that the socket file is missing. PID file will be out of whack as well.

In that happens, do a TCP/IP shutdown of MySQL

First do this

mysqladmin -uroot -p -h127.0.0.1 --protocol=tcp ping

If this echoes

mysqld is alive

then you can run

mysqladmin -uroot -p -h127.0.0.1 --protocol=tcp shutdown

GIVE IT A TRY !!!


SAN Storage & Server for Oracle Replication I need a 2-3 TB storage server group for Oracle Database.  We want SAN Storage & Server for Oracle Replication, but not Microsoft Storage Server.

For example : HP DL380 G6 as a server and EMC CLariion CX4 120 as a SAN Storage etc...

We have an option, but want some others.  What do you recommend for a Server & for SAN Storage (Make & Model) to use for Oracle Replication?

If you have already got an EMC then SRDF is the obvious choice, tho' Veritas Volume Replicator may be an option. SAN is not something you usually buy "off the shelf" the first time, the vendor will work with you to establish your requirements, both for performance and resilience. Other vendors you may which to consider are HDS, 3Par and NetApp.

However my recommendation is to not use the storage for this, use Data Guard because a) it's free once you already have paid for an oracle license and b) it enables Active Standby, something that I am very excited about myself.