Was Management Studio removed from SQL Server 2016 installation media? I just tried installing SQL Server 2016 Enterprise but I noticed that Management Studio was not installed by default. I also noticed there is a separate link to SSMS in the installation guide which points to https://msdn.microsoft.com/en-us/library/mt238290.aspx

What was the reason for this decision?

The following is noted on the above URL:

  This generally available release of SSMS is free and does not require a SQL Server license to install and use.

Perhaps, this is it?

SQL Server Management Studio is now available on its own page:
SQL Server Management Studio - Release Notes

  Welcome to our generally-available release of SQL Server Management Studio! This release of SQL Server Management Studio (SSMS) is a stand-alone install outside of the SQL Server release. Our goal is to update this frequently with new functionality, fixes, and support for the newest features in SQL Server and Azure SQL Database [...]

  Download SQL Server Management Studio
  Previous SQL Server Management Studio Releases

It is free and no SQL Server licence is required anymore:

  This generally available release of SSMS is free and does not require a SQL Server license to install and use.

It is now updated on a monthly basis since June 2016: Announcing SQL Server Management Studio – July 2016 Release


Locks and dirty writes for SQL dbs Lets say I have two transactions, t1 and t2, that write to the same two rows, r1 and r2. I understand a kind of “dirty write” would be if t1 overwrites t2 on r1 last while t2 overwrites t1 on r2.

Is it possible for such a situation to arise? Does it depend on the isolation level used? What would prevent such a problem?

Isolation levels are for reading data. Not sure what you mean by "dirty write". Any RDBMS wont allow dirty writes - since they follow ACID properties.

A write operation will always take X (Exclusive locks) till the transaction is completed. Multiple concurrent transactions will use MVCC - sql server uses locking and row versioning since 2005.

Read up - Paul White's series on Isolation levels.


Permissions for SSIS/SQL Server agent jobs for multiple user groups What permissions should I use for Active Directory groups to be able to deploy and then run/schedule their own jobs only? For example, I have 3 folders in the Integration Services catalog. Dept_A, Dept_B, and Dept_C. Each AD group should be able to DEPLOY to their own department's folder, and also create/schedule/run jobs that call these packages. I can control the permissions for deployment to those 3 folders by folder/project permissions. However, when it comes to the SQL Server Agent, things don't seem fine-grained enough. There are database roles in msdb: SQLAgentOperatorRole, SQLAgentReaderRole, and SQLAgentUserRole. However, I'm not seeing how I can limit security to keep each AD group only able to run their own packages from a job.

I'm not seeing how I can limit security to keep each AD group only able to run their own packages from a job.

If users are in different AD groups and their visibility into the SSIS catalog is properly restricted, then you should be able to assume that only the appropriate SSIS packages will be selected when creating new jobs or modifying existing jobs.  So what you really want to know comes down to your question of...

  What permissions should I use for Active Directory groups to be able to deploy and then run/schedule their own jobs only?

Creation, scheduling, and starting/stopping of SQL Agent jobs are all actions that involve two things, namely the current owner of the job and the account of the user logged in regards to their privileges via those three roles you mentioned.

I would solve this by creating one login per department and update the "owner" of existing jobs to the login which corresponds to the department that is responsible for each respective job.  Going forward, jobs are only assigned one of those logins.

For example, if each of these three logins belongs to the SQLAgentUserRole role, anyone using the login for Dept_A will only be able to see and modify jobs only owned by that login.  Jobs owned by logins associated with Dept_B and Dept_C won't even be visible when utilizing the login for Dept_A.  Credentials and SQL Agent proxies can then be setup in order to give any extra permissions to that login if needed (SSIS steps execute under the service account but any TSQL steps will use the job owner).  I'm not completely sure if a credential and proxy can be setup to allow users to update jobs as their assigned login/owner, though.

Another option is to create stored procedures that wrap around the system-supplied stored procedures to conduct actions.  Also see the social MSDN thread here.  The downside with this is that I'm not sure how GUI-friendly this approach is and you'll need to create a wrapper for each stored procedure that does something (sp_update_job, sp_start_job).  It will force all job changes to be scripted out which is a good practice in and of itself for things like review, troubleshooting, and documentation of changes but the trade-off is that more work happens for the developers.


Filter customers with negative balance across multiple accounts The company I'm working for allows their customers to have multiple accounts. It's also possible for an account to be in the negative. So, their customer balance table looks like this:

┌──────────────┬─────────┬─────────┐
│ Customer No. │ Account │ Balance │
├──────────────┼─────────┼─────────┤
│           1  │       A │    1.00 │
├──────────────┼─────────┼─────────┤
│           1  │       B │   -2.00 │
├──────────────┼─────────┼─────────┤
│           1  │       C │    3.00 │
├──────────────┼─────────┼─────────┤
│           2  │       A │    4.00 │
├──────────────┼─────────┼─────────┤
│           2  │       B │   -5.00 │
├──────────────┼─────────┼─────────┤
│           2  │       C │   -6.00 │
├──────────────┼─────────┼─────────┤
│           3  │       A │    7.00 │
└──────────────┴─────────┴─────────┘

I'm trying to get a sum total of the balance column, excluding any customers that have a negative total balance. So, the result from the table above should be 9.00 -- Customers 1 and 3 have a positive total balance, so all their accounts are included, but customer 2 has a negative total balance, so none of their accounts are included.

(Note: This is a once-a-year query, so performance isn't really an issue.)

This is a very simple query. To get the total balance of each customer (with non-negative total):

SELECT customer_no, SUM(balance) AS total_balance
FROM accounts
GROUP BY customer_no
HAVING SUM(balance) >= 0 ;

To get the total sum of all the above customer balances:

SELECT SUM(total_balance) AS super_total_balance
FROM
  ( SELECT SUM(balance) AS total_balance
    FROM accounts
    GROUP BY customer_no
    HAVING SUM(balance) >= 0
  ) AS t ;

or with a rather cryptic variation:

SELECT TOP (1)
       SUM(SUM(balance)) OVER () AS super_total_balance
FROM accounts
GROUP BY customer_no
HAVING SUM(balance) >= 0 ;


SQL query to find player with maximum runs I have the following tables with me :

Table 1 Title : Runs

                    +------------+---------+
                    |    P_ID    |  Runs   |
                    +------------+---------+
                    |      1     | 100     |
                    |      2     | 45      |
                    |      3     | 35      |
                    |      1     | 90      |
                    |      2     | 85      |
                    +------------+---------+

Table 2 Title : Player

                    +------------+---------+
                    |    P_ID    |  Name   |
                    +------------+---------+
                    |    1       | Dhoni   |
                    |    2       | Sehawag |
                    |    3       | Sachin  |
                    |    4       | Dravid  |
                    |    5       | Kohli   |
                    +------------+---------+

I have to find the player name whose sum of runs is maximum.

I am new to MySQL and not able to figure out the correct/most efficient  query for that, please help.

With the table of separate runs, making the sums will mean reading entire table each time so for performance it would be good to have the sums precomputed somewhere, maybe even in Player table, with updates either periodically (daily, each hour) or on insert/update in Runs table (trigger, ORM event or other ways).

But that may be not possible for your case, so query to compute them and find the max:

select
  sum(r.runs) total,
  r.p_id,
  p.name
from Runs r
join Player p using(p_id)
group by r.p_id
order by sum(r.runs) desc
limit 1;

Can be seen in action at http://sqlfiddle.com/#!9/27820/2

When two players have the same max, it will pick one of them (probably the one with lower p_id, but that does not have to be true). If it is not OK, define further requirements for this.

It would be good to have index on (p_id, runs) in Runs table so summing per player can be done fast, but then to find the max a filesort is still needed, so if you plan to have hundreds of thousands or millions of rows in Runs table, then you will probably really want to precompute it.

Players table only needs index in p_id, which is easily satisfied by it being primary key which seems quite natural.


idiom to treat empty result set as zero This is obviously a SSCCE.

I have an inventory table that holds the number of items (numOfItems) in a warehouse at any given date (inventoryDate). Both are integers to keep things simple:

CREATE TABLE inventory(inventoryDate INTEGER, numOfItems INTEGER);
ALTER TABLE inventory ADD PRIMARY KEY (inventoryDate);

Now I have some entries:

INSERT INTO inventory(inventoryDate, numOfItems) VALUES(1,250),(2,275)

What the above says is that on time 1 there were 250 items in the warehouse and on time 2 there were 275.

Now I want to test whether, at the latest date, the number of items in the warehouse exceeded 1000:

SELECT 1000<(SELECT numOfItems FROM inventory ORDER BY inventoryDate DESC LIMIT 1)

The above seems to work. However, on the edge case that there are no entries at all in the inventory table it's not working:

DELETE FROM inventory;
SELECT 1000<(SELECT numOfItems FROM inventory ORDER BY inventoryDate DESC LIMIT 1)

... returns:

?column?
(null)

I would like to treat the edge case where there are no entries at all as denoting zero (0) items. I know I can always initialize the inventory table with a fake first entry with a value of zero but like I said this is an SSCCE (in my real case this table is really a view which I don't want to modify).

So I end up writing the query like this:

WITH cte AS (SELECT numOfItems FROM inventory ORDER BY inventoryDate DESC LIMIT 1)
SELECT 1000 < (
CASE
WHEN NOT EXISTS(SELECT 1 FROM cte) THEN 0
         ELSE  (SELECT numOfItems FROM cte)
END)

The above does seem to work. But, is there a more idiomatic way? Something like a COALESCE but for an empty result set instead of null?

I think you want coalesce outside the subquery expression.

SELECT 1000 < coalesce((SELECT numOfItems FROM inventory ORDER BY inventoryDate DESC LIMIT 1), 0);


Query Amount of entries per column from multiple tables + provide table name I have five tables with all having the same column (objart):

I queried for the number of entries and combined the result with UNION, which worked well.

Two questions:

1) Is there a shorter/ better way to do it? Other possibilities than UNION?

2) The selection provides me the "objart"-entry and how many rows of this entry are in the table(s). How can I add the table name from which table the specific "objart" entry is derived? (Up to now I don't know whether a "objart" entry occurs in just one OR in more tables)

SELECT objart, COUNT(*) AS "Number of objects"
FROM "SchemaA".table_1
GROUP BY objart
UNION
SELECT objart, COUNT(*)
FROM "SchemaA".table_2
GROUP BY objart
UNION
SELECT objart, COUNT(*)
FROM "SchemaA".tanle_3
GROUP BY objart
UNION
SELECT objart, COUNT(*)
FROM "SchemaA".table_4
GROUP BY objart
UNION
SELECT objart, COUNT(*)
FROM "SchemaA".table_5
GROUP BY objart
ORDER BY objart;

A UNION (or perhaps a UNION ALL, for reasons that I will note below) is a reasonable way to do this. There are a couple of ways to write the UNION. You did the first, which is to aggregate at each individual table level and then UNION the aggregated results. Is there a possibility that two different tables will have the same object in them? That is, something like this:

"Schema".table_1
OBJART
'Renoir'
'Picasso'

"Schema".table_2
OBJART
'Rodin'
'Picasso'

If this is the case, your query above would actually discard one of the sub-results because of how UNION scans the resultsets it is UNIONing for duplicates and discards them . In other words, this subquery

SELECT objart, COUNT(*) AS "Number of objects" FROM "SchemaA".table_1

Will produce this resultset given the data above:

objart   Number of objects
'Renoir'   1
'Picasso'  1

And this query:

SELECT objart, COUNT(*) AS "Number of objects" FROM "SchemaA".table_2

Will produce this resultset:

objart   Number of objects
'Rodin'   1
'Picasso'  1

However, because UNION scans input resultsets and discards duplicates, you will lose one of the 'Picasso' rows and the overarching UNION query will result in this:

objart   Number of objects
'Renoir'   1
'Picasso'  1
'Rodin'    1

I think what you probably want is this:

objart   Number of objects
'Renoir'   1
'Picasso'  2
'Rodin'    1

Also, a sidenote -- can objart ever be NULL? If so, and if you don't want to count NULL rows, I wouldn't use COUNT(*) because that counts all of the rows in the table. COUNT(objart) would work better as it will only count non-NULL values. Yes, it might work either way but to be safe I always like to be very explicit that I am telling the DB to count the number of non-NULL values for a particular column and not the number of rows in a table overall. e.g.:

SELECT objart, COUNT(objart) AS "Number of objects" FROM "SchemaA".table_1

So, there is another way that might be a bit easier. UNION ALL all of the data in the underlying tables and then compute the aggregate on top of them. Here is the query for that:

WITH subquery_art AS
(SELECT objart FROM "SchemaA".table_1
UNION ALL
SELECT objart FROM "SchemaA".table_2
UNION ALL
SELECT objart FROM "SchemaA".table_3
UNION ALL
SELECT objart FROM "SchemaA".table_4
UNION ALL
SELECT objart FROM "SchemaA".table_5)
SELECT objart, COUNT(objart) AS "Number_of_objects"
FROM subquery_art
GROUP BY objart
ORDER BY objart;

If you want to add the table name that you're sourcing data from, here's how to do it:

WITH subquery_art AS
(SELECT 'table1' as table_name, objart FROM "SchemaA".table_1
UNION ALL
SELECT 'table2', objart FROM "SchemaA".table_2
UNION ALL
SELECT 'table3', objart FROM "SchemaA".table_3
UNION ALL
SELECT 'table4', objart FROM "SchemaA".table_4
UNION ALL
SELECT 'table5', objart FROM "SchemaA".table_5)
SELECT table_name, objart, COUNT(objart) AS "Number_of_objects"
FROM subquery_art
GROUP BY table_name, objart
ORDER BY table_name, objart;

EDIT: Fixed some misspellings of 'Renoir'


Beginner table design I have been coding in C# for a couple of years where I usually try to follow the 'single responsibility principle' which states that each class should ideally be responsible for only 1 goal. Now I want to design a small database for my app users. My initial go at this is as follows:

I am not sure how the single responsibility principle applies to the database world. Should I split this table into 3 tables, one which holds only the Facebook ID, and another for all high score related fields (HighScore, AllTimeHighScore, ScoreDate, ...) and a 3rd for all medal fields, and maybe a 4th table for just the Expert Mode Score, or keep it as is? I know this is probably overkill, but I would like to know the basic principles regarding what can be grouped in a single table.

Note: I would like to take into consideration future proofing. If in the future I decide I want to add a 'platinum medal' field, would that change at all how I would want to design my above table now?

There are a couple of ways you could model this database.  There is nothing wrong with the way you have it right now.  However, you could normalize the table structure something like:

CREATE TABLE Player (
   facebookId BIGINT NOT NULL,
   XP int NULL,
   PRIMARY KEY (facebookId)
)

CREATE TABLE Score (
    facebookId BIGINT NOT NULL FOREIGN KEY REFERENCES Player(facebookId),
    score INT NOT NULL,
    scoreDate2 DATETIME NOT NULL,
    PRIMARY KEY (score)
)

CREATE TABLE MedalType (
    medalType INT NOT NULL IDENTITY(1,1),
    medalName VARHCAR(10) NOT NULL,
    PRIMARY KEY (medalType)
)

CREATE TABLE PlayerMedal {
    medalId BIGINT NOT NULL IDENTITY(1,1),
    facebookId BIGINT NOT NULL FOREIGN KEY REFERENCES Player(facebookId),
    medalType INT NOT NULL FOREIGN KEY REFERENCES MedalType(medalType),
    PRIMARY KEY (medalId)
}

This type of structure would be a lot more flexible. To add your "platinum" medeal, you would add a row to the MedalType table.  You could then query the number of medals of that type that the player has from the PlayerMedal table.

For the All-Time High Score, use the Score table with the MAX() function to get their highest score.

Two things that I didn't quite model properly here are:

XP -- didn't know what this was, so wasn't sure where to place it.
Using facebookId as the primary key - Wouldn't do this in case you decide your system isn't going to be solely based on Facebook. Would use an unique key, with facebookId as another column in the table.


SQL Select WHERE row updated USE [RF_WORLD]
GO
/****** Object:  Trigger [dbo].[lastdeathtrigg]    Script Date: 08/20/2012 10:52:58 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:      Mauro Tamm
-- =============================================
ALTER TRIGGER [dbo].[lastdeathtrigg]
   ON  [dbo].[tbl_pvporderview]
   AFTER UPDATE
AS
IF (UPDATE(Death))
BEGIN
    SET NOCOUNT ON;
DECLARE @userserial int;
SELECT @userserial ='serial' WHERE UPDATE(Death)
Insert INTO [RF_WORLD].[dbo].[lastdeath]
(serial)
VALUES (@userserial)
END

The where part is suspicious, woudl it work like that? essentially i need it to only select the row serial value if it actually updated.

Several problems with your trigger:

You are assuming the trigger fires per row, whereas triggers fire per statement. This means you can't just assign a variable because this won't work if the update affects more than one row.
Your query to assign a variable doesn't pull from anywhere - you need to reference the inserted and/or deleted pseudo-tables.
Checking UPDATE() only tells you that the column was referenced, but it doesn't tell you whether the value has changed.

Here is how I would re-write your trigger:

-- this assumes Death is not nullable

ALTER TRIGGER [dbo].[lastdeathtrigg]
   ON  [dbo].[tbl_pvporderview]
   AFTER UPDATE
AS
IF (UPDATE(Death))
BEGIN
  SET NOCOUNT ON;

  INSERT RF_WORLD.dbo.lastdeath(serial)
    SELECT i.serial FROM inserted AS i
    INNER JOIN deleted AS d
    ON i.serial = d.serial
    AND i.Death <> d.Death;
END
GO


Is SQL Server 2008 Change Data Capture (CDC) possible NOT to keep record when inserting? SQL Server 2008 introduces a new feature for data auditing. The basic operation of this feature is as below:

When CDC is enabled on a table, SQL Server creates another table
(so-called cdc table here) that contains all columns of the original
table plus some extra meta-data column.
When insert happened, the cdc table kept a copy of record.
When update happened, the cdc table kept before changed and after
changed records.
When delete happened, the cdc table kept before changed record.
CDC use SQL Server Agent to monitor the log file and write the
changes to the cdc table
Typical size of the meta-data of each record in cdc table is about
51-bytes.

Eg. After 1 insert and 1 update actions
The original table kept 1 record
The cdc table kept 3 records (1 insert, 2 records per each update)

Assume the image size is 300KB, the total size is about 1,200 KB. It impacts the disk storage and disk IO.

Is it possible NOT to keep cdc record for a type of action (ie. insert)?

Is there any alternative for auditing Image that keeps less copy of records?

Well, do you want to keep track of all the versions of the image data, or not? If not, can't you specify only certain columns for CDC instead of just enabling all columns? That will let you keep track of the changes to other columns but ignore the image data changes.

In other words, if all you care about is the current state of the image data, you can get that from the base table. If you care about the previous states of the image data, there isn't any way to store that data without storing it somewhere.


Conditionally use computed column IS it possible that a not nullable column can be setup to either
a) add value freely with INSERT/UPDATE query
b) if value not provided on insert, to avoid null entry added default the value to that of another column?
I know that with a computed column I can set the default to be another columns value, but I cannot then override this.

An example:

CREATE TABLE test (id INT,
                   val INT);
GO

CREATE TRIGGER tr
ON test
AFTER INSERT, UPDATE
AS
UPDATE test
SET val = 123     -- custom value which must be set instead of NULL
                  -- maybe any expression
                  -- and even an output of the subquery
WHERE EXISTS ( SELECT NULL
               FROM INSERTED
               WHERE id = test.id
                 AND val IS NULL );
GO

INSERT INTO test VALUES (1,1), (2,null), (3,3);
SELECT * FROM test;
GO

id | val
-: | --:
 1 |   1
 2 | 123      -- NULL is replaced with custom value
 3 |   3

UPDATE test SET val = NULL where id = 1;
SELECT * FROM test;
GO

id | val
-: | --:
 1 | 123      -- NULL is replaced with custom value
 2 | 123
 3 |   3

db<>fiddle here


Why is adding a NOT NULL column with a default constraint instantaneous? CREATE TABLE TestTab (ID INT IDENTITY(1,1), st nvarchar(100))

INSERT INTO TestTab (st) values ('a')
INSERT INTO TestTab (st) values ('b')
INSERT INTO TestTab (st) values ('c')
INSERT INTO TestTab (st) values ('d')
INSERT INTO TestTab (st) values ('e')

INSERT INTO TestTab (st) SELECT TOP 10000 st from testtab
GO 30

ALTER TABLE TestTab ADD newcol nvarchar(10) DEFAULT 'newcol'
UPDATE TestTab SET newcol = 'newcol'  --6 sec
ALTER TABLE TestTab ADD newcol1 nvarchar(10) DEFAULT 'newcol1' NOT NULL

DROP TABLE TestTab

When I execute this test script, the ALTER with UPDATE takes 6 seconds which is understandable.

However, the ALTER with the DEFAULT NOT NULL executes instantaneously even on a much larger table. Is there any explanation on why this is instantaneous? On the physical disk, data still needs to be written to all rows right?

I tried looking at SET STATISTICS IO ON and the Query plan, however those dont seem to be available for DDL operations.

Yes, adding a column with NOT NULL and a default doesn't actually write the values to all the rows at the time of the alter, so it is no longer a size-of-data operation. When you select from the table, the columns are actually materialized from sys.system_internals_partition_columns, which prevents all the values from having to be written (until they are changed). Note that this doesn't work for all data types and requires Enterprise Edition.

Remus Rusanu explains this in more detail here:

Online non-NULL with values column add in SQL Server 2012

Also, for an ALTER at least, we still can't show you a plan because SQL Server doesn't produce one, but to see I/O, you can use SQL Sentry Plan Explorer.* This screen shot shows adding a column, c5, "online" as described above, and then another column, c6, "offline" because LOB types are not supported. You can see the I/O is mostly expressed as reads rather than writes, but what's more telling is the (invalid!) UPDATE associated with the offline alter.

If you don't have Enterprise Edition, both statements will have the secondary UPDATE attached (and the associated reads). (And if you use the free version of Plan Explorer, which doesn't get the full query call stack, you won't see the above - you will just see an empty statement tree. A paid version is required to see the full query call stack.)

Note that SQL Server will produce an estimated plan, but it's not very useful. At all. And the estimated plan for an online alter is identical to the estimated plan for an offline alter.

*Disclaimer: I work for SQL Sentry.


Confused to use join or sub-query SQL? Table1

Id Name  DemoID
1  a      33
2  b      44
3  c      33
4  d      33
5  e      44

Table2

Id DemoID IsTrue
11  33     1
12  44     1

Now we can find which DemoID is present in Table2 using below 2 queries using join and using sub-query, but which is more accurate/relevant for more number of records?

using join -

SELECT Table1.Id FROM Table1  Table1 join Table2 Table2 on Table1.DemoID  = Table2.DemoID
WHERE Table2.IsTrue = 1

using sub-query -

SELECT Table1.Id  FROM Table1  Table1
WHERE Table1.DemoID  in (select Table2.DemoID from Table2 Table2)

but which is more accurate / relevant for more number of records ?

Neither and both. The optimizer is free to rewrite subqueries as joins.

For clarity, since you don't need any columns from Table2, I'd go with a third option and use EXISTS.

This is only a benefit for the reader in this case. In other cases, writing a join may result in unwanted row duplication, which will typically lead someone to add a DISTINCT or GROUP BY to remove them, which are unnecessary with EXISTS or IN...

Of course, it's different with NOT IN. If NULLs exist in the relation, you'll get an empty result set. This can be quite confusing to beginning query writers.

As a style choice, I go with EXISTS or NOT EXISTS when I'm referencing a subquery, and IN/NOT IN when I have a list of literal values.

Hope this helps!


How to solve UTF8 invalid byte sequence copy errors on a restore, when the source database is encoded in UTF8? I was given the task to migrate a PostgreSQL 8.2.x database to another server. To do this I'm using the pgAdmin 1.12.2 (on Ubuntu 11.04 by the way) and using the Backup and Restore using the custom/compress format (.backup) and UTF8 encoding.

The original database is in UTF8, like so:

-- Database: favela

-- DROP DATABASE favela;

CREATE DATABASE favela
  WITH OWNER = favela
       ENCODING = 'UTF8'
       TABLESPACE = favela
       CONNECTION LIMIT = -1;

I'm creating this database exactly like this on the destination server. But when I restore the database from the .backup file using the Restore option it gives me some of these errors:

pg_restore: restoring data for table "arena"
pg_restore: [archiver (db)] Error while PROCESSING TOC:
pg_restore: [archiver (db)] Error from TOC entry 2173; 0 35500 TABLE DATA arena favela
pg_restore: [archiver (db)] COPY failed: ERROR:  invalid byte sequence for encoding "UTF8": 0xe3a709
HINT:  This error can also happen if the byte sequence does not match the encoding expected by the server, which is controlled by "client_encoding".
CONTEXT:  COPY arena, line 62

When I check which record triggered this error in fact some vartext fields have diacritical characters like ç (used in Portuguese, for example, "caça"), and when I manually remove them from the text in the records the error passes to the next record that has them - since when copy has an error it stops inserting data on this table. And I don't want to replace them manually one by one to accomplish this.

But it's kinda of strange because with UTF8 there shouldn't be this kind of problems, right?

I don't know how they got there in the first place. I'm only migrating the database, and I supose that somehow the database was like in LATIN1 and then was improperly changed to UTF8.

Is there any way to check if a table/database has invalid UTF8 sequences? Or any way to enforce/reconvert these characters into UFT8 so I don't run into any problems when I execute the restore?

Thanks, in advance.

Digging around the internet, I've seen that this is a pretty common problem.  The common solution is to use the plain text format dump and feed it through iconv to correct the encoding.

Here is more information about that.


Error SQLCODE=-443, SQLSTATE=38553 after applying fix pack 8 to DB2 v10.5 We have an utility which retrieves some data from DB2 (v 10.5 for AIX) database. After we had applied Fix Pack 8 to database (client and JDBC driver had been also updated) this utility didn’t work. There was the following error in log:

13:09:48,270 [INFO  ] SMU  {SCSExecute.Execute.java}  13:09:48,258 [ERROR ] org.apache.jackrabbit.core.fs.db.DatabaseFileSystem  failed to initialize file system
13:09:48,277 [INFO  ] SMU  {SCSExecute.Execute.java}  com.ibm.db2.jcc.am.SqlException: DB2 SQL Error: SQLCODE=-443, SQLSTATE=38553, SQLERRMC=SYSIBM.SQLTABLES;TABLES;SYSIBM:CLI:-514, DRIVER=3.69.66

As recommended here (http://www-01.ibm.com/support/docview.wss?uid=swg21369762) for similar cases we executed the following commands in DB2:

db2 connect to <databasename>
db2 bind db2schema.bnd blocking all grant public sqlerror continue
db2 terminate

But it didn’t solve the issues.

What could we forget to do?
How could we check that bind command executed properly?
How could we try to solve the issue or reveal its root?

What could we forget to do?

As documented in the DB2 fixpack readme, after you install on the server, db2schema.bnd needs to be bound to existing databases.

  How could we check that bind command executed properly?

If you installed the fix pack on DB2 database products that have existing databases, perform the following commands once for each database:

db2 terminate
db2 CONNECT TO dbname user USERID using PASSWORD
db2 BIND path\db2schema.bnd BLOCKING ALL GRANT PUBLIC SQLERROR CONTINUE
db2 BIND path\@db2ubind.lst BLOCKING ALL GRANT PUBLIC ACTION ADD
db2 BIND path\@db2cli.lst BLOCKING ALL GRANT PUBLIC ACTION ADD
db2 terminate

where dbname represents the name of a database to which the files should be bound, and where path is the full path name of the directory where the bind files are located, such as INSTHOME\sqllib\bnd where INSTHOME represents the home directory of the DB2 instance. db2ubind.lst and db2cli.lst contain lists of required bind files used by DB2 database products.

  How could we try to solve the issue or reveal its root?

may be the installation of the DB2 fixpack was incomplete.

For further your ref:- DB2 SQL Error occurs after DB2 patch install


Query optimization or missing indexes? I have this query to aggregate some data from t2 into t1. This is done to optimize the application I am working on so that there are less queries to the database. I chose the below approach to make sure that I don't have to update t1 twice.

The big questions is, what indexes am I likely missing here and can the query be optimized further?

update t1
set
  col1 = t2.col1_count,
  col2 = t2.col2_sum,
  col3 = t2.col3_sum
from  (
  select
    b.user_id, b.t1_id,
    coalesce(count(b.id), 0) as col1_count,
    sum(case when b.col5 = true then b.col2 else 0 end) as col2_sum,
    sum(case when b.col5 = false then b.col3 else 0 end) as col3_sum
  from t1 a
    left join t2 b on b.t1_id = a.id
  where
    b.user_id = 1
  group by b.user_id, b.t1_id
) as t2
where
  t2.t1_id = t1.id;

EDIT Adding requested information

These are my current indexes:

create index ix_t1_user_id on t1(user_id);
create unique index ux_t2_t1_id_t3_id on t2(t1_id, t3_id);
create index ix_t2_user_id on t2(user_id);
create index ix_t2_t1_id on t2(t1_id);

explain analyze gives me the following result:

Update on t1  (cost=2725.40..2737.42 rows=1 width=138) (actual time=1.428..1.428 rows=0 loops=1)
  ->  Nested Loop  (cost=2725.40..2737.42 rows=1 width=138) (actual time=0.646..1.148 rows=166 loops=1)
        ->  Subquery Scan on t2  (cost=2725.40..2725.42 rows=1 width=84) (actual time=0.642..0.729 rows=166 loops=1)
              ->  HashAggregate  (cost=2725.40..2725.41 rows=1 width=17) (actual time=0.639..0.685 rows=166 loops=1)
                    ->  Nested Loop  (cost=5.81..2725.39 rows=1 width=17) (actual time=0.034..0.536 rows=197 loops=1)
                          ->  Bitmap Heap Scan on t2 b  (cost=5.81..414.29 rows=193 width=13) (actual time=0.024..0.050 rows=197 loops=1)
                                Recheck Cond: (user_id = 1)
                                ->  Bitmap Index Scan on ix_t2_user_id  (cost=0.00..5.76 rows=193 width=0) (actual time=0.017..0.017 rows=197 loops=1)
                                      Index Cond: (user_id = 1)
                          ->  Index Scan using t1_pkey on t1 a  (cost=0.00..11.96 rows=1 width=8) (actual time=0.002..0.002 rows=1 loops=197)
                                Index Cond: (id = b.t1_id)
                                Filter: (user_id = 1)
        ->  Index Scan using t1_pkey on t1  (cost=0.00..11.98 rows=1 width=58) (actual time=0.002..0.002 rows=1 loops=166)
              Index Cond: (id = t2.t1_id)
Total runtime: 1.490 ms

Simplify query

Remove useless user_id from subquery.
Remove coalesce around count(). I quote the manual on aggregate functions:

  It should be noted that except for count, these functions return a
  null value when no rows are selected.

Meaning, count() never returns NULL.
Remove the redundant LEFT JOIN from the subquery (Update: not if you want to set columns to 0 where no rows are found in t2).

UPDATE t1
SET    col1 = t2.col1_count
      ,col2 = t2.col2_sum
      ,col3 = t2.col3_sum
FROM  (
   SELECT t1_id
         ,count(*) AS col1_count  -- if id is NOT NULL, count(*) is a bit faster
         ,sum(CASE WHEN col5 = true  THEN col2 ELSE 0 END) AS col2_sum -- might be simpler
         ,sum(CASE WHEN col5 = false THEN col3 ELSE 0 END) AS col3_sum -- missing info
   FROM   t2
   WHERE  user_id = 1
   GROUP  BY t1_id
   ) t2
WHERE  t2.t1_id = t1.id;

To reset rows in t1 without any matches in t2:

UPDATE t1
SET    col1 = 0, col2 = 0, col3 = 0
WHERE NOT EXISTS (SELECT 1 FROM t2 WHERE t2.t1_id = t1.id);

To do both at once, the version with LEFT JOIN the subquery like you had is probably faster, depends on your data distribution.

Avoid empty updates

If there is a chance that values in t1 are already up to date, add conditions to the WHERE clause that prevent empty updates (applies to both queries):

...
AND (col1 IS DISTINCT FROM t2.col1_count OR -- again: might be simpler
     col2 IS DISTINCT FROM t2.col2_sum   OR -- missing info
     col3 IS DISTINCT FROM t2.col3_sum)

For columns defined NOT NULL you can use <> instead of IS DISTINCT FROM.

This can make a big difference, updates are expensive.

Index

The only index you need for this (in addition to the primary key on t1.id) is:

CREATE INDEX ix_t2_user_id ON t2(user_id);


Applying (log backup) LSNs to the secondary node of an Availability Group I have a test instance configured as always-on go down today due to a restart of the primary node and the file-share going down together. I had a bunch of problems in order to get the secondary functioning again during which I had to remove it from the AG and add it again. During this time (the time when it went down till now) the log backup was running on the primary and when I tried to apply these backups to the secondary I got the following error:

  This backup set cannot be applied because it is on a recovery path that is inconsistent with the database. The recovery path is the sequence of data and log backups that have brought the database to a particular recovery point. Find a compatible backup to restore, or restore the rest of the database to match a recovery point within this backup set, which will restore the database to a different point in time. For more information about recovery paths, see SQL Server Books Online.

When I looked at sys.dm_hadr_database_replica_states:

last_hardened_lsn: 43000043815500001

When I looked at msdb.dbo.backupset (backup time):

backup time: 9:50 am
first_lsn: 43000043783000001
last_lsn : 43000043887200001

My question is, would there be any way to start applying this log backup starting from LSN 43000043815500001?

I am ok with restoring everything and then connecting my AGs since this is a very small instance, just want to know if there was an easier way as opposed to doing everything from scratch. I looked up online and did see the option for stopbeforemark or stopatmark but I am looking for something like stopaftermark.

You have 2 options :

Easy one - since this is test instance and its secondary.

Stop the log backup on primary.
Remove the db/s from AG.
Take a differential backup or full backup if dbs are small.
restore then on secondary with norecovery.
take an additional log backup on primary.
restore the additional log backup taken on secondary with norecovery.
join the dbs to AG.

A little bit harder one -

install sp_restoregene on primary.
Remove the db/s from AG.
Run it for the db/s in question and it will generate the full and log backup sequence with stopat.
Restore the sequence on secondary.
Join back to AG.

(I am telling above is a little bit harder since if you have not done a point-in-time recovery and you are doing it for the first time, test it first to be comfortable).


How to Log IP Address for Inbound Connections in MySQL Is there a way in MySQL 5.5 (on Redhat 6.x) to log the IP address from where a particular query has been run? And have that appear in the general log or even relay and bin logs? I am trying to track down the offending app server.

I googled around, and checked documentation but most of the discussion surrounded using IP Addresses as data in tables.

If you have shell access to the server on which mysql is running, you can use tcpdump + pt-query-digest. First, do a tcpdump with the appropriate switches necessary for pt-query-digest: tcpdump -i [your interface] port [3306,etc] -s 65535 -x -nn -q -tttt > tcpdump.out

Then, after you are done with your packet capture, do this:

pt-query-digest tcpdump.out --type=tcpdump --group-by=fingerprint --print --no-report

That will give you the raw digest of all queries and the hosts from which they originated. More information can be found at: http://www.percona.com/doc/percona-toolkit/2.1/pt-query-digest.html


Are there any free online collaboration database schema tools? I am interested in a free and simple MySQL tool which can offer online collaboration functionality in creating database general schema. Do you know any?

You can use Vertabelo. Vertabelo is an online database designer working under Chrome and Safari developed by company I work for.

Vertabelo focuses on online collaboration in the visual database schema creation area. The most important feature of this tool is that it allow to share database models across the team and collaborate on them via web browser. It also allow to have public model link with possibility of embeding diagrams in your webpage/blog/wiki.

Others Vertabelo features include:

Support for PosgreSql, MySQL, Oracle, MS SQL Server, DB2, SQLite, HSQLDB
Dynamic/Visual search
Live database model validation
Model versioning
Forward engineering
Reverse engineering

Vertabelo is free to use for academic projects and have commercial versions for larger database projects.


How to see tables and column names in exact case which is created and not by default uppercase? In toad, whenever I create an object such as a table, If I name columns in PascalCase when it created if I open the table, then toad displays all the column names in uppercase.

Is there any option in toad which prevents this default behavior and let us see the object names in the exact case which we create them?

That has nothing to do with TOAD.

As documented in the manual Oracle converts all non-quoted identifiers to uppercase.

If you really, really want to make your life a lot harder then it has to be, and really want to create mixed case identifiers, you have to quote them during creation:

create table "SomeTable"
(
   "Id" integer not null primary key,
   "SomeColumn" varchar(100)
);

But I strongly recommend to not use quoted identifiers. They will create much more problems in the long run than they are worth it.


Will SSDT publish always fail with "Data Loss May Occur" when table is rebuilt? Consider the following scenario:

We have a SSDT database project.
We add a new column to the middle of a table.
This forces the database project to rebuild the table in order to deploy the change.
We publish this SSDT database project  with the setting “Block incremental deployment if data loss might occur” enabled.
Because we have the above setting enabled, and (also, I think) the table is being rebuilt the deployment (correctly) throws an error saying data loss may occur.

My question is this: Can we rely on the fact that whenever the table needs to be rebuilt as part of a SSDT publish this error will be thrown?

Yes, you can rely on that. When you enable the "BlockOnDataLoss" attribute what actually happens is that SSDT adds to the deployment script a check like this one

IF EXISTS(SELECT 1 FROM TableToChange)
BEGIN
     RAISEERROR ...
END


PostgreSQL db volume at 100%. Can I create a tablespace on another volume so I can vacuum full? I have run out of space, because I am new to Postgres and I have just not been monitoring it properly.

Now I cannot even vacuum the tables:

ERROR:  could not extend file "base/20851/11787": No space left on device

I have deleted a whole bunch of data. I realize that only vacuum full will bring back space though.

I have another logical volume with lots of space on it. Can I create some kind of a tablespace over there? If so, is there a specific kind of space to tell the database to do it's "work" over there during the vacuum process? Or do I need to physically move tables to that tablespace? How do I do that?

I apologize for the rapid fire questions. I have found myself in a bit of a spot.

This is PostgreSQL 9.3.

Here's what You can do to move your data to the new tablespace :

Create the new tablespace pointing to the new location.

postgres=# create TABLESPACE movespace LOCATION '/home/postgres_tbsp';
CREATE TABLESPACE

Alter table or database to move to the new tablespace.

postgres=# ALTER DATABASE slonmaster SET tablespace movespace;
ALTER DATABASE

This process could last long as it moves all the data in the table or database to a new location.


How can I automate the process of checking of column and executing SQL statement? I am experiencing quite difficult problem and hope for your feedback. In my table I have a "Services"column in "MyData" table which contains names of my services. I have created SQL statement which should be executed via maintenance plan and get the minimum id value from one table and update "Value" column in "MyData" table:

declare @id int
select
    @id = min(id)
from
    MyTable
where
    MyTime >= dateadd(MM, -12, DATEFROMPARTS(YEAR(getdate()),MONTH(getdate()),1))

update
    MyData
set
    Value = @id
where
    ColumnName= 'MyServiceName'

The statement works correctly, but I need to automate the things. Problem is, I have several Services and it will be inefficient to manually change "MyServicename" column in where clause each time it is needed to be updated. I ask for your advice, how can I automate the process of checking the "MyServiceName" in order to execute the statement for appropriate service?
Sorry if it is written in too complicated way, but I hope could express the trouble I am expressing.

Question is not clear to me but

update
    MyData
set
    Value = @id
where
    ColumnName in ('MyServiceName', 'MyServiceName2')


Three table query to find the most recent item based on a distinct value I am attempting (without success thus far) to write a query that will join 3 tables and return the most recent record based on a distinct value.

Table A

itemnum  | itemname
-------- | --------
123      | document #1
456      | document #2

Table B

itemnum  | keyvaluedate
-------  | -------------
123      | 2015-09-05 00:00:00.000
456      | 2011-06-04 00:00:00.000
789      | 2011-06-04 00:00:00.000
546      | 2011-06-04 00:00:00.000
378      | 2011-06-04 00:00:00.000

Table C

itemnum  | keyvaluebig
-------- | -----------
123      | 123456
456      | 123456
545      | 987654
387      | 987654

Table A holds all of the records. What I need to find is the most recent record (keyvaluedate from table b) for each distinct project (keyvaluebig from table c).

The itemnum data is always unique. The keyvaluedate from Table B and the keyvaluebig from Table C both non-distinct values present.

What is the best way to go about this?

This returns the row from table A that relates to the most recent record in table B for each keyvaluebig in table C

Setup:

DECLARE @TableA TABLE
(
    itemnum INT
    , itemname NVARCHAR(50)
);

INSERT @TableA (itemnum, itemname)
VALUES (123, 'document #1'), (456, 'document #2');

DECLARE @TableB TABLE
(
    itemnum INT
    , keyvaluedate DATETIME2
);

INSERT @TableB (itemnum, keyvaluedate)
VALUES (123, '2015-09-05 00:00:00.000'), (456, '2011-06-04 00:00:00.000')
,(789, '2011-06-04 00:00:00.000'), (546, '2011-06-04 00:00:00.000')
,(378, '2011-06-04 00:00:00.000');

DECLARE @TableC TABLE
(
    itemnum INT
    , keyvaluebig BIGINT
);

INSERT @TableC (itemnum, keyvaluebig)
VALUES (123, 123456), (456, 987654), (545, 987654), (387, 987654);

Query:

WITH cte_Numbers
AS
(
    SELECT  c.keyvaluebig
            , b.itemnum
            , ROW_NUMBER() OVER(PARTITION BY c.keyvaluebig ORDER BY b.keyvaluedate desc) AS RowNumber
    FROM    @TableB b
    JOIN    @TableC c ON b.itemnum = c.itemnum
)

SELECT  a.*
FROM    @TableA a
JOIN    cte_Numbers n ON a.itemnum = n.itemnum
WHERE   RowNumber = 1

As mentioned by Mark Sinkinson this really is the place to go to see how to do this sort of stuff


Quitting PostgreSQL from mac terminal When I use terminal, enter my database and then quit by using \q I can not get to the following line in order for example to enter my other database (see below). The cursor is stuck without having the option to get to the next line.

postgres-#
postgres-# \q
logout
Saving session...
...copying shared history...
...saving history...truncating history files...
...completed.
[Process completed]

And then I can not go to next line. How do I overcome that problem?

First of all, you can switch to another database without leaving psql at all, use \c (or \connect) commad.

From psql's help (always available via \? command):

Connection
  \c[onnect] {[DBNAME|- USER|- HOST|- PORT|-] | conninfo}

So just type \c another_db to switch to "another_db".

Next, you see "[Process completed]" because you start psql via some shortcut your Postgres package (brew? postgres.app?) provided.

You can launch Terminal or iTerm2 (not default app in MacOS, but highly popular substitution for default Terminal), and then run psql inside it. In this case, you will not lose the window after \q command.


What's the quickest way to periodically export PostgreSQL data to a local file on Windows? I have access to a PostgreSQL database that I need to get data from for another system. The other system is running Windows 7 and does not (yet?) have PostgreSQL installed.

Ideally, I'd like to have the results of a few queries saved to local text files that I can then parse as needed, but if SQL dumping everything works, I'm fine with that too.

While I have experience with databases and programming, I'm not strong with either Windows or PostgreSQL.

Should I just install PostgreSQL on this Windows machine and use the command line tools that would then be found there to create a batch file? Or is there a better option?

Install the psql client (I believe it's in the pgAdmin III install). Add it to your PATH.
Put the following in %appdata%\postgresql\pgpass.conf (you may need to create that file):
hostname:port:database:username:password
In cmd:
psql -c "\copy (select * from foo) to 'fooLocal.txt';" --host $hostname --dbname $dbname --user $username
then fooLocal.txt will be a tab-delimited representation of the output of your query.
\copy (as opposed to COPY) can copy data from a remote server and doesn't need superuser privileges.
If you want to run that psql command on a schedule, you can use Task Scheduler (GUI) or schtasks (CLI), which is somewhat like cron for Windows.


Recommended settings for a small table with high update rate I have a small table which can be explained as follows:

create table mytab (
    primary_key_column number,
    a number,
    b number,
    c number,
    d number,
    e number,
    f number
);

It will have very few rows (not more than 256 records) but they will be updated at a high rate (for example one row could have up to 1000 updates per second).

What recommended settings should I use for the table to be able to handle this load and avoid update contention? Or other suggestions for doing this?

I will be using Oracle 12c or newer.

Edit

Concerning the high nnumber of updates, as was asked in the comments.
With pctfree and initrans I can perhaps get each row in a separate block, but there will still be a lot of updates for a given key. So any suggestions on how to handle that?
I see only three ways forward on this.

1) don't update but write the increments to a separate table and then have a job summing them and do the update regularly. This will get me out of the update contention, but I will be doing a lot of inserts instead

2) change the primary key in some way, like

create table mytab(
  primary_key_column number,
  sub_key number,
  a number,
  ...

and then let updates choose a random "sub_key" for the given primary_key. Sub_keys could be like the numbers 0-99 to give each row only 1% of the updates.

Given that all values a-f are zero at start, the total values can then be gotten by summing over the primary key.
It's ugly, but it could work.

Might the In-memory feature solve the problem?

Any comments on this?

Oracle 12c: Simple test case: one session inserting 256 rows:

MARK.STEWART@dev01> create table mytab (
    primary_key_column number,
    a number,
    b number,
    c number,
    d number,
    e number,
    f number
);

Table created.

MARK.STEWART@dev01>   insert into mytab (select rownum, OBJECT_ID, DATA_OBJECT_ID, NAMESPACE, DBMS_RANDOM.VALUE(1, 256), DBMS_RANDOM.VALUE(1, 256),  DBMS_RANDOM.VALUE(1, 256) from dba_objects  where rownum < 257);

256 rows created.

Elapsed: 00:00:00.77

Updated a row 1,000 times and committed after each update, took only 0.29 of a seconds:

MARK.STEWART@dev01> begin
   2      for loop_var in 1 .. 1000 loop
   3              execute immediate 'update  mytab set a= 1 where PRIMARY_KEY_COLUMN = 6';
   4              execute immediate 'commit';
   5      end loop;
   6  end;
   7  /

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.29
MARK.STEWART@dev01>

With  pctfree 99 initrans 20 it took almost twice as long (probably due to having high value for initrans): 00:00:00.65


Using INSERT INTO...EXEC AT linked server into temp table fails with Msg 7391 I am using SQL Server 2014. I want to execute EXEC (@remotecmd) AT [server_name]; (@remotecmd is a dynamic sql not a stored procedure) into ##eapb. My code is

insert into ##eapb
EXEC (@remotecmd) AT [ADSQLDB3S\DEV];

But I get the error:

  OLE DB provider "SQLNCLI11" for linked server "server_name" returned message "The transaction manager has disabled its support for remote/network transactions.".
      Msg 7391, Level 16, State 2, Line 71
      The operation could not be performed because OLE DB provider "SQLNCLI11" for linked server "server_name" was unable to begin a distributed transaction.

If I remove the insert into ##eapb, I have no error.

The Linked Server has the RPC Out option set to True.

In my testing (to another instance on the same server, not on a separate server), this worked if I had the Linked Server option of Enable Promotion of Distributed Transactions for RPC set to "False". You can accomplish that via the following command:

EXEC master.dbo.sp_serveroption
       @server = N'{linked_server_name}',
       @optname = N'remote proc transaction promotion',
       @optvalue = N'false';

This worked with the Distributed Transaction Coordinator (MSDTC) both ON (Running) and OFF (Stopped).

If you normally need the "remote proc transaction promotion" option set to "True", and if setting it to "False" allows this INSERT...EXEC to work, then you can set up another Linked Server with all of the same properties except this one option being different.

The main drawback to disabling "remote proc trans promotion" is that well, it's not a transaction at the remote server. So, if an error occurs there, you won't get the data inserted locally (obviously), but if there were any DML statements run remotely, those could still commit (depending on if it was a single statement or multiple statements). Still, you could / should still use proper transaction handling on the remote query (i.e. use the TRY...CATCH construct):

CREATE TABLE #Local ([name] [sysname]);

INSERT INTO #Local ([name])
    EXEC (N'
BEGIN TRY
    BEGIN TRAN;
    SELECT [name] FROM sys.servers WHERE 1/0 = ''a'';
    COMMIT;
END TRY
BEGIN CATCH
    ROLLBACK TRAN;
    THROW;
END CATCH;
') AT [{linked_server_name}];

P.S. The RPC Out option needs to be enabled / True. This was not mentioned above since according to a comment on the question, this option is already set correctly.


AG listener with MultiSubnetfailover True on Named Instance I have an AG configuration with databases on a Named instance. I have enabled
multisubnetfailover =true and registerallprovidersip =1
I just saw some blurb on the web that these settings are not supported by Named Instances.
Has anyone used these settings on a named instance and had a successful fast failover from 1 subnet to another?
Confusing details on leveraging multi subnet failover and sql server always on listeners.

As HandyD stated - the official doc does not mention issues with named instances. I also have not experienced any issues with AO multi-subnet failover with named instances.
The SQL Shack article to which you linked states: "It does not support SQL named instances. We can connect named SQL instances using a SQL listener" (emphasis added).  I think the confusion is caused by poor choice of words.  I suspect what the author meant to say is that in order for multi-subnet failover to work, a listener must be used to connect, using the instance names to connect will not work.


Get most recent 100 records from the database I am using SQL Server 2008, we have one database which contains 150 tables. We have introduced systimestamp DATETIME column in every tables. We do update this column with current timestamp whenever Insert/Update operation perform on the table.

How do I write a query to get the most recent (based on systimestamp column) 100 records of the database in a single result set ?

Based on the comments above, what you will need is something like this:

create table #topdates (ID int not null, TableName nvarchar(128), systimestamp smalldatetime)
exec sp_MSforeachtable @command1 = 'insert into #topdates select top 100 ID, ''?'' as TableName, systimestamp from ? order by systimestamp desc'
select top 100 * from #topdates
order by systimestamp
drop table #topdates

This will collate the top 100 results from each table into a temporary table, and then return the top 100 rows to you.

EDIT: Based on further information provided in the comments, it was established that the ID field might actually have a different name on every table.  The following SQL will list the top 100 last edited records in the entire database, across all tables, showing the Primary key value, the table name, and the systimestamp:

create table #keys (TABLE_NAME nvarchar(128), COLUMN_NAME nvarchar(128))
insert into #keys
SELECT cu.TABLE_NAME, cu.COLUMN_NAME
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE cu
WHERE EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc
    WHERE tc.CONSTRAINT_TYPE = 'PRIMARY KEY'
        AND tc.CONSTRAINT_NAME = cu.CONSTRAINT_NAME)
create table #topdates (Primary_Key nvarchar(128), TableName nvarchar(128), systimestamp smalldatetime)
declare @table nvarchar(128), @Column nvarchar(128), @sql nvarchar(max)
DECLARE cur CURSOR FOR select * from #keys
OPEN cur
FETCH NEXT FROM cur INTO @table, @Column
WHILE @@FETCH_STATUS = 0
BEGIN
    select @sql = 'insert into #topdates select top 100 [' + @Column + '], ''' + @table + ''', systimestamp from [' + @table + '] order by 3 desc '
    exec sp_executesql @sql
    FETCH NEXT FROM cur INTO @table, @Column
 END
CLOSE cur
DEALLOCATE cur
select top 100 * from #topdates order by systimestamp desc, TableName, Primary_Key
drop table #keys
drop table #topdates

You can easily extend this to show the column name of the Primary Key column too.


In SQL Server, what's the best way to split multiple delimited strings into rows without a function? I know there are numerous examples of how to split strings in SQL Server. However, they are often for a single delimited string. What's the best way to split multiple delimited strings without using a function (SQL Server 2016)?
Here's what I'm starting with:
GroupId     Val1      Val2
----------- --------- ------
G1           1,2,3     a,b,c
G2           4,5,6     d,e,f
G3           1,2,3,4   w,x,y,z

Here's what I would like to end up with:
GroupId Val1    Val2
------- ------- -------
G1      1       a
G1      2       b
G1      3       c
G2      4       d
G2      5       e
G2      6       f
G3      1       w
G3      2       x
G3      3       y
G3      4       z

Based on everything I've read, I think I may need to use the XML method because it allows me to specify an order, but I haven't been able to find an example of this method where multiple columns are split.
Here's the T-SQL to create the source table:
DROP TABLE IF EXISTS #TestData;

CREATE TABLE #TestData(GroupId VARCHAR(10),
                       Val1 VARCHAR(100),
                       Val2 VARCHAR(100)
                       );
INSERT INTO #TestData
VALUES ('G1', '1,2,3', 'a,b,c'),
       ('G2', '4,5,6', 'd,e,f'),
       ('G3', '1,2,3,4', 'w,x,y,z');

SELECT *
FROM #TestData;

Thank you in advance!

Transform the delimited string to a JSON array, and use OPENJSON, which exposes the lexical ordering for arrays.  eg
This
select *
from openjson('["w","x","y","z"]')

outputs
key       value
--------- -------
0         w
1         x
2         y
3         z

So
DROP TABLE IF EXISTS #TestData;

CREATE TABLE #TestData(GroupId VARCHAR(10),
                       Val1 VARCHAR(100),
                       Val2 VARCHAR(100)
                       );
INSERT INTO #TestData
VALUES ('G1', '1,2,3', 'a,b,c'),
       ('G2', '4,5,6', 'd,e,f'),
       ('G3', '1,2,3,4', 'w,x,y,z');

with q as
(
  SELECT GroupId, concat('["',replace(Val1,',','","'),'"]') Val1,concat('["',replace(Val2,',','","'),'"]') Val2
  FROM #TestData
)
select GroupId, v1.value Val1, v2.value Val2
from q
cross apply openjson(Val1) v1
cross apply openjson(Val2) v2
where v1.[key] = v2.[key]

outputs
GroupId    Val1     Val2
---------- -------- ---------------
G1         1        a
G1         2        b
G1         3        c
G2         4        d
G2         5        e
G2         6        f
G3         1        w
G3         2        x
G3         3        y
G3         4        z


Is there any doc listing standard  Oracle character sets correspondencies? I'm looking for a table listing Oracle and "standard" character sets, for example:

Oracle       Standard
------------ ----------
WEISO98859P1 iso-8859-1
AL32UTF8     utf-8
etc.         etc.

Does this kind of resource exist? I couldn't find any.

I believe this is covered by the documentation here


PostgreSQL execute a default query when no row are returned I need to calculate the average score of a list of userId.

I have a table user_place_score:

(user_id, place_id, score)

Sometimes I do not have the score (no row for (user_id,place_id) in the table) so I need to fetch a "default" score from the table default_place_score:

(place_id, score)

Is is possible in PostgreSQL to do this in one query? For a given list of users and one place_id, select all their scores in the user_place_score and if no row is found then take the score found if the default_place_score table?

Basically I want to avoid doing this kind of stuff:

for (Integer userId : userIds) {
  score = (query to fetch the score for this userId)
  if (score == null)
    defaultScore = (query to fetch the default score for this PlaceId)

  total = total + defaultScore
}

//return the average
return total/numberOfUser

You could use COALESCE() .. it returns the first non-null value in the list passed to it..

COALESCE((QUERY TO GET SCORE FOR USERID),(QUERY TO GET DEFAULT SCORE))

EDIT

oh - you added "list" requirement ... see @ypercube's answer, then.

EDIT2

If the place_ids exist in a separate table, and they only DON'T EXIST in the table you're querying to get scores (which, imo, would be considered a 'good design decision'), then you should include that table in your query .. COALESCE() as used in @ypercube's answer should work fine in that case.

SELECT
  u.user_id,
  u.place_id,
  COALECSE(u.score, d.score) AS score
FROM
  place_list AS pl
LEFT JOIN
  user_place_score AS u ON pl.place_id = u.place_id
LEFT JOIN
  default_place_score AS d
    ON d.place_id = u.place_id ;

Above taken and edited from @ypercube's answer.


Is there a more concise way to convert a UTC datetime into a local date only? I am trying to write a query that groups records based on the local date part only of a UTC datetime field.

For example, if my table contains 10/19/2012 2:00:00, then it should get grouped as 10/18/2012, since my local time is EST (-5h) and I'm only interested in the date portion of the field.

I know I can use DateAdd(day, DateDiff(day, 0, MyDate), 0) to get the date part only from the datetime field, and I can use DateAdd(minute, DateDiff(minute, GetUtcDate(), GetDate()), MyUtcDate) to convert a UTC datetime to a local date time.

But combining the two is seriously offending me.

Is there a better way than this to get just the Date part of a UTC DateTime field, converted to local time, in SQL Server 2005?

  SELECT DateAdd(day, DateDiff(day, 0, DateAdd(minute, DateDiff(minute, GetUtcDate(), GetDate()), MyUtcDate)), 0)
       , Count(*)
    FROM MyTable
GROUP BY DateAdd(day, DateDiff(day, 0, DateAdd(minute, DateDiff(minute, GetUtcDate(), GetDate()), MyUtcDate)), 0)

If you're not averse to having a function do the dirty work, this helps make the statement cleaner:

CREATE FUNCTION LocalDateFromUTCTime
(
    @UTCDateTime datetime
)
RETURNS datetime
BEGIN
    DECLARE @diff int;
    SET @diff = datediff(hh,GetUTCDate(), GetDate());
    RETURN DATEADD(day, DATEDIFF(day, 0, DATEADD(hh, @diff, @UTCDateTime)),0);
END

You could then do something like:

SELECT dbo.LocalDateFromUTCTime(MyUTCDate), COUNT(*)
FROM MyTable
GROUP BY dbo.LocalDateFromUTCTime(MyUTCDate);

This does, of course, make the statement non-SARGable.

If sargability is of primary concern due to the large number of records you may have, you could create a materialized view with an index on the calculated field.

EDIT:

Per @RichardThekiwi the SARGability of this particular statement is not affected by use of the function since the function is not part of either a JOIN or a WHERE clause.  Where the function is used, it is ran after any indexes would have been used.

ALSO, not the above code will truncate any time portion of the date input to the function (this is by design).  Therefore, any time zones that implement minutes such as the Prince Edward Island in Canada (UTC -4:30), India (UTC -4:30), and Kathmandu (UTC +5:45)


Replication taking too much time between requests We are using transactional replication in SQL Server 2014 with one master  (publisher), 1 distributor (dedicated server), and 3 slaves (subscribers).

All writes are made to master and reading is done from one of the 3 subscribers.

My issue is that if you make a insert/update/delete and the page is refreshing the update isnt there yet. There is a 1-4 sec delay until subscribers are updated which will confuse the user as the row inserted/deleted/updated but isn't reflected on the subscribers yet...

We are considering going for peer-to-peer replication, but it seems to be an overhead with IDENTITY which goes back to write to one but it will also take too much time to replicate...

What could/should we do?

What could/should we do?

Discard the assumption of synchronous replication and redesign your application accordingly. Replication was never designed for this. Latency of hours can happen and you should design accordingly.

None of the existing SQL Server technologies, including Availability Groups readable secondaries in synchronous commit mode, make a guarantee of synchronous propagation of changes to replicas/secondaries. the only place from where you can read back your own changes and be guaranteed to see your own changes is the publisher/primary, where the changes were made in the first place. Any expectation to the contrary is folly.


What steps can I take to determine if my server is under-provisioned for memory? My server is running SQL Server 2016. The environment is fairly high workload with a lot of write transactions and reading of the data throughout the day. I have a hunch the server isn't provisioned with enough memory and would like to dig in and see if that's so. What are the best ways to determine if and how much contention is being generated by the amount of memory available on the server?
I did take a look at the DMV sys.dm_os_wait_stats and when sorted by waiting_tasks_count desc, the top two wait types are "MEMORY_ALLOCATION_EXT" and "RESERVED_MEMORY_ALLOCATION_EXT" by an order of magnitude larger than any of the other wait type task counts. Are there other places I can check as well for memory pressure or contention?
Edit: The total size of all databases on this server is 3 TB, the primary database with most of the transactions is 2 TB, and the total amount of RAM on the server is 32 GB.
Edit 2: Here are the Lazy writes / second perfmon counter results over the course of a day:

Here are several perfmon counters you can use as a starter.
Memory Grants Pending - This is a counter that tells you if there any queries waiting for a memory grant (memory allocation). This should actually be 0. If it is consistently more than that, you have an issue.
Page Life Expectancy - This is the estimated amount of time in seconds that a page stays in memory. The higher the better, but there is a formula to calculate what the minimum value for your serer should actually be. It used to be 300 seconds minimum, but this is an old calculation and should nowadays be 100 seconds per GB. I got this from Richard Douglas during a SQL Saturday session, so credits to him. He works for SentryOne. Less than that value tells you there is too little memory assigned. Also use this counter in conjunction with Checkpoint pages/sec. Be aware that every NUMA node has it's own PLE value (if you have multiple NUMA nodes on your SQL Server). SQL Server starts deviding resources into (soft) NUMA nodes above 8 cores assigned.
Lazy writes/ sec - The lazy writer process clears out old pages from the cache when SQL Server experiencs memory pressure. Constantly above a value of 20 is a problem (got this from Richard Douglas as well). However, use it in conjunction with Page Life Expectancy. If you see a high PLE together with a spike in lazy writes/sec, then something is causing SQL to remove pages from it's cache and insert new pages. See the screenshot below for an example from my home lab.

Iam sure that some experts here know a little more about the memory clerks, still on my list to dive in deeper, so maybe someone has some additional info for you (in which I am very much interested as well :-) ).
Edit: you can also use sys.dm_os_performance_counters to get them on the fly with an query if you like.
Edit 24 June 2020:
@J.D. regarding your comment of June 23; i digged in a little deeper into memory pressure as well, because of the comments of @Dominique Boucher and this article: https://www.brentozar.com/archive/2020/06/page-life-expectancy-doesnt-mean-jack-and-you-should-stop-looking-at-it/. I was laughing when I got this in my mailbox; maybe he saw this post. :-) The article is telling us to stop looking at it. Well, although Brent is surely a lot more experienced then me, I don't think I can totally agree with his statement to never look at it. I get his point within the context of his sp_BlitzFirst, a single query using max 25% of the buffer cache, it is a lagging indicator, etc. but for trend analysis and history I would still look at PLE vs Lazy Writer. If I want to determine if a server has memory pressure over time, this is what i would use in combination with the waiting memory grants. Also, monitoring tools from RedGate and Quest still use this. Now @Dominique Boucher says to look at the RESOURCE_SEMAPHORE waits, which I agree on, but this will most likely be in line with the number of pending memory grants (which you can easily register with perfmon). If you have a constant queue of memory grants (it works with a FIFO queue), then you do have memory pressure.
As a reference, this is Lazy Writer for a certain system in this universe (32 GB mem, 1 TB database, although the type of workload is also very important). Yellow is batch requests p/s, 10 indicates a 1000, so you can see it is definitely not idle.

here is also a snapshot from my home lab regarding memory grants vs RESOUCE_SEMPAPHORE waits (and I also see I highlighted the tempdb writes, that was what david was talking about, too little mem, so spill to tempdb):

Now, looking at your perfmon counters, I think that you could definitely have a memory issue. I mean; something is constantly forcing SQL Server to remove pages from the buffer cache. If this would be a single time, ok, but it seems it is constantly busy doing that... However, I would like to see them compared to PLE. This can be a clear indication there is memory pressure ( over time I still think so). Secondly, you also want to look at pending memory grants. Now, I didn't say this before, but looking back I do think you want to look at the wait stats as Brent and Dominque are saying. However, that is a little more difficult over time. Wait stats are gathered cummulative, so you need to clear them first (which I don't prefer) and then look at them if the RESOURCE_SEMAPHORE are adding up.
To clear the wait stats: https://docs.microsoft.com/en-us/sql/t-sql/database-console-commands/dbcc-sqlperf-transact-sql?view=sql-server-ver15
You can also use sp_BlitzFirst to monitor it, but that is only a snapshot from the point of time your are running it. sp_BlitzFirst (or sp_Blitz i cannot recall) has an option to record this periodically in a table, so you could look at that as well. Or just querying dm_os_wait_stats yourself in some other way also works. In general, my personal preference is to collect data over time to analyze this. I do this using Database Health Monitor from Steve Stedman: databasehealth.com. Screenshot from my home lab:

This way you can monitor wait stats a lot better, although it will cost you a tiny bit of resources.
If you have SQL Server 2017 or above (we don't have that yet in our env.), then you can also use Query Store. As of SQL Server 2017 Query Store also records wait stats (it is a configurable option). Be carefull though, I have read stories of Query Store bringing very busy servers to their knees (you can monitor that with wait stats :-P ). You should always test before implementing functionality in prod of course. We do use it, and works great, but we have 2016, so we do miss the wait stats option :-(.
By the way; my tactic would be to gather information (perfomon, wait stats if possible), if you think there is memory pressure upgrade the RAM (if a VM that would be quite easy), gather performance indicaters afterwards and check if they have improved. Bit of a no brainer, but the latter is often forgotten or not done very well.


How to make a query using "where MONTH(datetime)" faster? I already tried to add an index on the datetime column, but the result is still unacceptably slow.

SELECT s.id, s.player,
COUNT(case when dg.winner = 1 AND dp.colour <= 5 then 1 when dg.winner = 2 AND dp.colour > 5 then 1 else null end) as totalwin,
COUNT(case when dg.winner = 2 AND dp.colour <= 5 then 1 when dg.winner = 1 AND dp.colour > 5 then 1 else null end) as totallose,
COUNT(dg.winner) as totalgames
       FROM dotaplayers AS dp
       LEFT JOIN gameplayers AS gp ON gp.gameid = dp.gameid and dp.colour = gp.colour
       LEFT JOIN stats AS s ON s.player_lower = gp.name
       LEFT JOIN dotagames AS dg ON dg.gameid = dp.gameid
       LEFT JOIN games AS g ON g.id = dp.gameid
       LEFT JOIN bans as b ON b.name=gp.name
       WHERE MONTH(g.datetime) = 4
       GROUP by gp.name
       ORDER BY totalwin DESC LIMIT 0,10

Showing rows 0 - 9 (10 total, Query took 7.7552 seconds.)

If someone wants to help, I can provide a copy of my database, it is only 120 MB in size.

Using the MONTH() function on a column automatically disqualifies any index from usage
You can state the range in the WHERE clause
SELECT s.id, s.player,
COUNT(case when dg.winner = 1 AND dp.colour <= 5 then 1 when dg.winner = 2 AND dp.colour > 5 then 1 else null end) as totalwin,
COUNT(case when dg.winner = 2 AND dp.colour <= 5 then 1 when dg.winner = 1 AND dp.colour > 5 then 1 else null end) as totallose,
COUNT(dg.winner) as totalgames
       FROM dotaplayers AS dp
       LEFT JOIN gameplayers AS gp ON gp.gameid = dp.gameid and dp.colour = gp.colour
       LEFT JOIN stats AS s ON s.player_lower = gp.name
       LEFT JOIN dotagames AS dg ON dg.gameid = dp.gameid
       LEFT JOIN games AS g ON g.id = dp.gameid
       LEFT JOIN bans as b ON b.name=gp.name
       WHERE g.datetime >= '2015-04-01 00:00:00'
       AND   g.datetime <  '2015-05-01 00:00:00'
       GROUP by gp.name
       ORDER BY totalwin DESC LIMIT 0,10

or move  the range from the WHERE to the LEFT JOIN
SELECT s.id, s.player,
COUNT(case when dg.winner = 1 AND dp.colour <= 5 then 1 when dg.winner = 2 AND dp.colour > 5 then 1 else null end) as totalwin,
COUNT(case when dg.winner = 2 AND dp.colour <= 5 then 1 when dg.winner = 1 AND dp.colour > 5 then 1 else null end) as totallose,
COUNT(dg.winner) as totalgames
       FROM dotaplayers AS dp
       LEFT JOIN gameplayers AS gp ON gp.gameid = dp.gameid and dp.colour = gp.colour
       LEFT JOIN stats AS s ON s.player_lower = gp.name
       LEFT JOIN dotagames AS dg ON dg.gameid = dp.gameid
       LEFT JOIN games AS g ON g.id = dp.gameid AND g.datetime>='2015-04-01 00:00:00' AND g.datetime<'2015-05-01 00:00:00'
       LEFT JOIN bans as b ON b.name=gp.name
       GROUP by gp.name
       ORDER BY totalwin DESC LIMIT 0,10

GIVE IT A TRY !!!
UPDATE 2015-07-08 15:19 EST
Try using INNER JOIN on games since you need a specific month
SELECT s.id, s.player,
COUNT(case when dg.winner = 1 AND dp.colour <= 5 then 1 when dg.winner = 2 AND dp.colour > 5 then 1 else null end) as totalwin,
COUNT(case when dg.winner = 2 AND dp.colour <= 5 then 1 when dg.winner = 1 AND dp.colour > 5 then 1 else null end) as totallose,
COUNT(dg.winner) as totalgames
       FROM dotaplayers AS dp
       LEFT JOIN gameplayers AS gp ON gp.gameid = dp.gameid and dp.colour = gp.colour
       LEFT JOIN stats AS s ON s.player_lower = gp.name
       LEFT JOIN dotagames AS dg ON dg.gameid = dp.gameid
       INNER JOIN games AS g ON g.id = dp.gameid
       LEFT JOIN bans as b ON b.name=gp.name
       WHERE g.datetime >= '2015-04-01 00:00:00'
       AND   g.datetime <  '2015-05-01 00:00:00'
       GROUP by gp.name
       ORDER BY totalwin DESC LIMIT 0,10


Why is PostgreSQL Not Using My Index? I have the following query:
SELECT "account_transactions"."subsidiary_id"
FROM "account_transactions"
WHERE (transaction_type = 'loan_payment'
        AND amount > 0
        AND status = 'approved')
        AND (DATE(transacted_at) >= '2020-08-01'
        AND DATE(transacted_at) <= '2020-08-31'
        AND subsidiary_type = 'Loan')
ORDER BY  transacted_at ASC

I identified that it was considered a slow query so I'm trying to optimize it by applying an index. However I tried variations of indices such as putting an index on (transaction_type, amount, status) WHERE amount > 0 AND status='approved' AND transaction_type = 'loan_payment' or an index on transaction_type only. But when I run EXPLAIN ANALYZE, it still shows that it's running Seq Scan meaning that indices aren't being used.
Is this a PostgreSQL thing or am I doing indexing wrong?

Your index is probably not used because it does not match the most selective conditions. If too many rows are returned from the index scan, a sequential scan is more efficient.
Try this index:
CREATE INDEX ON account_transactions (transaction_type, subsidiary_type, status, transacted_at);

If the condition of any of the leading three columns is not selective, omitting the column from the index is better. The important part is that transacted_at is last, because it is not used in a condition with = and is used in ORDER BY.
You can add WHERE conditions to the index like you did in your question.


Best way to Design Key,value pairs in relational database? I have a table which has a list of all attributes that any object can have:

attr_id    attr_name    attr_data_type
------      -----        --------------

I have a table [object_key] in which each object_key_id has an unique list i.e set of attr_id,attr_value pairs.

object_key_id   attr_id   attr_value
---------       -------   ----------
1               1          'value1'
1               2          'value2'
2               1          'value3'
2               2          'value4'
3               1          'value1'
3               2          'value2'
3               3          'value3'

In this case, what is the best way to get the object_key_id for a given set of attr_id,attr_value pairs.

Ex:  Input: [{1, 'value1'}, {2, 'value2'}]
     Output: 1

What's the best way to build a unique index i.e to have unique object_key_id for a set of attr_id,attr_value pairs?

What you want is called (exact) Relational Division.

See also this article: Divided We Stand: The SQL of Relational Division

There are several ways to have a query like that solves this problem. You can load the input data to a (temporary) table or use a CTE like this:

WITH input_data (attr_id, attr_value) AS
  ( SELECT *
    FROM
      ( VALUES                              -- the input data.
          (1, 'value1'),
          (2, 'value2')
      ) AS i (attr_id, attr_value)
  ),
t AS                                        -- all the object_key_id values.
  ( SELECT DISTINCT object_key_id           -- if you have a separate table with those
    FROM tableX                             -- you can use it, instead.
  )
SELECT object_key_id
FROM t
WHERE NOT EXISTS                            -- this subquery ensures
      ( SELECT attr_id, attr_value          -- that all input attribute/value pairs
        FROM input_data                     -- appear for an object_key
      EXCEPT
        SELECT attr_id, attr_value
        FROM tableX AS tt
        WHERE tt.object_key_id = t.object_key_id
      )
  AND NOT EXISTS                            -- and this subquery does the reverse
       ( SELECT attr_id, attr_value         -- i.e. the exact part of the division
        FROM tableX AS tt
        WHERE tt.object_key_id = t.object_key_id
      EXCEPT
        SELECT attr_id, attr_value
        FROM input_data
      ) ;

Test at SQL-Fiddle

This question at StackOverflow: How to filter SQL results in a has-many-through relation has a few more ways to solve it and benchmarks for Postgres but it's not for the exact variation, only for the "Division with Remainder". The queries will be similar but an extra check/condition has to be added for the exact variation.


Shall i upgrade Database server from 2005 to 2012 without upgrading SSRS or web Application Shall i upgrade Database server from 2005 to 2012 without upgrading SSRS or web Application relying upon the database?
Will the SSRS reports and Web application continue to work?
Would be appreciated if you could respond quickly

Will suggest you to go through this whitepaper SQL Server 2012 Upgrade Technical Guide, to get a proper understanding on what all is needed for the activity.

There are many points which would be need to be think upon, i.e.

  Just upgrading by keeping the compatibility level at a older version is not real upgrade. Evaluate at using some of the new features to increase productivity, performance, maintainability and scalability.

As the upgrade talks happens, get started with tracking if any deprecated features are being used. There are multiple ways to do this – I highly recommend using the “ SQL Server: Deprecated Featurese” Perfmon counter to track it like a background task. As your application runs, this gives you a rough idea of how many deprecated features you might be using inside your application that might have an impact to your upgrade process. There is also a Trace Event for capturing Deprecated features with SQL Server Profiler which you can use but that will be to nail down which code is causing that perfmon spike.

Also before upgrading you should check Upgrade Advisor as a first step. This surely guides you of potential problems upfront and is one of the essential step too for any SQL Server upgrade process.

Also, you need to understand on the OS version. If you're current OS running sql server 2005 is good enough to run SQL server 2012.

Similarly, when you check for apps and SSRS, you need to test their functionality if they are designed to run and function as per the latest SQL server 2012 features.

So, before coming to conclusion you need to have a thorough testing done on test/dev/staging servers to test the above apps\reports.

Get everything documented and then layout a plan to decided a YES or NO.

Hope this helps.


deleting large volumes of rows from table efficiently in SQL Server I have a table that has gotten a little out of control. I am not a DBA per se but seem to recall that deleting a huge volume of rows in one shot can cause transaction log issues, hamper overall system performance during the delete, etc...

Is there an efficient way for me to create a job that deletes records in small batches to over hampering other access/performance and prevents problems with the transaction log?

This process can be quite slow is that makes a difference

For additional context, the delete criteria will be based on something like... where x like '%blah%'. Also, there is one clustered index and 5 non-clustered indexes.

You can break it up into chunks - delete in a loop; each delete iteration it's own transaction and then clearing the log at the end of each loop iteration. Finding the optimal chunk size will take some testing.

I suggest you take a look at this article by Aaron Bertrand, where he explains the details and runs tests for different scenarios, to show the impact (duration, transaction log):
http://sqlperformance.com/2013/03/io-subsystem/chunk-deletes

As for the where x like '%blah%' delete condition - it will make the query not sargable (can't leverage the indexes to do an index seek). So even if you have an index to support column x, it's going to be a scan.

Resources for this:

Brent Ozar - Sargability: Why %string% Is Slow
Index usage by %Like% operator – Query Tuning


mariadb 10.3.13 table_open_cache problems Just upgraded mysql 5.6 to mariadb 10.3.13 - now when the server hits open_tables = 2000, my php queries stop working - if I do a flush tables it starts working correctly again. This never happened when I was using mysql - now I can't go a day without having to login and do a flush tables to get things working again

Use WHM / Cpanel to administer my VPS and on the last WHM release it started warning me that the version of MySql (really can't remember what version it was - it was what was loaded when I got my VPS) that I was running was soon coming to an end and I would need to upgrade to SQL 5.7 or MariaDB xxx.  Had been wanting to move to MariaDB for awhile anyway, so that is what I did - WHM recommended the 10.3.13 version.

After some more watching and looking it appears that what makes my open_tables hits the 2000 max was the automatic CPANEL backup routines - which also backup all of my databases at one get go. Doesn't crash anything just causes problems with my PHP application connections - I don't thing the connections get rejected - they just don't return any data .... Turned all of the automatice WHM/CPANEL backups off and things have settled down a little.

table_definition_cache  400
table_open_cache     2000

I still do a mysqldump via cron to do my database backups - only two live databases and they still make the tables_open grow to that 2000 max - just not as fast.

I now run a script that runs every hour to show me some of the variables and here is what I am seeing

after doing a flush tables command both open_tables and open_table_definitions start increasing until open_table_definitions hits 400 it stops increasing while open_tables keeps increasing thru the day.

then when the mysqldumps happen in the early morning hours tables_open hits 2000 (the max setting) and my php queries are not executed

I do not get a PHP error.

I ran the following command so that I could see what was happening on the db side.

SET GLOBAL general_log = 'ON'

Looking at the log, when everything is running OK I see my application connecting, preparing the statement, executing the statement and then disconnecting ....

I did the same thing when it started acting up (i.e. my php application starts to not get a result again)

Looking at the log I see my application connecting, then preparing the statement and then instead of seeing it execute the statement, it prepares the same statement 2 more times and then  disconnects ...

I logged into mysql and did a flush tables command and everything goes back to normal - application connects, prepares a statement, executes it, disconnects ...

But this never happened before I moved to MariaDB - I never messed with the MySQL server  stuff at all - the only time MySQL was restarted was when I did a CENTOS 6 system update and had to reboot the server - would go months without doing a thing on the server ....

10.3.3 was a beta release. As the is a new upgrade, use the latest GA 10.3 release. Lots of these sort of bugs pop up in earlier versions.

ref: https://mariadb.com/kb/en/library/release-notes-mariadb-103-series/


Find actual data size from a query? Is it possible to find the size of data that will be returned by a particular query?

For example, I can use the following to find the number of rows per date:

select mydatecolumn, count(*)
from MyTable
where mydatecolumn < '01-JAN-2014'
group by mydatecolumn

But is there a way to find out the size of the data that is contained in those rows?

e.g.

select mydatecolumn, "sizeofactualdata",
from mytable
where mydatecolumn < '01-JAN-2014'
group by mydatecolumn

Even if it's one query per date that's not a problem.

Here is a way of How to calculate the Result Set data size

If using  Management Studio:
Go to Menu >> Query >> Include Client Statistics

Once you enable the client statistics, it will display another tab in the result span; when clicked, we obtain the following:

As shown in the image, it will capture details for different trials and they are compared with each other. Green arrows indicate improved statistics and red arrows indicate degrading statistics. Please note that here an increase does not always imply a good result; sometimes some statistics when marked as “up” can imply a bad result as well. For the same reason, they are colored in green and red, signifying good and bad results, respectively.


Table income and expense is better to have it together or apart I am developing a small system where I have to record incomes and expenses of the company, and even I can not determine if the income and expense table should be in one table or in separate tables.

The design of the database must allow the system to generate a report of income and expenses by date (in column incomes and other expenses).

I would like to explain me what would be the advantage or disadvantage of having them together or separately

If you consider income as a negative expense, and have categorised it sufficiently, a single table is just fine. The columns describing an income transaction are essentially identical to the columns describing an expense.

You can easily have indexes that allow you to quickly find your income or expenses, based on your categories (because negative income is not necessarily an expense, you should have things categorised appropriately). And with an index on time you can easily pull up your change over time.

If you're doing double-entry accounting, you should be able to easily add up your transactions and see they always add to zero. It's a useful test for consistency. Then some reports will leave out "equity" lines to compare Profit and Loss, etc.

If you use separate tables, you can use UNION ALL queries to query across the lot, but I would consider this extra effort you don't really need.


SQL Server Error 18483 - When Setting Up SQL Distribution I'm trying to synchronize a couple tables in one SQL Server 2000 database into another SQL Server 2005 database using replication.

When I am attempting to set up my SQL Server as a distributor, I'm getting the following error:

  SQL Server Enterprise Manager could not configure 'VIPER' as the Distributor for 'VIPER' .
  Error 18483: Could not connect to server 'VIPER' because 'distributor_admin' is not defined as a remote login at the server.

In doing research, I found several sources and articles that show that my machine name, and the actual name of the server are not the same.  When I check the sysservers view in master, I see that:

0   1089    VMSSERV SQL Server  SQLOLEDB    VMSSERV         4/23/2008 9:50:41 AM
1   1601    repl_distributor    SQL Server  SQLOLEDB    VIPER           10/12/2011 1:23:18 PM
2   1184    CHARLIE SQL Server  SQLOLEDB    CHARLIE         2/18/2011 1:32:14 PM

The first line is the original database that was setup, and that I intend on using. You can see (kinda) that the original name is VMSSERV and the actual machine name is VIPER.

So, from the results of my extensive web search, I ran this in Query Analyzer:

USE MASTER
GO
SELECT @@SERVERNAME, SERVERPROPERTY('ServerName')

This returns:

VMSSERV VIPER

Most of my web searches show that I should refer to http://support.microsoft.com/kb/818334, which instructs to use:

-- Use the Master database
USE master
GO

-- Declare local variables
DECLARE @serverproperty_servername  varchar(100),
    @servername             varchar(100)

-- Get the value returned by the SERVERPROPERTY system function
SELECT @serverproperty_servername = CONVERT(varchar(100), SERVERPROPERTY('ServerName'))

-- Get the value returned by @@SERVERNAME global variable
SELECT @servername = CONVERT(varchar(100), @@SERVERNAME)

-- Drop the server with incorrect name
EXEC sp_dropserver @server=@servername

-- Add the correct server as a local server
EXEC sp_addserver @server=@serverproperty_servername, @local='local'

And finally my actual question...

Would my correct format to fixing this be on the last line of the above sequence:

EXEC sp_addserver @server = @serverproperty_servername, @local = 'VIPER'

?

Once I perform this task, if there are further errors, I will reply back.  Hopefully this ONE item will fix my issue...

The SQL Server was probably renamed in the past.  Here's a better article from Microsoft on how to change the server names:

http://msdn.microsoft.com/en-us/library/ms143799.aspx


Count nested joined table records only if the table associated via bridge table have two records with given names I have the main table on which I have to apply count. I have two bridge tables and two nested tables for has_many relation. You can generate tables and bridge tables with below code and also insert some data

main_table

CREATE TABLE main_table
(
    id        serial PRIMARY KEY,
    is_active BOOLEAN,
    name      VARCHAR(50) UNIQUE NOT NULL
);

nested_table_1

CREATE TABLE nested_table_1
(
    id   serial PRIMARY KEY,
    name VARCHAR(50) UNIQUE NOT NULL
);

nested_table_2

CREATE TABLE nested_table_2
(
    id   serial PRIMARY KEY,
    name VARCHAR(50) UNIQUE NOT NULL
);

bridge_table_1

CREATE TABLE bridge_table_1
(
    main_table_id     INTEGER REFERENCES main_table (id),
    nested_table_1_id INTEGER REFERENCES nested_table_1 (id),
    PRIMARY KEY(main_table_id, nested_table_1_id)
);

bridge_table_2

CREATE TABLE bridge_table_2
(
    main_table_id     INTEGER REFERENCES main_table (id),
    nested_table_2_id INTEGER REFERENCES nested_table_2 (id),
    PRIMARY KEY(main_table_id, nested_table_2_id)
);

Query

SELECT COUNT(main.id)
      FILTER (where nes_2.name IN ('nested table second 1', 'nested table second 2') AND
                    nes_1.name = 'Contact')                              AS contact_compliants,
      COUNT(main.id)
      FILTER (where nes_2.name = 'Drop' AND nes_1.name = 'Dropley')      AS droplet_compliant,
      COUNT(main.id) FILTER (where nes_2.name IS NOT NULL AND
                                    nes_1.name IN ('Contact', 'Droplet')) AS total_opportunities
FROM main_table AS main
        INNER JOIN bridge_table_1 AS bt_1
                    ON bt_1.main_table_id = main.id
        INNER JOIN nested_table_1 AS nes_1
                    ON bt_1.nested_table_1_id = nes_1.id
        INNER JOIN bridge_table_2 AS bt_2
                    ON bt_2.main_table_id = main.id
        INNER JOIN nested_table_2 AS nes_2
                    ON nes_2.id = bt_2.nested_table_2_id
WHERE main.is_active = 'true';

The query above is working all great counting everything fine. Just one issue. In the first part for contact_compliants, it has IN which means either one of the names for nested will increment count. which is not my requirement. I want to count only when when both the names matches.

to be clear: How do I make this part where nes_2.name IN ('nested table second 1', 'nested table second 2') to count only when nes_2.name = 'nested table second 1' AND nes_2.name = 'nested table second 2'). I have tried adding double join so that I can compare but no success. I have also seen similar solution but I am not sure, how it will apply here with Having

Insert some data with expected output

INSERT INTO main_table (is_active, name, id) VALUES (true, 'main 1', 1);
INSERT INTO main_table (is_active, name, id) VALUES (true, 'main 2', 2);

INSERT INTO nested_table_1 (name, id) VALUES ('Contact', 1);
INSERT INTO nested_table_1 (name, id) VALUES ('Dropley', 2);

INSERT INTO nested_table_2 (name, id) VALUES ('nested table second 1', 1);
INSERT INTO nested_table_2 (name, id) VALUES ('nested table second 2', 2);
INSERT INTO nested_table_2 (name, id) VALUES ('nested table second 3', 3);
INSERT INTO nested_table_2 (name, id) VALUES ('Drop', 4);

INSERT INTO bridge_table_1 (main_table_id, nested_table_1_id) VALUES (1, 1);
INSERT INTO bridge_table_2 (main_table_id, nested_table_2_id) VALUES (1, 1);
-- Count for contact_compliants should be zero till here but its 1

After inserting all above data, expected output should be
    contact_compliants,droplet_compliant,total_opportunities
    0,0,1

But its actually
    contact_compliants,droplet_compliant,total_opportunities
    1,0,1

INSERT INTO bridge_table_2 (main_table_id, nested_table_2_id) VALUES (1, 2);
-- Similarly, Count for contact_compliants should be 1 after inserting above but its 2

Please consider this... not a ready-made solution but a suggestion which should lead you in a proper direction.

  In the first part for contact_compliants, it has IN which means
  either one of the names for nested will increment count. which is not
  my requirement. I want to count only when when both the names matches.

Both? Then your whole idea with JOIN + count(*) will not work.

The problem is clearly visible if you replace the whole SELECT list with *:

SELECT * FROM main_table AS main
INNER JOIN bridge_table_1 AS bt_1 ON bt_1.main_table_id = main.id
INNER JOIN nested_table_1 AS nes_1 ON bt_1.nested_table_1_id = nes_1.id
INNER JOIN bridge_table_2 AS bt_2 ON bt_2.main_table_id = main.id
INNER JOIN nested_table_2 AS nes_2 ON nes_2.id = bt_2.nested_table_2_id
WHERE main.is_active = 'true';
 id | is_active |  name  | main_table_id | nested_table_1_id | id |  name   | main_table_id | nested_table_2_id | id |         name
----+-----------+--------+---------------+-------------------+----+---------+---------------+-------------------+----+-----------------------
  1 | t         | main 1 |             1 |                 1 |  1 | Contact |             1 |                 1 |  1 | nested table second 1
(1 row)

See? The FROM clause sees only one row. All the further FILTER tricks are a no-op. This stems from SQL conceptual workflow which is pretty well described in SELECT statement Description.

Looks like you need outer joins to catch the data you need. But then it will explode the rowcount of your result set.

So you need to rethink the whole query, especially this part which calculates contact_compliants. Maybe try to use EXISTS subqueries instead of JOINs?.

SELECT
  (SELECT count(*) FROM ...
   WHERE EXISTS (...)
   AND EXISTS (...)) AS contact_compliants,
  ... /* more subqueries here */
FROM main_table AS main
WHERE main.is_active;

Side note: I'm not sure if your schema is optimal, maybe the design is too convoluted and you can simplify it?