SQL Server 2012 database 3 mirroring questions

You can use SQL Server Express for the witness.

You only need a single license so long as you never use the mirror server for anything other than a mirror, and only failover at most once in 90 days.

The names of the servers, and the names of the SQL instances are irrelevant.

Access can somewhat automatically use the failover server as long as the connection string for the linked tables uses the SQL Native Client and the correct syntax:

Failover Partner=myMirrorServerAddress;

See http://www.connectionstrings.com for more info.


mysql dummy where check to utilize multi column index

It is doubtful that the optimizer would choose to use this index, and if it did, MySQL would need to do a complete index scan in order to match the second entry. This would be less efficient than a table scan.

Add an index on col2.

EDIT 7/11/2012

It occurs to me that an analogy might help you understand why this can't possibly work.

Consider that an index is sort of like a phone book from ye olde times.  Phone books have a compound index on (lastname, firstname).

This is a phone book:

CREATE TABLE phonebook (
  lastname varchar(255) NOT NULL,
  firstname varchar(255) NOT NULL,
  phone varchar(11) NOT NULL,
  PRIMARY KEY (lastname,firstname)
)

If you want to find my number in the book, you would find Brown, Aaron in the book which would then have the data that you want (my number).  If you want to find all people with the last name 'Brown', it's also easy because they are organized by lastname - you just find the first 'Brown' entry and keep going until you are no longer looking at "Brown's."  So, this works:

SELECT phone FROM phonebook WHERE lastname = 'Brown';

or even

SELECT phone FROM phonebook WHERE lastname LIKE 'B%';

What if you want to find everyone named "Aaron?"  You can't use the index that the phone book provides - you have to look at every single entry in the book in order to get all the Aarons.

-- this will still have to scan every record in the table
SELECT phone FROM phonebook WHERE lastname >= 'A' AND firstname = 'Aaron';

In your example, col1 is lastname and col2 is firstname.  You are trying to trick MySQL into using a phone book index by saying "Give me all the people with the first name of Aaron that also have a last name that starts with a letter."  Knowing that the last name starts with a letter doesn't help you find all the Aarons, just like knowing that col1 > 0 doesn't help.  You still have to look at every entry in the table.


Without sample data and desired results, can't be too confident, but it sounds like you just need to add a check for the existence of assistants on the same cases:

AND EXISTS (SELECT 1 FROM Links.Case_Person AS cp
  WHERE cp.Case_ID = LC.Case_ID AND cp.Role_ID = 22);

Based on the comments below it seems your description doesn't quite match your stated requirements above. Perhaps, then:

SELECT *,
  HasAssistant = CASE WHEN LC2.Case_ID IS NOT NULL THEN 1 ELSE 0 END
FROM Info.Case IC
INNER JOIN Links.Case_Person LC
ON IC.Case_ID = LC.Case_ID
INNER JOIN Info.Person IP
ON LC.Person_ID = IP.Person_ID
LEFT OUTER JOIN Links.Case_Person AS LC2
ON LC.Case_ID = LC2.Case_ID
AND LC2.Role_ID = 22
WHERE LC.Person_ID = 123 AND LC.Role_ID IN (5, 11);

(Note that if it is possible for a case to have more than one assistant, that case will produce multiple rows here. There are ways around that, but your specific requirements are still not clear to me.)


How does the MongoDB query optimizer evaluate candidate plans?

Does the query optimizer use collections statistics ?

The query optimizer does not (as at MongoDB 4.0) use collection statistics.

Plan evaluation is based on comparing candidate plans for a given query shape to see which returns the first batch of results (by default 101 documents) with the least amount of overall "work". The works score is a proxy for different effort involved in query stages (index key comparisons, fetching documents, etc). If multiple plans perform identical work during evaluation, there are some small tie-breaking bonuses that can help choose a plan to cache (for example, if a plan is a covered query or does not require an in-memory sort).

The winning plan will be cached for future queries matching the same query shape, but will be re-evaluated if performance declines over time or there are other relevant changes (for example, indexes are added/removed). See Query Plans in the MongoDB documentation for a general overview.

For best outcomes on common queries you generally want to have an ideal index for your query shape rather than several competing candidate indexes. An ideal index would include the fields in your query and support using the index to sort query results (if you have sort criteria). For a worked example, the Optimizing MongoDB Compound Indexes blog post is a helpful read (note: this uses explain output from an older version of MongoDB, but the general concepts are still applicable).

If adding an ideal index isn't possible and the query optimizer isn't able to deterministically chose the best index given your data, you could consider using an index hint to override the query optimization process and force a specific index to be used. Index hints should be used sparingly as they can force suboptimal choices and will ignore any future indexes added that might be more suitable.

  When we run explain("allPlansExecution"), we get a lot of information about each evaluated plans. But, among following, what are the selection criteria in order of importance to determine the winning plan ? totalKeysExamined, totalDocsExamined, executionTimeMillis, nReturned, memUsage, other... ?

From a query scoring perspective, the  works value for each query stage is the key indicator. Metrics like totalKeysExamined and totalDocsExamined are useful for a quick comparison of efficiency for the number of results returned (nReturned). Ideally the ratio of totalKeysExamined to nReturned would be 1:1 (a single index lookup for every result), but some complex queries will necessarily have to examine more values. Other execution metrics will vary based on the query stage, but these can provide insight into calculation of the works value for that stage. For a more detailed walkthrough, I recommend the Reading the .explain() Output presentation from Charlie Swanson.

If you want to better understand the execution stats for a specific query, I suggest posting a new question on DBA StackExchange with your specific MongoDB server version and output of explain("allPlansExecution"). You may also find it helpful to investigate using a UI tool like MongoDB Compass, which includes a Visual Tree of explain output which is easier to explore than raw JSON.


When can a runtime exception occur during commit?

I posted the same question to Oracle community and the answer was deferred constraints.


SQL Replication - Distribution database backlog of commands for one table

When it comes to replication you have options on how you want to initialize transactional replication. You can do that with a snapshot or from a backup. Only focusing on these two methods as they are the most common.

You may want to consider initializing from a backup to help save time, and to allow your distributor to only have to keep records of what was changed for the time it takes to restore the database.

Microsoft Article that explains the concept

MSSQL Tips article specific for SQL 2008


Grouping results and filtering duplicates by the row's date

Because you have other columns that you're not aggregating (name) but are eliminating rows based on the date column (latest record only), using GROUP BY is not ideal.
You can use windowing functions to find the specific record based on the grouping (code) and return only those records.
Example:
WITH CTE AS
(
  SELECT code
    , name
    , date
    , ROW_NUMBER() OVER (PARTITION BY code ORDER BY date DESC) AS [RowNumber]
  FROM Records
)

SELECT *
FROM CTE
WHERE RowNumber = 1

You can see a working example here. Basically it is using a CTE and the ROW_NUMBER function to assign a row number to each row within the code groups in a date descending order. This means you can simply select each row where the ROW_NUMBER value is 1.


Efficiency : Query Over Multiple Colomns vs Query over PK

Since no specific database is given, the below advice is general:

Most database engines try to limit the number of rows they have to look at as quickly as possible. Since both of your queries involve the primary key of you table, that will be used as the first basis for limiting the rows.

Obviously, a small amount of additional work is involved in the further limiting required in your first query, to only include the row in the result set if the Owner or Recipient match a specific value. If the check in your IF statement is done within the database (before, say, returning data to a calling application), then the performance is probably similar.

If the IF check is being done in an application, then you're sending data that may not be needed to the application. If it's as easy to check the data in the database as it is in the application, then I would generally filter on the database side, to minimize the amount of unnecessary data that needs to be sent to the application.

For one row, the difference is probably negligible. However, if you were trying to locate all rows in Wishlist that have the current user as Owner or Recipient and were added in the past 3 weeks, the difference would probably be very big.

Also: In most databases, applying an IF check like that outside the query requires that the data be stored in a staging table of some sort (ideally a temporary table) so a secondary query to filter using the additional criteria can be run, or by using a cursor to check the data row by row.

There are cases where using an intermediate table of some sort can greatly benefit a query. The DB engine may need much longer to figure out the best execution plan for very complex query, and may in fact have to go with a "best so far" plan sue to time constraints on the planning process. This can result in a sub-optimal query plan. Using a two-step process, where each query involved is simpler, can allow the best plan to be located more easily, and thus provide results faster than a single query. Note, however, that this will generally involve writing the intermediate results to disk, and writing to disk is one of the most costly operations most DBMSes perform.

Using some form of cursor, however, can be even worse. All SQL-based DBMSes I know of run best when treating rows as a "bag" of data, and processing them in bulk. In almost every case I can imagine, using a query that can apply a condition to all possible rows at once will have better results than checking the rows individually in a loop.

There are cases where that's appropriate. DBMSes so some thing better than others. Complex string processing is generally considered to be something most databases systems do relatively poorly. If you need to match on a complicated regular expression, and your DBMS doesn't have any native tools to handle regular expressions (or any easily-used add-ins that operate at native speeds), then sending the possible matches to your application and filtering based on the complex regular expression may be much faster (and easier) than cobbling together a series of comparable checks in a WHERE clause (see above notes on very complicated queries).

Another answer notes that MS SQL Server(and possibly other DBMSes) have some sort of row-level security. In the MS SQL Server case, note that this capability restricts access based on the SQL Server logins and users accessing the server. If you are looking at direct querying of the database by users, or if every user in your system has their own unique database-level login, then this could be of use to you. If, however, your application has its own internal users table, and needs to use that to control access (as would be true of most customer-facing applications, like the amazon.com website), then you probably need to manage that yourself.


SQL server moved, now can't use as linked server

I'm assuming that you are using a "sql server login" for the vendor's server, and you are using that when creating the linked server.

When you use a linked server, a query running on your server connects to the vendor's server. If a firewall blocks your local server from connecting to the vendor's server, your connection attempt will fail.

If connecting directly from your workstation to the vendor's server you go through a different firewall or no firewall, then the connection may succeed.

This scenario matches the behavior you describe. My usual tests would be:
1. Can I ping from my server to the vendor's server? Both by IP and by hostname? ping usually gets through firewalls.
2. Can I connect using SSMS from my server (using an RDP session) to the vendor's server? Both by IP and by hostname?

If you can ping OK but not connect with SSMS, this usually indicates that the ports for SQL Server probably need to be opened on the firewall.

In short, check all of the firewalls involved. That would include any software firewall on your server or on the vendor's server, or any hardware firewall between them.

In these situations, ping and traceroute are your friends. Ping and traceroute may help you locate the IP of the router that doesn't send your packets on to the vendor's server.


Clean Code - On which side to place the key

Yes so your approach / the second approach is generally more favorable and normalized because it leads to less redundant addresses. But you lose the flexibility of a user having multiple addresses this way, because of this one-to-one approach. If that's not a requirement of the logical schema, then your approach makes more sense.
Alternatively to support the normalization and flexibility best of both worlds, you can add one more table called userAddress that is a bridge / linking table between the two, supporting a many-to-many relationship by storing the user_id and address_id fields as pairs of all addresses for each user.
So in short:

Approach 1: More flexibility, but less normalization / more data redundancy is possible
Approach 2: More normalization / less data redundancy, less flexibility
Approach 3 (Bridge Table): Flexibility and normalization so reduced data redundancy

Also to your second question regarding Triggers, they aren't all implemented exactly the same way across all modern RDBMS (because the ANSI-compliant spec for them is very minimal relative to the other features of SQL). So for a generalized answer (since we're not talking about a specific database system in the context of this question), it depends on the database system if you'd be able to replicate the desired behavior via a Trigger. (I see you personally use PostgreSQL, so I believe in that context, then it would be possible with a Trigger.)


How to normalize table with multiple similar columns (day1, day2, day3...)?

The better way is the original design with 1 column for Date, From and To.
If data is large size than you can partition the table on the basis of date(month wise or day wise).

ID, Date, From, To, Location


view current session roles for another session

SESSION_ROLES view shows list of roles assigned after you makes session in instance as those roles granted to your connected users. you can view other users granted roles using below:

select   * from   dba_role_privs;

If you want to know list of roles of particular user, use below query:

select   * from   dba_role_privs where grantee='USER_NAME';

Please note that you must have DBA privilege to view above. and you can't see list of roles using currently connected others session. you can only see what role the can be use after getting connected.


In the end i used the OPTION(RECOMPILE) at the end it worked for the scenario I was facing, but bear in mind that this won't work for parameters.


How can I implement data encryption in SQL Express for a VB6 application?

I hope it is permitted to mention our third party product, NetLib Encryptionizer. It provides Transparent Data Encryption for all versions and editions of SQL Server including Express.


Mathematically compare multiple 3 rows sets of table and record result in new column

There are probably more efficient ways to do this, but here's my stab at it.
For each group of 3, I'm basically capturing the value of Result for Row1, Row2 and Row3 - sort of like a poor-man's PIVOT.  I then use a CASE expression to evaluate the differences between Row1, Row2 and Row3 to generate the 'Up' or 'Down' values and concatenate those values for each row

SET NOCOUNT ON
declare @Table table (Col1 Char(1), Col2 Char(2), Col3 Char(2), [Year] int, [Year+n] int, Result int)
insert into @Table
    (Col1, Col2, Col3, [Year], [Year+n], Result)
    values
        ('A', 'UK', '1Z', 2015, 2, 30),
        ('A', 'UK', '1Z', 2015, 1, 20),
        ('A', 'UK', '1Z', 2015, 0, 10),
        ('A', 'UK', '1Y', 2015, 2, 40),
        ('A', 'UK', '1Y', 2015, 1, 35),
        ('A', 'UK', '1Y', 2015, 0, 40)
;
WITH _CTE as
(
SELECT Col1
    ,Col2
    ,Col3
    ,[YEAR]
    ,[Year+n]
    ,Result
    ,CASE
        WHEN
            ROW_NUMBER() OVER(PARTITION BY Col1, Col2, Col3
                                ORDER by Col1, Col2, Col3, [Year], [Year+n]) = 1 THEN
                Result
        WHEN
            ROW_NUMBER() OVER(PARTITION BY Col1, Col2, Col3
                            ORDER by Col1, Col2, Col3, [Year], [Year+n]) = 2 THEN
                LAG(Result,1) OVER (PARTITION BY Col1, Col2, Col3
                            ORDER by Col1, Col2, Col3, [Year], [Year+n] )

        WHEN
            ROW_NUMBER() OVER(PARTITION BY Col1, Col2, Col3
                                ORDER by Col1, Col2, Col3, [Year], [Year+n]) = 3 THEN
                LAG(Result,2) OVER (PARTITION BY Col1, Col2, Col3
                            ORDER by Col1, Col2, Col3, [Year], [Year+n] )
        END AS Row1Result
    ,CASE
        WHEN
            ROW_NUMBER() OVER(PARTITION BY Col1, Col2, Col3
                                ORDER by Col1, Col2, Col3, [Year], [Year+n]) = 1 THEN
                LEAD(Result,1) OVER (PARTITION BY Col1, Col2, Col3
                            ORDER by Col1, Col2, Col3, [Year], [Year+n] )
        WHEN
            ROW_NUMBER() OVER(PARTITION BY Col1, Col2, Col3
                            ORDER by Col1, Col2, Col3, [Year], [Year+n]) = 2 THEN
                Result
        WHEN
            ROW_NUMBER() OVER(PARTITION BY Col1, Col2, Col3
                                ORDER by Col1, Col2, Col3, [Year], [Year+n]) = 3 THEN
                LAG(Result,1) OVER (PARTITION BY Col1, Col2, Col3
                            ORDER by Col1, Col2, Col3, [Year], [Year+n] )
                            END AS Row2Result
    ,CASE
        WHEN
            ROW_NUMBER() OVER(PARTITION BY Col1, Col2, Col3
                                ORDER by Col1, Col2, Col3, [Year], [Year+n]) = 1 THEN
                LEAD(Result,2) OVER (PARTITION BY Col1, Col2, Col3
                            ORDER by Col1, Col2, Col3, [Year], [Year+n] )
        WHEN
            ROW_NUMBER() OVER(PARTITION BY Col1, Col2, Col3
                            ORDER by Col1, Col2, Col3, [Year], [Year+n]) = 2 THEN
                LEAD(Result,1) OVER (PARTITION BY Col1, Col2, Col3
                            ORDER by Col1, Col2, Col3, [Year], [Year+n] )
        WHEN
            ROW_NUMBER() OVER(PARTITION BY Col1, Col2, Col3
                                ORDER by Col1, Col2, Col3, [Year], [Year+n]) = 3 THEN
                Result
        END AS Row3Result

from @Table
)
SELECT Col1
    ,Col2
    ,col3
    ,[year]
    ,[Year+n]
    ,Result
    ,Row1Result     --For debug/display purposes only - remove from final answer
    ,Row2Result     --For debug/display purposes only - remove from final answer
    ,Row3Result     --For debug/display purposes only - remove from final answer
    ,CASE
        WHEN Row2Result > Row1Result
            THEN 'Up'
        WHEN Row2Result = Row1Result
            THEN 'NoChg'
        ELSE 'Down'
        END + CASE
        WHEN Row3Result > Row2Result
            THEN 'Up'
        WHEN Row3Result = Row2Result
            THEN 'NoChg'
        ELSE 'Down'
        END AS Classification
FROM _CTE
ORDER BY Col1
    ,Col2
    ,col3 DESC

| Col1 | Col2 | col3 | year | Year+n | Result | Row1Result | Row2Result | Row3Result | Classification |
|------|------|------|------|--------|--------|------------|------------|------------|----------------|
| A    | UK   | 1Z   | 2015 | 0      | 10     | 10         | 20         | 30         | UpUp           |
| A    | UK   | 1Z   | 2015 | 1      | 20     | 10         | 20         | 30         | UpUp           |
| A    | UK   | 1Z   | 2015 | 2      | 30     | 10         | 20         | 30         | UpUp           |
| A    | UK   | 1Y   | 2015 | 0      | 40     | 40         | 35         | 40         | DownUp         |
| A    | UK   | 1Y   | 2015 | 1      | 35     | 40         | 35         | 40         | DownUp         |
| A    | UK   | 1Y   | 2015 | 2      | 40     | 40         | 35         | 40         | DownUp         |

After removing the extra 'RowResult' columns that I used to validate my results, I get the results you're looking for.

| Col1 | Col2 | col3 | year | Year+n | Result | Classification |
|------|------|------|------|--------|--------|----------------|
| A    | UK   | 1Z   | 2015 | 0      | 10     | UpUp           |
| A    | UK   | 1Z   | 2015 | 1      | 20     | UpUp           |
| A    | UK   | 1Z   | 2015 | 2      | 30     | UpUp           |
| A    | UK   | 1Y   | 2015 | 0      | 40     | DownUp         |
| A    | UK   | 1Y   | 2015 | 1      | 35     | DownUp         |
| A    | UK   | 1Y   | 2015 | 2      | 40     | DownUp         |


mysqlworkbench after cmd mysql startup went wrong

It sounds like you have two installations of MySQL: one installed normally and one installed through XAMPP. When starting MySQL through XAMPP it will start the one it installed, when started through the command line it installs the one that is installed natively. They both bind to the same port (so you can only have one active at a time) so MySQL Workbench will display the contents of the MySQL server that is started.

Because you have two installations of MySQL you should either only use one of them (and possibly remove the other) or only start the one that you need to access.


Is there a way to tell if a GUID column contains sequential or traditional / random GUID's?


tuning feed item's table query with multiple condition

I dont know about feed_id but your timestamp column by definition have probably many differents values. I will advise you to try to create a simple index on timestamp column.

Do you have "disparate" values too on feed_id column?

For me you should take your attention on the timestamp column (WHERE + ORDER BY). Maybe it'll not a index problem, maybe your MySQL instance have some difficulties to sort the result (bad temp table management) so you can try without ORDER BY clause to see its impact.

Let me know.

Max.


Compare table content from different PostgreSQL 9.3 DBs

For such a small amount of data getting the whole lot and performing a text comparison as you are is probably the way I would go too.

For larger data and/or data on remote sites that could be far too cumbersome so you might instead want to compare a checksum of the data in each table with something like:

SELECT ENCODE(
              DIGEST(
                     STRING_AGG(
                                COALESCE(<col1>,'NULL')
                               || COALESCE(<col2>,'NULL')
                               || COALESCE(<col3>,'NULL')
                               ,','
                     ),'sha512'
              ),'base64'
       ) AS table_checksum
FROM <table> ORDER BY <pk>

That way you are reading all the data but only transmitting the resulting checksum.

Also if your databases are on the same machine (unlikely in the case of dev/stg/prod, but...) you could use a cross-database query (see this question) to scan for differences.

In all the above cases you are going to be scanning the whole table(s) in both places.

CAVEAT: I'm a SQL Server person generally and I've translated something I've done there to postgres, the above syntax may need a little work!


Plan A:  Change the schema to have two date columns.  Then have the code INSERT a row when the item is "assigned" and UPDATE the other column when it is "unasigned".  This essentially eliminates the entire problem.  And it cuts in half the number of rows needed.
Plan B:  Split the effort.  Using the schema you currently have, SELECT ... ORDER BY ... to get the pairs of rows adjacent.  Then have the application code finish the task by remembering the contents of the first row of each pair ("assigned") just long enough to blend it with the next row ("unassigned").


Purge mysql log bins older than two weeks automatically

I am glad you found your own answer.

I am sure you found out you can add this to my.cnf

[mysqld]
expire_logs_days = 14

You do not have to restart mysql. You login as root@localhost and run this:

mysql> SET GLOBAL expire_logs_days = 14;

To trigger the auto rotate immediately, run

mysql> FLUSH LOGS;

If you want to zap binary logs from the mysql console that is older that two weeks, use the PURGE BINARY LOGS BEFORE syntax. For example, here is a query to give you midnight two weeks ago from today's date:

SELECT DATE(NOW() - INTERVAL 2 WEEK) + INTERVAL 0 SECOND;

Just apply that query to PURGE BINARY LOGS BEFORE

PURGE BINARY LOGS BEFORE (DATE(NOW() - INTERVAL 2 WEEK) + INTERVAL 0 SECOND);


Can a contained database SQL User be mapped to a remote user in a linked server?

FYI, per Microsoft Support, this is expected behavior - a contained database user cannot be configured as the Local Login for a local-to-remote security mapping in a linked server.

thanks

Martin


Slow Connection to Azure SQL Database using SQL Server Authentication

Here is the Microsoft explanation of this issue:
https://techcommunity.microsoft.com/t5/azure-database-support-blog/lesson-learned-132-delays-connecting-to-azure-sql-database-from/ba-p/1502030
They describe two types of authenticated users.  Ones with instance LOGIN's and ones that have only access to a single database ("contained" database users).
The problem with the slow SSMS connectivity only affects the first type of authentication (instance LOGIN's when they have access to a database but have no rights to the "master" database).  If you are able to switch to "contained" database users then you may avoid the problem altogether.


How does one scale SQL Server 2008 or 2012?

SQL Server doesn't scale out as such. It scales up.

There are 3 areas to do this, subject to edition limitations

CPU cores
RAM
Storage

And of course, use a higher edition eg Enterprise

SQL Server doesn't shard and any such solution (you can research MySQL sharding solutions) adds complexity and overhead to a system.

Scaling up one server (+ standby nodes/mirror) is usually quite straightforward with RAM, SSDs, more disk volumes to spread IO, separate drives for tempdb and logs etc

Also, if you find SQL Server is CPU bound then it's usually poor design and/or indexes and/or poorly written queries unless you have a massive load.


There is no way to store text in the definition of an index.

You can use Extended Properties to store descriptive information, however it won't be automatically displayed when you "script" the object.

USE tempdb;

CREATE TABLE dbo.t1
(
    ID INT NOT NULL
);

CREATE INDEX IX_t1
ON dbo.t1(ID);

/*
    This will add a comment named "Comments" to the IX_t1 index
*/
EXEC sys.sp_addextendedproperty @name=N'Comments'
    , @value=N'This is a test index to store comments'
    , @level0type = N'SCHEMA'
    , @level0name = N'dbo'
    , @level1type = N'TABLE'
    , @level1name = N't1'
    , @level2type = N'INDEX'
    , @level2name = N'IX_t1';

/*
    This displays all comments associated with indices in the current database
*/
SELECT SchemaName = s.name
    , ObjectName = o.name
    , IndexName = i.name
    , PropertyName = xp.name
    , PropertyValue = xp.value
FROM sys.schemas s
    INNER JOIN sys.objects o ON s.schema_id = o.schema_id
    LEFT JOIN sys.indexes i ON o.object_id = i.object_id
CROSS APPLY sys.fn_listextendedproperty(NULL, N'SCHEMA', s.name, N'TABLE',
    o.name, CASE WHEN i.name IS NULL THEN NULL ELSE N'INDEX' END, i.name) xp
ORDER BY s.name
    , o.name
    , xp.name;

Results of above query:

/*
    This provides the ability to delete Extended Properties
*/
EXEC sys.sp_dropextendedproperty @name = N'Comments'
    , @level0type = N'SCHEMA'
    , @level0name = N'dbo'
    , @level1type = N'TABLE'
    , @level1name = N't1'
    , @level2type = N'INDEX'
    , @level2name = N'IX_t1';

/*
    Clean up my test bed
*/
DROP TABLE dbo.t1;

The best-practices solution for this problem is to store object definitions inside a version-control system.


Group by and average from related tables

Are you looking for something along the lines of:

SELECT r.name as Region,
    avg(conc.ticket_price) as AverageCost
FROM Region r
INNER JOIN Country_Region cr
    ON cr.region_id = r.id
INNER JOIN Concerts c
    ON c.country_id = cr.country_id
GROUP BY r.name

This should not be a slow query at all - especially if the id fields are all indexed.

I recommend reading up on MySQL Aggregate Functions which use Group By from here: http://dev.mysql.com/doc/refman/5.0/en/group-by-functions.html


How to query all rows of a table into single json Postgresql?

Migrating a comment to an answer.

  I think you are looking for json_object_agg not json_agg – a_horse_with_no_name 2 hours ago

Links

JSON Functions and Operators


OK, this was a nightmare but we figured but we resolved the problem. We were able to remove the servers using the slonik DROP NODE command. For a while the slony services was segfaulting repeatedly. We had to DROP NODE on all the slaves and then add and subscribe them again. This caused the tables to be dropped and then it ran a copy set to restore the tables. It is working again. We were very hesitant to run DROP NODE on these due to this being a destructive operation. However the replication servers are running as expected again.

I am so looking forward to turning off this service permanently.


This is how I deploy them in my environment.
In SSMS, view Registered Servers, create a local server group called Staging and put all the Staging servers in there.
Right click on the Staging group and select New Query. This will open a new query window connected to all the servers in the group. Status bar will say Connected xx/yy where xx is the number of connected servers and yy is the total number of servers.
In the Query Window, paste the sp_Blitz code and execute it.
This applies the proc to all the Staging servers at once.
Repeat the process for the other sp_Blitz procs.
Repeat the process for the Production group of servers.
It only takes a few minutes to deploy all the sp_Blitz procs to a large number of servers this way.


How to know what database user I'm using in SQL Server?

But I don't know what user I'm currently using

CURRENT_USER will return the current username. See https://docs.microsoft.com/en-us/sql/t-sql/functions/current-user-transact-sql for more detail, and links to other similar functions.

As you can see there are a couple of ways to get the current username:
SELECT CURRENT_USER, USER_NAME(), USER_NAME(USER_ID())
should return your name three times.

  How can I change users depending on the person who's using the database server?

I think you are confusing database users with server logins, which is easy to do. You have explicitly created those users WITHOUT LOGIN. To let someone login as a specific account you need to create a login then create a database user for that login. Something like:

USE [master]
GO
CREATE LOGIN [NewLoginName] WITH PASSWORD=N'PasswordForLogin'
GO
USE [YourDatabaseName]
GO
CREATE USER [NewUserName] FOR LOGIN [NewLoginName] WITH DEFAULT_SCHEMA=[dbo]
GO

in SSMS. There are numerous other options when creating logins (for things like password policy and so forth) and users. See here and here for MS's documentation.


Allocation Unit ID calculation is incorrect?

This seems identical to a case discussed in the comments to Paul's post. He indicated the cause is that the page is no longer allocated (notice metadata IndexId = 1, but m_indexid = 0).
Decoding the allocation unit ID using Paul's script:
DECLARE @alloc BIGINT = 406903719657472;
DECLARE @index BIGINT;

SELECT @index =
    CONVERT (BIGINT,
        CONVERT (FLOAT, @alloc)
            * (1 / POWER (2.0, 48)) -- right shift, reciprocal of left shift
    );
SELECT
    CONVERT (BIGINT,
        CONVERT (FLOAT, @alloc - (@index * CONVERT (BIGINT, POWER (2.0, 48))))
            * (1 / POWER (2.0, 16)) -- right shift, reciprocal of left shift
    ) AS [m_objId],
    @index AS [m_indexId];

Result:

This matches the m_objid in your DBCC PAGE output.
Updated question
The calculation is correct if an index ID of 1 is used, again using the formula in Paul's post:
SELECT allocation_id =
    1 * CONVERT (BIGINT, POWER (2.0, 48)) | -- index id
    1913890731 * CONVERT (BIGINT, POWER (2.0, 16)); -- object id

So your actual question seems not to be about the allocation unit calculation at all; rather you are asking why DBCC PAGE shows  m_indexId (AllocUnitId.idInd) = 0 when the page is allocated and associated with index 1 - as shown in the DBCC IND output.
That, I don't know offhand. What I do know is that the calculation works both ways for me, testing on SQL Server 2012 build 6518. Perhaps there is something about your SQL Server version or table that means AllocUnitId.idInd is not being reported correctly. Without specific, reproducible details, you might have to ask Paul himself (though he's currently on holiday).
It's probably some edge case. For example, this post says that there may be differences in m_indexId if the database was upgraded from SQL Server 2000. Try rebuilding the table, that oftens sorts these sorts of things out.


SQL Server many-to-many relationship, row reuse?

The below sample structure illustrates how you can do the TasksTags table most efficiently.  The Tasks table enforces unique task names.  The Tags table enforces unique tag names.  The TasksTags table joins these together allowing any combination of Tasks and Tags.

USE tempdb;
CREATE TABLE dbo.Tasks
(
    TaskID INT NOT NULL CONSTRAINT PK_Tasks PRIMARY KEY CLUSTERED IDENTITY(1,1)
    , TaskName VARCHAR(255) NOT NULL CONSTRAINT UQ_Tasks_TaskName UNIQUE
);
CREATE TABLE dbo.Tags
(
    TagID INT NOT NULL CONSTRAINT PK_Tags PRIMARY KEY CLUSTERED IDENTITY(1,1)
    , TagName VARCHAR(255) NOT NULL CONSTRAINT UQ_Tags_TagName UNIQUE
);
CREATE TABLE dbo.TasksTags
(
    TaskID INT NOT NULL
    , TagID INT NOT NULL
    , CONSTRAINT PK_TasksTags PRIMARY KEY CLUSTERED (TaskID, TagID)
);
INSERT INTO dbo.Tasks (TaskName) VALUES ('Task1');
INSERT INTO dbo.Tasks (TaskName) VALUES ('Task2');
INSERT INTO dbo.Tasks (TaskName) VALUES ('Task3');

INSERT INTO dbo.Tags (TagName) VALUES ('MyTag1');
INSERT INTO dbo.Tags (TagName) VALUES ('MyTag2');
INSERT INTO dbo.Tags (TagName) VALUES ('MyTag3');

INSERT INTO dbo.TasksTags (TaskID, TagID) VALUES (1,2);
INSERT INTO dbo.TasksTags (TaskID, TagID) VALUES (1,3);
INSERT INTO dbo.TasksTags (TaskID, TagID) VALUES (1,1);
INSERT INTO dbo.TasksTags (TaskID, TagID) VALUES (2,2);
INSERT INTO dbo.TasksTags (TaskID, TagID) VALUES (2,3);
INSERT INTO dbo.TasksTags (TaskID, TagID) VALUES (3,2);
INSERT INTO dbo.TasksTags (TaskID, TagID) VALUES (3,1);

SELECT TK.TaskName, TG.TagName
FROM dbo.TasksTags TT
    INNER JOIN dbo.Tasks TK ON TT.TaskID = TK.TaskID
    INNER JOIN dbo.Tags TG ON TG.TagID = TT.TagID;

EDIT

The following allows a list of tags to be associated with more than one task.  You could blend this with the prior structure to create a totally customizable list of tags for each task.

USE tempdb;
CREATE TABLE dbo.Tasks
(
    TaskID INT NOT NULL CONSTRAINT PK_Tasks PRIMARY KEY CLUSTERED IDENTITY(1,1)
    , TaskName VARCHAR(255) NOT NULL CONSTRAINT UQ_Tasks_TaskName UNIQUE
);
CREATE TABLE dbo.Tags
(
    TagID INT NOT NULL CONSTRAINT PK_Tags PRIMARY KEY CLUSTERED IDENTITY(1,1)
    , TagName VARCHAR(255) NOT NULL CONSTRAINT UQ_Tags_TagName UNIQUE
);
CREATE TABLE dbo.TagList
(
    TagListID INT NOT NULL CONSTRAINT PK_TagList PRIMARY KEY CLUSTERED IDENTITY(1,1)
    , TagListName VARCHAR(255) NOT NULL CONSTRAINT UQ_TagList_Name UNIQUE
);
CREATE TABLE dbo.TagListTags
(
    TagListID INT NOT NULL CONSTRAINT FK_TagListTags_TagListID FOREIGN KEY REFERENCES dbo.TagList(TagListID)
    , TagID INT NOT NULL CONSTRAINT FK_TagListTags_TagID FOREIGN KEY REFERENCES dbo.Tags(TagID)
    , CONSTRAINT PK_TasksTags PRIMARY KEY CLUSTERED (TagListID, TagID)
);
CREATE TABLE dbo.TasksTagList
(
    TaskID INT NOT NULL CONSTRAINT FK_TasksTagList_TaskID FOREIGN KEY REFERENCES dbo.Tasks(TaskID)
    , TagListID INT NOT NULL CONSTRAINT FK_TasksTagList_TagListID FOREIGN KEY REFERENCES dbo.TagList(TagListID)
    , CONSTRAINT PK_TasksTagList PRIMARY KEY CLUSTERED (TaskID, TagListID)
);

INSERT INTO dbo.Tasks (TaskName) VALUES ('Task1');
INSERT INTO dbo.Tasks (TaskName) VALUES ('Task2');
INSERT INTO dbo.Tasks (TaskName) VALUES ('Task3');

INSERT INTO dbo.Tags (TagName) VALUES ('MyTag1');
INSERT INTO dbo.Tags (TagName) VALUES ('MyTag2');
INSERT INTO dbo.Tags (TagName) VALUES ('MyTag3');

INSERT INTO dbo.TagList (TagListName) VALUES ('Tag List 1');
INSERT INTO dbo.TagList (TagListName) VALUES ('Tag List 2');

INSERT INTO dbo.TagListTags (TagListID, TagID) VALUES (1, 1);
INSERT INTO dbo.TagListTags (TagListID, TagID) VALUES (1, 2);
INSERT INTO dbo.TagListTags (TagListID, TagID) VALUES (1, 3);
INSERT INTO dbo.TagListTags (TagListID, TagID) VALUES (2, 1);
INSERT INTO dbo.TagListTags (TagListID, TagID) VALUES (2, 3);

INSERT INTO dbo.TasksTagList (TaskID, TagListID) VALUES (1, 1);
INSERT INTO dbo.TasksTagList (TaskID, TagListID) VALUES (2, 1);
INSERT INTO dbo.TasksTagList (TaskID, TagListID) VALUES (2, 2);

SELECT Tasks.TaskName, Tags.TagName
FROM dbo.TasksTagList TTL
    INNER JOIN dbo.TagListTags TLT ON TTL.TagListID = TLT.TagListID
    INNER JOIN dbo.Tags ON TLT.TagID = Tags.TagID
    INNER JOIN dbo.Tasks ON TTL.TaskID = Tasks.TaskID;


Are there any tables that I can get all Oracle supported system privileges and object privileges?

Perhaps you are looking for SYSTEM_PRIVILEGE_MAP?


SQL Server Agent 2012 Job Schedule Report

If you expand the SQL Server Agent in SSMS and double click the Job Activity Monitor you should see what you need.


Mariadb concurrent insert not working for MYISAM tables

Face it, MyISAM survives only because it uses table locks for almost every kind of write.  Even a SELECT can get caught up in the middle.
The obscure exception has to do with multiple INSERT that are inserting into a table with AUTO_INCREMENT PRIMARY KEY (and, I suspect, no other UNIQUE keys).  In that case, they can play around at the "end" of the table with less locking.
Switch to InnoDB.
If your goal is to ingest lots of data from lots of sources, here's a technique:  http://mysql.rjweb.org/doc.php/staging_table


How to Backup a Log-shipped Mirror Database without breaking log-shipping

It is not possible to take a backup of a secondary log-shipped DB without bringing it online.  I believe to take a backup with log shipping, the secondary DB has to be brought online WITH RECOVERY as Standby/Read-Only won't allow the DB to be backed up.

Other sources that confirm:

https://social.msdn.microsoft.com/Forums/sqlserver/en-US/41d80415-80bc-4821-85b1-762ce9ea72f1/how-to-backup-the-log-shipping-secondary-database?forum=sqldatabaseengine

Can I backup (Full) the Log-Shipping Secondary Database?


Can a TRUNCATE or DROP table ever be very time consuming?

MyISAM -- DROP is like a file delete.  TRUNCATE performs the C file action of removing all the data but leaving the file in existence.
InnoDB with its own tablespace (cf innodb_file_per_table) -- like a file delete
InnoDB without its own tablespace -- lots of blocks to free up; this could take time.

A file drop can be fast or not so fast, depending on the Operating system.  Some OS's run around freeing up the blocks right then and there; you have to wait.  And, because it is an OS operation, this may interfere with virtually anything else.  What OS?

"Minutes" seems long.  How many minutes?  How many GB?


Restoring data into a replicated table after failed purge process

I'm going to assume the table in question is a replication publisher, not a replication subscriber (since you shouldn't be changing data in table that has been replicated from another source).

While you can just delete and then re-insert all the fixed data on a replicated table, if it is large enough that's going to generate tons of commands to replicate over to the subscriber, which (in environments I've worked in) may take hours or days to sync across.

So it is usually much easier to:

Remove the table from replication
Fix the table data, via bulk inserts or updates or SSIS or however you want
Re-add the table to replication
Do not re-initialize the subscription (you just want to snapshot that one table, not the whole database)
Run the snapshot job. It will push a snapshot of just the newly re-added table.
Run the distribution job

One further note: if you have already clogged up your replication with 100 million pending delete/insert/update commands, you can still execute these same 6 steps. The moment you remove the table from replication, all those pending records for that table will disappear from the queue, this usually happens very quickly and will cause a bogged-down subscriber to spring back to life.


how to grant a user permission to create databases?

Try this command

GRANT CREATE ON *.* TO 'user'@'host';

For further information, please see MySQL Documentation on GRANT syntax


Want to remove global_name from DB links (Oracle 11g)

To remove DB_DOMAIN from DB_LINKS.

ALTER SYSTEM SET global_names = false SCOPE = SPFILE;
ALTER SYSTEM SET db_domain = '' SCOPE = SPFILE;
UPDATE GLOBAL_NAME SET GLOBAL_NAME = [Name without db_domain];

then restart, DB and Listener.

However, I still do not know how to use this with domain.


Creating indexes on AutoId and time column

For history tables such as this one, it's often appropriate to create the clustered index with a date field as the leading column, and have the primary key index nonclustered.

In this case, since you know EventTime is non-unique, you can add the AutoId column to the key to make it unique (instead of relying on a uniquifier).

This is how the clustered index would be created (after first dropping the existing primary key constraint):

CREATE UNIQUE CLUSTERED INDEX IX_Events_EventTime_AutoId
    ON [Events](EventTime, AutoId);

And then recreate the primary key:

ALTER TABLE [Events] WITH CHECK
    ADD CONSTRAINT PK_Events
        PRIMARY KEY NONCLUSTERED(AutoId);

This setup allows for efficient date-range-based queries like the examples you've provided, and still doesn't create too much fragmentation to the base table as the clustering key is generally ever-increasing.


Options and best practices for Multiple Result Sets in Postgres11

The third solution is the best one if you want to save round trips and want to use prepared statements.

If you use cursors, you'll get a round trip per fetch.
When sending multiple statements in one call, you cannot have prepared statements.
You cannot use a stored procedure.
Putting two result sets into one with a join: that way lies madness.

Is is good to avoid excessive client-server round trips, but trying too hard to optimize can result in the opposite.


If you can modify the configuration of the slave, then the solution would be to fairly straightforward: turn on log_slave_updates on the slave, which will result in the writing everything changed on the slave via replication events into the slave's own binlog, which you could then "sniff" and decode every event as it occurs, by connecting to the slave as "your" master and reading from the replication stream that it generates.

Further, changing the slave's binlog_format to ROW will cause the slave to translate any statement-based events from the master so that they will be written to the slave's binlog (and replicated to you) as row-based events, which are dramatically simpler to process from the replication stream.

Once you have that, there are numerous projects in various languages that can spoof a slave connection and decode a replication stream, although in my case, I ended up writing my own parser because I wanted to genuinely understand the low-level nuts and bolts of the implementation well enough that I could thoroughly troubleshoot it as well as extend it... because with each major version iteration from MySQL Server 5.1 → 5.5 → 5.6 (I don't have anything older in my networks), new capabilities in replication have been introduced that will break older code that fails to understand the new structures.  This, incidentally, is why you always upgrade the slave first, when doing version upgrades, since new slaves understand everything an old master can send, but not the other way around.  Some of the open source projects that accomplish this will not work with newer versions of MySQL or will require non-default configurations, because of these changes.

I also wanted support for the compressed client/server protocol, which can significantly reduce the number of bytes sent over the wire in replication events, but which didn't often seem to be as much of a priority to some of the other projects.

Sniffing the replication stream has been very useful in my operation, since I really hate polling.  The one caveat I would point out that you might not expect is the fact that foreign key cascade events are not explicitly replicated, ever.  MySQL itself is apparently completely unaware when InnoDB performs foreign key operations on the child tables as a result of updates or deletes on the parent table.  I never thought about precisely which "layer" was doing this before, but it makes sense... if foreign keys weren't happening strictly internal to InnoDB, then there's no reason why MyISAM wouldn't have them by now... and there would also be no reason why foreign keys cascades couldn't activate triggers, but that too makes sense in this light, since triggers happen at the MySQL layer, not the storage engine layer... and this also explains why cascade actions are not reflected in the "rows affected" count.  The rows changing on the parent table are replicated, and on a normal slave, the foreign key cascades are handled internally by InnoDB magic... but that's the only unexpected thing I've encountered in the last couple of months of using this in live (back-end, not end-user-facing) systems.


Using the database engine tuning advisor - how do I generate the scripts for the recommendations?

Check the View Tuning Output:

  If you want to save all of the Transact-SQL scripts that create or drop all database objects in this recommendation into one script file, click Save Recommendations on the Actions menu.

As always review and test the recommendations before blindly applying them to your PROD environment.

I would highly suggest to look at more sane tools like sp_BlitzIndex from folks at BrentOzar.com


How to use a list in a list in report builder?

In my humble opinion, it depends on the complexity of the details view.

If I'm showing a sample list of values in each view then I'd rather use a List inside a List, inside a List.

For example:

[+] Customer Id + Customer Name
    [+] DeviCe Id + Device Name
        |------------|-----------------|
        | Job Id Job | Job Description |
        |------------|-----------------|
        |------------|-----------------|
        |------------|-----------------|

But, if the details view have a more complex structure, like graphics + tables or matrix, then I use a subreport.


My issue was that I misunderstood how logins work in SQL. I was using the USER name and the LOGIN password instead of the LOGIN name and the LOGIN password. It was a noob problem. Hopefully this saves someone else the time.


Thanks for such thorough questions, and sorry for all of confusion - commenting 1 year after initial answer is hard and now 3 years after.. initial thoughts fade and confuse me too, but I fear to edit the hole thing because I'm not working on storing notifications on backend right now and not sure if I make good judgements without practical application

I think at the time of writing:

Yes and no, notificationID was a foreign key, notificationObjectID was not. You do need another FK field to tie tables together. I blame my mongo experience in not being so clear about it :(
Yes and no, notification_object.object is vague because you can have it just as a string, or something complex (JSON, or FK). In my case it was just a noun.

So it all depends on how your notifications look. For simple case, you just want to link entire notification to some URL, like friends page - thats why having object (entityType) as a string is useful - you tie URLs to it.

Notification "you have 3 friend requests added" can be stored differently.

If you want to show them one at a time - you'll have 3 notifications, 3 notification objects (friend_request) and 3 entries in notification_change that link to specific friend user.

If you want to show one notification - you'll have 1 notification, 1/more objects and 3/more actions. So in this complex case, like «you have 3 friend requests from user A, user B, user C» - you use notificationObjectIDs for each user and have several links in your notification text.

Should you use 1 or 3 friend_request objects? It depends on

What is the object and what is the action. Is «article liked/commented»? Or is it «like/comment added» and object hierarchy gets linked only during display? Here is facebook distinguishing «photo commenting» from «user mentioning» which semantically seem to be very close — you have same photo, same actor but different notifications that could have been merged together

Can you remove a notification or do you need a history?
So if I send a friend request and then cancel it, or comment and then delete an article, like and then unlike something - should It show this history (as another action) to the end user as two different actions? Probably not.  In addition its techincally more complex - you need to search if there is an existing notification_object, add new notification_change to it (if not - add a new object) so that later I would need to search through notification_change to remove it. Instead I would just add another notification_object to the same notification and remove it if its gone with actions getting cascade-deleted.

On the other hand, there could be cases, where it might be useful to have grouping of actions where nothing gets wiped from the history.

I think thats why at the time of writing, 1:n relation between left and middle tables were done - so that you could group notifications not only by actors on the same entity (middle-right tables) but by several object/entities too.

But sure, you can simplify entire case and optimize storage by reversing left-middle relationship to n:1, so that you have per-user notifications generated for one event.

So that it would it look more like this..

╔═════════════╗      ╔═══════════════════╗      ╔════════════════════╗
║notification ║      ║notification_object║      ║notification_change ║
╟─────────────╢      ╟───────────────────╢      ╟────────────────────╢
║ID           ║←—n:1—║ID                 ║—1:n—→║ID                  ║
║noteObjFK    ║      ║entityType         ║      ║noteObjFK           ║
║viewerUserID ║      ║entityID           ║      ║actionOnEntity      ║
╚═════════════╝      ╚═══════════════════╝      ║actorUserID         ║
                                                ╚════════════════════╝

Hope this helped


Simplify:

SET @FQN = 'servername.database.dbo.newdatabase';

IF @table = 'OneTwoThree'
BEGIN
    SET @FQN = 'servername.database.dbo.onetwothree';
END

IF @table = 'FourFiveSix'
BEGIN
    SET @FQN = 'servername.database.dbo.fourfivesix';
END

Or:

SET @FQN = 'servername.database.dbo.' + CASE @table
  WHEN 'OneTwoThree' THEN 'onetwothree'
  WHEN 'FourFiveSix' THEN 'fourfixesix'
  ELSE 'newdatabase'
END;

Or even:

SET @FQN = 'servername.database.dbo.' + CASE
  WHEN @table IN ('OneTwoThree', 'FourFiveSix')
  THEN LOWER(@table) ELSE 'newdatabase'
END;

Also, you can do this whole thing without the cursor:

DECLARE @sql nvarchar(4000) = N'', -- always use nvarchar for dynamic SQL
        @FQN nvarchar(4000) = N'servername.database.dbo.';

CREATE TABLE #tablename (tablename varchar(1000));

INSERT #tablename(tablename) VALUES('OneTwoThree'),('FourFiveSix'),('FourOmegaSix');

SELECT @sql += N'
  SELECT TOP 1 * FROM ' + @FQN + CASE
  WHEN tablename IN ('OneTwoThree', 'FourFiveSix')
  THEN LOWER(tablename) ELSE N'newdatabase' END + N'; --' + tablename
FROM #tablename;

PRINT @sql;

DROP TABLE #tablename;

Output:

  SELECT TOP 1 * FROM servername.database.dbo.onetwothree; --OneTwoThree
  SELECT TOP 1 * FROM servername.database.dbo.fourfivesix; --FourFiveSix
  SELECT TOP 1 * FROM servername.database.dbo.newdatabase; --FourOmegaSix

(There's still a hidden loop there, but it's far less code to look at, maintain, etc.)


Cancel Query in remote ssms from ssms in my pc

Step 1:  Get query session id using below query

SELECT sqltext.TEXT,
req.session_id,----- SPID
req.status,
req.command,
req.cpu_time,
req.total_elapsed_time,req.start_time,db_name(database_id) DatabaseName
FROM sys.dm_exec_requests req
CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS sqltext
where [status] <> 'background'
and command<> 'TASK MANAGER'

Step 2: use kill command to kill the process execution

Kill(53) -- KIll (SPID)


Remove default value from NOT NULL column

The answer is to use the following (slightly odd) syntax:

ALTER TABLE items ALTER ordering DROP DEFAULT;


Could Not Be Accessed Value Of The For Loop Counter In PL/SQL

The problem is that the variable a that you declare in your declaration section is not the same as the variable a that is declared in the context of your loop.  You've declared two different variables named a with different scope.

If you run your block without a declaration section, you'll notice that it works even though you aren't declaring a variable a in the declaration section.  When you write a for loop, your counter is implicitly declared and is in scope only within the loop.  After the end loop, you can no longer reference the variable.

BEGIN
   FOR a IN REVERSE 10 .. 20 LOOP
      dbms_output.put_line('value of a: ' || a);
   END LOOP;
END;

If you want to explicitly declare the variable you are using in your loop and you want to be able to reference the variable outside the loop, you could write a while loop

declare
  i integer;
begin
  i := 20;
  while i >= 10
  loop
    dbms_output.put_line( 'In loop: ' || i );
    i := i - 1;
  end loop;
  dbms_output.put_line( 'Final value: ' || i );
end;


SSRS : How to give parameter values in URL Http requests?

Done.
Defining a parameter 'param2' whose values are integers and captions are strings, the point is the MDX query:
select {[Measures].[ID Distinct Count]} on COLUMNS,
  non empty filter({[DIM ANESTH].[ID].MEMBERS  },
  [DIM ANESTH].[ID].CURRENTMEMBER.MEMBERVALUE = @param2 ) on ROWS from BDD_PAT_ANESTH


There is another thing in play that hasn't entered your equations yet: HOT update. Related answers:

PostgreSQL unexplained table bloat
Are regular VACUUM ANALYZE still recommended under 9.1?
What is the meaning of n_live_tup and n_dead_tup in pg_stat_user_tables
How to speed up a slow UPDATE query

Setting FILLFACTOR to as low as 20 does seem excessive. It bloats the table to up to five times its size. If HOT updates work, you shouldn't have to go that low - normally.

There are exceptions: HOT updates can only reuse dead tuples from previous transactions, not from the same or concurrent ones. Therefore, heavy concurrent load or long transactions repeatedly updating the same rows can warrant such a low (or even lower) setting.

If you have big updates, changing large portions of the table at once, you might want to split them up in a couple of chunks, ideally only changing as many rows at once as fit locally on the data page. But that's hard to estimate and regulate.

Note that HOT updates only work when the changed columns are not involved in indexes in any way (neither as data nor as condition in a partial index). You might be blocking HOT updates with indexes on updated columns. If those are expendable, you might get better overall performance without them.

Finally, you can set autovacuum parameters per table. You could target heavily updated tables with aggressive settings allowing a somewhat tighter packing of rows than only FILLFACTOR 20.


Mysql Adding date/time stamp to file produced, or other method of automated file naming to keep resulting files unique

You may need to use shell script for this.
First write a script to do so.. a sample is as below
!/bin/bash

dto=date +%d'_'%b'_'%Y'_'%H_%M_%S
mysql -uroot -p dbname -e " select * from table_name into outfile 'file1_$dto' "

This will always give you unique names against your files.
Hope it helps.


Have to run same queries against many servers (30+), use CURSOR or LOOP

Connecting to other instances without Linked Servers is not convenient unless you use Registered Servers or Central Management Server.
If you have registered servers or CMS, you can execute the same query against all the instances in a group (or all registered servers) at once via SSMS (see the links above). But I don't know if you can output the results to a single table in one instance; you may be stuck with copy & paste to Excel.
If you're willing to go with Powershell, the dbatools module has a very convenient solution in Invoke-DbaQuery.
Invoke-DbaQuery -Query "select fields from table" -SqlInstance instance1,instance2,instance3 will return an object representing the results of the query plus a field with the corresponding instance name that each returned record came from.
From there, you can filter the results with Where-Object, or you can copy the results into a table in another instance with Write-DbaDbDataTable


Merge/Intersection between table and dataset - How to achieve?

Using the sample data:

DECLARE @T table
(
    Id integer IDENTITY NOT NULL PRIMARY KEY,
    [Hash] binary(16) NOT NULL INDEX h
);

INSERT @T ([Hash]) VALUES (0x31F777F0804D301936411E3ECD760859);
INSERT @T ([Hash]) VALUES (0xD64A593F3E9ACC972158D522A4289EA0);

DECLARE @S table
(
    [Hash] binary(16) NOT NULL
);

INSERT @S
    ([Hash])
VALUES
    (0x31F777F0804D301936411E3ECD760859),
    (0x31F777F0804D301936411E3ECD760859),
    (0x0C5A65264F92A543E7AAA06375349C06);

You could write it as a MERGE:

WITH
    T AS
    (
        SELECT
            T.[Hash],
            rn = ROW_NUMBER() OVER (
                PARTITION BY T.[Hash]
                ORDER BY T.[Hash], T.Id)
        FROM @T AS T
    ),
    S AS
    (
        SELECT DISTINCT
            S.[Hash],
            rn = ROW_NUMBER() OVER (
                PARTITION BY S.[Hash]
                ORDER BY S.[Hash])
        FROM @S AS S
    )
MERGE T
USING S
    ON S.[Hash] = T.[Hash]
    AND S.rn = T.rn
WHEN NOT MATCHED BY TARGET THEN INSERT ([Hash]) VALUES (S.[Hash])
WHEN NOT MATCHED BY SOURCE THEN DELETE;

db<>fiddle

But for performance reasons (as well as some bugs), I would normally write it as two separate statements:

WITH ToDelete AS
(
    SELECT
        T.*
    FROM @T AS T
    WHERE
        NOT EXISTS
        (
            SELECT
                S.*
            FROM @S AS S
            WHERE
                S.[Hash] = T.[Hash]
        )
)
DELETE ToDelete;

WITH ToInsert AS
(
    SELECT
        S.[Hash],
        rn = ROW_NUMBER() OVER (
            PARTITION BY S.[Hash]
            ORDER BY S.[Hash])
    FROM @S AS S
    EXCEPT
    SELECT
        T.[Hash],
        rn = ROW_NUMBER() OVER (
            PARTITION BY T.[Hash]
            ORDER BY T.[Hash], T.Id)
    FROM @T AS T
)
INSERT @T
    ([Hash])
SELECT
    ToInsert.[Hash]
FROM ToInsert;

db<>fiddle

You should have a unique index on the target on ([Hash], [Id]). Chances are you have this already, or equivalently an index on [Hash] and a unique (possibly clustered) index on [Id].

There may be other issues depending on what else was omitted from the question for brevity. In any case it should provide a couple of possible starting points for your own solution.


Are transactions in PostgreSQL via `psycopg2` per-cursor or per-connection?

Transactions are per-session, i.e. per-connection.

PostgreSQL doesn't support suspending and resuming transactions, so psycopg2 couldn't make them per-cursor unless it implicitly created new connections behind the scenes.

In practice I don't find psycopg2's cursors particularly useful. They can retain result sets if you're not using incremental fetch from the server, but I don't find them good for much else.

Why manually issue begin and commit though, rather than using the connection methods for them?


How can I get this code to work? It's in mysql, I keep getting an error 1822' failed to add foreign key constraint. Missing index for constraint'

From MySQL documentation:

  MySQL requires indexes on foreign keys and referenced keys so that
  foreign key checks can be fast and not require a table scan. In the
  referencing table, there must be an index where the foreign key
  columns are listed as the first columns in the same order. Such an
  index is created on the referencing table automatically if it does not
  exist. This index might be silently dropped later if you create
  another index that can be used to enforce the foreign key constraint.
  index_name, if given, is used as described previously.

You need to create corresponding indexes on EMPLOYEE table.


SQL: How do you create a table inside of a function

there is semi colon missing after RETURN 0 ... and also no BEGIN ...
... so a slight improvement, with no syntax error, would be :
CREATE FUNCTION _name_ ()
RETURNS
integer
AS
$$
BEGIN

CREATE TABLE inputCategories(
      Category varchar(255)
   );

DROP TABLE inputCategories;

RETURN 0 ;

END ;
$$ LANGUAGE plpgsql
   VOLATILE ;

(( VOLATILE indicates to postgres that the function could change the database !! It is the default for a function definition, so there is no need to write it, although it helps to put it in for clarity. VOLATILE also means that function 'can do anything'1, for example the returned result from the function could be different after each call (but not in this simple case) and so a VOLATILE function will be evaluated at each point in a query where it is called. In this case the function is VOLATILE because it has side effects in the database ... it creates and then drops a table. ))
postgres docs : Function Volatility Categories
Another way to define that function would be as an SQL language function :
CREATE FUNCTION _name_ ()
RETURNS
integer
AS
$$

CREATE TABLE inputCategories(
      Category varchar(255)
   );

DROP TABLE inputCategories;

SELECT 0;

$$
LANGUAGE SQL
VOLATILE ;

SQL language functions and PLPGSQL language functions are treated differently, and so depending on the nature of your real function, there may be an advantage to either way. If it is a more complex function requiring dynamically generated SQL, for example, then PLPGSQL is the way. Or some other supported langage like PL/Perl or PL/Python.
There is a discussion of the differences between PLPGSQL and SQL language functions (as well as a little about more recently implemented stored procedures) here :
dba.stackexchange.com : Function Performance


Is the order of attributes in a unique index still important in Oracle 12c?


How to use relationships to extract data via select

Try this

select used_for_lookup.id,
        used_for_lookup.datetime,
        fid_table.fid_important_string,
        did_table.did_important_string
 from used_for_lookup
 join fid_table on fid_table.id = used_for_lookup.fid
 join did_table on did_table.id = used_for_lookup.did
where datetime between '2014-10-09' and '2014-10-11';


odd OS memory consumption by two nearly-identical SQL 2008 R2 instances

My bet is #2 has Lock Pages in Memory privileges granted to the SQL Server service account. In this case, Task manager show only a minuscule memory usage (SQL Server uses a different Windows API in this mode). All is normal.

(As an aside, if you by "forcing at startup" mean setting min server memory, then that is not what happens. Min means that if SQL server climbs over it, then it won't go under it. It won't force that allocation at startup. )


Cannot install DB2 Express-C 10.1 on Mac OS X 10.7.5

I got it working by completely uninstalling db2 express-c (the db2_deinstall script failed so I manually deleted all directories that involved db2. :P ) .

Then I followed
https://www.ibm.com/developerworks/community/forums/html/topic?id=77777777-0000-0000-0000-000014927797

and it worked. I never ran db2_setup.


Will the foreign key constraint throw an error while restoring a database?

Depending on the type of backup, that can indeed happen. It's therefore recommended to disable foreign key checks before the restore, e.g. with

SET FOREIGN_KEY_CHECKS=0;

and re-enabling them afterwards with

SET FOREIGN_KEY_CHECKS=1;

This has another advantage: MySQL won't check the foreign key for every single imported record, but for all records at once. This will speed up your restore.

See Disabling foreign key checks on the command line on Stack Overflow.


Unable to get the DBMS Logs in Oracle SQl Developer

This works just fine:

SET SERVEROUTPUT ON
BEGIN
  DBMS_OUTPUT.PUT_LINE('Test);
END;

Then your output will look like this:

PL/SQL procedure successfully completed.
Test

Reference: DBMS_OUTPUT (Oracle | Docs)

If you want to get rid of the PL/SQL procedure successfully completed message, then you can turn if off with the command SET FEEDBACK OFF. The whole thing will then look like this:

SET FEEDBACK OFF
SET SERVEROUTPUT ON
BEGIN
  DBMS_OUTPUT.PUT_LINE('Test');
END;

Your output will then be:

Test

Reference: How do I Suppress “PL/SQL procedure successfully completed” message in sqlplus? (Stack Overflow)
Reference: SQLPlus® User's Guide and Reference (Oracle | Help Center)*


Oracle PL/SQL Trigger to update a outstanding field on one table when another table is updated with date

Using Oracle 12c, I've tried to re-trace your steps.  Your second trigger needs some modifications ...

Test setup:

create table movies (
  mid varchar2(64) primary key
, MovieName varchar2(64)
, outstanding number
);

create table rentals(
  sid number primary key
, mid varchar2(64) references movies(mid)
, rent_out date
, rent_in date
) ;

-- original trigger -> okay, works for INSERTs
create or replace
  trigger rentals_rent_trg
  after insert on rentals
  for each row
begin
  update Movies
  set outstanding = outstanding + 1
  where mid = :new.mid;
end;
/

Test data (inserts):

insert into movies (mid, moviename, outstanding)
values (1, 'Snatch', 0);
insert into movies (mid, moviename, outstanding)
values (2, 'Aliens', 0);
insert into movies (mid, moviename, outstanding)
values (3, 'Mars', 0);

-- check
select * from movies;

SQL> select * from movies;
MID  MOVIENAME  OUTSTANDING
1    Snatch     0
2    Aliens     0
3    Mars       0

-- table RENTALS: insert 42 rows for movies 1 and 2
begin
  for i in 1 .. 42
  loop
    insert into rentals (sid, mid, rent_out, rent_in)
    values (i+100,1,'19-AUG-2017',null);
    insert into rentals (sid, mid, rent_out, rent_in)
    values (i+200,2,'20-AUG-2017',null);
  end loop;
end;
/

-- checks
SQL> select count(*) from rentals;
COUNT(*)
84

SQL> select * from movies;
MID  MOVIENAME  OUTSTANDING
1    Snatch     42
2    Aliens     42
3    Mars       0

Let's use your second trigger, with minor modifications:

create or replace
  trigger rentals_return_trg
  after update of rent_in on rentals
  for each row
declare
  new_ number default 0 ;
begin
  new_ := :new.mid ;

  update Movies
  set outstanding = outstanding - 1
  where mid = new_;

end;
/

Trigger RENTALS_RETURN_TRG compiled

Now, if we UPDATE some of the rows in the "rentals" table, and have a look at "movies" and "rentals", respectively, everything seems to be working as you wanted it to work (- only the affected rows are SELECTed from rentals in this example).

-- "Aliens" and "Mars" not affected
begin
  for i in 120 .. 125
  loop
    update rentals
    set rent_in = '22-AUG-2017'
    where sid = i ;
  end loop;
end;
/

SQL> select * from rentals where rent_in is not null;
SID  MID  RENT_OUT   RENT_IN
120  1    19-AUG-17  22-AUG-17
121  1    19-AUG-17  22-AUG-17
122  1    19-AUG-17  22-AUG-17
123  1    19-AUG-17  22-AUG-17
124  1    19-AUG-17  22-AUG-17
125  1    19-AUG-17  22-AUG-17

SQL> select * from movies;
MID  MOVIENAME  OUTSTANDING
1    Snatch     36
2    Aliens     42
3    Mars       0


if you want to finish your database migration online， replication is necessary.

The common practice for database migration is building replication between your old mysql and new mysql(as slave). When the slave catch the master, switch your business to the mysql(slave).

if you just want to use binlog , you need to lock table during your migrating the database, or you will loss some data.


This is obviously opinion based, but I'd go ahead and partition the table at creation time.  I'd recommend RANGE RIGHT, as you apparently want to partition on a DATE and make sure you pre-define enough partitions before the minimum date you currently have and after the maximum date you currently have.  This will mitigate massive data movement when adding additional partitions in the future.

Check out Dan Guzman's Table Partitioning Best Practices


What's included in the default OSX 10.8 install of Postgres?

Well, the fact that you have psql (the client) installed, usually means that you have the PostgreSQL server installed as well. However, it does not mean that the server is running.

And, more importantly, OSX is an exception:

  PostgreSQL is the default database on Mac OS X Server as of version 10.7. The standard version of Mac OS X includes only the PostgreSQL commandline client utilities.

(from the documentation)


Steal ownership on database from user that no longer exists

The syntax you're using is attempting to change ownership of the object 'DestDB' within whatever database context you're currently in.  You need to specify that you're changing database ownership by using the class definition:

ALTER AUTHORIZATION ON DATABASE::[DestDB] TO [sa]


The main thing is to avoid the nested loop join that is caused by the "between" in the join condition.

In your example specifically, I would start by rewriting this as

SELECT
  visitor.id,
  visitor.ip,
  LAST_VALUE(zip IGNORE NULLS) OVER (
    ORDER BY COALESCE(geo_ip.start_ip, visitor.ip)
    ROWS UNBOUNDED PRECEDING
  ) as zip
FROM geo_ip
FULL OUTER JOIN visitor
ON visitor.ip=geo_ip.start_ip

Note that Redshift will only do a full outer join if considers it a merge joinable condition, which means you should set your distribution and sort key for both tables to be on visitor.ip and geo_ip.start_ip, respectively. If this is not an option, or too much of a bother, you can do this instead as a UNION ALL since you don't actually need the geo and visitor records to be on the same row:

SELECT
  visitor_id,
  visitor_ip,
  LAST_VALUE(zip IGNORE NULLS) OVER (
    ORDER BY ip, CASE WHEN zip IS NULL THEN 0 ELSE 1 END
    ROWS UNBOUNDED PRECEDING
  ) as zip
FROM (
  SELECT null as visitor_id, start_ip as ip, zip
  FROM geo_ip
  UNION ALL
  SELECT id as visitor_id, ip, null as zip
  FROM visitor
)

These would get rid of the nested loop join. You can still improve upon this for larger clusters by allowing the operation to be distributable.

For example, if you can partition your IP address table by the first octet (i.e, no row in the table has a start and end of the range with different first octets), you could add a partition to your window function, which should let the sorting be processed on separate nodes in your cluster:

SELECT
  visitor.id,
  visitor.ip,
  LAST_VALUE(zip IGNORE NULLS) OVER (
    PARTITION BY (COALESCE(geo_ip.start_ip, visitor.ip) / 1000000000 )::int
    ORDER BY COALESCE(geo_ip.start_ip, visitor.ip)
    ROWS UNBOUNDED PRECEDING
  ) as zip
FROM geo_ip
FULL OUTER JOIN visitor
ON visitor.ip=geo_ip.start_ip


MySQL - Can I have quick queries in a table with 200 million rows?

The best thing you could do to improve query performance for that query is to change your clustered index to an actual key and not an auto-incrementing row identifier.
Right now, all records for a given user are spread out across the pages of your table roughly based on order of insert.  This is not optimal as to locate these records you will have to:

Traverse a b-tree on a secondary index (not a huge deal)
Read all the pages with entries for that user, which could be a great deal more than just 1 or 2.

So assuming your key is (user_id, created_at)1, you would set your table up as:
CREATE TABLE `web_notifications`
(
  `user_id` int(10) unsigned NOT NULL,
  `text` varchar(255) NOT NULL,
  `url` varchar(255) NOT NULL,
  `seen_at` timestamp NULL DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  `updated_at` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  `deleted_at` timestamp NULL DEFAULT NULL,
  `operation` varchar(64) DEFAULT NULL,
  `start_year` smallint(5) unsigned NOT NULL,
  CONSTRAINT PK_webnotifications PRIMARY KEY (`user_id`,`created_at`),
  /* KEY `web_notification_users_seen_at_idx` (`user_id`,`seen_at`), */ /* This index would probably not be necessary or even utilzed with the new primary key */
  CONSTRAINT `fk_web_notification_users1` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION
)
ENGINE=InnoDB
DEFAULT CHARSET=utf8

This has the added benefits of:

Allowing the engine to directly access the rows via the clustered index
Saving a 8 bytes per row
Actual uniqueness
Potentially eliminating an additional index

However, there will be the need to additional maintenance, namely periodically rebuilding the table if performance degrades due to index fragmentation2.  Fragmentation may be managed by tweaking the fill_factor, but that will depend on the maturity of the table, the distribution of activity by user_id, and how long you retain data in the table3.
Other things to consider:

Don't do SELECT * - don't waste I/O on columns you don't need.
If you haven't already, wrap the query in a stored procedure.  Better from a security standpoint, and having the plan cached should save some compilation time.
You probably don't need deleted_at.  Upon delete, just change the value for updated_at and set a flag IsDeleted = 1.  It will be understood that the value for updated_at was the time the record was deleted.
operation looks like it might be something that has a limited number of values - consider creating a shorthand code for each of those and store that in a reference table.
If start_year is derived from another column, there is no need to persist it.  If it doesn't change depending on information in web_notifications consider moving it to another table.
You still may be limited by network I/O if you're returning a large amount of data, this is just the reality of being in the cloud.  But at least you'll be eliminating all other bottlenecks.

1 If there is an additional column required for uniqueness, just add it to the key.  Depending on the queries you run (and how many records each user has), it may or may not make sense to keep created_at as the last column in the index or the one immediately following user_id.
2 Or don't bother as Rick James mentions in the comments.  He has more MySQL experience so I'll defer to him.
3 Tweaking fill factor in other DBMSs with clustered indexes can reduce the rate at which a table fragments, but again I'll defer to Rick.


Do schema changes "break" Availability Groups or are they handled transparently?

Schema changes and data changes are essentially the same. It works like traditional mirroring today : what happened in the log on the primary happens on the secondary. Not everything that happens in Vegas has to stay in Vegas. :-)

Where you may want to be careful, is when you have an application that points to the primary, and you update that to match schema changes. But you may have a different application that points to the secondary (e.g. with read-only intent), and that app change would have to be synchronized as well.

Another potential gotcha is when your database that is part of an availability group has references to objects in other databases (e.g. a static lookup table that is stored in a utility database). If those change and the AG depends on those objects, you will have to push those changes manually. The same is true for jobs, server-level logins, linked servers etc. - anything that lives outside of the database and/or is not transactionable. Database users can get orphaned (contained users aside). I know this is probably obvious but wanted to list it explicitly for completeness.


Why doesn't SQL Server use my index in this SELECT ... WHERE?

Given the size of your table (~11k rows), I think it would be safe to assume that SQL Server estimated that the cost of performing a seek on the non-clustered index and then potentially multiple RID lookups was more expensive than performing a table scan.
There is some evidence to support this theory within the second query plan that you pasted. I would normally expect the Query Optimiser to suggest adding a covering index for your query as you have mentioned in your post. However, it did not. This to me suggests that SQL thinks that doing so would provide little or no improvement over a full table scan.
With all that being said, I am sure that if you added more rows to the table SQL Server may change its mind and ask you to add a covering index or start performing a seek + RID lookup as you expect. If you have Query Store enabled you can always keep an eye out for queries on this table that are causing problems - if it isn't causing you a problem, I wouldn't worry about it right now.


Should I delete disabled windows Active Directory accounts from SQL 2008?

The simplest scenario is to avoid managing individual AD accounts in SQL Server whenever possible. The most common solution is to use AD groups and provide the groups access to the correct SQL server database. Then when people join or leave the AD administrators control what their rights are within AD. This does assume that the AD team has a well defined process for managing accounts.
This was the solution suggested in the comment by @swasheck. I just thought it deserved to be an answer.


Filtering the results of a SELECT query to avoid certain words in the value of a column?

I've written this in SQL Server so you may have to translate to MySQL but this should work for you.

CREATE TABLE #ExcludeList (ExcludeVal varchar(50))

CREATE TABLE #VideoList (VideoName varchar(500))

INSERT INTO #ExcludeList VALUES ('Prob1'),('Prob2'),('DontUse1')

INSERT INTO #VideoList VALUES ('This is a prob1'),
            ('This is clean'),('This is also clean'),
            ('DontUse1 this one is bad')

SELECT *
FROM #VideoList VL
WHERE NOT EXISTS (SELECT 1 FROM #ExcludeList EL
                    WHERE VL.VideoName LIKE '%' + EL.ExcludeVal + '%')


First things first — 8.4 is no longer supported, so consider upgrading.

Autovacuum settings are documented.

Let's focus on the settings that affects when autovacuum kicks in. As you might know, this process is responsible for both, vacuuming and analyzing tables.

One of the settings affecting ANALYZE frequency is autovacuum_analyze_threshold. As you can read from the manual, this parameter specifies the minimal amount of rows that should be changed in order for ANALYZE to be triggered. This serves well for the small tables, but on big ones and/or tables with high activity this will lead to a way too frequent analyzing. In order to avoid this, another parameter exists, namely autovacuum_analyze_scale_factor. It specifies the fraction of the table to add to the threshold in order to check whether ANALYZE should kick in.

Say, we have a table with 10,000 rows and 200 of them had changed:

autovacuum_analyze_threshold tells us, that we're over the default 50;
we calculate the fraction based on autovacuum_analyze_scale_factor (which defaults to 0.1) and this gives us 1000 rows;
total calculated threshold therefore is 1050;
as 200 is less then 1050, ANALYZE is not started (we wait for more changes).

For the VACCUM there's another pair of parameters with completely similar behavior: autovacuum_vacuum_threshold and autovacuum_vacuum_scale_factor, except default scale for vacuuming is 0.2 or 20%.

Now, as you can guess, the bigger your table becomes, the more time it takes to trigger the autovacuum on it. Therefore, for bigger tables (typically over 1M rows) it is highly recommended to adjust these settings. You can do this on a per-tables basis using ALTER TABLE ... SET ( storage_parameter = ... ) syntax.

It might be tempting to set *_scale_factor to 0 and increase only the threshold for the bigger tables. Still, it better to keep factor to a small, but non-zero value anyway, as for a table with high activity 100,000 rows change might be occurring too frequently, causing unnecessary autovacuuming. See this thread in the pgsql-performance list.


How do you restore (or check) only the extended properties in a MS SQL Server .Bak file?

There's really no other way that I can think of than restoring the database.  The best option would be to have versioning stored elsewhere so that you don't have to go through all this trouble just to get a version number.

What you could do is have a little configuration file and a small application that performs the backups for you, and stores a key/value pair of filename/version.  That way you will have a reference as to what backup file is for which database version.  An easy application to write and it would save you a ton of time with restoring/querying just to find out this bit of data.


Postgresql Slow performance query after table partitioning

In the new plan, you can see seq. scan on the partitioned table and it's very expensive - cost about 18mln when the total cost of the old query is only 6mln in total.

I saw the same effect when I didn't use correct conditions in where or join. What is the partition key? Can your query use only one of the partition_sub_table or not. Do you have indices on sub-tables?


How to identify the ranges over which a postgres table was partitioned?

You need to look into pg_class. You can use pg_get_expr() to get a readable expression for the partition bounds:

select pg_get_expr(c.relpartbound, c.oid, true)
from pg_class c
where relname = 'tab1_p1';

If you want to see that for all partitions (and sub-partitions) of one table, you can use the following:

select pt.relname as partition_name,
       pg_get_expr(pt.relpartbound, pt.oid, true) as partition_expression
from pg_class base_tb
  join pg_inherits i on i.inhparent = base_tb.oid
  join pg_class pt on pt.oid = i.inhrelid
where base_tb.oid = 'public.tab1'::regclass;


what is oracle universal installer in oracle database products

Oracle's installers are mostly Java based - see here. Oracle apparently wants to "universalize" (yuck, what a word) the procedure. Apparently, however, for XE on Windows, they decided that it would be best just to have a plain old .exe.

Maybe they figured that Windows users who chose the XE option weren't up to installing a JVM? :-). Maybe they used some sort of Byte code-to-exe tool to convert their Java based installer to a simpler Windows exe?

Apart from the CPU, RAM and data limitations in XE, Java isn't included in the database. If I recall correctly (haven't done it in a while), the Linux installer is a Java based one - even though Java isn't in the db. I'm not sure if the installer has it's own JRE or whatnot.

Basically, Oracle is trying to make installs as generic (univeralize - what was wrong with the word "generic"?) as possible but has decided to keep the .exe paradigm for Windows.


How can I recover data from the LDF file?

You will need a previous Full Backup and any log backups taken since the last Full backup

1) Take a tail of the log backup

BACKUP LOG OldDB TO DISK='C:\OldDB_Tail.trn' WITH NO_TRUNCATE

2) Find Transaction for Deleted Records (Operation will be LOP_DELETE_ROWS for DELETEs and LOP_SET_BITS & LOP_MODIFY_ROW for a TRUNCATE TABLE)

SELECT * FROM fn_dblog(NULL,NULL) WHERE AllocUnitName = 'dbo.YourTableName'

3) Restore Previous Full Back and log backups plus the tail to a new
    database WITH NORECOVERY and STOPAT='Just Before Begin Time for Transaction'

RESTORE DATABASE NewDB
FROM DISK='C:\Previous_Full_Backup_Of_OldDB.bak'
WITH NORECOVERY,
STOPAT='2012/01/11 11:35AM',
MOVE 'OldDB' TO 'C:\DATABASES\NewDB.mdf',
MOVE 'OldDB_Log' TO 'C:\DATABASES\NewDB.ldf'

RESTORE LOG NewDB
FROM DISK='C:\Previous_TranLog_Backup_Of_OldDB.trn'
WITH NORECOVERY,
STOPAT='2012/01/11 11:35AM'

RESTORE LOG NewDB
FROM DISK='C:\OldDB_Tail.trn'
WITH NORECOVERY,
STOPAT='2012/01/11 11:35AM'

-- This recovers the restored database and allows access
RESTORE DATABASE NewDB WITH RECOVERY

This blog post covers in more detail finding the time to STOPAT using fn_dblog.
This blog post has a go at recovering data directly from the log but this might be very time consuming compared to a point in time recovery.

Also this blog post by Robert L Davis has an example on combining STOP and STANDBY to allow you to query the state of the databases at different points in time.


How to batch multiple insert statements for database dump with MySQL?

Your issue may involve the configuration settings for either OS or MySQL
HISTORICAL EXAMPLES
Years ago when I worked at a website firm, I used --skip-extended-insert to load a DB Server that had 2GB RAM. That made a whole world of sense to me because the server I dumped it from had 64GB RAM and the client wanted the data on a smaller machine.
At my current placement of employment, an outside vendor dumped data from SQL Server and produced a mysqldump compatible dumpfile. The problem with that dump file was each table had a single insert with all the rows. One table had 3.5 million. They could not be loaded into in-house VM will less than 8GB RAM configured. Shockingly, the solution to that was to scp the file to may laptop, load it into MySQL 5.5.37 on my laptop, mysqldump from it, send the new mysqldump back to the Linux CentOS VM, and load the mysqldump.
BTW, the 3.5 million row table loaded as a single insert into my Windows 7 laptop in 4.5 hours without changing any MySQL settings. To be honest with you, I did believe that "shot in the dark" would work because I did not reconfigure mysqldump on the laptop to work with the dump. My guess is that it probably loaded because the undo log in ibdata1 just grew on disk and held everything.
You should not have to live to skip-extended-insert if you dump on reload on the same machine.
SUGGESTIONS
While I was typing my answer, I saw you inject the following into the question

According to the a query I ran, the table clocks in at around 1.7GB, but it's MyISAM and apparently that number is unreliable (but it's a big table, anyway).

SUGGESTION #1
If that one MyISAM table has 1.7GB and has the 11 million rows, my suggestion would be for you to increase the size of the bulk insert buffer. The bulk insert buffer is for MyISAM usage only and supports extended inserts. Set bulk_insert_buffer_size to 256M.
Go to my.cnf or my.ini and add this
bulk_insert_buffer_size = 256M

Then, login to MySQL and run
mysql> SET GLOBAL bulk_insert_buffer_size = 1024 * 1024 * 256;

SUGGESTION #2
As for your InnoDB, you need to use a bigger log buffer. Set it to 64M by adding this to my.cnf
innodb_log_buffer_size = 64M

You must restart MySQL to use that new value.
SUGGESTION #3
You may also need bigger InnoDB Logs by increasing innodb_log_file_size. This require manual intervention as follows:
STEP 01: Run this command as root
mysql> SET GLOBAL innodb_fast_shutdown = 0;

STEP 02: Shutdown mysql

Linux : service mysql stop
Windows (as Administrator) : net stop mysql

STEP 03: Add this option to my.cnf or my.ini
innodb_log_file_size = 1G

STEP 04: In the OS, goto the folder where ib_logfile0 and ib_logfile1 are located and run
Linux
mv ib_logfile0 ib_logfile0.bak
mv ib_logfile1 ib_logfile1.bak

Windows
rename ib_logfile0 ib_logfile0.bak
rename ib_logfile1 ib_logfile1.bak

STEP 05: Start mysql (the log files get recreated so start will take an extra 1-2 min)

Linux : service mysql start
Windows (as Administrator) : net start mysql

After making one or more of these suggested changes, try dumping and reloading. It goes go much smoother. Start with just SUGGESTION #1 and try it. Try the other other two to see if it improves more.
SUMMARY
You need these options for your target MySQL DB Server in your my.cnf or my.ini
bulk_insert_buffer_size = 256M
innodb_log_buffer_size = 64M
innodb_log_file_size = 1G
max_allowed_packet = 1G

GIVE IT A TRY !!!


MySQL LEFT OUTER JOIN performance on a local column condition

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.


Ok, that error (ORA-00600 [6856]) is related to corruption.

Normally a DBA would go to My Oracle Support, follow the notes (for example    ORA-600 [6006] ORA-600 [6856] During Startup Instance, Followed by Termination by SMON (Doc ID 549000.1)), identify the affected objects then try to repair them, recover corruption, or restore a valid backup.

If you can not do that, and this is a sandbox/development database with no important data, in my opinion the easiest and fastest solution for you would be recreating your database with DBCA (Database Configuration Assistant).


How to fix the sys.host_summary table in MariaDB 10.3.13

The sys schema is not bundled with 10.3, but seems to be planned for 10.5. If you don't use/need it, you can safely remove it: DROP DATABASE sys;. If you do need it, you need to try resolving the issue outlined in the error message. For a start you can do SHOW CREATE VIEW sys.host_summary; to see which tables, views and functions it's referencing, as well as the definer/invoker.


You should be able to see if it's doing a reorg or a rebuild by using sp_whoisactive or something similar and inspecting the sql_text column.

If you kill it, you will not lose the work it has done on previous indexes that were defragmented as part of this script.  Those are done and committed.

As for the current index it's defragmenting, if it's doing a reorg, the progress it has made will not be lost if you kill it.  If it's a rebuild, which seems to be the case given the data you've provided, it's still OK to kill it, but you will lose any progress that has been made.


Oracle Database Client Installation 12c minimal install - Centos / Red Hat 7

So you want to perform a minimal install and instead of using the Instant Client which is below 100MB with minimal requirements, you chose the full client that may occupy over 1 GB space and requires additional packages to be installed, but you want to go against the documentation and have an irregular configuration, that is still way more than what you would have to do by using the Instant Client.

Just use the Instant Client and follow the installation instructions of that.


Finding Columns causing errors in SQL Developer

Set the preview window higher, default is 100 rows.

Then we can validate more during the preview.

You can scroll the preview window's worth of data, and each column value for each row will get validated via the column definition in the data.

Or, set the import type to insert script, and run the script in a worksheet.


A very similar question was asked on StackOverflow.  Have a look at my answer there.  I recommend a data model and give specific advice about the SQL to list of all messages in a thread, with read state and get all threads with latest message in each thread for a given user.


MBR cannot handle partitions bigger that 2 TB, thus if your partition is bigger than 2 TB you have to use GPT. There is no performance gain as far as I know. It's all about the capacity!


How does the T-SQL optimizer handle "and false"

With respect to your question

  will the query optimizer identify one of the queries as false and not
  run it?

You'd need to check the execution plans. An example below where it doesn't.

DECLARE @P INT =1

SET STATISTICS IO ON;

SELECT name
FROM sys.objects
WHERE type='P' AND @P=1
UNION
SELECT name
FROM master..spt_values
WHERE number >0 AND @P=0

Output

Table 'Worktable'. Scan count 0, logical reads 0
Table 'sysschobjs'. Scan count 1, logical reads 5

Notice that master..spt_values was not touched in the query execution. The reason for this is that the seek on this table is under a filter with a start up expression predicate of [@P]=(0). As this condition is not met the seek is never actually executed.

Both the Startup Expression and number of executions are shown in the operator tool tips as below.

With regard to the other part of your question

  Would it be better for me to put them in an IF statement?

Yes probably despite that. You're then guaranteed that the behaviour that you get is that which you want. Also even though master..spt_values doesn't have any pages read above it does still have an IS lock taken out against it (which doesn't happen in the IF ... ELSE version)

Better still might be to use an If that calls separate stored procedures for each branch. You haven't shown the rest of your code but one possible issue with these mutually exclusive branches might be parameter sniffing. The batch will be compiled as per the first set of parameters seen. These might be wholly unsuitable for the other branch(es). Splitting into child batches allows each branch to be compiled separately taking into account the parameter values that actually occurred when that branch was executed.


TokuDB optimize table does not reclaim disk space

I've had the same experience. The solution is to rebuild the table via this command:

ALTER TABLE my_table ENGINE=TokuDB ROW_FORMAT=TOKUDB_SMALL;

Replace TOKUDB_SMALL with your favourite compression algorithm.

With InnoDB an OPTIMIZE TABLE command simply does a trivial ALTER. But in TokuDB it does not, and tablespace is not reclaimed. By forcing an ALTER with ENGINE=TOKUDB you're taking this outside TokuDB's hands, and let the MySQL server tell TokuDB to create a (really) new table.


Database design for large volumes of data

Table for each user would be wildly inefficient as whenever a user leaves or joins you need to re-arrange your table structure.

having an owner_ID field in the table and then allowing the general users to be identifiable meaning that when someone is logged in the query can run AND owner_id = @active_user_ID

Be worth having a table to link the ID to the logged in user, and then you can also set up managers (Table User_ID, Manager_ID,name,detail...) which means you can allow managers to view all data of their underlings (if this is needed)

Its worth noting doing a structure like this to consider indexing the table in a structure that organises the data according to the owner_id field to improve performance for each user as they will unlikely overlap


eliminate extra spaces in VARCHAR columns

Thanks to @JNK's comment, select cast(trim(id) as varchar(6)) as id, cast(trim(voucher) as varchar(32)) as voucher, auth from my_table does the job.


How database size affects performance: Theory vs reality

It depends entirely on what you are doing with the data.

For basic insert/update/delete transactions that affect just a few rows, then the growth in data size is probably not a big consideration.  The database will use in-memory indexes to access the correct page.  You get more cache misses when the tables no longer fit into memory.  However, the overhead might be slight -- depending on the database, database configurations, and hardware configurations.

If you are doing queries that require full-table scans, then your performance is going to grow linearly or worse with the data size.  Indexes can actually make the situation worse, by randomizing page accesses, which then pretty much guarantee cache misses.

An alternative to more memory is improved disk speed -- solid state disk can provide tremendous improvement.

Just having more data is unlikely to affect performance unless the tables are used in queries.  Is the data redundant within a table or across tables?  Having large tables that never get used is messy, but has minimal impact on performance.  It is imaginable that if you have zillions of unnecessary tables, then then compiling queries could start to take more time.


Try adding books.id to ORDER BY. If books_data.date is not unique, relative order of rows in resultest is not guaranteed. Say you have 5 rows with date1, 10 rows with date2, and 5 with date3; date1 < date2 < date3.

Then some rows with date2 may appear on both pages, some on neither, and , if you are lucky enough, you may sometimes see expected results.
Assuming (books.id,books_data.date ) unique the following should return consistent results:

SELECT DISTINCT "books"."id", "books_data"."date" FROM "books"
LEFT OUTER JOIN "books_data" ON "books_data"."book_id" = "books"."id"
ORDER BY books_data.date DESC, books.id ASC LIMIT 10 OFFSET 0;

Short illustration :

id | some_non_unique_data
1  |  'val1'
2  |  'val2'
3  |  'val2'

When splitting such data into 2 pages, 2 rows per page ordering only by some_non_unique_data ascending the engine is free to return row id=2(or id=3) on any page. emphasized text