How to start indexing a new data warehouse I am building a data warehouse (SQL Server 2012, Kimball Dimensional modeling, 750GB) that will supply data to reports (SSRS), cubes (SSAS) and various datafeeds.

I plan to use an index tuning tool (probably sp_BlitzIndex), but how do I start? Do I start off with no indexes and then let sp_BlitzIndex tell me what indexes to use after a while? Or do I start with the obvious indexes and build on that with sp_BlitzIndex?

I was planning on adding a clustered index on every primary key and a non-clustered index on every foreign key -- and then let sp_BlitzIndex suggest the less obvious choices. But maybe I should leave it all up to sp_BlitzIndex.

Your plan of starting with clustered indexes on primary key is a great start.

Ideally, your clustered indexes (to start with) should be an INTEGER IDENTITY for the DW's surrogate keys to:

keep the clustered index as narrow as possible,
and so that the Dimension and Fact tables are not heaps.

If, and only if, you expect a table to surpass 2,147,483,647 rows (the max INT value), use BIGINT IDENTITY instead of INT. (See Numeric Data Types).

Carefully consider non-clustered indexes on EVERY foreign key. It might not be necessary, and may be more harm than good to put an NCI on EVERY foreign key. Instead, focus on creating non-clustered indexes only on Foreign Keys for which they would be used in JOINs or WHERE clauses in your queries. However, don't just create one Non-Clustered index for every FK -- if a query uses multiple FK's in joins, consider creating a single NCI to support those joins. Play with the order of the index's key columns to see which works best. TEST TEST TEST.

EDIT 9/26/2016
Here's a blog post by Kendra Little discussing indexes on Foreign Keys:
http://www.littlekendra.com/2016/09/22/indexing-foreign-keys-guidelines/

Heaps are bad for SELECT / UPDATE / DELETE performance (i.e. SSRS and SSAS queries), especially with any kind of JOIN or WHERE clauses in queries, because all queries on heaps would be Table Scans -- there's no such thing as a Heap Seek. Fragmentation is often a problem with HEAP tables.

Heaps are great for INSERT performance.
Heaps are generally known to be the fastest for INSERTing new data, because there would be no index changes (i.e. updates to an index's b-tree) or sorting of the data necessary that SQL Server would need to manage if there were indexes. This is most useful for STAGING tables. (See What are valid usage scenarios for heap tables). The degree to which HEAPS are better than CLUSTERED INDEXES while INSERTing new data will vary.

EDIT: 9/7/2016
Here's a blog post by Daniel Hutmacher discussing additional use cases for heaps:
https://sqlsunday.com/2016/09/01/compelling-case-for-heaps/

Be aware that clustered indexes (and non-clustered indexes) will reduce performance of your (assumed) daily nightly loads, because any data changes (INSERTs, UPDATEs, DELETES, MERGEs) will cause indexes that use those columns with changed data (I'll combine INSERTed, UPDATEd, DELETEd, MERGEd as "changed" data) to also be updated, which causes I/O writes to the indexes. It might be a small hit to performance; it might be a big hit to performance -- it depends on the size of the table and how much data is being changed (I/U/D/M), and other factors. A "small" dimension table would probably be fine to keep the Clustered Index and Non-Clustered indexes on for the ETL. A "large" dimension table might benefit from the disable/re-build method. TEST TEST TEST. (See Performance of Non Clustered Indexes on Heaps vs Clustered Indexes).

One method (not necessarily a recommendation, but an option) to combat the performance reduction for data changes of a (large) table with existing indexes is to:

disable or delete the indexes of table(s) as part of your SSIS ETL jobs prior to loading the data, in order to optimize INSERT performance,
then rebuild or re-create the index after data load is complete.

If you try this route, test and track performance to ensure this method is genuinely faster than loading the data to the table with indexes -- it might not be faster. TEST TEST TEST.

While performance tuning queries, take measurements using
SET STATISTICS IO, TIME ON; in front of your SELECT queries. Focus on reducing "logical reads" with your non-clustered indexes.
Document and track logical reads before the index and after the index; keep a history. If you are successful, you can show these numbers to your team and/or boss.

Your data warehouse will feed SSRS reports and SSAS cube processing runs. Create non-clustered indexes that support/tune these queries (maybe the queries that SSRS and SSAS use are views or stored procedures?). In other words, tune your SSRS/SSAS queries (via non-clustered indexes). You'll want to have your SSRS report views or procedures query code to be static/stabilized (i.e. no code changes) for some time (say, 1 week of steady executions) for the plan cache, and then proceed with tuning. You could do it sooner, but you might not get the same results.

For optimizing SSAS, use Profiler (or Extended Events, if you prefer) during your SSAS cube processing runs to capture the SELECT queries the cube uses when pulling data from your DW -- it might not be what you expect. These are the queries you tune for improving SSAS cube processing time. Don't forget to turn off profiler when done, as it does add some overhead.

The bonus of index tuning your DW, is that you are also tuning your SSRS reports and SSAS cube processing runs at the same time.

Index performance tuning is an on-going practice; you're never done. You might need to make changes to your index strategy if:

New tables get created / joined on
New where clauses or new joins to the views / stored procedures
New columns get added to existing tables
Data changes (i.e. now there's 1 million, or 10 million, or 100 million more rows than when you first index tuned)

Index performance tuning can be challenging and tricky, yet highly rewarding when you get a big performance win. I encourage you to search for free webinars that are dedicated to talking about index strategies. Learn about the different types of non-clustered indexes (i.e. "covering" indexes, filtered indexes, columnstore indexes, etc.) and how and when they are useful, and when they might do more harm than good.

When you're ready to step up above free webinars, consider finding books to buy, and/or paid training to further improve your indexing skills -- maybe your employer will pay for the training and/or books. Since you mentioned Brent Ozar team's sp_BlitzIndex, Kendra Little has done great in-person paid classes (which I've attended) and recorded webinars on index performance tuning. There are also plenty of other great MVPs and great SQL Pros that offer index training too.

Good luck, I hope this was helpful and insightful.


Why the value changes when I assign it to a variable? I have a table like this:

// mytable
+---------+-------------+
|   id    |    ban      |
+---------+-------------+
| int(11) |   BIT(11)   |
+---------+-------------+
| 1       | 01111111000 |
| 2       | 01111111111 |
| 3       | 00000000001 |
+---------+-------------+

As you see, the value of ban column is a binary number (its datatype is BIT(11)). and when I select it, the value is still a binary number, like this:

SELECT ban FROM mytable WHERE id = 1;
-- output: 01111111000

But when I assign it to a variable, surprisingly the value changes, like this:

SELECT ban INTO @ban FROM mytable WHERE id = 1;
SELECT @ban;
-- output: 1016

Well what happens when I set 01111111000 into @ban? Why it will be changed (1016)?

You know? In reality, each digit (bit) of that 11-bit number (01111111000) refers to something. For example, the second bit determines user's limitation on voting. I mean it the first digit (right to left) is 1, it means the user can vote, if it is 0 it means the user is banned for voting.

Anyway, I don't need to get a decimal like this 1016. I need to assign and keep exactly that 11-bit number to @ban variable. Because I need to check it like this:

IF ( IFNULL((@ban & b'10' > 0), 0) < 1 ) THEN
 -- user can vote
ELSE
-- user cannot vote
ENDIF;

So there is different action for each digit. That's why I don't want a 10-based number like 1016. I want a bit-based number like this 01111111000 in that variable.

To make it more clear, here is the task of third digit:

IF ( IFNULL((@ban & b'100' > 0), 0) < 1 ) THEN
 -- user can flag
ELSE
-- user cannot flag
ENDIF;

See? the third digit (of that 11-bit number) determines user's flagging ability (limitation).

Ok, in conclusion, how can I assign a bit value to a variable without converting its datatype (which seems converting will be happened automatically, so how can I avoid that)?

Maybe this quote from http://dev.mysql.com/doc/refman/5.6/en/bit-functions.html will help clarify:

Bit functions and operators comprise BIT_COUNT(), BIT_AND(), BIT_OR(), BIT_XOR(), &, |, ^, ~, <<, and >>.
Bit functions and operators require BIGINT (64-bit integer) arguments and return BIGINT values, so they have a maximum range of 64 bits. Arguments of other types (such as the BINARY and VARBINARY binary string types) are converted to BIGINT and truncation might occur.

(The 5.7 man page hints that some future version will extend BIT beyond 64 bits.)


Deployment script always re-create constraints and indexes When generating a deployment script from SSDT it always generates code that drops the foreign key constraints & indexes then creates again those constraints and indexes. How to remove the code that drops and recreates those objects when generating deployment script? Any options in SSDT?

It is normally because there is a difference between how they are written and how sql stores them.

As a one time thing to a compare back from the db to the project and you should see some difference on the keys, apply them and it should stop doing it.


Logon events detail in oracle I want to fetch the oracle user login detail for the month February.
I tried the below query.

select distinct
    os_username,username,userhost,terminal,timestamp,action_name,logoff_time
from  dba_audit_session
where timestamp between '01-FEB-2017'
                    and '28-FEB-2017'
order by timestamp;

but I need the IP address as well.
Can anyone help me! how to fetch the user IP address along each user session?

I am stuck here.

I have check dba_audit_trail table as well but didn't help.

I need the below columns:

username,os_user,logon_date,logoff_date,IPADDR, terminal, sessionid,hostname.

According to AskTom, "you can get the client ip address if you audit connect, it is in the comments column of the audit trail, you have to parse it out.". That's from 2003 but I think it is still the case:

select comment$text from sys.aud$

or possibly:

select comment_text from dba_audit_trail;


Best approach to compare specific columns of 2 tables in different databases? I'm running an application who does have a shorten version of a table in the backend database.

Both application- and backend-tables are having the same primary keys and some specific key columns.

What would be the best approach to compare both tables to find unmatched records? (The tables are containing currently 1.5 Mio rows -> going up to 5 Mio rows)

Application-Table-Example

id (pk), column1 (pK), column2, column3, column4, column5, column6, column7, column8, column9, column10 [datetime]

Backend-Table-Example

id (pk), column1 (pK), column2, column10 [datetime]

Columns I want to compare

id (pk), column1 (pK), column2, DATE(column10) [datetime -> converted to DATE]

My current approach is to create a CRC32-Checksum by the columns "id (pk), column1 (pK), column2, column10" for each row in the application database and to save them in a temporary table with the structure "id (pk), column1 (pK) and hash (crc32)". Afterwards I run an union select in the backend database and create also a crc32-checksum by the backend table and combine it with the temporary table. By grouping them after all common tables I should get for each dataset 2 rows. If I get only 1 row for a dataset, we have found an unmatch.

SELECT MIN(TableName) as TableName, {$columns} FROM (
                SELECT 'APPLICATIONTABLE' as TableName, {$columns}
                FROM APPLICATIONTABLE
                UNION ALL
                SELECT 'BACKENDTABLE' as TableName, {$pks}, CRC32(CONCAT_WS({$data['fields']})) as hash
                FROM BACKENDTABLE
            ) tmp GROUP BY {$columns} HAVING COUNT(*) = 1

Do you have any suggestions for a better approach? (My current approach works, but it's maybe not the most performant one)

Frontend- and Backend-Database are on different MySQL-Servers
Federated tables are no option to skip the temporary table part
https://www.percona.com/doc/percona-toolkit/2.2/pt-table-sync.html looks like, that it doesn't support a partial comparison of explicit columns?
To some datetime fields I need to add fuzziness to obtain the same checksum (DATE(column10) ignores mismatches in second range)

Thanks a lot!

So, your approach is good in my opinion, with little tweaks:

First, when you create the temp table, or when you are generating the hash value, use DATE function (You mentioned is but not sure you're using it in the query):

SELECT ...., crc32(CONCAT(column1, DATE(column10))),

so you don't have to deal with 'fuzziness' [Assuming that you want the preciseness to be at day level]

To get the records that exist in one table, and not the other:

SELECT applicationtable.id, applicationtable.column1
FROM applicationtable
LEFT JOIN backendtable USING (id, column1)
WHERE backendtable.id IS NULL [AND backendtable.column1 IS NULL]
UNION
SELECT backendtable.id, backendtable.column1
FROM backendtable
LEFT JOIN applicationtable USING (id, column1)
WHERE applicationtable.id IS NULL [AND applicationtable.column1 IS NULL]

To get the records that have different values for the same PK:

SELECT applicationtable.id, applicationtable.column1
FROM applicationtable
INNER JOIN backendtable USING (id, column1)
WHERE applicationtable.crc32_value <> backendtable.crc32_value

Be careful though:

If NULL value is allowed in column2 or in column10, CRC32 would give the same "null", even if the second field has a value, and this will not be reported by the query as "NULL <> NULL"
It is very unlikely to get collided CRC32 values, but it is not impossible. MD5 would be an option, but more expensive one.
Having [AND backendtable.column1 IS NULL] in the WHERE clause is not necessary if the field is defined to be NOT NULL


Best practice for working with materialized views in Postgres As background, I work with many materialized views which are based on PostGIS spatial queries, some of which take days to refresh (these views are infrequently refreshed whenever the underlying spatial data (ie road networks) are updated). I then have many other views which are dependent on these materialized views.

While using materialized views in general works well, it becomes a nightmare when I need to alter the definition of one of the views (for instance, changing a join condition or adding new columns). Since there's no equivalent of CREATE OR REPLACE ... for materialized views, I end up having to delete and then recreate all the dependent objects.

Recently I've started "wrapping" all my materialized views in standard views as a way around this. So basically every materialized view has a corresponding standard view which selects all columns direct from the materialized view. Then no dependents directly reference the materialized view, they only ever reference the wrapper view. This allows me to temporarily replace the definition of the wrapper to point at a different data source (not the materialized view), so I can then alter the definition of the materialized view and then lastly re-direct the wrapper back to the materialized view. Phew! It works, and avoids the need for the cascading drop/recreate object process, but still seems very clunky.

Is there a best practice process for working with materialized views to overcome these limitations? What's the usual approach to take here to avoid the cascading delete/recreate steps?

While using materialized views in general works well, it becomes a nightmare when I need to alter the definition of one of the views (for instance, changing a join condition or adding new columns).

One such idea is to put them all in the same schema, for instance foo_matviews or foo_cache, and then to create a script that drops the schema entirely (with CASCADE), and then reinitializes the schema and all the internal mat-views back.

To be honest, it's always worked so well I've been kind of confused at why this is an issue for anyone. I always have a few SQL scripts in my code repo that do something similar to this. I've never even needed anything more complex like schema migration tools like flyway.

That all said, after you have them all in your schema you can create an extension if you wish.


Does the Query Optimizer Prefer to Query on Constants before Columns? I think I have found the answer for this already but I am hoping to get some additional perspective.

Assume we are JOINing two tables together on a shared column and each table then has a different column we are going to do a Constant search on.  When we build an index to support the query, for each table do we want to put the JOINing column first, or the Constant column first?  I am thinking now it is the Constant column first.  When I look at a query plan for a different query that prompted this question, it appears it tries to create a subset of each table and then JOIN them together.  Instead of JOINing the two tables togeather and filtering down from there.

EX: Joining Shipments to Customers where Shipment is Shipped and Customer is Active

SELECT [Columns]
FROM Shipment S
   INNER JOIN Customer C
      ON S.CustomerID = C.CustomerID
WHERE S.IsShipped = 1
AND C.IsActive = 1

I am thinking the two best indexes to use are below.  Because the Query Optimizer would prefer to scan the Constant first then JOIN on the 2nd column instead of JOINing the two tables together and filtering on the constant after that.

CREATE NONCLUSTERED INDEX [IX_IsActive-CustomerID] ON [dbo].[Customer]
(
    [IsActive] ASC,
    [CustomerID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO

CREATE NONCLUSTERED INDEX [IX_IsShipped-CustomerID] ON [dbo].[Shipment]
(
    [IsShipped] ASC,
    [CustomerID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO

Instead of:

CREATE NONCLUSTERED INDEX [IX_CustomerID-IsActive] ON [dbo].[Customer]
(
    [CustomerID] ASC,
    [IsActive] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO

CREATE NONCLUSTERED INDEX [IX_CustomerID-IsShipped] ON [dbo].[Shipment]
(
    [CustomerID] ASC,
    [IsShipped] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO

Is that right?

Assuming there are no other queries of concern, the general answer is that you want more selective criteria in the leading columns of the index (note that selectivity here is in relation to the predicate rather than a measure of how unique the particular values in the column are).  In general, you want the optimizer to eliminate as many rows as possible as quickly as possible.

Assuming that CustomerID in Shipment is NOT NULL and has a foreign key to Customer, meaning that the inner join is guaranteed not to eliminate any rows from Supplier, then s.IsShipped = 1 is the only selective predicate and it would make sense for it to be the leading column.  If, on the other hand, the inner join was more selective (imagine that someone moved rows from Customer to Customer_Archive periodically and Shipment could join to either of those tables), it would make sense to have CustomerID as the leading column.


Merge MySQL tables without data repetition My situation is this: I have several DIFFERENT tables in my MySQL database with the same name columns. Such tables can contain (or not) the same data.

What I need to do is a kind of "merge" between these tables, creating a new table that will contain only the data repeated once.

In other words, I want to check for duplicate records in multiple tables, leaving one with the records without repetition.

A example to clarify:
I have 2 tables (in fact I have 10 tables, but 2 works for the example) with the columns id, name and city like below

+----+---------------------------------------------------------+-----------------------+
| id | name                                                    | city                  |
+----+---------------------------------------------------------+-----------------------+
| 6  | HOTEL PANAMBY GUARULHOS                                 | guarulhos             |
| 31 | PLAZA INN MASTER                                        | new york              |
| 40 | PLAZA INN AMERICAN LOFT SãO PAULO                       | sao paulo             |
| 41 | PLAZA INN PARIS                                         | paris                 |
+----+---------------------------------------------------------+-----------------------+

The id '41' of table 1 could be equal to id '80' of table 2, for example and so subsequently. My task is compare the tables and create a new one without repetition. Don't exist any FK between tables, they are isolated.

Until now, I discovered two approaches to deal with this.

1) Using SELECT UNION. Apparently is the best approach, but I couldn't see the correct joins and group by to use it, once I don't have FKs;

2) Using a Stored Procedure with cursors for two tables and compare row by row. I tried use this one, but SP it isn't my speciality, so i created a good one, but the tables has over 1000 rows each one and my SP always crashed the memory.

I found this question here, but is not my case, I want a new table without repetition. Other questions in this site with similar problem talk about SQL Server and don't work for me.

Currently, I'm doing the workaround with a PHP script, but I'd like to know for any MyQSL solutions, if any.
I found some articles about Dataclean, but I don't know if this is the case and never work with this in MySQL.

I hope I was clear.

It is not clear to me, if you want to keep records, which are duplicated, or you want only those records, which are in only one of the tables.

You can try:

-- create tables
create table tbl1
(
    id INT,
    name VARCHAR(50),
    city VARCHAR(25)
);

create table tbl2 like tbl1;

-- insert data
insert into tbl1 values (1,'adam', 'eden'), (2, 'eva', 'eden');
insert into tbl2 values (3,'adam', 'eden'), (4, 'pedro', 'alexandria');

This will give you records with unique name column:

select * from (select * from tbl1 union select * from tbl2) as a group by name;
+------+-------+------------+
| id   | name  | city       |
+------+-------+------------+
|    1 | adam  | eden       |
|    2 | eva   | eden       |
|    4 | pedro | alexandria |
+------+-------+------------+
3 rows in set (0.00 sec)

Note: this will keep id from tbl1, if you want to keep id from tbl2, just switch order of tables in sql statement

And this will give you only those records, which are in one, or other table, but not in both

select * from (select * from tbl1 union select * from tbl2) as a group by name having count(*) = 1;
+------+-------+------------+
| id   | name  | city       |
+------+-------+------------+
|    2 | eva   | eden       |
|    4 | pedro | alexandria |
+------+-------+------------+
2 rows in set (0.00 sec)

Is that what you wanted?


Understanding IAM Pages: extent intervals I'm reading Itzik's book "Querying Microsoft SQL Server 2012" as well as reading/watching different educational material on the internet. My intention is to have a useful understanding of how the database internals work.

I have a small doubt that I couldn't resolve about the IAM pages. Since I'm in a very early stage of my understanding I need maybe extra help from those who have a better picture of it, so excuse me if my doubts seem ridiculous.

In chapter 15 "Implementing Indexes and Statistics" appears an image - shown below - as an example of an IAM page:

You can see by the red arrow what seems to be 16 pages related to the same extent. How is that possible? Is it an author/editor's mistake? Or what is more likely: is there something I'm not understanding correctly?

The other question I have is related to the pages intervals. Why aren't they contiguous? Take for example the last extent, it will cover pages with id 336 to 22642, or the preceding one, 296 to 328.

You can see by the red arrow what seems to be 16 pages related to the same extent. How is that possible?

The red arrow points to an entry showing that extents starting at 1:176 and 1:184 are allocated to the current entity (both 8-page uniform extents are in use) within the GAM interval.

  The other question I have is related to the pages intervals. Why aren't they contiguous? Take for example the last extent, it will cover pages with id 336 to 22642, or the preceding one, 296 to 328.

They are not extents, they are extent ranges allocated to the current entity (e.g. table, index) within the 4GB GAM interval the current IAM page covers.

The extent ranges shown as NOT ALLOCATED may be in use by other entities.

For more information, see Inside the Storage Engine: IAM pages, IAM chains, and allocation units by Paul Randal.


Query session parameter values of running session I don't think there's a way to do this, but I'd like to be able to query what value a running session currently has for its COMMIT_WRITE session parameter.

I haven't seen anything in the v$ performance views... anyone know of a SYS x$ view from which such a value could be retrieved?

Clarification - the need is to pull the parameter setting for another session, running independent of current session.

I know this question is more than a year old, but in case anyone stumbles here like I did, you can query V$SES_OPTIMIZER_ENV:

SYS> select name, value
        from  V$SES_OPTIMIZER_ENV
        where sid=54
           and name='hash_area_size';

NAME                                     VALUE
—————————————- ————————-
hash_area_size                           100000

Source: http://oracleinaction.com/other-session-params/


For recent versions of SQL-Server that support CTEs (and not the obsolete 2000):

Using a CTE and copying a trick of @Paul White in this answer (to get TOP in the recursive part of the CTE): How to recursively find gaps where 90 days passed, between rows

WITH CTE AS
(
    -- Anchor:
    -- Start with the earliest date in the table
    SELECT TOP (1)
        T.id, T.update_time, T.price
    FROM tableX AS T
    ORDER BY
        T.update_time

    UNION ALL

    -- Recursive part
    SELECT
        SQ1.id, SQ1.update_time, SQ1.price
    FROM
    (
        -- Recursively find the earliest date that is
        -- more than 5 minutes after the "current" date
        -- and set the new date as "current".
        -- ROW_NUMBER + rn = 1 is a trick to get
        -- TOP in the recursive part of the CTE
        SELECT
            T.id, T.update_time, T.price,
            rn = ROW_NUMBER() OVER (
                ORDER BY T.update_time)
        FROM CTE
        JOIN tableX AS T
            ON T.update_time >= DATEADD(minute, 5, CTE.update_time)
    ) AS SQ1
    WHERE
        SQ1.rn = 1
)
SELECT id, update_time, price
FROM CTE ;

A cursor solution, that works in older versions (I don't have a 2000 instance to test and the following will probably need adjustments). Modified from another answer, in the same question How to recursively find gaps where 90 days passed, between rows:

(first, some needed tables and variables):

-- a table to hold the results
DECLARE @cd TABLE
(   id INT NOT NULL,                        -- adjust types
    update_time DATETIME PRIMARY KEY,       -- according to
    price DECIMAL(10,2) NOT NULL            -- your columns
);

-- some variables
DECLARE
    @id INT,
    @update_time DATETIME,
    @price DECIMAL(10,2),

    @diff INT,
    @previous_update_time DATETIME = '1900-01-01 00:00:00' ;

The actual cursor:

-- declare the cursor
DECLARE c CURSOR
    LOCAL STATIC FORWARD_ONLY READ_ONLY
    FOR
    SELECT id, update_time, price
      FROM tableX
      ORDER BY update_time ;

-- using the cursor to fill the @cd table
OPEN c ;

FETCH NEXT FROM c INTO @id, @update_time, @price ;

WHILE @@FETCH_STATUS = 0
BEGIN
    SET @diff = DATEDIFF(minute, @previous_update_time, @update_time) ;

    IF @diff >= 5
      BEGIN
        INSERT @cd (id, update_time, price)
            VALUES (@id, @update_time, @price) ;

        SET @previous_update_time = @update_time ;
      END ;

    FETCH NEXT FROM c INTO @id, @update_time, @price ;
END

CLOSE c;
DEALLOCATE c;

And getting the results:

-- get the results
SELECT id, update_time, price
    FROM @cd
    ORDER BY update_time ;

Tested at SQLfiddle (in 2008 version).


Howto implement correctly Businness Logic in database I'm searching about how to disable triggers, and seems like it's not possible under MariaDB.

My problem is, how to manage then, when you import data.
I have a database where almost all business logic is inside it with triggers and stored procs. Probably, there are records autogenerated by triggers and stored-procs that were filled later by a user, and I don't know how to manage this if I import a database dump, or importing a csv file for example.

I'm sure the problem is I'm not seeing the obvious, and there are some problem in my data model, but because I'm starting with this project, I'm in time to modify what is needed, knowing best practices to take into consideration.

My question is, there are some best-practices to take into consideration, when we want to implement the business logic of an application, inside the database?

I hope I explained correctly.

This is a little Example coming to my mind:
On a deployed database, there are a record that will be created from a trigger, and later, this record, is modified by the user. When import an sqldump, because this records exists, sql dump will fail trying to inserting this existing recods.

Best practice...

A database is a place to store data.  It is the 'source of truth'.

The application is the place for
* interpreting the data
* "Business logic"
* Formatting and pretty-printing of output
* Cleansing the data as it is stored into the database.

It is often wise (especially in more complex systems) to have a "database layer" in which the "business" info is transformed to/from "database" info.  On the database side, there is "INT" and "FLOAT"; on the business side, there are other terms for numeric things.  The business may say "store this"; the layer may turn that into one INSERT, or split the columns across two tables, or normalization plus inserts, or INSERT .. ON DUPLICATE KEY UPDATE .., or INSERT IGNORE, or an INSERT plus augment a summary table, or ...

Yes, some of those things could be done with TRIGGERs and FOREIGN KEY "cascading constraints", etc.  But I would rather put all the logic in a single place.  I find triggers clumsy to code.  I find FKs limted in capability.


Fail to copy all PostgreSQL databases between servers although there is no error message I want to copy all databases from PostgreSQL 9.3 on Server-1 (Windows 7) to an empty PostgreSQL 12.0 on Server-2 (Windows Server 2012 R2). The steps are as follows:

On Server-1:

C:\Program Files\PostgreSQL\9.3\bin>pg_dumpall.exe -U postgres -W -f fulldump.sql

Copy fulldump.sql from Server-1 to Server-2.
On Server-2:

C:\Program Files\PostgreSQL\12\bin\psql.exe -f fulldump.sql -U postgres
Password for user postgres:

No error message appears, all seems to be fine. However, none of the databases appears in PostgreSQL 12.0 of the new Server-2. Unfortunately, there are to my knowledge no matching questions in this forum where I could draw some hints from. What is missing in my workflow?

The dump-file had a size of 0 Bytes indicating that it was empty. Repeating the dumping process delivered a non-empty file with which the import process with psql worked fine.


Track all modifications to a PostgreSQL table We have a table which has only roughly 500 rows, but it is very important for us.

I want see all changes which happen to this table. The changes should get tracked with a timestamp.

I don't want the tracking to happen in the application code, since I want to track changes which happen via psql shell, too.

I am happy with a PostgreSQL specific solution, since I don't use a different DB in this context.

Answer compiled from comments

You would put triggers for any DML event on the table that write information about the event to a log table. If you want to track DDL too, have a look at event triggers - sticky bit.

Some example implementations given by a-horse-with-no-name:

Tracking changes in PostgreSQL by Hans-Jürgen Schönig
Impossibly Lean Audit System for Postgres with hstore
Copying Pavel Stehule’s simple history table but with the jsonb type by Glyn Astill
Audit trigger in the PostgreSQL Wiki

Even if they are several years old, the basic idea hasn't changed and all of them are valid choices. The source for the fourth alternative has been updated recently: https://github.com/2ndQuadrant/audit-trigger


MySQL, wait_timeout, Mac OS X Mountain Lion - how do i set it? I need to change the wait_timeout for my MySQL database, but when I set this parameter in the my.cnf class (either in /etc or in /private/etc, because one of them is a link to the other), I still get the same default value when I type show variables in MySQL.

How do I change that value on Mac OS X then?

UPDATE
I tried setting the value using mysql console, but it didn't help:

mysql> set global wait_timeout = 333;
Query OK, 0 rows affected (0.00 sec)

mysql> show variables like '%timeout%';
+----------------------------+----------+
| Variable_name              | Value    |
+----------------------------+----------+
| connect_timeout            | 10       |
| delayed_insert_timeout     | 300      |
| innodb_lock_wait_timeout   | 50       |
| innodb_rollback_on_timeout | OFF      |
| interactive_timeout        | 28800    |
| lock_wait_timeout          | 31536000 |
| net_read_timeout           | 30       |
| net_write_timeout          | 60       |
| slave_net_timeout          | 3600     |
| wait_timeout               | 28800    |
+----------------------------+----------+
10 rows in set (0.00 sec)

It did work without the word "global", though, but that affects only the current connection.

I believe you're running into a few different problems:

1) For interactive clients like the mysql client program (and not for most web applications), session wait_timeout is initialised from interactive_timeout when your client connects, so changing the global wait_timeout has no effect on the effective wait_timeout for your mysql client. This is covered in the MySQL documentation here:

  On thread startup, the session wait_timeout value is initialized from the global wait_timeout value or from the global interactive_timeout value, depending on the type of client (as defined by the CLIENT_INTERACTIVE connect option to mysql_real_connect()). See also interactive_timeout.

2) You're changing a global setting (set global ...) and then looking at session variables ('show variables' instead of 'show global variables')

3) Session variables are only reinitialised from global variables when you reconnect

For the solution, the following should set the effective wait_timeout for your mysql client to 333:

set global wait_timeout=333
set global interactive_timeout=333

Then disconnect.

To persist the changes across restarts, you can add the following to the [mysqld] section of my.cnf:

wait_timeout=333
interactive_timeout=333

If the reason you're making these changes is because of a problem a client that doesn't set CLIENT_INTERACTIVE is having, then you can disregard interactive_timeout.

Before making changes, I recommend reviewing this blog post and the MySQL documentation to ensure you understand how these settings function and the impact of changing them.


Is it a bad practice to associate database records to relevant object storage by ID? I'm using Google Cloud Platform to host a web app. The app has a typical database structure, with a schema defined by Django migrations. For one of the models, Song, each record should be associated with an object stored in Cloud Storage which contains the audio of that song. Since this audio is created and stored immediately after the database record is created, what I've done historically is to simply title the object with the ID of the database record, so that each object would be stored as song/[ID]/audio.mp3

Is there a better approach to this? As long as each database record has a static ID, I can't see any critical issues with this one (and indeed, I've used it successfully in past projects of mine). But I have a hunch that many people would consider this a weak coupling and could open up the possibility for bugs.

In this case, for any Song you would have only one possible audio.mp3 recoding.

With various versions of the same song you will have to provide an additional field within the metadata.

In certain circumstances it may be valuable to prepare in advance some structure for various versions of the same record, building a path with defined version number, default to, say, 0.


Bulk change Agent Jobs' definition to "Quit the job reporting failure" from "Go to the next step" On Failure There are agent jobs with multiple steps in our servers and all their steps are defined as "go to the next step" "On Failure". So, even a job step fails, operator doesn't get notified because eventually the job succeeds.
How can I modify all steps in all jobs so that job fails reporting failure if a step fails?
edit: best way, I guess, would be to use msdb.dbo.sp_update_jobstep dynamically to update all steps where @on_fail_action !=2
Thanks all.

Use Script to update Job steps In Bulk

USE MASTER
GO
SET QUOTED_IDENTIFIER ON
GO

    DECLARE  @job_id VARCHAR(1000),
             @Step_id INT

    DECLARE JobList CURSOR
            FOR

            SELECT  jb.job_ID,
                    jstp.step_id
            FROM    msdb.dbo.sysjobs jb
                    INNER JOIN msdb.dbo.sysjobsteps jstp ON jb.job_id = jstp.job_id
            WHERE   name = 'Jobname'
            --AND Step_name <>'Step Name'     Add Step name to filter
            ORDER BY jb.job_ID, jstp.step_id

    OPEN JobList
        FETCH NEXT FROM JobList INTO @job_id,@Step_id
        WHILE @@FETCH_STATUS = 0
            BEGIN

                     -- Execute SP sp_update_jobstep to update Job Steps and pass 2(Quit with failure) as value to parameter @on_fail_action
                    EXEC msdb.dbo.sp_update_jobstep @job_id = @job_id, @step_id = @Step_id, @on_fail_action = 2 ---- 2 Quit with failure.

                FETCH NEXT FROM JobList INTO @job_id,@Step_id
           END
        CLOSE JobList
        DEALLOCATE JobList

Note: Make sure all steps need to be updated on failure as "Quit the job reporting failure" otherwise filter them by adding in WHERE Clause

Reference Link : https://msdn.microsoft.com/en-us/library/ms189827.aspx


Compound Indexes for different sorts I'm searching on a field and I want to sort on 3 different fields (viewcount,rating,time) separately. Am I correct in assuming that I need to do three different compound indexes {searchField :1, viewcount:1}, {searchField :1, rating:1} and {searchField :1, time:1} ?

Also one query could search with the searchField then do a range query and a sort on the time field, the last index would be the correct one too in this situation right ?

Your assumption is correct with one comment about sort order. Make sure your query sort order matches your index sort order otherwise index will not be used. Read more details here.

Consider a collection events that contains documents with the fields username and date. Applications can issue queries that return results sorted first by ascending username values and then by descending (i.e. more recent to last) date values, such as:

db.events.find().sort( { username: 1, date: -1 } )

or queries that return results sorted first by descending username values and then by ascending date values, such as:

db.events.find().sort( { username: -1, date: 1 } )

The following index can support both these sort operations:

db.events.createIndex( { "username" : 1, "date" : -1 } )

However, the above index cannot support sorting by ascending username values and then by ascending date values, such as the following:

db.events.find().sort( { username: 1, date: 1 } )


How to pass multi-valued characters in SSRS Report? I have a SSRS report that has 3 data sets feeding off one data source. The main dataset is a stored procedure that is aggregating some data based on a set of parameters powered by the other two datasets.

The main stored procedure powering this report has 4 parameters. One is an ID for the type of data, two are for start and end dates and the third is just a flag parameter. The flag parameter is the multi-valued parameter where I want to pass multiple flag values that are also VARCHAR values.

In my stored procedure for my @Flag parameter, I have the common:

WHERE [Flag] IN (@Flag)

Then of course the @Flag parameter on the SSRS report set to allow 'Multiple Values' that are also populated from a query that is pulling those @Flag values from a dimension table.

My Problem

In most cases when dealing with INT values, using the same technique works. However, when I'm dealing with character values, it fails. If I choose one flag, the report works magically. If I select more than one flag, it doesn't seem to pass the flags correctly to the stored procedure and no results return.

When testing the multi-value flag directly in the stored procedure:

WHERE [Flag] IN ('A', 'B', 'C')

The stored procedure works correctly. So the problem is not the stored procedure, but how SSRS is passing the multi-valued values to the @Flag parameter.

Solutions Tried

I tried doing the following adjustments to the SSRS dataset for this @Flag parameters:

=join(Parameters!<your param name>.Value,",")

AND this one too:

=SPLIT(JOIN(Parameters!<your param name>.Value,","),",")

These all work on single values, but never multi-values.

What am I missing here?

I found a solution. I did not properly split the values in the stored procedure with a UDF.

To pass multi-values correctly in this stored procedure, I would need to add the following code to the dataset parameter that I am using:

=join(Parameters!<your param name>.Value,",")

This is basically going to join multiple values into an array and pass it through the @Flag parameter. The next step is adding SQL to the stored procedure to receive and digest the values correctly so it reads the values with the IN clause.

Google search any UDF string parser online. There are many to choose from. I used dba_parseString_udf from Michelle Ufford http://sqlfool.com.

Once I had my UDF installed, I can now alter my IN clause to receive the new multi-valued parameter being passed by SSRS as follows:

WHERE [Flag] IN (SELECT * FROM dba_parseString_udf(@Flag, ','))

Therefore, SSRS will pass the following value:

@Flag = 'A,B,C'

Then my UDF will parse that string out correctly to:

A
B
C

And populate my @Flag parameter correctly with SELECT * FROM UDF()...


SQL tables relations i'm making a MySQL data base , my tables are: users, activitylog, activitytype.

to avoid repetitions i thought about including activitytype in each activity which is logged into activitylog table, for example:

1-Activity Types Table

2-Activity Log Table

Is it an interesting model if yes how could i do it ?

@Wozniak form what i can see you need the sql syntax t build this structure ! to do so use the syntax belew :

create table Activity_Types(
activity_type_id in,
activity_name varchar(20),
PRIMARY KEY (activity_type_id)
);

create table Activity_Log(
activity_id int,
user_id id,
activity_type_fk id,
activity_date date,
PRIMARY KEY (activity_id),
FOREIGN KEY (activity_type_fk) REFERENCES Activity_Types(activity_type_id)
);

Where foreign key that makes the relation is hold on the Activiy_log table under the activity_type_fk column.
 to join this tables just use this columns !


Errors starting mysql after moving to a new directory Ubuntu 18.04
mysql  Ver 14.14 Distrib 5.7.29, for Linux (x86_64)

I moved my mysql installation from the default /var/lib/mysql location to an external drive, using the following:

sudo /etc/init.d/mysql stop
sudo rsync -av /var/lib/mysql /media/E
sudo mv /var/lib/mysql /var/lib/mysql.bak

and then edited the /etc/mysql/mysql.conf.d/mysqld.cnf file to point the datadir param to its new location media/E/mysql.

Attempting to restart mysql, I got:

[....] Starting mysql (via systemctl): mysql.serviceJob for mysql.service failed because the control process exited with error code.
See "systemctl status mysql.service" and "journalctl -xe" for details.

On the off chance, I went back and edited the log_error param within the mysql.conf.d file to point to its new location as well (as it previously pointed to the original /var/lib/mysql location).  Still wouldn't start, and left with the following output from journalctl -xe:

chris@chris-x1c6:/usr/share/mysql$ journalctl -xe
Feb 29 12:36:46 chris-x1c6 audit[26261]: AVC apparmor="DENIED" operation="mknod" profile="/usr/sbin/mysqld" name="/media/E/mysql/error.log" pid=26261 comm="mysqld" reque
Feb 29 12:36:46 chris-x1c6 audit[26261]: AVC apparmor="DENIED" operation="mknod" profile="/usr/sbin/mysqld" name="/media/E/mysql/error.log" pid=26261 comm="mysqld" reque
Feb 29 12:36:46 chris-x1c6 audit[26261]: AVC apparmor="DENIED" operation="mknod" profile="/usr/sbin/mysqld" name="/media/E/mysql/error.log" pid=26261 comm="mysqld" reque
Feb 29 12:36:46 chris-x1c6 audit[26261]: AVC apparmor="DENIED" operation="mknod" profile="/usr/sbin/mysqld" name="/media/E/mysql/error.log" pid=26261 comm="mysqld" reque
Feb 29 12:36:46 chris-x1c6 audit[26261]: AVC apparmor="DENIED" operation="mknod" profile="/usr/sbin/mysqld" name="/media/E/mysql/error.log" pid=26261 comm="mysqld" reque
Feb 29 12:36:46 chris-x1c6 audit[26261]: AVC apparmor="DENIED" operation="mknod" profile="/usr/sbin/mysqld" name="/media/E/mysql/error.log" pid=26261 comm="mysqld" reque
Feb 29 12:36:46 chris-x1c6 mysqld[26259]: 2020-02-29T20:36:46.356540Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for
Feb 29 12:36:46 chris-x1c6 mysqld[26259]: 2020-02-29T20:36:46.357247Z 0 [Warning] Can't create test file /media/E/mysql/chris-x1c6.lower-test
Feb 29 12:36:46 chris-x1c6 mysqld[26259]: 2020-02-29T20:36:46.357287Z 0 [Note] /usr/sbin/mysqld (mysqld 5.7.29-0ubuntu0.18.04.1) starting as process 26261 ...
Feb 29 12:36:46 chris-x1c6 mysqld[26259]: 2020-02-29T20:36:46.359205Z 0 [Warning] Can't create test file /media/E/mysql/chris-x1c6.lower-test
Feb 29 12:36:46 chris-x1c6 mysqld[26259]: 2020-02-29T20:36:46.359247Z 0 [Warning] Can't create test file /media/E/mysql/chris-x1c6.lower-test
Feb 29 12:36:46 chris-x1c6 mysqld[26259]: 2020-02-29T20:36:46.359818Z 0 [ERROR] Could not open file '/media/E/mysql/error.log' for error logging: Permission denied
Feb 29 12:36:46 chris-x1c6 mysqld[26259]: 2020-02-29T20:36:46.359828Z 0 [ERROR] Aborting
Feb 29 12:36:46 chris-x1c6 mysqld[26259]: 2020-02-29T20:36:46.359846Z 0 [Note] Binlog end
Feb 29 12:36:46 chris-x1c6 mysqld[26259]: Initialization of mysqld failed: 0
Feb 29 12:36:46 chris-x1c6 mysqld[26259]: 2020-02-29T20:36:46.359874Z 0 [Note] /usr/sbin/mysqld: Shutdown complete
Feb 29 12:36:46 chris-x1c6 systemd[1]: mysql.service: Control process exited, code=exited status=1
Feb 29 12:36:46 chris-x1c6 systemd[1]: mysql.service: Failed with result 'exit-code'.
Feb 29 12:36:46 chris-x1c6 systemd[1]: Failed to start MySQL Community Server.

I've found other answers (here, here) that suggest tweaking AppArmor policy settings, but seems a little janky and wonder:

(1) if I did something wrong in moving the existing mysql installation;

(2) whether there's a solution preferable to editing the AppArmor policy file.

After a bit of trial and error, was able to get it to work using this approach plus modifying the log_error variable within the mysqld.cnf file to point to the new location as well.

Adding a line to deal with AppArmor was required (it's included in the linked tutorial), but otherwise works without issue.


Is it a good idea to schedule a daily CHECKPOINT? Checking our production logs from yesterday, we discovered a period of about 5 minutes when a whole bunch of really simple queries were timing out. Further investigation on the server logs showed a huge spike in disk activity, which led me to the conclusion that an automatic CHECKPOINT was being run on the DB at that time.

That's something I really don't want to happen during peak hours. So I was thinking of scheduling a daily CHECKPOINT every day during off-peak hours.

Is that a good idea? Bad idea? Waste of time? If not that, then what?

It's hard to know this for sure given the limited information here, but I don't think you're quite down to root cause yet.

Why did a huge spike in disk activity make you think the root cause was a checkpoint?
Why would the best fix be running a checkpoint manually?
Have you checked into which queries were running at the time, and what their wait stats were?

Checkpoints already happen much more frequently than daily - the default in 2016 is every minute.

So to answer the question:

Adding a daily checkpoint would be a bad idea and ineffective
Consider doing more troubleshooting like checking the server's wait stats
For a followup, include server config and wait stats info in your question (disclaimer: that's my blog post)


SQL Server: sp_describe_first_result_set with more than one select in a stored procedure I'm using sp_describe_first_result_set to retrive metadata from a stored procedure and having the following error:
Msg 11512, Level 16, State 1, Procedure dbo.sp_describe_first_result_set, Line 1 [Batch Start Line 0]
The metadata could not be determined because the statement 'SELECT 1' in procedure 'test' is not compatible with the statement 'SELECT 'str'' in procedure 'test'.

This SP can return different datasets depending on a parameter:
ALTER PROCEDURE [dbo].[test]
    @test NVARCHAR(50) = 'test'
AS
BEGIN
    SET NOCOUNT ON;

    IF (@test = 'test')
    BEGIN
        SELECT 1
    END
    ELSE
    BEGIN
        SELECT 'str'
    END
END

Looks like sp_describe_first_result_set does an UNION of the queries because if both the queries returns the same col model, the procedure works.
I've even tried to pass @parameter like this:
DECLARE @test NVARCHAR(50) = 'test2'
EXEC [dbo].[sp_describe_first_result_set]
    @tsql = N'test @test = @test'
    ,@params = N'@test NVARCHAR(50)'

but it doesn't work.
Is there a way to make it work?

That behavior is expected and is described on the sp_describe_first_result_set doc under the Remarks section:

For each control flow path, the first statement (if any) that returns
a result set is determined by sp_describe_first_result_set.
When multiple possible first statements are found in a batch, their
results can differ in number of columns, column name, nullability, and
data type. How these differences are handled is described in more
detail here:
[...]

If the data type differs, an error will be thrown and no result is    returned except for the following cases:

varchar(a) to varchar(a') where a' > a.

varchar(a) to varchar(max)

nvarchar(a) to nvarchar(a') where a' > a.

nvarchar(a) to nvarchar(max)

varbinary(a) to varbinary(a') where a' > a.

varbinary(a) to varbinary(max)

The doc has an example where an Error because the data types differ happens, like in your case.


Is there a way to specify the initial size of MySQL File-Per-Table Tablespaces? Preparing for massive data import into a MySQL 5.7 community instance, I'd like to know if there is a way to allocate upfront the total amount of space that will be used by the volume of information?

The purpose would by to eliminate overhead caused by the gradual auto-extend increments of the tablespace that, according to the reference guide, will be 4MB at a time, tops.

No. For ibdata files you have variable innodb_data_file_path=ibdata1:XXXXM:autoextend you can specify one or more but not for individual table spaces.


IP configuration for MultiSubnet AAG setup Is it  a good practice to have all the ip's in a different subnet for an AAG setup as mentioned in the link below? When I wanted to setup a 2 node AAG, the windows team in my office built the cluster and sent it over to me but apparently, all the ip's belong to the same subnet. The 2 Node setup is within the same Datacenter. We plan to expand this with a 3rd node in another site at SanJose later on

https://blogs.msdn.microsoft.com/sambetts/2014/05/16/building-true-multi-subnet-windows-server-failover-clusters/

There are a few things going on in your question. To start I presume you are referring to an Always On Availability Group, just to be clear. You shared a link about multi-subnet failover.

Multi-subnet failovers are a feature, not a requirement. So the two nodes in your data center don't need to be on different subnets. Even when you go to San Jose later, it depends on if the subnet is "stretched" there whether you even need to worry about multi-subnet failover or not.

There are also various network connections involved in an Always On Availability Group.

At the very least there needs to be a public Network that all the nodes can see each other on and receive traffic from the outside on. You could setup with just this one network. You can be, as your link indicates , on separate subnets, but if you are on one? That makes life easier.

You can also add (and I say should) other networks if you don't then this one public network handles all of the functions:

Cluster Communications/Heartbeat - this is a network where the windows cluster nodes can send heartbeat traffic. These don't need to communicate on the public network and just need to be routable to one another.

SQL Server Availability Group Replication Network - The Availability Group replicas talk to each other over a network. You don't have to configure a separate network, but many environments prefer to segregate their traffic to a network that only has this replication traffic. These NICs would not need to speak to the public or rest of the netowrk, and just need to be routable to each other.

You didn't ask about those extra networks, and it seemed like you were just talking about the public network, but I wanted to throw a few thoughts out.


MySQL LEFT OUTER JOIN performance on a local column condition Assume the following tables, indexed on the obvious keys:

Table users
------------------------
id | name | has_cars
------------------------
1    Dan    0
2    Bob    0
3    Jake   1
4    Jen    1

Table cars
------------------------
id | user_id | description
------------------------
1    3         Corvette
2    3         Viper
2    3         Lamborghini
2    4         Camry

Is there any performance benefit to the latter of the following two queries?

SELECT users.*, cars.*
FROM users
LEFT OUTER JOIN cars
ON cars.user_id = users.id

vs

SELECT users.*, cars.*
FROM users
LEFT OUTER JOIN cars
ON users.has_cars > 0
AND cars.user_id = users.id

These tables, are of course for sake of example. I'm curious about the implications on much larger tables with very sparse has_cars > 0 condition-meeting-rows. Is the optimizer able to take advantage of using a row-local condition to determine whether or not to attempt the LEFT OUTER JOIN per row?

EDIT: Adding another potential use case to add legitimacy to the question.

Here's a potential use case: let's say you have a table with a type column. For type a rows you want to join the type_a table data, for type b rows you want to join the table_b table data, etc, and here a local condition would determine whether or not to perform a LEFT OUTER JOIN:

SELECT widgets.*, a_widgets.*, b_widgets.*, c_widgets.*
FROM widgets
LEFT OUTER JOIN a_widgets ON widgets.type = 'a' AND a_widgets.widget_id = widgets.id
LEFT OUTER JOIN b_widgets ON widgets.type = 'b' AND b_widgets.widget_id = widgets.id
LEFT OUTER JOIN c_widgets ON widgets.type = 'c' AND c_widgets.widget_id = widgets.id

For each table join, are you getting any performance benefit from the added local condition (widgets.type = _)? As compared to:

SELECT widgets.*, a_widgets.*, b_widgets.*, c_widgets.*
FROM widgets
LEFT OUTER JOIN a_widgets ON a_widgets.widget_id = widgets.id
LEFT OUTER JOIN b_widgets ON b_widgets.widget_id = widgets.id
LEFT OUTER JOIN c_widgets ON c_widgets.widget_id = widgets.id

(Assuming there is a widgets.type column, and if a row's type is a,b, or c there is a corresponding row in the a_widgets, b_widgets or c_widgets table.)

It is not 'proper' to have redundant information in a database.  has_cars is redundant with EXISTS(SELECT 1 FROM cars WHERE user_id = ...).

So, once you eliminate has_cars, you are left with just a third option:

SELECT users.*, cars.*
    FROM users
    JOIN cars  ON cars.user_id = users.id

which is the preferred query.

Do not use LEFT unless you are hoping to 'find' rows missing from the right-hand table.  In that case, use the LEFT JOIN ... IS NULL pattern:

SELECT users.name  'Does not have any cars'
    FROM users
    LEFT JOIN cars  ON cars.user_id = users.id
    WHERE cars.id IS NULL;          -- to list _only_ those without cars

Or to get data from cars whether or not there is a row:

SELECT users.name,
       cars.description  -- will be NULL if no cars
    FROM users
    LEFT JOIN cars  ON cars.user_id = users.id;

Another variant:

SELECT users.name,
       COUNT(cars.id)  AS 'how many cars owned (incl 0)'
    FROM users
    LEFT JOIN cars  ON cars.user_id = users.id
    GROUP BY users.id;

This is equivalent to that:

SELECT name,
       ( SELECT COUNT(cars.id) FROM cars  WHERE user_id = users.id
             )  AS 'how many cars owned (incl 0)'
    FROM users;

See also GROUP_CONCAT() as a way of listing (in a single cell) all the cars owned by each person.


May postgresql's uuid_generate_v1() conflict when we change the computer date/time? According to postgresql uuid-ossp documentation uuid_generate_v1() is based on Mac address + timestamp:

https://www.postgresql.org/docs/9.4/static/uuid-ossp.html

On a distributed database scenario where we have hundreds of databases generating records with UUID keys and syncing back to a central database.

Suppose we detect a machine has a wrong date/time in the future and we change it back to the correct date/time. May it generate a conflicted UUID key on this particular machine?

One scenario is the summer time / daylight savings.

There is no reason, that I know of to use uuid_generate_v1() over uuid_generate_v4(). That said, Philᵀᴹ is largely correct when he says

  "One scenario is the summer time / daylight savings." -- no, it is not. Everything should be stored as UTC. Time always goes forwards. – Philᵀᴹ

Even if your display time is set to DST, (or something like CSTCDT), the underlying storage mechanism should be UTC.

That said, though everything should be stored as UTC, it's not always that way. There are exceptions, like with XP, or Windows 7, you may see some kind of abnormality, especially if you permit the other OS to modify the RTC.

And, there is always the problem of leap seconds if you really want to nitpick, when some kernels or daemons will roll back the second and it's basically up to them to see if they support this. Linux-proper and OSX does, Android does not afaik humorous NDT on Leap seconds

For more info about leap seconds
For more info about RTC/system clocks in linux

For reference, the spec details the process of generating a UUID v1 time-based UUID, 4.2 Algorithms for Creating a Time-Based UUID.


Does SSRS report user needs database level permission? I have a separate reporting server and a separate database server.  Now on the Reporting server site settings I could see there are some roles and users.  Suppose a User BOB has ‘POWER USER’ role assigned and the user can create and deploy report.

Now the confusion lies here as I want to remove some database logins does BOB needs to have database level permission (example execute permission and database reader ) on the database and also on the report server database  or the permission assigned to the role on SSRS will be good enough.

Does SSRS report user needs database level permission or the login that is used on the data source will only need database level permission.

the data source login will need permission, the AD used by the user to run the report will not


create User Defined Type with CHECK In PostgreSQL one can create a User Defined Type with an implicit CHECK by doing the following:

CREATE DOMAIN OrdinalT AS INTEGER CHECK (value >= 0);

In Sybase ASE 15.7 I've figured that the following is almost equivalent (except for the CHECK):

sp_addtype OrdinalT, "INTEGER";

How do I make a CHECK part of the user-defined type definition in Sybase?

You cannot make a check condition a part of the type definition syntactically, but you can create a rule:

CREATE RULE OrdinalTRule AS @value >= 0

and bind it to the data type:

sp_bindrule OrdinalTRule, OrdinalT

In order to modify the rule later, you will have to drop and recreate it. Before being dropped, the rule must be unbound:

sp_unbindrule OrdinalT

Note that the argument in the last statement is not the rule itself but the type to which the rule is bound.


Aggregating nested set models with field constraints I have a table built on the nested set model that basically records transactions of different natures (defined by the field idx):
pk  lft rgt idx             value
 1   1  30  2783     20402.710000
 2   2   3  2783    -13885.670000
 3   4   5   131        78.547946
 4   6   7  2783      -586.810000
 5   8   9     1         0.013421
 6  10  11  2783     -1777.630000
 7  12  13   873     10791.237266
 8  14  15  2783       -72.510000
 9  16  17  1697       128.626280
10  18  19  2783      -600.430000
11  20  21  1168         6.949175
12  22  23  2783     -2479.080000
13  24  25  1758       853.502787
14  26  27  2783     -1000.580000
15  28  29  3794      3172.428304
16  31  40  2783       615.940000
17  32  33  2783      -615.940000
18  34  39     1         0.040390
19  35  36     1        -0.000152
20  37  38     1        -0.000300
21  41  42  2783      3012.050000

Since these transactions are nested in a particular way where a child node can be of different nature than its parent (representing a sort of "transformation" along the way), I am having a hard time trying to fetch the "balance" for each parent node in their own nature:
prnt_pk      idx            aggr
1           2783        0.000000
3            131       78.547946
5              1        0.013421
7            873    10791.237266
9           1697      128.626280
11          1168        6.949175
13          1758      853.502787
15          3794     3172.428304
16          2783        0.000000
18             1        0.039938
21          2783     3012.050000

How could I achieve this desired output given the original table? I have tried doing something along the lines described in this question, but all I have managed to get was a single parent node and all its children with the same idx, which I could do more easily in my case with SELECT * FROM table WHERE idx = 2783 or similar.
In case it helps, every parent node has a positive value, no node with negative values are allowed to have children.

These are the steps to obtain the desired result:

A cross self-join to obtain every single combination of primary keys possible (this squares the number of records);
Apply the nested set filter (commented below) to remove all combinations of nodes that are not related (e.g., pk 1 and 15);
Assert that only combinations of the same nature are being fetched and aggregated, by equalling the idx from both node and crosst;
Since we are aggregating from crosst.idx, that means it will sum the "balance" for all nodes, including those that are negative (and thus orphans); the third filter ensures we are only taking the total balance starting from original positive parents.

SELECT
    crosst.pk AS prnt_pk,
    crosst.idx,
    SUM(node.value) AS aggr
FROM
    nsm_table AS node,
    nsm_table AS crosst
WHERE 1=1
    AND node.lft BETWEEN crosst.lft AND crosst.rgt --NSM filter for containers
    AND node.idx = crosst.idx
    AND crosst.value > 0
GROUP BY crosst.pk, crosst.idx
HAVING SUM(node.value) >= 0

And the output is exactly as the desired one:
prnt_pk      idx            aggr
1           2783        0.000000
3            131       78.547946
5              1        0.013421
7            873    10791.237266
9           1697      128.626280
11          1168        6.949175
13          1758      853.502787
15          3794     3172.428304
16          2783        0.000000
18             1        0.039938
21          2783     3012.050000


Redesigning a multi-instance MySQL server for performance and resiliency I've got a single MySQL server which currently runs N separate MySQL instances on, which are rapidly growing beyond the capabilities of that box.

Is it possible to have an active-active MySQL cluster, with each individual node in the cluster hosting a single instance, but with the capability of having one or more of the instances failing over to another node if the primary hosting node fails?

Example

Current layout

Connection - Server1:{instance port}

Server - Server1

Instance - Instance 1; Instance 2; Instance N

Drive -    LocalDiskA; LocalDiskB; LocalDiskC

Proposed layout

Connection - Connection:{instance port}

Server -   Node1;        Node2;         NodeN

Instance - Instance 1;   Instance 2;    Instance N

Drive -    SanDrive A;   SanDrive B;    SanDrive C

Failover capability

Connection - Connection:{instance port}

Server -   Node1;                    Node2;         XNodeNX (offline)

Instance - Instance 1, Instance N;   Instance 2;

Drive -    SanDrive A, SanDrive C;   SanDrive B;

I've read a few articles on MySQL clustering, and most of what I've seen just refers to being able to load balance a single instance across multiple servers, which unfortunately doesn't meet my requirements (I need to keep identically structured databases separate, as each instance is environment specific).

I know this is possible with SQL Server, but don't know enough about MySQL so any help you can give would be greatly appreciated.

I would recommend that you set up separate clusters for different database instances. You should be able to set up multiple clusters sharing some or all of the hosts, just make sure each cluster has its own ports defined.


Blocking drop table for specific schema using trigger I'm trying to prevent anyone from dropping tables on a specific schema "public" unless the user is an rds_superuser but the function I wrote is guarding on all schemas.

CREATE OR REPLACE FUNCTION guard_tables()
RETURNS event_trigger
LANGUAGE plpgsql
AS $$
BEGIN
IF TG_TABLE_SCHEMA = 'public' AND (SELECT COUNT(*) FROM pg_roles WHERE pg_has_role(CURRENT_USER, oid, 'member') AND rolname = 'rds_superuser') = 0 THEN
RAISE EXCEPTION 'command % is disabled for this table', tg_tag;
END IF;
END;
$$;

How do I get this to work?

TG_TABLE_SCHEMA is not set in event triggers.

You'll have to call pg_event_trigger_dropped_objects() to get information about the dropped objects. Among the result columns is schema_name, which contains the schema name of the affected object.


Syntax error in UNION ALL I have syntax error in UNION ALL line 8.. what is the mistake? I'm using mariadb, (Innodb)

SELECT
'Süre Bitiş Tarihi','Tc Kimlik No', 'Kullanıcı Soyadı, Adı','E-posta',
'Telefon','Diğer Telefon','Adres','Şehir','Posta Kodu','Ev Adresi',
'Ev Şehir','Ev Posta Kodu','Barkod', 'Eser Adı', 'Yazar', 'Yeri',
'Kütüphane Adı','Materyal türü','Yerel Yer Numarası','Basım Tarihi',
'Hedef kitle','Dil kodu'

UNION ALL (

SELECT
i.date_due, bo.cardnumber,CONCAT(bo.surname,',',bo.firstname),bo.email,
bo.phone, bo.mobile, CONCAT(bo.address,' ',bo.streetnumber,' ',bo.address2),
bo.city, bo.zipcode, CONCAT(bo.B_address,' ',bo.B_streetnumber,' ',bo.B_address2) ,bo.B_city,bo.B_zipcode , it.barcode,
b.title,b.author,it.location,br.branchname,itype.description,
it.itemcallnumber,b.copyrightdate,bitems.agerestriction,
EXTRACTVALUE(bitems.marcxml,'/record/datafield [\@tag=\"041\"] /subfield [\@code=\"a\"]')

FROM issues i
LEFT JOIN items it on i.itemnumber=it.itemnumber
LEFT JOIN biblioitems bitems on bitems.biblioitemnumber=it.biblioitemnumber
LEFT JOIN biblio b on b.biblionumber=it.biblionumber
LEFT JOIN itemtypes itype on it.itype=itype.itemtype
LEFT JOIN borrowers bo on bo.borrowernumber=i.borrowernumber
LEFT JOIN branches br on br.branchcode=bo.branchcode
where it.holdingbranch=110

UNION ALL (

SELECT
i.date_due, bo.cardnumber,CONCAT(bo.surname,', ',bo.firstname),
bo.email, bo.phone, bo.mobile, CONCAT(bo.address,' ',bo.streetnumber,
' ',bo.address2),bo.city, bo.zipcode,
CONCAT(bo.B_address,' ',bo.B_streetnumber,' ',bo.B_address2),
bo.B_city,bo.B_zipcode , it.barcode, b.title,b.author,it.location,
br.branchname,itype.description,it.itemcallnumber,b.copyrightdate,
bitems.agerestriction,
EXTRACTVALUE(bitems.marcxml,'/record/datafield [\@tag=\"041\"] /subfield [\@code=\"a\"]')
FROM old_issues i
LEFT JOIN items it on i.itemnumber=it.itemnumber
LEFT JOIN biblioitems bitems on bitems.biblioitemnumber=it.biblioitemnumber
LEFT JOIN biblio b on b.biblionumber=it.biblionumber
LEFT JOIN itemtypes itype on it.itype=itype.itemtype
LEFT JOIN borrowers bo on bo.borrowernumber=i.borrowernumber
LEFT JOIN branches br on br.branchcode=bo.branchcode
where it.holdingbranch=110
)
);

I don'T want this output

ef kitle | Dil kodu |
| Süre Bitiş Tarihi   | Tc Kimlik No | Kullanıcı Soyadı, Adı     | E-posta | Telefon | Diğer Telefon  | Adres | Şehir  | Posta Kodu | Ev Adresi | Ev Şehir  | Ev Posta Kodu | Barkod | Eser Adı  | Yazar | Yeri | Kütüphane Adı    | Materyal türü   | Yerel Yer Numarası  | Basım Tarihi  | Hedef kitle | Dil kodu |
| Süre Bitiş Tarihi   | Tc Kimlik No | Kullanıcı Soyadı, Adı     | E-posta | Telefon | Diğer Telefon  | Adres | Şehir  | Posta Kodu | Ev Adresi | Ev Şehir  | Ev Posta Kodu | Barkod | Eser Adı  | Yazar | Yeri | Kütüphane Adı    | Materyal türü   | Yerel Yer Numarası  | Basım Tarihi  | Hedef kitle | Dil kodu |
| Süre Bitiş Tarihi   | Tc Kimlik No | Kullanıcı Soyadı, Adı     | E-posta | Telefon | Diğer Telefon  | Adres | Şehir  | Posta Kodu | Ev Adresi | Ev Şehir  | Ev Posta Kodu | Barkod | Eser Adı  | Yazar | Yeri | Kütüphane Adı    | Materyal türü   | Yerel Yer Numarası  | Basım Tarihi  | Hedef kitle | Dil kodu |
| Süre Bitiş Tarihi   | Tc Kimlik No | Kullanıcı Soyadı, Adı     | E-posta | Telefon | Diğer Telefon  | Adres | Şehir  | Posta Kodu | Ev Adresi | Ev Şehir  | Ev Posta Kodu | Barkod | Eser Adı  | Yazar | Yeri | Kütüphane Adı    | Materyal türü   | Yerel Yer Numarası  | Basım Tarihi  | Hedef kitle | Dil kodu |

SELECT
'Süre Bitiş Tarihi','Tc Kimlik No', 'Kullanıcı Soyadı, Adı','E-posta',
'Telefon','Diğer Telefon','Adres','Şehir','Posta Kodu','Ev Adresi',
'Ev Şehir','Ev Posta Kodu','Barkod', 'Eser Adı', 'Yazar', 'Yeri',
'Kütüphane Adı','Materyal türü','Yerel Yer Numarası','Basım Tarihi',
'Hedef kitle','Dil kodu'
UNION ALL (
SELECT *

from(

SELECT
i.date_due, bo.cardnumber,CONCAT(bo.surname,',',bo.firstname),bo.email,
bo.phone, bo.mobile, CONCAT(bo.address,' ',bo.streetnumber,' ',bo.address2),
bo.city, bo.zipcode, CONCAT(bo.B_address,' ',bo.B_streetnumber,' ',bo.B_address2) ,bo.B_city,bo.B_zipcode , it.barcode,
b.title,b.author,it.location,br.branchname,itype.description,
it.itemcallnumber,b.copyrightdate,bitems.agerestriction,
EXTRACTVALUE(bitems.marcxml,'/record/datafield [\@tag=\"041\"] /subfield [\@code=\"a\"]')

FROM issues i
LEFT JOIN items it on i.itemnumber=it.itemnumber
LEFT JOIN biblioitems bitems on bitems.biblioitemnumber=it.biblioitemnumber
LEFT JOIN biblio b on b.biblionumber=it.biblionumber
LEFT JOIN itemtypes itype on it.itype=itype.itemtype
LEFT JOIN borrowers bo on bo.borrowernumber=i.borrowernumber
LEFT JOIN branches br on br.branchcode=bo.branchcode
where it.holdingbranch=110

UNION ALL (

SELECT
i.date_due, bo.cardnumber,CONCAT(bo.surname,', ',bo.firstname),
bo.email, bo.phone, bo.mobile, CONCAT(bo.address,' ',bo.streetnumber,
' ',bo.address2),bo.city, bo.zipcode,
CONCAT(bo.B_address,' ',bo.B_streetnumber,' ',bo.B_address2),
bo.B_city,bo.B_zipcode , it.barcode, b.title,b.author,it.location,
br.branchname,itype.description,it.itemcallnumber,b.copyrightdate,
bitems.agerestriction,
EXTRACTVALUE(bitems.marcxml,'/record/datafield [\@tag=\"041\"] /subfield [\@code=\"a\"]')
FROM old_issues i
LEFT JOIN items it on i.itemnumber=it.itemnumber
LEFT JOIN biblioitems bitems on bitems.biblioitemnumber=it.biblioitemnumber
LEFT JOIN biblio b on b.biblionumber=it.biblionumber
LEFT JOIN itemtypes itype on it.itype=itype.itemtype
LEFT JOIN borrowers bo on bo.borrowernumber=i.borrowernumber
LEFT JOIN branches br on br.branchcode=bo.branchcode
where it.holdingbranch=110
))temp);

You are missing from clause in line No 8.  After using Union all you just combine with any temp variable.


Retrieving data from inner join using LIMIT and OFFSET These are my two tables:
table1

qid[PK]     |gid[PK]  |abcd     | xyz  |    date
---------------+---------+---------+------+------------
 00001      |     qwe |      54 |    a | 1994-11-29
 00002      |     asd |       0 |    s | 1994-11-29
 00003      |     azx |      50 | 0.25 | 1994-11-27

table2

qid[PK]     | gid[PK] | user[PK]
------------+---------+--------
 00001      |     qwe | shreya
 00001      |     qwe | nagma
 00001      |     qwe | koena
 00001      |     qwe | paoli
 00002      |     asd | anushka
 00002      |     asd | angelina
 00003      |     azx | jolie
 00003      |     azx | scarlett
 00003      |     azx | sharon
 00003      |     azx | jeniffer

As you can see for each qid and gid of table1 there can be any number of rows in table2.

My requirement :
I want to retrieve all the users for the first 10 values of qid and gid from the offset.

My Query :

    select  * from table1 q inner join table2 a on q.qid=a.qid
 and q.gid=a.gid  order by q.date desc limit 10 offset ?

But this query will retrieve  10 rows from offset from the inner join, But I want all rows from table 2 for the 10 rows[offset] from table1.

How can this be achieved?

Use a subquery (as displayed) or CTE for that purpose:

SELECT *
FROM  (
   SELECT qid, gid
   FROM   table1
   ORDER  BY date DESC
   LIMIT  10
   OFFSET ?
   ) q
JOIN   table2 a USING (qid, gid)

USING (qid, gid) is just a shortcut for ON q.qid = a.qid AND q.gid = a.gid with the side effect that the two columns are only included once in the result.


SSRS Report joining data sets in BID 2008 How can we join to Data sets in SSRS? For example we have Dataset1 and Dataset2 and the common columns between them are c1 and c2. I know we can use lookup, but wondering if there is a shortcut like using variables to do that based on your experience?

Thank you.

Hi I am afraid that this is not possible in 2008. You can workaround it though by implementing the logic in the dataset itself or you can create sub-reports to achieve what you are after.

However, if you have the ability to use a 2008R2 instance you can indeed do what you are asking by using the Lookup functions. Here is more information on it:

LOOKUP:

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

LOOKUPSET:

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

I hope this helps you.


how to run limit query within condition in mysql I have a huge database around ~20,000,000, presently I am running a php script, which verifies if user info is 'valid or invalid'. There are several entries named 'invalid' in the database. Problem is how to make a limit on database to scan only valid entries. I am running google cloud to device messaging service which only allows push messages to 200,000 users per day. So, I want to make limit per query and automate via cron.

Presently query which is running on database is

for valid:

SELECT * FROM TNAME WHERE SETNO=1 & flag='valid' ORDER BY id;

for invalid:

SELECT * FROM TNAME WHERE SETNO=1 & flag='invalid' ORDER BY id;

Different numbers are assigned to setno corresponding to database rows.

From your question, let's assume your table is called TNAME

You should collect the ids that are valid

First create a table to hold IDs and a process status per ID

CREATE TABLE idlist
(
    id INT NOT NULL,
    processed INT NOT NULL DEFAULT 0,
    PRIMARY KEY (key),
    UNIQUE KEY processed (processed,key)
) ENGINE=MyISAM;

Next, preload load all new IDs

INSERT IGNORE INTO idlist (id) SELECT id FROM TNAME WHERE SETNO=1 AND flag='valid';

This will load ids that are valid. IDs that were already loaded are bypassed

Using this idlist, pickup the first 200,000 ids

CREATE TABLE idlist200K SELECT id FROM idlist WHERE processed=0 ORDER BY id LIMIT 200000;
ALTER TABLE idlist200K ADD PRIMARY KEY (id);

Process the 200K rows with an INNER JOIN with queries like this:

SELECT B.* FROM idlist200K A INNER JOIN TNAME B;

When done processing, mark the IDs as processed:

UPDATE idlist A INNER JOIN idlist200K B USING (id) SET A.processed = 1;
DROP TABLE idlist200K;

All you have to do from here is automate all these steps, except the initial table creation

INSERT IGNORE INTO idlist (id) SELECT id FROM TNAME WHERE SETNO=1 AND flag='valid';
CREATE TABLE idlist200K SELECT id FROM idlist WHERE processed=0 ORDER BY id LIMIT 200000;
ALTER TABLE idlist200K ADD PRIMARY KEY (id);
SELECT B.* FROM idlist200K A INNER JOIN TNAME B; -- Whatever processing
UPDATE idlist A INNER JOIN idlist200K B USING (id) SET A.processed = 1;
DROP TABLE idlist200K;

Give it a Try !!!


Maintenance Plan Wizard generates invalid date parameter in script for delete The generated SQL for my Maintenance Plan is

EXECUTE master.dbo.xp_delete_file 0,N'''',N'''',N''2019-01-02T10:44:21''

but the execution returns error message

  Msg 102, Level 15, State 1, Line 1 Incorrect syntax near '2019'.

It dislikes the date parameter! I think it is to do with the fact my server is in Australia not USA, and we view dates differently!

Any ideas how I overcome this bug?

It's not related to the date format, the generated T-SQL from Maintenance Plans doubles up the single quotes (') in the generated script. A double quote escapes a single quote inside quoted text.

For example:

SELECT 'Figure 1'

would return the value "Figure 1", whereas:

SELECT ''Figure 1''

Would throw an incorrect syntax error similar to yours.

When you extract the T-SQL for a Maintenance Plan task, you should copy it into a new query window and do a Find & Replace to replace '' with '. You may have to run it twice depending on the generated script.


Change Data Capture Use Case? I'm working on a custom application that needs to determine changes to a particular column, on particular rows in our database (MS SQL 2008R2).

When the row is created a created & modify timestamp are inserted. Anytime any of the columns change, the modified timestamp is updated.

I' need to be able to fetch all the records where one particular column was changed between the time my application last ran, and now. Further more, i really only care if this one particular column value changed from a "N" to a "Y".

I want to do all this, without altering the DB schema, or using triggers due to their overhead.

My research led me to Change Data Capture, would this be a good use case to use Change Data Capture?

Is it possible to limit the overhead on CDC to an item only changing to a specific value, my table has approx ~200k rows, with 300mb in index and 200mb in data, and rows are heavily modified, so i'm concerned about the amount of data that will be captured.

The only alternate approach that i am aware of, is to capture the parent event (row created) in a separate audit table, and then compare it to the original table each time the application runs to surmise the change, is there an advantage to doing it this, or is there any simpler way to do it?

Change Tracking would be a lighter weight solution to your needs, and more robust than using a time stamp (even if driven from CDC in the way you describe).You can enable tracking at the column level. The versioning number - used to determine what has changed since your last pass - is safe from race conditions. Crucially, it is issued on transaction completion. Homebrew solutions can end up using a value (e.g. timestamp) issued at another point in time, and not of sufficient precision to always determine one transaction from another. This leads to edge cases where the determination of change may not be correct.The following series of MS documents describe how to set up change tracking. https://docs.microsoft.com/en-us/sql/relational-databases/track-changes/about-change-tracking-sql-server(Although lightweight, it is still important to test performance using a volume of change representative of your production environment. There are parameters such as change retention period that can be adjusted to balance performance.)


Columns into rows and rows into columns I need to do this table transformation, but its little hard for me.

I have original table wich looks like this

and i need to transform the table into this

How can i do that ?

Here i prepared example database http://sqlfiddle.com/#!3/0f324

Thank you for helping.

select * from
(
select SubBrand,Month,Saless,Variable
 from
(select * from DataTable
)as p
unpivot (Saless for Month in(January,February)) as u
) as p
pivot
(
min(Saless) for  Variable in ([Sales],[Promotion])
)as PP
order by SubBrand,Month


EventID 1196 | SQL Cluster & FailoverClustering I have Pre-Staged the SQLCluster objects, since login had no Domain admin rights.
Later SQL Cluster installation completed successfully.(Also tested with failover and failback).It is working fine till date.

But I am receiving error in event log of failover cluster, that Cluster resources failed registration of one or more associated DNS name(s).

Can anybody explain its impact?

Require help to understand below error.

**
Cluster network name resource 'SQL Network Name (servername)' failed
registration of one or more associated DNS name(s) for the following reason:
DNS operation refused.
**

From How to fix Hyper-V Cluster error 1196: Cluster Name failed registration by Matthew Mombrea:

The issue, it turns out after days of forehead slapping, is that there were some additional external DNS servers specified on the NIC for that particular node - a remnant of its pre-cluster days. Your cluster node network interfaces should only have DNS records for your internal DNS server. While this didn't cause issues in most cases, for the purpose of cluster network name registration it was wreaking havoc.
First, check the NIC settings for each of your cluster nodes to make sure there are no external DNS records present. If there are, delete them. Next, ensure the A record for your cluster exists on your internal DNS servers in case they have been scavenged.


Create temp tables in Amazon RDS PostgreSQL read-replica I have dozens of legacy stored procedures which create temporary tables inside for collecting results for a read-only application.

I've created a read-replica of my PostgreSQL in Amazon RDS and tried to perform this procedures, but failed, as it doesn't allow to create even temporary tables in a read-only transaction.

Are any ways how to solve this issue with minimal efforts?

As the most easy way which I found to solve this issue - is wrapping all "temporary tables" into separate stored procedures, which return TABLE(...) type, and then use Common Table Expressions (https://www.postgresql.org/docs/9.1/static/queries-with.html).


Is it more efficient to store 0 and 1 for a binary value or just empty and 1 in MySQL Is it more efficient to store 0 and 1 for a binary value indicating whether a flag is up or down or just empty and then set 1 for rows where the flag is up. Using MySQL.

E.g.

Flag
0
1
1
0
0

Or

Flag
''
1
1
''
''

Performance wise

a statement could be made that inserting would be faster when the flag is down as the field doesn't need to be included in the insert clause.

On the other hand, selecting all records where the flag is down could be slower because in the end, somewhere you need to make a conversion from NULL to 0.

As any performance difference that might exist will not be noticable, let's call this a draw.

Storage wise

I'm operating here under the assumption that you are actually using the BOOL datatype. Likely the reasoning holds for other types as well but that should be looked at on a case by case basis.

The BOOL's datatype underlying storage is a TINYINT(1) wich makes up for 1 Byte. The storage requirement is the same for 0, 1 or NULL so you don't gain anything here.

Again a draw.

But that's all moot...

... because in the end, you should use what is most maintainable. Rest assured that being explicit about your values is much more maintainable than having to convert them back and forth everywhere you use that field.


How to set lowercase_table_name I've been trying to setup lower_case_table_names for ages in MySQL 8.0 without success. What is the procedure to set this up in 8.0?

In previous versions of mysql I was able to override the variable in /etc/mysql/mysql.cnf by adding the following lines:

[mysql]
lower_case_table_names = 1

But when I try to restart the server / service mysql does not restart.
In the past all mysql servers had this setting working fine I just don`t know how to do the same in 8.0.

The server variable lower_case_table_names is described in the relevant documentation page:

Identifier Case Sensitivity, where it also mentions:

  lower_case_table_names can only be configured when initializing the server. Changing the lower_case_table_names setting after the server is initialized is prohibited.

Some more details appear in the linked page about server variables:

  It is prohibited to start the server with a lower_case_table_names setting that is different from the setting used when the server was initialized. The restriction is necessary because collations used by various data dictionary table fields are based on the setting defined when the server is initialized, and restarting the server with a different setting would introduce inconsistencies with respect to how identifiers are ordered and compared.


Execute a query but not return results to client I am testing the performance a query and trying to fix a parameter sniffing issue.  Based on the parameter sometimes it returns 3 rows and sometimes it returns over 5K rows.  I want to test the impact of the change I am proposing on execution time.  But since the number of rows returned is so wide the execution time is skewed as it takes into account the time taken by the client to consume the rows.  I want to remove the time taken to return the rows and jut measure the actual execution time on the server.

I have looked at SET FMTONLY ON but it looks like it does not run the query.
I also tried dumping the rows into a table/temptable but that adds cost to the estimation and causes the plan to change in a direction that is not what I want.

Any other way we can achieve this?

Cheers

You can use SET STATISTICS TIME ON to get the exact amount of time the server spends executing the query.

And Client Statistics can help determine the amount of time the client spends consuming it.

From Brent Ozar:
https://www.brentozar.com/archive/2012/12/sql-server-management-studio-include-client-statistics-button/

  Some really valuable information can be found here, such as the number
  of INSERT, DELETE, UPDATE, and SELECT statements – think of how useful
  that can be when tuning a complex stored procedure. You can see the
  number of rows affected or returned, and the number of transactions
  executed.

  The network statistics lets you see how much traffic is moving from
  your client to the server and back.

  The time statistics tells you how much time was spent processing on
  the client versus how much time was spent waiting for the server.
  These figures are in milliseconds.

I've often used it as a way to compare stored procedure performance executed with different parameters, especially useful when changing those parameters affects the volume of data processed and returned.

From SQL Server Management Studio

Once selected, every execution using the same session, will add a new column with performance results for that execution.

Some more info here as well: https://blog.sqlauthority.com/2009/10/01/sql-server-sql-server-management-studio-and-client-statistics/

Test using sysjobhistory

use msdb
go

set statistics time on

select * from dbo.sysjobhistory
go

From message tab:

(18 rows affected)

SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 3 ms.

From Client Statistics Tab

3 milliseconds to execute from SET STATISTICS TIME ON and 5 milliseconds Client processing time from the Client Statistics Tab.


Would using varchar(5000) be bad compared to varchar(255)? Since varchar anyways allocate space dynamically, my question is whether using varchar(255) be more efficient or save more space compared to using varchar(5000).  If yes, why?

Yes, varchar(5000) can be worse than varchar(255) if all values will fit into the latter. The reason is that SQL Server will estimate data size and, in turn, memory grants based on the declared (not actual) size of the columns in a table. When you have varchar(5000), it will assume that every value is 2,500 characters long, and reserve memory based on that.

Here's a demo from my recent GroupBy presentation on bad habits that makes it easy to prove for yourself (requires SQL Server 2016 for some of the sys.dm_exec_query_stats output columns, but should still be provable with SET STATISTICS TIME ON or other tools on earlier versions); it shows larger memory and longer runtimes for the same query against the same data - the only difference is the declared size of the columns:

-- create three tables with different column sizes
CREATE TABLE dbo.t1(a nvarchar(32),   b nvarchar(32),   c nvarchar(32),   d nvarchar(32));
CREATE TABLE dbo.t2(a nvarchar(4000), b nvarchar(4000), c nvarchar(4000), d nvarchar(4000));
CREATE TABLE dbo.t3(a nvarchar(max),  b nvarchar(max),  c nvarchar(max),  d nvarchar(max));
GO -- that's important

-- Method of sample data pop : irrelevant and unimportant.
INSERT dbo.t1(a,b,c,d)
  SELECT TOP (5000) LEFT(name,1), RIGHT(name,1), ABS(column_id/10), ABS(column_id%10)
  FROM sys.all_columns ORDER BY object_id;
GO 100
INSERT dbo.t2(a,b,c,d) SELECT a,b,c,d FROM dbo.t1;
INSERT dbo.t3(a,b,c,d) SELECT a,b,c,d FROM dbo.t1;
GO

-- no "primed the cache in advance" tricks
DBCC FREEPROCCACHE WITH NO_INFOMSGS;
DBCC DROPCLEANBUFFERS WITH NO_INFOMSGS;
GO

-- Redundancy in query doesn't matter! Just has to create need for sorts etc.
GO
SELECT DISTINCT a,b,c,d, DENSE_RANK() OVER (PARTITION BY b,c ORDER BY d DESC)
FROM dbo.t1 GROUP BY a,b,c,d ORDER BY c,a DESC;
GO
SELECT DISTINCT a,b,c,d, DENSE_RANK() OVER (PARTITION BY b,c ORDER BY d DESC)
FROM dbo.t2 GROUP BY a,b,c,d ORDER BY c,a DESC;
GO
SELECT DISTINCT a,b,c,d, DENSE_RANK() OVER (PARTITION BY b,c ORDER BY d DESC)
FROM dbo.t3 GROUP BY a,b,c,d ORDER BY c,a DESC;
GO

SELECT [table] = N'...' + SUBSTRING(t.[text], CHARINDEX(N'FROM ', t.[text]), 12) + N'...',
s.last_dop, s.last_elapsed_time, s.last_grant_kb, s.max_ideal_grant_kb
FROM sys.dm_exec_query_stats AS s CROSS APPLY sys.dm_exec_sql_text(s.sql_handle) AS t
WHERE t.[text] LIKE N'%dbo.'+N't[1-3]%' ORDER BY t.[text];

So, yes, right-size your columns, please.

Also, I re-ran the tests with varchar(32), varchar(255), varchar(5000), varchar(8000), and varchar(max). Similar results (click to enlarge), though differences between 32 and 255, and between 5,000 and 8,000, were negligible:

Here's another test with the TOP (5000) change for the more fully reproducible test I was being incessantly badgered about (click to enlarge):

So even with 5,000 rows instead of 10,000 rows (and there are 5,000+ rows in sys.all_columns at least as far back as SQL Server 2008 R2), a relatively linear progression is observed - even with the same data, the larger the defined size of the column, the more memory and time are required to satisfy the exact same query (even if it does have a meaningless DISTINCT).


Automatic Translation when Converting Unicode to non-Unicode / NVARCHAR to VARCHAR Unicode code point 9619 is a character called "Dark shade": ▓ (http://unicode-table.com/en/search/?q=9619).

Using the SQL_Latin1_General_CP1_CI_AS collation and 1252 code page, I would expect that casting / converting that Unicode character to non-Unicode data type would result in a question mark (?) as code page 1252 does not appear to contain this character and this appears to be SQL Server's behavior when conversion can not take place.

So my question is: why does SQL Server convert this character to an ASCII code 166 which is "Pipe, Broken vertical bar": ¦ ?

SELECT NCHAR(9619), CAST(NCHAR(9619) AS CHAR(1)), ASCII(CAST(NCHAR(9619) AS CHAR(1)))

Why does SQL convert Unicode 9619 to ASCII code 166?

SQL Server is not employing any special custom logic here; it is using standard operating system services to perform the conversion.

Specifically, the SQL Server type and expression service (sqlTsEs) calls into OS routine WideCharToMultiByte in kernel32.dll. SQL Server sets the input parameters to WideCharToMultiByte such that the routine performs a 'quick translation'. This is faster than requesting a specific default character be used when no direct translation exists.

The quick translation relies on the target code page to perform a best-fit mapping for any unmatched characters, as mentioned in the link Martin Smith provided in a comment to the question:

  Best-fit strategies vary for different code pages, and they are not documented in detail.

When the input parameters are set for a quick translation, WideCharToMultiByte calls OS service GetMBNoDefault (source). Inspecting the SQL Server call stack when performing the conversion specified in the question confirms this: