Can't reduce cost of query plan and get rid of Key Lookup because of cursor I have tried to create a non-clustered index on the fields that are in the output list which are created_by and Chk1002 .I don't have a column called Chk1002  anywhere .
I have read here that it has to do with the Cursor .

Is there any way I could keep the Cursor and get reduce the cost of the  Query plan and get rid of the Key Lookup?

     DECLARE @ClaimUniqueNo INT
        DECLARE ClaimAudit CURSOR
                FOR
                    SELECT CA.Claim_Audit_ID, CA.Claim_Audit_Action, CA.Create_Date, CA.Created_By, C.Claim_Status
                    FROM dbo.Claim_Audit_Tbl CA LEFT JOIN Claim_Tbl C
                    ON CA.Claim_Unique_No = C.Claim_Unique_No
                    WHERE CA.Claim_Unique_No = @ClaimUniqueNo
                    ORDER BY Create_Date ASC

Two solutions were mentioned in the comments to your question.  Let's review both of them.

Use a READ_ONLY cursor

  As the article implies, are you able to use the STATIC and READ-ONLY arguments? – Jacob H 23 hours

That comment refers to Paul Randal's blog post Adventures in query tuning: unexpected key lookups.  This points out that a cursor type is dynamic optimistic by default.  From the Microsoft Docs, you can see that the "optimistic" part is what's causing all your problematic key lookups:

  OPTIMISTIC
  ...
  It instead uses comparisons of timestamp column values, or a checksum value if the table has no timestamp column, to determine whether the row was modified after it was read into the cursor.

So it has to do the key lookups to check and see if the rows you've read in have been modified.

The solution proposed in the blog post, and by Jacob H, is to use a more restrictive cursor type (such as READ_ONLY) in order to avoid these extra key lookups entirely.

Optimize the index

  You can improve part of the Key Lookup to support the predicate by adding Claim_Unique_No to the nonclustered index as a key column. – sp_BlitzErik 23 hours ago

The query is currently using the clustered index to do the key lookup.

This comment points out that you could add Claim_Unique_No as a key column in your existing nonclustered index (the one being used in the scan: IDX_Claim_Audit...) to support the predicate (it looks like it already supports the output list that you want).


MySQL data types for common columns I have been a frontend programmer for a while and moving to making some applications on my own and becoming a full stack engineer. I have worked with plenty of databases for applications and sites but as I am building my own I want to make sure I am thinking in best practice. Now many applications have some shared concenpts so was wondering if there is some cheat sheet for common attributes paired with data types and why. Here are some expamles of what I was curious about:

table entry created and updated: date or timestamp? Why? With date isnt it easier to display and sort on the frontend?
Entry title - VARCHAR or text?
Entry body - I assume text.
5 Star rating - INT or Decimal? Why since I have read about both?
Phone & Zip - INT or VARCHAR?
Event Date - Again, Date or Timestamp

Thanks. I know this seems pretty basic, but just trying to an idea of best practice and many of these items are exchangable between applications.

This is all IMHO:

•table entry created and updated: date or timestamp? Why? With date isnt it easier to display and sort on the frontend?

Use a timestamp - better for multiple mods in a given day.

•Entry title - VARCHAR or text?

You're not going to lose much with a  text over VARCHAR, but I think that VARCHAR is the reasonable choice.

•Entry body - I assume text.

BLOB.

•5 Star rating - INT or Decimal? Why since I have read about both?

How flexible do you want to be - I'd tend to Decimal.

•Phone & Zip - INT or VARCHAR?

VARCHAR - intl codes, extensions... more flexible.

•Event Date - Again, Date or Timestamp

Again you're not losing much adding the time - never throw away information that you may use later.


MySQL, can't find MYD files on OS X, MAMP Try as I may I can't find the data files for my MAMP installation!

FRM files are here:

/Applications/MAMP/db/mysql/database_name/

but, there are no corresponding MYD or MYI files anywhere.
The tables definitely have content.

Is it possible that this version of MySQL creates MYD/MYI files that are not the same as the table name? I've been at this for hours.
Any help is appreciated.

OS X Mavericks.
MySQL 5.5.34

+--------------------+---------+
| engine             | tblsize |
+--------------------+---------+
| CSV                |       0 |
| InnoDB             |   16384 |
| MEMORY             |       0 |
| MyISAM             |    4096 |
| PERFORMANCE_SCHEMA |       0 |
| total              |       0 |
+--------------------+---------+
6 rows in set, 1 warning (0.38 sec)

+--------------------------------+
| @@global.innodb_file_per_table |
+--------------------------------+
|                              0 |
+--------------------------------+
1 row in set (0.00 sec)

+------------------------------+
| @@global.datadir             |
+------------------------------+
| /Applications/MAMP/db/mysql/ |
+------------------------------+
1 row in set (0.00 sec)

MyISAM still uses .MYD and .MYI.
The only way you can see .frm  files only and no other table file would be this:

All tables are using InnoDB
innodb_file_per_table disabled (all table data would be inside ibdata1)

If you can log into MySQL, run this
SELECT IFNULL(engine,'total') engine,(data_length+index_length) tblsize
FROM information_schema.tables WHERE table_schema NOT IN
('mysql','information_schema','performance_schema')
GROUP BY engine WITH ROLLUP;
SELECT @@global.innodb_file_per_table;
SELECT @@global.datadir;
SELECT table_schema,table_name,engine,(data_length+index_length) tblsize
FROM information_schema.tables WHERE table_schema NOT IN
('mysql','information_schema','performance_schema')
ORDER BY engine,table_schema,table_name;

These will fully reveal your data and storage engine situation.
UPDATE 2015-05-01 19:48 EST
Based on the output of the queries I gave you

4KB worth of MyISAM
16KB worth of InnoDB
@@global.innodb_file_per_table = 0 means all the table and index space for your InnoDB tables are inside /Applications/MAMP/db/mysql/ibdata1

To see your MyISAM tables, run this:
SELECT table_schema,table_name
FROM information_schema.tables WHERE table_schema NOT IN
('mysql','information_schema','performance_schema')
AND engine='MyISAM';

To see your MEMORY tables, run this:
SELECT table_schema,table_name
FROM information_schema.tables WHERE table_schema NOT IN
('mysql','information_schema','performance_schema')
AND engine='MEMORY';

To see your InnoDB tables, run this:
SELECT table_schema,table_name
FROM information_schema.tables WHERE table_schema NOT IN
('mysql','information_schema','performance_schema')
AND engine='InnoDB';

To get the count of the tables by engine:
SELECT table_schema,COUNT(1) table_count
FROM information_schema.tables WHERE table_schema NOT IN
('mysql','information_schema','performance_schema')
GROUP BY engine;

To get the count of the tables by engine in each database:
SELECT table_schema,engine,COUNT(1) table_count
FROM information_schema.tables WHERE table_schema NOT IN
('mysql','information_schema','performance_schema')
GROUP BY table_schema,engine;


SCD Type 2 Dimension -> Is this the correct layout for a type 2 scd with this type of data present? Here is an example picture of my layout.

As you can see I have my SCD types present (status/startdate/enddate/businesskey).

My key is a surrogate key that identifies each record.

My problem is that my hierarchy seems to error out and it may be due to an incorrect layout.  Here is my current key column structure.

Hierarchy

LineOfBusinessId (key column: LineOfBusinessId) (name column: LineOfBusinessName)

WorkerDivisionId (key column: LineOfBusinessId, WorkerDivisionId) (name column: WorkerDivisionName)

WorkerId (key column: LineOfBusinessId, WorkerDivisionId, WorkerId) (name column: WorkerName)

This dimension errors out because a workername changes and when the select distinct occurs during full processing it finds duplicates.  I am just wondering what the best way of setting up the key columns is for this specific situation.  Do the names need to be part of the key columns or is my hierarchy completely incorrect.  I was sure I had this set up correctly but I am obviously questioning myself now.

I have received some advice that the best thing to do in this situation is to use the surrogate key as part of my key columns, but then found out that this will cause issues as I only want one record to show up when querying my SCD.  Later, I received some more advice saying that I shouldn't use the surrogate in the id key columns but in the name key columns, specifically at the Worker level.

I am rather skiddish of updating all of my cubes with this setup without hearing from someone who has made a hierarchy of this type.  When I look at this setup, I have to think that others have made hierarchies of this type as it is rather simple.  Any advice/direction on this is greatly appreciated.

Thanks.

Making SSAS hierarchies on slowly changing dimensions is a bit of a fiddle.  You need to make surrogate keys for each historical version at each level of the hierarchy.  Then the key has the actual business facing name, which the user selects or reports by.

As an example, imagine worker BloggsJ in Division1, which is in LineOfBusiness1.  Now Division1 gets moved to LineOfBusiness2.  Logically you have the Division entity with two rows now:

DivisionKey     Division     LineOfBusinessKey
          1     Division1                   11
          2     Division2                   12
          3     Division1                   12

and

LineOfBusinessKey    LineOfBusiness
               11    LineOfBusiness1
               12    LineOfBusiness2

Now, we have worker BloggsJ, who is assigned to Division 1, which is subsequently moved

WorkerKey  WorkerName   DivisionKey   Division   LineOfBusinessKey   LineOfBusiness
      101     BloggsJ             1   Division1                 11  LineOfBusiness1
      102     BloggsJ             3   Division1                 12  LineOfBusiness2
      103     SmithF              2   Division2                 12  LineOfBusiness2

In this case the keys remain in a strictly hierachical order:

LineOfBusiness2 (Key=12) has two children: Division2 (Key=2) and Division1 (Key=3).  Division1 (Key=3) has one child: BloggsJ (Key=102) and Division2 (Key=2) has one child: SmithF (Key=103).
LineOfBusiness1 (Key=11) has one child: Division1 (Key=1), which has one Child: BloggsJ (Key=101)

Displaying the name in the cube allows you to build a hierarchy that can support drill-down operations.  You will also probably want to hide the base attributes for this hierarchy and display another set with just the names, so there is something in the dimension that will produce a clean, unique list of members at each level without opaque, confusing repeated names.

*****Update 05012012 3:22 PM CST

Here is an image of my data example.


How do I restore 3 tables into a MySQL database from a single Windows dump file? I want to import 3 specific tables from a dump file that contains 270 tables.

The dump file, naveen_ifs_db.sql, is 20 GB.

How do I restore 3 tables from that dump-file using the command line in windows?

Unless some unusual options are used when it is invoked, the files produced by mysqldump contain the raw sql statements needed to drop and recreate your tables as they existed at the time the dump was done, uncompressed, and human-readable.  They can generally be manipulated with a text editor... although finding a text editor that works well with 20GB files is another matter.

But restoring an individual table essentially requires extracting the lines you want, and piping those statements into the server via the mysql CLI.  Of course, I would not recommend that you do this on your production server without testing elsewhere, first.

It is also possible that there are dependencies in your schema that may make restoring a single table much more difficult... such as foreign keys referencing the table.  You might have to modify the resulting SQL file to get the data back in, but this situation would exist even if you had a mysqldump file of the individual tables you're needing to restore.

But for the task at hand, the first two tools that came to mind are Perl and sed, both of which are free and available for Windows.  Either one should do what you need.

We can find the range of lines we're interested in by displaying the line numbers of strategically-placed SQL comments mysqldump adds to the file:

K:\backup>perl -n -e "print \"$. $_\" if (/^--\s+.*for\stable\s`/ || /^--.*Database/)" backup_file.sql

Output:

3 -- Host: localhost    Database:
19 -- Current Database: `mediawiki`
27 -- Table structure for table `archive`
56 -- Dumping data for table `archive`
       ...
272 -- Table structure for table `image`
302 -- Dumping data for table `image`
312 -- Table structure for table `imagelinks`
327 -- Dumping data for table `imagelinks`
       ...

The numbers at the left are line numbers in the dump file (we just now calculated them).  So, if we wanted to restore mediawiki.image we need lines 272 through 301 to drop and re-create the table ... and 302 through 311 to restore the data.

K:\backup>perl -n -e "print $_ if ($. >= 272 && $. <= 311)" backup_file.sql > my_new_file.sql

Examine my_new_file.sql's content for sanity:

You should find, in order: DROP TABLE, CREATE TABLE, LOCK TABLES your_table WRITE, INSERT INTO your_table VALUES (all_your_data), and UNLOCK TABLES.

Once you're satisfied with the content of the file, you can pipe it to the server via the command line interface:

K:\backup>mysql [normal CLI options here] target_database < my_new_file.sql

If you wanted to use sed instead of Perl, count the lines with:

K:\backup>sed = backup_file.sql | sed "N;s/\n/\t/" | sed -n "/-- Table structure for/p;/-- Dumping data for/p;/-- Current Database/p"

(My sed skills are a little soft.  This could probably be written better and/or to run faster, but it does work as written.)

Then extract the lines you want with:

K:\backup>sed -n 272,311p backup_file.sql > my_new_file.sql

Of course, these work on Linux, too, but the outer double-quotes would be single quotes, and the inner double-quotes in the first Perl example wouldn't be backslash-escaped.

If you have sed or Perl installed, you may also need to add them to your path, or replace "sed" or "perl" in the above examples with fully-qualified pathnames (with the quotes as shown).

"C:\Program Files (x86)\GnuWin32\bin\sed.exe"
"C:\Perl64\bin\perl.exe"


Approach to save many to many relationship in case select all values I have Countries mapped to Products as many to many relationships. As a result a new mapping table is created with product_id and country_id. If a Product is saved then multiple country_ids are stored in new row against product_id, which is fine in case if there are 20 or 30 countries chosen.

But if there is select all option which contains almost 300 values, is it good to go with this approach? or I should use a flag to identify that it is for all values(Countries).

If you use a flag in the products table assume it's a bit data type and set to True which means that product is related to all countries, no doubt you will have less record in the database but your query becomes a bit complex.

If you go with the first approach using the regular joins on foreign keys would also solve the problem with simple queries.

I would prefer to go with a proper many-to-many relationship approach with proper indexes, avoid updates and simple query joins. However if the need arise of having a helper column to say this product is applicable in all countries then I would add it but only if the issue arise.


Will a DBCC CHECKDB with repair have to rescan? I've run a DBCC CHECKDB and it found allocation errors. When I run it with REPAIR_ALLOW_DATA_LOSS - will it need to take the same amount of time to scan for errors, or will it attempt to use the last CHECKDB I ran and go faster?

(SQL Server 2000.  Yes its ancient, no I can't upgrade.)

To add to the answer above, if you're on Enterprise Edition, where a normal DBCC CHECKDB will run in parallel, when you run with a repair option it must run single-thread, so it will also take more time.

Any DBCC CHECK* command has to scan all the data as the results from any previous DBCC CHECK* command are

not persisted anywhere
in no way can be trusted to still be accurate.

Also be wary of REPAIR_ALLOW_DATA_LOSS on SQL Server 2000 - there are many repairs that do not work correctly, that I fixed for SQL Server 2005 as part of my general rewrite of the code base.

Thanks


Is it somehow possible to predict / foresee when SQL Server will trigger auto-growth? I found numerous questions / answers about SQL-Server auto-growth - almost all indicating that one should try to avoid auto-growth if possible.

However auto-growth is available with MS SQL Server and sometimes required.

Is there any way how one could predict when SQL Server will trigger it's next auto-growth event or is this calculation completely internal to SQL Server?

(E.g. is there some kind of "fill-level" in settings which SQL Server compares to the current database usage and then triggers auto-growth when this fill-level will be reached?)

BR
Mark

I think the best way is to go based off of previous events, and one way to do that is to use a trace to start capturing history.

https://sqlblog.org/2007/01/11/reviewing-autogrow-events-from-the-default-trace

Also some ideas here:

https://serverfault.com/questions/33329/how-can-i-get-alerted-when-auto-growth-occurs-on-a-sql-server-database
http://www.sqlservercentral.com/articles/autogrowth/93229/

As you can see, there are many ways to approach this.  Let me know what you think.


How to make a function return a dynamically named column? Simple pseudocode example:

create function myfunc (someparam text, desired_return_column_name text)
returns table (somekeyname int, MAGIC_DYNAMIC_NAME text)
language SQL
as $funcdef$
  select
    somekeyname,
    fieldofinterest as <MAGIC HERE?> desired_return_column_name
  from the_base_table
  where some_other_field = someparam
$funcdef$;

In English, how can I make an SQL function in Postgres (9.6) return a table with statically defined types but where one of the returned column names is named according to a parameter passed in?

If this is not possible with an SQL function, is it possible with a PL/pgSQL function?

No it's not possible in functions, independently of their language.
The structure of a function result set is always static, just like the result set of any given query. In fact it's related, as this structure (column names and types) must be  incorporated into the SQL query that calls the function.

Remember that a client application must be able to prepare a query and obtain a description of its results before executing it. Also a prepared query can be repeateadly executed with different parameters, and cannot return a different structure across executions. It would be impossible to guarantee that property if a function could independantly and dynamically decide what structure it wants to return.

I'm not sure whether the documentation makes that point explicitly somewhere  (of course it tends to describe what can be done, not what is impossible) but here are some bits in the protocol flow documentation that support this answer:

  The Describe message (portal variant) specifies the name of an
  existing portal (or an empty string for the unnamed portal). The
  response is a RowDescription message describing the rows that will be
  returned by executing the portal; or a NoData message if the portal
  does not contain a query that will return rows; or ErrorResponse if
  there is no such portal.

In future versions (PostgreSQL 11 or beyond), we might have stored procedures, which are not called through a SQL query but through a special CALL statement that is not tied to a specific structure of result set. In fact, a stored procedure should be able to return several result sets with different structures, without prior declaration of the return types. This is currently being worked on and discussed in the -hackers mailing list (see Dynamic result sets from procedures and related recent threads on SQL procedures).

But as of PostgreSQL 10, there is no way to practically get dynamic result sets outside of using encapsulating types like JSON or XML.


Batch query a lot of MySQLdump files I have a website for an event. I set up a cron job to back up the database every hour. The backup command is

mysqldump -u"$DB_USER" -p"$DB_PASS" --database "$DB_NAME" >"${OUTPUT_DIR}/${DB_NAME}.$(date +%Y%m%d-%H%M%S).sql"

# crontab: 30 * * * * /path/to/backup_db.sh

I have 200+ of files like this in the backup directory (pulled from server):

$ ls -1 "$OUTPUT_DIR"
ibug.20190301-133002.sql
ibug.20190301-143001.sql
ibug.20190301-153001.sql
ibug.20190301-163001.sql
ibug.20190301-173001.sql
...

The files aren't very big by themselves, averaging less than 2 MB each but summing up to more than 400 MB.

The top few lines of a dump is

-- MySQL dump 10.14  Distrib 5.5.60-MariaDB, for Linux (x86_64)
--
-- Host: localhost    Database: ibug
-- ------------------------------------------------------
-- Server version   5.5.60-MariaDB

I want to perform a series of SELECT queries on each dump (data analysis). The queries range from simple SELECT COUNT(*) ones to complex ones with multiple JOINs and multiple subqueries (all are SELECT).

While it's possible, I don't want to write a script to import them one by one and run the queries. I wonder if there is another better approach.

My local machine is running on 10.1.38-MariaDB-0ubuntu0.18.04.1 Ubuntu 18.04 (not exactly MySQL, it's Maria).

I have (I believe) enough skills and knowledge in Python and Bash (and regular expressions) for a moderately complex script to batch or automate the job, not sure if this is relevant, though.

Importing a small quantity of data like 400M shouldn't take long. Other questions have suggestions on how to do this quickly.

Because these are SQL dumps for a single database, they could be imported into a different database name without a problem. You could use a database name per date, import the data and perform the operations in parallel.


How to determine efficacy of index on child side of foreign key Given a parent-child relationship between two tables, such as:

table ORDER
    order_id PK (already indexed)
    order_name

table ITEM
    item_id PK (already indexed)
    order_id (FK to ORDER.order_id)
    amount

I want to determine whether there would be any benefit (performance improvement to SELECT queries) in adding an index on ITEM.order_id.

What information should I gather that would help me determine this? (such as structure of the queries my application runs against these tables, etc.) I do know that my application regularly runs queries that perform a simple inner join between these two tables. Are there any general principles that apply to index tuning for this kind of scenario?

I am using Oracle 11g.

There are basically three situations where you would want to index the child in addition to the parent in an FK/PK relationship:

If you ever select from ITEM based on order_id (obviously!)
If you ever run queries on from the JOIN of ITEM and ORDERS with a filter on ORDERS
As with 2, but with a filter on ITEM that is less selective than that on ORDERS

In both cases, ITEM will benefit from becoming the inner part of the join and an index from on the child will help support a loop join strategy.


Join MySQL Lookup tables I have the following MySQL tables:

games
genres
game_genres

Games can have many genres (action, fighting, horror), so they're in a lookup table with game_id and genre_id. When I pull in my list of games, I want to combine these tables and get the text genre name into a comma separated column. I'm able to GROUP_CONCAT the genres from the lookup table into a column, but struggling to make those join into the plain text version. So if a game called "Street Fighter" was associated with the Fighting and Action genres, it would pull up a row with an aliased column name of genres and would be Street Fighter, Action. I'm also using "themes" in the same manner as genres, but I'm assuming whatever method will help me do what I need to genres will work for themes too.

Here is the query I'm currently using:

SELECT
    games.*,
    GROUP_CONCAT(DISTINCT genre.genre_id) genres,
    GROUP_CONCAT(DISTINCT theme.theme_id) themes
FROM
    retro_games games
LEFT JOIN retro_game_genres genre ON games.game_id=genre.game_id
LEFT JOIN retro_game_themes theme ON games.game_id=theme.game_id
    WHERE games.game_id='1375'
    GROUP BY games.game_id

So I finally got this to work by using the following query:

SELECT
    games.*,
    games.game_id game_u_id,
    GROUP_CONCAT(DISTINCT genres.genre_name) genre_names,
    GROUP_CONCAT(DISTINCT themes.theme_name) theme_names
FROM
    retro_games games
LEFT JOIN retro_game_genres all_genres ON games.game_id=all_genres.game_id
LEFT JOIN retro_game_themes all_themes ON games.game_id=all_themes.game_id
LEFT JOIN retro_genres genres ON all_genres.genre_id=genres.genre_id
LEFT JOIN retro_themes themes ON all_themes.theme_id=themes.theme_id
    WHERE games.game_id='1375'
GROUP BY games.game_id

Wondering if this is the most efficient way of doing it though, or is there another method that would faster? It's fine for my one row of 1375 but it will be eventually used on thousands of rows when they view all games.


Mysql: [match() against()] full text search order full field value match first I am doing a full-text search against a WordPress database, I am using match() against() to test the search term against multiple fields from multiple tables, all is working great, but I want to improve the order so when a field value is exactly the same as the search term it comes first before any other matches.
This is the current query that searches the database for the term traduction:
    SELECT  `ID`
        FROM  `wp_posts`
        JOIN
        (
            SELECT  `post_id`, `meta_key`, `meta_value`
                FROM  `wp_postmeta`
                WHERE  `meta_key` IN ('definition', 'note', 'definition_en',
                            'observation', 'الملاحظة', 'التحديد')
        ) AS pm  ON `wp_posts`.`ID` = pm.`post_id`
        JOIN
        (
            SELECT  `wp_terms`.`term_id`, `name`, `object_id`
                FROM  `wp_term_relationships`
                JOIN  `wp_term_taxonomy`  ON `wp_term_relationships`.`term_taxonomy_id` = `wp_term_taxonomy`.`term_taxonomy_id`
                JOIN  `wp_terms`  ON `wp_term_taxonomy`.`term_id` = `wp_terms`.`term_id`
                WHERE  `taxonomy` IN ('terme', 'terme_arabic', 'terme_english',
                            'sous-domaine')
        ) AS td  ON `wp_posts`.`ID` = td.`object_id`
        WHERE  (MATCH (`post_title`) AGAINST ("traduction" IN NATURAL LANGUAGE MODE)
            OR  MATCH (`meta_value`) AGAINST ("traduction" IN NATURAL LANGUAGE MODE)
            OR  MATCH       (`name`) AGAINST ("traduction" IN NATURAL LANGUAGE MODE)
               )
        ORDER BY  MATCH (`post_title`) AGAINST ("traduction" IN NATURAL LANGUAGE MODE) DESC

In the results, the post with the title traduction is ordered after other posts with titles like traduction didactique, aide à la traduction,...
How can I modify the query so exact field value matches rank higher than other matches?

ORDER BY post title != 'traduction',
         MATCH...

Explanation:

!= will generate 0 for = and 1 for !=, so the exact match will come first.
Then the rest ORDER BY will control the ordering.

(Please indicate which table each column is in.)


Does SQL Server execute queries in parallel? Does SQL Server execute queries in parallel?
In other words, if I run a heavy query that takes 10 seconds to execute, and at the same time start another heavy query that takes 10 seconds, will the second query actually start after 10 seconds, or will they start both at the same time?

You mean "concurrently". The answer is yes, with caveats that are too broad to discuss here.
In fact, the whole point of RDBMS is concurrency.

"Parallel" has a precise meaning in SQL Server: "a single query is distributed over more than one processor core".


MS SQL server set variable to where clause I am new in MS SQL server and oracle database
I have the following query:

 SELECT * from TBL1 where adc in ('21','24');

but I need something like this:

DECLARE @sqlq nvarchar(500)
    Set @sqlq = '21','23';--(how can I write this)
    SELECT* from OPENQUERY([CBSCSDB], 'SELECT * from TBL1 where adc in (@sqlq)');

problem is how can I set value @sqlq and put it in the select statement in where clause.

You can use table variables

DECLARE @sqlq TABLE
(
    adc VARCHAR(100)
)

INSERT INTO @sqlq VALUES (21);
INSERT INTO @sqlq VALUES (23);

SELECT * FROM TBL1 WHERE adc IN (SELECT adc FROM @sqlsq)


How do I find an invalid utf8 character "somewhere" in my MySQL/trac database I have an installation of trac, which uses MySQL. It has been upgraded so many times, moved servers etc. and chances are that the MySQL character sets were not always set correctly or consistently over the years. Currently all of them are utf8.

When attempting to upgrade the data using "trac-admin wiki update", I'm getting an error message that a byte ("UnicodeDecodeError: 'utf8' codec can't decode byte 0xa0 in position 4274: invalid start byte") is not valid unicode. Unfortunately trac-admin gives me no hint where (table/row/column) to look for that byte sequence, or what I could do to fix it.

My question is not about trac/trac-admin, however, it's about the database. How would you go about finding, "somewhere" in the database, the offending bytes, and replacing them with something that is at least valid utf8. I have attempted to mysqldump the database and to re-import it, but MySQL gives no indication that anything might be wrong. The invalid bytes get re-imported.

Ideas?

You could try turning on the general query log, which logs every query the server receives, and then use that information to see what's being queried, and then issue those queries yourself and examine the data.

To see the current setting for the general log (location and whether it's enabled):

mysql> SHOW VARIABLES LIKE 'general_log%';  # shows the on/off status and file path

To turn it on:

mysql> SET GLOBAL general_log = on; # or off to disable

Remember to turn if off again when you're done, to avoid filling the disk and potentially degrading performance with excessive I/O.

There are also options for logging to a table instead of a file.

mysql> SHOW VARIABLES LIKE 'log_output';    # shows whether logging to FILE or TABLE


Getting the referenced object from SQL Trace I am able to query the default trace or any SQL trace for the event "Audit Add Role Event" to see if anyone has received any instance level access escalation. What I am trying to figure out is how to get which specific user got added to the role. I am using the query below... I tried using the referenced object column but found that it was null.

SELECT TOP 1    TraceFile.[value]
                FROM sys.fn_trace_getinfo(NULL) TraceFile
                WHERE TraceFile.property = 2
---------------------------------------------------------
SELECT  TraceEvent.name AS [EventName],
        Trace.DatabaseName,
        Trace.DatabaseID,
        Trace.NTDomainName,
        Trace.ApplicationName,
        Trace.LoginName,
        Trace.SPID,
        Trace.Duration,
        Trace.StartTime,
        Trace.EndTime
FROM sys.fn_trace_gettable(CONVERT(VARCHAR(150), (SELECT TOP 1    TraceFile.[value]
            FROM sys.fn_trace_getinfo(NULL) TraceFile
            WHERE TraceFile.property = 2
                                             )), 4) Trace
    JOIN sys.trace_events TraceEvent ON Trace.EventClass = TraceEvent.trace_event_id
WHERE   TraceEvent.name LIKE 'Audit Add Role Event'
ORDER BY Trace.StartTime;

Audit Add Role Event is actually auditing the addition of a new role (e.g. CREATE ROLE). To audit adding a new member to an existing role, you want either 108 - Audit Add Login to Server Role Event or 110 - Audit Add Member to DB Role Event, depending on which type of role you actually mean.

Here's an example of each:

Database user added to database role:

DECLARE @path NVARCHAR(260);

SELECT @path = REVERSE(SUBSTRING(REVERSE([path]),
   CHARINDEX(CHAR(92), REVERSE([path])), 260)) + N'log.trc'
FROM    sys.traces
WHERE   is_default = 1;

SELECT RoleName, TargetUserName, StartTime
FROM sys.fn_trace_gettable(@path, DEFAULT)
WHERE EventClass = 110
-- AND DatabaseName = N'YourDatabase'
ORDER BY StartTime DESC;

Login added to server role:

DECLARE @path NVARCHAR(260);

SELECT @path = REVERSE(SUBSTRING(REVERSE([path]),
   CHARINDEX(CHAR(92), REVERSE([path])), 260)) + N'log.trc'
FROM    sys.traces
WHERE   is_default = 1;

SELECT RoleName, TargetLoginName, StartTime
FROM sys.fn_trace_gettable(@path, DEFAULT)
WHERE EventClass = 108
ORDER BY StartTime DESC;

You can also add other columns from the trace to see what application, host, and login actually performed the action. Combining the two queries above, and adding auditing columns:

DECLARE @path NVARCHAR(260);

SELECT @path = REVERSE(SUBSTRING(REVERSE([path]),
   CHARINDEX(CHAR(92), REVERSE([path])), 260)) + N'log.trc'
FROM    sys.traces
WHERE   is_default = 1;

SELECT
  RoleName, TargetUserName, TargetLoginName, DatabaseName,
  ApplicationName, HostName, LoginName, StartTime
FROM sys.fn_trace_gettable(@path, DEFAULT)
WHERE EventClass IN (108, 110)
AND COALESCE(DatabaseName, N'?') = CASE EventClass
--   WHEN 110 THEN N'YourDatabase' ELSE N'?' END
ORDER BY StartTime DESC;

Now when I do something like this on my system:

USE master;
GO
CREATE LOGIN floob WITH PASSWORD = 'x', CHECK_POLICY = OFF
ALTER SERVER ROLE [dbcreator] ADD MEMBER floob;
GO
CREATE DATABASE splunge;
GO
USE splunge;
GO
CREATE USER blat WITHOUT LOGIN;
ALTER ROLE db_datareader ADD MEMBER blat;

I can then run the above query, and I get the following results (leaving audit columns out of the output for brevity):

RoleName       TargetUserName  TargetLoginName  DatabaseName  ...audit columns...
-------------  --------------  ---------------  ------------
db_datareader  blat            NULL             splunge
dbcreator      NULL            floob            master

Clean up:

DROP USER blat;
GO
USE master;
GO
DROP DATABASE splunge;
DROP LOGIN floob;

Note that this will only tell you about events that haven't been aged out of the default trace, so if you're trying to find out when someone was added to a role last year, it's unlikely you're going to find it.

Also, as an aside, it's much more efficient to hard-code the event IDs in the query than to go look up the classes every time. You can see how to get the list of available events in any trace here (as well as examples of other information you can get from the default trace).

And finally, your TOP 1 without ORDER BY assumes that the default trace will always come out first. This is not necessarily always going to be true, even if it is what you observe most of the time (see #3).


Get name and definition of all table types I know how to create a table type (to be used as a table-valued parameter) - but once created, how can I view it?

You can see the list of names:

SELECT name FROM sys.table_types;

To see the definition, you can go to Object Explorer:

If you want to automate this, at least for the columns / data types (this leaves out indexes, keys, etc.) you can build the following:

DECLARE @sql nvarchar(max) = N'',
  @stub nvarchar(max) = N'SELECT N''$--obj--$'',
   name, system_type_name
   FROM sys.dm_exec_describe_first_result_set(''DECLARE
   @tvp $--obj--$; SELECT * FROM @tvp;'',null,null)
   ORDER BY column_ordinal;';

SELECT @sql += REPLACE(@stub, N'$--obj--$',
  QUOTENAME(s.name) + N'.' + QUOTENAME(t.name))
FROM sys.table_types AS t
INNER JOIN sys.schemas AS s
ON t.[schema_id] = s.[schema_id];

EXEC sys.sp_executesql @sql;

This produces output like the following (one resultset for each table type):

Of course, it will error if you have named a table type with an apostrophe - don't do that).


Transactional DDL workflow for MySQL I was a little surprised to discover that DDL statements (alter table, create index etc) implicitly commit the current transaction in MySQL.  Coming from MS SQL Server, the ability to do database alterations in a transaction locally (that was then rolled back) was an important part of my workflow.  For continuous integration, the rollback was used if the migration hiccuped for any reason, so that at least we did not leave the database in a half-migrated state.

How do people solve these two problems when using MySQL with migrations and continuous integration?

For many people, the MySQL Achilles' heel is implicit commit.

According to Page 418 Paragraph 3 of the Book

the following commands can and will break a transaction

ALTER TABLE
BEGIN
CREATE INDEX
DROP DATABASE
DROP INDEX
DROP TABLE
RENAME TABLE
TRUNCATE TABLE
LOCK TABLES
UNLOCK TABLES
SET AUTOCOMMIT = 1
START TRANSACTION

SUGGESTION

When it comes to MySQL, any ContinuousIntegration (CI)/SelfService jobs you construct should always make Transactional jobs and DDL scripts mutually exclusive.

This gives you the opportunity to create paradigms that would

support transactions that are properly isolated with START TRANSACTION/COMMIT blocks
control of DDL by scripting the DDL yourself, running such DDL as either constructor or destructor

Constructor : DDL to make Tables with a New Design
Destructor : DDL to make Tables Revert Back to Previous Design

never combine these operations under one job

WARNING : If you are using MyISAM for any this, you can (un)kindly add MyISAM to the list of things that can break a transaction, maybe not in terms of implicit commit, but definitely in terms of data consistency should a rollback ever be needed.

WHY NOT LVM ?

LVM snapshots are great and restoring whole instances of databases without having to perform heavy SQL processing is ideal. However, when it comes to MySQL, you have to account for two storage engines: InnoDB and MyISAM.

All-InnoDB Database

Look at InnoDB's Architecture (Picture courtesy of Percona CTO Vadim Tkachenko)

InnoDB has many moving parts

System Tablespace

Data Dictionary
Double Write Buffer (support data consistency; used for Crash Recovery)
Insert Buffer (Buffers Changes to Secondary Non-Unique Indexes)
Rollback Segments
Undo Space (where the most uncontrolled growth can happen)

InnoDB Buffer Pool

Dirty Data Pages
Dirty Index Pages
Changes to NonUnique Indexes

Other Important Memory Caches

Taking an LVM snapshot of an all-InnoDB database with uncommitted changes floating in the Buffer Pool and Memory caches would yield a dataset that would require InnoDB crash recovery once the LUN is restored and mysqld started.

SUGGESTION FOR ALL-InnoDB

If You Can Shutdown MySQL Before Taking Snapshot

Run SET GLOBAL innodb_fast_shutdown = 0;

Run SET GLOBAL innodb_max_dirty_pages_pct = 0;

Run SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_pages_dirty';

Repeat Step 3 until Innodb_buffer_pool_pages_dirty is 0 or as close to 0 as possible

service mysql stop

Take LVM snapshot

service mysql stop

If You Cannot Shutdown But Take Snapshot With MySQL Live

Run SET GLOBAL innodb_max_dirty_pages_pct = 0;

Run SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_pages_dirty';

Repeat Step 2 until Innodb_buffer_pool_pages_dirty is 0 or as close to 0 as possible

Take LVM snapshot

Run SET GLOBAL innodb_max_dirty_pages_pct = 75;

All-MyISAM Database or InnoDB/MyISAM Mix

MyISAM, when accessed, maintains a count of open file handles against it. If MySQL crashes, any MyISAM table with an open file handle count > 0 will be marked as crash and in need of repair (even if nothing is wrong with the data).

Taking an LVM snapshot of a database that has MyISAM tables in use will have one or more MyISAM tables in need of repair when the snapshot is restored and mysqld started.

SUGGESTION FOR All-MyISAM or InnoDB/MyISAM Mix

If You Can Shutdown MySQL Before Taking Snapshot

Run SET GLOBAL innodb_fast_shutdown = 0;

Run SET GLOBAL innodb_max_dirty_pages_pct = 0;

Run SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_pages_dirty';

Repeat Step 3 until Innodb_buffer_pool_pages_dirty is 0 or as close to 0 as possible

service mysql stop

Take LVM snapshot

service mysql stop

If You Cannot Shutdown But Take Snapshot With MySQL Live

You could enforce a flushing of certain InnoDB tables

Run SET GLOBAL innodb_max_dirty_pages_pct = 0;

Run SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_pages_dirty';

Repeat Step 2 until Innodb_buffer_pool_pages_dirty is 0 or as close to 0 as possible

Run FLUSH TABLES innodb_tbl1,... FOR EXPORT; on critical InnoDB tables

Run FLUSH TABLES WITH READ LOCK;

Take LVM snapshot

Run UNLOCK TABLES;

Run SET GLOBAL innodb_max_dirty_pages_pct = 75;

Could MySQL Replication Help ?

While you could restore one LVM snapshot to two servers and setup MySQL Master/Slave Replication, that becomes an extra source of housecleaning when restoring snapshots.

If you run CI jobs on a Master and those jobs are small, replication could be a timesaver under certain circumstances. You could just run STOP SLAVE; on the Slave, kick off the CI jobs on the Master, and run START SLAVE; on the Slave when the Master's data is certified.

If the CI jobs alert too much data, you could restore LVM snapshot and setup replication from scratch. If you find yourself doing this often, you could probably do with setting up MySQL Replication.

FINAL THOUGHTS

It is best to use multiple DB Servers (3 or more) to perform restores and regression tests.
Convert remaining MyISAM tables to InnoDB if those tables do not need to remain MyISAM.
If your data content is sensitive, you should execute a CI job to scrub the data after restoring a snapshot before initiating any tests. As an alternative, you may want to take snapshots of MySQL with the data already scrubbed.


MariaDB 5.5-56 not reading the my.cnf file and taking default values Permissions are correct. I actually went ahead and opened the file up to everyone (I know... big no-no but it's a closed off lab) any idea why MariaDB 5.5-56 wouldn't read the my.cnf file and just take default settings?

On a Linux OS, make sure the file /etc/my.cnf is owned by root.


SQL Server database tries to restore itself automatically (and gets stuck) I currently run into an issue, that a SQL Server Database has the state 'recovery pending' every morning.
No one has started a database recovery, the server itself decides to do it.
The recovery process gets stuck and the SQL Server stops working, as its memory limit is reached.
Temporarily, I can resolve it by restarting the MSSQLSERVER process and then taking the database offline and back online. However, as this seems to be happening every day now, this is an unacceptable workaround.
I am eager to know:

What are the possible reasons, that a SQL Server automatically starts such an automated restore process
Are there any Logs / Events I can look for, to find the reason.
How can I solve it :)

Any help or hint will be highly appreciated.

Update: Database was in recovery pending, not in restore pending . The SSMS is installed in german language and didn't show me the english status.

I have a feeling that what you really  have is "recovery pending" and not "restore pending". If you look at the documentation for sys.databases, the state column, you will see that there isn't a state called "restore pending". So, I will assume it is indeed "recovery pending" below. If it is anything else, please let us know, show is what it look like in SSMS Object Explorer as well as the state column from sys.databases.
Recovery pending means that SQL server couldn't do recovery at startup of the database. Note that start of the database can happen outside startup of SQL Server. Having the autoclose database option set to true is one such case.
Anyhow, whenever recovery isn't doable, the database goes into recovery pending. But SQL server will document why, in the errorlog file. So your task is to hunt down these error messages and act on them. My guess is that any of the database files are used by some other process (anti-virus, snapshot thingie, etc). But at a later time, the files are accessible to SQL Server.


ibdata1 grows exponentially when innodb_file_per_table is configured I Have installed a MySQL Cluster with InnoDB (innodb_file_per_table enabled subsequently),  but since I switched to innodb_file_per_table, the file ibdata1 grows (2GB at month).
Is My my.cnf file is correct?
Why my ibdata1 is so big (22GB)?
How I can look what there is in the ibdata1?
Server Configuration:

Debian 6 amd64

mysql-client-5.1  5.1.61-0+squeeze1

My InnoDB Configuration File:
#
# * InnoDB
#
# InnoDB is enabled by default with a 10MB datafile in /var/lib/mysql/.
# Read the manual for more InnoDB related options. There are many!
#

innodb_data_home_dir            = /var/lib/mysql
innodb_data_file_path           = ibdata1:10M:autoextend
innodb_log_group_home_dir       = /var/lib/mysql
innodb_file_per_table
innodb_buffer_pool_size         = 5G
innodb_additional_mem_pool_size = 48M
innodb_log_files_in_group       = 3
innodb_log_file_size            = 512M
innodb_log_buffer_size          = 8M
innodb_flush_log_at_trx_commit  = 0
innodb_lock_wait_timeout        = 50
innodb_thread_concurrency       = 15
innodb_flush_method             = O_DIRECT

Procedure:
1) Dump All DB's
2) Stop MySQL
3) Add "innodb_file_per_table"
4) Delete all ib* file
5) Start MySQL
6) Import All DB's

InnoDB Conf:
mysql> show variables like 'innodb%';
+-----------------------------------------+------------------------+
| Variable_name                           | Value                  |
+-----------------------------------------+------------------------+
| innodb_adaptive_hash_index              | ON                     |
| innodb_additional_mem_pool_size         | 50331648               |
| innodb_autoextend_increment             | 8                      |
| innodb_autoinc_lock_mode                | 1                      |
| innodb_buffer_pool_size                 | 25165824000            |
| innodb_checksums                        | ON                     |
| innodb_commit_concurrency               | 0                      |
| innodb_concurrency_tickets              | 500                    |
| innodb_data_file_path                   | ibdata1:10M:autoextend |
| innodb_data_home_dir                    | /var/lib/mysql         |
| innodb_doublewrite                      | ON                     |
| innodb_fast_shutdown                    | 1                      |
| innodb_file_io_threads                  | 4                      |
| innodb_file_per_table                   | ON                     |
| innodb_flush_log_at_trx_commit          | 0                      |
| innodb_flush_method                     | O_DIRECT               |
| innodb_force_recovery                   | 0                      |
| innodb_lock_wait_timeout                | 50                     |
| innodb_locks_unsafe_for_binlog          | OFF                    |
| innodb_log_buffer_size                  | 8388608                |
| innodb_log_file_size                    | 536870912              |
| innodb_log_files_in_group               | 3                      |
| innodb_log_group_home_dir               | /var/lib/mysql         |
| innodb_max_dirty_pages_pct              | 90                     |
| innodb_max_purge_lag                    | 0                      |
| innodb_mirrored_log_groups              | 1                      |
| innodb_open_files                       | 300                    |
| innodb_rollback_on_timeout              | OFF                    |
| innodb_stats_method                     | nulls_equal            |
| innodb_stats_on_metadata                | ON                     |
| innodb_support_xa                       | ON                     |
| innodb_sync_spin_loops                  | 20                     |
| innodb_table_locks                      | ON                     |
| innodb_thread_concurrency               | 15                     |
| innodb_thread_sleep_delay               | 10000                  |
| innodb_use_legacy_cardinality_algorithm | ON                     |
+-----------------------------------------+------------------------+

ibdata1 can still grow despite innodb_file_per_table being enabled. Why ?

Question: If InnoDB tables and associative indexes are written to individual tablespace files (file extension .ibd), what information still needs to be written to ibdata1 ?

Answer: Here are the following classes of information written to InnoDB's system tablespace:

Data Dictionary
Double Write Buffer

Safety Net to Prevent Data Corruption
Helps Bypass OS for Caching

Insert Buffer (Streamlines Changes to Secondary Indexes)
Rollback Segments
Undo Logs
Click Here to see a Pictorial Representation of ibdata1

I have discussed this before:

Dec 09, 2011 : What is the best way to reduce the size of ibdata in mysql?
Apr 01, 2012 : Is innodb_file_per_table advisable?
Mar 25, 2012 : Why does InnoDB store all databases in one file?

EPILOGUE

Given enough transactions, rollback segments and undo logs to support REPEATABLE READs can make ibdata1 grow. INSERTs and UPDATEs to InnoDB tables with Secondary Indexes can pile up  in the Insert Buffer. The Double Write Buffer Provides a second level of data redundancy in the event of a crash and subsequent crash recovery on mysqld startup. The only thing that can never grow is the Data Dictionary (unless you have DDL statements to create new InnoDB tables).


Can install a test machine with Oracle 11G R2, not express edition, on a VirtualBox? Im starting like a Oracle DBA, and I want play with the database and your administration task in a test enviroment.

I have a macbook pro i5, and had thought install on it oracle under virtual box machine.

What distro of linux your recommends me to start?
I can install Oracle RAC in a Vbox machine?
Some handbook?
Other considerations...

I appreciate all your comments, and excuse my poor english D:

Most of your questions are covered in official documentation.
http://docs.oracle.com/cd/E11882_01/nav/portal_11.htm

Read the installation guide (NOT the "quick installation" one).

I don't recommend using "pre-built" virtual machines - try to install OS and db from scratch. The process is the same as installing on "real" hardware. Use CentOS as mentioned in comments.

Not sure for RAC, but Data Guard works perfectly.

Good luck and have fun :)


What is the industry term for superset of "metrics" and "dimensions"? If I want to refer to measures/metrics and dimensions with a single term, what would be a good industry-accepted expression?

Metrics is used commonly in UIs as well such as Google Analytics or Tableau.

The context for my question is for a UI. I'd like to group both of them and refer to them with an industry-accepted name, if one exists.

Dimensions and Measures are the two main components of cubes. The dimensions are basically ways to look at the data while the measures are the data.

When writing MDX the measures are a member of the [Measures] dimension but that is mostly a syntax convention.

As for a common name, I would say they both are partly definitions of a cube design. The measures define the values and the dimensions define how you slice or aggregate the measures.

The combined result of a query across dimension members and measures is called a cellset but that term is used for a result rather than a cube definition (which is what measures and dimensions are).

If there even is a common term for the combination of dimensions and measures it would be physical aspects of data aggregation

If I were to design a UI where I list both in an interface for users to add a report I would probably call them 'report items' or 'data components'  or something but I think the clearest for an end user would be to follow  what Excel does and label them 'rows' or 'columns' and 'values' where things you can add to rows/columns are the dimensions and values are the measures.

I don't think it's any clearer to an end user to try and find a common name and from a UI design perspective I would make a distinction between things they should put on an axis and 'values'.


Permissions using a TYPE as a Table Valued Parameter I'm developing a .Net App that calls an stored procedure that have a table valued parameter declared as a TYPE READONLY.

First time I tried to call the SP I received the next error:

  Msg 229, Level 14, State 5, Line 1
  The EXECUTE permission was denied on the object 'TYPE_OBJ', database 'MY_DB', schema 'dbo'.

After granting EXECUTE permission to the user it works fine.

But I can't find in MS-DOCS neither in CREATE TYPE nor in Use table-valued parameters any reference to the permissions needed to use it as a parameter.

Where can I find information about the necessary permissions to use a TYPE as a parameter?

Where can I find information about the necessary permissions to use a TYPE as a parameter?

I've submitted a PR against the doc page to get the requirements documented.

https://github.com/MicrosoftDocs/sql-docs/pull/3351

Here's a simple repro:

create type dbo.tt as table(id int)

go

create procedure dbo.ptt @tt tt readonly
as
select * from @tt

go

create user joe without login

grant references on type::dbo.tt to joe
grant execute on dbo.ptt to joe

go

execute as user='joe'

  declare @t tt
  exec ptt @t
  --The EXECUTE permission was denied on the object 'tt', database 'a', schema 'dbo'.

revert

go

grant execute on type::dbo.tt to joe

go

execute as user='joe'

  declare @t tt
  exec ptt @t
  --no error

revert


PostgreSQL: duplicates despite having a unique constraint We have a table in the production DB that looks like this:

create table domains
(
 domain_id serial not null
  constraint domains_pkey
   primary key,
 domain_name text not null
  constraint domains_domain_name_key
   unique
)

For some reason, we've got duplicates in that table, even though, in theory, this is impossible.

Here's a query that I use to find these duplicates:

SELECT md5(t1.domain_name), md5(t2.domain_name)
  FROM domains t1
  JOIN domains t2
    ON t1.domain_name = t2.domain_name
 WHERE t1.domain_id != t2.domain_id;

It returns 50 records, and there are over 7M total records in the table.

I understand how to get rid of them. However, I would really like to know how to prevent new duplicates and why this happened?

PostgreSQL version: PostgreSQL 11.7 (Debian 11.7-0+deb10u1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 8.3.0-6) 8.3.0, 64-bit

It seems that the problem is caused by the glibc update issue that @a_horse_with_no_name highlighted in the comments: https://postgresql.verite.pro/blog/2018/08/27/glibc-upgrade.html

I suppose it would be enough to get rid of the duplicates and reindex that table, there will be no new duplicates after that.


Mysql functions for web apps I have few questions about using functions in a MySQL database for plugins and extensions of web apps like Magento or Wordpress.  Mostly this is for a few extensions I want to do for Magento.  The questions are:

is it possible under normal average hosts out there, that functions can be added.
(if 1) is it worth the effort to create function that would replace php in some manner (speed mostly is what I'm thinking)
(if 1) can functions be looped? like timed or call to it's self x time like sleep or something.

I was wanting to use the db for simple things like removing rows that were older then or something like that.  Examples are not as important as possibility here in my mind.  This is more of a set of questions that are due to not every really seeing web app use functions, and it just seems like an area of untapped potential for optimization when designing web apps.

I can't answer to question 1 as I do not have enough information about the MySQL instances offered by different hosting providers.

To answer the rest of your questions, yes, if you can leverage the features of the RDBMS you're using, you might replace PHP code with SQL scripts that can run faster than your code. Just think about the time you're saving up by not transferring your data back and forth.

On the other hand you would probably still end up using PHP code or other programs (crontab or any other kind of scheduler) to run your timed tasks as there's no built-in scheduler in MySQL.


How can I keep two databases on different sites in sync? I'm working on a web application that exists at two sites. Each system is hosted on it's own LAMP stack, with access restricted to specific users on the network.

When content is updated via user input on one side, an XML is transferred from that system to the other to update it, so both systems match.

These files are transferred by a 'guaranteed delivery' system. However, sometimes the XMLs get bunched up and arrive in the wrong order, causing problems at the receiving end.
This is particularly noticable with rapid user input, as the delay from the transmission system causes the files to 'bump into eachother'.

It was suggested that database replication would be more efficient to keep the systems in sync.

My question is: Is replication suitable for small incremental changes, does it have to update every single item each time or just the part that has changed?

Another question I have is, what protocol would the databases use to communicate from two separate sites?

Replication was made for small changes, so you're on the right track.
To keep the systems in sync, at the database level, you will need to setup a mysql master-master replication scenario.
I've used the tutorial from this linke (http://howtodba.com/how-to-build-a-cheap-web-cluster-with-replicated-storage-and-mysql-master-master-configuration/), to setup such configuration.
Depending on how much data is changed, you can choose between [statement] or [row-based] replication.
More info about the differences between the 2 types: http://www.databasejournal.com/features/mysql/article.php/3922266/Comparing-MySQL-Statement-Based-and-Row-Based-Replication.htm


Oracle SQL. Error creating type I'm learning SQL.

When I try to create a type in Oracle SQL:

CREATE TYPE boolean_typ AS OBJECT
(valor char(5) CHECK (VALUE IN ('true', 'false', NULL)) );
/

I get this error:

Warning: Type created with compilation errors.

It gives no explanation on what is the error. May you help me?

Type the following command right after you receive your error-message:

show errors

Show here:

SQL> CREATE TYPE boolean_typ AS OBJECT
  2  (valor char(5) CHECK (VALUE IN ('true', 'false', NULL)) );
  3  /

Warning: Type created with compilation errors.

SQL> show errors
Errors for TYPE BOOLEAN_TYP:

LINE/COL ERROR
-------- -----------------------------------------------------------------
2/16     PLS-00103: Encountered the symbol "CHECK" when expecting one of
         the following:
         := ) , not null default external character

SQL> drop type boolean_typ;

Type dropped.

SQL> exit


Azuredb The database 'tempdb' has reached its size quota We are runnig a V12 Azure Database instance on the S3 tier. There is still about 100GB of a free space on the database. When loading 85MB XML file with SSIS running on a different non azure SQL server, and directly inserting it into the azure database, the insert crashes on the target database with the following error.

  The database 'tempdb' has reached its size quota. Partition or delete data, drop indexes, or consult the documentation for possible
  resolutions.

Is there any limit on the tempdb or any idea why this might be crashing? There is no way how the 85MB file can fill the remaining space on the database.  The tempdb seems somehow hidden, how can I monitor its usage?

Limits depend from your service trier, in this sitiation it is no less than 250 GB for database, but it is only 32 MB for tempdb attached to this database. You can check other available resources here Azure SQL Database resource limits and information about Azure tempdb here Tempdb database in SQL Database.

There are several options which allow you to investigate what is going on with your tempdb. It is hidden, but you can still query sys objects to retrive information about this kind of databases. Try to check following resources:

Lesson Learned #10: Monitoring TempDB usage (useful answer in
comment, one tempdb per database)
How to identify which query is
filling up the tempdb transaction log?
How to Monitor tempdb use

When you find out what is consuming tempdb resources, you should be able to fix it or extend your question with additional information.

As you said, your file is 85 MB and we can see that it exceed max tempdb data size. Try to work with smaller chunks of data at a time. You should also consider BULK insert in this scenario.


Better solution for creating many-to-many table per entity I have a table for images.

I have tables for entities such as people, cars, houses, trains and dogs to which I need to assign images.

I need to assign images to entities. The obvious solution is to create many many-many tables:

people_images (person_id, image_id, sort_position, ...)
cars_images   (car_id,    image_id, sort_position, ...)
houses_images (house_id,  image_id, sort_position, ...)

And perhaps modify the images table like so:

images (image_id, url, is_person_image, is_cars_image, is_houses_image, ...)

I am wondering if there is a better solution or common pattern for this.

Does every image belong to one and only one entity (i.e. to one person only, to one car only, etc)? If so, your image table needs a key which will identify to which entity it belongs - instead of having a table for each kind of entity. If several entities can use the same image, consider one table of images and one table whose primary key is composed of the image primary key and an entity identifier.


INSERT performance difference between temporary tables and table variable I have the following issue in SQL Server 2005: trying to inserts some rows into a table variable takes a lot of time compared to the same insert using a temporary table.

This is the code to insert into the table variable

DECLARE @Data TABLE(...)
INSERT INTO @DATA( ... )
SELECT ..
FROM ...

This is the code to insert into the temp table

CREATE #Data TABLE(...)
INSERT INTO #DATA( ... )
SELECT ..
FROM ...
DROP TABLE #Data

The temporary table doesn't have any keys or indexes, the select part is the same between the 2 queries, and the number of results returned by the select is ~10000 rows. The time needed to execute the select alone is ~10 seconds.

The temp table version takes up to 10 seconds to execute, I had to stop the table variable version after 5 minutes.

I have to use a table variable because the query is part of a table value function, that doesn't allow access to temporary table.

Execution plan for the table variable version

Execution plan for the temp table version

The obvious difference between the two plans is that the fast one is parallel and the slower one serial.

This is one of the limitations of plans that insert into table variables. As mentioned in the comments (and it seems as though it had the desired effect) you could try doing

INSERT INTO @DATA ( ... )
EXEC('SELECT .. FROM ...')

to see if that gets around the limitation.


Can I connect mysql client to a mariadb server? I am new to databases, so I might have done something very simple wrong.
I cannot connect to my Mariadb server via my mysql client. Is this possible at all?
cmd> mysql -h 192.168.0.17 -u root -P 3306

gives me an error:

ERROR 2013 (HY000): Lost connection to MySQL server at 'reading initial communication packet', system error: 0

On my host machine, I have mysql with version
cmd> mysql -V
mysql  Ver 14.14 Distrib 5.6.10, for Win64 (x86_64)

and on my VM, I have an ubuntu server
$ mysql -V
mysql  Ver 15.1 Distrib 10.3.23-MariaDB, for debian-linux-gnu (x86_64) using readline 5.2

and my config on VM:
$ cat /etc/mysql/my.cnf.dpkg-new | head -n 20 | tail -n 7

# This will be passed to all mysql clients
# It has been reported that passwords should be enclosed with ticks/quotes
# escpecially if they contain "#" chars...
# Remember to edit /etc/mysql/debian.cnf when changing the socket location.
[client]
port        = 3306
socket      = /var/run/mysqld/mysqld.sock

Ping is OK:
cmd> ping 192.168.0.17

Pinging 192.168.0.17 with 32 bytes of data:
Reply from 192.168.0.17: bytes=32 time<1ms TTL=64
Reply from 192.168.0.17: bytes=32 time<1ms TTL=64
Reply from 192.168.0.17: bytes=32 time<1ms TTL=64
Reply from 192.168.0.17: bytes=32 time<1ms TTL=64

Ping statistics for 192.168.0.17:
    Packets: Sent = 4, Received = 4, Lost = 0 (0% loss),
Approximate round trip times in milli-seconds:
    Minimum = 0ms, Maximum = 0ms, Average = 0ms

Anyway, I fixed the problem via
MariaDB [(none)]> Grant All Privileges ON *.* to 'remoteroot'@'192.168.0.15' Identified By '123456';
MariaDB [(none)]> FLUSH PRIVILEGES;

where 192.168.0.15 is the host machine IP.
Then connected via
cmd> mysql -h 192.168.0.17 -u remoteroot -P 3306 -p

Looks like working.
Still concerned some features face problems.


Select Count (case when)! Friends, I would like help from you to find a solution to the following case:

A table with 2 fields: datetime, record (the record being any number that can be repeated a few times)

I need to count how many times each record appears in 12h, 24h, and 72h intervals.

Example, supposing record number 1: It appears 2 times in a 12h interval, plus 1 within 24h and another 3 in the 72h interval.

Thus, for the first record 1, in the 12-hour column there would be a value of 2, a 24-hour value would be 3, a 72-hour value would be 6. In the second row of record 1 the results would be: for 12h value 1, 24h value 2, 72h value 5 and so on.

Any suggestion?

I can share the conceptual answer to this problem, you will have to fill in correct syntax for query as per your database engine.

select record, count(record)
from table
where datetime is interval of 12 hours from now
group by record

This query can give you record occurrences in one single interval of time.

select record,
   SUM(case when datetime is in interval 12h then 1 else 0 end) 12_hour_occurrence,
   SUM(case when datetime is in interval 24h then 1 else 0 end) 24_hour_occurrence,
   SUM(case when datetime is in interval 72h then 1 else 0 end) 72_hour_occurrence
from table
where datetime > now() - 72 hours # don't pass thorugh extra records
group by record

For running on single query on multiple instances, you can use this concept to find occurrences in multiple intervals.


NDB Cluster seems to ignore foreign_key_checks=OFF According to MySQL documentation on the local foreign_key_checks variable

  Beginning with MySQL Cluster NDB 7.3.2, setting this variable has the same effect on NDB tables as it does for InnoDB tables—previously, the setting was ignored and all such checks were enforced (Bug #14095855).

I should be able to import a unordered InnoDB dumpfile with foreign keys.

My NDB version

# ndbd --version
MySQL distrib mysql-5.6.21 ndb-7.3.7, for Linux (x86_64)

But when I am trying to add a structure-only dump beginning with

SET foreign_key_checks=OFF;

--
-- Table structure for table `aclAktion`
--

DROP TABLE IF EXISTS `aclAktion`;
CREATE TABLE `aclAktion` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(64) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT '',
  `editMask` set('read','write','create','delete','super') CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT 'read,write,create,delete',
  PRIMARY KEY (`id`),
  UNIQUE KEY `aclAktion_uIdx_name` (`name`)
) ENGINE=NDBCLUSTER ROW_FORMAT=DYNAMIC;

--
-- Table structure for table `aclGroup`
--

DROP TABLE IF EXISTS `aclGroup`;

SHOW VARIABLES LIKE 'foreign_key_checks';
CREATE TABLE `aclGroup` (  -- this is line #63 in the dumpfile
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `aclRole_id` enum('gast','kunde','owner','owner_boss','red_ext','red_ext_boss','red_int','red_int_boss','verlag_boss','sysop','root') CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT 'gast',
  `numUsers` int(11) NOT NULL DEFAULT '0',
  `name` varchar(64) NOT NULL DEFAULT '',
  `aktiv` tinyint(1) NOT NULL DEFAULT '0',
  `login` tinyint(1) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  UNIQUE KEY `aclGroup_uIdx_name` (`name`),
  KEY `aclGroup_idx_aclRole_id` (`aclRole_id`),
  CONSTRAINT `aclGroup_fk_aclRole_id` FOREIGN KEY (`aclRole_id`) REFERENCES `aclRole` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=NDBCLUSTER ROW_FORMAT=DYNAMIC;

--
-- Table structure for table `aclRole`
--

DROP TABLE IF EXISTS `aclRole`;
CREATE TABLE `aclRole` (
  `id` enum('gast','kunde','owner','owner_boss','red_ext','red_ext_boss','red_int','red_int_boss','verlag_boss','sysop','root') CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT 'gast',
  `numGroups` int(11) NOT NULL DEFAULT '0',
  `login` tinyint(1) NOT NULL DEFAULT '0',
  `checkNumLogins` tinyint(1) NOT NULL DEFAULT '0',
  `defaultPrivileges` set('read','write','create','delete','super') CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT 'read',
  `beschreibung` varchar(192) NOT NULL DEFAULT '',
  PRIMARY KEY (`id`)
) ENGINE=NDBCLUSTER ROW_FORMAT=DYNAMIC;

I get an error:

ERROR 1215 (HY000) at line 63: Cannot add foreign key constraint

The error log on the API node shows that NDB actually tries to handle the foreign key correctly

2014-11-03 17:50:05 [NdbApi] INFO     -- Flushing incomplete GCI:s < 10865/8
2014-11-03 17:50:05 [NdbApi] INFO     -- Flushing incomplete GCI:s < 10865/8
2014-11-03 17:50:05 22485 [Note] NDB Binlog: starting log at epoch 10865/8
2014-11-03 17:50:05 22485 [Note] NDB Binlog: ndb tables writable
2014-11-03 17:50:05 22485 [Note] NDB Binlog: Node: 2, subscribe from node 4, Subscriber bitmask 010
2014-11-03 17:50:05 22485 [Note] NDB Binlog: Node: 3, subscribe from node 4, Subscriber bitmask 010
2014-11-03 17:50:08 22485 [Note] NDB Binlog: Node: 2, unsubscribe from node 4, Subscriber bitmask 00
2014-11-03 17:50:08 22485 [Note] NDB Binlog: Node: 3, unsubscribe from node 4, Subscriber bitmask 00
2014-11-03 17:50:16 22485 [Note] NDB Binlog: Node: 2, subscribe from node 4, Subscriber bitmask 010
2014-11-03 17:50:16 22485 [Note] NDB Binlog: Node: 3, subscribe from node 4, Subscriber bitmask 010
2014-11-03 18:01:29 22485 [Note] NDB Binlog: CREATE TABLE Event: REPL$database/aclAktion
2014-11-03 18:01:29 22485 [Note] NDB Binlog: logging ./database/aclAktion (UPDATED,USE_WRITE)
2014-11-03 18:01:29 22485 [Note] NDB FK: Created mock table 'NDB$FKM_20_0_aclRole' referenced by 'aclGroup'

No errors on the ndb node

It seems, the variable foreign_key_checks = off is ignored here.
Any hints?

Mock tables were how mysqldump were loaded in older versions of MySQL a long time ago

Issue like this existed

load of mysqldump script may fail with error 1215 if dest already has tables
Foreign keys: mysqldump should respect foreign keys

See that last line

2014-11-03 18:01:29 22485 [Note] NDB FK: Created mock table 'NDB$FKM_20_0_aclRole' referenced by 'aclGroup'

Since mysqldumps were in alphabetical order by default, there might be some cases where the parent table of a foreign key relationship would appear after the child table rather than before. As a consequence, the mock table was created to have a parent to adopt the aclGroup table.

Since a child cannot have more than one foreign key reference to the same parent on the same columns, then NDB is ignoring it and still trying to enforce foreign_key_checks=OFF

Under the hood, this is what the NDB storage engine should be doing afterCREATE TABLE aclGroup

ALTER TABLE aclGroup DROP CONSTRAINT aclGroup_fk_aclRole_id;
DROP TABLE `NDB$FKM_20_0_aclRole`;
DROP TABLE IF NOT EXISTS aclRole;
CREATE TABLE aclRole (...) ENGINE=NDBCLUSTER;
ALTER TABLE aclGroup ADD CONSTRAINT aclGroup_fk_aclRole_id
FOREIGN KEY (`aclRole_id`) REFERENCES `aclRole` (`id`) ON DELETE CASCADE ON UPDATE CASCADE;

If you look at Dump of foreign key constraints with mysqldump not sorted, you will note that this was a feature request for InnoDB long ago. This same feature is probably not properly implemented in NDB. I am sure if you change the NDBCLUSTER to InnoDB in your script and run it, it will work without incident.

So, YES, foreign_key_checks=OFF is being ignored.


How can I do a differential query (delta plus/minus) telling me what rows are in view A that are not in view B and vice versa? I have two views which use radically different means to accomplish what I hope is the same end view. One uses complex query expressions and walks entire giant tables  and is highly expensive, and the other uses a new boolean bit flag which has been added to the database for precisely the purpose of accelerating this view.

I can write this kind of union, and then (in my program code) use this to determine that a row in view A is not in the results of view B, but I'm hoping I can do this entirely in SQL:

Here's the union I came up with:

select trantype,product_code, 0 as type from vwVIEW1 where key = 'DEMO'
union
select trantype,product_code, 1 as type  from vwVIEW2 where key = 'DEMO'
order by product_code, trantype

My old view is fictionally called vwVIEW1 above, and it walks giant database tables to determine its output, whereas vwVIEW2  does a very quick index-lookup on two fields to achieve the same results (select a,b,c from tblUnderlyingTable where tblUnderlyingTable.HeyRickyCanIBeInYourShow=1).

Typical results coming from the above union would in the healthy case, return a pair of rows for each trantype+product_code, plus my invented "type" flag:

trantype product_code  type
-------- ------------  ----
0        MATCHY        0
0        MATCHY        1
0        NONMATCHY     0

Out of the above set of data, I would want to only see the row NONMATCHY, because it is in vwVIEW1 but not in vwVIEW2.  All fields in the union above are non-null. No nulls are ever expected in trantype, or product_code.

You can use a FULL OUTER JOIN for this

WITH T1
     AS (SELECT trantype,
                product_code
         FROM   vwVIEW1
         WHERE  KEY = 'DEMO'),
     T2
     AS (SELECT trantype,
                product_code
         FROM   vwVIEW2
         WHERE  KEY = 'DEMO')
SELECT *
FROM   T1
       FULL OUTER JOIN T2
         ON T1.trantype = T2.trantype
            AND T1.product_code = T2.product_code
WHERE  T1.trantype IS NULL
        OR T2.trantype IS NULL


count distinct events per day I have dataset:

timestamp               event   user
2020-04-28 20:07:55.503 log_in  john
2020-04-28 20:08:01.996 log_out john
2020-04-28 20:08:02.470 log_in  john
2020-04-28 20:08:03.996 log_out john
2020-04-28 20:08:05.729 log_failed  john
2020-04-29 10:06:45.683 log_in  mark
2020-04-29 10:08:58.299 password_change mark
2020-04-30 14:19:24.921 log_in  jeff
2020-04-30 14:20:31.266 log_out jeff
2020-04-30 14:21:44.438 create_new_user jeff
2020-04-30 14:22:44.455 create_new_user jeff

How to write a sql query to count all unique events per day. the unclear part for me is the presence of hours in timestamp. The desired result looks like this:

timestamp  count
2020-04-28 3
2020-04-29 2
2020-04-30 3

You could run something like this:

select Cast([timestamp] as date) "date", COUNT(distinct event)"nb events"
from yourtable
group by Cast([timestamp] as date)

Also, it is not a best pratice to use reserved work for column name (like user, timestamp, etc).


Does an analysis/optimization tool like SqlYog exist for SQL Server 2005 (and later)? I'm curious about effective space allocation of my tables, so:

does something like SqlYog exist for SQL Server 2005 (and later)?

Of course I know SSMS, but I was looking for something more precise like column space allocation, and so on... I'd like to find a tool for analysis and optimization of structures and queries.

Microsoft provided, for free, SQL Server Management Studio (SSMS) starting with SQL Server 2005. Is it not exactly like SqlYog but there are similarities. With regards to table space I would direct you to the Standard Reports that were added to SSMS after SQL 2005 SP2. They provided a more eye friendly look at allocation of space, index usage, performance information, etc.


Measuring the relative performance of queries given the assertion that "cost" isn't reliable The cost field is a comparative cost that is used internally to determine the best cost for particular plans. The costs of different statements are not really directly comparable.

Source

Does this make room for a more reliable metric we can use to measure the relative performance (CPU, I/O, or time) of similar queries which are designed to have the same end goal?

There are a variety of metrics you can use.

If you actually execute the queries, Oracle provides a ton of different metrics to evaluate relative performance.  In SQL*Plus, if you enable autotrace, you'll get a quick listing of a few execution statistics after you run a query.  In the vast majority of cases, if you are reducing logical I/O (the "consistent gets" metric in the autotrace output), you're improving response time.

SQL> set autotrace on;
SQL> select ename from emp;

ENAME
----------
SMITH
ALLEN
WARD
JONES
MARTIN
BLAKE
CLARK
SCOTT
KING
TURNER
ADAMS

ENAME
----------
JAMES
FORD
MILLER

14 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 3956160932

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |    14 |    84 |     3   (0)| 00:00:01 |
|   1 |  TABLE ACCESS FULL| EMP  |    14 |    84 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------

Statistics
----------------------------------------------------------
        340  recursive calls
          0  db block gets
         71  consistent gets
          8  physical reads
          0  redo size
        715  bytes sent via SQL*Net to client
        523  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          7  sorts (memory)
          0  sorts (disk)
         14  rows processed

If you want to go into more detail, however, Oracle provides far more metrics.  If you query V$STATNAME, you'll see hundreds of statistics that are available.  V$SESSTAT contains the current value of every statistic for your session so you can grab the "before" data, run a query, then grab the "after" data and look at the change in virtually any performance statistic you might be interested in.  Tom Kyte put together a nice runstats package that automates this so that you don't have to write the test harness yourself.


Is there a way to use Oracle XE as an easy-rollback "proxy" to regular Oracle For development, we're currently connecting to shared dev databases that are way too big for XE (some are 100s of GB). Is there a way to set it up so that we can have a local XE on each developer's machine that acts as a proxy to the shared server for reads, but stores both DDL and DML changes in the local XE schema, so that it can easily be reset by the developer without requiring intervention from a DBA (after initial set up)?

It may be that Oracle has another product that does what I want, rather than XE? This is a big (non tech) company you've all heard of, so (AFAIK) we have a "whole site + whole suite" license.

Any thoughts? Am I dreaming?

Oracle 12c Snap Clone feature would probably fit your bill. Via a self-service portal through OEM, your non-DBA users (i.e., developers) can spin up private thin clones DBaaS-style. Since it uses copy-on-write, it doesn't physically duplicate the source. Each clone just needs space for the deltas. End users can make snap clones off of a TB sized master in minutes and see when they are ready for use. The clones can quickly be deprovisioned and reprovisioned, which would take care of "refreshing" the private DBs when desired. Really cool stuff, but requires an experienced Oracle DBA and equally good & patient Linux/Solaris admin to stand up this infrastructure, basically a private cloud.

UPDATE: although this requires OEM 12c, it works with Database 11gR2 & 12c.  This 20-minute video from Oracle is illuminating:  http://youtu.be/J7fnfLS5Dxg


Index on Persisted Computed column needs key lookup to get columns in the computed expression I have a persisted computed column on a table which is simply made up concatenated columns, e.g.

CREATE TABLE dbo.T
(
    ID INT IDENTITY(1, 1) NOT NULL CONSTRAINT PK_T_ID PRIMARY KEY,
    A VARCHAR(20) NOT NULL,
    B VARCHAR(20) NOT NULL,
    C VARCHAR(20) NOT NULL,
    D DATE NULL,
    E VARCHAR(20) NULL,
    Comp AS A + '-' + B + '-' + C PERSISTED NOT NULL
);

In this Comp is not unique, and D is the valid from date of each combination of A, B, C, therefore I use the following query to get the end date for each A, B, C (basically the next start date for the same value of Comp):

SELECT  t1.ID,
        t1.Comp,
        t1.D,
        D2 = (  SELECT  TOP 1 t2.D
                FROM    dbo.T t2
                WHERE   t2.Comp = t1.Comp
                AND     t2.D > t1.D
                ORDER BY t2.D
            )
FROM    dbo.T t1
WHERE   t1.D IS NOT NULL -- DON'T CARE ABOUT INACTIVE RECORDS
ORDER BY t1.Comp;

I then added an index to the computed column to assist in this query (and also others):

CREATE NONCLUSTERED INDEX IX_T_Comp_D ON dbo.T (Comp, D) WHERE D IS NOT NULL;

The query plan however surprised me. I would have thought that since I have a where clause stating that D IS NOT NULL and I am sorting by Comp, and not referencing any column outside of the index that the index on the computed column could be used to scan t1 and t2, but I saw a clustered index scan.

So I forced the use of this index to see if it yielded a better plan:

SELECT  t1.ID,
        t1.Comp,
        t1.D,
        D2 = (  SELECT  TOP 1 t2.D
                FROM    dbo.T t2
                WHERE   t2.Comp = t1.Comp
                AND     t2.D > t1.D
                ORDER BY t2.D
            )
FROM    dbo.T t1 WITH (INDEX (IX_T_Comp_D))
WHERE   t1.D IS NOT NULL
ORDER BY t1.Comp;

Which gave this plan

This shows that a Key lookup is being used, the details of which are:

Now, according to the SQL-Server documentation:

  You can create an index on a computed column that is defined with a deterministic, but imprecise, expression if the column is marked PERSISTED in the CREATE TABLE or ALTER TABLE statement. This means that the Database Engine stores the computed values in the table, and updates them when any other columns on which the computed column depends are updated. The Database Engine uses these persisted values when it creates an index on the column, and when the index is referenced in a query. This option enables you to create an index on a computed column when Database Engine cannot prove with accuracy whether a function that returns computed column expressions, particularly a CLR function that is created in the .NET Framework, is both deterministic and precise.

So if, as the docs say "the Database Engine stores the computed values in the table", and the value is also being stored in my index, why is a Key Lookup required to get A, B and C when they are not referenced in the query at all? I assume they are being used to calculate Comp, but why? Also, why can the query use the index on t2, but not on t1?

Queries and DDL on SQL Fiddle

N.B. I have tagged SQL Server 2008 because this is the version that my main problem is on, but I also get the same behaviour in 2012.

Why is a Key Lookup required to get A, B and C when they are not referenced in the query at all? I assume they are being used to calculate Comp, but why?

Columns A, B, and C are referenced in the query plan - they are used by the seek on T2.

Also, why can the query use the index on t2, but not on t1?

The optimizer decided that scanning the clustered index was cheaper than scanning the filtered nonclustered index and then performing a lookup to retrieve the values for columns A, B, and C.
Explanation
The real question is why the optimizer felt the need to retrieve A, B, and C for the index seek at all. We would expect it to read the Comp column using a nonclustered index scan, and then perform a seek on the same index (alias T2) to locate the Top 1 record.
The query optimizer expands computed column references before optimization begins, to give it a chance to assess the costs of various query plans. For some queries, expanding the definition of a computed column allows the optimizer to find more efficient plans.
When the optimizer encounters a correlated subquery, it attempts to 'unroll it' to a form it finds easier to reason about. If it cannot find a more effective simplification, it resorts to rewriting the correlated subquery as an apply (a correlated join):

It just so happens that this apply unrolling puts the logical query tree into a form that does not work well with project normalization (a later stage that looks to match general expressions to computed columns, among other things).
In your case, the way the query is written interacts with internal details of the optimizer such that the expanded expression definition is not matched back to the computed column, and you end up with a seek that references columns A, B, and C instead of the computed column, Comp. This is the root cause.
Workaround
One idea to workaround this side-effect is to write the query as an apply manually:
SELECT
    T1.ID,
    T1.Comp,
    T1.D,
    CA.D2
FROM dbo.T AS T1
CROSS APPLY
(
    SELECT TOP (1)
        D2 = T2.D
    FROM dbo.T AS T2
    WHERE
        T2.Comp = T1.Comp
        AND T2.D > T1.D
    ORDER BY
        T2.D ASC
) AS CA
WHERE
    T1.D IS NOT NULL -- DON'T CARE ABOUT INACTIVE RECORDS
ORDER BY
    T1.Comp;

Unfortunately, this query will not use the filtered index as we would hope either. The inequality test on column D inside the apply rejects NULLs, so the apparently redundant predicate WHERE T1.D IS NOT NULL is optimized away.
Without that explicit predicate, the filtered index matching logic decides it cannot use the filtered index. There are a number of ways to work around this second side-effect, but the easiest is probably to change the cross apply to an outer apply (mirroring the logic of the rewrite the optimizer performed earlier on the correlated subquery):
SELECT
    T1.ID,
    T1.Comp,
    T1.D,
    CA.D2
FROM dbo.T AS T1
OUTER APPLY
(
    SELECT TOP (1)
        D2 = T2.D
    FROM dbo.T AS T2
    WHERE
        T2.Comp = T1.Comp
        AND T2.D > T1.D
    ORDER BY
        T2.D ASC
) AS CA
WHERE
    T1.D IS NOT NULL -- DON'T CARE ABOUT INACTIVE RECORDS
ORDER BY
    T1.Comp;

Now the optimizer does not need to use the apply rewrite itself (so the computed column matching works as expected) and the predicate is not optimized away either, so the filtered index can be used for both data access operations, and the seek uses the Comp column on both sides:

This would generally be preferred over adding A, B, and C as INCLUDEd columns in the filtered index, because it addresses the root cause of the problem, and does not require widening the index unnecessarily.
Persisted computed columns
As a side note, it is not necessary to mark the computed column as PERSISTED, if you don't mind repeating its definition in a CHECK constraint:
CREATE TABLE dbo.T
(
    ID integer IDENTITY(1, 1) NOT NULL,
    A varchar(20) NOT NULL,
    B varchar(20) NOT NULL,
    C varchar(20) NOT NULL,
    D date NULL,
    E varchar(20) NULL,
    Comp AS A + '-' + B + '-' + C,

    CONSTRAINT CK_T_Comp_NotNull
        CHECK (A + '-' + B + '-' + C IS NOT NULL),

    CONSTRAINT PK_T_ID
        PRIMARY KEY (ID)
);

CREATE NONCLUSTERED INDEX IX_T_Comp_D
ON dbo.T (Comp, D)
WHERE D IS NOT NULL;

The computed column is only required to be PERSISTED in this case if you want to use a NOT NULL constraint or to reference the Comp column directly (instead of repeating its definition) in a CHECK constraint.


How to find the last data backup on secondary replica? To determine when database has been backed up the last time usually is enough to look into backup tables in msdb database.

However, it doesn't work well with Availability Groups - after the failover new active replica doesn't know when the last FULL\DIFF backup was taken because local msdb database doesn't have those records.

It is a bit easier with log backups - DBCC DBINFO (@DBName) WITH TABLERESULTS gives you the last LOG backup data no matter where it was taken.

My question is - is there an easy way to say on the new active replica, right after failover, when the last data backup was taken?
There is always an option to query each replicas through OPENROWSET (or anything like this), but this introduces unwanted complexity.

The goal is to make sure that code like this works stable even after the failover:

IF dbo.lastBackupWasTakenMoreThanSevenDaysAgo(@DatabaseName) = 1
   < full backup >
ELSE
   < diff backup >

Probably I'm missing something very simple.

Thanks!

The differential_base_time and lsn columns in sys.database_files indicate the point in time and in the log stream when the last non-copyonly backup was taken.  So:

select min(differential_base_time) last_full_backup_utc,
       datediff(second,min(differential_base_time),SYSUTCDATETIME())/( 24*60*60.) days_since_last_full_backup
from sys.database_files
where type_desc = 'ROWS'
and is_read_only = 0

If you have read-only filegroups a backup will not update the differential_base_time, so ignore those.


Slecting specific query returnned data related records from another table Product table:

productId | productName | unitPrice | supplierId
_______________________________________________
    1     | Iphone8     | 1500      |     3
    2     | Nokia 6     | 800       |     1
    3     | Samsung S9  | 1500      |     2

Supplier table:

supplierId | supplierName | country
___________________________________
     1     | Nokia Inc    | India
     2     | Samsung Inc  | Korea
     3     | Apple Inc    | USA

I’m confused, how can I write a query using above two Product and Supplier tables to list the product unitprice 1500 only and their supplier Id, name and country. Such as the table below shows.

    productId | unitPrice| supplierId |supplierName | country
    _____________________________________________
        1     |   1500   |     3      | Apple Inc   | USA
        3     |   1500   |     2      | Samsung Inc | Korea

I used join queries with supplierId on product table to get its relating data from the supplier table. But each time I ended up in error when I try to fetch '= 1500' to this query.

this is the query I tried;

select *
from Product, Supplier
where Product.supplierId = Supplier.supplierId

but it gives all the records, how can I insert "unitPrice = 1500" to this query to achieve my answer?

See if this give you the desired results.

--demo setup
DECLARE @ProductTable TABLE (
  productId INTEGER,
  productName VARCHAR(10),
  unitPrice INTEGER,
  supplierId INTEGER
);

INSERT INTO @ProductTable
  (productId, productName, unitPrice, supplierId)
VALUES
  ('1', 'Iphone8', '1500', '3'),
  ('2', 'Nokia 6', '800', '1'),
  ('3', 'Samsung S9', '1500', '2');

DECLARE @SupplierTable TABLE (
  supplierId INTEGER,
  supplierName VARCHAR(11),
  country VARCHAR(5)
);

INSERT INTO @SupplierTable
  (supplierId, supplierName, country)
VALUES
  ('1', 'Nokia Inc', 'India'),
  ('2', 'Samsung Inc', 'Korea'),
  ('3', 'Apple Inc', 'USA');

--solution
SELECT p.productId
    ,p.unitPrice
    ,p.supplierId
    ,s.supplierName
    ,s.country
FROM @ProductTable P
JOIN @SupplierTable s
    ON s.supplierId = P.supplierId
where p.unitPrice=1500
order by p.productId

| productId | unitPrice | supplierId | supplierName | country |
|-----------|-----------|------------|--------------|---------|
| 1         | 1500      | 3          | Apple Inc    | USA     |
| 3         | 1500      | 2          | Samsung Inc  | Korea   |


Better way to get months contained in a period for querying sales tables I'm trying to automate some sales processing that is currently largely manual. I get the sales by month expressed as
201701, 201702 etc.

Part of what I'm trying to accomplish is better re-use of aggregation queries for historical data, currently there are just a lot of queries saved in the cloud. I need to able to pass in a year and quarter, and get a pattern to match against the sales data. I'd like to accomplish this using SQL's built in datetime functions rather than a case statement and casts.

What's a better way to do the following, that doesn't rely on string manipulation?

-- the @year and @quarter will be passed into a SP
declare @year int = 2017
declare @quarter int = 2

declare @name_timePeriod nvarchar(20) = 'Qtr'+ ' ' + cast(@quarter as nvarchar(2)) + ' ' + cast(@year as nvarchar(4))

declare @contained_periods nvarchar(max) =

case @quarter
    when 1 then cast(@year as nvarchar(4)) + '0[1-3]'
    when 2 then cast(@year as nvarchar(4)) + '0[4-6]'
    when 3 then cast(@year as nvarchar(4)) + '0[7-9]'
    when 4 then cast(@year as nvarchar(4)) + '1[0-2]'
end

I noticed that you have already decided on your solution, but wanted to offer another suggestion which utilizes Aaron Bertrand's Date Dimension table.

There are just so many situations where a Date Dimension table comes in handy.

Assuming you had created and loaded his Date Dimension table, it would be a simple as:

DECLARE @year INT = 2017
    ,@quarter INT = 3

SELECT DISTINCT convert(VARCHAR(4), [Year]) + right(REPLICATE('0', 2) + convert(VARCHAR(2), [Month]), 2) as Period
FROM DateDimension
WHERE [Year] = @year
    AND [Quarter] = @quarter

--OR

SELECT DISTINCT substring(MMYYYY,3,4) + substring(MMYYYY,1,2) as Period
FROM DateDimension
WHERE [Year] = @year
    AND [Quarter] = @quarter

giving

| Period |
|--------|
| 201707 |
| 201708 |
| 201709 |


MSSQL - GRANT EXECUTE permission inside stored procedure Environment

SQL Server 2012 (11.0.2100.60)

Situation

user is allowed to execute a number of stored procedure;
user is readonly denywrite
one of stored procedure has an EXECUTE statement;

The Problem
The user can execute all the stored procedure but not the one having the EXECUTE (even if the EXECUTE is trying to read from a view).

CREATE PROCEDURE [dbo].[SP_INJECT_TEST]

@Query varchar(8000)

AS

SET NOCOUNT ON;
EXECUTE(@Query)

GO;

Question
Is there a specific permission for this situation? Should I switch context and impersonate another USER with all the rights inside the database?

Try GRANT SELECT on the view and then GRANT EXECUTE on the procedure to the user.
see this

https://msdn.microsoft.com/en-us/library/ms188371.aspx


SQL Server: how to determine a column algorithm encryption I have a table with a column, Column_Password, where I can see the password encrypted.

How can I know what type of algorithm encryption the column is using? How can I find if it's sha1, MD5, sha256 or whatever?

My goal is to check if the password is present in this massive database

You can use sys.columns system catalog view.

Details here:

https://docs.microsoft.com/en-us/sql/relational-databases/system-catalog-views/sys-columns-transact-sql


How to group data and write it's group id for each row? I have sample data:

CREATE TABLE #T (Name varchar(5), GroupId int NULL)
INSERT INTO #T (Name)  VALUES
('A'),
('A'),
('A'),
('B'),
('B'),
('C'),
('D'),
('D')

Name  GroupId
----- -----------
A     NULL
A     NULL
A     NULL
B     NULL
B     NULL
C     NULL
D     NULL
D     NULL

How can I group that data by name and after it, write groupId (sequential, maybe identity)? That is what I want to get:

Name  GroupId
----- -----------
A     1
A     1
A     1
B     2
B     2
C     3
D     4
D     4

The table to group has about 15m rows. How to do that better? Thanks!

;WITH cteA
AS(SELECT  Name,GroupID,
        DENSE_RANK () OVER(ORDER BY Name) AS New_GroupID
     FROM #T)

UPDATE  cteA
SET GroupID  = New_GroupID

Now , depending of your system , this could take a few seconds or more.
You can split the update , to do in chunks.
Something like A-G , then from G to M ... and you can add , in the DENSE_RANK  something like MaxGroupID + DENSE_RANK () OVER(ORDER BY Name) AS New_GroupID


sys.database_files / sys.filegroups VS sys.sysfiles / sys.sysfilegroups These catalog views seem to show the exact same data.  What is the point of having multiple system views that seem to show the same thing?

Here is how I proved it is the same data:

select
    fil.name as [FileName],
    fg.name as GroupName
from sys.database_files fil
inner join sys.filegroups fg
on fil.data_space_id = fg.data_space_id

select
    fil.name as [FileName],
    fg.groupname as GroupName
from sys.sysfiles fil
inner join sys.sysfilegroups fg
on fil.groupid = fg.groupid

Almost always, backward compatibility. Often for end user compatibility but also for internal purposes.

I'd swear I was reading an article earlier this week that mentioned one of the old views, possibly sysfiles, having been 99% removed during SQL2005 development but the final 1% proved to difficult/risky to eliminate.

As a general rule, target the most recent versions of any system tables/views in your scripts, they're more likely to still be there in subsequent releases.