PostgreSQL behaviour in presence of two different type of indexes on the same column How does PostgreSQL behave if I create two (or more) indexes of different type on the same column?

In my case I want to compare B-Tree indexes against GIN on a tsvector column.
I know that GIN are specifically there for tsvector. What's curious is that PostgreSQL doesn't complaint if I create a B-Tree index, but I see that the query planner doesn't use it. I can also create a GIN index (without removing the B-Tree index) and now the planner uses the newly created index. The column now has two indexes but only one of them is used.

What's the criteria used for selecting an index type against another, even in presence of more than two indexes? Why doesn't PostgreSQL tell me that a B-Tree index on a tsvector is useless and won't be never used by the planner?

Update
The GIN index is used only on certain check conditions like my_tsvector IS NOT NULL but (obviously) not on conditions like my_tsvector @@ '...'::tsquery.

An index is going to be used if it's part of the fastest plan the query planner can come up with. Many factors are involved in the cost estimation. Conditio sine qua non: the index must be applicable. Indexes are bound to operators, not data types or functions or anything else. And operators are defined by the operator and data type of left and right operand. The manual about btree indexes:

  B-trees can handle equality and range queries on data that can be
  sorted into some ordering. In particular, the PostgreSQL query planner
  will consider using a B-tree index whenever an indexed column is
  involved in a comparison using one of these operators:

<
<=
=
>=
>

There is more, read the manual.

Related case with more explanation:

Can PostgreSQL index array columns?

A btree index on tsvector is not useless. It can be used to sort rows (implicitly using >= or <= operators):

SELECT * FROM tbl ORDER BY my_tsvector;

Or it can be used for equality predicates:

DELETE FROM tbl WHERE my_tsvector = 'foo bar'::tsvector;

But it cannot be used for a predicate my_tsvector @@ '...'::tsquery. You need a GIN or GiST index for that. Both use the the operator class tsvector_ops by default (no need to declare it explicitly):

https://www.postgresql.org/docs/current/static/gin-builtin-opclasses.html
https://www.postgresql.org/docs/current/static/gist-builtin-opclasses.html

Then the index is applicable. But Postgres may still decide to use a different query plan, based on table statistics and cost settings ...

You can have many indexes involving the same index columns. There are various kinds, types and various operator classes, which can be combined in many ways. The query planner will only consider applicable indexes and estimate the cost based on many factors ...

And you can even create the same index any number of times under different names. Then Postgres will take an arbitrary pick. It's your responsibility not to create pointless, duplicated objects.


Unable to backup SQL Server from a newly added clustered drive We have a clustered SQL server, and the backups of the databases are stored on the same drive where the data files are(F:). The storage team finally acted on my request to have a new clustered drive solely for SQL backups(G:). When I try to backup the database on the backup folder on the new clustered drive I get an error:

Cannot access the specified path or file on the server. Verify that you have the necessary security privileges and that the path or file exists.
If you know that the service account can access a specific file, type in the full path for the file in the File Name control in the Locate dialog box.

I have already:

Assigned the cluster disk to SQL Server in WSFC
Added the SQL Server service account in the Security on the disk properties, and has been inhereted by the children
Changed the database settings on the instance properties to have the new path for backup

Is there anything else that i missed? I still cant backup to the new clustered disk. Does this process require a server restart?

When adding clustered disk to an instance of SQL Server, there are a few steps that need to be taken.

Present the disk to all possible owners of that disk (nodes)
Bring the disk online from the first node that will own it
Bring the disk into the cluster - it'll now reside in available storage
Move the disk into the resource group for SQL Server
Set the SQL Server resource to DEPEND on that new disk
Double check sys.dm_io_cluster_shared_drives to see that SQL "sees" the disk. If it doesn't, one of the steps above was not successfully completed.

In this case, step 5 was missed which is why it didn't show up in the DMV listed in step 6.


Trying to find a way fit an extremely large index in to memory Description:

I am having trouble with the on-growing data size in my MySQL database.

I am using Ejabberd and MAM function which will make use of an archive table to store messages sent between users and the table keeps growing. It now takes over 10 seconds to query something like

SELECT COUNT(*) FROM archive
    WHERE username = '<some_id>'
      and bare_peer = '<some_string>'
      and timestamp >= '<some_timestamp_in_microseconds'

or

SELECT timestamp, XML, peer, kind, nick FROM archive
    WHERE username = '<some_id>'
      and bare_peer = '<some_string>'
      and timestamp >= '<some_timestamp_in_microseconds>'
      and timestamp <= '<some_timestamp_in_microseconds>'

These are very common SQL that would execute thousands of times each day, and since the SQL are executed from within Ejabberd, I cannot change the syntax.

Current Situation:

Instance Specification: 8 core CPU, 64 GB RAM
innodb_buffer_pool_size: 49392123904 bytes (roughly around 49GB)
With references to this post, I got the result of 1005383M (roughly 1TB) estimated requirement of memory size.
The archive table size: 700GB of data, and ~200GB of index, around 0.9b of rows (yes, a lot of rows)
Here is the table creation SQL:

CREATE TABLE `archive` (
  `username` varchar(191) COLLATE utf8mb4_unicode_ci NOT NULL,
  `timestamp` bigint(20) unsigned NOT NULL,
  `peer` varchar(191) COLLATE utf8mb4_unicode_ci NOT NULL,
  `bare_peer` varchar(191) COLLATE utf8mb4_unicode_ci NOT NULL,
  `xml` text COLLATE utf8mb4_unicode_ci NOT NULL,
  `txt` text COLLATE utf8mb4_unicode_ci,
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `kind` varchar(10) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `nick` varchar(191) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY `id` (`id`),
  KEY `i_username` (`username`) USING BTREE,
  KEY `i_timestamp` (`timestamp`) USING BTREE,
  KEY `i_peer` (`peer`) USING BTREE,
  KEY `i_bare_peer` (`bare_peer`) USING BTREE,
  FULLTEXT KEY `i_text` (`txt`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

Discussion:

With the below information, one thing I could think of is to:

Partition the archive table with Primary Key (RANGE / Per 5m of rows), but from my understanding, since MySQL doesn't support fulltext index in Partitions, I would be required to drop the fulltext index in the txt column, which I think is ok.
Unfortunately, since MySQL could only partition on Primary Keys, and I cannot change the SQL. I therefore cannot utilize the partition directly on the SQL. What I could do is to drop the entire partition regularly and keep the remaining index size to fit into memory as much as possible.

I am posting to seek for a second opinion on whether:

Is this the best way I could do with the above limitations?
If so, How can I partition such a big table without downtime, by using possibility pt-online-schema-change ?

Thank you all for your time.

Fitting the index into RAM is not a useful goal.  Decreasing the number of blocks of the index to use is a useful goal.

"Using intersection" is not as fast as the following composite index.  Both of your queries would benefit from

INDEX(username, bare_peer, timestamp)

When adding it, you can drop i_username since it is a prefix of this.

PARTITIONing, even if possible, is unlikely to improve performance.

Also, normalizing out the 4 names (in the same table?) would shrink this table significantly, thereby helping performance a little.

What version of MySQL?  If it a new one, adding the above index should be relatively non-invasive.

More on designing indexes:  http://mysql.rjweb.org/doc.php/index_cookbook_mysql


SQL Cluster with different Edition Is it possible to join new node SQL in Failover cluster with different Edition?
As example:

Cluster Failover running based on SQL Enterprise Evaluation Edition add a new node based on SQL Standard Edition

I've never tried this but I'm reliably informed it is 100% not possible. It is certainly not supported by Microsoft, for good reason, so why would you do it?

  The SQL Server edition you are installing must match across all the
  nodes in a SQL Server failover cluster. When you add a new node to an
  existing SQL Server failover cluster, make sure that you specify that
  the edition matches the edition of the existing failover cluster.

This comes from this MSDN link found almost instantly after a quick web search.

Bear in mind there are a few obvious and not-so-obvious functional reasons why you would not want to failover from Enterprise to Standard as some features are not supported in both.

The idea of a clustered environment should be that you maintain integrity of the database environment upon failover, so why compromise that in any way, whether talking about support or features?


How to show the column names of a table? The case is simple: You have a MySQL database where you have only an SQL query interface and you want to know the database structure with queries. You can list tables with show tables; command, but how do you see the individual column names?

(SELECT statement shows Empty set if no data is present and can NOT be thus used.)

select column_name from information_schema.columns where table_name='table'


Is it good practice to use non standard schema names in SQL Server? If I have a database MyDB, is it considered good practice / bad practice to use schemas other than dbo?

For example:

MyDB.reports.YearlySales
MyDB.reports.TopSellers
MyDB.[data].ProviderX
MyDB.[data].ProviderY
etc

In my opinion there is nothing wrong with it. In my data warehouse I use different schemas to differentiate between data for departments or in the case that I am copying tables from a third party database, to show where those tables come from.


DBCC checkdb on tempdb Is it recommended to run DBCC CheckDB on tempdb? If yes, can someone please list out the reasons to do so? This could be on any SQL Server version. The one I have is SQL Server 2017.

No, it's not recommended because it's largely a waste of time. When SQL Server restarts, tempdb is created from scratch. Also, the data in there isn't around long enough to be a good indicator of if there's an issue with it, or the underlying storage.

Both Ola's scripts and Maintenance Plans will skip tempdb when you set up CHECKDB routines.

Another reason why it's largely useless is because if tempdb does get corrupted, you'll know in a hurry. Check out my blog post here where I corrupt tempdb on purpose: DBA Days: Killing TempDB softly

Coming up in SQL Server 2019, we'll be able to use "in memory" technology for system tables in tempdb. While this solves pretty big contention issues, you can't run CHECKDB against in-memory tables. The only way you could check those is if you backed up tempdb, which isn't something you can do.

BACKUP DATABASE tempdb TO DISK = 'D:\Backups\t.bak'

  Msg 3147, Level 16, State 3, Line 27 Backup and restore operations are
  not allowed on database tempdb. Msg 3013, Level 16, State 1, Line 27
  BACKUP DATABASE is terminating abnormally.

If you really think it's worth running checkdb against a database that SQL Server won't allow you to back up, well, it's your maintenance window, I suppose.


How to find which tables were deleted (in Oracle) A user dropped number of tables (~1449) on Development server and the user has no record of the tables name that were dropped.

Things we know:
1. Date on which the action was performed.
2. Username of the user who executed the statements.

Is it possible to get the table names (that were dropped)?

If you have the recyclebin turned on in your database you can query the DBA_RECYCLEBIN view to find which objects were dropped within a certain time period.

SELECT owner,
       original_name,
       operation,
       droptime,
       can_undrop
FROM   dba_recyclebin
WHERE  TO_DATE (droptime, 'YYYY-MM-DD:HH24:MI:SS') BETWEEN SYSDATE - 3
                                                       AND SYSDATE

You can issue a FLASHBACK command to restore the table;

FLASHBACK TABLE <you table's original name> TO BEFORE DROP

Alternatives are to flashback to a specific SCN, TIMESTAMP, etc.

See https://docs.oracle.com/cd/B28359_01/server.111/b28286/statements_9012.htm#SQLRF01802 and http://docs.oracle.com/cd/B28359_01/server.111/b28310/tables011.htm#ADMIN01511 for some more reading (it never hurts to read)


How can I use Count() or Sum() with advanced criteria such as matching the most recent rows where a condition is true? I have created a table with the following structure:

| ID | RecordDate       | SerialNumber | ProductID | StageID | Result |
|----+------------------+--------------+-----------+---------+--------|
|  0 | 2019-05-23 06:00 | A123456789   |         3 |       6 |      1 |
|  1 | 2019-05-23 06:04 | B123456789   |         4 |       6 |      0 |

It is interesting to be able to generate some basic statistics anchored on the StageID or the ProductID.  Like this:

DECLARE @StartDate datetime = '2019-05-10 08:00:00';
DECLARE @EndDate datetime = '2019-05-18 09:00:00';
DECLARE @StageID int = 6;

SELECT ProductID,
    COUNT (*) 'Total',
    SUM (CASE WHEN Result = 1 THEN 1 ELSE 0 END) 'Output'

FROM table1
WHERE RecordDate BETWEEN @StartDate AND @EndDate AND StageID = @StageID
GROUP BY ProductID;

This yields the following typical output:

| ProductID | Total | Output |
|-----------+-------+--------|
|         3 |  4533 |   4211 |
|         4 |  3444 |   3001 |
|           |       |        |

Where:

Total = total number of product processed including passes, fails, duplicates (re-attempts) etc.

Output = count of good product which has been sent on from the process.

Now, one problem is that my count of output isn't very precise.  If someone were to process the same product twice, I would count it twice even though the true output stays the same.
I have also counted product whose result was 1, but then it was processed again and the result was zero.  I count this even though the true output has actually gone down.

What I really want to do in this scenario is count the most recent record for each SerialNumber where that result was 1.  I can get that information with a basic query like this:

SELECT COUNT(*)
FROM ( SELECT MAX(ID) AS 'ID'
       FROM table1
       WHERE StageID = @StageID
         AND Result = 1
         AND RecordDate BETWEEN @StartDate AND @EndDate
       GROUP BY SerialNumber ) ID

This query gives me the single result that I want, but I can't figure out how to apply such criteria to the original format so that I get the given table grouped by ProductID.  I feel like I want another count() or sum() function with some critieria attached.

How do I do it? Or is there a better way? I am very new to SQL so there could be a basic answer staring me in the face here.

Using a common table expression for Max(RecordDate) might help.

Not sure if this code is just what you need, but you can modify. The script works on the sample table that I created from your question.

DECLARE @StartDate datetime = '2019-05-10 08:00:00';
DECLARE @EndDate datetime = '2019-05-24 09:00:00';
DECLARE @StageID int = 6;

WITH MyRecentSerialNumber AS
    (SELECT ID, MAX(RecordDate) AS MaxRecordDate,   SerialNumber,   ProductID,  StageID,        Result
    FROM t1
    WHERE Result = 1
    GROUP BY ID, SerialNumber, ProductID, StageID, Result)
SELECT ProductID, COUNT (*) 'Total',
    SUM (CASE WHEN Result = 1 THEN 1 ELSE 0 END) 'Output'
FROM MyRecentSerialNumber
WHERE MaxRecordDate BETWEEN @StartDate AND @EndDate AND StageID = @StageID
GROUP BY ProductID;

Output is the same with the sample data, but you can test and adjust with data that should be excluded.


Is this a good database design or transition? Normalize to Denormalized for Data Warehousing As of now in sql server , we have a  data and a series normalized table structure that is connected to a C# web app (Human Resource / Recruiting System) (slightly medium (or small 12,000 rows.) size as of now) that does create , read , update and delete.

In the future (2 years time) , we are planning to turn this database into a data warehouse. however in data warehousing normalization in not exactly needed.

My senior programmer(who is not a programmer, but more on business intelligence) suggest that we should denormalize all tables on the same database (Kimball's data warehouse) connect that to the C# web app , do the crud and if test succeeds the data put it in azure data warehouse.

So my main question is this a good thing or is there a better way? like extracting the data from the normalized table and putting it into a new database with the denormalized table? Is there any other way?

Please excuse , I'm more on software development usually using an ORM and not much on business intelligence.

Thank you to All.

(slightly medium size as of now)

You should be more precise than that when talking about data sizes. Generic words like small/medium/large will mean different things to different people, though in this instance it probably isn't significant.

  In the future, we are planning to turn this database into a data warehouse.

You shouldn't turn an application database into a data warehouse - keep the application database optimised for normal live application use and have data fed from it into the warehouse database for offline analysis.

  however in data warehousing normalization in not exactly needed.

Correct. Often warehouse solutions are explicitly denormlised in various ways to make certain reports easier to produce efficiently (especially by automated tools and relatively unskilled users).

  like extracting the data from the normalized table and putting it into a new database with the denormalized table?

This is generally the way to go.

Keep your live data structures optimised for the applications main workflows and data correctness (i.e. keep things in normal form to avoid data inconsistencies due to design issues or other bugs), and build & update (or rebuild) your warehouse from those application databases using some form of ETL process. The live, normal form, data in the production databases is your source of truth from which you can rebuild the warehouse if something goes wrong. The warehouse should not live in one application instance's database because you may later want to combine data sourced from multiple applications (and/or multiple instances of the same application) in the warehouse, and the differing IO and locking requirements of live applications and large reporting tasks usually mean you want to keep those tasks separate and not competing for resources.

There are exceptions to this of course, as with everything. For instance you may want live reports in the application that you denormalise some data for, for instance, though in this case you would still have core application tables optimised for data consistency and the application's main activity, and the reporting structures built/updated/rebuilt from that source of truth.


Retrieve data from SP inside of From Goal:
Retreive a list of data from a stored procedure that is acting as a database name. When you have retrieved the list from stored procedure, I want to make another new data manipulation.

Problem:
I would like to use [sp_Test] to retrieve the data from the syntax code "FROM" but it does not work. Do you know how to solve it?

create table datatable (id int,
                        name varchar(100),
                        email varchar(10),
                        phone varchar(10),
                        cellphone varchar(10),
                        none varchar(10)
                       );

insert into datatable values
  (1, 'productname', 'A', 'A', 'A', 'A'),
  (2, 'cost', '20', 'A', 'A', 'A'),
  (3, 'active', 'Y', 'A', 'A', 'A');

insert into datatable values
  (4, 'productname', 'A', 'A', 'A', 'A'),
  (5, 'cost', '20', 'A', 'A', 'A'),
  (6, 'active', 'Y', 'A', 'A', 'A');

insert into datatable values
  (7, 'productname', 'A', 'A', 'A', 'A'),
  (8, 'cost', '20', 'A', 'A', 'A'),
  (9, 'active', 'Y', 'A', 'A', 'A');

CREATE PROCEDURE [sp_Test]
as
begin
    set nocount on

    SELECT a.name, a.email, a.phone
    FROM datatable a

end

That is not a supported syntax as a stored procedure can return multiple result sets or do other data manipulations and return nothing.

You would need to convert your stored procedure to a table valued function or insert the results into a temp table.


How do I translate a Windows SID to an SQL Server server_user_sid? There is this nice SQL Server function SUSER_SNAME which translates a server_user_sid to a user name. This is useful for translating well-known Windows SIDs to (potentially localized) user names.

Example:

SELECT SUSER_SNAME(0x01020000000000052000000021020000)

-- yields 'BUILTIN\USERS' (or, on a German system, 'VORDEFINIERT\Benutzer')

With some Googling and trial-and-error (= create the user manually and check sys.server_principals afterwards) I have determined the following equivalencies:

Built-in User/Group    Windows SID      SQL Server server_user_sid

BUILTIN\USERS          S-1-5-32-545     0x01020000000000052000000021020000
NT AUTHORITY\SYSTEM    S-1-5-18         0x010100000000000512000000

What's the algorithm to convert Windows SIDs to SQL Server server_user_sids?

SIDs in the form of 0x01020000000000052000000021020000 are not "SQL Server" SIDs. That is simply the underlying binary value of the SID. Another form it can take (and still be the same value) is the "string" form (SID String Format Syntax), which looks like S-1-5-32-545 (referred to as being the "SDDL" format in some MSDN documentation, though SDDL covers more than just SIDs). Both are the same Windows SID. This setup is similar to how GUIDs have a string representation that is different than their underlying binary value.

There is an undocumented built-in function, SID_BINARY, that does this translation from the SDDL form into the binary form:

SELECT SID_BINARY(N'S-1-5-21-408552231-458724953-3089381293-513');
-- 0x01050000000000051500000027035A185996571BAD3724B801020000

This function should work across most SID types. The following two queries show it working correctly for Certificates and Asymmetric Keys (you can verify proper translation since these two system catalog views have both forms of the SID in them). And it would work for any Logins created from Certificates and Asymmetric Keys as the SIDs for those (both Logins and Users) are the Cert / Key SIDs:

SELECT [name], [string_sid], [sid], SID_BINARY([string_sid])
FROM   [master].sys.certificates;

SELECT [name], [string_sid], [sid], SID_BINARY([string_sid])
FROM   [master].sys.asymmetric_keys;

Please note that principals of types "S" (SQL Server Login / SQL Server User) and "R" (Server Role / Database Role) do not have an SDDL representation as they are not Windows SIDs. These two types of principals have SQL Server properietary SIDs, so I guess these would be "SQL Server SIDs", though the distinction (between Windows SIDs and SQL Server SIDs) is of value and not form.

If you don't want to use an undocumented function, this can also be accomplished via SQLCLR using .NET's SecurityIdentifier class.

Pre-made SQLCLR functions to do these translations can be found in the Free version of the SQL# library (that I created): Convert_SddlSidToBinary (does the same translation as SID_BINARY) and Convert_BinarySidToSddl.


Is it possible to virtually vertical partition and oracle database table? Just like the question asks; is it possible to transparently partition a table vertically, by using virtual partitions? I'd imagine if it was possible, that columns would just be stored in separate table spaces or some such.

In my specific environment, we have an application server that batch writes a large amount of data every 15 minutes or so. There is way more data than our reporting applications need. The primary fact table has roughly 50 columns. We use about 10 of them.

Is there any scenario (even if virtual vertical partitions do not exist), that such a scheme could improve performance? I'd imagine that having your data split across multiple tablespaces (and therefore potentially multiple disks) would improve the seek times.

I'd imagine that having your data split across multiple tablespaces (and therefore potentially multiple disks) would improve the seek times.

Your whole premise behind the question is faulty. You do not need to split your data between tablespaces to spread the i/o across more disks, you need to increase the number of disks in your RAID10 or (better still) ASM array. You will get less performance gain, less space efficiency and far more maintenance trying to manually tune the i/o like you are suggesting.

ASM beats RAID10 primarily because it understands the data being written to it - so for example it can vary the stripe size between data blocks and logs.


Select first and last row based on each user id mysql I want to select every first and last event of every user id, if no first event exists,then just the last event instead . tried using partition over , but i am getting first 2 events instead.

Input:

id  timestamp   event
10096   1545136190  songs
10096   1545136202  tv
10096   1545136239  book
10096   1545136244  eat
10096   1545136252  travel
10096   1545136271  tv
10096   1545136275  laptop
10096   1545313396  sleep
10105   1545464444  book
10105   1545464455  eat
10105   1545464457  travel
10105   1545464460  tv
10105   1545464605  book
10105   1546740388  sleep
10213   1545134176  tv
10213   1545134180  computer
10213   1545134221  play
10213   1545134238  book
10213   1545134267  eat
10213   1545146190  travel
10213   1545146212  play
10213   1545623861  sleep
1053    1545131279  sleep

Expected Output:

   id   timestamp   event
10096   1545136190  songs
10096   1545313396  sleep
10105   1545464444  book
10105   1546740388  sleep
10213   1545134176  tv
10213   1545623861  sleep
1053    1545131279  sleep

tried doing this but not getting exactly what i want:

SELECT * FROM (
SELECT
    ROW_NUMBER() OVER(PARTITION BY id ORDER BY id) as RowNum,
    id,
    timestamp,
    event
FROM
    `table`
) MyData
WHERE RowNum < 3

SELECT id,
       MIN(`timestamp`) first_timestamp,
       FIRST_VALUE(event) OVER (PARTITION BY id ORDER BY timestamp ASC) first_event,
       MAX(`timestamp`) last_timestamp,
       LAST_VALUE(event) OVER (PARTITION BY id ORDER BY timestamp ASC) last_event
 -- or FIRST_VALUE(event) OVER (PARTITION BY id ORDER BY timestamp DESC) last_event
FROM `table`
GROUP BY id


Execute Sql statement and send result to email I have to execute SQL statement every day and send result to dba by email.I configured database email.

Below is query:

 SELECT
        account.accountID,
        account.name
    FROM
        account
        LEFT OUTER JOIN accountfeaturesetting afs
            ON afs.accountid = account.accountid
                and afs.featureid = 'Schedules'
                and afs.settingid = 'EditReasons'
    WHERE
        ISNULL(afs.Value, '0') = '1'
        AND NOT EXISTS
 (SELECT 1 FROM program
WHERE program.AccountID = account.AccountID
 AND program.Active = 1 AND
(program.ScheduleEditReasonFlags <> 0 OR
 program.ScheduleEditReasonFields <> 0))
        AND account.IsMaster = 0

For this I created Sql job having 2 steps:

execute select statement
send result:

Use MSDB
    EXEC msdb.dbo.sp_send_dbmail
        @profile_name = 'abc',
        @recipients = 'recipients@company.com',
        @subject = 'queryresultset',
        @body='testmail',
        @query_attachment_filename='warning.csv'

It is throwing an error and the result set is not attached with query.

  Error: Parameter @attach_query_result_as_file cannot be 1 (true) when no value is specified for parameter @query.
  A query must be specified to attach the results of the query.
  [SQLSTATE 42000] (Error 14625)

Edit:

Use MSDB
    EXEC msdb.dbo.sp_send_dbmail
        @profile_name = 'abc',
        @recipients = 'recipients@company.com',
        @subject = 'queryresultset',
        @body='testmail',
        @query_attachment_filename='warning.csv'
    DECLARE @msg VARCHAR(250);
    declare @query varchar(2048);
    SELECT @msg = 'Please refer to the attached spread sheet for the report.';

    set @query='
        SELECT
        account.accountID,
        account.name
    FROM
        account
        LEFT OUTER JOIN accountfeaturesetting afs
            ON afs.accountid = account.accountid
                and afs.featureid = 'Schedules'
                and afs.settingid = 'EditReasons'
    WHERE
        ISNULL(afs.Value, '0') = '1'
        AND NOT EXISTS
 (SELECT 1 FROM program
WHERE program.AccountID = account.AccountID
 AND program.Active = 1 AND
(program.ScheduleEditReasonFlags <> 0 OR
 program.ScheduleEditReasonFields <> 0))
        AND account.IsMaster = 0
        go';
        exec (@query)

Above query is not taking schedules and editreasons value column.  I am still getting a syntax error after declaring all variables like:

declare @featureid varchar(100);
declare @settingid varchar(100);
declare @value varchar(100);

Your code is backwards.  You are sending the email, then generating the query results.  The sp_send_dbmail stored procedure will run the query for you, and append the results either in the body of the email or as an attachment.

In case you aren't aware, Microsoft has extensive, comprehensive, and free documentation on their website for SQL Server.  For instance, sp_send_dbmail is available in great detail at:  https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-send-dbmail-transact-sql

The @query parameter is documented as:

  [ @query= ] 'query'
  Is a query to execute. The results of the query can be attached as a file, or included in the body of the e-mail message. The query is of type nvarchar(max), and can contain any valid Transact-SQL statements. Note that the query is executed in a separate session, so local variables in the script calling sp_send_dbmail are not available to the query.

They also have several examples of calling sp_send_dbmail, including this salient point:

EXEC msdb.dbo.sp_send_dbmail
    @profile_name = 'Adventure Works Administrator',
    @recipients = 'yourfriend@Adventure-Works.com',
    @query = 'SELECT COUNT(*) FROM AdventureWorks2012.Production.WorkOrder
                  WHERE DueDate > ''2004-04-30''
                  AND  DATEDIFF(dd, ''2004-04-30'', DueDate) < 2' ,
    @subject = 'Work Order Count',
    @attach_query_result_as_file = 1 ;

So, you need to pass the query definition into msdb.dbo.sp_send_dbmail like this:

DECLARE @msg VARCHAR(250);
DECLARE @query varchar(2048);

SET @msg = 'Please refer to the attached spread sheet for the report.';

SET @query='
    SELECT
        account.accountID,
        account.name
    FROM
        dbo.account
        LEFT OUTER JOIN dbo.accountfeaturesetting afs
            ON afs.accountid = account.accountid
                AND afs.featureid = ''Schedules''
                AND afs.settingid = ''EditReasons''
    WHERE
        ISNULL(afs.Value, ''0'') = ''1''
        AND NOT EXISTS
            (
                SELECT 1
                FROM dbo.program
                WHERE program.AccountID = account.AccountID
                    AND program.Active = 1
                    AND (
                        program.ScheduleEditReasonFlags <> 0
                        OR program.ScheduleEditReasonFields <> 0
                        )
            )
        AND account.IsMaster = 0;';

EXEC msdb.dbo.sp_send_dbmail
    @profile_name = 'abc'
    , @recipients = 'recipients@company.com'
    , @subject = 'queryresultset'
    , @body= @msg
    , @body_format = 'TEXT'
    , @query = @query
    , @execute_query_database = 'MyDB'
    , @attach_query_result_as_file = 1
    , @query_attachment_filename='warning.csv'
    , @query_result_header = 1
    , @query_result_width = 80
    , @query_result_separator = ' '
    , @exclude_query_output = 0
    , @append_query_error = 1
    , @query_no_truncate = 0
    , @query_result_no_padding = 0;

Also note the extra parameters included, which control various output factors such as how the query is formatted etc.

Note in the @query text above, I've "escaped" the single quotes to prevent SQL Server from considering them as literal delimiters.  Essentially, any single-quotes appearing inside the @query parameter need to be turned into two single-quotes.  Erland Sommarskog has an excellent piece on Dynamic SQL and nested strings.


Corrupt InnoDB tables after every mysql restart. What's wrong? Recently I changed a few settings in my mysql configuration. I did the following:
- stop mysql
- save the changed settings
- delete all ibdata and ib_logfiles
- start mysql

Normally this works fine but recently, everytime I do it, all my InnoDB tables get currupted. What am I doing wrong?

My settings:

# The MySQL server
[mysqld]
port        = 3306
socket      = /var/lib/mysql/mysql.sock
key_buffer = 16K
max_allowed_packet = 1M
table_cache = 4
thread_cache = 16
sort_buffer_size = 64K
read_buffer_size = 256K
read_rnd_buffer_size = 256K
net_buffer_length = 2K
thread_stack = 64K

#skip-networking
server-id   = 1

# Uncomment the following if you want to log updates
#log-bin=mysql-bin

# Uncomment the following if you are NOT using BDB tables
skip-bdb

# Uncomment the following if you are using InnoDB tables
innodb_data_home_dir = /var/lib/mysql/
innodb_data_file_path = ibdata1:10M:autoextend
innodb_log_group_home_dir = /var/lib/mysql/
innodb_log_arch_dir = /var/lib/mysql/
# You can set .._buffer_pool_size up to 50 - 80 %
# of RAM but beware of setting memory usage too high
innodb_buffer_pool_size = 400M
innodb_additional_mem_pool_size = 30M
# Set .._log_file_size to 25 % of buffer pool size
innodb_log_file_size = 400M
innodb_log_buffer_size = 8M
innodb_flush_log_at_trx_commit = 0
innodb_lock_wait_timeout = 50

[mysqldump]
quick
max_allowed_packet = 16M

[mysql]
no-auto-rehash
# Remove the next comment character if you are not familiar with SQL
#safe-updates

[isamchk]
key_buffer = 8M
sort_buffer_size = 8M

[myisamchk]
key_buffer = 8M
sort_buffer_size = 8M

[mysqlhotcopy]
interactive-timeout

markus you want to read the very last paragraph on mysql.com docs for resizing log file http://dev.mysql.com/doc/refman/5.1/en/innodb-data-log-reconfiguration.html - you only need to remove the ib_ log files and not the ibdata1 file

  Stop the MySQL server and make sure that it shuts down without errors
  (to ensure that there is no information for outstanding transactions
  in the log). Copy the old log files into a safe place in case
  something went wrong during the shutdown and you need them to recover
  the tablespace. Delete the old log files from the log file directory,
  edit my.cnf to change the log file configuration, and start the MySQL
  server again. mysqld sees that no InnoDB log files exist at startup
  and creates new ones.


Migrating Sql Cluster Installation To New Drive We are migrating from an old SAN to a new SAN and need to migrate our SQL Server instance to new LUNs. We are able to migrate the data without any problem, but the SQL Server instance itself is clustered (single instance) and the clustered disks must also be migrated to the new SAN.

Paths are as follows:

Create LUNs on new SAN.
Present new LUNs to the cluster.
Copy everything from old clustered disk resource to new clustered disk
resource.
Rename OLD disk resource to something.
Rename NEW disk resource to OLD name.
Cross fingers.

OR

Reinstall OS/Sql on the cluster.

We will have to do this for 13 instances.

Your steps to do this are pretty much right, I've added some steps and changed the wording on a few items.

Some SAN vendors provide migration software so you only have to turn off the cluster nodes for a few minutes and they will migrate the data behind the scenes.  You take a breif outage and a performance hit until the migration is complete, but you don't have to manually copy the data over or have to change the drive letters.  It's worth looking into for having 13 instances.

Create LUNs on new SAN.
Present new LUNs to the cluster.
Copy everything from old clustered disk resource to new clustered
disk resource.
add new disk to the cluster group
change old disk letter to an unused disk letter or remove disk letter
entirely
change disk letter for new drive to be the same as old disk letter
change dependencies in the cluster resources to the new disk
remove old disk from cluster group
bring SQL resource online


Would it be considered as a bad practice to have multiple nullable FKs on a table in SQL Server On my database structure in SQL Server, I have 3 types of products which requires different information about the order. So, I created one Customers table and three different orders tables: OrdersForProductAs, OrdersForProductBs, OrdersForProductCs. All orders table has one to many relationship on Customers table.

I also have another table which is Payments and will hold the payment details inside. But I have doubts here on how to structure it.

As I have multiple product types and a customer may have orders for multiple products at the same time, I need to relate those three order tables to Payments table.

The other issue is that a customer may have an order for only one type of product. So, the FK columns on Payments table needs to be nullable.

My question is whether those nullable FK columns would be a headache for me on the long run or not? Generally speaking, would it be considered as a bad practice to have nullable FK columns on a table?

I'd question why you have OrdersForProductX tables at all
It's possible the FK problem you've asked about can be designed out...

If these tables have the same structure, then you simply need a ProductType column on some OrderProduct table. Then Payment just links to that with one FK

If the table have different structures, I assume they have some common attributes. So, you can have a common OrderProduct table then specific child table per product type (see below) Again, Payment just links to the commone table with one FK

This is the "superkey/subtype pattern"

UQ1 is the "super key" used a foreign key on the subtype tables
Each subtype table has a composite PK and FK on (OrderID, ProductType)
Each subtype table has a CHECK constraint to restrict types in that table

OrderProduct

OrderID, PK, UQ1
ProductType, UQ1
CommonThing1
...

OrderProductA

OrderID, PK, FK
ProductType, PK, FK, CHECK ProductType = A
ProductAThing1
...

OrderProductB

OrderID, PK, FK
ProductType, PK, FK, CHECK ProductType = B
ProductBThing1
...


How to restrict SQL server profiler to show my symmetric key password while executing query from C# code? I used symmetric encryption to encrypt sensitive data in my application. I don't want anyone see my password for decrypting the data. But system admin can able to see symmetric password in SQL Server Profiler.

How can I stop them ?

Profiler Result :

exec sp_executesql N' OPEN SYMMETRIC KEY sym_RegAna_Default DECRYPTION BY ASYMMETRIC KEY asym_RegAna_Default WITH PASSWORD = ''admin@123'' ;

Don't execute the statement as a string by using sp_executesql

Execute the statement directly:

OPEN SYMMETRIC KEY sym_RegAna_Default DECRYPTION BY ASYMMETRIC KEY asym_RegAna_Default WITH PASSWORD = 'admin@123'

That way, SQL Server is able to parse the statement and will notice that it's a command that contains sensitive information and will not show it in any trace output.

it will show up as:

--*OPEN SYMMETRIC KEY----------------------------

Please be aware that this applies to ALL statements that contain passwords. So also when you create the keys.


What queries are supported by MySQL but not PostgreSQL? In process of migrating to PostgreSQL and am looking for a list of types of queries I must change (or some online query converter should one exist).  What originally prompted this is my attempt to execute LEFT OUTER JOIN t ON t.id=t1.id || t.id=t2.id || t.id=t3.id which works with MySQL but not PostgreSQL (turns out I must use OR instead of || and I expect the same goes for &&).

https://dba.stackexchange.com/a/60254/95143 primarily lists PostgreSQL features which are not supported by MySQL and may be out of date (last updated 3 years ago), however, might list some still relevent:

There are no session variables in PostgreSQL, unlike MySQL. (You can abuse GUCs for this, but not to do the same sort of things you do with MySQL session variables).
PostgreSQL has no INSERT ... ON DUPLICATE KEY UPDATE ("upsert")
At the moment, PostgreSQL has different replication options than MySQL. MySQL has no point-in-time recovery, but PostgreSQL has no logical replication (yet, we're working on it).
GROUP BY ... ROLLUP for summaries of groupings. Doing the same thing in PostgreSQL requires a union, usually over two queries over a CTE.
PostgreSQL doesn't have a built-in event scheduler. You generally just use cron or the Windows Task Scheduler; there's also PgAgent from PgAdmin-III.
Things that MySQL has (but not Postgres) are clustered indexes, GROUP BY .. ROLLUP and the Event (scheduler). – (From ypercube's comment)

Many of these I don't care about, however, I will definitely need to address ON DUPLICATE KEY UPDATE and take a close look at PostgreSQL's reserved words.  What other queries or features would one need to change?

re 1) very often they can be replaced with other features (e.g. I have seen query variables being used to simulate window functions or pass the result from one query to another)

re 2) Postgres has insert ... on conflict()

re 3) Postgres has logical replication

re 4) Postgres has GROUP BY ROLLUP

re 5) That's true. Other alternatives are pg_cron or pg_timetable

re 6) I have worked more (and longer) with Oracle than I have worked with Postgres. And I can count the number of times I needed a table with a clustered index (called "index organized table" in Oracle) on my two hands in the last 10 years. So I don't really miss them in Postgres (although I do agree it would come in handy sometimes).

  What other queries or features would one need to change?

In general, queries that are invalid (standard) SQL.

You already noticed that Postgres complies with the SQL standard and uses  || as the string concatenation operator, not for OR).

Integer division is different. 1/10 yields 0 in Postgres (because both arguments are integers) whereas MySQL returns a decimal value.

Then there is the invalid use of GROUP BY, invalid use of MySQL's interpretation of "boolean" expressions, invalid DATE values (2020-02-31or 0000-00-00), Postgres also does not allow to divide by zero.

Postgres is much stricter when it comes to matching datatypes (varchar_column = 1 will fail), while MySQL usually lets you get away with comparing apples to oranges.

Non-standard quoting using those dreaded backticks ` (in general do not quote identifiers at all).

Accept that a "database" in MySQL is really a schema (as seen by Postgres and the SQL standard). If you have multiple databases in MySQL you most probably want a single database with multiple schemas in Postgres.

There are other syntax differences, e.g. date arithmetic works differently in MySQL and Postgres, including interval literals. Many functions have different names (string_agg() vs. group_concat()).

Obviously several data types are different as well (e.g. the infamous int(11))

P.S.: I maintain a more up-to-date feature comparison in this answer


Recursively call STUFF for results in a table valued function This is a static query that kind of shows where I am trying to get

declare @Airbill VARCHAR(50) = '12345678912'
select stuff(stuff(@Airbill, 4, 0, '-'), 9, 0, '-')

But I need to make it more variable.  I have the following varible:

DECLARE @AirbillMask VARCHAR(50) = '999-9999-9999'

Which can (and does) change.  It is always '9's and '-'s.  But the placement of them changes.

If @AirbillMask is 999-99-99-9999 my desired result would be 123-45-67-8912.
If @AirbillMask is  999-9999-9999 my desired result would be 123-4567-8912.

I am running dbo.FindPatternLocation(@AirbillMask, '-') that returns a table of dash locations.

I was planning to find a way to STUFF the parts of the @Airbill into it corresponding locations to produce a calculated field of FormattedAirbill, but I got stuck trying to iterate down into the STUFF calls.

Is there a way to call STUFF inside another STUFF call, over and over till I am out of rows?

I feel like a recursive CTE or cross apply could accomplish this, but I can't seem to wrap my head around it.

NOTE: I would prefer to do this without looping, if possible.


Regressed queries after statistics update I've scheduled job with statistics update on 100GB database (some of them were really old, from several months to 2-3 years), job ended succesfully after 20minutes at 1AM.
Now i checked Query Store (after 15 hours ~) and there are several regressed queries with markedly worse execution plans. Average times are significantly worse than one day before update stats.
What should i do now? Do you think DBCC FREEPROCCACHE could help? Thanks a lot
PS: SQL Server is version 2019, but database has Compatibility Level set on 100 (2008). Idk why, this is our new customer. This will be changed soon.

These are the steps I would take:

First of all download Microsoft Migration Assistant and try to spot incompatibilities between compatibility level 2008 and 2012.
Then download Microsoft Assessment and Planning Toolkit and scan for all sort of incompatibilities following a guide.
Spot the incompatible queries using the Query Tuning Assistant .
If everything is alright now move to 2012 compatibility level first and check if all the applications are still working. If the database is old I suppose the applications bight be old too. The applications may contain deprecated command not compatible with later version of SQL Server
If the previous step worked now move to compatibility level 2016 and activate Query Store. Use that to monitor your database for a good week or two and check if everything is alright. At this point 99% of your queries should run faster and 1% should be completely blocked. Query Store is your friend and you will use it to debug blocking and waits.
Now that everything is fixed moving to compatibility level 2019 should be a glass of water as there are not many changes between 2016 and 2019


Select statement with dynamic columns where these columns are rows in another table I want to write a select statement that with some fixed columns and dynamic columns.
here Dynamic Columns names are the rows in another table.

I have tried this but getting error. Please tell me where my error is

set @sql=(Select subject from tbl_subjects where C_Id=22);
SELECT
    ta.rollno,
    ta.StdNm,
    tc.C_Name,
    @sql
FROM
    tbl_cmarks tm,
    tbl_admission ta,
    tbl_classes tc
WHERE
    tm.Cid = tc.C_Id
        AND ta.rollno = tm.rollno
        AND tm.Cid = 22 and tm.Examid=9

Mysql showing:

  Error Code: 1242. Subquery returns more than 1 row

Could any one tell me how to do this in mysql?

Looking at your table structures, I would suggest that you change the design to something that is normalized. For example:

create table tbl_subjects
(
  sub_id int,
  subject_name varchar(25)
);

create table tbl_cmarks
(
  c_id int,
  examid int,
  rollno int,
  sub_id int,
  mark int
);

Using something similar to above will allow you to add new subjects without having to alter your table.  Then you will just join the tables on the sub_id to get the list of subjects for each class.

select ta.rollno,ta.StdNm,tc.C_Name,
  tm.mark,
  s.subject
FROM tbl_cmarks tm
INNER JOIN tbl_classes tc
  ON tm.Cid = tc.C_Id
INNER JOIN tbl_admission ta
  ON ta.rollno = tm.rollno
INNER JOIN tbl_subjects s
  on tm.sub_id = s.sub_id
where tm.Cid = 22
  and tm.Examid=9;

See SQL Fiddle with Demo. The above will give you a result in rows, but you could easily apply an aggregate function with a CASE expression to pivot the data into columns. Similar to the following:

select ta.rollno,
  ta.StdNm,
  tc.C_Name,
  max(case when s.subject = 'English' then tm.mark end) Emglish,
  max(case when s.subject = 'Physics' then tm.mark end) Physics
FROM tbl_cmarks tm
INNER JOIN tbl_classes tc
  ON tm.Cid = tc.C_Id
INNER JOIN tbl_admission ta
  ON ta.rollno = tm.rollno
INNER JOIN tbl_subjects s
  on tm.sub_id = s.sub_id
where tm.Cid = 22
  and tm.Examid=9
group by  ta.rollno, ta.StdNm, tc.C_Name;

See Demo.

But if you don't change your current table structure and if you don't know the columns that you are going to return, then you will have to implement a prepared statement to generate dynamic SQL.

First, you will create the list of the classes:

set @sqlList = null;
set @query = null;

SELECT
  GROUP_CONCAT(concat('tm.', subject))
INTO @sqlList
FROM tbl_subjects
where C_Id=22;

See Demo. This will give you the list of classes for each c_id. Once you have the list of classes, then you can add this to the rest of the sql string so the full code will be:

set @sqlList = null;
set @query = null;

SELECT
  GROUP_CONCAT(concat('tm.', subject))
INTO @sqlList
FROM tbl_subjects
where C_Id=22;

SET @query
  = CONCAT('SELECT ta.rollno,ta.StdNm,tc.C_Name, ', @sqlList, '
            FROM tbl_cmarks tm
            INNER JOIN tbl_classes tc
              ON tm.Cid = tc.C_Id
            INNER JOIN tbl_admission ta
              ON ta.rollno = tm.rollno
            where tm.Cid = 22
              and tm.Examid=9');

PREPARE stmt FROM @query;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

See SQL Fiddle with Demo


Update request using table alias Executing this request:

update table t1 set t1.column = 0 where t1.column2 = 1234

Getting this error:

  column "t1" of relation "table" does not exist

This request runs fine in MySQL.
Why do I get this error in PostgreSQL?

I'm not sure if that's your desired syntax or not. Check your syntax for UPDATE

Currently, that's

[ WITH [ RECURSIVE ] with_query [, ...] ]
UPDATE [ ONLY ] table_name [ * ] [ [ AS ] alias ]
    SET { column_name = { expression | DEFAULT } |
          ( column_name [, ...] ) = ( { expression | DEFAULT } [, ...] ) |
          ( column_name [, ...] ) = ( sub-SELECT )
        } [, ...]
    [ FROM from_list ]
    [ WHERE condition | WHERE CURRENT OF cursor_name ]
    [ RETURNING * | output_expression [ [ AS ] output_name ] [, ...] ]

So if you provide table t1, it's getting parsed as a table named table. Actually, to do that you need to have it in quotes "table" t1 which you're doing, or your library is doing.

As a design note, don't do that. In fact, don't name anything any SQL keywords.
But, if you want to have some fun and see what's happening we can play..

Let's create some text data,

CREATE TABLE "table" AS
SELECT x AS column, x AS column2
FROM generate_series(1,12345) AS t(x);

Now we can try your original query and get your original result,

UPDATE "table" t1 SET t1.column=0 WHERE t1.column2=1234;
ERROR:  column "t1" of relation "table" does not exist
LINE 1: UPDATE "table" t1 SET t1.column=0 WHERE t1.column2=1234;

And that's the problem you're getting. As with the table, if you're going to use a SQL keyword, you need to quote it. Interestingly, that's not enough here.

UPDATE "table" t1 SET t1."column"=0 WHERE t1.column2=1234;
ERROR:  column "t1" of relation "table" does not exist
LINE 1: UPDATE "table" t1 SET t1."column"=0 WHERE t1.column2=1234;

In addition to that, it seems that table aliasing is not supported in the SET list, regardless of whether or not the column is reserved keyword.

UPDATE "table" t1 SET "column"=0 WHERE t1.column2=1234;

Why it's currently Working As Designed

Why you can not use aliases, xocolatl from IRC helps with that,

  < xocolatl> EvanCarroll: the reason you can't use the alias on the left of the = is because of composite types

  < xocolatl> EvanCarroll: so, it's not a bug but WAD

So in code to CREATE a table with a custom composite type an execute an UPDATE on it.

CREATE TYPE foo AS ( x int, y int );

CREATE TABLE foobar AS
  SELECT v::foo AS mycol
  FROM ( VALUES (1,2), (2,100) ) AS v;

UPDATE foobar SET mycol.x = 9;

So the syntax that permits the . is mycol.type-address, not tablealias.col-name.

Solving the ambiguous syntax problem

If that didn't make sense, any behavior but this behavior would give you an ambiguous syntax,

CREATE TYPE foo AS ( mycol int, x int );

CREATE TABLE mytable AS
  SELECT v::foo AS mycol, 1 AS x
  FROM ( VALUES (1,2), (2,100) ) AS v;

UPDATE mytable AS mycol SET mycol.x = 9;

What does mycol.x refer to there? As is it's not ambiguous, table-referencing and table-aliasing is disabled, so it's definintely 100% of the time a composite-type named mycol, on the table mytable.


Write update in more efficient way with joins How can I write this update with joins in 'more sql' way? I have been told that this is weird coding:

-- Updates recipient's account
    UPDATE table1
    SET balance = balance + @amount
    WHERE accountID = @recipientID
        AND -- check if a currency is the same in both accounts
        (
            SELECT t.CurrencyID
            FROM table1 t
            WHERE t.accountID = @payerID
            ) = (
            SELECT t.CurrencyID
            FROM table1
            WHERE t.accountID = @recipientID
            );

You can use an INNER JOIN on CurrencyID to join table1 back on itself but filter the table being updated to records where accountID = @recipientID and the joined table is filtered where accountID = @payerID

In this fashion, only record meeting the WHERE clause for both tables that also matches CurrencyID values will be included in the UPDATE.

UPDATE t1
SET t1.balance = t1.balance + @amount
FROM table1 t1
INNER JOIN table1 t2 ON t1.CurrencyID = t2.CurrencyID
WHERE t1.accountID = @recipientID
    AND t2.accountID = @payerID


Conversion failed when converting date and/or time from character string I have a problem, because I have been using this query without problem... until now:

UPDATE T1
SET ORDINAL = DATEDIFF(DAY, T2.Opening_Date, T1.Date)
FROM FactTransactions T1
INNER JOIN DimStore T2 ON T1.cod_store = T2.cod_storeKey

But now It gave me an error:

  Conversion failed when converting date and/or time from character string

I have no idea what's going on. Here are the columns:

Ordinal(numeric,null)
Opening_date(varchar, not null)
Date(varchar, not null)
cod_store(int,not null)
cod_storekey(PK,int, not null)

You are storing dates as strings - why? Opening_Date and Date should be date or datetime, not varchar. But before you can fix that, you need to identify the rows that are causing the conversion problem:

SELECT cod_store, [Date]
FROM dbo.FactTransactions
WHERE ISDATE([Date]) = 0;

SELECT cod_storekey, Opening_Date
FROM dbo.DimStore
WHERE ISDATE(Opening_Date) = 0;

And now that you've fixed the question and I know you're using 2012 and not 2008 R2, it might be cleaner to use TRY_CONVERT(), especially since it will allow you to identify any rows where the month and day are transposed incorrectly. For example, assuming you expect dates to be stored as mm/dd/yyyy strings:

SELECT cod_store, [Date]
FROM dbo.FactTransactions
WHERE TRY_CONVERT(datetime, [Date], 101) = 0;

SELECT cod_storekey, Opening_Date
FROM dbo.DimStore
WHERE TRY_CONVERT(datetime, Opening_Date, 101) = 0;

In addition to identifying garbage rows where users have stored nonsense like floob and 9992-13-36 as "dates," this will also identify rows where users have stored 13/07/1999 instead of 07/13/1999 (but there is no way to know if 05/06/2000 is meant to be May 6 or June 5).

Now you need to fix those rows before you can correct the tables.

ALTER TABLE dbo.DimStore ALTER COLUMN Opening_Date date; -- or datetime;
ALTER TABLE dbo.FactTransactions ALTER COLUMN [Date] date; -- or datetime;

You might also consider renaming the Date column to be (a) less vague and (b) not a reserved word.

If you can't fix the tables, then you need to change your query:

UPDATE T1
SET ORDINAL = DATEDIFF(DAY,
  CASE WHEN ISDATE(T2.Opening_Date) = 1 THEN T2.OpeningDate END,
  CASE WHEN ISDATE(T1.[Date]) = 1 THEN T1.Date END)
FROM dbo.FactTransactions AS T1
INNER JOIN dbo.DimStore AS T2
  ON T1.cod_store = T2.cod_storeKey
WHERE ISDATE(T2.Opening_Date) = 1
  AND ISDATE(T1.[Date]) = 1;

And @RLF brought up a great point, too; if you can't fix the table, then the date columns could contain data that represent a specific date (say, September 7) but be entered in the wrong format (e.g. on a US English system, entered as a string in British format, 7/9/2015). So really, you need to fix the table and stop storing these things as strings.

Some other useful material:

Bad habits to kick : mis-handling date / range queries
Bad habits to kick : avoiding the schema prefix


How do I write a mysql query to combine data from related tables into a single summary? I'm having trouble defining the data in a query for a chart. I want to summarize data from the first table and combine it with a data summary from the second table.

Products   - (serial_number, date)
Warranties - (id, lookup_to_serial_number, warranty_issue)

What I want to do is to get the total number of products grouped by month and the total number of Warranties grouped together by month of production. It is a one product to many Warranties relationship. I can figure out how to get one or the other, but not both. The product serial number is unique but can have many Warranties on a single product.

The chart will be total products produced in a month compared to total number of Warranties on the products produced in that month. Both totals will be plotted on the same chart.

This is what I have so far :

select count(warr_id), prod_date
from product_t, warr_t
where prod_date is not null and serial_number = product_look
group by year(prod_date), month(prod_date)

union

select count(serial_number), prod_date
from product_t
where prod_date is not null
group by year(prod_date), month(prod_date)

This returns the results I'm looking for, but is there a way to differentiate between the two sets of data? I just came up with this an hour ago.

Yes, there is a way. Just add an identifier column to the selection list. E.g.:

select count(warr_id), year(prod_date) AS prod_year, month(prod_date) AS prod_month, 'W' as type
from product_t, warr_t
where prod_date is not null and serial_number = product_look
group by year(prod_date), month(prod_date)

union

select count(serial_number), year(prod_date) AS prod_year, month(prod_date) AS prod_month, 'P' as type
from product_t
where prod_date is not null
group by year(prod_date), month(prod_date)

When you build the chart for the query use the type column for different series


How to manage a huge database? We are building a service witch require a huge database. We are actually planning on getting more than 100 millions rows in our biggest table.

So, what are the best options to store all this data in our database and afterward access to this data (search in the database) ?

No need to say that it must be fast.

Thanks.

100 million rows in a table does not make a database "huge" by today's standards

This is also not MySql related in most ways - because you leave pretty fat the server software level and talk hardware.

The standard answer to this is that it all depends on your workload profile. Is it transactional, reporting, OLAP?

You mentioned that your largest table is 100 million rows. How does this compare to the other tables? Are they much smaller, or about the same size?

You may want:

As much RAM as you can. It is nice that servers these days should be able to load this size table in memory, isn't it?
As much disk performance as you can get.

This is where special hardware gets into the game fast. The last project I did in this area had 3x768gb flash disks with write back cache and 2x96gb with 48 cores each. However, this was an Oracle RAC and probably way beyond what MySQL could handle.

Anyhow, your best bet would be to go SSD for the IO side, memory for the buffer side, and with something in the 100 million row range you should be able to keep a significant part of that in memory. Using a 64gb server and 48gb buffer for that table I get  480 bytes per row (table, index). That wont be exact, but it gives you an idea that you can easily (64gb servers are quite cheap) fit the data in memory.

Here is a hint: Databases are not large when the fit in memory. They are large when you can not fit them into decent server hardware.


dba_registry_history vs dba_registry_sqlpatch and OJVM patch Should dba_registry_sqlpatch-view list all installed patches and therefore it would also list OJVM patches?

Or OJVM patches are only listed in dba_registry_history-view?

If the database was patched properly, DBA_REGISTRY_SQLPATCH should list the OJVM patch. Below output is from one of my sandbox environments:

SQL> select DESCRIPTION, ACTION, STATUS, ACTION_TIME from dba_registry_sqlpatch;

DESCRIPTION                                                       ACTION          STATUS          ACTION_TIME
----------------------------------------------------------------- --------------- --------------- --------------------
Database PSU 12.1.0.2.190716, Oracle JavaVM Component (JUL2019)   APPLY           SUCCESS         2019-10-01 15:31:58
DATABASE PATCH SET UPDATE 12.1.0.2.190716                         APPLY           SUCCESS         2019-10-01 15:31:59

SQL> select comments from dba_registry_history;

COMMENTS
--------------------------------------------------------------------------------
RDBMS_12.1.0.2.0DBPSU_LINUX.X64_161210


MySQL Fulltext match with forward slashes I'm trying to run a fulltext match against a product database, where the product names have forward slashes in them. These are staple sizes, so it's quite important that they get matched.

However, for every query I execute with a forward slash, I get 0 results.

The query I'm using is:

SELECT `product`.*
FROM `product`
WHERE MATCH (`product`.`name`) AGAINST('26/6')

I have also tried

SELECT `product`.*
FROM `product`
WHERE MATCH (`product`.`name`) AGAINST('\"26/6\"')

However, running the following returns 6 results, as expected:

SELECT `product`.*
FROM (`product`)
WHERE `product`.`name` LIKE '%26/6%'

The table is MyISAM and the product.name is varchar(255), with a FULLTEXT index on that column alone.
If I search using fulltext for something else, like 'tool', I get the results as expected, and the server is configured for 4 character or more matching.

What do I need to do to make this fulltext match work?

You are looking for Adding a Collation for Full-Text Indexing

The example explains how to treat the hyphen character ('-') as a word character.


How to join all combinations from two tables with a common column I have a list of suppliers who supply a list of countries. For example:

Supplier A supplies to:

USA
France
China

and another list of suppliers with their suppliers' factories For example:

Supplier A manufactures from:

UK
Germany

How can I build the list (in rows) of all possible combinations of (suppliers, countries, factories)? I have more than 10.000 suppliers. For example, for Supplier A:

USA-UK,
USA-Germany
France-UK
France-Germany
China-UK
China-Germany

Could someone please help me with this? I have been trying to work on it for weeks using Excel and then Access.

Using SQL Server, because I do not have Access installed, hopefully this is generic enough to be useful to you:

Sample tables and data

CREATE TABLE SupplierCountry
(
    SupplierName varchar(50) NOT NULL,
    CountryName varchar(50) NOT NULL
);

INSERT SupplierCountry
    (SupplierName, CountryName)
VALUES
    ('Supplier A', 'USA'),
    ('Supplier A', 'France'),
    ('Supplier A', 'China');

SupplierCountry

╔══════════════╦═════════════╗
║ SupplierName ║ CountryName ║
╠══════════════╬═════════════╣
║ Supplier A   ║ USA         ║
║ Supplier A   ║ France      ║
║ Supplier A   ║ China       ║
╚══════════════╩═════════════╝

SupplierFactory

CREATE TABLE SupplierFactory
(
    SupplierName varchar(50) NOT NULL,
    CountryName varchar(50) NOT NULL
);

INSERT SupplierFactory
    (SupplierName, CountryName)
VALUES
    ('Supplier A', 'UK'),
    ('Supplier A', 'Germany');

╔══════════════╦═════════════╗
║ SupplierName ║ CountryName ║
╠══════════════╬═════════════╣
║ Supplier A   ║ UK          ║
║ Supplier A   ║ Germany     ║
╚══════════════╩═════════════╝

Query:

SELECT
    SC.SupplierName AS Supplier,
    SC.CountryName AS SupplyCountry,
    SF.CountryName AS FactoryCountry
FROM SupplierCountry AS SC
JOIN SupplierFactory AS SF
    ON SF.SupplierName = SC.SupplierName;

Output:

╔════════════╦═══════════════╦════════════════╗
║  Supplier  ║ SupplyCountry ║ FactoryCountry ║
╠════════════╬═══════════════╬════════════════╣
║ Supplier A ║ USA           ║ UK             ║
║ Supplier A ║ France        ║ UK             ║
║ Supplier A ║ China         ║ UK             ║
║ Supplier A ║ USA           ║ Germany        ║
║ Supplier A ║ France        ║ Germany        ║
║ Supplier A ║ China         ║ Germany        ║
╚════════════╩═══════════════╩════════════════╝


Add full text index facility to SQL Server instance Can anyone help me to add full text index facility to a SQL Server instance? FYI  it is cluster environment, SQL Server 2012 Developer Edition.

Running select serverproperty('isfulltextinstalled') returns value 0.

When you install a clustered instance, the fulltext components are installed by default and it is not possible to de-select them. If you don't have fulltext installed, you're in big trouble. Are you sure the components are not there? What does SELECT SERVERPROPERTY('IsFulltextInstalled') return? If it returns false, I'm afraid you will have to reinstall this instance. I had the same thing happening to me once and there was no way other than reinstalling from scratch.


SQL AlwaysOn & System Center Configuration Manager TP 4 1601 Recently Microsoft released Technical Preview 4 for Configuration Manager (1601).  Per TechNet, "Configuration Manager now supports using a SQL Server AlwaysOn availability groups to host the site database. When you install a new site, you can direct setup to use the availability group instead of a normal instance of SQL Server."

They then note "Successful configuration and use of availability groups requires you to be comfortable with configuring SQL Server availability groups, and relies on documentation and procedures provided in the SQL Server documentation library."

Currently, the best practice for creating fault tolerance and to avoid a single point of failure for the Configuration Manager Site Database is to install SQL replicas at each management point.  I've watched several overview videos (from 2013) to get a better understanding of how AlwaysOn works.

In the DBA community, is AlwaysOn a better solution to providing fault tolerance and avoiding a single point of failure over SQL replicas?

Full discloser:  I'm learning Configuration Manager and have very basic knowledge of SQL Server so any advice you have or suggestions would be greatly appreciated.

Availability Groups have rapidly become Microsoft's key HA strategy. They are slightly easier to setup and maintain than SQL Server Failover Clustering but provide slightly different benefits and drawbacks. FCIs are purely for SQL Instance level HA solutions whereas Availability Groups provide HA at the Availability Group (group of databases) level.

They are not without their faults or restrictions but their simplicity, flexibility and integration with Azure make them compelling to use for SQL Server, SharePoint and Systems Center HA (where supported).


Are logs in the LDF file being removed after a checkpoint? Using the fn_dblog I can get the active portion of the online transaction log of my database.

But after every checkpoint I can only see 2 entries showing the beginning of the checkpoint LOP_BEGIN_CKPT and the end of the checkpoint LOP_END_CKPT.

I'm wondering whether if the previous logs removed after every checkpoint or they are only ignored because the dbi_checkptLSN value that located in the bootpage indicate to the latest LSN?

If it is result of the dbi_checkptLSN am I able to move that indicator, and if yes how?

If not, and if the logs is not removed how can I access the previous logs?

If your database is in simple recovery the log might be truncated after a checkpoint.

This does not always occur though for reasons discussed in Factors That Can Delay Log Truncation (the most obvious being an open transaction)

To see the inactive portion of the log you can use the undocumented DBCC log command.

DBCC log(0,1) /*First parameter is database id with 0 as current DB*/

or trace flag 2537 in conjunction with sys.fn_dblog

DBCC TRACEON(2537)

SELECT *
FROM sys.fn_dblog(NULL,NULL)

Of course there is no guarantee that the thing you are looking for will be there. It may be overwritten at any moment.


PostgreSQL: streaming binary data to a bytea field is very slow I am trying to stream binary data into a bytea field.
The process is quite simple:

loop until the end of the incoming stream
  UPDATE myTable SET data = data || $chunk WHERE id = myId

$chunk is a binding for the current stream chunk
The row (myId) already exists with NULL data
each chunk is about 64k
the final data is around 4MB

Everything is working properly expect that concatenating data become more and more slow as chunks are stored.

Since I need to keep my SQL quite portable, I cannot use "PostgreSQL Large Objects"

Is it possible to optimize this process ?

maybe by pre-allocating with with an empty data, and then use overlay() to place each chunk ?

Updating a row in a multiversion model like postgres means creating a second copy of that row with the new contents. Physically, there is no in-place update: UPDATE is similar to DELETE + INSERT the new contents.

So in the above loop, the first chunk, instead of being written once, is written N times, the second chunk is written N-1 times, the third N-2 times and so on.

To make it worse, all these writes have to go to the WAL files for journaling too, and all the intermediate versions of the rows will need be picked up by the autovacuum process to be eventually discarded.

Assuming that these chunks cannot be assembled on the client and must be streamed, it might help to do something like this:

CREATE TEMPORARY sequence s;
CREATE TEMPORARY TABLE buffer(seq int default nextval('s'), chunk bytea);

-- buffer in temporary storage
LOOP
   INSERT INTO buffer(chunk) VALUES ($chunk)
END LOOP

-- assemble in final storage
INSERT INTO permanent_table(data)
   SELECT string_agg(chunk,''::bytea order by seq) FROM buffer;

TRUNCATE (or DROP) TABLE buffer;

At least the chunks will be written only twice (once in buffer storage and once in final durable storage), and only once in the journal, as the temporary table won't be WAL-logged.


In partition definition filegroup name appears twice, what does that mean? short question regarding partitions, consider the following partition definition:

CREATE PARTITION SCHEME [MSAppServerPS_ASWcfEventsTable] AS
PARTITION [MSAppServerPF_ASWcfEventsTable] TO ([PRIMARY], [PRIMARY])
GO

From reading about this here I understand, that this will create the partition on the socalled primary filegroup. Unfortunately in the examples on that page I did not see one example that used the same filegroup name twice.

Can someone explain to me what that means?

A partition scheme works with a partition function.

you start by creating the function where you will define the range. then the partition scheme will tell you where to store the data according to that range.

Or, to say it in another way, that particular partition scheme is just stating that for values that fall under the first condition store it in the PRIMARY filegroup, and for values that fall into the the second condition use the PRIMARY FILEGROUP.

you'll sometime see it in examples as CREATE PARTITION ... ALL TO ([PRIMARY])

Hope this helps


How to get the definition of a database trigger? when I run the following script in one of my databases:

SELECT * FROM SYS.triggers

when I try to find out about sys.triggers

  Contains a row for each object that is a trigger, with a type of TR or
  TA. DML trigger names are schema-scoped and, therefore, are visible in
  sys.objects. DDL trigger names are scoped by the parent entity and are
  only visible in this view.

How then, do I get the database triggers definition?

how about

select m.definition from sys.triggers t
inner join sys.objects o on t.object_id = o.object_id
inner join sys.sql_modules m on m.object_id = o.object_id


Non-Replicating commands from (master) Mysql 5.5.40 to (slave) MariaDB 10.0.19 CRUD replicates just fine. But we noticed that DB structure altering commands such as ALTER TABLE and/or adding/updating columns to a table and even creating new ones do not replicate to the slave at all.

Setup

This is the content of our my.cnf files:

  Replication Setup A

Where: (master) Mysql 5.5.40 > (slave) MariaDB 10.0.19

Master:

server-id = 1002
binlog-do-db = db_1
binlog-do-db = db_2
binlog-do-db = db_3
sync_binlog = 1
expire_logs_days = 30
innodb_flush_log_at_trx_commit = 1

Slave:

server-id = 1005
replicate-do-db = db_1
replicate-do-db = db_2
replicate-do-db = db_3
slave-parallel-threads=4
slave_compressed_protocol = ON
slave-skip-errors = all
log-slave-updates = ON

Strangely, DB structure altering commands works just fine on a different server pair with an *almost similar replication setup:

  Replication Setup B

Where: (master) MariaDB 10.0.17 > (slave) MariaDB 5.5.41

Master:

server-id = 1004
bind-address = "0.0.0.0"
binlog-do-db = db_a
binlog-do-db = db_b
binlog-do-db = db_c
binlog-do-db = db_d
binlog-do-db = db_e
binlog-do-db = db_f
binlog-do-db = db_g
sync_binlog = 1
expire_logs_days = 30
innodb_flush_log_at_trx_commit = 1

Slave:

server-id = 1003
bind-address = "0.0.0.0"
replicate-do-db = db_a
replicate-do-db = db_b
replicate-do-db = db_c
replicate-do-db = db_d
replicate-do-db = db_e
replicate-do-db = db_f
replicate-do-db = db_g
slave_compressed_protocol = ON
slave-skip-errors = all

  Notes for both replication pairs:

  Names of the databases were obscured.
  Parts of the my.cnf file where i think replication is configured are only posted.

Questions

Is this a bug or an undocumented feature?
Did i do something wrong on my setup?
What could be other factors that might effect to this behaviour?
If there really are (query)commands that aren't supported for
replication, what are these?

4.a) Mysql/MariaDB version setups that has the same effect?

I tried looking at the error logs of the slave for any hints and used structure altering query commands as search keywords but to no avail. Maybe I'm not looking in the right direction?

Update (2015-06-04)

Further testing ensued. As suggested by this SO post, I might have to remove or comment out replicate-do-db from my.cnf; So I did and I found out that:

Creating a new table is replicated.
Altering a table is not replicated.
CRUD transactions were not replicated.

Also, things just got stranger for Replication Setup B where everything was just supposed to work.

One DB failed to replicate an ALTER TABLE command just yesterday and we only found out about it now.
After the incident, I tried testing executing ALTER TABLE commands to all other DBs of the same server and it replicated just fine. In this test, I am unable to remove replicate-do-db from my.cnf as restarting the server is currently not an option.

USE db_99;
do something with db_1 or 2 or 3;

will not replicate because of your binlog-do-db and replicate-do-db.  (The latter is redundant in your case.)

This is because replication (except for "wild" versions) is based on the USE, not on any explicit db spelled out in the statement.

Is it a "bug"?  Well, a lot of people get burned by it.


Forwarding records in #temp table I have a temp table which I update many times based on joins (each time almost all the rows). I have kept it as a heap since if I don't get an inner loop join then after each update I get a sort in the execution plan. And also the other tables which I join on need to have a unique index if I want to get the correct join (i.e. so I don't get a sort after the join). This answer helps explain the problem: Updating a table efficiently using JOIN
This was just an explanation for why the table is a heap.
Now my question is: how much do these forwarded records hurt the performance of my SP? Say I have 10 updates and with each update I get more forwarded records. Should I rebuild the heap temp table after say five updates? How can I figure out how many times to rebuild the heap?
So basically my question is how to deal with a table that gets updated many times and with each update basically all rows for a particular column get updated. This is a reporting query.
If I keep the table has a heap I get extra IO and if I create a clustered index I need to get all the statements to do an inner loop join and even if I achieve this I still will get page splits...
I have not tried this yet but I am thinking to change the string columns to fixed length columns in order to avoid forwarded records, but that seems to increase the IO also, at least at first...

The question of when does forwarded records hurt a particular query's performance is too broad to answer, and will vary depending on what the query is actually doing.
Nowhere in the DBA.StackExchange post you linked recommends using a Heap table, rather it shows how to make use of an indexed table by forcing alternative execution plans with proper query hinting to solve the performance problem, when applicable. If you were previously experiencing performance issues that matched the question of that post, I recommend taking up Paul White's advice, but in either case the recommendation would be to index your table, especially if you're joining to it frequently.
Alternatively, Tara Kizer has a great article on How To Fix Forwarded Records which essentially ends with the suggestions of either a temporary fix by rebuilding the table, or a permanent fix by adding a clustered index to it.
And if you're still deadset on keeping it as a Heap then to directly answer your question of "when" to rebuild the table, it generally is recommended to do so based on the Heap fragmentation. That's a more easily measurable number to use to determine when's the right time to rebuild your table. Some people choose to rebuild after 20% fragmentation, others choose 50%, some wait until the table is mostly fragmented, it just depends on your situation and the particular queries you're running against the Heap table. But you can gather more information about Heap fragmentation and rebuilding the table in SQL Server Heaps, and Their Fragmentation.


A constant scan takes either 0 seconds or 2-3 minutes A query like the one below that is guaranteed not to return any rows, takes anything from 0 to 160 seconds on one of our servers:

select col1, col2, col3
from tab1
where 0 = 1

Two weeks ago, this happened six times in a 48 hr interval. Last week the same query took ~0 seconds. I have logs of our application's SQLs but haven't found any suspects yet. Besides, I thought a top 0/where 0=1 type query never hit the data pages, so it should be resistant to row/page/table-level data locks? The schema is not touched by any (known) SQLs.

Since the problem is not consistent, and the server is under very heavy load I'd like to understand the theory behind what is happening before attaching SQL profiler. Other queries run without problems during these delays. A known problem in the application is a high number of dynamically created SQL queries - around 200k unique queries of 850k total (logged) queries over a period of 48 hrs, can this cause problems like this?

The server is running SQL Server 2005 standard edition, 96 GB RAM, disks on SAN and 4 CPUs/16 cores. Database files and filegroups are well optimized and shouldn't be a problem (but we're looking into this separately).

Any pointers where to look is greatly appreciated.

Edit: Perfect! Replayed the query to add the execution plan, and it took 1min 35secs. Here's the execution plan and screenshot showing the query duration:

Edit 2: statistics time details for a second run. Seems to be consistently slow right now, so we'll be attaching profiler and perfmon:

SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 97402 ms.
SQL Server parse and compile time:
   CPU time = 0 ms, elapsed time = 0 ms.

It appears as though even with a ... WHERE 0 = 1 clause that there will still be a requirement for an intent shared (IS) lock on the table.  Let's prove this:

I will start out by creating a test table:

use TestDb1;
go

create table dbo.MyTestTable1
(
    Id int identity(1, 1) not null,
    SomeInt int not null
);
go

insert into dbo.MyTestTable1 (SomeInt)
values (10), (20), (30), (40), (50);
go

Now that I have my test table, in one session (query window) I'm going to execute the following to put an exclusive (X) lock on dbo.MyTestTable1:

use TestDb1;
go

begin tran;
    select
        Id, SomeInt
    from dbo.MyTestTable1 with (tablockx);
--commit tran;

I can verify the exclusive lock by looking at the sys.dm_tran_locks DMV.  Then in another session (new query window) I do exactly what your query does:

use TestDb1;
go

select
    Id, SomeInt
from dbo.MyTestTable1
where 0 = 1;

At first glance I see that it isn't completing.  Looking at sys.dm_exec_requests, I see exactly why this is the case:

select
    r.session_id,
    r.status,
    r.wait_type,
    r.wait_time,
    r.wait_resource,
    r.blocking_session_id
from sys.dm_exec_requests r
cross apply sys.dm_exec_sql_text(r.sql_handle) st
where st.text like '%where 0 = 1%'
and r.session_id <> @@spid;

I can see here that my ... WHERE 0 = 1 query is waiting on an IS lock for this object (that object_id translates to dbo.MyTestTable1).

I am by no means saying that concurrency is your problem, but by the sounds of it you are exhibiting the symptoms.  The example above is to prove that your aren't exempt from locking and blocking even with a WHERE clause that'll never return data.

All we can do is guess, so what you need to do when it's "taking a long time" is to see exactly what that request is doing that is taking so long.  If it's waiting on something, then see what it's waiting on.


Error while creating Extended Events on 2008 SQL Server I am trying to create an extended event to capture the login information of my server. While creating an extended event on SQL 2008 using the below query

CREATE EVENT SESSION [SA_Monitor] ON SERVER
ADD EVENT sqlserver.login(
ACTION(sqlserver.server_principal_name,sqlserver.sql_text,sqlserver.username)

I am getting the following error.

  The event name, "sqlserver.login", is invalid, or the object could not
  be found.

On Initial investigation I found that creating extended events using SSMS was introduced from 2012 and the only method to create EE is through SQL query. Is there a generic query that can help me capture this information in the 2008 server.

You can use a logon trigger to record the event info.

USE yourDatabase;     -- select name from sys.databases;

--IF (OBJECT_ID('LogonInfo') IS NOT NULL)   -- select name from sys.tables where name = 'LogonInfo'
--  DROP TABLE LogonInfo;

CREATE TABLE LogonInfo
(
    LogonTime   DATETIME,
    LoginName   VARCHAR(50),
    ClientHost  VARCHAR(50),
    LoginType   VARCHAR(50)
);

--  SELECT * FROM yourDatabase..LogonInfo ORDER BY LogonTime DESC
--  SELECT DISTINCT LoginName FROM yourDatabase..LogonInfo ORDER BY LoginName

USE [master];

--IF EXISTS (SELECT * FROM sys.server_triggers WHERE [type] = 'TR' AND [name] = 'LogonInfoTrigger')
--  DROP TRIGGER LogonInfoTrigger ON ALL SERVER;

CREATE TRIGGER LogonInfoTrigger
ON ALL SERVER WITH EXECUTE AS '<serviceAccount>'   -- select name from sys.server_principals;
FOR LOGON
AS
BEGIN
    -- Check if the log table exists before trying to do the insert
    -- Otherwise the trigger will error and not allow anyone to log in
    IF (OBJECT_ID('yourDatabase..LogonInfo') IS NOT NULL)
    BEGIN
        DECLARE
            @LogonTriggerData xml,
            @EventTime datetime,
            @LoginName varchar(50),
            @HostName varchar(50),
            @LoginType varchar(50);

        SET @LogonTriggerData = eventdata();

        SET @EventTime = @LogonTriggerData.value('(/EVENT_INSTANCE/PostTime)[1]', 'datetime');
        SET @LoginName = @LogonTriggerData.value('(/EVENT_INSTANCE/LoginName)[1]', 'varchar(50)');
        SET @HostName = @LogonTriggerData.value('(/EVENT_INSTANCE/ClientHost)[1]', 'varchar(50)');
        SET @LoginType = @LogonTriggerData.value('(/EVENT_INSTANCE/LoginType)[1]', 'varchar(50)');

        -- use the if statment if you want to ignore certain accounts
        -- remove the if statement and just run the insert statement if you want to capture all accounts
        IF @LoginName NOT IN ('yourSafeAccount1','yourSafeAccount2')
        BEGIN
            INSERT INTO yourDatabase..LogonInfo (LogonTime, LoginName, ClientHost, LoginType)
            VALUES (@EventTime, @LoginName, @HostName, @LoginType);
        END;
    END;
END;


INFORMATION_SCHEMA.VIEW_TABLE_USAGE only shows tables from the same catalog I'm working with a database that has many cross-database views. While INFORMATION_SCHEMA.VIEWS lists all these views, INFORMATION_SCHEMA.VIEW_TABLE_USAGE and INFORMATION_SCHEMA.VIEW_COLUMN_USAGE do not.

All views in question are in this format:

CREATE VIEW [dbo].[Invoice]
AS SELECT * FROM [otherdb].[dbo].[Invoice]

The following query returns 0 rows:

SELECT
    VIEW_CATALOG
    ,VIEW_SCHEMA
    ,VIEW_NAME
FROM INFORMATION_SCHEMA.VIEW_TABLE_USAGE
WHERE TABLE_CATALOG <> VIEW_CATALOG

Leaving the appropriateness of SELECT * for another discussion, why are the external database references not in the INFORMATION_SCHEMA views and is there any way to get them?

Yes, stop using INFORMATION_SCHEMA for various reasons, including one I didn't mention in that post (yet), which is the fact that they don't handle cross-database dependencies.

Use the catalog views and dynamic management functions instead.

To get just the views, you can use sys.sql_expression_dependencies:

SELECT
  [schema] = s.name,
  [view]   = v.name,
  d.referenced_database_name,
  d.referenced_schema_name,
  d.referenced_entity_name
FROM sys.views AS v
INNER JOIN sys.schemas AS s
ON v.[schema_id] = s.[schema_id]
INNER JOIN sys.sql_expression_dependencies AS d
ON v.[object_id] = d.referencing_id
WHERE d.referenced_database_name IS NOT NULL;

To get the column names too, you can use sys.dm_sql_referenced_entities:

SELECT
  [schema] = s.name,
  [view]   = v.name,
  r.referenced_database_name,
  r.referenced_schema_name,
  r.referenced_entity_name,
  [column] = r.referenced_minor_name
FROM sys.views AS v
INNER JOIN sys.schemas AS s
ON v.[schema_id] = s.[schema_id]
CROSS APPLY sys.dm_sql_referenced_entities
(
   QUOTENAME(s.name) + N'.' + QUOTENAME(v.name), N'OBJECT'
) AS r
WHERE r.referenced_database_name IS NOT NULL
AND r.referenced_minor_name IS NOT NULL;


What privileges do I need in order to grant execute on dbms_rls? I have an automated build script that creates a new user and builds up a schema. I also want to grant execute on dbms_rls to myuser as part of this script, but I am getting this error:

Error starting at line : 3 in command -
grant execute on sys.dbms_rls to chemreg
Error report -
SQL Error: ORA-00942: table or view does not exist
00942. 00000 -  "table or view does not exist"

I can successfully execute grant execute on dbms_rls to myuser if I am running as SYS, but my build script will be running as another user.  Which privileges does that user require in order to grant execute on dbms_rls?

Which privileges does that user require in order to grant execute on dbms_rls?

The grating user should have the execute privilege on DBMS_RLS with GRANT OPTION.

I have two users namely usra and usrb. I have granted execute on DBMS_RLS to usra with admin option. Then usra is able to grant the privilege to usrb.

SQL> conn / as sysdba
SQL> grant execute on dbms_rls to usra with grant option;

Grant succeeded.

SQL> conn usra/usra
Connected.
SQL> grant execute on sys.dbms_rls to usrb;

Grant succeeded.


SQL Server on Linux: Case Sensitivity I haven’t yet tried out SQL Server on Linux, but I have a question regarding case sensitivity.

In MySQL and Oracle (as far as I am aware) table names and database names are case insensitive on Mac & Windows, but case sensitive on Linux. I understand this has something to do with the case sensitivity of the underlying OS.

PostgreSQL, on the other hand, doesn’t work that way.

The question is: are database and table names in SQL Server on Linux case sensitive?

I ask this because I always recommend sticking to the case in which names are defined, but I want to check whether it is also future-proofing the project.

It has nothing to do with the underlying OS. It has to do with the collation of the instance and the collation of the database. Here is an example that shows that database names, table names, column names, and data are not subject to case sensitivity by default.

If you want those things, you can turn them on, by specifying a case-sensitive collation for the column, for the database, or - I think - for the instance. (The images I've been using for SQL Server Docker containers don't have the traditional setup package where you can pick instance collation prior to setup, and changing instance collation after the fact might not be fun.)

Perhaps you're thinking about physical files? I know when I was moving my stuff from IIS to a Unix host I had to change references to things like file.css because the file on disk was actually File.css and the server wouldn't be able to locate it. SQL Server doesn't work that way.

Regardless

Yes, you should absolutely stick to the proper casing of things, and that future-proofs you no matter what operating system you use to host SQL Server. Anybody can come along and change the collation of various bits of your installation (or deploy your code to a place where the collation is already different). See these articles:

Bad Habits : Being CarELesS about cAsE
#BackToBasics : Why I use lower case for data type names (now)


Notification on long-running query or deadlock in SQL Server 2008 R2? I'd like to know if there is a way to send a notification on deadlock? If so what queries would be required. I understand that SQL Server takes care of deadlocks, I simply would like information on the queries involved.

I found the following to determine long-running queries:

SELECT
    creation_time
    ,last_execution_time
    ,total_physical_reads
    ,total_logical_reads
    ,total_logical_writes
    , execution_count
    , total_worker_time
    , total_elapsed_time
    , total_elapsed_time / execution_count avg_elapsed_time
    ,SUBSTRING(st.text, (qs.statement_start_offset/2) + 1,
    ((CASE statement_end_offset
        WHEN -1 THEN DATALENGTH(st.text)
        ELSE qs.statement_end_offset END
    - qs.statement_start_offset)/2) + 1) AS statement_text
FROM sys.dm_exec_query_stats AS qs
    CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st
where total_elapsed_time >= 300000000 --5 min
ORDER BY total_elapsed_time / execution_count DESC;

I'd like to know if the above is the right way to go, or is there a better way to determine if any query takes longer than a specific interval say 5 min as shown?

Thanks

You can configure alerts for both of these with SQL Agent.  Create a new alert and select type "SQL Server performance condition alert"

For long running queries, choose Object "MSSQL$InstanceName:Transactions" and Counter: Longest Transaction Running Time. Configure the values, and the alert notification options, and you're good to go.

For deadlocks, the Object is "MSSQL$InstanceName:Locks" and the Counter is "Number of Deadlocks/sec"

If you want more fine grained control of the deadlock notification, check this out:
http://www.sqlservercentral.com/articles/Administration/3243/


How to calculate query that access system view cost in Oracle? For a query that returns if there are any active transactions within current session ,

  SELECT COUNT(*)
  FROM
  v$transaction t
  INNER JOIN v$session s ON (t.ses_addr = s.saddr )
  INNER JOIN v$mystat m  ON  (s.sid = m.sid )
  WHERE ROWNUM = 1;

EXPLAIN shows 0 cost. However, that doesn't seem to be true. Periodically running this query in high load environment causes server to waste almost all its resources on it.

What is the right way to estimate impact of such queries (I believe the same problem occurs not for just this particular query, but anything that involves system views)?

Thanks.

Some (all?) dynamic V$ views are based not on dictionary tables but on memory structures, so the traditinoal stats are not collected and therefore the optimizer can't compute the cost of queries on those views.

However,  the explain plan cost is only a rough approximation of the expected work so it shouldn't be your only way to evaluate the efficiency of a query. The true cost of a query can be revealed with a trace. Trace your actual query and all alternative queries and the tkprof will reveal which one is the most efficient.

For your query in particular, it seems you want to determine if your current session has uncommited work. Alternate queries are described in the following SO questions :

How can I tell if I have uncommitted work in an Oracle transaction?
How to find out if there is a transaction pending?

Can you try the queries in these questions and tell us which one is the most efficient (on your high load environment)?


Update fields that contains certain prefix by lookup table I have a temporary table #NAMEOLD_NAMENEW which contains a column NAMEOLD and a column NAMENEW where every row contains the old and new value of a value to be updated. This table consists of roughly 2.000 rows. I created a clustered index on NAMEOLD.

The temporary table will be used to update certain fields in the database. I know which columns in which tables need to be updated (roughly 25 columns over 20 tables).

Now, the fields that need to be updated contain the string DOMAIN\ as a prefix and after that a NAMEOLD value of the temporary table.

What I want to do is: use the temporary table to replace the full value of the string DOMAIN\ plus the string after that with the appropriate value in the #NAMEOLD_NAMENEW table.

Example:

Example data in #NAMEOLD_NAMENEW:

  NAME_OLD NAMENEW
  user1 user5
  user2 user6

Example data in TABLE1:

  ColumnWithName1
  DOMAIN\user1
  DOMAIN\user2

Now running the query should update TABLE1 to:

  ColumnWithName1
  user5
  user6

I think that scanning the full table multiple times will not perform well enough (SELECT on certain tables already takes minutes).

What I thought of is typing out all the columns that need to be updated, and then updating them with a regex with a capture group. Something like this (non-working code):

UPDATE [dbo].[Table1]
SET [ColumnWithName1] = (SELECT [NAMENEW] from new_old WHERE [NAMEOLD] = [/1]) -- first CAPTUREGROUP??
FROM #NAMEOLD_NAMENEW new_old
WHERE [ColumnWithName1] like '%DOMAIN\(.*)'

Here the capture group does not work since it does not seem to exist in T-SQL. Furthermore, using the nested SELECT is I believe a bad practice for some reason.

Is it possible to do something like this in T-sql? Or is there perhaps even a better approach?

Typically the pattern would be something like this:

UPDATE t
  SET t.ColumnWithName1 = 'DOMAIN\\' + new_old.NAMENEW
  FROM dbo.Table1 AS t
  INNER JOIN #NAMEOLD_NAMENEW AS new_old
  ON t.ColumnWithName1 = new_old.NAMEOLD
  WHERE <some filter on a pattern>;

But your question doesn't really explain how to identify the specific columns that match. Is [/1] a real string value, a pattern, or something else? Is \(.*) supposed to be a regular expression (which doesn't exist in native T-SQL)? Is the User column a typo or do you have multiple columns that have this domain prefix?