MS SQL "migration" to older version - problem with Attach On SQL 2019 I try to change compability level to 2008 (to be sure that I will be able to attach on older version, at least I was in hope this will be possible).

I detach this db.
When I try to attach on destination SQL I still get

As so far I found few related topics like this following:
Restore DB to older version
But there are talks about Restoring backup, but I ask about Detach/Attach and compability level.
So Here are my questions:
Question 1: Is there any way to attach newest mdb file on older SQL server ?
if NO
Question 2: What for is feature "Compatibility level" ?
After reading both following ansers I have:
Question 3: Do you think that with tools like dbeaver or devart it will be possible to export/import data between NEW<>OLD SQL versions , or would you propose other tools for this kind of job to do ?

Question 1 - NO
You cannot restore or attach a database from a newer version of SQL to an older version.  The only way to go backwards is to use a bacpac to export the schema and data and then import again.
Question 2 - Compatibility Level
This mainly controls whether certain features are enabled on the database (such as JSON), and it also controls which cardinality engine is used for estimating plans and their various costs.
Question 3 - How to go backwards
I haven't used the tools you referenced so I don't know about them.  But I have used bacpac (included with SQL Server) to move databases around before.  It works better with smaller databases.
You can also use SSIS to migrate the data although that may be a lot of work compared to just using a bacpac.
https://www.sqlshack.com/importing-a-bacpac-file-for-a-sql-database-using-ssms/


Document Design and Document IDs I was wondering about specifying id's in CouchDB. For example:

House
- Rooms
-- Kitchen
-- Living room
-- ...
- Windows
- Doors

There are some documents, like "House", where there is and will only be one document of this type. Should I specify the id "house" for this document for quick access or rather use a generated id?

It's best not to use the name of a "type" as an id - it is better to use generated id's and have a key called "type"  in each document where you have a string value of "Home" etc


Why does my query hang when I put one more id in where statement? I have a query that looks like

select count(*)

FROM (joins)

WHERE customer_id in (select id from customer limit 117) AND ... (more statements)

It involves tables with several hundred million records, table sizes around 60GB including all the indexes. The result for 117 would be a count of 1965, the expected count for 118 would be 1986.

With the limit at 117 the statement returns in a few hundred milliseconds. When I put the limit up to 118 the query just sits there and doesn't return at all.

This is using an RDS Postgres instance, and running statements through IntelliJ. I've also read around that using large WHERE IN statements aren't as performant as joining on the ids through a temporary table instead, so I've edited the statement to use WITH, but the same hang between 117 and 118 exists.

I assume I'm hitting some constraint/limit with something more to do with hardware than the statement, I'm just not sure what with.

Edit: When I change the WHERE clause to be WHERE customer_id between 1 and 1000 the query is still really fast, and ids between 1 and 1000 have no gaps.

I've also since changed my RDS instance from a medium to an XL and I can now limit from 128, but 129 hangs. Nothing looks strange from the monitoring.

You are probably exhausting some resource (adequate resource settings?) and Postgres starts swapping out to disk. And / or, more likely, the query planner switches to a different query plan, based on your cost settings (which may be configured inadequately) and table statistics (which may be outdated).

I can't be more specific, information is missing.

All these possible problems in your setup aside, assuming customer.id is a unique column, this equivalent query with a JOIN replacing in the IN (subquery) should be considerably faster:

SELECT count(*)
FROM   (SELECT id FROM customer LIMIT 128) c  -- arbitrary rows! see below
JOIN   joins j ON j.customer_id = c.id        -- resolve "joins" properly
WHERE  -- ... (more expressions)

Or put the subquery in a CTE. The important point is the join instead of IN. Like:

WITH cids AS (SELECT id FROM customer LIMIT 128)  -- arbitrary rows!
SELECT count(*)
FROM   cids c
JOIN   ...

Adapt the join clause, depending on what's behind (joins) in your original query.

Plus, be aware that LIMIT without ORDER BY selects arbitrary rows. So due to internal effects, the subquery with LIMIT 128 can return completely different IDs from the one with LIMIT 129 (even for the same LIMIT), which can result in a completely different count. Is that what you want?

Related:

SQL sorting by 0
Complex view becomes slow when adding ORDER BY in outer query with small LIMIT


Changes the default database owner in SQL Server 2008 How can I change the default database owner when creating a new database in SQL Server 2008.

ALTER AUTHORIZATION ON DATABASE::MyDB TO sa;

Note, this doesn't add the new owner to db_owner (sa + sysadmin map to dbo user which is different)


How SQL Server agent calculates next run date for a job I am developing a system for scheduling task & I am following Microsoft SQL agent approach to schedule task.
I have replicated sysschedules table for capturing users scheduling occurrences and timings.
Now I need to know how SQL Server get the Next_run_date and time from this table.

I have found solution on internet which are using sysjobhistory table to get the next run date, but in my case I have only sysschedules table. So, what is the formula to calculate the next_run_date and time for any job.

Note: I am not looking for entire code, concept/algorithm will work for me.

In the dbo.sysschedules table you will see all of the data needed to calculate the schedules.

The columns named active_* delimit the periods in which the jobs can run.

The columns named freq_* define the frequency of the job runs.  You will notice a freq_type, which defines a type of frequency, which drives how the freq_interval and other columns are interpreted.

Please read the definitions at:
http://msdn.microsoft.com/en-us/library/ms178644.aspx

EDIT: You can calculate the next_run_datetime by using one of the following:

sp_help_jobschedule @job_name = 'Jobname'
sp_help_jobschedule @job_id= '<job id guid>'

Since there can be several schedules you can also use the schedule_id as such:

sp_help_jobschedule @schedule_id = 1, @job_name = 'Jobname'

This will return a data set in which the last columns give the data, such as:

next_run_date   next_run_time   schedule_uid                             job_count
20141110        190000          301DC5D5-9569-49FA-8BC5-9129109AFA6A     1

And you can script out the msdb procedure sp_help_jobschedule to see the code in the procedure, in case you want to include some of that in your own script.


Scalar UDF causing performance issues in SQL Server I have a scalar UDF doing some string manipulations. When I use it in select clause it causes performance issues. I read that if we do not query tables or views in scalar functions it won't affect your performance much. Below is the part of my UDF code which I am trying to replace based on that.

WHILE @i <= @Maxid
 BEGIN
         SET @i = @i + 1
         SELECT @addrname = name
         FROM [dbo].[temp]
         WHERE id = @i

         IF CHARINDEX (@addrname, 'TEST STRING FOR TEST') > 0
            BEGIN
               SET @addrstart = CHARINDEX (@addrname, 'TEST STRING FOR TEST')
               SET @addrlen = LEN (@addrname)
               SET @i = 0
               BREAK
         END
 END

My dbo.temp table contains some sample char strings which I need to search. Temp table contains two columns id and name. It has following sample data in it:

1, #
2, TEST
3, STRING

I am looking to out to replace use of temp table in above code to see if it helps in improving performance. Please help me with that.

UDF basically creates some type of code from addressline and PO code fields and it needs to run for 5 million rows.

Scalar UDFs always affect performance. What you might have read is that you probably don't want to use them in your WHERE or JOIN ON clauses because that test is happening as part of the filtering stage and thus may force every single row to go through the UDF to determine the output.

This is compared to the less worse option of just using it in the part of the SELECT statement that specifies the columns/data you want to return; in which case you've already filtered everything and so there's presumably less to work on. i.e. Doing a test of the filtered data to return a bit field as to whether there's a match or not.

Apart from this, about making searches more performance, in your case you are searching for a string anywhere within another string, and that's bad. To make things faster you would need to make it so that you are only searching for a string at the beginning of the string (x like '%...') or end of the string (reverse(x) like ('%' + reverse('...') as a persisted computed column and then stored in an index).

Either way you would then go with dynamic SQL; iterate the temp table and build up a string with the SQL statement you want that codes in the specific search terms you are looking for in the WHERE clause; then run with sp_executesql. But it's terrible practice and sometimes a security nightmare.

But it would only go significantly faster if it's on those '%...' searches. Not '%...%'.

The other alternative is to investigate full-text indexing to see if your specific search terms are going to meet that criteria, but it's much more complicated, and you're going to have to crack open a book to learn it.


What is the difference between views and materialized views What is the difference between PostgreSQL's materialized views and MySQL's views?

A VIEW is a stored query. When you select from it, you essentially run the query.

CREATE VIEW foo
AS
  SELECT col1,col2
  FROM bar;

Is essentially

SELECT foo.*
FROM (
  SELECT col1,col2
  FROM bar
) AS foo;

A materialized view is more or less a view that is cached (or materialized) to disk,

CREATE IF NOT EXISTS TABLE foo
AS
  SELECT col1,col2
  FROM bar;

With the added bonus that, at least in PostgresSQL, there is a command REFRESH MATERIALIZED VIEW that you can feed to it the name of the view, and it'll regenerate it from scratch: REFRESH MATERIALIZED VIEW foo;


Columnstore index analysis does not point me to correct columns I am using this query to look deeper onto my columnstore indexes while t-shooting my execution plans (i.e. segment elimination etc.) :
    SELECT
    tables.name AS table_name,
    indexes.name AS index_name,
    columns.name AS column_name,
    partitions.partition_number,
    column_store_segments.segment_id,
    column_store_segments.min_data_id,
    column_store_segments.max_data_id,
    column_store_segments.row_count
FROM sys.column_store_segments
INNER JOIN sys.partitions
ON column_store_segments.hobt_id = partitions.hobt_id
INNER JOIN sys.indexes
ON indexes.index_id = partitions.index_id
AND indexes.object_id = partitions.object_id
INNER JOIN sys.tables
ON tables.object_id = indexes.object_id
INNER JOIN sys.columns
ON tables.object_id = columns.object_id
AND column_store_segments.column_id =
     columns.column_id
WHERE tables.name = 'R***'

ORDER BY tables.name, columns.name,
column_store_segments.segment_id;

(table name redacted).
The result of the analysis is as follows:
Postimg.
My question is simple: How come in the outcome of the analysis does not correspond to the columns name in the indexes? As you can see on the right part of the image, the column names on the result grid are different to what is shown in columnstore index properties.
Environment: SQL 2019 CU10 Standard ; Windows Server 2019.
Thank you in advance for a feedback,
take care S.

The column_id field that is part of the columnstore system catalog view: sys.column_store_segments

column_id int ID of the columnstore column.

Source
Is different from the column_id field that is part of the system catalog view: sys.columns

column_id int ID of the column. Is unique within the object.

Source
If you are ever wondered about what SSMS is running in the background you can always set up an extended event to capture the queries when you select the properties of the index.
The query that SSMS uses that shows the properties of the index and the columns in correct order:
exec sp_executesql N'SELECT
(case ic.key_ordinal when 0 then ic.index_column_id else ic.key_ordinal end) AS [ID],
clmns.name AS [Name],
ic.is_included_column AS [IsIncluded],
ic.is_descending_key AS [Descending],
CAST(COLUMNPROPERTY(ic.object_id, clmns.name, N''IsComputed'') AS bit) AS [IsComputed],
CAST(ISNULL(ic.column_store_order_ordinal,0) AS int) AS [ColumnStoreOrderOrdinal]
FROM
sys.tables AS tbl
INNER JOIN sys.indexes AS i ON (i.index_id > @_msparam_0 and i.is_hypothetical = @_msparam_1) AND (i.object_id=tbl.object_id)
INNER JOIN sys.index_columns AS ic ON (ic.column_id > 0 and (ic.key_ordinal > 0 or ic.partition_ordinal = 0 or ic.is_included_column != 0)) AND (ic.index_id=CAST(i.index_id AS int) AND ic.object_id=i.object_id)
INNER JOIN sys.columns AS clmns ON clmns.object_id = ic.object_id and clmns.column_id = ic.column_id
WHERE
(i.name=@_msparam_2)and((tbl.name=@_msparam_3 and SCHEMA_NAME(tbl.schema_id)=@_msparam_4))
ORDER BY
[ID] ASC',N'@_msparam_0 nvarchar(4000),@_msparam_1 nvarchar(4000),@_msparam_2 nvarchar(4000),@_msparam_3 nvarchar(4000),@_msparam_4 nvarchar(4000)',@_msparam_0=N'0',@_msparam_1=N'0',@_msparam_2=N'IX_Column_bla',@_msparam_3=N'ColumnstoreTest',@_msparam_4=N'dbo'

Here we see that an intermediate system catalog view, sys.index_columns is used.
Knowing that, your final adapted query could be something like this:
SELECT
    tbl.name AS table_name,
    i.name AS index_name,
    clmns.name AS column_name,
    p.partition_number,
    css.segment_id,
    css.min_data_id,
    css.max_data_id,
    css.row_count
FROM sys.column_store_segments AS css
INNER JOIN sys.partitions AS p
ON css.hobt_id = p.hobt_id
INNER JOIN
sys.tables as tbl ON p.object_id = tbl.object_id
INNER JOIN sys.indexes AS i ON (i.object_id=tbl.object_id)
INNER JOIN sys.index_columns AS ic ON  ic.object_id=i.object_id
INNER JOIN sys.columns AS clmns ON clmns.object_id = ic.object_id and clmns.column_id = ic.column_id
WHERE tbl.name = 'TableName'
AND i.name= 'IndexName'
ORDER BY tbl.name, clmns.name,
css.segment_id;


What are reasons **NOT** to use the MEMORY storage engine in MySQL? I recently discovered that MySQL has a "memory" engine that I wasn't aware of (most of my database work is for hobby projects so I learn what I need as I go).  It seems like this option should give me drastically improved performance, so I'm wondering if there are any drawbacks that go with it.  The two that I know of are:

I need to have enough RAM to hold the table(s) in question.
The tables are lost if the machine shuts down.

I believe #1 shouldn't be an issue since I'm using AWS EC2 and can move to an instance type with more memory if needed.  I believe I can mitigate #2 by dumping back to disk as needed.

What other issues are there?  Can the memory engine ever give worse performance than either MyISAM or InnoDB?  I think I read something that indices are different with this engine; is this something I need to worry about?

Looking at the feature availability list at http://dev.mysql.com/doc/refman/5.1/en/memory-storage-engine.html two possible problems jump out:

No transaction or FK support, meaning you will have to manage transactional integrity and referential integrity in your own code were needed (which could end up being a lot less efficient than letting the DB do this for you, though that very much depends on your app's expected behaviour patterns).
Table level locking only: this could be a significant barrier to scalability if your app needs multiple concurrent writers to the same set of tables or in cases where your read operations use locks to ensure consistent data is read - in such cases a disk based table that supports much finer lock granularity will perform far better if enough of its content is currently cached in RAM.

Other than that, assuming you have enough RAM, a memory based table should be faster than a disk based one. Obviously you need to factor in taking snapshots to disk to address the issue of what happens when the server instance is reset, which is likely to completely negate the performance benefit overall if the data needs capturing often (if you can live with losing a day of data in such an instance you could just take a backup once per day, but in most cases that would not be acceptable).

An alternative might be to:

Use disk based tables, but ensure that you have more than enough RAM to hold them all in RAM at any given time (and "enough RAM" might be more than you think as you need to account for any other processes on the machine, OS IO buffers/cache and so forth)
Scan the entire contents (all data and index pages) of the table on each startup to preload the content into memory with SELECT * FROM <table> ORDER BY <pkey fields> for each table followed by SELECT <indexed fields> FROM <table> ORDER BY <index fields> for each index

This way all your data is in RAM, you only have to worry about I/O performance for write operations. If your app's common working set is much smaller than the whole DB (which it usually the case - in most applications most users will only be looking at the most recent data most if the time) you might be better of being more selective about how much you scan to preload into memory, allowing the rest to be loaded from disk on demand.


Is it possible to have extra tables in a Slave with MySQL Replication As my title mention I have a Master and a Slave database.

Master if for operations data and my slave mainly for reporting stuff.

The issue is that I need to create extra tables on reporting that can't be on the master, but the way my replication is set (the simplest one mentioned by the official doc)  at the moment, this breaks the replication system.

How could I add tables on the Slave without Master caring about it ? Is it even possible ?

I think I got a rough idea how you setup replication. I think you have taken mysqldump --all-databases ... something along those lines.

Now if you want extra tables inside one of those database that you exported by mysqldump and those that belong to the master replication will break as you mentioned aswell.

Now to create more tables you need to create a new database with some name and then create tables in there. This will not break the replication.

I do not know if this helps....

Masood


MYSQL - Run a query based on the results of a query Trying to do some cleanup in the database, per manager request. However, I can't seem to get Mysql to run the inner query as written.

select @tbl:=column_name as'Table',
       ( select max(length(@tbl))
         from project) as 'used_length',
       character_maximum_length as 'defined length'
from INFORMATION_SCHEMA.COLUMNS
where table_name = 'project'
  and column_name = 'announcement_notes'
group by column_name,
         character_maximum_length
order by 1

Unfortunately, it's not running the max(length) query - it is returning the length of the label - in this case announcement_notes is 18 characters long.

Table              used_length  defined_length
-----------------  ------------ ---------------
announcement_notes 18           2000

[if that wasn't clear - the return from the query is announcement_notes, 18, 2000).

however, checking the actual length
select max(length(announcement_notes)) from project
returns 404 characters.

Any help would be greatly appreciated.

Prepared Statement is the answer for this.

set @stmt1= concat( 'select ', @var, ' from ', @vardb, ' where ', @varcond);
prepare stmt from @stmt1;
execute stmt;

For getting @tbl used properly,

   concat( 'select max(length(', @tbl, ')' )

will get it. But to select required columns from information_schema, you need to run a query first followed by coining this prepared statement and then execute the same.


sql server express lan I want to know if sql server (express) need to have always admin right on a computer.

I ask my company to have a version on my pc, but I need a tech to install it because I dont have admin right.

I'm afraid that I will not be able to use it after the installation.

I will need of course to manage it

and connect local

and connect remote lan

did you think my right will cause problem

(I know taht I can talk to a mysql db on my computer by lan)

I found this http://www.symantec.com/connect/articles/install-and-configure-sql-server-2008-express and its said that I will need to enable TCP/IP, is it that I can do it? did I need it on lan?

The account which runs the SQL Server instance doesn't need admin rights on the computer and neither does the account which will manage the SQL Server instance.  If you plan on creating databases within the instance you'll need to make sure that you have sysadmin rights within the instance but this isn't related to the OS rights at all.  The person doing the install will need to make sure that they give you the rights within the database instance that you need before they leave.

The person doing the install that has admin rights will need to enable TCP/IP on the instance as that does require admin rights to do.


WITH ROLLUP WHERE x IS NULL I tried to find this question somewhere else on here, and found a couple that were similar to it -- but all the answers seemed pretty convoluted, and I think it was surprising to me because I just imagined that SQL would have a way easier way to do this.

Essentially, I'm using a GROUP BY to group two values on top of each other. The coding is simple and works well, where I get these results using WITH ROLLUP at the end:

Type    Person  Count
Type A  Person 1    10
Type A  Person 2    91
Type A  Person 3    13
Type A  Person 4    10
Type A  Person 5    2
Type A  NULL        126
Type B  Person 6    16
Type B  Person 7    63
Type B  Person 8    15
Type B  Person 9    22
Type B  Person 10   16
Type B  Person 11   3
Type B  Person 12   20
Type B  Person 13   44
Type B  NULL        198
NULL    NULL        360

Is there an easy way to substitute something saying if "NULL" is in the "Person" column, then make NULL AS "Subtotal" and if "NULL" is in the "Type" column, NULL AS "Grand Total?"

And if that's weirdly complicated, I'd be totally fine with just calling all of them "Total."

Thanks!

Let's say your query looks like this:

SELECT type,person,COUNT(*) `Count`
FROM mytable GROUP BY type,person WITH ROLLUP;

The NULL values coming out can be substituted with something like this...

SELECT
    IFNULL(type,'All Types') Type,
    IFNULL(person,'All Persons') Person
    COUNT(*) `Count`
FROM mytable GROUP BY type,person WITH ROLLUP;

Using IFNULL() will forcibly put those Text String in place of the NULL for each level of the GROUP BY. As an example, here are past posts where I actively substitute NULL in my answer involving WITH ROLLUP:

Sep 13, 2011 : Determining max database and table size supported and present size
Dec 01, 2011 : MySQL Workbench Database Sizes
Jan 11, 2013 : Translating backup size to database size

From these posts I have this example,

SELECT Statistic,DataSize "Data Size",IndexSize "Index Size",TableSize "Table Size"
FROM (SELECT IF(ISNULL(table_schema)=1,10,0) schema_score,
IF(ISNULL(engine)=1,10,0) engine_score, IF(ISNULL(table_schema)=1,
'ZZZZZZZZZZZZZZZZ',table_schema) schemaname,
IF(ISNULL(B.table_schema)+ISNULL(B.engine)=2,
"Storage for All Databases",IF(ISNULL(B.table_schema)+ISNULL(B.engine)=1,
CONCAT("Storage for ",B.table_schema),CONCAT(B.engine," Tables for ",
B.table_schema))) Statistic,
CONCAT(LPAD(REPLACE(FORMAT(B.DSize/POWER(1024,pw),3),',',''),17,' '),' ',
SUBSTR(' KMGTP',pw+1,1),'B') DataSize,
CONCAT(LPAD(REPLACE(FORMAT(B.ISize/POWER(1024,pw),3),',',''),17,' '),' ',
SUBSTR(' KMGTP',pw+1,1),'B') IndexSize,
CONCAT(LPAD(REPLACE(FORMAT(B.TSize/POWER(1024,pw),3),',',''),17,' '),' ',
SUBSTR(' KMGTP',pw+1,1),'B') TableSize FROM (SELECT table_schema,engine,
SUM(data_length) DSize,SUM(index_length) ISize,SUM(data_length+index_length) TSize
FROM information_schema.tables WHERE table_schema NOT IN
('mysql','information_schema','performance_schema') AND engine IS NOT NULL
GROUP BY table_schema,engine WITH ROLLUP) B,(SELECT 3 pw) A) AA
ORDER BY schemaname,schema_score,engine_score;

Here is the output from one of my client's DB Servers:

+------------------------------------+----------------------+----------------------+----------------------+
| Statistic                          | Data Size            | Index Size           | Table Size           |
+------------------------------------+----------------------+----------------------+----------------------+
| MyISAM Tables for dev_oxygen       |             0.000 GB |             0.000 GB |             0.000 GB |
| InnoDB Tables for dev_oxygen       |             1.480 GB |             0.506 GB |             1.986 GB |
| Storage for dev_oxygen             |             1.480 GB |             0.506 GB |             1.986 GB |
| MyISAM Tables for dumbpdb          |             0.001 GB |             0.001 GB |             0.002 GB |
| InnoDB Tables for dumbpdb          |             1.552 GB |             0.583 GB |             2.135 GB |
| Storage for dumbpdb                |             1.553 GB |             0.584 GB |             2.137 GB |
| InnoDB Tables for duplicate_oxygen |             0.166 GB |             0.073 GB |             0.239 GB |
| Storage for duplicate_oxygen       |             0.166 GB |             0.073 GB |             0.239 GB |
| InnoDB Tables for facebook_app     |             0.000 GB |             0.000 GB |             0.000 GB |
| Storage for facebook_app           |             0.000 GB |             0.000 GB |             0.000 GB |
| InnoDB Tables for generationo      |             0.016 GB |             0.002 GB |             0.018 GB |
| Storage for generationo            |             0.016 GB |             0.002 GB |             0.018 GB |
| InnoDB Tables for mantisbt         |             0.000 GB |             0.000 GB |             0.001 GB |
| Storage for mantisbt               |             0.000 GB |             0.000 GB |             0.001 GB |
| MyISAM Tables for oxygen           |             0.003 GB |             0.002 GB |             0.005 GB |
| InnoDB Tables for oxygen           |             1.771 GB |             0.739 GB |             2.510 GB |
| Storage for oxygen                 |             1.774 GB |             0.741 GB |             2.515 GB |
| Storage for All Databases          |             4.990 GB |             1.907 GB |             6.897 GB |
+------------------------------------+----------------------+----------------------+----------------------+
18 rows in set (12.67 sec)

Notice I check the ISNULL() of each column and compare IF() function calls to produce a single column output. In your particular case, you are going to need one of these two queries

QUERY #1

SELECT
    IF(ISNULL(type)=1,'Grand Total',
        IF(ISNULL(person)=1,CONCAT(QUOTE(type),' Subtotal'),
        CONCAT(QUOTE(type),' Subtotal for ',QUOTE(person)))
    ) Statistic,`Count`
FROM
(
    SELECT type,person,COUNT(*) `Count`
    FROM mytable GROUP BY type,person WITH ROLLUP
) A;

QUERY #2

SELECT
    IF(type='AllDone','Grand Total',
        IF(person='AllDone',CONCAT(QUOTE(type),' Subtotal'),
        CONCAT(QUOTE(type),' Subtotal for ',QUOTE(person)))
    ) Statistic,`Count`
FROM
(
    SELECT
        IFNULL(type,'AllDone') type,
        IFNULL(person,'AllDone') person,
        COUNT(*) `Count`
    FROM mytable GROUP BY type,person WITH ROLLUP
) A;

Here is some sample data:

DROP DATABASE IF EXISTS ryanodonnell;
CREATE DATABASE ryanodonnell;
USE ryanodonnell
CREATE TABLE mytable
(
    id int not null auto_increment,
    type varchar(20) not null,
    person varchar(20) not null,
    key type_person_index (type,person),
    primary key (id)
);
INSERT INTO mytable (type,person) VALUES
('Type A','Person 1'),('Type A','Person 2'),('Type A','Person 3'),
('Type A','Person 5'),('Type A','Person 2'),('Type A','Person 3'),
('Type A','Person 2'),('Type A','Person 3'),('Type A','Person 1'),
('Type A','Person 5'),('Type A','Person 1'),('Type A','Person 5'),
('Type A','Person 3'),('Type A','Person 4'),('Type A','Person 5'),
('Type B','Person 1'),('Type B','Person 2'),('Type B','Person 3'),
('Type B','Person 2'),('Type B','Person 3'),('Type B','Person 4'),
('Type B','Person 3'),('Type B','Person 4'),('Type B','Person 5'),
('Type B','Person 4'),('Type B','Person 5'),('Type B','Person 4'),
('Type B','Person 3'),('Type B','Person 1'),('Type B','Person 3');

Here is that sample data loaded:

mysql> DROP DATABASE IF EXISTS ryanodonnell;
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE DATABASE ryanodonnell;
Query OK, 1 row affected (0.00 sec)

mysql> USE ryanodonnell
Database changed
mysql> CREATE TABLE mytable
    -> (
    ->     id int not null auto_increment,
    ->     type varchar(20) not null,
    ->     person varchar(20) not null,
    ->     key type_person_index (type,person),
    ->     primary key (id)
    -> );
Query OK, 0 rows affected (0.08 sec)

mysql> INSERT INTO mytable (type,person) VALUES
    -> ('Type A','Person 1'),('Type A','Person 2'),('Type A','Person 3'),
    -> ('Type A','Person 5'),('Type A','Person 2'),('Type A','Person 3'),
    -> ('Type A','Person 2'),('Type A','Person 3'),('Type A','Person 1'),
    -> ('Type A','Person 5'),('Type A','Person 1'),('Type A','Person 5'),
    -> ('Type A','Person 3'),('Type A','Person 4'),('Type A','Person 5'),
    -> ('Type B','Person 1'),('Type B','Person 2'),('Type B','Person 3'),
    -> ('Type B','Person 2'),('Type B','Person 3'),('Type B','Person 4'),
    -> ('Type B','Person 3'),('Type B','Person 4'),('Type B','Person 5'),
    -> ('Type B','Person 4'),('Type B','Person 5'),('Type B','Person 4'),
    -> ('Type B','Person 3'),('Type B','Person 1'),('Type B','Person 3');
Query OK, 30 rows affected (0.00 sec)
Records: 30  Duplicates: 0  Warnings: 0

mysql>

Now, here is the output of Query #1:

mysql> SELECT
    ->     IF(ISNULL(type)=1,'Grand Total',
    ->         IF(ISNULL(person)=1,CONCAT(QUOTE(type),' Subtotal'),
    ->         CONCAT(QUOTE(type),' Subtotal for ',QUOTE(person)))
    ->     ) Statistic,`Count`
    -> FROM
    -> (
    ->     SELECT type,person,COUNT(*) `Count`
    ->     FROM mytable GROUP BY type,person WITH ROLLUP
    -> ) A;
+----------------------------------+-------+
| Statistic                        | Count |
+----------------------------------+-------+
| 'Type A' Subtotal for 'Person 1' |     3 |
| 'Type A' Subtotal for 'Person 2' |     3 |
| 'Type A' Subtotal for 'Person 3' |     4 |
| 'Type A' Subtotal for 'Person 4' |     1 |
| 'Type A' Subtotal for 'Person 5' |     4 |
| 'Type A' Subtotal                |    15 |
| 'Type B' Subtotal for 'Person 1' |     2 |
| 'Type B' Subtotal for 'Person 2' |     2 |
| 'Type B' Subtotal for 'Person 3' |     5 |
| 'Type B' Subtotal for 'Person 4' |     4 |
| 'Type B' Subtotal for 'Person 5' |     2 |
| 'Type B' Subtotal                |    15 |
| Grand Total                      |    30 |
+----------------------------------+-------+
13 rows in set (0.27 sec)

mysql>

Now, here is the output of Query #2:

mysql> SELECT
    ->     IF(type='AllDone','Grand Total',
    ->         IF(person='AllDone',CONCAT(QUOTE(type),' Subtotal'),
    ->         CONCAT(QUOTE(type),' Subtotal for ',QUOTE(person)))
    ->     ) Statistic,`Count`
    -> FROM
    -> (
    ->     SELECT
    ->         IFNULL(type,'AllDone') type,
    ->         IFNULL(person,'AllDone') person,
    ->         COUNT(*) `Count`
    ->     FROM mytable GROUP BY type,person WITH ROLLUP
    -> ) A;
+----------------------------------+-------+
| Statistic                        | Count |
+----------------------------------+-------+
| 'Type A' Subtotal for 'Person 1' |     3 |
| 'Type A' Subtotal for 'Person 2' |     3 |
| 'Type A' Subtotal for 'Person 3' |     4 |
| 'Type A' Subtotal for 'Person 4' |     1 |
| 'Type A' Subtotal for 'Person 5' |     4 |
| 'Type A' Subtotal                |    15 |
| 'Type B' Subtotal for 'Person 1' |     2 |
| 'Type B' Subtotal for 'Person 2' |     2 |
| 'Type B' Subtotal for 'Person 3' |     5 |
| 'Type B' Subtotal for 'Person 4' |     4 |
| 'Type B' Subtotal for 'Person 5' |     2 |
| 'Type B' Subtotal                |    15 |
| Grand Total                      |    30 |
+----------------------------------+-------+
13 rows in set, 2 warnings (0.00 sec)

mysql>

Give it a Try !!!


Schema blocking in READ_COMMITTED_SNAPSHOT I am trying to troubleshoot locking behavior and the READ_COMMITTED_SNAPSHOT isolation level while attempting to resolve concurrency issues.

Background: Assume an online ordering system (ecommerce).  Product price changes are calculated minimum monthly, and this results in around 600,000 records that must be changed in the database.  When posting the price change updates to the database (SQL Server 2008R2 Web Edition) the site becomes unusable due to the significant levels of locking in the primary ProductDetails table when using READ_COMMITTED transaction isolation level.

To resolve this, READ_COMMITTED_SNAPSHOT is enabled, however other transactions are still being blocked during the price updates.  Investigation of sys.dm_tran_locks shows the blocked session is caused by a waiting Sch-S lock.  As I understand it, Sch-S locks (schema stability) are taken while a schema-dependent query is being compiled and executed (aren't they all schema-dependent?).  But sys.dm_tran_locks also shows a series of Sch-M locks (schema modification), which are not compatible with any outside operations per BOL.  I assume this is caused by the fact that the 3rd party tool used to replicate data changes drops foreign keys during the update process and recreates them after the update is completed.  And so, in spite of READ_COMMITTED_SNAPSHOT, other queries are still blocked, not by the update, but by the Sch-M locks cause by the changes to foreign key relationships.

This theory was confirmed by eliminating the setting that dropped/recreated the foreign keys.  Now the update process no longer takes Sch-M locks (sys.dm_tran_locks only shows X, IX, S locks), and other transactions are not blocked from using the version store to satisfy their queries.

However, when executed using this process, the price changes take approx 1 hour to process (vs. 1-2 minutes) and sys.dm_tran_locks shows the transaction taking almost 90,000 different locks, compared to around 100-150 when foreign keys were being dropped/recreated.

Can anyone explain this behavior and offer suggestions on how concurrency could be maintained without exponentially increasing the maintenance time for price changes?

The following behavior may be caused by missing indexes on referring side of your FKs: "the price changes take approx 1 hour to process (vs. 1-2 minutes) and sys.dm_tran_locks shows the transaction taking almost 90,000 different locks, compared to around 100-150 when foreign keys were being dropped/recreated"

When a row is deleted or its PK/Unique is mutating, the database engine need to make sure there are no orphans. When there is no proper index to support it, it scans the whole thing.


Selection of entries that have only two words with SQL wildcards I have a table place that has a column called title. I want to select only those data which have only two words.
For example, if the table consists of a title named "Buckingham Palace", I would have wanted it in my resultant table rather than having titles such as "Tower Of London" or "Berlin". How can this be done in MySQL?
Note: Any title that has only one space in between will be considered. For example, "St. Marc" should also be selected.
I have tried
select title from place where title not like '% % %'

I still get results in the table like St. Bishop's Palace (the entries which usually have words like St. or Mt.).
When I try that query with just a space extra added, I get unnecessary entries like Tower of God and Palace of Parliament.
select title from place where title not like ' % % % '.

Assuming you consider the space character as the delimiter to separate words (as per your examples) this query should do what you're looking for:
SELECT title
FROM Place
WHERE CHAR_LENGTH(SUBSTRING_INDEX(TRIM(title), ' ', 2)) = 0 -- Titles that don't have more than 1 space
    AND CHAR_LENGTH(SUBSTRING_INDEX(TRIM(title), ' ', 1)) > 0 -- And Titles that have at least 1 space (effectively this conjoined predicate results in Title's with exactly 1 space, that isn't at the start or end, aka two words)

There's probably a simpler solution, but this will remove trailing and leading spaces from the title column then filter out anything that has more than 1 space (the first predicate) and filter out anything doesn't have at least 1 space (the second predicate). Also this syntax is only for MySQL, as you requested, but you also tagged your question as PostgreSQL (so you should either remove that tag or update the question with the information you need on PostgreSQL).


How to tune a SQL query? I have a question about SQL query tuning.

How can rewrite that SQL query for better performance?

Query:

select *
from (
    select (
        select max(rn)
        from (
            select row_number() over (order by islemno) rn, k.id
            from cari k
            where k.yilid = :yilid and k.id=k.baslik
        )
    ) aktifSira
        , row_number() over (order by islemno) sira
        , ks.id
    from cari ks
    where yilid = :yilid and id=baslik
) where sira = (aktifSira - 1)
    or sira = (aktifSira + 1)
    or sira = aktifSira
order by sira;

That's cost of query:

---------------------------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name     | Rows  | Bytes | Cost (%CPU)| Time     |    TQ  |IN-OUT| PQ Distrib |
---------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |          |     1 |    92 |  3368   (1)| 00:00:01 |        |      |         |
|   1 |  SORT AGGREGATE             |          |     1 |    13 |            |          |        |      |         |
|   2 |   PX COORDINATOR            |          |       |       |            |          |        |      |         |
|   3 |    PX SEND QC (RANDOM)      | :TQ10001 |     1 |    13 |            |          |  Q1,01 | P->S | QC (RAND)|
|   4 |     SORT AGGREGATE          |          |     1 |    13 |            |          |  Q1,01 | PCWP |         |
|   5 |      VIEW                   |          |     1 |    13 |  1684   (1)| 00:00:01 |  Q1,01 | PCWP |         |
|   6 |       WINDOW SORT           |          |     1 |   144 |  1684   (1)| 00:00:01 |  Q1,01 | PCWP |         |
|   7 |        PX RECEIVE           |          |     1 |   144 |  1683   (1)| 00:00:01 |  Q1,01 | PCWP |         |
|   8 |         PX SEND RANGE       | :TQ10000 |     1 |   144 |  1683   (1)| 00:00:01 |  Q1,00 | P->P | RANGE   |
|   9 |          PX BLOCK ITERATOR  |          |     1 |   144 |  1683   (1)| 00:00:01 |  Q1,00 | PCWC |         |
|* 10 |           TABLE ACCESS FULL |     CARI |     1 |   144 |  1683   (1)| 00:00:01 |  Q1,00 | PCWP |         |
|  11 |  PX COORDINATOR             |          |       |       |            |          |        |      |         |
|  12 |   PX SEND QC (ORDER)        | :TQ20002 |     1 |    92 |  3368   (1)| 00:00:01 |  Q2,02 | P->S | QC (ORDER)|
|  13 |    SORT ORDER BY            |          |     1 |    92 |  3368   (1)| 00:00:01 |  Q2,02 | PCWP |         |
|  14 |     PX RECEIVE              |          |     1 |    92 |  3367   (1)| 00:00:01 |  Q2,02 | PCWP |         |
|  15 |      PX SEND RANGE          | :TQ20001 |     1 |    92 |  3367   (1)| 00:00:01 |  Q2,01 | P->P | RANGE   |
|* 16 |       VIEW                  |          |     1 |    92 |  3367   (1)| 00:00:01 |  Q2,01 | PCWP |         |
|  17 |        WINDOW SORT          |          |     1 |   144 |  3367   (1)| 00:00:01 |  Q2,01 | PCWP |         |
|  18 |         PX RECEIVE          |          |     1 |   144 |  1683   (1)| 00:00:01 |  Q2,01 | PCWP |         |
|  19 |          PX SEND RANGE      | :TQ20000 |     1 |   144 |  1683   (1)| 00:00:01 |  Q2,00 | P->P | RANGE   |
|  20 |           PX BLOCK ITERATOR |          |     1 |   144 |  1683   (1)| 00:00:01 |  Q2,00 | PCWC |         |
|* 21 |            TABLE ACCESS FULL|     CARI |     1 |   144 |  1683   (1)| 00:00:01 |  Q2,00 | PCWP |         |
---------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

  10 - filter("K"."ID"="K"."BASLIK" AND "K"."YILID"=SYS_OP_C2C(:YILID))
  16 - filter("SIRA"="AKTIFSIRA"-1 OR "SIRA"="AKTIFSIRA"+1 OR "SIRA"="AKTIFSIRA")
  21 - filter("ID"="BASLIK" AND "YILID"=SYS_OP_C2C(:YILID))

What makes you think you need to rewrite it? SQL Tuning is more than just trying to rewrite your query. If you do not understand what happens behind the scenes, then how will you rewrite your query?

The estimated cardinality in step 10 and 21 is 1. Still the database decides to scan the whole CARI table. Twice.

Is the above estimation correct? Would those steps really return just 1 or a few rows? If you run this query (with actual values), will this return 1 or hundreds, thousands, millions?

select count(*) from cari where id = baslik and yilid = :yilid;

Are the table statistics up-to-date?

select count(*), count(distinct yilid) from cari;
select blocks from user_segments where segment_name = 'CARI';

Compare the above values to:

select num_rows, blocks from user_tables where table_name = 'CARI';
select num_distinct, histogram from user_tab_columns where table_name = 'CARI' and column_name = 'YILID';

If there is a big difference, gather statistics.

Is there any correlation between the columns BASLIK and YILID? If there is, did you gather extended statistics on them, so the database can estimate cardinality more accurately?

If both the statistics and the above estimation are good enough, then why isn't there an index on the YILID (and BASLIK) column that the database could use to access the required rows effectively?

If both the statistics and the above estimation are good enough, then why does the database decide to use parallel query at all? Did you force it or set it at table level?

In the Predicate Information section:

10 - filter("K"."ID"="K"."BASLIK" AND "K"."YILID"=SYS_OP_C2C(:YILID))
21 - filter("ID"="BASLIK" AND "YILID"=SYS_OP_C2C(:YILID))

This is really bad: "YILID"=SYS_OP_C2C(:YILID). You did not bind the variable properly. SYS_OP_C2C means char to nchar conversion, so the column type and variable type is different. The database will implicitly convert one type to another, this time you got lucky, but the other way around it would make impossible to use an index, unless you have a function-based index especially for the affected columns - do not do that, bind properly. If the column YILID is an nvarchar2 type column, then bind :yilid as an nvarchar2 type variable.

If the above still does not help, did you actually measure the steps in the execution plan? Did you identify the steps that take long? Run the below and report back with the output:

alter session set statistics_level = all;
-- run your query here in the same session --
select * from table(dbms_xplan.display_cursor(format=>'allstats last'));


Any benefit to having multiple filtered indexes? I have a SQL Server database and stumbled across a table with a uniqueidentifier primary key named ActivityId and columns StateCode (int) and ActivityTypeCode (int) along with many others. The tables has 13M rows and there are 94 unique values for ActivityTypeCode with row counts ranging from 1 to 4M. OK, here’s the weird part: I have 97 different filtered indexes on (StateCode, ActivityId) where the only difference is the where clause on (ActivityTypeCode=(_some_value_)).

To my eyes, all 97 indexes could be dropped and replaced with one index on just StateCode (since ActivityID is the primary key and is already included as a hidden column). My question is: Is there any reason why I would want to keep multiple filtered indexes? Could they reduce deadlocks somehow? I’ve done some testing and I don’t see any reason to have so many indexes. Thoughts?

Ken

[Edit]
Based on the excellent answer below, I've done some testing and it does not appear that the filtered indexes prevent blocking on even simple queries.  My indexes are of the form:

CREATE INDEX IX_StateCode_ActivityID_ActivityTypeCode_Filtered_0 ON dbo.Activities(StateCode,ActivityId) WHERE ActivityTypeCode = 0;

If I go into one database connection and issue the following:

Begin transaction;
Update Activities set StateCode=888 where ActivityTypeCode=1

Then go into a second connection and issue this:

Update Activities set StateCode=999 where ActivityTypeCode=3

The second connection hangs.  It seems these different indexes do not prevent blocking so I don't see any reason to keep them.  Given that there are indexes for ActivityTypeCode values that do not exist, I suspect they were all generated by some sort of development tool.  FYI:  This is from a third party vendor.

Thanks everyone for your help.

[Edit #2] Upon detail examiniation, not all indexes are the same.  Below are the combinations.  Keep in mind ActivityID is the primary key.

StateCode, ActivityID
StateCode, CreatedOn, ActivityID
StateCode, CreatedOn, StatusCode, ActivityID
StateCode, StatusCode, CreatedOn, Activity ID

I don't think any of this will prevent me from replacing them all with a single index - it will just be a bit larger than originally anticipated.

To my eyes, all 97 indexes could be dropped and replaced with one
  index on just StateCode (since ActivityID is the primary key and is
  already included as a hidden column). My question is: Is there any
  reason why I would want to keep multiple filtered indexes?

Like Brent said in the comments, this will be hard to give a good anser without many more details.

  I think it's tough to get a good answer here without knowing a lot
  more specifics, like the queries involved that are using the indexes.
  That's probably where I'd start with the research - start looking at
  the index usage statistics on each filtered index, see if there are
  any differences between them, see if maybe there are some
  ActivityTypeCode values that aren't included in the filters, etc

And as Jonathan Fite said, if the queries are using the filtered indexes, they could be beneficial.

  If your queries are actually using the filtered indexes and then
  especially if the index is a covering one for the query, then they
  could be beneficial. But I'm guessing (given the number of them) that
  they were added because someone had read about them and thought it
  would be a good idea (tm). See here
  for guidance on how to tell if the indexes are being used in queries.

Testing

This answer will simply be some tests and considerations in the hope of making your decision easier. There could be reasons for having all these filtered indexes depending on your workload, or they could be hoarding space and resources without having benefits. My amount of data will be smaller than yours, testing on SQL Server 2017. As always YMMV.

Trying to recreate parts of your setup

  I have a SQL Server database and stumbled across a table with a
  uniqueidentifier primary key named ActivityId and columns StateCode
  (int) and ActivityTypeCode (int) along with many others. The tables
  has 13M rows and there are 94 unique values for ActivityTypeCode with
  row counts ranging from 1 to 4M

CREATE TABLE dbo.Activities(ActivityId UNIQUEIDENTIFIER PRIMARY KEY NOT NULL  DEFAULT NEWSEQUENTIALID() , StateCode int, ActivityTypeCode int,val1 int, val2 int);

INSERT INTO dbo.Activities(StateCode,ActivityTypeCode,val1,val2)
SELECT 1,1,1,1;
-- 1 value

INSERT INTO dbo.Activities(StateCode,ActivityTypeCode,val1,val2)
SELECT TOP(4000000) 2,2,ROW_NUMBER() OVER(ORDER BY(SELECT NULL)) as rn1,ROW_NUMBER() OVER(ORDER BY(SELECT NULL)) as rn2

FROM master..spt_values spt1
CROSS APPLY master..spt_values spt2;
--4m values

INSERT INTO dbo.Activities(StateCode,ActivityTypeCode,val1,val2)
SELECT TOP(9000000) ROW_NUMBER() OVER(ORDER BY(SELECT NULL)) / 1000 % 300 as rn1,
                    ROW_NUMBER() OVER(ORDER BY(SELECT NULL)) / 1000 % 93 as rn2,
                    ROW_NUMBER() OVER(ORDER BY(SELECT NULL)) as rn3,
                    ROW_NUMBER() OVER(ORDER BY(SELECT NULL)) as rn4

FROM master..spt_values spt1
CROSS APPLY master..spt_values spt2
CROSS APPLY master..spt_values spt3;
-- 9M other values, ActivityTypeCode from 0 - 93

  I have 97 different filtered indexes on (StateCode, ActivityId) where
  the only difference is the where clause on
  (ActivityTypeCode=(_some_value_)).

I went for 94 indexes, for the 94 distinct values in ActivityTypeCode

SELECT 'CREATE INDEX IX_StateCode_ActivityID_ActivityTypeCode_Filtered_'+CAST((rn-1) as varchar(2))
        + ' ON dbo.Activities(StateCode,ActivityId) INCLUDE(ActivityTypeCode)'
        + ' WHERE ActivityTypeCode = '+CAST((rn-1) as varchar(2)) +';'
FROM
(
SELECT TOP(94) ROW_NUMBER() OVER( ORDER BY (SELECT NULL)) as rn
FROM master..spt_values
) as a

The space used

EXEC sp_spaceused 'dbo.Activities'

name        rows                    reserved    data        index_size  unused
Activities  13000001                994736 KB   527920 KB   437488 KB   29328 KB

Filtered index testing

In your question you did not specify if ActivityTypeCode is included in the filtered indexes. If they are not included, only exact filter matches will be used. More on that here.

Parameterized queries without OPTION RECOMPILE

When using parameterized queries without OPTION(RECOMPILE), the parameter will not be known to sql server at runtime.

This means that you will not be able to use your filtered indexes. We would not be able to reuse the plan with different parameters if we were able to use filtered indexes.

--If your queries are parameterized
EXEC SP_EXECUTESQL N'SELECT StateCode,ActivityId
                     FROM dbo.Activities
                     WHERE ActivityTypeCode = @P1 and StateCode = @P2',N'@P1 INT, @P2 int',@P1=1,@P2=2

This is also true for stored procedures like the following:

CREATE PROCEDURE dbo.bla(@P1 int, @P2 int)
AS
BEGIN
SELECT StateCode,ActivityId
FROM dbo.Activities
WHERE ActivityTypeCode = @P1 and StateCode = @P2

END
EXEC dbo.bla @P1=1, @P2=2;

Parameterized queries with OPTION RECOMPILE

EXEC SP_EXECUTESQL N'SELECT StateCode,ActivityId
                     FROM dbo.Activities
                     WHERE ActivityTypeCode = @P1 and StateCode = @P2
                     OPTION(RECOMPILE)',N'@P1 INT, @P2 int',@P1=1,@P2=2

As stated earlier, OPTION(RECOMPILE) enables us to use filtered indexes when creating the plan.

You could add the same OPTION(RECOMPILE) to the procedure talked about earlier.

You can also resort to dynamic SQL to pass parts of the filtering as a literal and part of the filtering as a parameterized query. More on that here.

Estimates

The one issue with the plan that uses the filtered index is that the query estimates are way higher than the actuals:

You could resolve these estimates by creating / changing the filtered index so that it has ActivityTypeCode as one of the key columns instead of ActivityId.

CREATE INDEX IX_StateCode_ActivityID_ActivityTypeCode_Filtered_1_B
ON dbo.Activities(StateCode,ActivityTypeCode)
INCLUDE(ActivityId)
WHERE ActivityTypeCode = 1;

This is only true when also filtering on StateCode.

I was not able to create custom statistics or get sql server to use custom statistics to fix this. There might be a way to do this without having to change the index

Filtered index < - > regular index(es)

  To my eyes, all 97 indexes could be dropped and replaced with one
  index on just StateCode (since ActivityID is the primary key and is
  already included as a hidden column). My question is: Is there any
  reason why I would want to keep multiple filtered indexes?

Assumptions

Lets assume that the queries can leverage the filtered indexes and that the ActivityTypeCode is included in the index.

The indexes

Proposed index by OP

CREATE INDEX IX_StateCode
ON dbo.Activities(StateCode);

This index seems strange to me, if you are only filtering on StateCode, why would the filtered indexes on ActivityTypeCode even exist?

Which is why we will focus on two other indexes in this example:

CREATE INDEX IX_StateCode_ActivityTypeCode
ON dbo.Activities(StateCode,ActivityTypeCode);

CREATE INDEX IX_ActivityTypeCode_StateCode
ON dbo.Activities(ActivityTypeCode,StateCode);

The space used

EXEC sp_spaceused 'dbo.Activities'

name        rows                    reserved    data        index_size  unused
Activities  13000001                1819584 KB  527936 KB   1260992 KB  30656 KB

Testing

Filtering on StateCode & ActivityTypeCode

Running the query that used the filtered index earlier:

EXEC SP_EXECUTESQL N'SELECT StateCode,ActivityId
                     FROM dbo.Activities
                     WHERE ActivityTypeCode = @P1 and StateCode = @P2
                     OPTION(RECOMPILE)',N'@P1 INT, @P2 int',@P1=1,@P2=2

The new index is used and the estimates are correct.

We saw earlier that the estimated for the filtered index were ~4M rows for these specific parameters. This could have been circumvented by creating a new index

Filtering only on ActivityTypeCode

When only filtering on ActivityTypeCode it starts to get interesting

EXEC SP_EXECUTESQL N'SELECT StateCode,ActivityId
                     FROM dbo.Activities
                     WHERE ActivityTypeCode = @P1
                     OPTION(RECOMPILE)',N'@P1 INT',@P1=1

The non filtered index is used

While in theory, the entire filtered index could be scanned without having to apply any filtering:

And both estimated rows are correct.

Even when filtering on the parameter that should return +4M rows @P1=2 the non filtered index + seek is chosen.

This is due to the estimated rows and the index seek <-> scan tipping point.

When adding 4M extra rows, the scan is chosen.

If it comes down to this, I would be fine with dropping the filtered indexes and using the regular indexes specified above. But again, this is based on one query on own test data. More details would be needed to get an answer closer aligned with your dataset & workload.

What does the testing tell us?

The testing results only gives us information about my own tests and my own queries.

Some takeaways are that:

the size of the filtered indexes is not going to be higher than
regular indexes.
Managing these will be harder, what if new values are added that have no filtered index
There are  cases when you cannot use filtered indexes, such as
parameterization
If you are using literals / OPTION(RECOMPILE) you will be able to
use filtered indexes and might get better query plans at the cost of
recompilations
You should at include the column in the filtered index & watch out
for wrong estimates

The rest is up to you to figure out.


postgresql cannot drop a table when autovacuum holds lock on it Autovacuuming process holds RowExclusiveLock and ShareUpdateExclusiveLock on the table, which I want to drop. I read that autovacuum should be canceled automatically, when submitting DROP TABLE command, but it doesn't happen. Maybe I have wrong configuration of postgresql? Furthermore I don't have any delete or update operations of those tables — only inserts and drops. Can I disable autovacuum on those particular tables?

If no delete or update happened, autovacuum shouldn't process the table, per autovacuum_analyze_threshold. See Automatic Vacuuming for the relevant configuration parameters.

You may set log_autovacuum_min_duration to zero to help figure out what's being autovacuumed.

In any case, it is possible to completely disable autovacuum for a particular table, with:

ALTER TABLE tablename SET (autovacuum_enabled = false,  toast.autovacuum_enabled = false);

This is documented in  Storage parameters.


How do I resolve this data source connection issue with Cognos? We have (fairly recently) installed IBM's Cognos 10 as our business intelligence reporting tool. Most of the databases that we report over are DB2 9.7 LUW Enterprise Edition. We installed the DB2 Runtime-Time Client in order for Cognos to have access to the driver to communicate with DB2.

We also have some legacy systems and databases on our iSeries (aka AS/400) - so this means DB2 for i. We need Cognos to now recognize these databases. From what we have read up on, we need to install DB2Connect to grant access to DB2 for i from a DB2 LUW perspective.

Our Cognos was installed on a Windows Server system. We installed the RTC on the same system and can catalog DB2 LUW databases. We can get Cognos to create data sources with those databases.

To get iSeries functionality, we recently installed DB2Connect (on the same system as Cognos and RTC) and were able to catalog the iSeries database and connect and call into it. But we cannot get Cognos to see the cataloged database. It just looks like it is not there.

Some of the documentation we looked at makes it seem like you cannot access two different DB2 drivers locally within the same process in Cognos.

Our thoughts:

Uninstall RTC and just install DB2Connect with all the datasources cataloged through that and then have Cognos look them up. Our concern with this method is that we are afraid that Cognos may lose information for existing reports and projects that are already set up. We do not want to mess up/lose work already done by other teams.
We are wondering if there would be a nice clean way to migrate from RTC to DB2Connect so we could move connections off of the one to the other. Our concern here...how do we get DB2Connect to be recognized by Cognos in the first place?
Is there another way to catalog the iSeries databases and/or get them recognized by Cognos?

Thoughts? Ideas?

Edit: Our company has decided not to wait. They are bringing in a consultant to help us rebuild this from scratch so we better understand the product, how it installs, works, etc.

That being said, I would still welcome any thoughts or ideas on this one.

What we ended up doing was rebuilding our environment since it was only "development" anyway. We installed DB2 Connect rather than the Runtime-Client so that way we could catalog the iSeries databases and the LUW databases for Cognos to use.

We also could have used a gateway pattern where we could have set up DB2 Connect as a "Gateway Server" and cataloged the iSeries databases there and then cataloged the databases from the Gateway Server on the Runtime Client. That would have gotten us around our original issue too.

Interestingly enough, we were steered away from using a Gateway Server when we rebuilt our environment. No reason why. Even though IBM supports it, they don't recommend it. Only thing I can think of is that it introduces another failure point.


BLObs – Should I Store In Database Or in Directory Structure? BLObs – Should I Store In Database Or in Directory Structure

I have been developing a database to replace an ageing data structure that contains 1000's of files within a directory structure. The database being used is MySQL running on a Linux Mint server although much of the development s being done on a Mint laptop for convenience. Part of the reason for moving from the old structure to a database is to avoid errors that have been caused by misnaming of directories and files, and also to extract related information more easily for use in trend analysis and other scenarios. Two of the tables contain BLObs. One has six of which four are infrequently accessed – five of them can be about 2Mb in size but may be larger. The other has four files – three of around 0.1Mb and one of around 4Mb. At a meeting this morning a consultant (cloud/web/database) said that it would be better to keep the BLObs in a file structure with the database holding pointers to them.

Having spent the last few hours Googling I am now started to get confused. Is there a right or wrong way to handle BLObs, and are there any guidelines on which method to choose?

Thank you...

I've seen it done both ways. If you want to bulk up your database size just start using BLOBS.

Pro's for storing documents in your database

you're already storing your data there so why not a little more? (Only it's a lot more)
a certain simplicity of access.  Any request to read only has to go one place to access the document or thing

Cons for storing BLOBS in your database

datafiles grow quickly, backups are slower.  As soon as it takes longer to backup the database than you have available quiet time more sophisticated and expensive options are required
in Oracle you cannot easily access BLOBS/CLOBS across a database link so your data in those fields cannot be accessed by other databases

Pro's for using a file system

system admins feel backing up a directory is a lot easier than backing up a database
same for adding more disc space
separation of concerns (although this is illusory: if the database is down you can't find the document anyway and same if the application server is down)


DBCC Usage on production system We have a system running SQL Server 2008 R2 on a Hyper-V VM (64GB RAM and 16 virtual cores).

Another VM (the application server) within the system accesses the db server quite intensively for both querying and data updates.

We have been experiencing two main exceptions in the application server at the time were a particular maintenance sub plan stored procedure is executed.

The two exceptions are:

  System.Data.SqlClient.SqlException (0x80131904): Timeout expired.  The timeout period elapsed prior to completion of the operation or the server is not responding (.......)

  System.Data.SqlClient.SqlException (0x80131904): The app domain with specified version id (1859) was unloaded due to memory pressure and could not be found. (.....)

The maintenance plan stored procedure executed at this time has the following statements:

DBCC FREESYSTEMCACHE ('ALL')
DBCC FREESESSIONCACHE
DBCC FREEPROCCACHE
CHECKPOINT
DBCC DROPCLEANBUFFERS

This is obviously causing the issues after some testing. Also I have found out that:

Timeout exception is caused by this:

CHECKPOINT
DBCC DROPCLEANBUFFERS

Memory pressure exception is caused by this:

DBCC FREESYSTEMCACHE ('ALL')
DBCC FREESESSIONCACHE
DBCC FREEPROCCACHE

Unfortunately we don't know the reason for having this maintenance task happening. My first thought is remove this task, but I don't know if there would be more problems by not having these running.

Are these DBCC commands really necessary?

Thanks

First, find the person who put that job in place and have him/her tied to a chair listening to cats scratching a chalk board while you go for lunch.

When done with that, compare your resource utilization and performance data for extended periods before and after the job runs. I suspect you'll see perf dips and a number of resources getting really busy after the job executes. You can temporarily suspend the job and monitor closely for a week to see what happens. I doubt you'll run into problems but as with all changes to a prod system, still keep a close eye for a while and make sure you capture perf, resource usage and waits data.

These sprocs are typically run in a test environment, especially for performance testing, to see how specific queries or the overall workload behaves on a cold-start equivalent. It can also be used to see how specific parts of the system handles unexpected load spikes especially storage.

We have encountered a single support engineer whose standard perf tuning method involves doing these things on a production system; we couldn't get rid of him fast enough. Running all of them on a production system regularly is pretty close to rebooting your server daily to keep it healthy and doing it in the middle of a busy day for best results - really, really shouldn't.


Combining columns in index With reference to RolandoMySQLDBA's answer on

MySQL: Index when joining to tables not being used (Performance optimizing question)

He mentioned

  Make sure you build an index that involves the columns deleted, visible, and date_created as follows:

ALTER TABLE blogposts ADD INDEX deleted_visible_date_created (deleted,visible,date_created);

May I ask, what is the benefit of combining columns in index? And why have you not included date_published in the index, which was also part of WHERE clause?

Should we create indexes of all columns used in WHERE clause or not?

What has to be adhered to is the query you know you are going to make.

Let go back to the original question's query

SELECT /* [things omitted] */ articles.blogpost_id, articles.id AS articleid
FROM blogposts
JOIN articles ON articles.blogpost_id = blogposts.id
WHERE blogposts.deleted = 0
AND blogposts.title LIKE '%{de}%'
AND blogposts.visible = 1
AND blogposts.date_published <= NOW()
ORDER BY blogposts.date_created DESC
LIMIT 0 , 50

Look carefully at the WHERE clause. I see two static values

blogposts.deleted = 0
blogposts.visible = 1

These cannot be indexes by themselves because of their implied cardinality. Think about it:

deleted is either 0 or 1 (That's a Cardinality of 2)
visible is either 0 or 1 (That's a Cardinality of 2)

All Query Optimizers (MySQL, Oracle, PostgreSQL, MSSQL, etc) would take one look at those indexes and decide not to use the index.

Look at the ORDER BY clause : ORDER BY blogposts.date_created DESC. This shows a predictable ordering schema. This may help bypass the need for sorting.

Combining these three columns into a single index gives the Query Optimizer some relief by gathering data with delete=0,visible=1,date_created already merged.

Although MySQL is perfectly capable of doing Index Merging, you should never expect the Query Optimizer to do index merging on indexes with very low cardinalities. Even if it did, just expect poor performance. Query Optimizers will choose full table scans, full index scans, and range scans over lopsided index merges any day. Creating covering indexes can bypass having to do index merges, but it can become a burden or a big waste of time and diskspace if the cardinality of the multiple-column index is still too low. Thus, the Query Optimizer would still choose not use it. Therefore, you must know your key distribution well in order to formulate multiple column indexes that you will write queries to use properly.

Here are some nice Links on Covering Indexes and When to Make Them

http://peter-zaitsev.livejournal.com/6949.html
http://www.mysqlperformanceblog.com/2006/11/23/covering-index-and-prefix-indexes/
http://ronaldbradford.com/blog/tag/covering-index/

As far the date_published column goes, it would be alright to add it to the index. It would bring that much more relief to the Query Optimizer. It will perform an index scan to test for <=NOW() but that OK. That's will covering indexes are for.


Does sp_droparticle @force_invalidate_snapshot=1 invalidate the entire snapshot? Forgive this simple SQL Server question, but if I just want to remove a single stored procedure from snapshop replication (2008 SQL Server R2), if I call sp_droparticle, will it invalidate the entire snapshot, or just the snapshot on that article?

IE, I've got a big database, and I don't want to force a full snapshot generation again, does sp_droparticle only affect the snapshot for that single object?

The idea of a snapshot is a point-in-time copy of the entire publication, so once you make a change that affects the snapshot, it's no longer valid in its entirety.

From here:

  Dropping an article from a publication involves dropping the article and creating a new snapshot for the publication. Dropping an article invalidates the current snapshot; therefore a new snapshot must be created.

Once you call sp_droparticle and invalidate the snapshot, the publication cannot be subscribed to again until a new snapshot is generated.


Select rows with most recent date for the given date I have a table to manage the stock which looks like below

product    batch   qty  date
Grape      BAT0    42   10/3/2016
Apple      BAT1    12   11/3/2016
Banana     BAT2    15   11/3/2016
Orange     BAT3    24   11/3/2016
Pine       BAT4    28   11/3/2016
Apple      BAT1    9    12/3/2016
Banana     BAT2    10   12/3/2016
Pine       BAT4    25   12/3/2016
Apple      BAT1    5    13/3/2016
Banana     BAT2    6    13/03/2016
GreenApple BAT5    15   13/03/2016

If I pass the date 12/03/2016 then I need the last dated row to return as below

product batch   qty date
Grape   BAT0    42  10/3/2016
Orange  BAT3    24  11/3/2016
Apple   BAT1    9   12/3/2016
Banana  BAT2    10  12/3/2016
Pine    BAT4    25  12/3/2016

Also I have more than 160,000 rows in that table and it will grow each day.

How can I write an optimised query?

It's a bit hard to understand the logic of the query from the explanation, but I think you mean you get a date as input and you need the closest by date for each product?

If so, I have a solution, pretty sure not the optimal one, but maybe the logic can lead you in the right direction:

SELECT t.id, t.product
FROM table t
JOIN
(
    SELECT
        t.id,
        t.product,
        t.date, MIN(ABS(UNIX_TIMESTAMP('yourdate') - UNIX_TIMESTAMP(t.date))) AS diff
    FROM table t
    GROUP BY t.product
) x ON x.diff = ABS(UNIX_TIMESTAMP('yourdate') - UNIX_TIMESTAMP(t.date))
AND x.product = t.product


Find what values are not in multiple columns I need to find what "Codes" from column "RMS_CODE" and column "RMS_ALT1_Cde" are not being used in the Child # (starting in position 6 for a length of 4) column within the "APC_Spec" number. I tried using the sql in the image provided, but it only looks in the row and doesn't check the whole column within the "APC_Spec" requirement. Can anyone suggest a method to check which codes are not being used per "APC_Spec" in the Child column #?

Try this:
with tRMS_CODE as
   (SELECT DISTINCT RMS_CODE FROM RMS_Table)
   , tRMS_ALT1_Cde as
   (SELECT DISTINCT RMS_ALT1_Cde FROM RMS_Table)
SELECT DISTINCT * FROM RMS_Table
WHERE
   SUBSTR(CHILD,6,4) NOT IN (SELECT * FROM tRMS_CODE) AND
   SUBSTR(CHILD,6,4) NOT IN (SELECT * FROM tRMS_ALT1_Cde) AND
   APC_Spec='36559'


Handling 'Transaction count after EXECUTE indicates a mismatching number of BEGIN and COMMIT statements' Our systems suddenly started failing on:

  Transaction count after EXECUTE indicates a mismatching number of
  BEGIN and COMMIT statements.

There was no service pack or other installation changes and the data has not changed in nature.

It is a legacy system with no documentation with nested stored procedures and triggers, some of which have XACT_ABORT ON and IF @@TRANCOUNT > 0 ROLLBACK.

In order to make a temporary fix, I want to ignore the error and carry on through the procedure but it is not picked up by the CATCH.

Is there any way I can catch and handle this error? Is there any reason that badly written nested transactions could start behaving differently? We have checked connections defaults have not been changed.

  Microsoft SQL Server 2012 - 11.0.5343.0 (X64) Standard Edition
  (64-bit) on Windows NT 6.3  (Build 9600: ) (Hypervisor)

Is there any way I can catch and handle this error?

No.  This isn't your problem.  You have some other error, and your stored procedure is coded to rollback on error.  Performing a ROLLBACK in a stored procedure that didn't start the transaction is also an error, and will generate this message.

You need to look at the previous error.  Before the ROLLBACK the procedure should be calling RAISERROR or THROW to return the underlying error details to the client.  In SSMS you'd see both error messages, in Visual Studio debugging the SqlException has a collection of Errors you can interrogate.  But other clients sometimes only display the last error message.

If it isn't, it should.  Also you can use Profiler to see it regardless, using the "Errors and Warnings"/"User Error Message Event" or similar XEvent.


Decision concerning RAM limit What steps would you do to be sure that STANDARD edition with its 128GB RAM limit is enough for your production?

My subjective feeling is that we are somehow on the edge and I am looking for strong arguments to do the right decision to the future.

We are now running EVALUATION edition (so in fact ENTERPRISE). That gave me the space for testing the influence of having RAM limit (Max Server Memory) set to 128 GB and more (in fact set to 169 GB). Let's call the states before (Max Server Memory = 128 GB) and after (Max Server Memory = 169 GB). While testing I was collecting wait stats and several Memory based counters.

In general, wait statistics didn't change much while monitoring before and after the RAM increase. Not even in buffer latch and memory category of wait types.

Memory counters provides more interesting values but are they strong enough indicators of lack of memory?

Memory Grant Pending: value stays = 0 before and after

Total & Target server memory: keeps close to each other before and after - no spikes

System memory state:
before - it's mostly in "Physical memory state us steady" state
after - it goes to "Available physical memory is hight" state, not suprisingly

Page life expectancy: varies much over time (before and after)
before: sometime goes under 300 but in average it is more
after: sometimes goes under 300 (but less time)

Batch Reguests/sec & SQL Compilations / sec: the ratio between those two counters didn't change (before / after) - so there seems to be enought space for plans in the buffer, unfortunately the ratio is still higher than 10%.

Memory Utilization

I would also run the following statement to see how SQL Server memory is being used:

SELECT
  physical_memory_in_use_kb/1024            AS sql_physical_memory_in_use_MB,
    large_page_allocations_kb/1024          AS sql_large_page_allocations_MB,
    locked_page_allocations_kb/1024         AS sql_locked_page_allocations_MB,
    virtual_address_space_reserved_kb/1024  AS sql_VAS_reserved_MB,
    virtual_address_space_committed_kb/1024 AS sql_VAS_committed_MB,
    virtual_address_space_available_kb/1024 AS sql_VAS_available_MB,
    page_fault_count                        AS sql_page_fault_count,
    memory_utilization_percentage           AS sql_memory_utilization_percentage,
    process_physical_memory_low             AS sql_process_physical_memory_low,
    process_virtual_memory_low              AS sql_process_virtual_memory_low
FROM sys.dm_os_process_memory;

At some point when you keep on increasing memory, the sql_memory_utilization_percentage column will start moving away from 100%. This is because SQL Server may not require more memory.

SQL Server Edition Features

Depending on your SQL Server Edition there are some features that are available or not. Two being the Read-Ahead and Advanced Scanning capabilities which are described in the article Reading Pages (Microsoft | SQL Docs).

SQL Server 2016 Scalability and Performamnce

Feature           | Enterprise | Standard
------------------+------------+----------
Read-Ahead        | Yes        | Yes (possible limitations; see Correction)
Advanced Scanning | Yes        | No

SQL Server 2017 Scalability and Performamnce

Feature           | Enterprise | Standard
------------------+------------+----------
Read-Ahead        | Yes        | Yes (possible limitations; see Correction)
Advanced Scanning | Yes        | No

SQL Server 2019 Scalability and Performamnce

Feature           | Enterprise | Standard
------------------+------------+----------
Read-Ahead        | Yes        | Yes (possible limitations; see Correction)
Advanced Scanning | Yes        | No

Read Ahead

  The Database Engine supports a performance optimization mechanism called read-ahead. Read-ahead anticipates the data and index pages needed to fulfill a query execution plan and brings the pages into the buffer cache before they are actually used by the query. This allows computation and I/O to overlap, taking full advantage of both the CPU and the disk.

Correction Apparently the Read Ahead feature is available in all editions of SQL Server. I found the article When are read ahead reads issued (Microsoft Social MSDN - Paul White) which answers this question. However, there can still be internal limitations depending on the edition that would affect the amount of read-aheads performed in the Standard Edition.

  i.e. Enterprise can read further ahead, if the circumstances are
  right.  The exact numbers might change between versions (that second
  quote is taken from Bob Dorr's SQL Server 2000 I/O Basics paper -
  http://technet.microsoft.com/en-us/library/cc966500.aspx).  It might
  be 2,048 pages these days - who can keep up? ;c)

Advanced Scanning

  In SQL Server Enterprise, the advanced scan feature allows multiple tasks to share full table scans. If the execution plan of a Transact-SQL statement requires a scan of the data pages in a table and the Database Engine detects that the table is already being scanned for another execution plan, the Database Engine joins the second scan to the first, at the current location of the second scan. The Database Engine reads each page one time and passes the rows from each page to both execution plans. This continues until the end of the table is reached.

(Taken from the linked Microsoft article Reading Pages)

Because you are testing on "Enterprise Edition" you may be observing a performance boost just because of these additional features.

  Caution
  There are features which may or may not be available in the version of your SQL Server Standard Edition. Some features became available within the same version but with different Service Packs!

Please ensure you are comparing Apples (Standard) with Apples (Standard) and not Apples (Standard) with Pears (Enterprise).

Rare Cases & Trace Flags

In some rare cases having too much memory can be counter-productive. This situation is described in the Knowledge Base article regarding the Trace Flag 2335.

  When you configure max server memory sp_configure option in Microsoft SQL Server to a large value, you may notice that a particular query may run slowly. But if you lower the value for this option, the same query may run much faster.

Answering Your Question

  What steps would you do to be sure that STANDARD edition with its 128GB RAM limit is enough for your production?

Compare Standard Edition with Standard Edition
Keep an eye on your baseline counters

If your counters show a decrease in performance and users are complaining, consider switching to Enterprise Edition if only for the benefits of the various features available in Enterprise Edition. Please test thoroughly before switching.
If your counters don't show a decrease in performance and users are happy, then 128 GB might be just right.


Transfer ERD between Databases on SQL Server I've a development database on SQL Server 2008. I want to copy the production database back to my development server to get the latest data for testing. Unfortunately the production database is missing the many ERDs I've created to my development database.
If I just restore a backup, I'll loose my ERDs. Is there any way to save or script the ERDs and transfer them to another database if both have the same schema?

Thanx

It sounds as if you are using the Database Diagrams feature from within SSMS. I found this tool out at CodeProject: http://www.codeproject.com/Articles/15080/Script-SQL-Server-2005-diagrams-to-a-file

I cannot speak to how well it would work for you. I would suggest you use a different tool, such as perhaps Visio, in order to create your ERDs so that they are more portable.


Can SQL profiles be deployed to other Oracle Databases? When using The SQL Tuning Advisor in SQL-Developer. I get often some recommendations to accept a SQL-Profil. I guess it is a good idea to do so.

But is this similar to proposed indexes, which I can deploy to similar databases or is this something, that must be analyzed for each individual database?

SQL Profiles are basically a kind of statistics rather than a database object like an index, but they can be transported to another database - see this SF question for how


Get the old version of stored procedure code in sql server We have modified one of our stored procedures code and accidentally committed it in production DB also. Now we need the older version of the code. Is there any way to get the old version of a stored procedure from the DB? We didn't take any backup of the code. Please help.

To answer you directly.

You cannot get back the old version of the sp , if it is already executed in production, unless you have a backup or you have a customize trigger that logs all execution of sp.

This happens to my team before, that's why we created a trigger that logs all execution. or you can use SQL source control.

The only thing that you can do now is data fix in production.


With SQL transactional replication, does changing user permissions to a table trigger a new snapshot? I was hoping to get some help with a SQL Server 2012 transactional replication scenario - or maybe just my understanding of where permissions are stored in SQL server. If I change a users permissions to a particular table (like granting SELECT or INSERT) on the publisher in a transactional replication configuration, would that change the schema such that subscribers would need to pull from a new snapshot?

My concern is potential down-time with such a change where the subscriber would effectively be offline while the new snapshot is created. Ideally I could just grant new permissions to a particular user to a replicated article either on the publisher or subscriber without making things reset. I'm not concerned about the updated permissions being replicated from the publisher to the subscriber (in fact, ideally they wouldn't). Thanks in advance!

If you change a user's permissions to a particular table BEFORE a Subscriber has been initialized or reinitialized, if the article property Copy permissions is set to true, the permissions will be copied to the Subscriber when the snapshot is applied.

If you change a user's permissions to a particular table AFTER a Subscriber has been initialized, the permissions will not be replicated on the fly, unless you generate a new snapshot and mark the Subscriber for reinitialization.  Note that the article property Copy permissions must be set to true.

What is typically done is if you need to change a user's permissions to a particular table AFTER a Subscriber has been initialized, and you need the permissions to be present at the Subscriber without reinitializing the Subscriber, the script to grant permissions are posted to the Subscriber using sp_addscriptexec.

I have a post detailing sp_addscriptexec located here at Executing scripts with sp_addscriptexec.


PostgreSQL: Corrupt primary key, inconsistent table While recovering from a cloud failure, I found that some tables on a PostgreSQL database are behaving strangely. These tables are indexed using a primary key, but a pg_dump yielded duplicate fields, failing a pg_restore on a backup server.
I have tried to REINDEX:
REINDEX INDEX rank_details_pkey;
ERROR:  could not create unique index "rank_details_pkey"
DETAIL:  Table contains duplicated values.

The index is defined as:
<table info here>
Indexes:
    "rank_details_pkey" PRIMARY KEY, btree (user_id)

And, oddly,
SELECT user_id, COUNT(*) FROM <table name> GROUP BY 1 HAVING COUNT(*) > 1;
 user_id | count
---------+-------
(0 rows)

To conclude - I have duplicate values in my table which can not be found or cleared.
Any ideas how to fix this? This is a production server, so all fixes should be done without affecting service.

There are various ways this can happen in Oracle - I'm not sure about postgres, but I think I would call this an "integrity violation" rather than "corruption"

Perhaps you can do one of the things suggested here, ie set enable_indexscan = off or

begin;
drop index rank_details_pkey;
select user_id, count(*) from rank_details group by user_id having count(*) > 1;
rollback;

But "there are likely some locking issues with this, so be careful with it in production"

The idea is to force the query to scan the table rather than just the index (which does not have the duplicates). You may also, and more simply, be able to acheive the same by:

select user_id, f(<some other column>), count(*)
from rank_details
group by user_id, f(<some other column>)
having count(*) > 1

where f() returns a constant, which may trick the planner into a table scan.


How to return specific row number of a Hierachial query - Oracle I have this query that works as expected. Within Oracle SQL Developer it returns ~19,500 rows.  Unfortunately our current application limits our result to 10,000.  I know I will need to run this query multiple times and merge the resultsets. But I need to be able to limit query results in blocks of 10,000.

Adding a Where clause before the Start statement only works for the first execution:

Where RowNum Between 1 and 10001 works as expected.

Where RowNum Between 10001 and 20001 returns no rows.

Based on my testing, limiting by Level will not work due to the number of number of children within each level.

Thanks for all your help!

Select WFS.WORKFLOWSTEPID

, WFS.STEPTYPE

, WFS.SUBWORKFLOWBASEID

, WFS.SUBWORKFLOWID

, WFS.Sequence

, WFS.WORKFLOWSTEPNAME

, WFS.Sequence ParentSEQ

, WFB.WorkflowBaseID

, WFB.WORKFLOWNAME

, WFS.WORKFLOWID

, Level

from WorkflowBase WFB

Inner Join Workflow WF on WFB.REVOFRCDID = WF.Workflowid

Inner Join WorkflowStep WFS on WF.WORKFLOWID = WFS.WorkflowID

Start With WFS.WORKFLOWID =  '48810e80000001ca'

Connect by Prior WFS.SubWorkflowBaseID = WFB.WorkflowBaseID

Order Siblings by ParentSeq, Sequence

See this article from Oracle why the rownum approach won't work:
http://docs.oracle.com/cd/B19306_01/server.102/b14200/pseudocolumns009.htm

See this article on StackOverflow for the solution:
https://stackoverflow.com/questions/13351318/table-rownum-returns-0-records-if-rownum-is-tested-for-value-greater-than-100

Edit (for those who don't like reading):
- you want to do something like this

select count(*)
from (select rownum r
        from some_table
        order by r)
where r>=101
and rownum <= 400;


How to create a table to a specific user First good evening every body.
assume that you are logged in as a super user and you want to create a user.

  CREATE USER 'user1'@'%' IDENTIFIED WITH 'mysql_native_password' AS '*483EC567DF968A6AD5E279257828E7CC4A12F0C2' REQUIRE NONE PASSWORD EXPIRE DEFAULT ACCOUNT UNLOCK;

and you want to create some tables for that user something like this

CREATE TABLE user1.user1Table -- table for user1
(
idUser INTEGER PRIMARY KEY AUTO_INCREMENT,
Name VARCHAR(30)
) ENGINE=INNODB ;

when I run the sql script I get an error like this

ERROR 1049 (42000): Unknown database 'user1'

I thought at the beginning that I would have something like that

mysql> desc user1Table ;
+--------+-------------+------+-----+---------+----------------+
| Field  | Type        | Null | Key | Default | Extra          |
+--------+-------------+------+-----+---------+----------------+
| idUser | int(11)     | NO   | PRI | NULL    | auto_increment |
| Name   | varchar(30) | YES  |     | NULL    |                |
+--------+-------------+------+-----+---------+----------------+
2 rows in set (0.01 sec)

mysql>

why that is not possible ??? i have wamp server

thank you in advance

CREATE TABLE {database}.{tablename}

creates a table in the specified database. As such you'll need a user only table
CREATE TABLE user1Table...

To give a user access to the table:

GRANT ALL ON {database}.user1Table TO 'user1'@'%'

Where {database} is the database in which the tables are created.


SQL Server Automate refresh of one table only from one environment to another I'm looking for a best practice/option to refresh one table only from Prod to Staging environment.

Technical Details:

The table in Source is loading daily with new data.
The table in destination should represent new data only when refreshed.

Some of the options I'm thinking:

Replication (Transactional)
SSIS
TSQL Script - SQL Agent Job

My first preferences would be to go with option 3. Will this be a good option? If yes can someone guide me to a script.

It's a question of preference. Please see below some advantages/disadvantages I found working with each of them.

Replication (Transactional) - works excellent as long as you don't need to touch the table. It's real time so you will make your developers/testers really happy with "fresh", almost real-time data. It becomes tricky if you need to add a column, remove a column or if your table structure changes because in almost all cases you need to reinitialize the replication from scratch, but for just one table of a few MB it will work fast, this becomes an issue from a few GB on because it takes time. If you set up the replication with your account make sure it will not expire or get disabled because you will crash the replication; best is to use a service account. Another thing to consider is networking & server resources - transactions getting replicated real time will 'suck up' some network and some of the server memory and I/O, but unless your table gets a few hundreds rows (or more) modified every second I don't see that as being a real problem. You have a nice tutorial on how to set this up here.
SSIS - you will not have (almost) real time data in your staging environment. So if any of your developers or testers have the idea of comparing data between prod an stage - refresh time needs to be considered. If you change the table structure you need to modify the package (always make sure to also refresh the metadata inside the package). My experience is that you work more to keep a SSIS package up to date than you work to recreate replication, but this is subjective to personal preference and requirements. Also if you set up your package as a 'truncate & load' you will delete and write again the same data every day no matter if your source table gets updated or not. If you don't want that you need to add some logic in your package and that can be complicated and lead to data loss or duplicates if you don't know your data very well. Another thing to consider is your access rights: if you want to deploy it you need SQL Admin-like rights, if you want to run this from a local folder, you need RDP rights to that machine and write permissions to copy your package to a local drive on the server. You still need a SQL Agent job to automate it (I assume you don't want to manually run this). Please keep in mind that you need to use Visual Studio with BI add-on to create the package. You find documentation about setting up the SSIS package here and a nice tutorial here.
TSQL Script - SQL Agent Job - This is also my personal preference as it is the easiest to set up and maintain, specially if you have no experience with SSIS and/or replication. Just like for SSIS, you will not have  (almost) real-time data. You have much more control and it's easier to change if your table structure changes. Depending on what kind of changes you can have on your source table it can get tricky to synchronize your data, but you always have the option of a truncate table and insert everything from production every time (highly NOT recommended for big tables and even if your table is small today, consider how big it will be in 3-5 years!). When I set up jobs like this I usually take my time to understand the data and all the changes that might happen and I use a merge statement as much as possible. If you have a insert date column in your prod table, then you can work your logic around that. When you set up the SQL Agent job make sure you change the owner to a service account, otherwise the job will crash if your account gets disabled.

For point 2 & 3 consider doing some index work right after you insert/modify the data to keep your performance to a high level.


How do I ensure I have split my tables without loss of data? This is w.r.t Database Normalisation, when I have a redundant table that causes anomalies, I would want to consider splitting it in smaller tables as per the NF rules.
BUT
Say I split a big table into two eg. Student and Marks Table my questions is ---
How do I know I've done the right split?
Or to put it in another way,
How can I cross check if I haven't lost any information after the split i.e. I can still do a join on both tables and get my un-normalized table back?
This is easy to inspect visually when you have a small number of attributes, but what if the table is really huge.
How can I ensure that I've split the table in a right manner?
When I was in college my professor spoke of some equation which must always be true whenever you split tables, that ensures that the split is done without loss of any information. And by doing the right joins we can get the big dirty redundant table back.

A classical theorem in normalization theory says that you have a lossless join (that is a decomposition of a relation such that when you perform the natural join of the decomposed relations you obtain the original relation) in the case of two relations  if and only if the intersection of the attributes of the relations contains a candidate key for one of the two relations.
This result can be generalized to a decomposition with any number of relations.


How to call modify() on a nodelist in T-SQL? I have a process step where I have an XML document in a SQL Server column and I need to alter the text of a node that could occur multiple times. Modify() with 'replace' DML only operates on single nodes. To complicate matters, the replacement needs to happen based on a lookup table maintained locally.

I can use nodes() to get a nodelist and contrive a query to join the data appropriately, but at that point, I no longer have a reference that modify() can act on.

It appears that my only choice is to create a CLR procedure or function and use some sort of dotnet native XML iteration. That's less appealing, since the business logic would be locked up in the source code, not visible in the procedure definition. Is there any way to do this transformation using the XML methods exclusively?

Three options come to mind.

Use a while loop and modify one node at a time

Shred the XML using nodes(), modify the values and then rebuild using for xml.

Cast the XML to a string and use replace().

Whatever works best in your case depends on the shape of the XML and what kind of modification you need to do. If you add the table structures with sample data and expected output to the question you might get a better answer.


How to check for null in column? and use other column instead? How would I write a query that involves the following check:

if colA is null then colB = colB1 + colB2

if colA is not null then colB = colA

According to SQL Standards (works with multiple flavors of SQL), you can use the COALESCE() function:

SELECT
    colB = COALESCE (colA, (colB1 + colB2))
FROM theTable;

or a CASE expression (which is not so elegant but can be used for more complex conditions):

SELECT
    colB = CASE WHEN colA IS NOT NULL
                  THEN colA
                ELSE colB1 + colB2
           END
FROM theTable;

Using T-SQL (works with Sybase and Microsoft SQL Server)

SELECT
    colB = ISNULL (colA, (colB1 + colB2))
FROM theTable;

However, you've not told us about any guarantees that either colB1 or colB2 will always have a value. If either of those are NULL then everything will be NULL. If that's the case then you can chain along conditions in COALESCE which would look like

colB = COALESCE(colA, (colB1 + colB2),0)

or using ISNULL

colB = ISNULL(ISNULL(colA, (colB1 + colB2)),0)

If you don't want to use it in the result but in a WHERE or HAVING condition, no change is needed. Either:

WHERE    colB = COALESCE (colA, (colB1 + colB2))

or:

WHERE    colB = CASE WHEN colA IS NOT NULL
                       THEN colA
                     ELSE colB1 + colB2
                END

but you have one more option:

WHERE (  colB = colA
      OR colA IS NULL  AND  colB = colB1 + colB2
      )