Is it possible to avoid filesort? Is it possible to avoid 'Using temporary' and 'Using filesort' for the following SELECT query? I can't figure out a way to do it.

I tried adding indexes, for both top_expire and program, but didn't help
With the ORDER BY the query takes over 1 second and withoud it is just 0.003 seconds on localhost

Query

SELECT ad.*, p.link
    FROM (SELECT ad.*
        FROM mod_ad ad
        JOIN mod_ad_auta auta ON ad.id = auta.ad_id
        WHERE ad.active != 0 AND ad.usr_active != 0 AND ad.expire > 1371151608  AND ad.cat_id = '1' AND ad.price <= '10000'
          AND auta.rocnik BETWEEN '1950' AND '2013'
          AND auta.km BETWEEN '0' AND '500000'
        ORDER BY top_expire DESC, program DESC,  ad.id DESC  LIMIT 0,10) as ad
JOIN pages p ON ad.page_id=p.page_id;

Schema

CREATE TABLE `mod_ad` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `user_id` int(10) unsigned NOT NULL,
  `page_id` int(10) unsigned NOT NULL,
  `cat_id` int(10) unsigned NOT NULL,
  `subcat_id` int(10) unsigned NOT NULL,
  `program` tinyint(1) unsigned NOT NULL,
  `region_id` int(10) unsigned NOT NULL,
  `zone_id` int(10) unsigned NOT NULL,
  `city_id` int(10) unsigned NOT NULL,
  `sort` enum('firm','priv') NOT NULL,
  `type` enum('predaj','kúpa','výmena','darujem','hľadám','ponúkam','iné') NOT NULL,
  `condition` varchar(24) NOT NULL,
  `name` varchar(128) NOT NULL,
  `desc` text NOT NULL,
  `location` varchar(128) NOT NULL,
  `keywords` varchar(255) NOT NULL,
  `price` decimal(10,2) NOT NULL,
  `price_type` varchar(20) NOT NULL,
  `cperson` varchar(128) NOT NULL,
  `firmname` varchar(128) NOT NULL,
  `zip` varchar(5) NOT NULL,
  `email` varchar(255) NOT NULL,
  `tel` varchar(20) NOT NULL,
  `tel2` varchar(20) NOT NULL,
  `web` varchar(255) NOT NULL,
  `video` varchar(255) NOT NULL,
  `marked_expire` int(11) unsigned NOT NULL,
  `top_expire` int(11) unsigned NOT NULL,
  `ad_hot_expire` int(11) unsigned NOT NULL,
  `ad_border_expire` int(11) unsigned NOT NULL,
  `ad_heading_expire` int(11) unsigned NOT NULL,
  `ad_weblink_expire` int(11) unsigned NOT NULL,
  `active` int(10) unsigned NOT NULL,
  `usr_active` int(10) unsigned NOT NULL,
  `added` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `archive` int(10) unsigned NOT NULL,
  `expire` int(11) unsigned NOT NULL,
  `token` varchar(32) NOT NULL,
  `views` mediumint(9) NOT NULL,
  `ip` varchar(15) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `user_id` (`user_id`),
  KEY `page_id` (`page_id`),
  KEY `cat_id` (`cat_id`),
  KEY `region_id` (`region_id`),
  KEY `zone_id` (`zone_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

CREATE TABLE `mod_ad_auta` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `ad_id` int(10) unsigned NOT NULL,
  `model` int(10) unsigned NOT NULL,
  `rocnik` smallint(5) unsigned NOT NULL,
  `palivo` varchar(10) NOT NULL,
  `karoseria` varchar(12) NOT NULL,
  `km` mediumint(8) unsigned NOT NULL,
  `prevodovka` varchar(12) NOT NULL,
  `farba` varchar(16) NOT NULL,
  `metaliza` tinyint(1) unsigned NOT NULL,
  `obsah` smallint(5) unsigned NOT NULL,
  `vykon` smallint(5) unsigned NOT NULL,
  `vybava` text NOT NULL,
  PRIMARY KEY (`id`),
  KEY `ad_id` (`ad_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

The docs say

  In some cases, MySQL cannot use indexes to resolve the ORDER BY,
  although it still uses indexes to find the rows that match the WHERE
  clause. These cases include the following:

and then go on to list many circumstances that prevent MySQL from using indexes. Among them are

  You use ORDER BY on different keys
  The key used to fetch the rows is not the same as the one used in the ORDER BY

and there are probably others.

To avoid a filesort, you have to find a way to get what you want without running into any of those (many) documented conditions.

You can also

  Increase the size of the sort_buffer_size variable.
  Increase the size of the read_rnd_buffer_size variable.
  Use less RAM per row by declaring columns only as large as they need to be to hold the values stored in them.
  Change tmpdir to point to a dedicated file system with large amounts of free space.

(Same link.)

Also see docs for Optimizing LIMIT Queries, which interacts with ORDER BY.


Problem passing rows results to columns names I have a table with following data:

ID  IDALUMNO    ALUMNO                          IDTIPOEVALUACION    TIPOEVALUACION  NOTA
1   682         ALVITEZ MIRANDA, KAREN LIZET    1                   PR1             13
2   682         ALVITEZ MIRANDA, KAREN LIZET    1                   PR2             14
3   682         ALVITEZ MIRANDA, KAREN LIZET    1                   PR3             NULL
4   682         ALVITEZ MIRANDA, KAREN LIZET    4                   TR1             11
5   682         ALVITEZ MIRANDA, KAREN LIZET    6                   PA1             14
6   682         ALVITEZ MIRANDA, KAREN LIZET    6                   PA2             18
7   682         ALVITEZ MIRANDA, KAREN LIZET    6                   PA3             17
8   682         ALVITEZ MIRANDA, KAREN LIZET    6                   PA4             16

And using PIVOT

SELECT [ALUMNO], [PR1], [PR2], [PR3], [TR1], [PA1], [PA2], [PA3], [PA4]
FROM
( SELECT * FROM ALUMNOSEVALUACIONES ) AS [SOURCE]
PIVOT
(
    AVG(NOTA)
    FOR [TIPOEVALUACION] IN ([PR1], [PR2], [PR3], [TR1], [PA1], [PA2], [PA3], [PA4])
) AS PIVOTTABLE

I get the following result:

ALUMNO                          PR1     PR2     PR3     TR1     PA1     PA2     PA3     PA4
ALVITEZ MIRANDA, KAREN LIZET    13      NULL    NULL    NULL    NULL    NULL    NULL    NULL
ALVITEZ MIRANDA, KAREN LIZET    NULL    14      NULL    NULL    NULL    NULL    NULL    NULL
ALVITEZ MIRANDA, KAREN LIZET    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL
ALVITEZ MIRANDA, KAREN LIZET    NULL    NULL    NULL    11      NULL    NULL    NULL    NULL
ALVITEZ MIRANDA, KAREN LIZET    NULL    NULL    NULL    NULL    14      NULL    NULL    NULL
ALVITEZ MIRANDA, KAREN LIZET    NULL    NULL    NULL    NULL    NULL    18      NULL    NULL
ALVITEZ MIRANDA, KAREN LIZET    NULL    NULL    NULL    NULL    NULL    NULL    17      NULL
ALVITEZ MIRANDA, KAREN LIZET    NULL    NULL    NULL    NULL    NULL    NULL    NULL    16

But I need this result:

ALUMNO                          PR1     PR2     PR3     TR1     PA1     PA2     PA3     PA4
ALVITEZ MIRANDA, KAREN LIZET    13      14      NULL    11      14      18     17       16

My question is whether you can get the results as they wish using PIVOT.

Please look at my demo

I haven't done the actual brainwork to articulate what the bug is, but I believe the following code may solve your problem.

with PvtData ( ALUMNO, TIPOEVALUACION, AvgNOTA ) as
    ( select
        ALUMNO
        ,[TIPOEVALUACION]
        ,avg( nota ) as AvgNOTA
    from ALUMNOSEVALUACIONES
    group by ALUMNO
        ,[TIPOEVALUACION] )
select
    *
from PvtData
PIVOT
(
    sum(AvgNOTA)
    FOR [TIPOEVALUACION] IN ([PR1], [PR2], [PR3], [TR1], [PA1], [PA2], [PA3], [PA4])
) AS PIVOTTABLE

Pre-aggregating the average appears to return the correct Data. I hope this helps you!


Merge Replication: Updates to Schema We have a main SQL Server which replicates to two backup servers via continuous merge replication.

There are regular updates to the schema. Regular changes include:

Adding new tables and relationships with existing tables
Changing the data type of columns on tables already replicated

What is the easiest way to maintain replication in such an environment?

If changes are made to the publisher, some changes cannot be synced to the subscriber and the whole replication has to be setup again (agent + subscriptions).

These databases are too big to replicate in its entirety every time there is an update.

Any advice will be appreciated.

Depending on what version of SQL server you are running you can initialize via a backup. However, Microsoft has recommendations as to when it's appropriate to use this processes. Also need to make sure your security is setup properly as well.


How do I implement a database/table as a stack I have a state-machine which needs to push/pop some file-names for different users. I would traditionally use stacks as the choice of data structure, but this needs to be done using a database since I don't have a way to retain the data structure between incoming web-requests.

I was wondering what would be a good way to implement the stack functionality using databases ?

I need to support:

push(fileName, user) : push a fileName for the user
pop(user) : Pop the top-most fileName for the user

EDIT:

I am prototyping an idea, and so I am using sqlite3 with python.

Thanks!

If you're asking about which database to use, it really depends on personal preference and what you want out of it. As I'm only familiar with MySQL, I'll answer the other part of the question assuming MySQL:

you will want to use INNODB because your table is going to be write-intensive and for large tables, the row-locking of INNODB will be a life-saver over MyISAM.

As far as table design, it seems you only really need one table:

CREATE TABLE `wordpress`.`<table_name>` (
`id` smallint(4) NOT NULL AUTO_INCREMENT UNSIGNED,
`user` varchar(30) NOT NULL,
`filename` varchar(255) NOT NULL,
`date_insert` datetime NOT NULL,
PRIMARY KEY (`id`),
UNIQUE `userFile`(user, filename)
) ENGINE=`InnoDB`;

I went with an arbitrary 'id' column set to AUTO_INCREMENT because the primary key is replicated in every entry of every index. So, doing a primary key of (user, filename) could cause performance issues if your filenames are extremely long.

The size of your 'id' column depends on how big your table is going to grow. Unsigned Smallint will give you 65k rows.

User and filenames are varchar, because they'll vary on length drastically I assume.

The date_insert is just a way to order your results based on when it was inserted (helpful for your POP)


Need help in increasing maximum table size Two of the tables vb_post, vb_profilevisitor getting corrupted again and again.

The hosting company suggested the following:

  Looks like the problem is that the .MYD table was converted to a .TMD
  table as per the below article:

  http://www.raskas.be/blog/2008/04/08...and-tmd-files/

  ".TMD basically a temparory file of .MYD. it gets converted into .TMD
  if the data size of the table has exceeded the actual size of the
  data. in that case you can alter the table and increase the MAX size
  of the table."

  We renamed the table back to the .MYD extension, but please check on
  the MAX size of the the table.

May i know how to do it.
I want to change for two tables,

vb_post
vb_profilevisitor

This is my present values in database:

  vb_profilevisitor: present Rows: 11995 Avg_row_length: 15
  Max_data_length: 64424509439 vb_post: present Rows: 1325242
  Avg_row_length: 1514 Max_data_length: 281474976710655

Can I alter it and increase the maximum table size with the following.

ALTER TABLE `vb_profilevisitor` MAX_ROWS=98281474976710655 AVG_ROW_LENGTH=1000000000;
ALTER TABLE `vb_post` MAX_ROWS=98281474976710655 AVG_ROW_LENGTH=1000000000;

Need quick replies.

Sounds like MISSION: IMPOSSIBLE. Just kidding. I just cringe when I see MyISAM.
I can tell you what the MySQL Documentation says. Please note what it says:

You are using a MyISAM table and the space required for the table exceeds what is permitted by the internal pointer size. MyISAM permits data and index files to grow up to 256TB by default, but this limit can be changed up to the maximum permissible size of 65,536TB (2567 – 1 bytes).

With that in mind, you would have to change myisam_data_pointer_size to 7
First, add this to my.cnf under the [mysqld] group header
myisam_data_pointer_size=7

Then, run this in mysql
SET GLOBAL myisam_data_pointer_size = 7;
SET myisam_data_pointer_size = 7;

This will allow your current session and new connections to have the extended capacity and permit you to safely run those ALTER TABLE statements.
Give it a Try !!!
UPDATE 2014-12-03 00:51 EST
I was just thinking. The MyISAM tables vb_profilevisitor  and vb_post may not like the ALTER TABLE because they were created when myisam_data_pointer_size was 6.
To change it manually, do the following
CREATE TABLE vb_post_new LIKE vb_post;
ALTER TABLE  vb_post_new MAX_ROWS=98281474976710655 AVG_ROW_LENGTH=1000000000;
CREATE TABLE vb_profilevisitor_new LIKE vb_profilevisitor;
ALTER TABLE  MAX_ROWS=98281474976710655 AVG_ROW_LENGTH=1000000000;

Check the stats on vb_profilevisitor_new and vb_post_new
SELECT table_name,max_data_length,avg_row_length
FROM information_schema.tables
WHERE table_name IN ('vb_post_new','vb_profilevisitor_new');

If the max_data_length and avg_row_length changed, then copy the table data and swap new tables in
INSERT INTO vb_profilevisitor_new SELECT * FROM vb_profilevisitor;
RENAME TABLE
    vb_profilevisitor TO vb_profilevisitor_zap,
    vb_profilevisitor_new TO vb_profilevisitor
;
DROP vb_profilevisitor_zap;
INSERT INTO vb_post_new SELECT * FROM vb_post;
RENAME TABLE
    vb_post TO vb_post_zap,
    vb_post_new TO vb_post
;
DROP vb_profilevisitor_zap;


Trouble with SQL and aggregates With this query:

SELECT SUM(qty) AS sumQty,
       cnetprodid
FROM   quoteitem
WHERE  ordered = 1
       AND sageSOPOrderReturnLineID IS NOT NULL
       AND LEN(LTRIM(RTRIM(cnetprodid))) > 0
       AND cnetprodid LIKE 'S%'
       AND price > 10
       AND created > DATEADD(MONTH, -3, GETDATE())
GROUP  BY cnetprodid
ORDER  BY SUM(qty) DESC

I get one row back per product, the top row has 294 as sumQty and 'S10381902' as the cnetprodid

Enter this query:

SELECT SUM(qty) AS sumQty,
       p.ProdID,
       p.CatID
FROM   quoteitem
       INNER JOIN cds_prod AS p
         ON p.ProdID COLLATE Latin1_General_CI_AS = cnetprodid
       INNER JOIN cds_atr AS a
         ON a.ProdID COLLATE Latin1_General_CI_AS = p.ProdID
       INNER JOIN lan_cat_sections AS c
         ON c.attID = a.atrID
             OR ( LEN(ISNULL(c.attID, '')) = 0
                  AND c.cat_id LIKE p.CatID + '%' )
                AND ( ( c.[include] = 0
                        AND a.valID NOT IN (SELECT Value
                                            FROM   Split(c.valID, ',')) )
                       OR ( c.[include] = 1
                            AND a.valID IN (SELECT Value
                                            FROM   Split(c.valID, ',')) ) )
WHERE  ordered = 1
       AND sageSOPOrderReturnLineID IS NOT NULL
       AND LEN(LTRIM(RTRIM(cnetprodid))) > 0
       AND cnetprodid LIKE 'S%'
       AND price > 10
       AND created > DATEADD(MONTH, -3, GETDATE())
GROUP  BY p.ProdID,
          p.CatID,
          a.prodID
HAVING SUM(qty) > 10
ORDER  BY SUM(qty) DESC

I get one row per product back but now the row with 'S10381902' as the ProdID column has a figure of 8820 for sumQty.

All I want is a number for how many products have been sold in the last quarter, not interested in products that have sold less than 10 units, hence the HAVING part.

The COLLATE is there because the tables come from different databases; one is in SQL_Latin1_General_CP1_CI_AS and another database is in Latin1_General_CI_AS.

Fiddle: http://sqlfiddle.com/#!3/ce1ed/4

The likely problem is that, by joining to those additional tables, rows in quoteitem become represented more than once in the intermediate result. That is, you have a one-to-many join at some point. SQL Server then obediently sums up those rows multiple times. It's a mistake that's very easy to make.

The simple workaround is to perform your aggregate calculations before doing any one-to-many joins. It might look something like this.

SELECT q.sumQty,
       p.ProdID,
       p.CatID
FROM (
        SELECT ProdID, SUM(qty) AS sumQty, ...other columns...
        FROM quoteitem
        GROUP BY ProdID
        HAVING SUM(qty) > 10
    ) q
       INNER JOIN cds_prod AS p
         ON p.ProdID COLLATE Latin1_General_CI_AS = cnetprodid
       INNER JOIN cds_atr AS a
         ON a.ProdID COLLATE Latin1_General_CI_AS = p.ProdID
       INNER JOIN lan_cat_sections AS c
         ON c.attID = a.atrID
             OR ( LEN(ISNULL(c.attID, '')) = 0
                  AND c.cat_id LIKE p.CatID + '%' )
                AND ( ( c.[include] = 0
                        AND a.valID NOT IN (SELECT Value
                                            FROM   Split(c.valID, ',')) )
                       OR ( c.[include] = 1
                            AND a.valID IN (SELECT Value
                                            FROM   Split(c.valID, ',')) ) )
WHERE  ordered = 1
       AND sageSOPOrderReturnLineID IS NOT NULL
       AND LEN(LTRIM(RTRIM(cnetprodid))) > 0
       AND cnetprodid LIKE 'S%'
       AND price > 10
       AND created > DATEADD(MONTH, -3, GETDATE())
GROUP  BY p.ProdID,
          p.CatID,
          a.prodID
ORDER  BY q.sumQty DESC


Check whether a key value is referenced in any related table I have one table in the database having ID as the primary key. Now this ID is referenced (as Foreign Key) in many other tables. How can I create a result with each row that whether it is referenced or not. As I want to prevent users from deleting items that have references. So the output should be:

ID   Title(Another Column in the table)   IsReferenced
--   ----------------------------------   ---------------------------------
1    Title 1                              True (or Count any one will work)
2    Title 2                              True
3    Title 3                              False
4    Title 4                              False

Given this data:

USE tempdb;
GO

CREATE TABLE dbo.Books
(
  BookID int PRIMARY KEY,
  title varchar(32)
);
-- insert 3 rows
INSERT dbo.Books(BookID, title) VALUES(1,'no relations'),
    (2,'one relation'),(3,'all relations');

CREATE TABLE dbo.OverdueBooks
(
  BookID int NOT NULL FOREIGN KEY REFERENCES dbo.Books(BookID)
);
-- insert 1 row
INSERT dbo.OverdueBooks(BookID) VALUES(2);

CREATE TABLE dbo.OneStarBooks
(
  BookID int NOT NULL FOREIGN KEY REFERENCES dbo.Books(BookID)
);
-- insert 2 rows
INSERT dbo.OneStarBooks(BookID) VALUES(2),(3);

You can avoid knowing all of the tables and columns that reference the parent by building dynamic SQL from the metadata about the foreign keys:

-- all you need to identify is the table / column that is the parent:
DECLARE @parent nvarchar(800) = N'dbo.Books.BookID';

DECLARE @sql nvarchar(max) = N'';

SELECT @sql += N'
UNION ALL SELECT ' + QUOTENAME(c.name) + N' AS id FROM '
 + QUOTENAME(s.name) + N'.' + QUOTENAME(o.name) + N' WHERE '
 + QUOTENAME(c.name) + N' = ' + PARSENAME(@parent,2) + N'.' + PARSENAME(@parent,1)
FROM sys.foreign_keys AS fk
INNER JOIN sys.foreign_key_columns AS fkc
ON fk.object_id = fkc.constraint_object_id
INNER JOIN sys.columns AS c
ON fkc.parent_object_id = c.object_id
AND fkc.parent_column_id = c.column_id
INNER JOIN sys.objects AS o
ON c.object_id = o.object_id
INNER JOIN sys.schemas AS s
ON o.schema_id = s.schema_id
WHERE fk.referenced_object_id = OBJECT_ID(PARSENAME(@parent,3)+N'.'+PARSENAME(@parent,2));

SET @sql = N'SELECT Books.BookID, Books.Title,
  IsReferenced = MIN(CASE WHEN x.id IS NOT NULL THEN ''True'' ELSE ''False'' END)
  FROM dbo.Books
  OUTER APPLY
  (
  ' + STUFF(@sql,1,CHARINDEX(N'SELECT',@sql)-1,N'') + N'
  ) AS x(id)
GROUP BY Books.BookID, Books.Title;';

PRINT @sql;
--EXEC sys.sp_executesql @sql;

When you're happy with the PRINT output (if you have tons of foreign keys, it may get truncated; see this tip for ideas), uncomment the EXEC. My print output looked like this:

SELECT Books.BookID, Books.Title,
  IsReferenced = MIN(CASE WHEN x.id IS NOT NULL THEN 'True' ELSE 'False' END)
  FROM dbo.Books
  OUTER APPLY
  (
  SELECT [BookID] AS id FROM [dbo].[OverdueBooks] WHERE [BookID] = Books.BookID
UNION ALL SELECT [BookID] AS id FROM [dbo].[OneStarBooks] WHERE [BookID] = Books.BookID
  ) AS x(id)
GROUP BY Books.BookID, Books.Title;

And when I ran that I got the expected:

BookID  Title          IsReferenced
------  -------------  ------------
1       no relations   False
2       one relation   True
3       all relations  True

Don't forget to clean up tempdb:

DROP TABLE dbo.OneStarBooks, dbo.OverdueBooks, dbo.Books;


Postgres to_tsvector is case sensitive on mac os On PostgreSQL 10.4 on Ubuntu 10.4-2.pgdg16.04+1

select to_tsvector('simple', 'БОЛЬШИЕ БУКВЫ');

returns 'большие':1 'буквы':2

But on postgres 10.3 (installed by brew) on Mac Os High Sierra version 10.13.3 it returns 'БОЛЬШИЕ':1 'БУКВЫ':2 for not english letters

How can I fix it on Mac os?

The ability to identify the case of characters depend on the LC_CTYPE of your database, which by default, depends on the environment in which the PostgreSQL instance has been created (with initdb).

For instance, on Ubuntu with PostgreSQL11:

tstc=# show lc_ctype;
 lc_ctype
----------
 C

tstc=# select to_tsvector('simple', 'БОЛЬШИЕ БУКВЫ');
      to_tsvector
-----------------------
 'БОЛЬШИЕ':1 'БУКВЫ':2
(1 row)

That's the result you got on your db on MacOS.

But when I'm logged to a different database, with an UTF-8 locale this time:

postgres=# show lc_ctype;
  lc_ctype
-------------
 fr_FR.UTF-8
(1 row)

postgres=# select to_tsvector('simple', 'БОЛЬШИЕ БУКВЫ');
      to_tsvector
-----------------------
 'большие':1 'буквы':2
(1 row)

Now letters are put in lower case.

The fix is to create the database with the correct LC_CTYPE. It cannot be changed afterwards. By default, this setting comes from template1, but it can be overriden by choosing template0, if template1 does not suit you, for instance:

CREATE DATABASE newDB lc_ctype='C.UTF-8' template template0;

The locale specified with lc_ctype must also be supported by the system. Check with locale -a or some equivalent if that doesn't work on MacOS.


What is meant by Account is disabled in dba_users password column for account status as locked? When querying dba_views, for some of the users the password column shows account disabled. These users are locked accounts as account_status column says. I want to understand what is meant by account disabled? Is it same as expired and locked account or it has some other meaning?

I want to understand what is meant by account disabled? Is it same as expired and locked account or it has some other meaning?

As per Oracle BOL , Yes, A locked account is an account for which login is disabled.

For Example : Oracle Database provides sample schema account, SCOTT. The SCOTT schema contains the tables EMP, DEPT, SALGRADE, and BONUS. The SCOTT account is used in examples throughout the Oracle Database documentation set. When you install Oracle Database, the SCOTT account is locked and expired.

  Locking an account preserves the user password, as well as other
  account information, but makes the account unavailable to anyone who
  tries to log in to the database using that account. Unlocking it makes
  the account available again.

For your Ref:-  Here and Here


MySQL Clustered Index or Non Clustered index? (Database Design) I know this might be a basic thing for you guys in this corner of SE but I'm having some trouble figuring out the best option for designing my DB.
I am creating a custom app where the user will be entering data into a table(lets call this table "manifest") at least 1 entry every minute, each entry will have data grabbed from the input field and when adding the new row, the current date and time is also being added to a datetime column.
This table will later be browsed by the app users on the front end at least 4 times a week when they come to do some exporting of their previously entered data.
I expect them to a lot of ordering by date, so I was planning to do an index on the date column for the first 8 characters of the MYSQL DATE = 00/00/00 (id leave out the time portion).

From what I know about indexes is that they slow down inserting and updating of data because the index needs to be rebuilt every time new data is entered.
This sounds like it would be inefficient to create an index(other than the primary key[id]) if the users will be inputting data at least once a minute for maybe 2 hours straight(per day) and the index would have to be rebuilt every minute(?) new data comes in.

I know this table would just keep growing because they need to keep track of the records for years on end, that's why I'd want to put an index on the date column to make it future proof in terms of speed. But the fact that they'd be inputting data into it so frequently and indexes would have to be rebuilt every time has me a little lost on how to proceed.
I was thinking a non-clustered index would solve the issue? I've watched several videos explaining the two and it seems that the rebuilding would still need to take place for non-clustered indexes?
Any help and performance tips would be appreciated

About PRIMARY KEYs:

Each table must have a PRIMARY KEY.
The PK (in MySQL's InnoDB engine) is always "clustered"
Indexes other than the PK are non-clustered.
The PK is "unique".  It uniquely identifies each row.

In your question:

One INSERT per minute is very slow.  (So, no performance concerns here, regardless of the indexes on the table, regardless of anything else.)
"Exporting" rows -- Do you mean "read" (SELECT) or "archive" (SELECT, then DELETE)??  (More details, please.)
"first 8 characters of the MYSQL DATE = 00/00/00" -- Are you not storing the DATE part?  If not, then make it a TIME column.  If you want to "extract" just the time (from a DATETIME, see the TIME() function.
"indexes ... slow down inserting and updating of data because the index needs to be rebuilt every time new data is entered."  NO, NO, NO.  Indexes are not rebuilt except on explicit requests -- which are rarely warranted.
Having an index does slow down writes slightly, but the index is likely to speed up reads significantly.  So, don't fear having indexes.
After 10 years, you will have about 5 million rows.  As tables go, this is only medium-sized.  Not a problem.  However, for accessing individual rows or groups of rows, indexes (whether PK or secondary) are strongly advised.
"I'd want to put an index on the date column to make it future proof in terms of speed." -- That does not "future-proof" it.  It will help some SELECTs, but not others.  Show us the selects so we can recommend a set of indexes that would be optimal for those selects.
Later, if you add/drop/change an index (primary or secondary), there will be some amount of effort, possibly involving copying the table over.  It is premature to worry about that.


Index before or after bulk load using load infile? I have a database with over 1B rows and two columns that are indexed (in addition to the PK).
Is it better to have the index pre-defined in the table before the load infile or better to index after the data has been loaded?

A couple of notes regarding data size and system:

System is Linux w/ 8 cores and 32GB memory (currently maxed out
unless I move to new HW)
DB is 1B rows that in raw data size is 150GB data.
Database is MyISAM and is mainly read-only after it's loaded.

I have tried a different variety of solution with a similar data load -over 1B- but the better that I have found is this:
From MySQL documentation
With some extra work, it is possible to make LOAD DATA INFILE run even faster for a MyISAM table when the table has many indexes. Use the following procedure:

Execute a FLUSH TABLES statement or a mysqladmin flush-tables command.

Use myisamchk --keys-used=0 -rq /path/to/db/tbl_name to remove all use of indexes for the table.

Insert data into the table with LOAD DATA INFILE. This does not update any indexes and therefore is very fast.

Re-create the indexes with myisamchk -rq /path/to/db/tbl_name. This creates the index tree in memory before writing it to disk, which is much faster that updating the index during LOAD DATA INFILE because it avoids lots of disk seeks. The resulting index tree is also perfectly balanced.

Execute a FLUSH TABLES statement or a mysqladmin flush-tables command.

LOAD DATA INFILE performs the preceding optimization automatically if the MyISAM table into which you insert data is empty. The main difference between automatic optimization and using the procedure explicitly is that you can let myisamchk allocate much more temporary memory for the index creation than you might want the server to allocate for index re-creation when it executes the LOAD DATA INFILE statement.
In order to obtain better performance from the myisamchk you have to tune some params like :
--key_buffer_size --myisam_sort_buffer_size --read_buffer_size --write_buffer_size

Depending on your hardware architecture
Note
When using LOCAL with LOAD DATA, a copy of the file is created in the server's temporary directory. This is not the directory determined by the value of tmpdir or slave_load_tmpdir, but rather the operating system's temporary directory, and is not configurable in the MySQL Server.
So, you you have this kind of problem and your file it's a csv, you can split you "huge" file into chunks
$ split -l (numbersofrowsinfile / ((filesize/tmpsize) + 1)) /path/to/your/<file>.csv

Then repeat your LOAD DATA LOCAL (step 3) for every chunk file.


Permissions required for running extended events I've found this link: Changes to Extended Events Permission Requirements in SQL Server 2012 that mentions the need of CONTROL SERVER permission for using extended events on SQL Server 2008. I want to grant access to extended events to developers, is not any other way to do it? We are running on SQL Server 2008R2.

As Jonathan stated and the link shows CONTROL SERVER is required prior to SQL Server 2012 to create an Extended Event session. The ALTER ANY EVENT SESSION permission prior to SQL Server 2012 only allowed a user to start/stop a session, or change a current session; it will not allow them to create a session. CONTROL SERVER is required for that ability.

If this is a production system it is based on your company's policies but I would not provide a developer that level of access to anything in production. I would likely create a few different sessions that they might need for troubleshooting code in production, and then grant them ALTER ANY EVENT SESSION so they could run that as needed. They would also have the ability to then change the session as they see fit. However, be aware that even though Extended Events has less overhead than Profiler it still has overhead depending on what you are trying to capture. I would hope the developers have an understanding on what events can cause additional overhead.


What are the minimum persmission required to restore mysql dump I'm trying to create a cronjob which will be populating the data on the server from a local machine. What are the best permission which are required to restore mysql Dump

I want to restore the dump on the web server I want to know what are the least privileges that A user on the Web server should have so that It can restore the database on the web server.

1) what server is the original database on?

MySQL Local Machine.

2) What mysqldump command do you run as what user to get the sql file?

mysqldump -u user -p pass dbname > db.sql "Here i don't have permissions issue."

3) What server are you restoring the dump file to? Or is it the same machine?

Its a Webserver machine resides in the cloud. And having MySQL server here as well. There is no issue of the local and web server MySQL version.

4) If not, how are you transferring the sql dump (i.e. which user account?)
mysql -hweb.server.ip -u restore_user -phalal db < db.sql

5) What command are you hoping to run on the second server to restore the database?

I am executing the command mentioned in the 4th question on the local machine. which restores the dump remotely.

Now  I am having issue of the restore_user permissions I don't want to give the maximum permission to the restore_user only the necessary permissions should be there as the data is much sensitive.

For normal database restore operations, the MySQL database user (which you pass as an argument into the mysqldump command) will needs privileges related to:

STRUCTURE (Schema)
DATA manipulation

For common STRUCTURE operations the user typically will need:

DROP
CREATE
ALTER
TRIGGER

DATA privileges typically should be

SELECT, INSERT, UPDATE and DELETE.

Importantly database structure administration privelege GRANT can be revoked.
By starting with this limited set and then adding privileges on an as needed basis you are improving the containment policies.


How to get the oracle dataguard log transfer and applying status? Logs are transferred from primary db instances to stand-by db and applied continuously.

Please advise how to get the report in the following format (preferably in browser) at some frequency (say 5sec/10sec) for monitoring purposes at stand-by db (prefer to any built-in function or class in oracle dataguard):

-----------------------------------------------------------------------------------
log_name   instance   created_time   tx_start    tx_end  tx_duration  tx_success   apply_start    apply_end   apply_duration apply_success
-----------------------------------------------------------------------------------
...
...
...
-----------------------------------------------------------------------------------

Oracle Dataguard does not transfer transactions, it transfers log records. Physical standby knows nothing about transactions (logical things), it knows only about changes to the files (physical changes).

From the standby side, you can check v$dataguard_stats view. It gives information for "transport lag" and "apply lag" in terms of time.

From primary and standby you can see  what logs were archived (transmitted) and what were applied to the standby:

select sequence#, to_char(completion_time, 'dd.mm.yyyy hh24:mi:ss') completion_time,
       deleted, applied, dest_id, STANDBY_DEST, fal, round(blocks*block_size/1024/1024) mb
  from v$archived_log
 where completion_time > sysdate - 3/24
 order by sequence#, dest_id;

If you have logical standby, then you can look into v$logstdby_progress, etc;

https://docs.oracle.com/cd/B19306_01/server.102/b14239/manage_ps.htm#CHDFFFAJ
https://docs.oracle.com/cd/B28359_01/server.111/b28294/manage_ls.htm#g1057004


What impact does UseMicrosoftUpdate have on the UpdateSource option when installing SQL Server 2014? The ConfigurationFile.ini generated for SQL Server 2014 contains a new parameter that I cannot find documented anywhere - UseMicrosoftUpdate.

What impact does this parameter have on the UpdateSource and UpdateEnabled parameters?

UseMicrosoftUpdate - Setting it to TRUE - it will change the computer settings to include the latest updates when you scan for Windows Update.
I would suggest to set it FALSE and you decide what updates you want for SQL Server in-terms of SPs/CUs.
From BOL :

UpdateEnabled - Specify whether SQL Server setup should discover and include product updates. The valid values are True and False or 1 and 0. By default, SQL Server setup will include updates that are found.
UpdateSource - Specify the location where SQL Server setup will obtain product updates. The valid values are "MU" to search Microsoft Update, a valid folder path, a relative path such as .\MyUpdates or a UNC share. By default, SQL Server setup will search Microsoft Update or a Windows Update Service through the Windows Server Update Services

So the UseMicrosoftUpdate is more of a global thing governed as part of windows update vs UpdateEnabled is governed only during sql server installation/setup and UpdateSource is the location that will have sql server update files. Thats my understanding.
Honestly, I will not even enable any of those, since any SP/CU has to be regression tested and lately there are many problems with SP/CUs that can screw up many things. They are addressed with subsequent hotfixes.
With introduction of SQL Server 2016, Microsoft has made changes to their Incremental Servicing Model (ISM)

As of January CU releases, we now recommend ongoing, proactive installation of CU’s as they become available. You should plan to install a CU with the same level of confidence you plan to install SPs (Service Packs) as they are released. This is because CU’s are certified and tested to the level of SP’s.


How to read T-SQL time statistics? I have a simple stored procedure as an example, which reads a table with 3 rows.

CREATE PROCEDURE demo_procedure
AS
    BEGIN
        SELECT * FROM my_table
    END

When I run it with time statistics on:

SET STATISTICS TIME ON
GO
exec demo_procedure
GO

I get:

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

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

(3 rows affected)

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

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

How should I read this data? Does the stored procedure takes 2 ms, 3 ms or 5 ms?

Parse and compile is the time it takes to validate syntax and object validity, then compile an execution plan. Elapsed time is how long it takes the query to execute and finish returning rows to the client (SSMS). If you're returning a lot of rows, the query itself may be done long before rows are done rendering in SSMS.


Scheduling MySQL Workbench to execute and export on a daily basis Here's my question.

I have a couple scripts (very simple stuff; select queries) that I run in MySQL Workbench which I can than export to CSVs. What I want to know is if it is possible to schedule MySQL Workbench to run the scripts and export to CSVs at a specific time everyday.

I am using Windows 7.

Use the Windows Task Scheduler or if you want to use the command line, then an at job should do the trick. On Window 7, the schtasks (see also here) should be of use.

MySQL Workbench is not for doing this, except if you have the Enterprise Edition.


MySQL test if function is available Since MySQL 5.8 the function GeomFromText() is no longer available and one should use ST_GeomFromText() instead, which works exactly the same. Progress, of course :-(

Ref: https://dev.mysql.com/doc/refman/5.7/en/gis-wkt-functions.html#function_geomfromtext

Now I'd like to use this function in a way that is backwards compatible so it can run on very old MySQL server versions as much as possible. What is the best way to check if this function is defined and which of the two (if any) I can use? I'd like to prevent having to check the version number of the server to select the correct query, if at all possible.

You can use the IF() function, VERSION() function and Dynamic SQL:

SET @dynamic_func_name = CONCAT(IF(LEFT(VERSION(),3)>'5.6','ST_',''),'GeomFromText');
SET @sql = CONCAT('CALL ',@dynamic_func_name,'(wkt,srid)');
PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;

I used the left 3 character of the VERSION() to get the major release. Why ?

If LEFT(VERSION(),3) returns 5.7 or more, @dynamic_func_name will be ST_GeomFromText
If LEFT(VERSION(),3) returns 5.6 or less, @dynamic_func_name will be GeomFromText


SQL Server transactional replication is out of sync I have setup transactional replication in SQL Server and I am using PULL to retrieve the data from the subscriber.

Recently, I noticed that the data in my subscriber is not synced up with the database on the publisher server.

Also, I found the following issues:

The replication monitor on the publisher indicates that the performance is critical and the latency is 00:04:25
The replication monitor also indicates there are 56000 undistributed commands. (This has not changed, seems to be stuck)
The "Distributor to Subscriber History" indicates there are 4 running sessions of the distribution agent.

I've setup verbose logging on the distribution agent job, and I am seeing this error message:

  Raised events that occur when an agent's reader thread waits longer
  than the agent's -messageinterval time. (By default, the time is 60
  seconds.) If you notice State 2 events that are recorded for an agent,
  this indicates that the agent is taking a long time to write changes
  to the destination.

I have tried restarting the distribution agent job, but this doesn't seem to fix the issue. I am not really sure where to troubleshoot next.

Also, it is worth mentioning that the subscriber is running on a secondary SQL Server instance, on the same physical server as the publisher, so I would think latency should be low.

check it out the scripts on this question:

Replication Monitor Information using T-SQL

Just make sure you run them in the right place - publisher, distributor or subscriber.

  Recently, I noticed that the data in my subscriber is not synced up
  with the database on the publisher server.

first thing is to find out what is there to be replicated that has not been, and why.
this will give you a starting point:

from Querying msdistribution_status slow

--this is to be run on your distributor server if you have one, distribution database

USE distribution;

SET NOCOUNT ON;
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

      SELECT t.article_id,s.agent_id,
    'UndelivCmdsInDistDB'=SUM(CASE WHEN xact_seqno > h.maxseq THEN 1 ELSE 0 END),
    'DelivCmdsInDistDB'=SUM(CASE WHEN xact_seqno <= h.maxseq THEN 1 ELSE 0 END)
    FROM (SELECT article_id,publisher_database_id, xact_seqno
    FROM MSrepl_commands (NOLOCK) ) as t
    JOIN (SELECT agent_id,article_id,publisher_database_id FROM MSsubscriptions
    (NOLOCK) ) AS s
    ON (t.article_id = s.article_id AND
    t.publisher_database_id=s.publisher_database_id )
    JOIN (SELECT agent_id,'maxseq'= isnull(max(xact_seqno),0x0) FROM
    MSdistribution_history (NOLOCK) GROUP BY agent_id) as h
    ON (h.agent_id=s.agent_id)
    GROUP BY t.article_id,s.agent_id

and this one:

USE distribution;

SET NOCOUNT ON;
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

select rc.publisher_database_id, rc.xact_seqno, rc.command, rt.entry_time
   from MSrepl_commands rc, MSrepl_transactions rt
    where rc.xact_seqno =  rt.xact_seqno

-- this is to be run on your publication server

this will find out what is pending, between the data you get here, and those queries above,
it will give you some info

SET NOCOUNT ON;
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
--You can only specify the READPAST lock in the READ COMMITTED or REPEATABLE READ isolation levels.
exec sp_replmonitorsubscriptionpendingcmds
 @publisher ='ctdb1',
 @publisher_db = 'APIA_Repl_Pub',
 @publication ='APIA_Repl_Pub', --'Enter name of publication',
 @subscriber ='SQLPROD',
 @subscriber_db ='APIA_Repl_Sub',
 @subscription_type ='0' --0 for push and 1 for pull

  I have tried restarting the distribution agent job, but this doesn't
  seem to fix the issue. I am not really sure where to troubleshoot
  next.

there should be only one process reading the log and marking the transactions as replicated otherwise you get errors like this one:

-- Msg 18752, Level 16, State 1, Procedure sp_replcmds, Line 1 [Batch Start Line 212]
--Only one Log Reader Agent or log-related procedure
--(sp_repldone, sp_replcmds, and sp_replshowcmds) can connect to a database at a time.
--If you executed a log-related procedure,
--drop the connection over which the procedure was executed or execute sp_replflush
--over that connection before starting the Log Reader Agent or executing another log-related procedure.

you mention:

  Also, it is worth mentioning that the subscriber is running on a
  secondary SQL instance, on the same physical server as the publisher,
  so I would think latency should be low.

that has an impact on the transaction log.
I understand that you are talking about availability groups?
check these scripts to see if it is healthy.

what is the size of your transactions?

what is the size and VLFs of your transaction logs?

--===============================================
--check the transaction log size for all dbs and percent available
--===============================================
DECLARE @log_stats TABLE (
    [DBName] [nvarchar](128) NOT NULL,
    log_size decimal(10,5),
    log_used decimal(10,5),
    [status] int
 PRIMARY KEY CLUSTERED
(
    [DBName] ASC
)
)

          INSERT INTO @log_stats(

                            [DBName],
                            log_size,
                            log_used,
                            [status]

          )
EXEC('SET NOCOUNT ON;DBCC SQLPERF(LOGSPACE) WITH NO_INFOMSGS;')

SELECT [DBName]
      ,log_size=CAST (log_size AS NUMERIC (10,2))
      ,log_used=CAST (log_used AS NUMERIC (10,2))
      ,[status]
FROM @log_stats

--===============================================
-- DBCC LOGINFO into table
--===============================================
DECLARE @log_stats TABLE (
RecoveryUnitId  bigint,
FileId int,
FileSize bigint,
StartOffset bigint,
FSeqNo  int,
Status  int,
Parity  int,
CreateLSN int
)

          INSERT INTO @log_stats(

RecoveryUnitId, FileId, FileSize,   StartOffset,    FSeqNo, Status, Parity, CreateLSN

          )
EXEC('SET NOCOUNT ON;DBCC LOGINFO(''YOUR PUBLICATION DATABASE GOES HERE'') WITH NO_INFOMSGS')
SELECT * FROM @log_stats

how it works?

The reader thread of the Log Reader executes sp_replcmds to pull commands from the transaction log.
The stored procedures below can be executed in SQL Server Management Studio to track time required to pull next pending replicated transaction.

How long do these command take to execute?
How many commands were returned?

Scanning a large transaction log for a single transaction containing a million+ row modifications may take 20+ minutes to complete.

The execution time for this query approximates the startup time for the Log Reader to replicate this same transaction.
 Display 1st pending transaction metadata such as publication_id, article id, xactid, and command_type.

Is this command part of normal database operation or an unexpected large batch of commands?

 --Return all commands from the next pending transaction
 sp_replcmds @maxtrans = 1
 GO

 --Return all commands for the next 500 transactions
 sp_replcmds @maxtrans = 500
 GO
 sp_replflush
 GO

this will give you a good starting point, however, it is a complex environment, and there are things not so clear from your question.

I tried to answer the best I could, if you let me know how it goes, I can improve my answer.


Calculate all latitude and longitude distance in result using MySQL this is my current query to calculate between two latitude and longitude, but how do i using this or similar formula to calculate all the latitude and longitude in result?

SELECT ROUND(6353 * 2 * ASIN(SQRT( POWER(SIN((a.GPSLat -
      abs(b.GPSLat)) * pi()/180 / 2),2) + COS(a.GPSLat * pi()/180 ) * COS(
      abs(b.GPSLat) *  pi()/180) * POWER(SIN((a.GPSLon - b.GPSLon) *
      pi()/180 / 2), 2) )), 2) as TotalKM
from table1 a
      inner join table1 b on a.ID = 70 and b.ID = 71;

This example of Lat and Lon in my database

The result count by id70 and id71

Edited : The result that i need is

(id69 + id70 = 0.4km),

(0.4km + id71 = 0.7km),

(0.7km + id72 = 1.0km),

totalkm is 2.1km

ID | GPSLAT   | GPSLon     | DistanceInKM
69 | 3.214502 | 101.638419 | 0.0
70 | 3.214478 | 101.638101 | 0.4
71 | 3.214668 | 101.638049 | 0.7
72 | 3.215089 | 101.638022 | 1.0
                           |TotalKM
                             2.1

Add something like this onto the query in order to get the TotalKM

GROUP BY id, gpslat, gpslon WITH ROLLUP

Don't attempt to get that exact output with SQL; do that in you application code.  The ROLLUP will give you an extra line something like

NULL NULL NULL 2.1


How to pass scheme name to PgSQL function? I'm using PostgreSQL 9.6 (can move to 12 if needed).

I have many schemes with same table_name, so I want to pass scheme_name to functions like this:

CREATE FUNCTION public.test()
RETURNS TABLE(base_name text, slow real)
    LANGUAGE plpgsql
    AS $$
BEGIN
RETURN QUERY
    SELECT * FROM scheme_name.bases, scheme_name.servers
    WHERE scheme_name.bases.id = scheme_name.servers.id;
END
$$;

Answer:

  DROP FUNCTION IF EXISTS public.test;
    CREATE FUNCTION public.test(scheme_name character varying)
    RETURNS TABLE(base_name text, scheme_name text)
        LANGUAGE plpgsql
        AS $$
    BEGIN
    RETURN QUERY EXECUTE
        'SELECT ' ||scheme_name||'.bases.base_name, ' ||scheme_name||'.servers.scheme_name FROM ' ||param||'.bases,'||param||'.servers'
        ' WHERE ' ||scheme_name||'.stations.id = ' ||scheme_name||'.servers.id';
    $$;


Cassandra - Why does Read/Write Latency fluctuate? Question

Why does write (read as well) latency fluctuate in Cassandra?

Background

Running Cassandra Stress Tool on a node (s01) against another node (m01). Run several times but always saw the latency (write as well as read) always flucuating. The metric is from Cassandra JMX oneMinuteRate of the keyspace.

$syds01 > cassandra-stress write -node sydm01

The replication factor was changed to 1.

cqlsh > ALTER KEYSPACE keyspace1 WITH REPLICATION = { 'class' : 'SimpleStrategy', 'replication_factor' : 1 };

Write

Result (example)

Results:
Op rate                   :   73,184 op/s  [WRITE: 73,184 op/s]
Partition rate            :   73,184 pk/s  [WRITE: 73,184 pk/s]
Row rate                  :   73,184 row/s [WRITE: 73,184 row/s]
Latency mean              :   12.4 ms [WRITE: 12.4 ms]
Latency median            :    6.2 ms [WRITE: 6.2 ms]
Latency 95th percentile   :   40.6 ms [WRITE: 40.6 ms]
Latency 99th percentile   :   73.8 ms [WRITE: 73.8 ms]
Latency 99.9th percentile :  127.3 ms [WRITE: 127.3 ms]
Latency max               :  423.4 ms [WRITE: 423.4 ms]
Total partitions          :  6,108,134 [WRITE: 6,108,134]
Total errors              :          0 [WRITE: 0]
Total GC count            : 206
Total GC memory           : 64.555 GiB
Total GC time             :    8.2 seconds
Avg GC time               :   40.0 ms
StdDev GC time            :   17.9 ms
Total operation time      : 00:01:23

Improvement over 609 threadCount: 37%

Read

Environment

Cassandra 3.7 on VMWare 4CPU 12G memory (m01 and s01)

[cqlsh 5.0.1 | Cassandra 3.7.0 | CQL spec 3.4.2 | Native protocol v4]

Linux

Ubuntu 14.04.3 LTS 64 bit

VMWare

I did not fully understand the stress test was doing. It was stepping up the number of threads, hence the load was stepping up, causing the latency stepping up too.

Running with 4 threadCount
Running WRITE with 4 threads until stderr of mean < 0.02

Running with 8 threadCount
Running WRITE with 8 threads until stderr of mean < 0.02

Running with 16 threadCount
Running WRITE with 16 threads until stderr of mean < 0.02

Running with 24 threadCount
Running WRITE with 24 threads until stderr of mean < 0.02

Running with 36 threadCount
Running WRITE with 36 threads until stderr of mean < 0.02

Running with 54 threadCount (08:03)
Running WRITE with 54 threads until stderr of mean < 0.02

Running with 81 threadCount (08:04)
Running WRITE with 81 threads until stderr of mean < 0.02

Running with 121 threadCount (08:04)
Running WRITE with 121 threads until stderr of mean < 0.02

Running with 181 threadCount (08:05)
Running WRITE with 181 threads until stderr of mean < 0.02

Running with 271 threadCount (08:06)
Running WRITE with 271 threads until stderr of mean < 0.02

Running with 406 threadCount (08:07)
Running WRITE with 406 threads until stderr of mean < 0.02

Running with 609 threadCount (08:07)
Running WRITE with 609 threads until stderr of mean < 0.02

Running with 913 threadCount (08:07)
Running WRITE with 913 threads until stderr of mean < 0.02

Because I repeated the test 10 times back to back, there were 10 peaks.


error altering database collation Due to a database-restore, collation has been changed. If I try to

alter database <xyz> collate <xyz>

I get an error, that no exclusive lock to database to fulfil this operation can be gotten.
Since sql-server is part of a virtual-machine, I'm quite sure, that no*one* has any database-access at this time but me. I thing that one ore few processes prevent me altering the collation having access to db, but I can't determine which it could be.
I've deactivated all possible services having something in common with the sql-server, but error stays the same. ( Running Processes: MSSMS and Report Server )

I know I have successfully changed collation on another db in the past - easyway - via contextmenu/properties/collation, but this also has no success.

Reading several articles of changing collation of each object by one via cursor operation is not what i want to do.

Any hints would be appreciated!

Assuming that you have full control over the database and can kick everyone out/close all existing process that are connecting to that database, you can use the following statement to force the database into single user mode with you as the only connection, then make your change, then switch it back to multi user.

The T-SQL to do this is:

--Step 1
USE [foo];
GO;

ALTER DATABASE [foo] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
GO;

--Step 2
ALTER DATABASE [foo] COLLATE <<Colation Name>>;
GO;

--Step 3
ALTER DATABASE [foo] SET MULTI_USER;
GO;

If you want to be nice and identify the processes that are connected to that database first, you can get that information from sys.dm_exec_sessions:

USE [foo];

SELECT
    session_id
    ,login_name
    ,host_name
    ,program_name
FROM
    sys.dm_exec_sessions
WHERE
    database_id = DB_ID()
    AND session_id != @@SPID;


Override database option in a user-specific .my.cnf config file Using MariaDB 10.2.15 (Linux Debian Stretch)

I have created a ~/.my.cnf config file in my home directory in order to set a default database at connection time for all local client tools:

[client]
user=myuser
password=mySecretPassword
database=mydb

But I want to unset (skip) option database for a specific client program (mysqldump). This is to prevent such warning when running mysqldump:

Warning: /usr/bin/mysqldump: ignoring option '--databases' due to
invalid value 'mydb'

I was hoping using skip-OPTIONNAME in a [mysqldump] section would have worked, but unfortunately it doesn't.

[client]
user=myuser
password=mySecretPassword
database=mydb

[mysqldump]
skip-database

In fact, I noticed that database is multi-value. So overriding it is not really possible. For example, the following .my.cnf config file:

[client]
user=myuser
password=mySecretPassword
database=mydb

[mysqldump]
database=mydb2

produces 2 warnings when running mysqldump:

Warning: /usr/bin/mysqldump: ignoring option '--databases' due to
invalid value 'mydb'
Warning: /usr/bin/mysqldump: ignoring option '--databases' due to
invalid value 'mydb2'

Is there a way to "unset" option database in an overridden section?

In fact I am realizing that setting database option in a [client] section is may be nonsense as many client tools won't be compatible.

mysqldump and mysql_upgrade for example are complaining about this option. So I may specify it in a more specific [mysql] section instead:

[client]
user=myuser
password=mySecretPassword

[mysql]
database=mydb

Even if it meets my needs, It would still be great to find a way to override database option though...


Selective Xml Indexing I have recently been researching Selective Xml Indexing and have been trying to figure out how to index a .query method

If I have the below query

WITH XMLNAMESPACES (Default 'http://www.w3.org/2001/XMLSchema' )
SELECT

    ,a.value('@Number' , 'INT') As Number
    ,c.query('.') as FormattedXml

    From

    dbo.XmlTable As X

    Cross Apply x.XmlColumn.nodes('Root') aa(a)
    Cross Apply a.nodes('Block') bb(b)

I can write a selective Index as below if I am following it right:

CREATE SELECTIVE XML INDEX xsi_XmlTable
ON dbo.XmlTable( XmlColumn )
WITH XMLNAMESPACES ( Default 'http://www.w3.org/2001/XMLSchema' )
FOR
(
      root = '/Root' AS XQUERY 'node()'
    , rootNumber = '/Root/@Number' AS SQL INT SINGLETON

)
GO

Can I include the c.query in the index?  Thanks

A selective index will not be used when using query to retrieve the data.

From Selective XML Indexes (SXI) - Supported XML Features

  Selective XML indexes support the XQuery supported by SQL Server
  inside the exist(), value() and nodes() methods.

  For the query() and modify() methods, selective XML indexes may be
  used for node filtering only.

  For the query() method, selective XML indexes are not used to retrieve
  results.

However testing shows that the index is used when you rewrite the query using nodes. These queries returns the same but the second is faster because it uses the the selective XML index.

select data.query('/Log/CallStack/ExceptionThread/Line')
from LogData

select T.X.query('.')
from LogData
  cross apply data.nodes('/Log/CallStack/ExceptionThread/Line') as T(X)

The selective XML index used in the second query is defined as..

[Line] = '/Log/CallStack/ExceptionThread/Line' as XQUERY 'node()',

I used a table of my own just to be able to show what the difference in this case is.

(67992 row(s) affected)
Table 'LogData'. Scan count 1, logical reads 388, physical reads 0, read-ahead reads 0, lob logical reads 1840865, lob physical reads 313514, lob read-ahead reads 24.

 SQL Server Execution Times:
   CPU time = 129792 ms,  elapsed time = 180087 ms.

(1850106 row(s) affected)
Table 'xml_sxi_table_622625261_256000'. Scan count 1, logical reads 8416, physical reads 3, read-ahead reads 8403, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'LogData'. Scan count 1, logical reads 388, physical reads 0, read-ahead reads 0, lob logical reads 927882, lob physical reads 0, lob read-ahead reads 24.

 SQL Server Execution Times:
   CPU time = 65271 ms,  elapsed time = 67636 ms.


Data Security in MS SQL Server How do I store the sensitive information on SQL server?. We have policy guidelines, DBA and Development engineers are not allowed to view the data as plain text.

I am aware of the encryption technologies and data masking technologies but the problems is, except the person who is directly responsible for the data, no one else can access the data as per the policy.

When developers need to perform some calculation on sensitive information, how is it possible to do that, without decrypting? while still allowing SysAdmin users to view the encryption key details? I am sure, I am not the first one to implement this system. How do I design this solution? Thank you!

Edit 1:

Is it possible to create a reference column based on the original column and give it to developer for calculations? In that case, how to avoid direct logics or how to avoid people reverse engineer the logic and how is it possible to keep the encryption / hashing logic away from DBA.

I am open to any other suggestion or workarounds. Thank you!

One alternative is to encrypt and decrypt in the app, and SQL server will of course have no knowledge that this data was born as anything else. Or, in SQL Server 2016, use Always Encrypted which means that the database API (ODBC, ADO.NET, JDBC...) does the job for you. Regardless of which solution, make sure that the DBA don't have access to the key. And, since the whole point is that the SQL Server never sees the clear-text data, you cannot manipulate using pure T-SQL. (In SQL Server 2019 will will have secure enclaves allowing for the database engine to decrypt the data to allow for things such as rotation of keys, LIKE, BETWEEN etc, but that isn't released yet.)


Implementing Pub/Sub initiated from the database I have a number of state-full Windows Services that currently poll the database looking for new or changed records in specific Tables and/or Views. For obvious reasons this limits scalability and is adding unnecessary load to my SQL Server 2008 databases. I'd like to implement something akin to a publish & subscribe pattern where the database; through some mechanism; publishes messages to indicate when certain state changes occur.

This brings me to my question... What mechanisms reside within SQL Server 2008 natively; if any; which could support this pattern?

I’m open to other ideas, assuming I can get away from blind polling. As an extra bit of context, the message will need to carry only basic information to describe the nature of the change and what; by id; has changed.

Note: I’ve considered using SSIS packages and triggers but don’t think it’s appropriate, especially during times when the change rate is higher.

Sounds like your looking for query notifications.

Exactly as you describe, this feature allows you to subscribe to notifications which are generated when the results of a query change. Typically more efficient than repeatedly polling the database for changes.

Under the hood, notifications rely on the mechanisms used to maintain indexed views. Because of this, notification queries are constrained by the same restrictions as apply to indexed views. It's a lengthy set of requirements that you should review in detail to determine if it would make this a non-starter for your scenario.


Create interval by string in PostgreSQL Is it possible to create an interval by string in PostgreSQL?

for example, I have a table:

CREATE TABLE x (
 id BIGSERIAL PRIMARY KEY,
 data JSONB
);
INSERT INTO x(data) VALUES('{"day_sess":"4_1"}');

-- the query:
SELECT DISTINCT (INTERVAL (LEFT(data->>'day_sess',1) || ' days')) FROM x ORDER BY 1;

it always give

ERROR:  syntax error at or near "LEFT"

Ah nevermind, found the answer

SELECT DISTINCT LEFT(data->>'day_sess',1)::INT * (INTERVAL '1 day')
FROM x ORDER BY 1;


Can I use the database and query it while full-text indexing? I need to index my database, but I need to query it while it is indexing, it may take a long time, can I do that?

Your comments note that you're specifically talking about full-text indexing.

You can indeed query the database while a full-text index is being created. Full text indexes are created in the background on SQL Server 2005, 2008, and 2008R2.

You can continue to query the database using the LIKE operator, although of course the queries won't be as fast as a full text index, and your database server's performance may be degraded while the index is being created.


Sql server 2012 replication scenario I am under a really specific scenario. I have a database on a customer's site and a website on Azure cloud that connects to that database.

User is on a 50mbit fiber but this is obviously slow. So I am thinking of implementing replication with SQL Server on the local Azure server.

Is there a way of implementing the reads from the replica but the writes on the main database (I am thinking about this so that I don't have to deal with merges and I don't really care about the write speed)? Moving everything to cloud is not an option as on site there are a lot of clients that are already connected.

Blockquote
  Is there a way of implementing the reads from the replica but the writes on the main database (I am thinking about this so that I don't have to deal with merges and I don't really care about the write speed)?
  Blockquote

Technically possible but probably not in your scenario. Bi directional transactional replication would be required, which isn't invisible to the app/website.


MySQL - Add index to low cardinality text column? I'm working on project where an enum column is being converted into a text column (I cannot change this). The cardinality of the column is low (7 unique values). Would I gain a performance increase by adding a 10-15 char index, or is the cardinality low enough where the index would result in diminishing returns?


I got this error when am trying view dba_tables Ora 03113 end of file on communication channel I got this error when am trying to view DBA_tables:

  ORA 03113 end of file on communication channel
  Process I'd: 9244. Serial number 1431

Error  ORA 03113 end of file on communication  means: The connection between Client and Server process was broken.

Possible causes and general troubleshooting:

Network problems
Oracle internal errors / aborts on the server
Client incorrectly handling multiple connections
check SQL*Net setup
look in the alert.log file for any errors


Is it possible to raiserror within a For Delete Trigger that rolls back the deletes There is a claim at the end of this accepted answer that if an error occurs within a "For Delete" trigger, the delete action would be rolled back because it is part of an implicit transaction.

However here is an example showing that the deleted rows remain deleted even though an error is raised:

create table T1 ( i1 int  );
go

create trigger T1_ForDelete on T1
for delete
as
raiserror('Raised 16', 16, 1);
raiserror('Raised 18', 18, 1);
raiserror('Raised #2 16', 16, 255);
raiserror('Raised #2 18', 18, 255);
go

insert into T1 (i1) values (1);

set xact_abort on; -- makes no difference

delete from T1;

Output (order changes):

Msg 50000, Level 16, State 1, Procedure T1_ForDelete, Line 4
Raised 16
Msg 50000, Level 16, State 255, Procedure T1_ForDelete, Line 6
Raised #2 16

(1 row(s) affected)
Msg 50000, Level 18, State 1, Procedure T1_ForDelete, Line 5
Raised 18
Msg 50000, Level 18, State 255, Procedure T1_ForDelete, Line 7
Raised #2 18

Then

select * from T1; -- Returns no records

Is this the expected behavior or is there a way to prevent deletion on an error (e.g. different severity/state)?

I cannot use an "Instead of Delete" because "on delete cascade" is employed.  Create Trigger (MSDN), search "For INSTEAD OF"

In order for that to work as you've coded it above, you need a rollback after your RAISERROR or, as the comments correctly state, wrap in a TRY/CATCH.

By itself, it's not going to be treated the same as an actual statement or batch terminating error. For example, this will work the way you expect:

create table T1 ( i1 int  );
create table t2 (i2 int primary key);
go

create trigger T1_ForDelete on T1
for delete
as
    insert into t2 (i2)
    SELECT i1
    FROM DELETED
go

insert into T1 (i1) values (1);
insert into t2 (i2) values (1);

delete from T1;
go

SELECT * from t1

In the above code the trigger will error, and cause the delete (outer) transaction to rollback. By itself, RAISERROR is only really transferring control and/or sending messages up the stack (so to speak)

BOL describes this behavior:

  When RAISERROR is run with a severity of 11 or higher in a TRY block,
  it transfers control to the associated CATCH block. The error is
  returned to the caller if RAISERROR is run:

  Outside the scope of any TRY block.

  With a severity of 10 or lower in a TRY block.

  With a severity of 20 or higher that terminates the database
  connection.

So RAISERROR outside the scope of a TRY block simply returns the error to the caller and is not treated as a statement-terminating error regardless of the severity you define.


Registered Servers without the ability to connect to Multiple Servers at once I'd like to provide a "directory" of all the different SQL Servers we have our different DEV, TEST, PROD environments by setting it up in Central Management Servers.

Another DBA said to be cautious of giving this to the group because there is an ability to inadvertently run a query on multiple servers IF they did a "New Query" on a group, instead of an individual server (and this would make sense why they call the feature Central Management Server).

Is there any way to provide this "directory" without having to worry about executing commands on multiple machines?

There is no way to avoid that once you share CMS.
Just be aware that it only stores the list of instances of SQL Servers available, but not necessarily everybody has access to connect in every single instance.

Because the connections that are maintained by a central management server execute in the context of the user, by using Windows Authentication, the effective permissions on the registered servers might vary. For example, the user might be a member of the sysadmin fixed server role on the instance of SQL Server A, but have limited permissions on the instance of SQL Server B.


What standard should I follow when naming tables and views? What standard should I follow when naming tables and views? For instance, is it a good idea to put something like tbl_ at the beginning of table names? Should I designate code/lookup tables in some way like ct_, lut_, or codes_? Are there any other do's/don'ts?

I'm using MS SQL Server and have many databases with many tables so it would be nice to have something we can use as a standard with some supporting rational.

OK, first NEVER put tbl in front of a table's name.  It's a table, we already now that.  That's called Hungarian Notation, and people stopped doing that 5+ years ago.

Just call the object based on what it is.  If a table holds employee data call it "Employee".  If it holds information about computers call it "Computer".  If it maps computers to employees call it "EmployeeComputer" or "ComputerEmployee" (personally I like "EmployeeComputer" better).

There's no real right naming convention to use (other than to not use Hungarian Notation).  As long as the object names make sense that it what is important.


Created Check Constraint WITH NOCHECK - still trusted I needed to alter the conditions of existing constraint - so I dropped it and recreated with the new conditions.

This failed, because there were 800k+ rows, that didn't meet the condition. That strikes me as odd, because

SELECT * FROM sys.[check_constraints] cc

showed is_not_trusted as 0

Anyway, I recreated the constraint again, this time with hint WITH NOCHECK
This time it passed and I expected is_not_trusted to be 1, but it is still trusted.

Can someone explain it to me?

My code:

ALTER TABLE dbo.MyTable
DROP CONSTRAINT CK_MyTable;
GO

ALTER TABLE dbo.MyTable WITH NOCHECK
ADD CONSTRAINT CK_MyTable
CHECK
(
    -- 7 conditions, mainly checking null and not null

)

This might not be an accepted answer and I am ok with that but I cannot put the whole thing in a comment.

I tested your scenario with SQL Server 2016, SP2 and SQL Server 2017 CU9. In both cases I get expected result for is_not_trusted column value. Meaning 1 while using WITH NOCHECK and 0 while using `WITH CHECK'

Here is my test code. Some of the code I used is from Search Engine Q&A #9: How to update constraints? written by Paul Randal.

DROP DATABASE IF EXISTS checkConstraints;
CREATE DATABASE checkConstraints;
GO

USE [checkConstraints]
GO

DROP TABLE IF EXISTS dbo.Sales;
CREATE TABLE dbo.Sales (salesID INT IDENTITY, SalesDate DATETIME);
GO

CREATE CLUSTERED INDEX Sales_CL ON Sales (SalesID);
CREATE NONCLUSTERED INDEX Sales_NCL ON Sales (SalesDate);
GO

SET NOCOUNT ON;
GO

DECLARE @a INT;
SELECT @a = 1;
WHILE (@a < 1000)
BEGIN

INSERT INTO Sales VALUES (GETDATE ());
SELECT @a = @a + 1;

END;
GO

ALTER TABLE Sales DROP CONSTRAINT IF EXISTS  [CK_Sales_nocheck];
GO
ALTER TABLE Sales WITH NOCHECK ADD CONSTRAINT [CK_Sales_nocheck] CHECK (YEAR (SalesDate) >= 2005 AND YEAR (SalesDate) < 2008);
GO

ALTER TABLE Sales DROP CONSTRAINT IF EXISTS [CK_Sales_check];
GO
ALTER TABLE Sales WITH CHECK ADD CONSTRAINT [CK_Sales_check] CHECK (YEAR (SalesDate) >= 2005 AND YEAR (SalesDate) < 2019);
GO

SELECT name, is_not_trusted FROM sys.check_constraints;
GO

Result I get is following:


How is INDEX on Composite Primary Key in mysql? When making a composite primary key for two or more columns, e.g. PRIMARY KEY(col1, col2, col3); will the system INDEX each column individually?

The reason that I am asking this question is that when we use UNIQUE INDEX (col1, col2, col3), it acts as INDEX for the first column only, and we need to create additional INDEXs for other columns. I want to know if that's the case for Composite Primary Key too.

The composite primary index will always ensure that the combination of col1, col2, col3 values are unique. If the purpose of the index is to ensure that the values are unique then you will have achieved that
A composite index also provides for index matches on any combination of the columns col1, col2, and col3
You would create separate indexes on col2, and col3, if you join or filter using any of the columns without the others.
I always prefer a numeric primary key (with no business association), and unique indexes over a composite primary key where necessary.


Dimension for Product Properties based on Product Type - Sparse Dimension? I am creating dimension of product properties for sales facts.

Property of product depend of product type. For example:
- Type = smartphone. Properties = model, OS, size
- Type = book. Properties = author, title

How dimension should be for this case?

Should I create dimension which contain ALL properties?
In this case dimension content will be sparse, there will be many null values.

|----------------------------------------------------|
| DimKey | Type | Model | OS | Size | AUTHOR | TITLE |

OR, should I create dimension for each?
In this case sales fact will have many FKs.

|-------------------------------------------------------------|
| FactKey | Quantity | Total | Book_FK | Smartphone_FK | .... |

Is there any other way to do this?

I would not create a separate foreign key for each product type. This is known as a centipede fact table.

http://www.kimballgroup.com/data-warehouse-business-intelligence-resources/kimball-techniques/dimensional-modeling-techniques/centipede-fact-table/

I would consider creating separate fact tables for products which can logically be grouped. ie FactTechnologySales, FactBookSales. And then you would have DimTechnologyProducts and DimBooks dimensions. This would minimise NULL fields in your dimension table. (note that I would actually populate these NULL fields with N/A rather than leave them NULL).

If you want to only have one fact table, then create a product dimension with attributes like Product Name, Product Description, Product Category, Product Type. Where Product Description is Model + OS + Size for the smartphones and Author + Title for books. Use the Product Category and Type fields to create a hierarchy for users to drill down. For any other attributes that don't fit into the description, then put them in a Junk dimension.


Explicitly granting permissions to update the sequence for a serial column necessary? Recently I did create a table as a superuser including a serial id column, e.g.,

create table my_table
(
    id serial primary key,
    data integer
);

As I wanted my non-superuser user to have write access to that table, I granted it permissions:

grant select, update, insert, delete on table my_table to writer;

At a random point in time after doing so, the insertions made by that user started to fail because the user lacked permission to modify the sequence my_table_id_seq associated to the serial column. Unfortunately I can't reproduce that on my current database.

I worked around this by giving the user the required permission, like this:

grant all on table my_table_id_seq to writer;

Can someone help me understand

why, at some point, the previously sufficient permissions might start to fail?
what is the proper way to grant write permission for a table with a serial column?

You most probably need:

GRANT USAGE ON SEQUENCE my_table_id_seq TO writer;

Per documentation:

  USAGE
  ...
  For sequences, this privilege allows the use of the currval and nextval functions.

nextval() is the reason you need the USAGE privilege on the sequence for a table with serial column.
Details in this related answer on SO.

Since a sequence is a special kind of table (and for historical reasons) GRANT ... ON TABLE works on sequences, too. But you do not normally need that at all.


Why would a SELECT query cause writes? I've noticed that on a server running SQL Server 2016 SP1 CU6 sometimes an Extended Events session shows a SELECT query causing writes.
For example:

The execution plan shows no obvious cause for the writes, such as a hash table, spool, or sort that could spill to TempDB:

Variable assignment to a MAX type or an automatic statistics update could also cause this, but neither was the cause of the writes in this case.

What else could the writes be from?

In some cases Query Store can cause writes to occur as an effect of a select statement, and in the same session.

This can be reproduced as follows:

USE master;
GO
CREATE DATABASE [Foo];
ALTER DATABASE [Foo] SET QUERY_STORE (OPERATION_MODE = READ_WRITE,
  CLEANUP_POLICY = (STALE_QUERY_THRESHOLD_DAYS = 30),
  DATA_FLUSH_INTERVAL_SECONDS = 900,
  INTERVAL_LENGTH_MINUTES = 60,
  MAX_STORAGE_SIZE_MB = 100,
  QUERY_CAPTURE_MODE = ALL,
  SIZE_BASED_CLEANUP_MODE = AUTO);
USE Foo;
CREATE TABLE Test (a int, b nvarchar(max));
INSERT INTO Test SELECT 1, 'string';

Create an Extended Events session for monitoring:

CREATE EVENT SESSION [Foo] ON SERVER
ADD EVENT sqlserver.rpc_completed(SET collect_data_stream=(1)
    ACTION(sqlserver.client_app_name,sqlserver.client_hostname,sqlserver.client_pid,sqlserver.database_name,sqlserver.is_system,sqlserver.server_principal_name,sqlserver.session_id,sqlserver.session_server_principal_name,sqlserver.sql_text)
    WHERE ([writes]>(0))),
ADD EVENT sqlserver.sql_batch_completed(SET collect_batch_text=(1)
    ACTION(sqlserver.client_app_name,sqlserver.client_hostname,sqlserver.client_pid,sqlserver.database_name,sqlserver.is_system,sqlserver.server_principal_name,sqlserver.session_id,sqlserver.session_server_principal_name,sqlserver.sql_text)
    WHERE ([writes]>(0)))
ADD TARGET package0.event_file(SET filename=N'C:\temp\FooActivity2016.xel',max_file_size=(11),max_rollover_files=(999999))
WITH (MAX_MEMORY=32768 KB,EVENT_RETENTION_MODE=ALLOW_MULTIPLE_EVENT_LOSS,MAX_DISPATCH_LATENCY=30 SECONDS,MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=NONE,TRACK_CAUSALITY=ON,STARTUP_STATE=OFF);

Next run the following:

WHILE @@TRANCOUNT > 0 COMMIT
SET IMPLICIT_TRANSACTIONS ON;
SET NOCOUNT ON;
GO
DECLARE @b nvarchar(max);
SELECT @b = b FROM dbo.Test WHERE a = 1;
WAITFOR DELAY '00:00:01.000';
GO 86400

An implicit transaction may or may not be necessary to reproduce this.

By default, at the top of the next hour Query Store's statistics collection job will write out data. This appears to (sometimes?) occur as part of the first user query executed during the hour. The Extended Events session will show something similar to the following:

The transaction log shows the writes that have occurred:

USE Foo;
SELECT [Transaction ID], [Begin Time], SPID, Operation,
  [Description], [Page ID], [Slot ID], [Parent Transaction ID]
FROM sys.fn_dblog(null,null)
/* Adjust based on contents of your transaction log */
WHERE [Transaction ID] IN ('0000:0000042c', '0000:0000042d', '0000:0000042e')
OR [Parent Transaction ID] IN ('0000:0000042c', '0000:0000042d', '0000:0000042e')
ORDER BY [Current LSN];

Inspecting the page with DBCC PAGE shows that the writes are to sys.plan_persist_runtime_stats_interval.

USE Foo;
DBCC TRACEON(3604);
DBCC PAGE(5,1,344,1); SELECT
OBJECT_NAME(229575856);

Note that the log entries show three nested transactions but only two commit records. In a similar situation in production, this led to an arguably faulty client library that used implicit transactions unexpectedly starting a write transaction, preventing the transaction log from clearing. The library was written to only issue a commit after running an update, insert, or delete statement, so it never issued a commit command and left a write transaction open.


SQL Server Migration using a DB Mirror (Rolling upgrade) I'm planning to do SQL Server migration of SQL Server 2012 to a new hardware and a new version of SQL Server (2016). I'm trying to find the best solution with minimal downtime. I found some suggestions on the internet that in theory sounds great. I'm talking about DB Mirroring and Rolling Upgrade. But I also have read that MS does not recommend DB Mirroring between different SQL Server versions. So now I'm confused should I or shouldn't use DB Mirroring for this. Any suggestions? Have anybody tried that method before? I'm also thinking about Log Shipping.

We used successfully mirroring to migrate databases from SQL 2005 to SQL 2016 (on new hardware). All worked flawlessly. On hardware that was 13-14 years old and using Windows Server 2003.

Can't say anything about MS support, as since we had such an old version, we didn't have any type of support from MS (not even Extended).

If you're curious, here's a short list of steps we used:

Prerequisites

logins
linked servers
jobs
SSIS packages
export sys.messages

Mirroring preparation

setup endpoints and permissions
disable log backups
restore dbs on mirror
mirror setup on both the principal and partner
re-enable backups

Actual migration - the only moment of downtime

manual fail-over, make sure you do it step by step, as it will take a while to get in sync
break mirror and set dbs online
set the databases on the old principal as OFFLINE, so the apps won't connect to it
UPDATE STATS (fully if possible) - can't stress how important this step is
then enable the application logins and you can get on with it

Everything gets a tad more complicated with replications, but you get the point. It works very well. Log shipping will work too, no worries. But since it involves a bit more manual steps, we preferred to reduce the complexity if possible.


Serializability schedules: can someone help me understand it? I am struggling with the Databases courses I have, so I am trying to find answer to my question.
This has been asked:

Add the appropriate locks to enforce serializability on the following
schedule:
T1: W(Y)
T2: R(Y)

And:

Add the appropriate locks to enforce serializability on the following
schedule:
T1: R(Y) R(Z) W(Y)
T2: W(Y) R(Z)

For me, right now, it's hocus pocus. Could someone explain to me what exactly I need to do here... as in: what's the structure I need to follow? I need some structure to understand it.

R(Y) is a read action on database object Y
W(Y) is a write action on Y
S(Y) sets a shared (read) lock on Y
X(Y) sets a exclusive (write) lock on Y
U(Y) releases any lock on Y

PS: it's not about the answer to the questions, although I would appreciate it... yet it's about me understand WHY that's the answer.
Hope someone can help me. I'd appreciate it a lot!

I just invented a lame example that might help.

Have you ever driven by a gas station while they are changing the prices?  (And the prices are big numerals that need to be taken down and new numbers put up.)

There can't be two guys changing the same price at the same time -- exclusive lock.

Everyone riding by can read the numbers -- shared lock (sort of).  But, if you want to get the price you saw, and don't want it to change before you pump your gas, it gets more complex.  You want the shared lock to prevent changing the price until you are finished with your "transaction" (filling your tank at the price you saw).

In my lame example, you can't really take out a shared lock, except with a "dirty read".  "Dirty read" is a flavor of "transaction isolation".
 The price can change while after you see the price and before you pump your gas.  With a stronger isolation, you will get the price you saw.


SHRINKFILE Failure - Why does increasing file size resolve it? I am running some SHRINKFILE operations to clean up a bunch of tiny, unnecessary files in a filegroup. For one of the shrinks, the command below results in an error:

DBCC SHRINKFILE (N'myfile' , EMPTYFILE)'

  File ID x of database ID x cannot be shrunk as it is either being
  shrunk by another process or is empty

It is not empty nor being shrunk. It is being run on a database not currently in use by anyone except myself. Auto shrink is not enabled and never was. However, there were manual shrinks performed on this database on a regular basis prior to me getting my hands on it, if that matters at all.

On SQLServerCentral, a thread from a decade ago suggests adding a few MB to the file because that "resets an internal counter or switch that tells it it's not in the middle of a shrink now."

This worked - awesome. But can anyone explain with greater detail how/why this works in regard to SQL Server internals?

I poked around in the file header page, as suggested by Martin Smith in the comments.  I think this is part of the answer, but it's mostly speculation based on observing changes to the file header page flag values between performing shrinks and other operations.

First I created a database to test with, including a secondary filegroup:

CREATE DATABASE [Shrinkfile_Test]
ON PRIMARY
(
    NAME = N'Shrinkfile_Test',
    FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL13.SQL2016\MSSQL\DATA\Shrinkfile_Test.mdf',
    SIZE = 8192KB,
    FILEGROWTH = 65536KB
),
FILEGROUP [SECONDARY]
(
    NAME = N'ShrinkFile_Test_Secondary',
    FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL13.SQL2016\MSSQL\DATA\ShrinkFile_Test_Secondary.ndf',
    SIZE = 1024KB,
    FILEGROWTH = 65536KB
)
LOG ON
(
    NAME = N'Shrinkfile_Test_log',
    FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL13.SQL2016\MSSQL\DATA\Shrinkfile_Test_log.ldf',
    SIZE = 73728KB,
    FILEGROWTH = 65536KB
)
GO

USE Shrinkfile_Test;
GO

The I looked at "page 0" for the secondary file, which is file_id 3:

DBCC TRACEON (3604);
GO
DBCC PAGE (N'Shrinkfile_Test', 3, 0, 3);

There's a field called m_flagBits that has a value of 0x208.

If I empty this file:

DBCC SHRINKFILE (N'ShrinkFile_Test_Secondary' , EMPTYFILE);

That m_flagbits field stays the same (0x208).  Not that interesting, but now I'm in the situation you reported: if I try to empty the file again, I get this error:

  File ID 3 of database ID 19 cannot be shrunk as it is either being shrunk by another process or is empty.

I'll try growing the file (the solution that worked for you):

ALTER DATABASE ShrinkFile_Test
MODIFY FILE
(
    NAME = ShrinkFile_Test_Secondary,
    SIZE = 1025KB
);
GO

Now m_flagbits is 0x8!

At this point, emptying the file again is successful returns the value to 0x208 as you might expect.

The thing that I find interesting is that if I do this after growing the file back out (AKA flagbits value is 0x8):

USE [master]
GO
ALTER DATABASE [Shrinkfile_Test] MODIFY FILEGROUP [SECONDARY] READONLY
GO

The file is marked as is_read_only in the sys.databases table, and m_flagbits is set back to 0x208.  So it appears there is some similar file-level flag set when shrinking a file and when setting it to read only.

My best guess is that this value is used along with some other (internal) flag to indicate that a file is eligible to be shrunk.  Growing the file appears to un-set that flag (at least the one visible in m_flagbits).


How can I clear buffer for a SQL Server DB Hosted On Azure? I am trying to fine tune my queries and Stored Procedures. The Issue that I am facing is that there is no way to clear the Database buffers in Azure. Which means that I cannot use DBCC DROPCLEANBUFFERS on Azure.

Is there some workaround by which I can clean the buffers so that I can test the Stored procedures' actual time? I was thinking of reduing the Page Life expectancy (though I am not sure if I can actually do it).

I tried changing the tier's of the DB as it was mentioned in one article that it might clear the buffer, but it didn't have any effect.

I need this as the local machines (I5 with 8 GB ram) are too fast and the query which takes a second to execute on the local machine takes around 30 - 40 seconds on an Azure S2(20 DTU) SQL DB.

What you're basically trying to do is measure storage speeds in the cloud. Unfortunately, that's not necessarily repeatable or reliable - it can suffer from noisy neighbors, other people doing development on the same database, varies with your storage type, etc.

Rather than clearing the buffers or measuring query runtime, try starting with measuring logical reads. It's the number of 8KB pages a query reads in order to accomplish its goals.

In either SSMS or Operations Studio, run:

SET STATISTICS IO ON;

And then run your query, and look in the output messages for the logical reads on each table - that's the number of 8KB pages it read. (Ignore physical reads - those can change from time to time based on what's in cache.) Generally speaking, reading less pages means a faster query - regardless of whether those pages are read from memory, or from disk.

If your query references a lot of tables, then instead of trying to add up those numbers with a calculator, copy/paste the messages into StatisticsParser.com. Gives you a nice summary of the logical reads overall, even across multiple statements in a batch. (Disclaimer: that open source site was written by one of my employees, but it's not affiliated with my company.)


SQL switching values on a column In interviews for software developer roles SQL questions can sometimes come up. There is a classic question around switching values in a column, typically swapping the gender of people in a table for example:

id   name   sex   salary
------------------------
0    Tom    f     45000
1    Jane   m     22000

the typical question asks you to do this in one statement (and the answer is here: Switching values in a column with one update statement)

However my question is NOT that.

My question is what is the naive approach, that is, how I can temporarily store the values. In fact I'd like to do this two ways: 1. In a temporary column (that does not persist after the statement) and 2. In a temporary variable.

Odd question with terms like temporary storage. General SQL is denormalized so fetching when need rather than a transform.

Question 1: results and be an expression like:

SELECT id, sex, IF(sex='m', 'f', 'm') as antisex FROM table

Question 2: A variable can contain only a single value. So from ref manual on SELECT INTO:

SELECT id, sex, IF(sex='m', 'f', 'm') INTO @id, @sex, @antisex FROM table LIMIT 1


How to represent the relationship between artists and albums in an entity-relationship diagram I'm learning and practicing with the E-R model trying to design a music industry database.

With this section of the diagram I have a doubt:

I suppose an album can be had by an artist and a band, not necessary by only one of these. How can I represent this relationship?

Replace ALBUM with SONG, keep ALBUM but add a "many to many" relationship between the two.

There are still a lot of complexities you are missing (eg "Artist formerly known as Prince" vs "Prince", cover songs, etc)


Calculated Column Or Trigger We currently have an oninsert() trigger set-up that everytime a row is insert into a table an update query fires off to update 3 fields.  The fields are a simple calculation such as

field1update = (amt/12)*14
field2update = (amt/12)*16
field3update = (amt/12)*18

The trigger is adequate, but it will sometimes lock the row so it can not be immediately accessed for a moment.  My question being if these fields were converted to a (please excuse my ignorance) calculated column or a computed column would we see performance improvement?

Usually this is a spreadsheet import and is roughly 20,000 - 25,000 rows being inserted at a time.

Based on the information you provided I did a very basic test and I suggest you set up something similar before you make a final decision.
My result shows computed column will perform better over trigger but I want to stress that it might vary with your table structure, insert rate, other activity in the table and you need to test.

Set up:

CREATE TABLE TestTrigger
(
    amt INT NOT NULL,
    field1update INT,
    field2update INT,
    field3update INT
 )
 GO

CREATE TRIGGER triTestTrigger on TestTrigger
INSTEAD OF INSERT
AS
BEGIN
  INSERT INTO TestTrigger
       SELECT amt, (amt/12)*14, (amt/12)*16,(amt/12)*18
       FROM inserted
END
GO

CREATE TABLE TestCompCol
(
    amt INT NOT NULL,
    field1update AS (amt/12)*14,
    field2update AS (amt/12)*16,
    field3update AS (amt/12)*18
 )
 GO

Inserting into both table.

 INSERT INTO TestTrigger
 (amt)
 VALUES(1)
 GO
 INSERT INTO TestCompCol
 (amt)
 VALUES(1)
 GO

First(trigger) insert has to do more work(70%) compare to second (computed column) insert(30%).
If I look at the subtree cost for first insert (.010023+.0132842)=0.0233072 and for 2nd insert it is .0100022.

For cpu time of trigger insert I get

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

For computed column insert I get:

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


BULK INSERT permission on a single database I work on a company that has straight IT security manners. I was hired as a Database Analyst in order to optimize some processes in our team. The problem is that I only have read and write permissions and I'm trying to work out in getting advanced permissions on the databases. Currently we have a lot of Databases in a single server and we can only work on a single Database, I was wondering if the BULK INSERT permission could be given to a single database or would that be permission to the whole server?

I've searched through the web about this but I got a little confused. This is what I found out. In order to use BULK INSERT I should have INSERT permission to the desired table or database and the ADMINISTER BULK OPERATIONS which is a server wide permission.
If I was given ADMINISTER BULK OPERATIONS permission on the whole database and the INSERT permission on a single database, wouldn't I be able to only BULK INSERT in that single database, since I don't have INSERT permission on other databases?

Is it possible to restrict the use of BULK INSERT to a single database on a server that has multiple databases?

Sorry about the text wall, and thanks!

Ok folks,

I've asked this on Brazilian msdn forum as well and I got the answer (Forum discussion: BULK INSERT por DATABASE). The user Roberson Ferreira _ helped me. I imagine you can't read Portuguese so I will describe the answer here.

Answer:
It doesn't matter if the ADMINISTER BULK OPERATIONS is server-wide, the user won't be able to BULK INSERT into a certain database if they don't have the INSERT permission as well. The INSERT permission is a database/table-level permission.

Additionally, the ADMINISTER BULK OPERATIONS is not going to grant the user any other permissions over the server or database, all the user will be able to do is BULK INSERT in the database where they have INSERT permission as well.


Same time even after deleting the row I have a table which has 1 million record.
When I run

Select count (*) from accounts;

It took around 2 min.
But when I deleted 300 thousands record and run the same query it is still taking same time.
I read in Google many things. Should I do enable row movement and shrink space or
 should I gather the statistics. Which is helpful or both are necessary.

You raise a good and important point. I think it is crucial to understand what happens when data is deleted. I've seen many applications, where this was not thought about until the storage was full or slow or the data protection people came round and complained.

If you DELETE rows in Oracle, the space is marked as free, but left as "holes" in the storage. If you INSERT again afterwards, these holes might be filled if the new data fits in.

Some but not by far not all queries are sped up by indexes. Those that are not faster need then to read all data, including holes (so called full table scan). Reading an 8K block from disk that is full of data takes the same amount of data than reading an completely empty block which contains only "holes". Your time measurement points to this scenario.

Regarding statistics: Oracle updates the optimizer statistics at the nightly maintenance window (usually 10pm/22:00). Personally, I'd certainly gather stats after deleting a third of a large table, especially if this is a regular job, for instance in a procedure.

Regarding row movement: I leave it always on for most tables, but I'd judge it as not so important. It hurts only code that stores rowids in tables or longer time in memory and we don't do that. It is necessary if you do a ALTER TABLE xxx MOVE.

Regarding shrink: I wouldn't be bothered to shrink or reorganize for 300k rows. For 10GB+, it may be worth the effort.


how I can create check sum to validate no changes at int string pairs on table Business case is.
1)Users print document on paper
2)users carry printed paper to Boss, Boss sing on paper
3)after that users going to administrator to digitally sign that paper.

My task is to help administrator to on easy way be sure that there is no changes between paper document and digital document.

Document is written in three sql tables.
On 1st table integrity is string+date
On 2nd table integrity is combination int+float
On 3rd table integrity is int+string.

There is others columns in these tables which can be updated freely after Boss sing document without needs to Boss sing it again.

My idea is to from these three table / combination make some kind of SHA or CheckSum.
Which is going to be same while there is no changes on document.
I do not want to my checksum be infinite or too long so administrator can easily check is
there violation on my custom integrity.

I start thinking that I can for int - float make sum( int * folat)
For stings for each letter I can use ASCII number and do sum(int * (ascii(S)+ascii(T)+ascii(I)+ascii(N)+ascii(G))).
For date - int same make int * (date to int)
After that I can totalize (sum) these three numbers and convert to HEX.
But perhaps my number is going to be out of range or too large for comparing by human eye.

How I can shrink extra large numbers to be easily readable and comparable by humans ?
Is there another way to I ensure that there was no changes on my protected columns in one period of time ?

I am using MSSQL2005/8 server.
My reporting engine is most of rime SqlReportingServices, but I prefer to do this loginc
on database level not reporting level.

I do not want to lock editing of document because sometimes Boss push users to go back and correct document before signing.
I also can't push boss to take mouse in hand.

You could just use the CHECKSUM function as it returns an INT.  Just put in the columns that you want to checksum against for those tables and it'll output the checksum value for those columns.  Here's an example from AdventureWorks2012 but this function has been around for quite a while.  In this case I've got columns from two tables as part of the single checksum function call.

select loginID, checksum(LoginID, JobTitle, ShiftID)
from HumanResources.employee
join HumanResources.EmployeeDepartmentHistory on employee.BusinessEntityID = EmployeeDepartmentHistory.BusinessEntityID
and EndDate is null