Reasons For Slowness in SQL Server 2008 R2 Express Edition We are observing intermittent slowness in our SQL Server. We ran sp_who2, sp_who3 stored procedures for checking CPU/IO usage but did not see any spikes/abnormal values. What else could be the possible reasons for slowness. What other reports we can take from SQL Server to analyze the issue?
We are using SQL Server 2008 R2 Express.

Without some examples of queries you're experiencing the slowness with, and their execution plans there's not much we have to go off of.
Other good monitoring tools you can use to try to capture something is sp_WhoIsActive and sp_Blitz. You can also check the most commonly occuring Wait Stats to see if there's anything abnormal there.


Decompose table into third normal form Tables X and Y are given as below :

X (A, B, D, E, F, G, H, J) with A and B as primary key
Y (K, L, M, N, O, P, Q, R) with K and L as primary key

Now we need to decompose these tables into tables in the third normal form using the functional dependencies specified below:

A   ->  G, H
B   ->  D, E, F
E   ->  F
H   ->  G
L   ->  N, O, P, Q
N   ->  Q

I need to specify primary keys and foreign keys also. Please help to solve this problem.

I am newbie to normalization. So please help to solve this problem.If I normalize to 2NF I loose some relations which doesn't suit to 3NF. So I am confused. Any help will be appreciated. Because to convert to 3NF I need to go through 2NF too.

I had changed it to 3NF. Please help in finding if am right in doing it

R1 (E, F)
R2 (H, G)
R3 (A, B, L)
R4 (A, H)
R5 (B, D, E)

R6 (N, Q)
R7 (K, L, M, R)
R8 (L, N, O, P)

We have

X (A, B, D, E, F, G, H, J) with A and B as primary key
Y (K, L, M, N, O, P, Q, R) with K and L as primary key

With functional dependencies:

A -> G, H
B -> D, E, F
E -> F
H -> G
L -> N, O, P, Q
N -> Q

2NF Normalization

R1((A,B) (PK), J)
R2(A (PK) ,G,H)
R3(B (PK) ,D,E,F)
R4((K,L) (PK), M,R)
R5(L (PK) ,N,O,P,Q)

3NF Normalization

Ra((A,B) (PK), J)
Rb(A (PK) ,H)
Rc(H(PK), G)
Rd(B(PK),D,E)
Re(E(PK),F)
Rf((K,L) (PK), M,R)
Rg(L (PK),N,O,P)
Rh(N (PK), Q)


MySQL: LEFT JOIN not working as expected I have 2 tables: Employees, attendanceIn

When I query using LEFT JOIN

SELECT employees.eno,employees.name,employees.dept,attendanceIn.attIn FROM `employees`
LEFT JOIN attendanceIn ON employees.eno = attendanceIn.eno
WHERE date(attIn) like '2016-07-02%'

What I got is,

Whats wrong with my Query?
How do I get my Expected result?

The WHERE date(attIn) like '2016-07-02%' is converting the LEFT join to an INNER join. The condition should be moved to the ON clause.

Also:

It's not good practise to use LIKE for dates comparison
Using functions on columns (like the date()) before comparing it makes indexes useless. It's better to make the condition sargable.

The query corrected:

SELECT e.eno, e.name, e.dept, a.attIn
FROM employees AS e
  LEFT JOIN attendanceIn AS a
    ON  e.eno = a.eno
    AND a.attIn >= '2016-07-02'
    AND a.attIn  < '2016-07-03' ;

Tested at SQLfiddle.


Sql server database restore vs index rebuilds In SQL Server, if Database Restore Time takes less time compare to Index Rebuilds OR Reorganize  . Can we  Choose Restore as a option for DE-fragmentation.
If its not a 24/7 database.

That doesn't make much sense.  If you restore a database from a time earlier, you won't be capturing data that has changed since the backup. (but I'm assuming you mean a backup directly followed by a restore)

If you're talking about taking a full backup and then immediately a restore, then you will persist the fragmentation in the restored database as it was when it was backed up.

So either way I will say no that is not advisable.  Prudent index rebuilds/reorganization based on the fragmentation is best practice.

See this blog post that on database restoration and index fragmentation that debunks that myth.  Restoring a database also restores the fragmentation at the time of the backup.


Does updating a partially indexed field add or remove it from an index in Postgres We can create a partial index for a field in Postgres.

I am looking at using this to stop rows which are archived from being in an index on a created_at field:

First we create the index:

test=# CREATE INDEX idx_my_table_created_at ON my_table(created_at) WHERE NOT(archived);

We insert two rows, one which gets in idx_my_table_created_at and one which does not:

 id |         created_at         | archived
----+----------------------------+----------
  2 | 2012-10-12 17:39:17.28511  | t
  1 | 2012-10-12 17:38:55.531278 | f

Now we UPDATE a row which is presently in the index, such that it shouldn't be in the index:

test=# UPDATE my_table SET archived = true WHERE id = 1;

My question: Is id = 1 still in idx_my_table_created_at?

This question follows on from an answer to a previous question I asked on how to handle rows which won't be queried once they are old, hence archived.

(Note: I'm not a deep expert in the Pg guts. This is only my understanding. You should really read the mvcc and internals sections of the manual).

Yes, initially the row with id = 1 is still in the index, despite the index's WHERE clause excluding it because it has been updated with archived = true. At some point down the track the index entry will be purged by VACUUM or autovacuum, freeing the space.

The reason for this is PostgreSQL's MVCC design. Other concurrent transactions might still be able to see the old version of the row with id = 1, archived = false - either READ COMMITTED transactions with a long-running statement, or SERIALIZABLE transactions that don't see changes committed after they began. If the index entry were removed when the row was updated those transactions wouldn't find the row in index scans and would produce the wrong result.

Technically what's actually happening is that the old row still exists, it's just had an xmax set so newer transactions ignore it when they see it. After the update the index points to that row, same as before. A new row with archived = true has been inserted with an xmin above the old row's xmax, so any given transaction either sees the old or new version, never both. That new row is not added to the index.

Eventually there will be no open transaction that can still see the old row, so VACUUM will come by and remove the old row and its index entry, freeing the space for re-use.

Note that the index its self has no row visibility information. You can - and often do - have many versions of a row, each with index entries. When a transaction looks for matching index entries it reads all of the versions (unless it can tell from the visibility map that it doesn't need to) and ignores all but the one that has an xmin and xmax that makes it visible to that transaction.

This is one of many good reasons to ensure that autovaccum runs frequently. It reduces the number of dead index entries and dead rows you have lying around wasting space and I/O.


Review Technical ETL process for knowledge management I'm looking for source code and all related component/material to review technical ETL process between source database and Data Warehouse in order to increase my knowledge in technical Business Intelligence. Where can I find these types of source code and database?

I can find the database for adventurework OLTP and its DW but not the source code for the ETL process

Kimball Group books are great source of DW knowledge and ETL information in particular.

http://www.kimballgroup.com/html/booksMDWTtools.html

Follow this link, click Tools and Utilities, scroll to Chapter 7 — Design and Develop the ETL System.

Download Sample SSIS packages contains full solutions for Adventure Works.


Truncate tables with dependent foreign key constraints Hi I'm trying to truncate some tables from my db, but they are related with foreign key constraints so every time I try it SQL Server throws an error like this:

  Cannot truncate table 'Table' because it is being referenced by a FOREIGN KEY constraint

I don't want to drop the tables or delete them.

A friend told about a Truncate Cascade for this case, but I haven't found any info related, also other user told me to try this; I did try it, but I'm still not getting my tables truncated.

Also I read about SCRIPT for Foreign Keys DROP and RE-CREATE for use with TRUNCATE.

And the script is supposed to affect my db or at least I thought so. But after running it, I was unable to truncate my tables and it threw the same error. I'm using SQL Server 2008 R2, and running my queries with that version of SSMS.

You cannot truncate a table that has foreign key constraints. I would script your truncate statement to drop the constraints, then truncate the table, and then re-create the constraints.

Because TRUNCATE TABLE is a DDL command, it cannot check to see whether the records in the table are being referenced by a record in the child table.
This is why DELETE works and TRUNCATE TABLE doesn't: because the database is able to make sure that it isn't being referenced by another record.

Reference by SO user Michael
One more reference by SO John Rudy
As per your reference to truncate cascade, there is a delete cascade or update that is based on constraints. This is not the same as a truncate. Here is a link with some information regarding that.
In regards to your link, all it is doing is running a script that creates statements you can copy and paste, then run on your database. If you wanted it to become more automated with your truncate; you would need to find a way to store the create statements, run the drop constraint statements, truncate your table, then pull back and run the create statements.
Dynamic SQL is likely your best bet if this had to be automated. I'd look into another solution though if this were the case. Perhaps partition swapping or something else, but we'd need a lot more information to dig into other suggestions for an automated best practice process.


Change Schema Binding for All Views I have to make quite a few changes to my tables.  I have numerous views all with schema binding on.  Is there a way to turn schema binding off for all views then turn it back on again when I'm done (other than doing it individually)?  Any help or direction would be appreciated.

Please visit https://stackoverflow.com/questions/18341607/drop-schemabinding-from-all-views-in-a-database

One way would be to highlight the Views node in Object Explorer, then hit F7 (View > Object Explorer Details). On the right will be all of the views; unfortunately you can't filter or add a new column to sort based on whether the view does have SCHEMABINDING. So you'll have to manually select the ones you want (or just select all except the System Views folder), then right-click, Script View As > DROP and CREATE to > New Query Editor Window. Now you can just search that script for SCHEMABINDING, delete or comment out each instance, and then run the script. If some of the views you selected did not have SCHEMABINDING in the first place, then no big deal - they'll just get dropped and re-created too.

(This is much easier, in my opinion, than trying to search and replace CREATE VIEW with ALTER VIEW - since there could be any number of whitespace characters between CREATE and VIEW, and you'll still have to comment or remove the SCHEMABINDING. Though it might not hurt, as part of this exercise, to ensure that the syntax conventions here are consistent, and fix views that don't adhere, e.g. changing CREATE______VIEW to CREATE_VIEW (underscores to represent spaces).)

Note that this will obviously delete any indexes that are associated with these views.

Also note that the generate scripts wizard will also allow you to select all of the views, but not filter on whether they have SCHEMABINDING, and the wizard won't script ALTER but you can choose DROP and CREATE in Advanced settings. You'll still need to adjust the output to remove the SCHEMABINDING and, depending on options selected, you may need to remove any CREATE INDEX statements for indexed views that will now fail.


List distinct column values where those rows share other column values I'm trying to write a query to detect possibly-invalid data in a PostgreSQL table. We have a table of city names like this:

# `city_names`
id    | name     |   language | dialect | city_id
------------------------------------------------
01    | London   |   A        | A1      | 1
02    | London   |   A        | A2      | 1
03    | London   |   B        | B1      | 2
04    | London   |   B        | B2      | 3

In our domain:

It's fine that rows 01 and 02 both map "London" to city 1; the dialects don't happen to differ
It's fine that row 03 maps "London" to city 2; in that language, the name may refer to a different city
It's suspicious that row 04 maps "London" to city 3, because we already have a mapping to city 2 in the same language

I want to write a query that selects only rows 03 and 04 so that a human can decide whether one of them points to the wrong city.

I can solve this problem procedurally, but I'm having trouble doing it in SQL. For example, if I GROUP BY language and name, I lose the city_id values from the individual rows.

Basically my goal is: "If there's more than one city_id for the same name and language, list those city_ids."

How can I do this?

This query will do. The trick is to use COUNT(DISTINCT city_id):

SQL Fiddle

PostgreSQL 8.3.20 Schema Setup:

CREATE TABLE Table1
    ("id" int, "name" varchar(6), "language" varchar(1), "dialect" varchar(2), "city_id" int)
;

INSERT INTO Table1
    ("id", "name", "language", "dialect", "city_id")
VALUES
    (01, 'London', 'A', 'A1', 1),
    (02, 'London', 'A', 'A2', 1),
    (03, 'London', 'B', 'B1', 2),
    (04, 'London', 'B', 'B2', 3)
;

Query 1:

select t.*, d.dups
FROM table1 t INNER JOIN
(
  select name, language, count(distinct city_id) as dups
  from table1
  group by name, language
  having count(distinct city_id) > 1
) d
ON t.name = d.name and t.language = d.language

Results:

| ID |   NAME | LANGUAGE | DIALECT | CITY_ID | DUPS |
|----|--------|----------|---------|---------|------|
|  3 | London |        B |      B1 |       2 |    2 |
|  4 | London |        B |      B2 |       3 |    2 |


Optimizing a DMV query I have found a query that is a major performance issue in my environment. I looked at the actual execution plan for this query and found out the main problem is the remote scan (98%). Its remote because SQL Server is accessing its system objects (DMVs).

If you look at the estimated number of rows it says 33, but the actual number of rows is over 16 000.

If it was a regular table query I would look at the statistics for the columns but in this case I don't know what I can do.

DECLARE @Duration FLOAT
DECLARE @Date DATETIME
DECLARE @MinDuration FLOAT
SET @MinDuration = 5
SET @Duration = null

SELECT TOP 1 @Duration = wt.wait_duration_ms / 1000.0
FROM sys.dm_tran_locks AS tl
INNER JOIN sys.databases db ON db.database_id = tl.resource_database_id
INNER JOIN sys.dm_os_waiting_tasks AS wt ON tl.lock_owner_address = wt.resource_address
INNER JOIN sys.partitions AS p ON p.hobt_id = tl.resource_associated_entity_id
INNER JOIN sys.dm_exec_connections ec1 ON ec1.session_id = tl.request_session_id
INNER JOIN sys.dm_exec_connections ec2 ON ec2.session_id = wt.blocking_session_id
CROSS APPLY sys.dm_exec_sql_text(ec1.most_recent_sql_handle) AS h1
CROSS APPLY sys.dm_exec_sql_text(ec2.most_recent_sql_handle) AS h2
order by wt.wait_duration_ms desc

IF (@Duration IS NOT NULL and @Duration > @MinDuration )
BEGIN
    SET @Date = GETDATE()
    INSERT INTO Log_Locks
        SELECT
        db.name DBName,
        tl.request_session_id,
        wt.blocking_session_id,
        OBJECT_NAME(p.OBJECT_ID) BlockedObjectName,
        tl.resource_type,
        h1.TEXT AS RequestingText,
        h2.TEXT AS BlockingTest,
        tl.request_mode,
        wt.wait_duration_ms,
        @Date,
        es.host_name BlockedHostName,
        ec2.client_net_address BlockedClientNetAddress
        FROM sys.dm_tran_locks AS tl
        INNER JOIN sys.databases db ON db.database_id = tl.resource_database_id
        INNER JOIN sys.dm_os_waiting_tasks AS wt ON tl.lock_owner_address = wt.resource_address
        INNER JOIN sys.partitions AS p ON p.hobt_id = tl.resource_associated_entity_id
        INNER JOIN sys.dm_exec_connections ec1 ON ec1.session_id = tl.request_session_id
        INNER JOIN sys.dm_exec_connections ec2 ON ec2.session_id = wt.blocking_session_id
        left join sys.dm_exec_sessions es ON es.session_id = wt.blocking_session_id
        CROSS APPLY sys.dm_exec_sql_text(ec1.most_recent_sql_handle) AS h1
        CROSS APPLY sys.dm_exec_sql_text(ec2.most_recent_sql_handle) AS h2
END

Do you have any ideas on how to optimize it?

A dynamic management view, like the name implies, isn't a table with indexes and statistics, but rather a view which could potentially use a large number of system tables.

To generally speed up complex DMV queries, you could try dumping the contents of some of the larger DMVs into a temp table or table variable and then use those. You can control the indexing on the temp tables, and it also reduces the number of join operations that the server has to perform.

For the same reason, if your solutions allows for it, consider using WITH (NOLOCK) in order to minimize locking on those system tables, which could otherwise affect your entire database or server.


How to write a query which finds all circular references when a table references itself? I have the following schema (names changed), which I cannot change:

CREATE TABLE MyTable (
    Id INT NOT NULL PRIMARY KEY,
    ParentId INT NOT NULL
);

ALTER TABLE MyTable ADD FOREIGN KEY (ParentId) REFERENCES MyTable(Id);

That is, each record is a child of another record. If a record’s ParentId is equal to its Id, then the record is considered a root node.

I want to run query which will find all circular references. For example, with the data

INSERT INTO MyTable (Id, ParentId) VALUES
    (0, 0),
    (1, 0),
    (2, 4),
    (3, 2),
    (4, 3);

the query should return

Id | Cycle
2  | 2 < 4 < 3 < 2
3  | 3 < 2 < 4 < 3
4  | 4 < 3 < 2 < 4

I wrote the following query for SQL Server 2008 R2, and I am wondering if this query can be improved:

IF OBJECT_ID(N'tempdb..#Results') IS NOT NULL DROP TABLE #Results;
CREATE TABLE #Results (Id INT, HasParentalCycle BIT, Cycle VARCHAR(MAX));

DECLARE @i INT,
    @j INT,
    @flag BIT,
    @isRoot BIT,
    @ids VARCHAR(MAX);

DECLARE MyCursor CURSOR FAST_FORWARD FOR
    SELECT Id
    FROM MyTable;

OPEN MyCursor;
FETCH NEXT FROM MyCursor INTO @i;
WHILE @@FETCH_STATUS = 0
BEGIN
    IF OBJECT_ID(N'tempdb..#Parents') IS NOT NULL DROP TABLE #Parents;
    CREATE TABLE #Parents (Id INT);

    SET @ids = NULL;
    SET @isRoot = 0;
    SET @flag = 0;
    SET @j = @i;
    INSERT INTO #Parents (Id) VALUES (@j);

    WHILE (1=1)
    BEGIN
        SELECT
            @j = ParentId,
            @isRoot = CASE WHEN ParentId = Id THEN 1 ELSE 0 END
        FROM MyTable
        WHERE Id = @j;

        IF (@isRoot = 1)
        BEGIN
            SET @flag = 0;
            BREAK;
        END

        IF EXISTS (SELECT 1 FROM #Parents WHERE Id = @j)
        BEGIN
            INSERT INTO #Parents (Id) VALUES (@j);
            SET @flag = 1;
            SELECT @ids = COALESCE(@ids + ' < ', '') + CAST(Id AS VARCHAR) FROM #Parents;
            BREAK;
        END
        ELSE
        BEGIN
            INSERT INTO #Parents (Id) VALUES (@j);
        END
    END

    INSERT INTO #Results (Id, HasParentalCycle, Cycle) VALUES (@i, @flag, @ids);

    FETCH NEXT FROM MyCursor INTO @i;
END
CLOSE MyCursor;
DEALLOCATE MyCursor;

SELECT Id, Cycle
FROM #Results
WHERE HasParentalCycle = 1;

This calls for a recursive CTE:

WITH FindRoot AS
(
    SELECT Id,ParentId, CAST(Id AS NVARCHAR(MAX)) Path
    FROM dbo.MyTable

    UNION ALL

    SELECT C.Id, P.ParentId, C.Path + N' > ' + CAST(P.Id AS NVARCHAR(MAX))
    FROM dbo.MyTable P
    JOIN FindRoot C
    ON C.ParentId = P.Id AND P.ParentId <> P.Id AND C.ParentId <> C.Id
 )
SELECT *
FROM FindRoot R
WHERE R.Id = R.ParentId
  AND R.ParentId <> 0;

See it in action here: SQL Fiddle

Update:

Added distance to be able to exclude all self cycles (see ypercube's comment):

WITH FindRoot AS
(
    SELECT Id,ParentId, CAST(Id AS NVARCHAR(MAX)) Path, 0 Distance
    FROM dbo.MyTable

    UNION ALL

    SELECT C.Id, P.ParentId, C.Path + N' > ' + CAST(P.Id AS NVARCHAR(MAX)), C.Distance + 1
    FROM dbo.MyTable P
    JOIN FindRoot C
    ON C.ParentId = P.Id AND P.ParentId <> P.Id AND C.ParentId <> C.Id
 )
SELECT *
FROM FindRoot R
WHERE R.Id = R.ParentId
  AND R.ParentId <> 0
  AND R.Distance > 0;

SQL Fiddle

Which one you should use depends on your requirement.


Full Plan Cache Behavior In SQL Server 2005, what happens when the plan cache fills up?  I have done some research, and from bits and pieces gathered it seems that cached plans are given an "age" that consists of it's weight, or cost to compile, multiplied by the number of times it has been run since being cached.  Over time this age is decremented, until it reaches 0, when it is considered "aged out" and is a candidate for flushing when needed.  When the plan cache fills, aged out plans are flushed from the cache.

Is there anything else that is happening behind the scenes to complicate this scenario?

Thanks,

Matt

Best info for the subject found on MSDN is here: Execution Plan Caching and Reuse
.
Quote from the MSDN article:

"Removing Execution Plans from the Procedure Cache

Execution plans remain in the procedure cache as long as there is enough memory to store them. When memory pressure exists, the Database Engine uses a cost-based approach to determine which execution plans to remove from the procedure cache. To make a cost-based decision, the Database Engine increases and decreases a current cost variable for each execution plan according to the following factors.

When memory pressure exists, the Database Engine responds by removing execution plans from the procedure cache. To determine which plans to remove, the Database Engine repeatedly examines the state of each execution plan and removes plans when their current cost is zero. An execution plan with zero current cost is not removed automatically when memory pressure exists; it is removed only when the Database Engine examines the plan and the current cost is zero. When examining an execution plan, the Database Engine pushes the current cost towards zero by decreasing the current cost if a query is not currently using the plan."

It's better to read the complete article, it's very well documented. See that it also has some links.


How to update DynamoDB item by searching on GSI primary key? Table has these columns on DynamoDB:

primaryKey
sortKey
GSI_primaryKey
data

And there is GSI added with primary key GSI_primaryKey and sort key on primaryKey

How to update column data if I have only the value of the GSI_primaryKey?

With NodeJS document client.

Thanks :)

What I found out is I need to get the item by GSI and then using primaryKey and sortKey update the item and cannot update item using GSI only.


SQL Server Failover Cluster Instances: why current vote is 0? I set up a 2 node SQL Server Fail-over Cluster Instances. Each node is EC2 instance.
At the moment, under "Cluster core resources", my cluster is online. But one node is online and the other one is offline.

As i checked the Nodes, i noticed that: Both are Up, assigned vote = 1. But one has current vote = 1, the other one has current vote = 0.

If i set a file share witness from 3rd server (the one is outside of cluster and used to manage), the node with current vote = 0 turned to 1.

Why is that?

As i checked the Nodes, i noticed that: Both are Up, assigned vote = 1. But one has current vote = 1, the other one has current vote = 0.

That's dynamic quorum kicking in with a 2 node cluster and no witness. I definitely wouldn't continue to run this way, either add another node or add a witness.

  If i set a file share witness from 3rd server (the one is outside of cluster and used to manage), the node with current vote = 0 turned to 1.

This is due to dynamic quorum and dynamic witness kicking in. Dynamic witness says if there are an even number of possible votes, give the witness a vote. If there are an odd number, take the vote away.

Since you have two nodes each with a possible vote (assigned = 1) then there are an even number of possible votes. Thus, dynamic witness says the witness must have a vote to make it an odd number. Dynamic quorum then says that it feels the best way to set the dynamic weights so that the cluster has the best uptime is to set all votes to 1.

Dynamic quorum is in WS2012 and newer, it can be turned off but is on by default.
Dynamic witness is in WS2012R2 and newer, it cannot be turned off.


Same query taking 0.2 sec in Oracle database but 4 mins in SQL Server SELECT A.Field1 FROM Table1 A INNER JOIN Table1 B
ON A.Field2 = B.Field2
AND A.Field3 = B.Field3
AND A.Field4 = B.Field4
AND B.Field1 = "string1"
AND B.Field2 = "string2"
AND A.Field5 = (SELECT MAX(C.Field5) FROM Table1 C INNER JOIN Table2 D
                 ON
                 (C.Field5 < B.Field5
                 AND B.Field6 = D.Field6
                 AND C.Field2 = B.Field2
                 AND C.Field3 = B.Field3
                 AND C.Field4 = B.Field4
                 AND D.Field7 = 'Y')
              OR
                 (C.Field5 < B.Field5
                 AND B.Field6 = D.Field6
                 AND C.Field2 = B.Field2
                 AND C.Field3 = B.Field3
                 AND C.Field4 = B.Field4
                 AND D.Field7 = 'N')
              )

This query takes 0.2 sec in an Oracle database but 4 mins in SQL Server database.

Both the databases have the same indexes, the same records structure and the same column structure.

Can anyone tell me why?

There's a chance that the SQL server table or index statistics are out of date, aren't as accurate as they could be (both of which can affect how the SQL query plan is put together) or SQL could be reusing an old query plan which was based on a smaller data set.

If the statistics are out of date, or aren't accurate, then SQL will generate the query plan based on flawed data, so you'll get poorly performing code (looking specifically at your SQL Query plan and the 98% on the nested loop, it might just be a single index, or one of the tables affected by that part of the query plan).

If the statistics are up to date and you're still getting poor performance and the query plan is pushing everything through a Nested Loop, you could try forcing it to use something else like using an OPTION query hint, although try it out on a dev system first.
MSDN - Query Hints article

This quote posted on SQLServerCentral by Kevin Boles is a something to keep in mind when looking at inefficient query plans.

  One thing to watch out for here is when the optimizer THINKS there will be few rows (check the estimated rows in the popup for the estimated query plan graphic) but in reality there are LOTS of rows (thousands or even millions).

Specifically regarding the nested joins, Gail Shaw wrote this article on execution plan operations joins, which is a good reference on the physical join types and where/how they're normally used.


Does PostgreSQL support wildcard indexes on JSON? I have a multi-tenant service with a table like this:
project_id | user_id | user_properties

Each project belongs to a different customer and customers can freely attach metadata to their users. A project may have millions of users.
Then a customer may want to find some users inside his project filtering with the user_properties (e.g. age grater than X, favorite music equal to Y, etc.)
user_properties can be an arbitrary json of key-value pairs and a customer can run arbitrary queries on the user_properties. The json is not nested (only key-value pairs).
Since a query may return many results it would also be useful to use some sort of pagination (e.g. order by user_id + limit). But pagination, together with arbitrary filters, seems an additional issue for performance...
Is it possible to handle that case in PostgreSQL? Is EAV the only solution?
I see that MongoDB supports wildcard indexes: does PostgreSQL offer anything similar?

This won't work. In particular, you cannot create a single index that will support conditions like
WHERE (user_properties ->> 'some random property')::date >= '2000-01-01'::date

for arbitrary attributes.
You will have to identify the properties you want to use in comparisons and create specialized indexes on these properties. In the above sample, that would be:
CREATE INDEX ON mytable (((user_properties ->> 'some random property')::date));

It would be better to extract such columns from the JSON and have them be regular table columns.


How do I join on a timestamp and include NULL entries? I am attempting a query to compare the sum of daily accounting invoices to a daily budget. The budget is stored as one value for a month, so I divide that amount by the total number of days as in April would have 30 entries.

However, I want the budget amount over days to exist permanently even if there are no invoices for that day, either yet or actually on that day.

My current query joins on the date_part('day',a.date) of a timestamp which only hits if there are invoices on that day. I am using PostgreSQL version 11.3. I have tried all join possibilities, and  end up with the same result each time. Unless I use CROSS JOIN which does give all 30 days, but the sum of ALL invoices against each day. I had thought that a FULL OUTER JOIN would work, or some other way of using UNION ALL, but as yet I am unsuccessful.

WITH RECURSIVE t(days,budget) AS (
 SELECT 1 as days,b.amount/30 AS budget
  FROM budgets b
  WHERE b.id = 10
 UNION ALL
  SELECT t.days + 1,t.budget
   FROM t WHERE t.days < 30)
SELECT
 t.days AS "Day",
 COALESCE ( SUM ( CASE WHEN a.account_id IN (17,43,44)
       THEN a.credit - a.debit ELSE 0 END),0) AS "Invoices",
 t.budget AS "Budget"
FROM t
LEFT JOIN accounting a
 ON date_part('day',a.date) = t.days
WHERE a.date >= '2020-04-01'
 AND a.date <= '2020-04-30'
 AND a.account_id = 20
GROUP BY 1,3;

For the ouput, lets assume days 1, 2, and 3 have passed where day 2 had no entries.
Current Output
Day Invoices Budget
1 100 50
3 75 50

Desired Output
Day Invoices Budget
1 100 50
2 0 50
3 75 50
4 0 50
5 0 50
...
30 0 50

I am open to other query ideas to accomplish this. I appreciate the help!

I have managed to get this, by removing the join altogether from the main query and creating a CASE WHEN subquery to look first if a match is made, and assign 0 if not. Then, to sum the invoices on conditions met.

If there is a better method to handle this than the query I now have below, please let me know. Since I am working with a CASE WHEN and subqueries within, it reads to me that this part can be better streamlined somehow. But if it works, it works and does not seem terribly expensive.

Also, please note: above I make a reference to a.account_id in error, on line 18 (second from the bottom). That happened when I modified the exact tables being used in my situation to a more simple set for understanding the SQL being used. To be sure of the fields in my accounting table representation:

a.account_id = IDs representing Chart of Accounts
a.invoice_id = IDs representing the Sales Group on the invoice (this above is in error at a.account_id is 20, and should read a.invoice_id = 20)
a.credit = Double Ledger Accounting Credits
a.debit = Double Ledger Accounting Debits

WITH RECURSIVE t(days,budget) AS (
SELECT 1 as days,b.amount/30 AS budget
FROM budgets b
WHERE b.id = 10
UNION ALL
SELECT t.days + 1,t.budget
FROM t WHERE t.days < 30)
SELECT
t.days AS "Day",
SUM ( CASE WHEN
( SELECT a.date FROM accounting a
LEFT JOIN t ON date_part('day',a.date) = t.days
WHERE a.date >= '2020-04-01'
AND a.date <= '2020-04-30'
AND a.invoice_id = 20
LIMIT 1) IS NULL
THEN 0 ELSE
( SELECT SUM
( CASE WHEN a.account_id IN (17,43,44)
THEN a.credit - a.debit ELSE 0 END)
FROM accounting a
WHERE date_part('day',a.date) = t.days
AND a.date >= '2020-04-01'
AND a.date <= '2020-04-30'
AND a.invoice_id = 20)
END ) AS "Invoices",
t.budget AS "Budget"
FROM t
GROUP BY 1,3
ORDER BY 1;

I appreciate those who took a peek at this query, and considered an approach to solve it.


How to take Backup of BackUp DB in SQL Server 2012 In Our environment, Log log shipping is configured and working good.

Transaction logs are Backed-up on Primary Server.
Copy to Backup DB
Restore on Backup DB.

Background:

We are in process of changing the IP of Backup (Secondary) DB server. And before changing IP, client wants to take the backup of the Secondary(Backup) DB.

Question:

Is it possible to take the backup of Secondary Database?
(When I Right click on Backup (Secondary) DB --> Task --> backup, backup option is grayed out.)
--> Do I need to 'Take Offline' to take back up?
--> Or is there any other way?
Will there be any impact if we change IP of Backup (Secondary) DB and do we need to make changes anywhere else?
Will Log shipping keeps running without any issues, or do we need to reconfigure it as new?

Changing the IP of the secondary server shouldn't be an issue as long as you used DNS names in the log shipping configuration.

To answer your questions:

No you can't take a backup of a standby database, at least not with the native functionality in SQL Server.
As long as all the setting stay the same, there shouldn't be any impact. You can disable the copy and restore job before the change though.
There shouldn't be any impact as long as you used DNS names in your log shipping configuration. The only thing that I can think of is that you need to refresh the DNS cache of the primary server to get the new IP address.


question about MySQL5.7 server(Master) and a MariaDB 10.3(Slave) I have question:

I want to install a MySQL5.7 server(Master) and a MariaDB 10.3(Slave).Replay SQL(select and update) on the slave server after SQL running on the master server.get slow sql log on slave server(MariaDB).how to deployed it ?

Thanks!

There are some lesser-known settings that should be set on the Slave.  Pehaps you are thinking about log_slow_slave_statements.  More details here:  http://mysql.rjweb.org/doc.php/mysql_analysis#slow_queries_and_slowlog

Note: MySQL / MariaDB / Percona -- these have diverged somewhat in what options are available.  Also, newer versions have more settings.  The above like provides my recommendations, but check the details with the manual.


SQL SERVER 2014 - View value changes depending on the date In a Data Mart I have a table with information about Players (Dimension Player). And It have the following attributes:

Name (nvarchar(250))
Dt_Contract (date)
New_Player (int)
DT_Execution (datetime)

The New_Player is a “binary code” and it show us the new team players. It is calculated as follows:
MONTH(DT_Contract) = MONTH(CAST(@DT_Execution AS DATE)) THEN 1 ELSE 0 END
So,

1 – Is a new player

0 – Is a old player
The Dt_Execution is  the Date on which the process runs

I liked to be able to see what new players on a specific date e not only when Month(DT_Conttract) = Month(Dt_Execution)

Basically,
Players who have been entered today are with the correct value in New_Player but if I want to observe what were the new players last month I will not be able to watch what they were. I think I need something more dynamic paw achieve this flexibility.

Does anyone have any idea how can I get it? What type of modification I have to implement in the table?

PS: The code is inserted SP that runs every day.

It sounds like the Dt_Execution changes, or is updated over time? If so, that will make it difficult to see "new players on a certain date" as there is no history.

In a scenario like this, the "New_Player" attribute should be calculated based on dates, so that either a current or past date could be used.
It may not be appropriate for this table, but you could create another table that has history of Dt_Execution (for weekly games, for example). Then this example table you listed could be compared to that history; if the player's contract date was within a week of the date you select, the procedure or view would calculate that he's a new player.

Without a historical table, this would be difficult or impossible.


Shopping cart schema with multiple product tables I have a shopping cart table used to store customer id and product id of their chosen items. However I have multiple product tables, for example cars, books, houses. I started out with one table but then realized using an auto increment key for the product tables might cause a problem, as I will not know which table to join the shopping cart with. I originally started out with one table so I did not have this problem before, but now I have to expand it to accommodate two additional product tables. I am looking for some help in how to restructure this schema to properly store products to the cart table.

Cars

╔══════════╦═══════════╦══════════╗
║ CarID    ║ Model     ║ Brand    ║
╠══════════╬═══════════╬══════════╣
║        0 ║ E 320     ║ Bmw      ║
║        1 ║ JR 505    ║ Audi     ║
╚══════════╩═══════════╩══════════╝

Books

╔══════════╦═══════════╦══════════╗
║ Book ID  ║ Author    ║ Title    ║
╠══════════╬═══════════╬══════════╣
║        0 ║ A.R       ║ Smithy   ║
║        1 ║ J.R.      ║ Hailey's ║
╚══════════╩═══════════╩══════════╝

Houses

╔══════════╦═══════════╦══════════╗
║ HouseID  ║ Address   ║ Town     ║
╠══════════╬═══════════╬══════════╣
║        0 ║ Belmont Av║ Bull Bay ║
║        1 ║ Cedar Drv ║ Cinclairs║
╚══════════╩═══════════╩══════════╝

Cart Table

╔══════════╦═══════════╦══════════╗
║ CartID   ║ memberID  ║ ProductID║
╠══════════╬═══════════╬══════════╣
║        0 ║ 3         ║ 1        ║
║        1 ║ 5         ║ 0        ║
╚══════════╩═══════════╩══════════╝

I would solve this by adding a generic PRODUCT table containing the attributes which are common for all products (including the ProductID) and a ProductType column describing the type of product (Car, Book etc). Tables CAR, BOOK can then contain the same ProductID as in the main PRODUCT table and attributes which are specific to their product type.


Incorrect behaviour of SQL Server 2016 with memory-optimized tables Please have a look at the following SQL query:

CREATE TYPE dbo.IN_MEMORY_TABLE_TYPE AS TABLE
(
  source_col INT NULL,
  target_col INT not NULL
  INDEX ix_InMemoryTable NONCLUSTERED (target_col)
)
WITH (MEMORY_OPTIMIZED = ON)
GO

DECLARE
  @t dbo.IN_MEMORY_TABLE_TYPE

INSERT @t
(
  source_col,
  target_col
)
VALUES
  (10, 0),
  (0, 0)

UPDATE r1
SET
  target_col = -1
FROM @t r1
WHERE EXISTS
      (
        SELECT *
        FROM @t r2
        WHERE r2.source_col > 0
      )

SELECT *
FROM @t

GO
DROP TYPE dbo.IN_MEMORY_TABLE_TYPE

When executing it on SQL Server 2014 (12.0.4100.1 X64) the UPDATE in the query performs as expected and the following valid result is returned:

source_col | target_col
----------------------
10         | -1
0          | -1

However, when executing on SQL Server 2016 (13.0.4001.0 X64) not all the rows get updated and the following is returned:

source_col | target_col
----------------------
10         | -1
0          | 0

This looks like a bug to me, does it look so to you?

Yes it is a bug, which seems to only affect table variables, with a bw-tree index access method, and an uncorrelated self-join.

Simplified repro using DELETE:

CREATE TYPE dbo.IN_MEMORY_TABLE_TYPE AS TABLE
(
    col integer NOT NULL INDEX i NONCLUSTERED (col)
)
WITH (MEMORY_OPTIMIZED = ON);
GO
DECLARE @T AS dbo.IN_MEMORY_TABLE_TYPE;

INSERT @T (col)
VALUES (1), (2), (3), (4), (5);

DELETE T
FROM @T AS T
WHERE EXISTS
(
    SELECT 1
    FROM @T AS T2
    WHERE T2.col = 1 -- Vary this number 1-5
);

SELECT T.col FROM @T AS T;
GO
DROP TYPE dbo.IN_MEMORY_TABLE_TYPE;

Note in the above plan the search for rows to delete terminates earlier than expected (only two rows are read from the scan). Halloween Protection is generally correctly handled for In-Memory OLTP, there just seems to be a specific issue with the combination of factors mentioned above.

This bug is fixed in SQL Server 2016 SP1 CU5 and SQL Server 2017 CU1:

FIX: Incorrect behavior when you use memory-optimized tables with "where exists" statement in SQL Server 2016 or 2017


"column ... does not exist" error when calling an SQL procedure (new in PostgreSQL 11) Using PostgreSQL 11 Beta 2, a very simple trading_holiday table is created in the config schema:

DROP TABLE IF EXISTS config.trading_holiday;
CREATE TABLE config.trading_holiday
(
  id smallint GENERATED ALWAYS AS IDENTITY NOT NULL,
  holiday_date DATE,
  name CHARACTER VARYING(80)
);
ALTER TABLE config.trading_holiday
  ADD CONSTRAINT trading_holiday_pk
  PRIMARY KEY (id);

Then a very simple stored procedure, config.sp_add_holiday is created to add holidays to the table:

CREATE OR REPLACE PROCEDURE config.sp_add_holiday(holiday_date DATE,
                                                  name CHARACTER VARYING(80))
AS $$
BEGIN
  INSERT INTO config.trading_holiday(holiday_date,name)
    VALUES(sp_add_holiday.holiday_date,sp_add_holiday.name);
END
$$
LANGUAGE PLPGSQL;

Then a simple call is made to add the first holiday:

CALL config.sp_add_holiday(holiday_date='2018-01-01',name='New Years Day');

And I get the following error message:

[2018-08-07 11:56:18] [42703] ERROR: column "holiday_date" does not exist
[2018-08-07 11:56:18] Position: 21

Doing a manual insert, e.g.:

INSERT INTO config.trading_holiday(holiday_date,name)
  VALUES('2018-01-01','New Years Day');

Works successfully:

[2018-08-07 12:04:01] 1 row affected in 2 ms

Despite being new to the PostgeSQL 11 SQL procedure functionality (who isn't?), this seems like such a simple proc. What in the world am I doing wrong?

The problem is with the parameter assignment in the call. To fix:
CALL config.sp_add_holiday(holiday_date => '2018-01-01', name => 'New Years Day');

db<>fiddle here
Explanation
Parameter notation is the same for functions and procedures (new in Postgres 11). The manual:

See Section 4.3 for the full details on function and procedure call syntax, including use of named parameters.

Bold emphasis mine.
You have been using = by mistake. That's the plain old "equal" operator, forming a boolean expression. Obviously not what you intended. The expression is evaluated, Postgres looks for a column named "holiday_date" in the calling SQL context and cannot find it. Hence the error message:

ERROR: column "holiday_date" does not exist

Only => (SQL-standard notation ) or := are valid for parameter assignment in the call. Details in the manual. Related:

Functions with variable number of input parameters

There is a history of misunderstandings around the use of ...

... the operators = and := in plpgsql code.
... the use of = as short syntax for DEFAULT in CREATE FUNCTION and now also CREATE PROCEDURE statements.
... the assignment notation => and := in function calls.

Further reading:

The forgotten assignment operator “=” and the commonplace “:=”


Best practices to encrypt all the data I'm working in a project, where the client is afraid their own employees (ie.: junior I.T. guys who the client does not trust) querying the database to get sensitive information.

The database is a new SQL Server database, so I have some kind of freedom.

Googling a bit, I landed here at SQL Server Transparent Data Encryption, and wondered if that what I need, and if it is not...

What are the best practices to encrypt all the columns, in all the tables, in a database in order to prevent users for querying the database?

Your problem boils down to access control.

The first defense I'd propose is to simply deny access to the untrusted users. If they can't get into the database, they can't query the database and get at the sensitive data.

If they must be allowed to access the database server, you can look at either explicitly granting them read permission to the tables they must access to perform their job tasks. Alternatively, you can leave their current permissions alone and simply revoke their ability to query tables or columns within those tables with sensitive data.

The reasons I'd start with this approach is that there is overhead to encryption. There is key management as well as the CPU cost of encrypting/decrypting the data. As I understand it, there's also going to be a performance cost of looking up data as the encrypted data if it's in an index, the encrypted value will be indexed and not the source value. The net result is that 2013-06-11 and 2013-06-12 could have been stored in contiguous disk locations but once they're encrypted they could be on opposite ends of the disk and your simple range query which used to perform well now sucks hind teat.

All of that said, Steve Jones had a good presentation on the Encryption Primer. I used some of that content plus this article on encryption in the DB but not in the cube to get our encryption stuff up and running.

As others have pointed out in the comments and other answers, despite having Encryption as part of its name, TDE, isn't going to protect the data in the database itself. The purpose of TDE is to protect your backups from unauthorized access. It has nothing to do with encrypted packets flowing across the network or automagically creating PCI compliance. It simply ensures that if our offsite backup tapes are lost, stolen or simply snooped, people will not be able to use them without the key.

The other point to access control that I failed to mention is physical access. If the concern is the junior admin accessing data they shouldn't, then in addition to denying them access to the database, don't overlook basics of preventing them from accessing the machine itself---either through remote desktop or physically logging onto the machine. Once they are able to get onto the box, there's nothing preventing them from restarting SQL Server into single user mode and removing your access controls.


How to return resultset from MySQL Stored Procedure using prepared statement? DELIMITER $$

CREATE PROCEDURE List_IL()

BEGIN

  DECLARE Project_Number_val VARCHAR( 255 );
  DECLARE Temp_List_val VARCHAR(255);
  DECLARE Project_List_val VARCHAR(255);
  DECLARE FoundCount INT;
  DECLARE Project_Number INT;
  DECLARE db_Name VARCHAR(255);

  DECLARE no_more_rows BOOLEAN;
  DECLARE loop_cntr INT DEFAULT 0;
  DECLARE num_rows INT DEFAULT 0;

  DECLARE   projects_cur    CURSOR FOR
    SELECT  Project_Id
    FROM    Project_Details;

  DECLARE CONTINUE HANDLER FOR NOT FOUND
    SET no_more_rows = TRUE;

  OPEN projects_cur;
  select FOUND_ROWS() into num_rows;

  the_loop: LOOP

    FETCH  projects_cur
    INTO   Project_Number_val;

    IF no_more_rows THEN
        CLOSE projects_cur;
        LEAVE the_loop;
    END IF;

SET Project_List_val = CONCAT(Project_Number_val, '_List');
SET db_Name='panel';

SELECT COUNT(1) INTO  FoundCount  FROM information_schema.tables WHERE table_schema = `db_Name`  AND table_name = `Project_List_val`;

 IF FoundCount = 1 THEN
SET @Project_Number=Project_Number_val;

SET @sql = CONCAT(' SELECT Panel_Id,', Project_Number_val,'
                    FROM ', @Project_List_val,' Where status=1');

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
END IF;

    SET loop_cntr = loop_cntr + 1;
  END LOOP the_loop;

END $$

In the above stored procedure How can I get the all the rows selected during execution of prepared statement and after the loop terminates I want to return the entire result set whichever calls the stored procedure. Can you please help me how to do this?

I will suggest you a solution create a log table like this

CREATE TABLE log_records
(
Panel_Id INT,
Project_Number_val VARCHAR(255)
);

In your code add lines as follows here i will insert those record into log_table which you was selecting please look the line /**Added by abdul */

    IF FoundCount = 1 THEN
        SET @Project_Number=Project_Number_val;

        SET @sql = CONCAT(' SELECT Panel_Id,', Project_Number_val,'    FROM ', @Project_List_val,' Where status=1');

    /*--Added by Abdul*/
        SET @insert_sql = CONCAT ('INSERT INTO log_records' ,'SELECT Panel_Id,', Project_Number_val,'    FROM ', @Project_List_val,' Where status=1');
        PREPARE stmt_insert FROM @insert_sql;
        EXECUTE stmt_insert;
        DEALLOCATE PREPARE stmt_insert;
    /**--End**/

        PREPARE stmt FROM @sql;
        EXECUTE stmt;
        DEALLOCATE PREPARE stmt;
    END IF;

And then where loop end add line

SELECT * FROM log_records;

This will give you the desired result.

Also Write one line in your code before you open the cursor otherwise each procedure call will be appending data in that table.

TRUNCATE TABLE log_table;


Enabling admin connection on SQL Server Express to fix logon trigger Well, luckily I got this error on my test environment. I'm so scared about this error that I disabled all logon triggers I was using on production environment.

I created the famous logon trigger, and I put a database in offline mode just to mess with something else. Then I discover that because the trigger can't find the table, I can't access the instance anymore. There are plenty of questions related to this, but even doing everything I could find, I still can't acces the instance, not even with SQLCMD.

I'm doing this:

sqlcmd -S server\instance -U sa -P <pass>  -A

If I don't use -A I receive the famous error:

logon failed for sa due trigger execution.

If I use -A:

  Sqlcmd: Error: Microsoft ODBC Driver 11 for SQL Server : SQL Server
  Network Interfaces: An error occurred while obtaining the dedicated
  administrator connection (DAC) port. Make sure that SQL Browser is
  running, or check the error log for the port number [xFFFFFFFF]. .
  Sqlcmd: Error: Microsoft ODBC Driver 11 for SQL Server : Login timeout
  expired. Sqlcmd: Error: Microsoft ODBC Driver 11 for SQL Server : A
  network-related or instance-specific error has occurred while
  establishing a connection to SQL Server. Server is not found or not
  accessible. Check if instance name is correct and if SQL Server is
  configured to allow remote connections. For more information see SQL
  Server Books Online..

Why i'm not able to connect to the sql server

sql server express here.

I could enable SQL Browser, but I still can't access it.

I don't think this question is duplicated because is not about the trigger, is about Why I can't access the server with SQLCMD. I pretty much know how to fix this. I just don't know how can I acces sql server without triggering the trigger.

The documentation for "Diagnostic Connection for Database Administrators" states:

  To conserve resources, SQL Server Express does not listen on the DAC port unless started with a trace flag 7806.

That TF needs to be specified either on the command line, if you are starting SQL Server via the command line, or in the Registry if you are starting via the Services interface (the typical method).

To get this into the Registry:

open a command prompt
run regedit
navigate to the following key:

  Computer\HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL{version_number}.{instance_name}\MSSQLServer\Parameters

where {version_number} is the 2-digit number of the release, such as "11" for SQL Server 2012, and "14" for SQL Server 2017.
Add the following String Value:

SQLArg3
Edit the new String Value to provide the following value data:

-T7806

Please note: upper-case "T", and no space between the "-T" and the number!


Enable Auto Update Stats in Async mode We have a table with 10 billion records, where daily there are hundreds of thousands of updates/inserts/selects happening. As the auto-update stats was ON there were a few performance issues; hence, we turned it OFF and there were positive results.

Now, we want to turn the auto-update stats on in async mode, then perform another round of testing on it. How can the auto-update stats in async mode be enabled for one particular table?

You can't enable async auto-update stats for just one table. The setting is at the database level.

I would recommend keeping auto-update stats enabled and also having an UPDATE STATISTICS job that runs on a schedule, perhaps daily. You could test doing a 10% sampling on the large tables rather than a more expensive fullscan.


PostgreSQL Update and Delete are not (logically) replicated while having REPLICA IDENTITY I was trying to sync a table with Logical Replication. This needs to cover Insert, Update and Delete. However the replication only worked for Insert and not the rest.
As documented here

A published table must have a “replica identity” configured in order
to be able to replicate UPDATE and DELETE operations, so that
appropriate rows to update or delete can be identified on the
subscriber side.

Here's the schema of the publishing table
CREATE TABLE mytest.point_a (
gid serial NOT NULL PRIMARY KEY,
the_value numeric(10,2),
name TEXT NOT NULL,
geom geometry(Point,4326) );

CREATE INDEX test_point_a_geom_idx
ON mytest.point_a USING gist (geom);
ALTER TABLE mytest.point_a REPLICA IDENTITY DEFAULT;

The above  table I was trying to replicate has the REPLICA IDENTITY clause and a serial integer column as Primary Key.
The corresponding column in the subscribing table is just a plain bigint without Sequence.
Here's the subscribing table:
CREATE TABLE mytest.point_a (
gid bigint,
--column order is different, this is on purpose
name TEXT,
address TEXT, --not exists in publishing
geom geometry, --more flexible than the one in publishing
the_value(10,2)
);
--this one does not have index

All inserts are replicated but Updates and Deletes are not.
So what's missing here? Did I add REPLICA IDENTITY incorrectly?
Another relevant question: How do we know if a (publishing) table has REPLICA IDENTITY clause? In pgAdmin III and DBeaver, the DDL view of my publishing table in the above do not show any hint about it.

Add a primary key constraint on gid on the replica so that rows can be identified.
The default value for REPLICA IDENTITY of a table is documented with ALTER TABLE:

This option has no effect except when logical replication is in use. DEFAULT (the default for non-system tables) records the old values of the columns of the primary key, if any. USING INDEX records the old values of the columns covered by the named index, which must be unique, not partial, not deferrable, and include only columns marked NOT NULL. FULL records the old values of all columns in the row. NOTHING records no information about the old row.

That is, the default setting logs enough data under the assumption that the replica has the same primary key as the source table.
If you don't have a primary key or unique index on the table, you could use REPLICA IDENTITY FULL so that a row is identified by all its columns, but if you have duplicate rows, you will still get in trouble (deleting one of them on the primary will delete all on the standby). I don't think that is a smart thing to do, and all tables should have a primary key.


UTF8 Trouble while migrating from MSSQL to MySQL with MySQL Workbench I am tasked with migrating part of our database from an on-site MSSQL server to an Amazon RDS MySQL. This will be my first attempt with a database migration, so I am doing a dry run from our MSSQL server to a locally installed instance of MySQL using MySQL Workbench. Local install MySQL Workbench version 6.3. MySQL Server version 5.7

Error generated: Inserting Data: Incorrect string value: '\xA0\xA0 I-D...' for column 'Address1' at row 77 while performing bulk data transfer.

This happens twice in the same table (two different rows). All other tables are successful.

Symptoms:

On the Object Migration -> Manual Editing -> Column Mappings screen, I see a list of migration messages/warnings.

On columns that go from source VARCHAR/NVARCHAR to VARCHAR, I get "Collation SQL_Latin1_General_CP1_CI_AS migrated to utf8_general_ci. Same for Char to Char.
On columns that go from source BIT to TINYINT(1), I get "Source column type BIT was migrated to TINYINT(1)

Settings:

Source Selection  is MSSQL Server, ODBC (Free TDS)

Advanced tab has "Driver sends Unicode data as UTF-8" checked. Required for Free TDS

Target Selection is local MySQL. Connection method is Standard(TCP/IP)

I have read THIS article which seems quite promising, but I am not sure how to implement step 3 as it is a migration and when I get to the "Create Schemas" step, it says it will drop the schema and recreate anew if it already exists.

All the indexes on this database are on small columns, so the 3-bit to 4-bit utf8 will not be an issue for indexes.
As far as "Modify connection, client, and server character sets" to be utf8mb4, I cannot even find the file to modify. I went to MySQL Server 5.7/my-default.ini and changed it to include

[client]
default-character-set = utf8mb4

[mysql]
default-character-set = utf8mb4

[mysqld]
character-set-client-handshake = FALSE
character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci

as requested, but my variables still look like the below picture, so that is obviously not the right file. Plus, will I actually have access to this on the Amazon RDS instance?

Any thoughts?

Hex A0 probably came from a Word document, where it is a "hard space".

It appears that you did not specify that the source was 'latin1' and the destination is 'utf8'.  Otherwise, A0 should have turned into C2A0.  That seems correct.  However, this seems incorrect: "Driver sends Unicode data as UTF-8".  Or perhaps just inconsistent with "Collation SQL_Latin1_General_CP1_CI_AS migrated to utf8_general_ci".  Did you do anything in ODBC to tell it to convert latin1 to utf8?

You have an inconsistency -- some places you mention utf8, some mention utf8mb4.  Outside MySQL, there is only "utf8" (or "UTF-8"), but inside MySQL there is a difference between utf8 and utf8mb4.  If you intend to handle Chinese or Emoji, go with utf8mb4.


How to easily show very long "canonical" name for each column in all reports? Related:

Does the length of a table name affect performance?

For query writing purposes I want to have nice short actual column names.

However, from my actual data source, I have an extremely long "canonical" name for each field.  (This was the column name in the source.)

When showing my query results, I would like to show these long column names.

How can I do this?

I considered a couple ways of approaching this:

I could COMMENT ON each column, which would at least store the long names directly in the table definition, but this wouldn't show the comments with each query result.  (Unless there's some way to do that...?)
I could make a big column alias block of code that I could copy and paste for each query, but that would only work if I'm returning ALL the columns, which isn't likely in most cases.
I could try some fancy magic with the information schema (or system tables) and use PL/pgSQL to run every query so it gives the desired column names.  (I wouldn't know how to approach this, actually, though I have a decent grasp of PL/pgSQL.)
There must be some better way I haven't thought of...can you help me here?

Identifiers are limited to 63 bytes by default, which doesn't qualify as "extremely long". Quote from the manual:

  By default, NAMEDATALEN is 64 so the maximum identifier length is 63
  bytes. If this limit is problematic, it can be raised by changing the
  NAMEDATALEN constant in src/include/pg_config_manual.h

(and recompiling)

Besides the need to recompile that might give pause, increasing NAMEDATALEN by a large margin is not very good idea, because every single identifier stored in the catalog will consume that size, no matter its length.

To me, the only reasonable way to show arbitrary long column names would be to implement it in the client-side presentation layer. Storing the names themselves could be done in the SQL column comments as you suggest, or in your own custom metadata table. The presentation layer could retrieve that data at the start of the session and cache it so that it would be essentially transparent for the rest of the SQL.


Configure Postgresql column capitalization with being able to query using different capitalization I have an existing database that we need to replace from MySQL to PostgreSQL. The problem being that we currently have two applications that use the database (one to create and one to view the data) that use different capitalization.

As the viewing application is in php it will grab the data using the following:

$row["Data"]

But the column "Data" doesn't exist because PostgreSQL converts it to lowercase to there is only the column "data".

A solution to this would be to rewrite the database and use "Data" in the queries and make the queries case-sensitive. The only problem is that this would break the creating application as it uses the lowercase variant.

So the question is, if there is some kind of setting within PostgreSQL that would allow to use capitalization for the column names while having case-insensitive queries?

As documentation claims unquoted identifiers are case insensitive. If you see that the identifiers are case-sensitive, this means that your application (connection library?) adds identifiers quoting. Configure it properly. - akina

  Is there some kind of setting within PostgreSQL that would allow to use capitalization for the column names while having case-insensitive queries?

You will have to modify at least one of your applications. There is no parameter in PostgreSQL that makes it "partly case sensitive". - laurenz-albe


Select with CROSS APPLY runs slow I am trying to optimize the query to run faster.
The query is the following:
SELECT grp_fk_obj_id, grp_name
FROM tbl_groups as g1
         CROSS APPLY (SELECT TOP 1 grp_id as gid
                      FROM tbl_groups as g2
                      WHERE g1.grp_fk_obj_id = g2.grp_fk_obj_id
                      ORDER BY g2.date_from DESC, ISNULL(date_to, '4000-01-01') DESC) as a
WHERE g1.grp_id = gid

grp_id is a primary key. grp_fk_obj_id is a foreign key to another object. There are indexes on both of these columns (I guess it comes as default).
It takes about half a second to complete but I need it to make work faster. I took a look at the execution plan and it shows that "Top N sort" has a cost of more than 90%. Also, I have noticed that if I remove a where clause inside the cross apply then it runs at least 5x faster, but I need that where clause in one way or another.
Do you see any possibilities to improve the performance of this query?
EDIT:
table creation DDL:
create table tbl_groups
(
    grp_id        bigint identity
        constraint PK_tbl_groups
            primary key,
    grp_fk_obj_id bigint      not null
        constraint FK_grp_fk_obj_id
            references tbl_other,
    grp_name      varchar(30) not null,
    date_from     date        not null,
    date_to       date
)
go

create index IDX_grp_fk_obj_id
    on tbl_groups (grp_fk_obj_id)
go

create index IDX_grp_name
    on tbl_groups (grp_name)
go

You need better indexing, and a bit of a rewrite.
Because you’re only using one table, you can use ROW_NUMBER() in a sub-query and then grab the top row from each partition.
SELECT grp_fk_obj_id, grp_name, grp_id
FROM (
    SELECT grp_fk_obj_id, grp_name, grp_id, ROW_NUMBER() OVER (PARTITION BY grp_fk_obj_id ORDER BY date_from DESC, ISNULL(date_to, '4000-01-01') DESC) AS rownum
    FROM tbl_groups
) g
WHERE g.rownum = 1;

But also, fix your ORDER BY clause, because it forces it to do the Sort. Because you’re ordering on an expression, you can’t index it effectively. If you can force date_to to have a value, order by date_from desc, date_to desc, then an index on (grp_fk_obj_id, date_from, date_to) include (grp_name) will really help. It would even make your original query run way quicker.


Insert to SQL Azure through Linked Server very slow I have a table on SQL Asure server with no indexes except clustered.
And I insert into this table small amount of data just about 1000 rows and it takes more than 4 minutes. Although, when I do it with SSIS it takes just a second.
This is the query:

INSERT INTO DatabaseName.dbo.tmpOurClients
(KodClient,Name ,ShortName,INN,Boss,contact,Email,Adres,Tel,City_ID,DateLop)
SELECT KodClient,Name ,ShortName,INN,Boss,contact,Email,Adres,Tel,City_ID,DateLop
FROM dbo.OurClients

That is how I created the Linked Server:

EXEC sp_addlinkedserver
@server='Azure',
@srvproduct='',
@provider='sqlncli',
@datasrc='ServerName.DATABASE.WINDOWS.NET',
@location='',
@provstr='',
@catalog='db_name'

EXEC sp_addlinkedsrvlogin
@rmtsrvname='Azure',
@useself='false',
@rmtuser='login@server_name.database.windows.net',
@rmtpassword='password'

EXEC sp_serveroption 'Azure', 'Collation Compatible', true;

I've already checked this topic Linked server to SQL Azure very slow but none of suggestions helped me.

Version of local SQL-Server: Microsoft SQL Server 2008 R2 (SP2) - 10.50.4000.0 (X64) Jun 28 2012 08:36:30 Copyright (c) Microsoft Corporation Enterprise Edition (64-bit) on Windows NT 6.1  (Build 7601: Service Pack 1) (Hypervisor)

So, what can be the problem?

I insert into this table small amount of data just about 1000 rows and it takes more than 4 minutes

Remote data modifications through a linked server use the sp_cursor model. The effect is similar to issuing 1000 separate single inserts (one for each row). If a round trip takes 250ms, 1000 such trips will take 4 minutes and 10 seconds. Using a bulk loading method such as bcp or SSIS will generally be more efficient (unless the number of rows to be inserted is tiny).

Another alternative for ad-hoc needs is to build a string containing a single INSERT statement with multiple rows in the VALUES clause. The statement is then used with EXECUTE AT, for example:

-- Note local-to-azure table name used, not four-part syntax
DECLARE @sql varchar(max) = 'INSERT dbo.Test VALUES ';

-- Build a list of 1000 numbers to insert
SELECT @sql += '(' + CONVERT(varchar(11), n) + '),'
FROM dbo.Numbers AS N
WHERE n BETWEEN 1 AND 1000;

-- Remove trailing comma
SET @sql = LEFT(@sql, LEN(@sql) - 1);

-- For debugging
PRINT @sql;

-- Execute the finished statement at the remote server;
EXECUTE (@sql) AT AZURE;

The constructed insert statement executes at the Azure database, so a local name is used for the table (note: building a dynamic insert statement using a four-part target name buys you nothing).

Note that EXECUTE ... AT requires RPC and RPC OUT enabled in the linked server options.

The VALUES clause for a plan INSERT statement is limited to 1000 elements. There are ways around that (a VALUES clause in a CTE does not have that restriction). You could also choose to build 1000-row batches or build single-row insert statements, but if the there is that much data on a regular basis, you would probably be better off using one of the bulk loading methods instead.


'cmd' Field Definitions sysprocesses MS SQL Server I am looking at doing some profiling of connections types and volumes across the day.

I am using the sys.sysprocesses to gather the information I require, looking specifically at the 'cmd' field to categorize connections. Does anyone have a good comprehensive list of definitions for the values in this field? I can figure out what a lot of them represent, but I can't find online any definitions for some of them, for example 'XE TIMER'

Many thanks,

As suggested in comment sys.sysprocesses is deprecated and Microsoft does not recommends it to use further. Its just there for backward compatibility

The above view has been replaced by sys.dm_exec_requests. You would see cmd column from sys.sysprocesses is now command column in sys.dm_exec_requests and this column tells you

  Identifies the current type of command that is being processed. Common
  command types include the following:

  • SELECT

  • INSERT

  • UPDATE

  • DELETE

  • BACKUP LOG

  • BACKUP DATABASE

  • DBCC

  • FOR

  The text of the request can be retrieved by using sys.dm_exec_sql_text
  with the corresponding sql_handle for the request. Internal system
  processes set the command based on the type of task they perform.
  Tasks can include the following: • LOCK MONITOR

  • CHECKPOINTLAZY

  • WRITER

  Is not nullable.

Instead I suggest you to focus on column sql_handle which is present in sys.dm_exec_requests. Below code would give you query behind sql_handle

select er.session_id,
er.command,
t.text --gives query behind sql_handle
from sys.dm_exec_requests er
cross apply sys.dm_exec_sql_text(er.sql_handle) t


MySQL Replication question I have setup a master-slave replication between two MySQL servers using mysql  Version 14.12 Distrib 5.0.51a, for debian-linux-gnu.

Recently the replication errored out with this in the logs:

  111128 17:41:12 [ERROR] Slave: Error 'Cannot add or update a child
  row: a foreign key constraint fails (test/bookmap_item,
  CONSTRAINT bookmapitem_bookmap FOREIGN KEY (map_id) REFERENCES
  bookmap (map_id) ON DELETE CASCADE ON UPDATE CASCADE)' on query.
  Default database: 'test'. Query: 'INSERT INTO bookmap_item
  (map_id, ezcontentobject_id, ezcontentnode_id, section, sub_section)
  VALUES(21514, 20205, 27601, 252, 291)', Error_code: 1452

My question is, when using MySQL's replication, is there anyway to keep the slave from enforcing foreign keys?  Or is there something else I could try?

On the slave, you can configure MySQL to skip certain errors by using the slave-skip-errors configuration item.

In the case above, you'd add the following to your my.cnf;

slave-skip-errors = 1452

But I'd be extremely wary about doing this, as the referential integrity is there for a reason, and without it, your slave could not be satisfactorily promoted to take the place of the master in the case of something going wrong with your master hardware...

http://dev.mysql.com/doc/refman/5.5/en/replication-options-slave.html#option_mysqld_slave-skip-errors


Problem when using gtid with MySQL replication I am faced with this problem when using gtid in MySQL replication:

  When @@SESSION.GTID_NEXT is set to a GTID, you must explicitly set it
  to a different value after a COMMIT or ROLLBACK. Please check
  GTID_NEXT variable manual page for detailed explanation. Current
  @@SESSION.GTID_NEXT is 'xxx'"

It makes my slave SQL thread is impossible to run.

I am stuck with it. What is the cause of the problem ?

I've been having this issue on a new install of 5.6.26. I found a bug report on this and it suggests that the fix is available on 5.6.27 or 5.7.9.

https://bugs.mysql.com/bug.php?id=68525

I've upgraded to 5.6.27 and just watching to see if it happens again.

Hope this helps.


Creating a Primary Key on Existing table We have a partition table with 2 billion records and we need to assign a primary key to one of the columns for enabling change tracking feature on it.

After searching we found out that primary key can be assigned by using below query :-

USE AdventureWorks2012;
GO
ALTER TABLE Production.TransactionHistoryArchive
    ADD CONSTRAINT PK_TransactionHistoryArchive_TransactionID
    PRIMARY KEY CLUSTERED (TransactionID);
GO

But wanted to know if it is really possible to assign a PK to such huge table without dropping it and if yes , how much time will it consume in doing so?

Also is there any other option to enable Change tracking on this table as it does not consist a Primary key. Help on this query will be much appreciated.

Note:- This column does not contain duplicate values and is also set to NOT NULL. Also the existing column is set as identity (1,1), how do we convert the identity column to a Primary key?

Yes what you want to do is possible. It is impossible, from what you've given, to know how long it will take or how many additional resources will be required in terms of disk space.  Testing will get you those answers.

Some things to take into consideration...

You are going to need additional space for your new constraint/index.  Ensure the TransactionID is as small and unique as possible to maximize on space usage
Is your TransactionID ever growing or can it be changed for a given record?  You want the former to minimize fragmentation leading to wasted disk and performance issues
Assuming you have other indexes, you will want to script those indexes out and drop them prior to creating the PK/clustering key.  Reapply the indexes once completed; this will add additional time against your maintenance window.
You will essentially be ordering your data since you are also making your Primary Key your clustered index as well; you will need to ensure there is plenty of additional disk space to accommodate the sorting operations


Concatenating time values I have a problem.

In my access database I have two fields with DateTime. I use them for when a meeting starts and ends.

I would like to concatenate them for a report, but 09.00 ends up as 9.0 so my desired "09.00 - 10.15" ends up as "9.0 - 10.15".

Please help.

A pound sign in a mask suppresses zeros. Change your pound signs to 9's. Or just use Format([startdate],'Short Time') + ' - ' + Format([enddate],'Short Time')


How does MySQL return a result set from a stored procedure? I'm trying to get my head around stored procedures for use in a web application. It seems to me that whatever the last statement is in a MySQL stored procedure is seems to be what gets treated as that procedure's result set. Unfortunately though, it seems there are references in the MySQL docs that say a procedure can return multiple result sets. How is this behavior triggered? How can I tell the MySQL server that I explicitly want only one result set returned?

(For instance, I have a query that does a SELECT and a few inserts. I don't want to tell clients about the inserts, but I do want the client to be given the SELECT's result set....)

Each SELECT statement that does not insert into a table or a variable will produce a result set.

If you want your stored procedure to return only one result set, make sure that you only have one SELECT statement. If you have other SELECT statements, make sure that they insert results into a table or variable.

UPDATE
Here are examples of stored procedures.

This stored procedure would return one result set:

DELIMITER ;;
CREATE DEFINER=CURRENT_USER PROCEDURE stored_procedure_name()
BEGIN
    DECLARE local_variable_name INT;

    SELECT column_name FROM table_1 LIMIT 1 INTO local_variable_name;

    SELECT * FROM table_1;
END;;
DELIMITER ;

This stored procedure would return two result sets:

DELIMITER ;;
CREATE DEFINER=CURRENT_USER PROCEDURE stored_procedure_name()
BEGIN
    DECLARE local_variable_name INT;

    SELECT column_name FROM table_1 LIMIT 1 INTO local_variable_name;

    SELECT * FROM table_1;

    SELECT * FROM table_2;
END;;
DELIMITER ;


I need to create SYNONYM with name 'END' Using EnterpriseDB I am not able to create/drop SYNONYM with name 'END'

DROP SYNONYM END;
CREATE SYNONYM END FOR TABLENAME;

I am getting similar issue while creating table with column name REFERENCES:

create tab1
( name character varying(250),
REFERENCES character varying(250) )

I am converting the table from Oracle to Postgres (EntrepriseDB). In Oracle (END, REFERENCES) are used. Hence I need to use the same words here, too. Is there any way to use the reserved key words as synonyms name and column name?

END and REFERENCES are both reserved keywords.

So if you really want to use them as names (of tables, columns, schemata, synonyms, ...  whatever), you have to double-quote them, the same way as in Oracle:

CREATE SYNONYM "END" ... ;
DROP SYNONYM "END" ;

CREATE tab1
( name character varying(250),
  "REFERENCES" character varying(250)
) ;

The problem is that you have to always double-quote them everywhere and match the case. "END" is not the same as "End" nor "end" nor "eNd", etc.

It would be much easier to use non-reserved words for all identifiers.


replicate data from one db to another db server using automated process I have an scenario,
I have two DB server running on two different machines.
I have to build an automated process by which the data will replicate from one DB to another DB server on every insertion happens in first one.
My application using spring- hibernate and mysql DB.
I can go for DB side or spring-hibernate side approach.
Is it possible to do this with rabbitMQ?
I have an option to call automated process on each insertion or as a batch process or using queue.Some of the answers mention in the following link, but it is not clear to me
 1. https://stackoverflow.com/questions/33130260/transforming-data-from-one-sql-server-db-to-another
2. https://stackoverflow.com/questions/3821897/replicating-data-from-one-db-to-another
Any help would be greatly appreciated

Do not try to make it harder than it actually is. Replication mechanism build-in mySQL will be the best for that. There is a ton of tutorials in google, ex.:

https://fromdual.com/how_to_setup_mysql_master-slave_replication

Basically, You need master-slave configuration.


What does `x` stand for in this query? select
convert(varchar(10),  TotalSeconds / 3600) +':'+
convert(varchar(10),   TotalSeconds % 3600 / 60) +':'+
 convert(varchar(10),  TotalSeconds % 60) as Seconds
from
(select
  DateDiff
(second, date,outtime )
 as TotalSeconds
    from attendance.attn_card_register) x -- this one

All tables used in a FROM clause must have a "correlation name" in standard SQL terms a.k.a "alias" in common vernacular SQL

When you use a table directly, the name is obvious. When you use an inline derived table it must be aliased whether it is actually referenced or not

You can use a CTE too. Here "MyCTE" is the name and doesn't required an alias

;WITH MyCTE AS
(
    select
      DateDiff (second, date,outtime)  as TotalSeconds
    from
        attendance.attn_card_register)
)
Select
   convert(varchar(10),  TotalSeconds / 3600) +':'+
   convert(varchar(10),   TotalSeconds % 3600 / 60) +':'+
   convert(varchar(10),  TotalSeconds % 60) as Seconds
from
   MyCTE;

Edit: not all RDBMS require it. SQL Server, MySQL, and Teradata do, Oracle doesn't.


Encoding issue with SQL Server VARCHAR column retrieved in Python We recently had an issue with encoding related to a field that's being stored as a varchar(120) in SQL Server.  In SSMS, the varchar appears as:

"Who Killed JonBen‚t?"

However, when it's brought into python, it appears as:

I've researched this from the Python side, and nothing strange is going on.  My theory is that the varchar in SQL Server is accepting UTF-8 characters which are displaying differently in python than SSMS.  I'm not very familiar with encoding in SQL Server.  Can someone please let me know the following:

Is there a way in SSMS to view the encoding of the varchar?  For instance see \x82 instead of displaying the comma as it is currently from SSMS?
We're using SQL Server 2008.  Is there any way to change the encoding for any UTF-8 characters to ASCII characters without using import /export tools or dumping to a flat file?  I.e. can I make this conversion via a query?
Is there any way to programmatically identify problematic records via a query (problematic being defined as UTF-8 characters that are not supported via ASCII)?

Thank you in advance!

Using sp_help N'table_name'; I found that the Collation of this VARCHAR column is: SQL_Latin1_General_CP1_CI_AS.

SQL Server does not store UTF-8 under any circumstances. You get either UTF-16 Little Endian (LE) via NVARCHAR (including NCHAR and NTEXT, but don't ever use NTEXT) and XML, or some 8-bit encoding, based on a Code Page, via VARCHAR (including CHAR and TEXT, but don't ever use TEXT).

The problem here is that your code is mistranslating that 0x82 character, thinking that it's UTF-8, but it's not. There is no UTF-8 "character" having a value of 0x82, which is why you get the "unknown" / replacement symbol of "�". Please see the following UTF-8 table which shows that there is no character for a single-byte of 0x82:

UTF-8 encoding table

As stated by the O.P., the Collation of the column in question is SQL_Latin1_General_CP1_CI_AS, which means that the 8-bit encoding is using Code Page 1252, which is Windows Latin 1 (ANSI). And checking that chart (scroll down to the bottom chart as it has the character names) value 0x82 (look for "82" in the "Code Point" column) is in fact the Single Low-9 Quotation Mark that you see in SSMS. That character, in UTF-8, is a 3 byte sequence: E2 80 9A.

What all of this means is: your Python code needs to either set the client-encoding for the SQL Server connection to Code Page 1252, or you need to change / convert the encoding of the returned string from Code Page 1252 to UTF-8.

Of course, if this is being displayed on a web page, then you could change the declared charset of the page to be Windows-1252, but that might interfere with other characters on the page if there are UTF-8 characters already there.


PostgreSQL GRANT ALL on mutliple tables using LIKE I have more than 100 tables named public.test_*

How can I easily GRANT ALL access to the user test to all that tables at once?

I tried with:

GRANT ALL ON TABLE public.test_* TO test;

But it is not working ...

GRANT doesn't take wildcards in table identifiers.

You can use ON ALL TABLES IN SCHEMA, but that requires a single schema name.

If you want to do things with wildcard pattern table names you will need to use PL/PgSQL's EXECUTE format(...) in a DO block to loop over the information_schema.tables view. See many related answers here on DBA.se and Stack Overflow for dynamic DDL in PL/PgSQL.

Untested rough example to give you the idea:

DO
$$
DECLARE
    t record;
BEGIN
    FOR t IN
    SELECT table_schema, table_name
    FROM information_schema.tables
    WHERE table_schema = 'public'
    AND table_name LIKE 'test\_%'
    LOOP
        EXECUTE format('GRANT ALL ON TABLE %I.%I TO test;', table_schema, table_name);
    END LOOP;
END;
$$ LANGUAGE plpgsql;

For what %I means see the reference for the format function. If you don't have a format function your PostgreSQL is obsolete and you should probably plan an upgrade; you can use quote_ident and string concatenation in the mean time.


Error in the code shown below - Why is it Why does the following code show an error

SELECT TO_DATE('Today is the Seventeenth of December, Twenty Fifteen','"Today is the" Ddspth "of" Month, Year')
from dual

Twenty Fifteen is a valid year.

SQL> select to_char(sysdate, 'Year') as year from dual;

YEAR
------------------------------------------
Twenty Fifteen

But what you tried works only one way, you can not parse text like that (with suffixes) into a date type.

Datetime Format Element Suffixes

  Notes on date format element suffixes:

  When you add one of these suffixes to a datetime format element, the return value is always in English.
  Datetime suffixes are valid only to format output. You cannot use them to insert a date into the database.


Find Transactions in Time Range, Ignoring Date My client wants to know all the people that logged in to the system during the night hours. I keep all login times. Basically looking for anyone who logged in from 1am-5am for all of this year. Is there a way to find this using SQL?

I keep my time in basic DateTime Format with a 24 hour clock.

I think you will be fine using DATEPART.

Assume the following columns exist, and assume dates and times don't test for daylight saving time.

This will select all the UserIDs from a table where the time is between 1am and 5pm, since January 1st.

SELECT
    UserID,
    LoginDate
FROM
    [dbo].[LoginDates]
WHERE
    [LoginDate] > '20160101'
    AND DATEPART(HOUR,[LoginDate]) >= 1
    AND DATEPART(HOUR,[LoginDate]) < 5

Notes:

The LoginDate > '20160101' will check for all logins after midnight on the 1st of January 2016.
I'm using >= and < instead of BETWEEN, because it's more accurate.


Which version of Microsoft Tape Format is supported by which MS SQL Server When you create a backup with Microsoft Sql Server, the resulting file is in Microsoft Tape Format.
Different versions of SQL Server apparently use different version of this format.

Which version of SQL server is required to restore a given file with a specific format version?

Can I somehow influence the version SQL server uses when creating a backup?

Background:
I am currently trying to restore a database backup created with an unknown version of SQL server, probably 2012. I have SQL Server Express 2008 R2. When executing the command

RESTORE VERIFYONLY FROM DISK='C:\myfile.bak'

I get the error

The media family on device 'C:\myfile.bak' was created
using Microsoft Tape Format version 1.8.
SQL Server supports version 1.0.

I guess this is simply a case of being unable to restore a database with an older version of SQL server, and there is nothing to be done about that besides upgrading. However I found no information about which MS tape format version corresponds to which sql server, and the error message is not very enlightening.

Therefore, in order to understand such error messages, I am interested in some general information about this format, in particular the exact correspondences between tape format version numbers to SQL Server version numbers.

Update:

It seems this is not simply a problem with the wrong version of SQL Server.

Restoring on SQL Server 2012 Express failed with the same error.

When executing a RESTORE HEADERONLY the resulting headers where

BackupName: *** INCOMPLETE ***
Position: 1
Every other column: NULL

I am currently in the process of acquiring another copy of the file.

The problem was indeed a corrupted backup, as Kin correctly deduced. Most likely the corruption was introduced during an ftp transfer.

So, when you get the error "The media family on device 'C:\myfile.bak' was created using Microsoft Tape Format version 1.8. SQL Server supports version 1.0." or the error "Specified cast is not valid. (SqlManagerUI)" when using the SQL Management studio Gui to restore a backup, you most likely have a corrupted backup file.

In my case, zipping the file before ftp transfer solved the problem.

You would think that backup files always contain a checksum, and if the file was later modified that sql server would report this incorrect checksum, but you would be wrong.


How to restore a database if full database is corrupt? I have the following strategy for my SQL Server Database:

Full backup on Sunday at 1 PM
Differential backups daily from Monday to Saturday at 1 PM
Transaction log backups daily every 3 hours

Now my last full backup got corrupt. In this case how can I restore my database?

Can I use Full backup that was taken before the corrupt full backup i.e two weeks before?

Database corruption can exist in the database, FULL backups and DIFF backups. This is because they contain data pages. LOG backups are different and don't contain database corruption.

All of your backups are susceptible to file corruption, but that's different. We're talking database corruption here.

If you need to do a restore due to database corruption, do one last LOG backup so that you can restore without data loss, called the tail.

Now time to start the restores. Start with the FULL backup that is before the last successful DBCC CHECKDB. If you don't have a last successful DBCC CHECKDB, you'll need to run it after each restore to see what's good and what's not.

Then apply a DIFF backup if you have one that is relevant to the FULL you just restored.

Lastly, restore the entire LOG chain since that DIFF (or FULL if you didn't use a DIFF) backup and then apply that final LOG backup (the tail).

You didn't ask this question, but I'm including it anyway. It makes sense to have your FULL backup job occur before the DBCC CHECKDB job so that you know which backups are good if you ever encounter database corruption that requires you to do a restore.


interpreting how much data has been lost by analyzing dbcc output We had a SAN outage and this corrupted several databases with no backups.

We ran this to do a repair:

EXEC sp_resetstatus 'test'

ALTER DATABASE test SET EMERGENCY

DBCC CheckDB ('test')

ALTER DATABASE test SET SINGLE_USER WITH ROLLBACK IMMEDIATE

DBCC CheckDB ('test', REPAIR_ALLOW_DATA_LOSS)

ALTER DATABASE test SET MULTI_USER

It ran for approximately 4 hour and generated a huge output:

http://pastebin.com/qXf0DXd4  (very slightly truncated)

Here's a preview:

Msg 8909, Level 16, State 1, Line 1
Table error: Object ID 0, index ID -1, partition ID 0, alloc unit ID 0 (type Unknown), page ID (1:6769) contains an incorrect page ID in its page header. The PageId in the page header = (0:0).
        The error has been repaired.
Msg 8909, Level 16, State 1, Line 1
Table error: Object ID 0, index ID -1, partition ID 0, alloc unit ID 0 (type Unknown), page ID (1:6770) contains an incorrect page ID in its page header. The PageId in the page header = (0:0).
        The error has been repaired.
Msg 8909, Level 16, State 1, Line 1

1. is this enough info to interpret how much data lose there was?
2. if that's not enough info, can i use the logs to determine how much and WHICH data was lost?

Scanning through the output rather quickly, looks like you lost about 1,045 pages (so 1045 x 8192 bytes). I did this simply by performing a find for "has been deallocated" in the pastebin and seeing how many occurrences of that term were discovered - 1,039. Then I searched for "actual 0:0" and that yielded 6 more pages that weren't found.

No way to guess how many rows that represents. But not sure how that helps you anyway - whether you lost 100 pages or 1,000 pages, or 800 rows or 8,000 rows, what are you going to do with this information?

Whether you can find any more detailed information about the actual data from the transaction log depends on a couple of things, e.g. what recovery model are you in, why were there no backups (because they were on the same drive or because none had ever been taken), and how good are you with a hex editor?


Why the owner of a database can't alter the tables of this database that are owned by other owners? For now I don't understand why this access rights segregation exist.
Is it implemented for some security reasons? Or can it be needed by some use cases I can't imagine right now?

Knowing almost nothing about the reasons behind concrete PostgreSQL design I wonder why prevent a database owner from altering those tables of the database owned that are owned by other owners.

Here's what the official documentation on altering tables says regarding who can alter tables of a database:

  You must own the table to use ALTER TABLE.

Of course I don't argue the design or the documentation. I'm just wondering why it can be needed to limit a database owner that way?

It is a security consideration. One way to get around it is to have all objects created by specific user(s) have default rights.

In my opinion you should limit what user(s) can do DDL in a database for a number of reasons. Primarily for security and controlling who/what user can do DDLs. For a small company / personal setup may not matter, but the second you have multiple people working on a DB can get pretty chaotic if you don't control it.


Database Design for Sports Stats I am designing tables for tracking statistics for multiple sports like basketball and football. So my question now is - would it make more sense to

design a table when both data for basketball and football are stored
separate the stats for basketball and football completely
create a base table and store common information there and then split them into 2 different tables where they start to differ from each other

my main concern with option 1 is that down the road, the table might become too complex and too many optional/nullable columns

number 2 might be overkill? i like reusing things whenever i can.
number 3 - not sure how this would look like...

If you find yourself saying "nullable columns" before you have written code I generally think you need to normalize more(as always, it depends.)

If you want one system to maintain, the last option seems the most relevant, but "splitting them into two tables" is not really where you would want to take it.

if we want to track basic things like Players, Games, and Sport type you would simply add intersection tables between the relevant things you want to store.

Players(playerid, player information) -> PlayerGames(playerid, gameid) <- Games (gameid, game information)
^
|
PlayerSport (playerid, sportid)
|
v
Sports (sportid, sportname)

This means we could count all of Michael Jordan's games by sport(as he played multiple) with a query something like:

select count(*), sportname
from Players p
join PlayerSport ps ON ps.playerid = p.playerid
join PlayerGames pg ON p.playerid = pg.playerid
where p.name='Michael Jordan'
group by sportname


Count(*) in Delete statement How can I use count(*)>0 in a where clause of a delete statement?

My scenario is  if count(*)>0 then delete the table1.  I've tried this code:

delete table1 where c='n' and having count(*)>0;

But it's getting an error.

Your query wants to delete all the rows in table1 where the value of c is n.

Simply modify the DELETE statement so it no longer mentions the COUNT(*) > 0, since that doesn't change the semantics of the query at all.

DELETE
FROM table1
WHERE c = 'n';


query cache hit value is not changing in my database The query cache hit value is not changing in my database only for some queries.If I write

select * from drugs_info limit 1000;

the query cache hit has changed, but if I execute the following queries for two to three times the query cache hit is not changing. The queries as follows:

select * from drugs_info limit 10000;

select * from drugs_info limit 15000;

like this. I don't know what is the problem.

The table, which contains 64500 rows:

CREATE TABLE drugs_info (
    did int(11) NOT NULL AUTO_INCREMENT,
    brand_name varchar(150) DEFAULT NULL,
    generic varchar(500) DEFAULT NULL,
    tradename varchar(150) DEFAULT NULL,
    manfactured varchar(100) DEFAULT NULL,
    unit varchar(300) DEFAULT NULL,
    type varchar(50) DEFAULT NULL,
    quantity varchar(50) DEFAULT NULL,
    price float DEFAULT NULL,
    PRIMARY KEY (did),
    KEY id1 (brand_name,generic)
)
ENGINE=InnoDB AUTO_INCREMENT=64379 DEFAULT CHARSET=latin1;

and the query:

select * from drugs_info;

and my settings are

mysql> show status like 'qcache%';
+-------------------------+---------+
| Variable_name           |  Value  |
+-------------------------+---------+
| Qcache_free_blocks      | 1       |
| Qcache_free_memory      | 8958376 |
| Qcache_hits             | 0       |
| Qcache_inserts          | 0       |
| Qcache_lowmem_prunes    | 0       |
| Qcache_not_cached       | 2       |
| Qcache_queries_in_cache | 0       |
| Qcache_total_blocks     | 1       |
+-------------------------+---------+

Why is this?

You have two things running into each other

Issue #1 : Differing Queries
Issue #2 : InnoDB Storage Engine

Issue #1 : Differing Queries
Look at your three queries
select * from drugs_info limit 1000;
select * from drugs_info limit 10000;
select * from drugs_info limit 15000;

Each query stores a unique result (if the results are stored). If the results are too big (as in the case of the second and third queries), the result sets must be retrieved again.
You could potentially solve this with a bigger query cache, which must be big enough to hold each query's result set. This leads us into ...
Issue #2 : InnoDB Storage Engine
Unfortunately, the InnoDB Storage Engine likes to play traffic cop with the query cache. I had discussed this before in my answer to Is the overhead of frequent query cache invalidation ever worth it? where I learned this pages 213-215 in the book (Second Edition)

If you really want the query cache, you have to meticulously set the query cache size to suit your needs. In almost all cases, it is best to set query_cache_size to 0 when dealing with InnoDB.
First, add this to my.cnf
[mysqld]
query_cache_size=0

Then, run this
mysql> SET GLOBAL query_cache_size = 0;

and you should be all set.
Give it a Try !!!