How locate error "Login failed for user 'user_test'" inside huge stored procedure? I am in the process of refactoring legacy system which relies on huge a stored procedure with large calling tree of dependent stored procedures/UDFs and a lot of interconnected linked servers.

Production stored procedure runs fine while test one fails with

  Login failed for user 'user_test'

I can get no further details from error_procedure() and error_line().

Complete output:

  (1 row affected)

  (1 row affected)

  ...skipped

  ErrorNumber=[18456]; ErrorSeverity=[14]; ErrorState=[1]; ErrorProcedure=[]; ErrorLine=[1]; ErrorMessage=[Login failed for user 'user_test'.]

  Msg 18456, Level 14, State 1, Line 1
  Login failed for user 'user_test'.

Apparently some of the linked servers are not properly setup on the test environment. So I would need to either set them up or mock/comment external calls. I cannot debug the stored procedure on a test server.

How to find exact line in the code which throws this error without adding traces after each line of SQL?

System runs in SQL Server 2016 on Windows Server 2012 R2.

SQL Profiler can capture the individual statements in a stored procedure. See details here. Using such capture I have identified statement preceding an error. Then I have searched through SQL code using Toad. Problem was pinpointed. It was something like:
exec sp_executesql 'select top 1 * from openquery(ext_server, ...

If there exists more elegant solution to this - please post an answer here.


How to create an insert trigger? This is 1/4 of an overall assignment and I need help with this part.
The assignment question:

Create a trigger on the Rental table that will check when a new rental is to be inserted, that the member (MemberId) that is renting a book:
a.    Does not have more than 2 books rented out: the books that are rented out are identified by having ReturnDate as NULL.
b.    That the member does not owe more than 50 dollars to the library (the BalanceOwing attribute of the Member table).

What I have done so far :
CREATE TRIGGER tr_tblRental_ForInsert
ON dbo.Rental
FOR INSERT
AS
BEGIN
    declare @MemberId int
    select  @MemberId int = MemberId from inserted

I would appreciate tips on how to go about this.

Don't assume that the trigger will always handle a single row inserted in the table. If you're inserting more than one row in Rental, the code you attempted to write will fail.

The logical tables inserted and deleted contain all the rows modified by the DML operation and they must be joined or correlated to the base table (or other tables) in order to take all the rows modified into consideration.

As far as the business logic required is concerned, this is a possible implementation:

CREATE TRIGGER tr_tblRental_ForInsert
ON dbo.Rental
FOR INSERT
AS
BEGIN
    -- Always add this to your triggers: some libraries don't like
    -- triggers altering the number of rows affected by the DML operation
    SET NOCOUNT ON;

    -- a. Does not have more than 2 books rented out:
    -- the books that are rented out are identified by having ReturnDate as NULL
    IF EXISTS (
        SELECT COUNT(*)
        FROM dbo.Rental
        WHERE MemberId IN (
                SELECT MemberId
                FROM inserted
            )
            AND ReturnDate IS NULL
        GROUP BY MemberId
        HAVING COUNT(*) > 2
    )
    BEGIN
        ROLLBACK;
        THROW 51000, 'Members are not allowed to rent more than 2 books at a time.', 1;
    END

    -- b. That the member does not owe more than 50 dollars
    -- to the library (the BalanceOwing attribute of the Member table).
    IF EXISTS (
        SELECT BalanceOwing
        FROM dbo.Member
        WHERE MemberId IN (
                SELECT MemberId
                FROM inserted
            )
            AND BalanceOwing > 50
    )
    BEGIN
        ROLLBACK;
        THROW 51000, 'Members are not allowed to rent more books if they owe more than $50 to the library.', 1;
    END

END

Please do not use this code in your project/homework if you don't understand what it does: your teacher will ask questions and you'd better have answers. If you need help understanding the code, place a comment here.


how to select records from two columns where one column consist word from second column? In postgres I want to select rows where column1 matches column2 for corresponding row and returns result having same values as well as records consisting word from column1.
select * from table_name where "column1" ILIKE "column2";

i am using above query but it returns exact matches, it should also return rows consisting words from column1 in column2.
column1 | column2
hello   | hello
please  | please guys
help    | help me
query   | website

my above query returns
column1
hello

i want it to return
column1
hello
please
help

Do you need it to be case sensitive? If not try using LIKE
Create Table #Test
(
ColA Varchar(100)
,ColB Varchar(100)
)

Insert Into #Test
Values ('TEST', 'THIS IS A TEST')
,('String', 'This Also has word string in it')
,('ABC','DEF')
,('123','456')

Select      *
From        #Test
Where       ColB Like '%' + ColA + '%'


Grant users privilege to use QUERYTRACEON 9481 Microsoft suggests using OPTION (QUERYTRACEON 9481) for queries that have "degraded" performance against SQL Server 2014 databases running under compatibility level 120.

In my situation, "degraded" means going from a few seconds to several minutes or more. Performance wise, 9481 works extremely well; however, regular users get a nice error instead of query results (running SSRS reports):

User 'xxxxx' does not have permission to run DBCC TRACEON.

The documentation indicates that users must be members of the sysadmin role to run DBCC TRACEON.

Are there any workarounds to allow non-sysadmins to run queries that use this hint?

I'd rather not drop the compatibility level back down to 110 because the majority of our queries are acceptable or working better.

You can create a plan guide as sysadmin for the query, which would then allow the user to run the pre-existing plan.  Otherwise, rewriting the query is about all you can do.

Using QUERYTRACEON in plan guides


Replicating a SQL Server database & Optimization We got a database of approximately 100-120 GB on a server with 128GB ram, while the db is sitting on an 256 GB SSD drive.

The bulk of the database space is spent on the tables below:

Page_contents (page_content_id, member_id, page_id, added_on, status)
Custom_fields (page_content_id, custom_field_key, custom_field_name)

and then statistics like

messages, visits, clicks table where we store the raw data (page_content_id, timestamp, page_campaign_id) on each table per status (visits on visits table, messages on messages etc)

Those tables take up pretty much 90% of the db space.

We had a blow up in production recently due to a failure on the raid controller and we are looking to see if the database can be replicated to a second equivalent server.

Questions:

What's the best way to do that on a SQL Server 2012 database?
Should we expect performance degradation?
Is the setup described above adequate for our size you think? We expect the database to keep growing.
Is there a way to optimize the schema above that you can think of. I understand you have little information.

EDIT: To answer some of the questions in the comments

The SQL Server is 2012 Enterprise edition. I am in control of what I can do and how I use the two boxes. I can decide to drop one box and double the power on the other one. It's really up to me.

I would like to leave questions 3 & 4 in case someone can think of optimizations of the existing schema. If there are questions to answer to clarify things I l happily do so.

My requirement is for the database to be accessible at all times. I don't mind losing some data (1 hour) in terms of the analytics, but can't sustain more than 5 minutes loss of data for the other two tables. If I can increase the performance as well as prevent downtime given the requirements above I would prefer that option for obvious reasons.

If I were you I'd be looking at AlwaysOn Availability Groups with a synchronous replica.  It'll add a couple of milliseconds of latency to the SQL commands when they are run, but it'll give you a second server that's available in the event of a server failure without any loss of data.  It requires no shared storage so you can keep using your local SSD drives.

It does require setting up Windows clustering but the SQL Server instances don't need to be clustered.


How can I create a unique and random alphanumeric string using MySQL's Default Value? (v 5.6) I am building a MySQL database and think using implicit DEFAULTS seems very promising.

I am successfully using DEFAULT CURRENT_TIMESTAMP and DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP for on insert timestamps and on update timestamps.

I would like to add a default for a public key. A 32 character unique and random alphanumeric string. Is this possible using the DEFAULT field specification? If so, how?

Or must I handle this by creating a random value and checking it's uniqueness, for the 1 in 1.5^50 chance it's a duplicate, on INSERT?

This article seemed promising, but didn't give me an definitive answers: https://www.percona.com/blog/2015/04/29/generated-virtual-columns-in-mysql-5-7-labs/

Thank you in advance

I'm a bit late, but I've been having difficulty finding a way to insert cryptographically secure alphanumeric text, so here's what I've come up with.

I created a function to generate the strings and a stored procedure to insert data into the table. It uses INSERT IGNORE and checks ROW_COUNT() to see if the insert succeeded. I know INSERT IGNORE is not ideal, but this is the best I have so far.

DROP FUNCTION IF EXISTS random_alphanumeric;
DELIMITER //
CREATE FUNCTION random_alphanumeric
(len INT)
RETURNS VARCHAR(512)
BEGIN
    DECLARE result VARCHAR(1200);
    DECLARE segment VARCHAR(700);
    DECLARE l INT;
    IF len<0 THEN
        SIGNAL SQLSTATE '45000' SET message_text = 'Attempt to generate random alphanumeric with negative length.';
    END IF;
    IF len>512 THEN
        SIGNAL SQLSTATE '45000' SET message_text = 'Cannot generate random alphanumeric longer than 512 characters.';
    END IF;
    SET result='';
    WHILE LENGTH(result)<len DO
        SET segment=REPLACE(
            REPLACE(
                REPLACE(
                    REPLACE(
                        TO_BASE64(RANDOM_BYTES((len-LENGTH(result))+3)),'/',''
                    ),'=',''
                ),'+',''
            ),'\n',''
        );
        #The last characters of a base 64 string aren't always well distributed
        #Trim a couple off
        SET l=LENGTH(segment)-3;
        IF l>0 THEN
            SET result=CONCAT(result,SUBSTRING(segment,1,l));
        END IF;
    END WHILE;
    RETURN SUBSTRING(result,-1*len);
END //
DELIMITER ;

CREATE TABLE `test_random` (
  `id` VARCHAR(32) NOT NULL,
  `int_col` INT,
  `varchar_col` VARCHAR(45),
  PRIMARY KEY (`id`));

DROP PROCEDURE IF EXISTS test_random_insert;
DELIMITER //
CREATE PROCEDURE test_random_insert
( IN int_value INT, IN varchar_value VARCHAR(45))
        BEGIN
            DECLARE id_value VARCHAR(32);
            DECLARE ok INT;
            SET ok=0;
            WHILE NOT ok DO
                SET id_value=random_alphanumeric(32);
                INSERT IGNORE INTO `test_random` (`id`,`int_col`,`varchar_col`) VALUES (id_value, int_value, varchar_value);
                SET ok=ROW_COUNT();
            END WHILE;
            SELECT id_value id;
        END//
DELIMITER ;

To insert a record CALL test_random_insert(100,'text value'); The returned recordset has an id column, since LAST_INSERT_ID() only applies to autoincrement values.


SQL Snapshot Redirect Company is implementing Database Mirroring Asynchronous with Snapshot. This provides some Disaster Recovery with Reporting area.  Server A Principal contains database which mirrors to Server B, Snapshot is taken on ServerB database.

To enable updated data, we will create database snapshots every 30 min on Mirror server databases. How do we redirect all SSRS/report stored procedure queries to latest snapshot? Our report queries are pretty short, general around 10 sec. Microsoft mentions programmatic solution below, not sure what that is?
So SalesDB1030, SalesDB1100, SalesDB1130, 24clock added after dbname

Report queries may be as

select * from SalesDbSnapshot.dbo.SalesTransaction st
inner join SalesDbSnapshot.dbo.Customer cs
    on st.CustomerId = cs.CustomerId
inner join SalesDbSnapshot.dbo.Product pr
    on st.ProductId = pr.ProductId

Create a Database Snapshot

"To use a database snapshot, clients need to know where to find it. Users can read from one database snapshot while another is being created or deleted. However, when you substitute a new snapshot for an existing one, you need to redirect clients to the new snapshot. Users can manually connect to a database snapshot by means of SQL Server Management Studio. However, to support a production environment, you should create a programmatic solution that transparently directs report-writing clients to the latest database snapshot of the database."

One approach is to create a database containing only synonyms referencing the objects in the snapshot you need for reporting. Clients can then connect to the synonym database and run queries just as if they were using the snapshot directly.

Whenever a new snapshot is created, run a script to update the synonyms with the new snapshot database name. Below is an example that recreates synonyms for all tables, views, and procedures in the snapshot. Add additional object types if needed.

USE SynonymDatabase;

DECLARE
      @SnapshotDatabaseName sysname = N'YourSnapshotDatabase'
    , @SQL nvarchar(MAX)
    , @DropSynonymsSQL nvarchar(MAX)
    , @CreateSynonymsSQL nvarchar(MAX);

--generate DROP SYNONYM script
SELECT @DropSynonymsSQL = (SELECT N'DROP SYNONYM ' + QUOTENAME(sch.name) + N'.' + QUOTENAME(syn.name) + N';
'
FROM sys.synonyms AS syn
JOIN sys.schemas AS sch ON sch.schema_id = syn.schema_id
FOR XML PATH(''), TYPE).value('(./text())[1]', 'nvarchar(MAX)');

--generate CREATE SYNONYM script
SET @SQL = N'USE ' + QUOTENAME(@SnapshotDatabaseName) + N';
SELECT @CreateSynonymsSQL = (SELECT N''CREATE SYNONYM '' + QUOTENAME(sch.name) + N''.'' + QUOTENAME(obj.name) +
    N'' FOR '' + QUOTENAME(@SnapshotDatabaseName) + N''.'' + QUOTENAME(sch.name) + N''.'' + QUOTENAME(obj.name) + N'';
''
FROM sys.objects AS obj
JOIN sys.schemas AS sch ON sch.schema_id = obj.schema_id
WHERE
    obj.type IN(''U'', ''V'', ''P'')
    AND obj.is_ms_shipped = 0
FOR XML PATH(''''), TYPE).value(''(./text())[1]'', ''nvarchar(MAX)'');';
EXECUTE sp_executesql
      @SQL
    , N'@SnapshotDatabaseName sysname, @CreateSynonymsSQL nvarchar(MAX) OUTPUT'
    , @SnapshotDatabaseName = @SnapshotDatabaseName
    , @CreateSynonymsSQL = @CreateSynonymsSQL OUTPUT;

BEGIN TRY
    BEGIN TRAN;
    EXEC sp_executesql @DropSynonymsSQL;
    EXEC sp_executesql @CreateSynonymsSQL;
    COMMIT;
END TRY
BEGIN CATCH
    IF @@TRANCOUNT > 0 ROLLBACK;
    THROW;
END CATCH;
GO

From Aaron:
The job that creates the snapshot could store the new database name in a table, and SSRS can use that data source to dynamically create its connection. This example is slightly different, but same general concept applies

https://www.mssqltips.com/sqlservertip/4302/implement-dynamic-data-sources-in-sql-server-reporting-services/


database design question - How to make the table 3NF and have no orphans I trying to find out what is the best way to build schema for port-nic associations:

I am storing all information about ports (e.g eth0, eth1, lo0, lo2 of a Linux pc) in a table called ports and NIC information in nics (for storing slot info, etc.)

I am trying to find out if I should store a network-device port information all in one table by a column type_of_port to separate types of ports.

Using type_of_port, the advantages are that: 1. I have only one table, e.i easier queries, 2. I don't have stale orphans if a NIC is removed. Disadvantage: is that I have nic info as NULL values for loopbacks

sqlfiddle: http://sqlfiddle.com/#!9/50977/1:

CREATE TABLE nics (
    nic_id INTEGER UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    slot_no INTEGER UNSIGNED NOT NULL
) DEFAULT CHARSET=utf8 ENGINE=InnoDB;

CREATE TABLE ports (
    port_id INTEGER UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    nic_id INTEGER UNSIGNED DEFAULT NULL COMMENT 'references to NIC',
    FOREIGN KEY (nic_id) REFERENCES nics(nic_id) ON DELETE CASCADE,
    serial_no VARCHAR(100) DEFAULT NULL COMMENT 'serialNumber of NIC;',
    port_name VARCHAR(100) UNIQUE NOT NULL COMMENT 'e.g eth0',
    type_of_port VARCHAR(3) NOT NULL COMMENT 'phy, vir'
) DEFAULT CHARSET=utf8 ENGINE=InnoDB;

insert into nics(nic_id, slot_no) VALUES(1, 0);
insert into nics(nic_id, slot_no) VALUES(2, 1);
insert into ports(nic_id, serial_no, port_name, type_of_port) VALUES(1, '0000', 'eth0-0', 'phy');
insert into ports(nic_id, serial_no, port_name, type_of_port) VALUES(2, '1111', 'eth1-0', 'phy');
insert into ports(nic_id, serial_no, port_name, type_of_port) VALUES(NULL, NULL, 'lo0', 'vir');
insert into ports(nic_id, serial_no, port_name, type_of_port) VALUES(NULL, NULL, 'lo1', 'vir');

OR

by its type e.i one table for loopbacks, another table for physical ports.
Disadvantage here is that, ports are scattered over different tables for each type of port. But the advantage is that its in 3NF.

Any suggestions?

The closer to reality you map, the more flexible the design usually is (and that usually means normalize it!).

I wouldn't worry about orphans if you have your F-key constraints set. In the case of deleting a NIC though, remember reality...did we really set the NIC on fire and crush it into oblivion? If so, then in your example, deleting a NIC will delete the port also...which is good. For making what is plugged in appear for normal "active" views, slot_no IS NOT NULL can be a part of the WHERE clause.

For both slot_no and the case of loopback (no NIC), having a NULLable column in InnoDB doesn't hurt anything, maps well to reality, and is made for just such a case. It means "I don't have one" in both columns, which is perfect.

If it were me, and I thought I might want metadata about the ports (lanes, speed, CPU association), I'd even go as far as having an additional table for slots which are referenced by NICs (some multi-jack cards appear as more than one) by that slot_no column you have.

Off the top of my head, here are some natural views (WHERE clause with "slot_no IS NOT NULL" would work in your 2 table design too):

If you start your queries include an inner join to slot, NICs which are not plugged in (but metadata retained) will not appear for active listings
For viewing metadata about all NICs (past and present), outer join instead
For an auto-configuring script, unplugged NICs or rearranged NICs can retain their metadata and port associations without them showing in active SELECTs (see #1)

In any case, be sure to keep the NIC info with the NICs.


Unique constraint for new values only I have a large table with a column that contains duplicates.

I want to add a unique constraint on this column for future entries only (not the old data).

Is this possible?

I don't see any way as for future inserts you will need to have the index. Maybe consider adding another column to your table ensuring the uniqueness. This could be filled with a trigger function on new inserts.


What is the exact relationship between a database transaction and locking? This is a humble question asked in the spirit of increasing my knowledge; kindly be gentle in your response.

As a long-time application developer, I know at some level what a transaction is (I use them all the time).  Leaving aside transaction isolation levels for the moment, at a high level a transaction allows a block of work to be completed entirely or not at all, and allows for a certain amount of isolation from other database-modifying activity.

I also know what (in various databases) a lock is, or at least how one behaves (if I lock a table in some way explicitly, then no other process or thread can update anything about that table).

What I am most distinctly not clear about is: in various databases, when I explicitly lock a row or a table, am I employing the exact same constructs that are used by the database's transaction facilities under the covers to make the transaction work properly?

That is, it occurs to me that in order for a transaction to be atomic and isolated, it must be doing some locking.  Is this transaction-initiated, tranasction-hidden locking the same sort of locking that various databases let me access through constructs such as SELECT FOR UPDATE or explicit LOCK commands?  Or are these two concepts completely different?

Again, I apologize for the naïveté of this question; I am happy to be pointed to more foundational sources.

when I explicitly lock a row or a table, am I employing the exact same constructs that are used by the database's transaction facilities under the covers to make the transaction work properly?

Yes. If that would not be true, then your own 'locking' would only be scoped to other similar 'locking' and not interact with the engine own locking. So you would lock a row in a table so that it cannot be locked by another application in the same manner, but your lock would be ignored by the engine itself. These semantics are seldom desired. Most of the time an application locking a row means 'lock it against any means of access/modify'. Side note that locking mechanisms that are strictly application specific do exists, because they are useful. For instance SQL Server has application locks.

  it occurs to me that in order for a transaction to be atomic and isolated, it must be doing some locking.

Locking is one means to achieve this. The major alternative is versioning. Nowadays most databases support both (which also means that if you 'lock' a row in the app but another transaction uses versioning to read the row, it will read it because your locking does not block versioned reads).

You are sort of circling around a concept known in the database implementation world as 'two phase locking protocol'. the linked Wikipedia article is a good starter. If you want to read more detailed explanation about this topic I recommend head to the library and ask for a loan on Transaction Processing: Concepts and Techniques. Pretty much every database out there is, at its core, an implementation of that book.


Join two SELECT results with the same post_id value I need to do a query to the MySQL database of my site built in WordPress / WooCommerce.

The 'wp_posts' table keeps products, articles and more. Every row in 'wp_posts' has a 'post_id' to connect 'wp_posts' with 'wp_postmeta' table. In the 'wp_postmeta' table, there are rows with additional data and values for every 'wp_post'. These rows have 'meta_key', 'meta_value' and 'post_id' values.

I need to SELECT post_ids where values downloable and virtual are 'no' at the same time. To have an example, there are two rows in 'wp_postmeta', one where 'meta_key' is '_downloable', 'meta_value' is 'no' and 'post_id' is 100, and another one where 'meta_key' is '_virtual', 'meta_value' is 'no' and 'post_id' is 100.

I just get to do it separately, as follows:

SELECT wp_postmeta.post_id
FROM wp_postmeta
WHERE wp_postmeta.meta_key='_downloadable'
AND wp_postmeta.meta_value='no';

SELECT wp_postmeta.post_id
FROM wp_postmeta
WHERE wp_postmeta.meta_key='_virtual'
AND wp_postmeta.meta_value='no';

Can you help me?

Are you looking for IN?

SELECT wp_postmeta.post_id
FROM wp_postmeta
WHERE wp_postmeta.meta_key in ('_downloadable', '_virtual')
AND wp_postmeta.meta_value='no';

Updated answer as per updated question:

I'd try to understand your question.

You have following table:

mysql> select * from s;
+------+------+------+
| id   | skey | sval |
+------+------+------+
|    1 | v    | n    |
|    1 | d    | n    |
|    2 | b    | n    |
|    3 | v    | n    |
|    3 | d    | y    |
|    4 | v    | n    |
|    4 | d    | n    |
|    2 | c    | n    |
+------+------+------+

You want final answer as 1 and 4 because both "v" and "d" are "n" at-the-same-time (for id).

If this is right requirement then below query should work.

select id from s where skey in ('v','d') and sval='n' group by id having count(*)=2;


Select permission denied when using linked server in a function unless inlining is off We are encountering an unusual error in our beta environment after upgrading to SQL Server 2019. This occurs when selecting from a scalar-valued function. This error does not occur in our production environment running SQL 2014.

The statement is:

select function(1234567)

The error message is:

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

The function is:

CREATE FUNCTION [dbo].[function](@var1 INT)
RETURNS BIT
BEGIN
    DECLARE @var2 BIT, @var3 BIGINT, var4 INT
    SET @var2 = 0

    SELECT @var3= ISNULL(tbl2.field1,tbl3.field1) FROM tbl1
    LEFT OUTER JOIN tbl2 WITH (NOLOCK) ON tbl2.field2 = tbl1.field2
    LEFT OUTER JOIN tbl3 WITH (NOLOCK) ON tbl3.field2 = tbl1.field2
    WHERE tbl1.field2 = @var1

    SELECT @var4 = ISNULL(field4,1) FROM linked_server.database.dbo.tbl4 WHERE field5=Convert(VARCHAR(20),@var3)
    IF(@var4 <> 1)
    BEGIN
        SET @var1 = 1
    END

    RETURN @var1
END

GO

Successful workarounds:

Individual users can execute the statement inside the function without error
Modifying the function to include the clause "with inline = off"
Adding the user executing the function to the sysadmin server role

I'm not sure if we've found a bug or if it's just a case of missing permissions (or something else entirely). We would like to avoid changing the code if possible; elevating user permissions to sysadmin is a non-starter.

We are running CU2. We have not tested on CU3.

UDF inlining is now blocked when the definition references remote tables.
This restriction was added in Microsoft SQL Server 2019 CU6.


How to grant permission to new user created in mysql? I want to create new user and want to give them specific permission like inset, update and delete.
But how to create new user and give them permission i don't know.

Any help please.

It would be something like this :

GRANT SELECT, INSERT, DELETE ON database TO username@'localhost' IDENTIFIED BY 'password';

To see a list of the privileges that have been granted to a specific user:

select * from mysql.user where User='username';

OR

CREATE USER 'newuser'@'localhost' IDENTIFIED BY 'password';
GRANT ALL PRIVILEGES ON * . * TO 'newuser'@'localhost';


How to measure improvement after splitting tablespaces into multiple disk volumes? (Oracle 10g AWR) I have a recently released 10 000 user analytical application that is suffering from performance issues because of excessive amount of transactions. Apart from re writing many of the SQLs in the application for better performance we are also on top down approach where we are adding more volumes and tweeks to memory etc. An Oracle specialist redesigned our sever from single tablespace with 2 volumes to 2 tablespaces with 4 volumes where the indexes will have their own volume and tablespace. Each volume is a separate disk array so they do not compete for IO.

We have executed this change in development env and we are about to move into acceptance but before paying for the change the business would like to see measurable gains.

Where in the AWR report can I make comparisons to measure such data on the before and after the change is executed?

From user APC:

The AWR has this section called IO Stats. Not unreasonably, this is where it reports the statistics for IO activity. Within that section there are figures for each tablespace. You need to run these reports against the target database. Ideally you want several different runs, each run being no more than twenty minutes long; the longer the run, the more likely it is that the law of averages will drain meaning from the figures you get.

So, the principle is: you run this report and get a benchmark. Then you deploy your changes and re-run the report. The difference in the various columns is the performance benefit of the change. Really you would want to requests and data per sec go up, average time and waits go down.

To make these figure more convincing you should run the same workload in the before and after tests. This is where something like Real Application Testing really comes into its own.


How should I reinit a new db (windows 8 64b)? In the data directory of Mysql some files are corrupt. Recovering is not working anymore. I have a complete sql backup and want to setup a new db. The complete stuff is installed with the use of wampserver.

I just renamed the :
D:\wamp\bin\mysql\mysql5.6.17\data
to
D:\wamp\bin\mysql\mysql5.6.17\data_old

and made a new folder called
D:\wamp\bin\mysql\mysql5.6.17\data

Changed socket 3306 to 3307 but now getting:

Fatal error: Can't open and lock privilege tables: Table 'mysql.user' doesn't exist

How should I reinit a new db (windows 8 64b)?

you would require mysql folder from the data_old directory. As it looks for mysql related tables whenever you initiate mysql on with some other folder.    Perform following steps in sequence

  1 stop MySQL instance for old instance

  2 copy data folder or system databases folders(mysql,performace_schema,information_schema,test) folder from  "D:\wamp\bin\mysql\mysql5.6.17\data_old"   to the new path " D:\wamp\bin\mysql\mysql5.6.17\data"

  3 mysqld --install  --defaults-file =Absolute Path to .ini file

If you are successful, the message “Service successfully installed.” will appear

If so you should be able to connect to new MySQL instance with new port.

Hope it helps


Referring system views and functions in production functions I am using the following system objects in a stored procedure in order to build dynamic T-SQL statements:

[sys].[objects]
[sys].[parameters]
[sys].[dm_exec_describe_first_result_set]
[sys].[columns]

As I have never used system objects in production code I am wondering are there any drawbacks or issues that could appear (for example concerning performance) or it is OK to use them as regular user defined objects.

There should be no real performance problems related to using the system objects to build your T-SQL.

Consider also that building your dynamic T-SQL is probably a much lighter load on your server than actually running the T-SQL statements that you generate.

There are also INFORMATION_SCHEMA views, but these only partially cover the system objects.  I mention this only to warn you that, although once recommended, it has many limitations and I do not use them any more.

Likely the biggest issue for your code is that system objects do change over time.  Probably most changes would not affect your code generation, but you should plan in advance before upgrading to a new version of SQL Server.

SQL Server documentation usually details out the changes for the next upgrade.

For example, here is a link for SQL Server 2014's breaking changes.
https://msdn.microsoft.com/en-us/library/ms143179.aspx#SQL14


SQL Server Cannot Create Transactional replication Error 14013 I am trying to create transactional replication but encountered the following error:

Please help,

[

You can follow these steps before setting up a Publication.
Tutorial: Prepare SQL Server for replication (publisher, distributor, subscriber)

Create Windows accounts for replication.
Prepare the snapshot folder.
Configure distribution.

I believe you skipped this. If not, please edit you question with the steps you already followed successfully.


Troubleshooting high disk read - buffer cache query I have a table tbl_Table1 that is about 5 GB in size and has millions of rows.

It has these columns:

ID, ContractID, Person, Address1, Address2, and many more.

One clustered index on the PK, ID.

I then have a query like this:

SELECT * FROM tbl_Table1 WHERE ContractID = 2000

So this query isn't going to be using an index, so will do a full table scan.

Will this table then be kept in the buffer cache after this query is executed? Or will it continually read from disk?

I understand the answer is to add an index, but I want to know what happens in terms of buffer cache.

Thank you

So this query isn't going to be using an index, so will do a full table scan

Yes it will definitely do table scan if there is no index.

  Will this table then be kept in the buffer cache after this query is executed? Or will it continually read from disk?

No whole table might or might not be kept into buffer cache depending on size and whether SQL Server finds all such pages during read ahead reads. SQL Server would try to bring as much page as possible which it can do with read ahead reads. The amount of read ahead pages SQL Server can read in enterprise edition is much more and efficient than standard edition. So its quite possible that SQL Server would read not only page that holds row but more such pages so it might not do any Physical reads but since most pages which belong to table are bought into memory (by read ahead) and since index is not present SQL Server will do some good amount of logical reads to find the row

Example:

I run a query like below on Developer edition which is same like Enterprise edition. Table T1 has no index

set statistics IO ON
set statistics TIME ON
select * from T1 where col1 =2

you would see following in message section of output

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

  (1 row(s) affected) Table 't1'. Scan count 1, logical reads 164,
  physical reads 0, read-ahead reads 170, lob logical reads 0, lob
  physical reads 0, lob read-ahead reads 0.

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

The table structure is almost same as your table and query as well. So you can see SQL Server did 170 read aheads and 164 logical reads. 170 read ahead brought all the pages that were associated with table now since all pages were in memory SQL Server scanned all pages to locate the row doing 164 logical reads.

Remember every read is logical read, physical reads are just there to show that page was brought into memory using physical I/O and read ahead reads do not count under physical reads.


SQL server tools to identify query optimisations and missing indexes I have a broad question regarding SQL Server that was given to me on a quiz and I am not sure what the answer is as I do not have access to SQL Server at home. My question is how can I utilise SQL Server tools to identify query optimisations, missing indexes etc?

Can somebody give me a very brief answer to this?

Thank you

how can I utilise SQL Server tools to identify query optimisations, missing indexes etc?

No tool is perfect and you should not rely implementing the suggestions without proper testing your entire business life cycle.

Native tools like DTA are OK, but I have seen many problems with using it. I would not rely on it.

That being said, SQL Server exposes a wealth of information using DMVs and Extended Events.

I would suggest you start with : SQL Server Diagnostic Information Queries - Glenn Berry - choose as per your version of SQL Server.

Also, highly recommend this FREE book from RedGate - Performance Tuning with SQL Server Dynamic Management Views

You have to baseline your servers and then you can compare what is good and bad for your environment.

Remember performance tuning is an art, not science. It will take you some time to digest the resources I have cited and this is how I have learned.

You should be cautious when creating Missing indexes exposed by DMVs

Other blogs that contain a wealth of information are (in no particular order):

SQLSkills.com
BrentOzar.com
SQLPerformance.com
Querrprocessor.com
CSS SQL Server Engineers
SQLServerCentral - Stairway series


What is the special database "postgres" for? I have a PostgreSQL server with several databases running on it. One of the databases is called postgres, and it was there right from the beginning. I could not see any tables inside and I've never used it explicitly, but I noticed, that any user is allowed to create tables within the database (this is not what I want - I do not want any users except the administrator to create any tables anywhere).

I have a feeling that it is some kind of a special database that is required for a normal operation of the server, but what is exactly its purpose? What kind of data is (or could be) stored inside? Does it hold the metadata about other databases (e.g. the information_schema?). Can something nasty happen, if I revoke connect privilege to this database from public? Or if I even drop it?

I googled for half an hour, but always got completely unrelated stuff, because the word postgres is also used for the user postgres and the DBMS itself.

There is nothing special about it. As described in PostgreSQL documentation, Creating a Database, you need to be connected to a database, in order to create another one:

Since you need to be connected to the database server in order to execute the CREATE DATABASE command, the question remains how the first database at any given site can be created. The first database is always created by the initdb command when the data storage area is initialized. (See Section 17.2.) This database is called postgres. So to create the first "ordinary" database you can connect to postgres.

and in Template Databases:

Note: template1 and template0 do not have any special status beyond the fact that the name template1 is the default source database name for CREATE DATABASE. For example, one could drop template1 and recreate it from template0 without any ill effects. This course of action might be advisable if one has carelessly added a bunch of junk in template1. (To delete template1, it must have pg_database.datistemplate = false.)
The postgres database is also created when a database cluster is initialized. This database is meant as a default database for users and applications to connect to. It is simply a copy of template1 and can be dropped and recreated if necessary.


Optimize SELECT statement for mysql database I have this query:

SELECT COUNT(*) AS "Other"
FROM events
JOIN triggers ON events.objectid = triggers.triggerid
WHERE events.name REGEXP "No data from SAM0056(.FTG|.REC|.LEFT|.RIGHT|.BACK), .* offline"
AND events.value = 1
AND triggers.value = 1;

In total I need to execute 32 variants of these query depending on the REGEXP. The part "SAM0056" ranges form "SAM50" to "SAM0082". So the pattern is "No data from SAM00XX(.FTG|.REC|.LEFT|.RIGHT|.BACK), .* offline" where XX ranges from 50 to 82.

I also need to execute these query every 15 seconds. So I have a lot of select statements.

These are the DDL for events and triggers:

CREATE TABLE `events` (
  `eventid` bigint(20) unsigned NOT NULL,
  `source` int(11) NOT NULL DEFAULT '0',
  `object` int(11) NOT NULL DEFAULT '0',
  `objectid` bigint(20) unsigned NOT NULL DEFAULT '0',
  `clock` int(11) NOT NULL DEFAULT '0',
  `value` int(11) NOT NULL DEFAULT '0',
  `acknowledged` int(11) NOT NULL DEFAULT '0',
  `ns` int(11) NOT NULL DEFAULT '0',
  `name` varchar(2048) COLLATE utf8_bin NOT NULL DEFAULT '',
  `severity` int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`eventid`),
  KEY `events_1` (`source`,`object`,`objectid`,`clock`),
  KEY `events_2` (`source`,`object`,`clock`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin

and

CREATE TABLE `triggers` (
  `triggerid` bigint(20) unsigned NOT NULL,
  `expression` varchar(2048) COLLATE utf8_bin NOT NULL DEFAULT '',
  `description` varchar(255) COLLATE utf8_bin NOT NULL DEFAULT '',
  `url` varchar(255) COLLATE utf8_bin NOT NULL DEFAULT '',
  `status` int(11) NOT NULL DEFAULT '0',
  `value` int(11) NOT NULL DEFAULT '0',
  `priority` int(11) NOT NULL DEFAULT '0',
  `lastchange` int(11) NOT NULL DEFAULT '0',
  `comments` text COLLATE utf8_bin NOT NULL,
  `error` varchar(2048) COLLATE utf8_bin NOT NULL DEFAULT '',
  `templateid` bigint(20) unsigned DEFAULT NULL,
  `type` int(11) NOT NULL DEFAULT '0',
  `state` int(11) NOT NULL DEFAULT '0',
  `flags` int(11) NOT NULL DEFAULT '0',
  `recovery_mode` int(11) NOT NULL DEFAULT '0',
  `recovery_expression` varchar(2048) COLLATE utf8_bin NOT NULL DEFAULT '',
  `correlation_mode` int(11) NOT NULL DEFAULT '0',
  `correlation_tag` varchar(255) COLLATE utf8_bin NOT NULL DEFAULT '',
  `manual_close` int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`triggerid`),
  KEY `triggers_1` (`status`),
  KEY `triggers_2` (`value`,`lastchange`),
  KEY `triggers_3` (`templateid`),
  CONSTRAINT `c_triggers_1` FOREIGN KEY (`templateid`) REFERENCES `triggers` (`triggerid`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin

An explain-statement of the query shows this:

+------+-------------+----------+--------+--------------------+---------+---------+------------------------+------+-------------+
| id   | select_type | table    | type   | possible_keys      | key     | key_len | ref                    | rows | Extra       |
+------+-------------+----------+--------+--------------------+---------+---------+------------------------+------+-------------+
|    1 | SIMPLE      | events   | ALL    | NULL               | NULL    | NULL    | NULL                   | 8397 | Using where |
|    1 | SIMPLE      | triggers | eq_ref | PRIMARY,triggers_2 | PRIMARY | 8       | zabbix.events.objectid |    1 | Using where |
+------+-------------+----------+--------+--------------------+---------+---------+------------------------+------+-------------+

And an insert-statement into these 2 tables (events and triggers):

insert into events (eventid,source,object,objectid,clock,ns,value,name,severity)
values (4985807,0,0,22281,1585805538,856070799,1,'No data from SAM0106-FTG, FortiGate offline',3)

INSERT INTO triggers (description,expression,recovery_mode,type,url,priority,comments,manual_close,status,correlation_mode,recovery_expression,correlation_tag,templateid,triggerid)
VALUES ('CPU-Temperature to high','{29157}>35','0','0','','3','','1','0','0','','','22515','22541')

So how can I optimize these query?
I use mariadb version 10.1.44 on a Ubuntu version 18.04.4.

FULLTEXT(name) and MATCH(name) AGAINST('+SAM0106' IN BOOLEAN MODE)

That will let you quickly whittle down the list of possible rows.  After that, the REGEXP (or whatever) can finish the filtering.

PRIMARY KEY(triggerid) is the other index that is useful for that SELECT.


Understanding DBCC CHECKDB, Snapshots, and SQL Server Standard Edition Understanding the circumstances under which SQL Server creates a snapshot when running CHECKDB, I followed a link to this KB that states:

Database snapshots are available only in SQL Server Enterprise.

I'm trying to plan some maintenance work and need to understand potential I/O bottlenecks that could extend this maintenance window.

Does this snapshot limitation imply that running DBCC commands on SQL Server Standard SKUs will always run against the actual database, or is an internal snapshot still used?

You can refer to Paul Randal's blog (the guy who wrote DBCC CHECKDB) :
CHECKDB From Every Angle: Complete description of all CHECKDB stages :

For SQL Server 2005, CHECKDB uses an internal database snapshot to provide the required transactional consistency.
So when CHECKDB starts in SQL Server 2005, the first thing it does is work out whether it can run online – if so it creates a hidden database snapshot of the source database (i.e. CHECKDB’s target database). As the database snapshot is hidden, there is no control over where the snapshot files are placed – in fact they are created as alternate streams of the files comprising the source database.
From SQL Server 2014 onward, alternate streams are not used, although the database snapshot is created in the same location as the existing database.

As a side note, since you are using Standard edition, DBCC CHECKDB will not perform parallel checking of objects as opposed to Enterprise edition wherein it honors the MAXDOP setting configured for the server instance.
Also, BOL has a clear writeup on CHECKDB Internal Database Snapshot


Splitting a database into multiple databases I have a database which contains large amounts of measurements (20+ instruments, each recording 60 measurements per second, each measurement containing 10+ complex values). All measurements are stored in a single table, normalized, and indexes are used to allow filtering by instrument, time and other columns.

Needless to say, every 6 months, the database gets so large that scheduled index maintenance tasks take more than a day to complete. So far, I've had to turn off the db twice, for almost two days, to run scripts which would backup and then delete old data in order to "refresh" it.

My colleagues suggest a (logical, IMHO) modification to physically separate the database into multiple databases, by instrument and time. Furthermore, different clients will access different instruments, so keeping each instrument separated will simplify multi-tenant scenarios (providing backup to customers, migrating to other machines, etc.).

In other words, I would go from this:

  Big database
     - AllMeasurements table

to this:

  Instrument001 database
     - Measurements-2012-01 table
     - Measurements-2012-02 table
     - Measurements-2012-03 table

  Instrument002 database
     - Measurements-2012-01 table
     - Measurements-2012-02 table
     - Measurements-2012-03 table

Since my DAL is abstracted through the repository pattern, it would be easy to swap the underlying implementation to switch databases and tables on demand, so coding this shouldn't a problem.

But I am now wondering if this is really the best way?

Does it make sense?

[Edit] I'm using SQL Server 2008, if that's relevant.

If the problem is this single table getting huge, you should consider partitioning it (by date, since your data seems to be time-based).

This way the SQL engine query planner may be able to ignore some partitions (if they contain data too old to appear in your query results) when querying and thus perform better.

Additionally, partitionning will be handled transparently by your database engine, so you have nothing to change in your code.

Another advantage would be that you can imagine to move the 'old' partitions, containing data that is rarely accessed, to slower (and cheaper) storage.

You can also set up your indexes to be partitioned so you can only rebuild a part of an index:
http://msdn.microsoft.com/en-us/library/ms187526.aspx

Index partitioning is a different feature from table partitioning, it can avoid you to have to rebuild an entire index if it's not needed.


What are the options for archiving old data of mariadb tables if partitioning can not be implemented due to a restriction We are planning to archive some old data of our MariaDB tables to some other volume(or mount point) of the linux server apart from the one the tables are on.

We are using partitioning for this and the tables are innodb tables with 'innodb_files_per_table=ON'.

While doing this we are placing the partitions containing old data to a directory located in some other volume.

But a restriction in the form of 'every unique key on the table must use every column in the table's partitioning expression. (This also includes the table's primary key, since it is also a unique key.)' is a major road blocker in our activity.
(https://dev.mysql.com/doc/refman/5.7/en/partitioning-limitations-partitioning-keys-unique-keys.html )

Here for partitioning we have to use a particular column which in most of the  cases is not included in every unique key created on the table.
We can  not even change the key structure of the tables as it may impact the operation of applications.

So , we have to opt for other practices to archive the data. Any suggestions on this will be really helpful.

Sample scenario:
Table:

CREATE TABLE `tbl` (
  `col1` int(11) NOT NULL DEFAULT '0',
  `col2` int(11) NOT NULL AUTO_INCREMENT,
  `col3` int(11) NOT NULL DEFAULT '0',
  `col4` varchar(60) NOT NULL DEFAULT '',
  `col5` varchar(60) DEFAULT NULL,
  `changed` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE
   CURRENT_TIMESTAMP,

   UNIQUE KEY `uniq1` (`col2`),
   UNIQUE KEY ` uniq2` (`col1`),
   UNIQUE KEY ` uniq3` (`col4`,`col3`),
   UNIQUE KEY `uniq4` (`col5`,`col3`),
   KEY `key1` (`changed`),
 );

We are partitioning  w.r.t  col1.

Query:

alter table tbl partition by range(col1)( partition p0
values less than (10000000) data directory = '/partitions',partition p1 values less than
(20000000) data directory = '/partitions',partition p2 values less than
(30000000),partition p3 values less than (maxvalue));

ERROR 1503 (HY000): A PRIMARY KEY must include all columns in the table's partitioning
function

Here's a thought on archiving, with transactional integrity.

Have another table; let's call it archive.  It needs few, if any indexes.
Move 1000 rows from your table to archive at a time, transactionally.
After you have moved "enough". do whatever you want to Archive archive.

See this for suggestions on efficiently DELETEing rows from a table.  The rest is

INSERT INTO `archive` SELECT ... FROM main ...;

where the ... come from the chunking.  That can probably be done with autocommit=on and not need any BEGIN...COMMIT.


How to create the multiple sum query with joins Please consider following database:

Table `invoices`:
-- invoice_id

Table `amounts`:
-- amount_id invoice_id type_id amount

I am trying to get the sum for each type from table like this:

SELECT
    invoice_id,
    SUM(amounts1.amount),
    SUM(amounts2.amount),
    SUM(amounts3.amount)
FROM
    invoices
LEFT JOIN
    amounts amounts1 ON (amounts1.invoice_id = invoices.invoice_id AND amounts1.type_id IN (1,2,3))
LEFT JOIN
    amounts amounts2 ON (amounts2.invoice_id = invoices.invoice_id AND amounts2.type_id IN (4,5,6))
LEFT JOIN
    amounts amounts3 ON (amounts3.invoice_id = invoices.invoice_id AND amounts3.type_id IN (7,8,9))
GROUP BY
    invoices.invoice_id

This query returns unexpected results. Result is different when I remove one of the joins:

SELECT
    invoice_id,
    SUM(amounts1.amount) // returns different amount
FROM
    invoices
LEFT JOIN
    amounts amounts1 ON (amounts1.invoice_id = invoices.invoice_id AND amounts1.type_id IN (1,2,3))
GROUP BY
    invoices.invoice_id

Any ideas what I am doing wrong?

Same thing with COUNT(amounts1.amount_id)

Edit: I realized problem occurs when there is one type_id=1 amount and more than one type_id=4 amount - amount type_id=1 is added twice. Like this:

amount_id amount type_id invoice_id
    51     2.99     1        2          // SUM(amounts.amount1) returns 8.97
    52     2.99     4        2
    53     2.99     4        2
    54     2.99     4        2

How to avoid it?

I believe you can avoid joining the same table 3 times by

SELECT
    i.invoice_id,
    SUM(CASE
        WHEN a.type_id IN (1,2,3) THEN a.amount
        ELSE 0 END) AS sum1,
    SUM(CASE
        WHEN a.type_id IN (4,5,6) THEN a.amount
        ELSE 0 END) AS sum2,
    SUM(CASE
        WHEN a.type_id IN (7,8,9) THEN a.amount
        ELSE 0 END) AS sum3
FROM
    invoices AS i
LEFT JOIN
    amounts AS a ON a.invoice_id = i.invoice_id
                AND a.type_id IN (1,2,3,4,5,6,7,8,9)
GROUP BY
    i.invoice_id ;

Note: depends on your requirements, LEFT JOIN can be replaced with INNER (then you won't have invoices without amounts in resultset)


Issues with maintenance plan - SQL 2016 One of my client is having issues with one of the jobs failing every weekend to perform index maintenance on a couple of databases. It is a SQL job which uses maintenance plans underneath. Okay, so the maintenance plans consist of check database integrity task, followed by 3 t-sql task in sequence which runs the same script but for different databases (code provided below) and finally runs a reorg task and then update of statistics. I know this seems a bit weird the way they are running things at this time but I will change it moving forward. Presently, I am stuck with this job completing the maintenance check but failing to execute the t-sql task with the following error.

Executing the query "USE XMain;

declare @frag_Temp as Table
(
  ..." failed with the following error: "Incorrect syntax near '-'.
Changed database context to 'XMain'.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

USE XMain;

DECLARE @frag_Temp AS TABLE (
  ID int IDENTITY (1, 1),
  [objectid] [int] NULL,
  [indexid] [int] NULL,
  [partitionnum] [int] NULL,
  [frag] [float] NULL
)

DECLARE @Count int
DECLARE @i tinyint = 1
DECLARE @schemaname sysname;
DECLARE @objectname sysname;
DECLARE @indexname sysname;
DECLARE @objectid int;
DECLARE @indexid int;
DECLARE @partitionnum bigint;
DECLARE @partitioncount bigint;
DECLARE @SQLCommand AS nvarchar(3000)
INSERT INTO @frag_Temp
  SELECT
    object_id AS objectid,
    index_id AS indexid,
    partition_number AS partitionnum,
    avg_fragmentation_in_percent AS frag
  FROM sys.DM_DB_INDEX_PHYSICAL_STATS(DB_ID(), NULL, NULL, NULL, 'LIMITED')
  WHERE avg_fragmentation_in_percent >= 30.0
  AND index_id > 0;

-- Get Total Count
SELECT
  @Count = COUNT(*)
FROM @frag_Temp
WHILE (@i <= @Count)
BEGIN
  SELECT
    @objectid = objectid,
    @indexid = indexid,
    @partitionnum = partitionnum
  FROM @frag_Temp
  WHERE ID = @i

  -- Get tableName and its schema
  SELECT
    @objectname = o.name,
    @schemaname = c.name
  FROM sys.objects o
  INNER JOIN sys.schemas c
    ON o.schema_ID = c.schema_ID
  WHERE o.object_id = @objectid
  -- Get Index Name
  SELECT
    @indexname = name
  FROM sys.indexes
  WHERE index_id = @indexid
  AND object_id = @objectid
  -- Get Partition Count
  SELECT
    @partitioncount = COUNT(*)
  FROM sys.partitions
  WHERE object_id = @objectid
  AND index_id = @indexid

  SELECT
    @SQLCommand = 'Alter Index ' + @indexname + ' ON ' + @schemaname + '.' + @objectname + ' REBUILD;'
  IF (@partitioncount > 1)
    SELECT
      @SQLCommand = @SQLCommand + ' PARTITION=' + CONVERT(char, @partitionnum);

  EXEC (@SQLCommand);

  -- Increment Count
  SET @i = @i + 1

END
GO

Your client should use the QUOTENAME function, if not, here is an example:

DECLARE @schemaname sysname = 'dbo';
DECLARE @objectname sysname='-mySuperTableName';
DECLARE @indexname sysname = 'ix1';
DECLARE @SQLCommand AS nvarchar(3000)

  SELECT
    @SQLCommand = 'Alter Index ' + @indexname + ' ON ' + @schemaname + '.' + @objectname + ' REBUILD;'
    print  @SQLCommand

Try to execute this code and the command you received is Alter Index ix1 ON dbo.-mySuperTableName REBUILD; and it definitely gives you an error

  Incorrect syntax near '-'.

if you try to execute the command


Having a subquery somehow do the same group by as the outer query i'm pretty decent with SQL but it's not my main experience of expertise. A colleague asked me a stumper of a question the other day. I was wondering if you guys could provide assistance.

Basically we're doing an over all count and grouping these counts by days... but we'd also like to kind of subquery those counts to get a specific type of count.

This is what I have so far that's not quite right.

select d.start_date,
       count(a.status),
       (   select count(checked_ins) as checked_ins
           from (   select distinct(a.id) as checked_ins
                    from schema1.employee_se_assignment a,
                         schema2.se d
                    where a.se_id= d.id
                      and a.status not in ('unassigned')
                      and d.customer_name in (‘Test Client’)
                      and a.status = 'checked_in'
                ) src
       )
from schema1.employee_se_assignment a,
     schema2.se d
where a.se_id= d.id
  and a.status not in ('unassigned')
  and d.customer_name in (‘Test Client’)
  and a.status = 'checked_in'
group by d.start_date
Order by d.start_date

that yields the following results. which is almost what he wants. but it's clearly yielding the total of all of a certain type of status when we want it broken down by day. EG (see below):

It is legal, but confusing and a bad idea, that you use the same table aliases in different levels of the query.  You could likely do what you want by adding the condition and d.start_date = d2.start_date in the subquery, but of course you would have to have one of the aliases be "d2" rather than "d" to do that.

But it seems like you could get rid of the subquery altogether by doing:

select d.start_date,
       count(a.status),
       count(distinct a.id) as checked_ins
from schema1.employee_se_assignment a,
     schema2.se d
where a.se_id= d.id
  and a.status not in ('unassigned')
  and d.customer_name in (‘Test Client’)
  and a.status = 'checked_in'
group by d.start_date
Order by d.start_date

Although these do differ in how NULL values of a.id are tallied, if it is possible for there to be any of these.


Questioning a DB Consultant and their recommendations We have hired a systems design consultant to help use architect a new CRM system, bringing together several business needs into one system, instead of the several we currently have.

It's the typical project that a company spends a fortune on, and then discovers it doesn't do what it thought it would. I'm trying to make sure that doesn't happen to us.

Could I have some community feedback on some parts of the solution our consultant has recommended? Personally I'm unsure, but I'd defer to their expertise, and to this community's collective knowledge.

Our User Tables

Some of our users have verified accounts with us. Some will have bought tickets through a third party provider. Others will simply reach out to our customer service, and may not be customers yet.

One way of doing this would be to use three tables: VerifiedUsers (data from our verified user accounts -- email, name, post code, etc.), TicketPurchasers (data pulled from our ticket provider - also verified -- email, name, post code, etc.), and Individuals (essentially only an email addresses used to contact our customer service).

Obviously it would be great to make connections between these tables, so we can pull together as much information about our customers/users as possible.

This all seems fine to me so far. Where I get a little unsure is that the consultant has suggested the Individuals table should be the master table.

This table is populated with the most "dirty" data: The least verified data sent from people with the lowest at stake (potentially not even customers, sometimes just so they can rant at our customer service team).

Also, if someone has several email addresses they use to reach out to customer service, how are we supposed to make a reliable trustworthy connection between the TicketPurchasers and VerifiedUser tables? It just seems likely that one person could easily have several rows of data in the Individuals table, and we would never really know.

Question: Does this make sense, even just from a database integrity point of view?

Building our own analytics

The consultant has also recommended we start creating our own analytics. This ostensibly makes sense as well: It would be great to be able to collate every touch point our customers/users make.

The idea would be that a table would be filled with a user_id (presumably taken from the Individuals table), an action_id (to reference the action they took -- eg. "Logged into their account", "Contacted customer service") and a timestamp.

This data could be potentially interesting to look at (although I'd like to sit down and ensure it's useful, too), but after a few quick calculations it seems would could fill 1,000,000 rows in a year.

If we continue growing as a company (as we wish to), this table of analytics could easily hit 10,000,000 rows in a few years, and just keep growing.

This scares me.

For a start, I don't like the idea of a table just growing and growing. It makes me uneasy. Secondly, running live operations on a table of millions of rows (which is what would be required), could be prohibitively resource intensive. (Yes, I guess it depends how much my organisation is prepared to spend on this.)

Question: Are my concerns valid, or is it my inexperience?

Edit: In terms of size, it looks like I need to relax: https://stackoverflow.com/a/1995078/199700 Phew.

Thanks for any help!

If we continue growing as a company (as we wish to), this table of
  analytics could easily hit 10,000,000 rows in a few years, and just
  keep growing.

  This scares me.

10M rows isn't a matter to be worried about. Even 10B rows isn't a matter for a single host. The main criteria is the throughput of the DB/engine. To prevent hangups and outages you need to be able to ingest/return the data at the rate at least 3x faster than your actual (and potential) average load. 10x is better of course. This goal can be achieved in two ways. First is the DB/queries optimization. In most cases results are more than acceptable. But every engine has its limitation that can be overcomed by some kind of scaling. Another way is the clustering, segmentation, load balancing etc. Here the DBA should pass the problem to the system architect. This is a different set of competencies. Here is the good explanation how it works:
https://github.com/donnemartin/system-design-primer


"Server not open" / "Drop tablespace" catch-22 situation I am getting the "server not open" error in SQLPLUS, and the equivalent on Oracle SQL Developer, which says "Oracle Initialization or shutdown in progress".

Fair enough. The fix for this is as follows:

You check to see the status of your database:

select status, database_status from v$instance;

and I see that it is mounted and active. Then I run the following command:

 alter database open;

Which generates the following error:

  ORA-01157: cannot identify/lock data file 6 - see DBWR trace file
  ORA-01110: data file 6: 'F:\XXXX_INDEX.DBF'

To resolve this issue, I referred to this resource.

Which is telling me to open the database by running the same alter database open command as above.

The issue is circular.

No, it is not circular, alter database open is the 3rd step.

If you have a backup of that datafile, then restore and recover it.

If you do not have a backup, then do what the post you referenced tells you. Since your database is already mounted, you can skip step 1.

  If the database is down, mount it.

  STARTUP MOUNT;

  Offline drop the datafile.

  ALTER DATABASE DATAFILE 'full_path_file_name' OFFLINE DROP;

  If the database is at mount, open it.

  ALTER DATABASE OPEN;

  ...


Why does altering a table and adding a foreign key create an extra constaint? Edit: It was a default constraint as a result of setting a default value when adding the column. The name gave it away when starting with 'DF__'. Anyway, solved it by adding the column and manually naming the constraint.

ALTER TABLE MyTable ADD [FooId] [int] NOT NULL CONSTRAINT DF_MyTable_FooId DEFAULT 0

And now I can easily delete the constraint and delete the column.

For testing purposes I need to drop and recreate a few tables quite often. I am using MS-Sql server 2008 r2.

When I create the tables I also create a foreign key in an existing table (one that I cannot just drop) with:

ALTER TABLE MyTable
ADD CONSTRAINT FK_MyTable_NewTable
FOREIGN KEY (FooId) REFERENCES NewTable (Id)

this does create the foreign key correctly, but it also adds a constraint with a system generated name (something like DF__MyTable_FooId__53385258), that is not create if the foreign key is defined in the CREATE TABLE statement.

My problem is, that when I want to drop MyTable, I have to remove the foreign key constraint - this is easy enough since I named it, but the other system generated constraint is still there and I don't have a direct way to delete it.

Is it possible to add the foreign key to an existing table without the system generating a second (and redundant I assume) constraint?

The only way I can think of that a DDL statement like yours may result in an "extra" constraint is when there's a DDL trigger that creates that constraint in response to your DDL statement(s). If that is not the case with your database, the said extra constraint is definitely the result of a totally different statement unconnected with the one that creates the foreign key.

It could have appeared at an earlier stage (e.g. when the table was being created), or it could have been added by a statement that was executed in the same script with the one in your post and that fact for some reason didn't register with you at the time.


Azure blob storage container vs VM managed disk for SQL on Azure VM database files Are there benefits specific to SQL Server on Azure VM when using the SQL Server Data Files in Windows Azure feature? Aka using Azure blob storage container for database files versus an Azure VM data disk which itself is backed by Azure storage page blob?

The MS docs SQL Server data files in Microsoft Azure encourage the use of this feature with SQL on Azure VM but don't make clear a compelling advantage compared to using an Azure managed data disk.

The listed benefits apply to both forms of Azure storage AFAIK.

Are there any benefits specific to SQL on Azure VM?

  Why use SQL Server data files in Microsoft Azure?

  Easy and fast migration benefits
  Cost and limitless storage benefits
  High availability and disaster recovery benefits
  Security benefits
  Snapshot backup

Per MS docs SQL Server data files in Microsoft Azure > Concepts and Requirements

  This new feature uses Page blobs, which are more efficient when ranges
  of bytes in a file are modified frequently.

Per MS docs, Introduction to Azure managed disks

  Azure managed disks are stored as page blobs, which are a random IO
  storage object in Azure. We call a managed disk ‘managed’ because it
  is an abstraction over page blobs, blob containers, and Azure storage
  accounts.

The biggest advantage of putting the database files on page blobs is for very large databases is you can use File-Snapshot Backups for Database Files in Azure.  But it's a more advanced and somewhat more tricky feature than using Azure Disks, which are very simple and user-friendly.

So the basic guidance is to use Azure Disks, unless you have a scenario where you really need to avoid creating a copy of the data for backups, or restoring as a size-of-data operation.  Note that for HA/DR using regular backup to URL or managed backup to Azure Blob storage is also much more useful than file snapshot backups, as you get Lifecycle Management, multi-region support, soft delete, and all the other features of blob storage of your backups.

And Azure Disks support things like Azure Backup and Azure Site Recovery, so they are compatible with enterprise-wide BC/DR solutions.  If you store your databases directly on blobs, or in Azure Premium Files the data is not automatically associated with your VM from a infrastructure, management, and monitoring POV.


MongoDB: how to create a new database After installation, test,admin and local are the default database.
As per the MongoDB tutorial, when we insert data in collection a database will be listed in show dbs command.
For a specific purpose, I want to create a new database but I did not find any information about creating a new database. I executed a command like create db dbname or create database dbname. For both of the statement I got below error:
SyntaxError: Unexpected identifier

Can I know what are the steps to create a database?

Just execute on shell "use dbname"
When you insert the first document on any collection you will see the db with "show dbs"


Is there a way to find the transaction count on a sql table? I need to find out the count of inserts, updates and deletes on a SQL tables in a given day or today? All counts together is fine too, if individual counts are not possible. Is there a way to find this stats information in SQL Server 2012?

You want to use the sys.dm_db_index_operational_stats DMF.

SELECT *
FROM sys.dm_db_index_operational_stats(DB_ID(), OBJECT_ID(N'schema.table'), NULL, NULL);

There is a lot of very interesting and useful info in that DMF, but with respect to this particular question, the following columns should have what you are looking for:

  leaf_insert_count -- bigint -- Cumulative count of leaf-level inserts.

  leaf_delete_count -- bigint -- Cumulative count of leaf-level deletes. leaf_delete_count is only incremented for deleted records that are not marked as ghost first. For deleted records that are ghosted first, leaf_ghost_count is incremented instead.

  leaf_update_count -- bigint -- Cumulative count of leaf-level updates.

It won't show history, so take a sample once per day. And like all DMVs and DMFs, it probably gets recent upon service restart.


Update value in same view using CASE expression I am creating a view where i have daily data of different items and each item has a state. I created a column that shows what the column was yesterday using

LAG(state) OVER(PARTITION BY item_id ORDER BY currentdate) AS yesterday_state

Now I want to count how long the state has been the same value and im doing so with this:

COUNT (CASE WHEN state = yesterday_state THEN state ELSE NULL END) OVER(PARTITION BY item_id ORDER BY currentdate AS state_age

This is working properly but I need to find a way to set the value back to 0 when state != yesterday_state

This all is happening inside of SELECT statement as I'm creating a view. How could I go around doing this so that the state_age sets to 0 when state is not the same value as yesterday_state

SOLUTION:

CREATE VIEW view AS (
    WITH
        cte1 AS (SELECT *, LAG(state) OVER(PARTITION BY item_id ORDER BY currentdate) AS state_yesterday
            FROM table),
        cte2 AS (SELECT *, CASE WHEN state = state_yesterday THEN 1 ELSE 0 END AS state_remained
            FROM cte1),
        cte3 AS (SELECT *, CASE WHEN state_remained != 0 THEN SUM(state_remained) OVER(PARTITION BY item_id, state ORDER BY currentdate) ELSE 0 END AS state_age_days
            FROM cte2)
        SELECT *
        FROM cte3
)

Schematically

WITH
cte1 AS ( SELECT *, LAG(state) OVER (ORDER BY datetime) AS prev_state
          FROM sourcetable),
cte2 AS ( SELECT *, CASE WHEN state = prev_state THEN 0 ELSE 1 END AS state_changed
          FROM cte1 ),
cte3 AS ( SELECT *, SUM(state_changed) OVER (ORDER BY datetime) AS group_number
          FROM cte2 )
SELECT *, COUNT(*) OVER (PARTITION BY group_number) AS group_count
FROM cte3

For multiple entities add proper partitioning / partitioning level.


Does MS provide a way to query a SSDT project Is there a way to query a SSDT project to get a list of tables or foreign keys?  Similar to what you can do with the actual database?

YES! You can query the output to an SSDT project called a dacpac using the DacFx.

The easiest way is to grab this sample project from microsoft (https://github.com/microsoft/DACExtensions/tree/master/DacFxStronglyTypedModel) and build the "DacExtensions" dll then reference that from your own code (c#, powershell etc.)

When you use the DacExtensions you can then you can ask for a list of tables or foreign keys, have a look at this:

https://stackoverflow.com/questions/40747082/finding-data-type-information-for-certain-objects-in-dacfx-api

The question shows how to get the TSQLModel and the answer has how to get a list of tables.

Good luck!


Differences between MATCH FULL, MATCH SIMPLE, and MATCH PARTIAL? I've noticed a MATCH SIMPLE and MATCH FULL, but I don't understand what they do. I see default is MATCH SIMPLE; but, how do the other MATCH clauses to the FOREIGN KEY constraint function?

Check the CREATE TABLE page of the manual:

  There are three match types: MATCH FULL, MATCH PARTIAL, and MATCH SIMPLE
  (which is the default). MATCH FULL will not allow one column of
  a multicolumn foreign key to be null unless all foreign key columns
  are null; if they are all null, the row is not required to have a
  match in the referenced table. MATCH SIMPLE allows any of the foreign
  key columns to be null; if any of them are null, the row is not
  required to have a match in the referenced table. MATCH PARTIAL is not
  yet implemented. (Of course, NOT NULL constraints can be applied to
  the referencing column(s) to prevent these cases from arising.)

Also, in the chapter on Foreign Keys:

  Normally, a referencing row need not satisfy the foreign key
  constraint if any of its referencing columns are null. If MATCH FULL
  is added to the foreign key declaration, a referencing row escapes
  satisfying the constraint only if all its referencing columns are null
  (so a mix of null and non-null values is guaranteed to fail a MATCH FULL
  constraint). If you don't want referencing rows to be able to
  avoid satisfying the foreign key constraint, declare the referencing
  column(s) as NOT NULL.

And be sure to consult the current manual or the version matching your installation. Don't fall for outdated Google links to outdated versions.


Update query in a trigger I have a configuration table:

Table: refdatecol

id           int(11)
startdatecol varchar(100)
enddatecol   varchar(100)
datecolTable varchar(100)

This table holds the user configured column names and table names.
I am building a query run time using the data from refdatecol and updating another table project_wbs. I am using dynamic SQL.
This works fine inside the procedure.

Problem is: I want to fetch the current date values into the project_wbs on insert OR update. Naturally, I thought of using trigger. But triggers do not support dynamic SQL in MySQL.

So I thought of calling the procedure from the trigger. Then I learnt it is a strictly no-no.

Is there any way I can achieve this?

I am also open to considering a complete redesign of this user-configurable dates

Update

Here is the stored procedure:

create  procedure sp_project_date (_id int)
begin

set @_startdate ='';
set @_enddate ='';
set @_projectId = (select project_wbs.proj_projectId
                   from   project_wbs project_wbs
                   where  id = _id);

set @q = (select concat('select ', startdatecol, ' , ', enddatecol ,
                        ' into @_startdate, @_enddate  from ' , datecolTable ,
                        ' where project_ProjectId = ' , @_projectId) as 'query'
          from refdatecol
          where id = (select p.cogsDateId
                      from   project p
                      where  p.projectId = @_projectId)
          );

prepare stmt from @q;
execute stmt;

UPDATE project_wbs
SET    project_startdate = @_startdate,
       project_enddate = @_enddate
WHERE  id = _id;

DEALLOCATE PREPARE stmt;

Perhaps you can use some user defined variables. I once wrote an answer to this : Disable trigger for just one table. I suggested setting up a user defined variable to disable a trigger.

In your case, dynamic SQL is not necessary if you are simply changing parameter values for a SQL statement. If you are changing table names and column names for the query, you got to construct some good IF...THEN blocks in the trigger like my post demonstrates. If there are too many tables or too many column names to make IF...THEN blocks, then stick with the Stored Procedure approach.


Sum duration for each day of a month Question: I would like to create a query that can return a list of all days of the month with a sum of all duration for each day including zero for each day that has no data.

Database: PostgreSQL 9.x

Table structure (only relevant columns shown):

-------------------------------------------------------------
| id | start_at            | finish_at           | duration |
-------------------------------------------------------------
| 1  | 2015-08-14 14:01:00 | 2015-08-15 13:59:00 | 86280    |
-------------------------------------------------------------

I have the following query for grabbing the data I want but the part that is missing is the days between with zero and I'm not entirely sure on the best approach to achieve the desired outcome.

SELECT date_trunc('day', start_at) AS "day" , sum(duration) AS "duration"
FROM time_logs
JOIN users ON time_logs.user_id = users.id
JOIN account_users ON account_users.user_id = users.id
JOIN categories ON categories.id = time_logs.activity_id
WHERE account_users.account_id IN (1,2,3)
AND time_logs.start_at BETWEEN '2017-03-01 00:00:00 +1000' AND '2017-03-31 00:00:00 +1000'
AND categories.report_group = 'Segment1'
GROUP BY 1
ORDER BY 1

Add an outer join to a complete series of days using generate_series():

SELECT day, COALESCE(duration, 0) AS duration
FROM  (  -- your original query, just added table aliases to reduce noise
         -- and fixed the upper bound of your time range
   SELECT date_trunc('day', t.start_at) AS day
        , sum(duration) AS duration  -- add table qualification to duration
   FROM   time_logs     t
   JOIN   users         u  ON t.user_id = u.id
   JOIN   account_users a  ON a.user_id = u.id
   JOIN   categories    c  ON c.id = t.activity_id
   WHERE  a.account_id IN (1,2,3)
   AND    t.start_at >='2017-03-01 00:00 +1'
   AND    t.start_at < '2017-04-01 00:00 +1' -- to get the whole month
   AND    c.report_group = 'Segment1'
   GROUP  BY 1
   ) sub
RIGHT  JOIN generate_series(timestamptz '2017-03-01 00:00 +1'
                                      , '2017-03-31 00:00 +1'  -- !
                                      , '1 day') day USING (day)
ORDER  BY 1;

And COALESCE to get 0 ("zero") instead of NULL, if you need that.

BETWEEN '2017-03-01 00:00 +1' AND '2017-03-31 00:00 +1' would exclude the last day of march (except for its very first µs). I changed it to include the whole month. AND t.start_at < '2017-04-01 00:00 +1' has the additional advantage that you don't need to adapt the day of the upper bound, just the month.

Since start_at is obviously timestamptz, that's the most efficient call with matching data type. Detailed explanation here:

Generating time series between two dates in PostgreSQL

The actual data type of start_at and your actual time zone setting might lead to disagreement when days start. Consider:

How do I use window functions ("over") together with crosstab in Postgres?
Ignoring timezones altogether in Rails and PostgreSQL


SSIS SQL Server Destination Good or Bad? Do people still recommend NOT using the SQL Server Destination? I know there have been questions about this in the past, but they seem to be from 4+ years ago. I have been timing OLEDB versus SQL Server Destination for ~5million+ records, the time savings are definitely enough where I need to consider it. The source/target databases and ssis packages are located on the same server.

The source/target databases and ssis packages are located on the same server.

Since you have above, you can use SQL Server destination. From Data loading performance guide :

The SQL Server destination is the fastest way to bulk load data from an Integration Services data flow to SQL Server. This destination supports all the bulk load options of SQL Server – except ROWS_PER_BATCH.
Be aware that this destination requires shared memory connections to SQL Server. This means that it can only be used when Integration Services is running on the same physical computer as SQL Server.

Internally, SQL Server destination uses Bulk Insert .. uses Shared Memory which is extremely fast (you can confirm running a trace to see what it is executing behind the hood).


How does date math work in SQL Server? I often see queries written with DATEADD and DATEDIFF in the WHERE clause to define a range, flatten datetime to 0 hours, or find the last day of a month or year, but I don't understand how all the parts work.

For example, this will find dates from the start of the current day, to the start of the the day 30 days ago.

SELECT *
FROM tbl
WHERE datecol >= DATEADD(DAY, DATEDIFF(DAY, 0, GETUTCDATE()), 0)
AND datecol   <  DATEADD(DAY, DATEDIFF(DAY, 0, GETUTCDATE()), -30);

What do all the different parts of this accomplish?

The best way to understand how date math works is to break a query down into its parts.

Start with this:

SELECT GETUTCDATE() AS utc_date

On this particular day, it returns 2017-10-04 19:34:20.050.

In date math, the number 0 and the date 1900-01-01 are interchangeable.

   SELECT DATEDIFF(DAY, 0, GETUTCDATE()) AS [what does zero mean?],
          DATEDIFF(DAY, '19000101', GETUTCDATE()) AS [it means 1900-01-01]

Which means that you can convert nearly any number to a date. Even negative numbers.

   SELECT CONVERT(DATETIME, -1) AS [how is -1 a date?]

This will bring back 1899-12-31 00:00:00.000, which is the same thing as this:

SELECT DATEADD(DAY, -1, '19000101') AS [zero minus 1 days]

Just subtracting one day from 1900-01-01. Weird, right?

How does this help us?

Let's look inside our original query:

SELECT DATEDIFF(DAY, 0, GETUTCDATE())

That gives us the number of days between 1900-01-01 and the current date. Which means the full expression:

SELECT DATEADD(DAY, DATEDIFF(DAY, 0, GETUTCDATE()), 0)

Is the adding the number of days between 1900-01-01 and current to 1900-01-01. That gives us the very start of the current day, at 0 hours.

The second part does the same thing, except subtracting 30 days from 1900-01-01, at 0 hours.

DATEADD(DAY, DATEDIFF(DAY, 0, GETUTCDATE()), -30);

If this is all too much to remember, I totally understand.

For a cheat sheet of important date calculations, see Tim Ford's article.

For a calendar table of important dates, see Aaron Bertrand's article.


SQLite how to store translations Im looking for advise because my experience is not really good in SQLite.

My table look like this:

CREATE TABLE IF NOT EXISTS Route (
  id integer PRIMARY KEY UNIQUE AUTOINCREMENT NOT NULL,
  name text NOT NULL,
  localizedName blob NOT NULL
);

Im storing the localized name of the route as a binary blob in the localizedName property(it can hold multiple strings like English, French etc...). then to retrieve it I use a special SQL function

SELECT * FROM Route WHERE tkLocalizedString('en', localizedName) LIKE ?1;

  tkLocalizedString is a function written in C programming language and is added to SQLite at runtime and it accept 2 parameters, the first is the language code (ISO 639-2), the second is the localized string blob

  localizedName is a binary string table. The key is the language id and the value is the string.

Is this a good practice? Is there a better approach?

I ended up using a separate table to hold translation because maintaining the custom functions and translation blob is a non necessary complexity and is not a portable solution.

CREATE TABLE IF NOT EXISTS Route (
  id integer PRIMARY KEY UNIQUE AUTOINCREMENT NOT NULL,
  name text NOT NULL,
  nameLocalizationId INTEGER REFERENCES Localization(id)
);

The localization table have 3 columns:

CREATE TABLE IF NOT EXISTS Localization (
    id INTEGER NOT NULL,
    language TEXT NOT NULL,
    text TEXT NOT NULL
);

id is the translations id (its possible to have multiple rows with same id),
language is the BCP 47 language tag,
text is the localized string.


Log sizes unexpectedly large: log_reuse_wait_desc == "log_backup" I've been reading posts and manuals all day, I need help.

My problem: transaction log sizes after data and transaction log backups will not shrink, and grew to be unexpectedly large.  The system is quiet; no programs/utilities updating the database.

What I expect to see is that after database file and transaction log backup, is to be able to shrink the log, that transaction log sizes decrease, and the log_reuse_wait_desc in sys.database, changes to == '0'

My environment:

SQL Server 2012 (SP1) - 11.0.3321.0 (X64), build 9200.
Configured w/high-availability mode; recovery set to full.
Database file size, 16Gb, Compressed backup 13GB.
3x log files: 20MB, 10GB, 104GB  (they all grew to 104GB, but I was able to shrink the first two)
Backups complete, no update jobs are running now … DBCC SQLPERF(logspace) == 112GB log size, 64% used
…and, ? Select from sys.database log_reuse_wait_desc == "log_backup"

I update the database with Sql stored procs, some integrated-services truncate/load jobs, and a few C# linq-to-sql programs.  No transactions…

Any expertise and guidance would be appreciated.  Thank you.

Few suggestions from my end, Kindly check and let us know

Run SQL Profiler and check for any unusual activities.
Check if the increase in size of the log files happens regulary at or around the same time (If yes, growht in log file might be the result of scheduled job)
Run DBCC LOGINFO  and check if the VLF's are active or inactive.
I randomly GUESS the result of the log growth is due to index maintenance activities you might have scheduled.
Lastly, Just for information: You need to change the database recovery model to SIMPLE and then perform the  DBCC SHRINKFILE  command.


How to retrieve top 3 highest and 3 lowest values in SQL? I created a table as shown below,
CREATE TABLE SALES
(
       SALE_ID          INTEGER,
       PRODUCT_ID       INTEGER,
       YEAR             INTEGER,
       QUANTITY         INTEGER,
       PRICE                INTEGER
);

Now i want query which retrieves three highest and three lowest price products in the sales table. I am new in SQL please help. I tried a lot of queries but nothing happen. below is my query.
SELECT TOP 3 PRICE FROM SALES
Union
SELECT PRICE FROM SALES order by PRICE;

You can also use something like this (see the fiddle here) - it's perhaps more flexible than the other answer - in any case, you should look at learning window functions in SQL - they can be very powerful.
First, table and data:
CREATE TABLE sale
(
  SALE_ID          VARCHAR (10),
  PRODUCT_ID       INTEGER,
  YEAR             INTEGER,
  QUANTITY         INTEGER,
  PRICE                INTEGER
);

INSERT INTO sale (sale_id, price) VALUES
('Item_1', 1), ('Item_2', 2), ('Item_3', 3), ('Item_4', 4), ('Item_5', 5), ('Item_6', 6),
('Item_7', 7), ('Item_8', 8), ('Item_9', 9), ('Item_10', 10), ('Item_11', 11),
('Item_12', 12), ('Item_13', 13), ('Item_14', 14);

Run this SQL:
SELECT
  t.sale_id,
  t.price,
  CASE
    WHEN t.rn_up <= 3 THEN 'Low priced'
    WHEN t.rn_down <= 3 THEN 'High priced'
  END as price_range
FROM
(
  SELECT
    s.sale_id,
    s.price,
    ROW_NUMBER() OVER (ORDER BY s.price ASC) AS rn_up,
    ROW_NUMBER() OVER (ORDER BY s.price DESC) AS rn_down
  FROM sale s
) AS t
WHERE t.rn_up <= 3 OR t.rn_down <= 3
ORDER BY t.price ASC;

Result:
sale_id price     price_range
Item_1      1      Low priced
Item_2      2      Low priced
Item_3      3      Low priced
Item_12    12     High priced
Item_13    13     High priced
Item_14    14     High priced
6 rows

If you want to deal with ties - I've added a record:
INSERT INTO sale (sale_id, price) VALUES  ('Item_43', 13);

And now, the results are (more or less) arbitrary - I run the same query and I get the same results - I reckon it may be the INSERTion order of records that have a price of 13 (see the fiddle).
What you can do then is use DENSE_RANK() as follows:
SELECT
  t.sale_id,
  t.price,
  CASE
    WHEN t.rn_up <= 3 THEN 'Low priced'
    WHEN t.rn_down <= 3 THEN 'High priced'
  END as price_range
FROM
(
  SELECT
    s.sale_id,
    s.price,
    DENSE_RANK() OVER (ORDER BY s.price ASC) AS rn_up,
    DENSE_RANK() OVER (ORDER BY s.price DESC) AS rn_down
  FROM sale s
) AS t
WHERE t.rn_up <= 3 OR t.rn_down <= 3
ORDER BY t.price ASC, sale_id;

Result:
sale_id price   price_range
 Item_1     1   Low priced
 Item_2     2   Low priced
 Item_3     3   Low priced
Item_12    12   High priced
Item_13    13   High priced    --  <<=== Note: now we have 2 items with price 13!
Item_43    13   High priced
Item_14    14   High priced

As mentioned in comments, what exact SQL statement you'll use will depend on your  requirements  - I would just urge you to spend some time studying window functions - they'll repay that time ten times over!


MongoDb use indexes partially Can mongodb take advantage of an index partially? I'm not talking about using a "partial index" ( https://docs.mongodb.com/manual/core/index-partial/ ), but using a regular index partially.

For example, let's say I create this index that has two fields:

    eventLog = database.getCollection("event_log", EventItem.class);
    eventLog.createIndex(Indexes.ascending("userId", "eventDate"));

What happens if I try to find elements for a given userId ONLY e.g.:

find({userId:23423})

Will mongodb take advantage of the index that begins with the userId field? Or do I have to create another index to speed up the query, e.g.

    eventLog.createIndex(Indexes.ascending("userId"));

MongoDB can take advantage of a compound index when you query for the leading column(s), so in your case the index on userId and eventDate can be used for a query on userId. You don't need a separate index.


SQL Server Client configuration via file Is it possible to configure a SQL Server CLIENT (cliconfg) via file? I'm looking forward to something like tnsnames.ora in Oracle world.

The data about connection aliases is stored in the Registry, so either a .reg file or a script that updates registry would do. Updating the Registry can be done with reg.exe or Powershell.

The tricky parts are cliconfg syntax and the fact that 32/64 bit registry keys are not the same. That is,

HKLM\SOFTWARE\Microsoft\MSSQLServer\Client
HKLM\SOFTWARE\WOW6432Node\Microsoft\MSSQLServer\Client

Take a look at this article about 32/64 bit versions.


joins with "could be blank" conditions Question is most  probably stupid but I am stuck on this and cant think anymore(Pissed Off).

I have these tables->  employee(has a column foreign to location),location and location_flag. And I need to select employee that are related to locations that have some specific flags on (like provides holidays, provides healthcare).

Suppose, a location(1) provides healthcare and holidays(having 'YES' for location_flagS) then I can simply check if they are 'YES'. So far so good?

Here's the complication:

What to do if the location have no track for some flags, Then joins will be failed.

My query will be:

select *
from employee
inner join location
  on employee.locationId=location.id
inner join location_flag lf
  on lf.locationId=location.id
inner join location_flag lf2
  on lf2.location_id=location.id
where lf.value='YES' and lf.flag_id=1
  and lf2.value='YES'and lf2.flag_id=2

If one of the flags are simply don't exists, the result will be NULL.

What I want to is to give me the employees of the location that if have flags must be 'YES'. How can I do this?

Try this:

select emp.*, loc.*
from employee as emp
  inner join location as loc
    on emp.locationId = loc.id
where not exists
      ( select *
        from location_flag as lf1
        where lf1.locationId = loc.id
          and lf1.flag_id = 1
          and lf1.value <> 'YES'
      )
  and not exists
      ( select *
        from location_flag as lf2
        where lf2.locationId = loc.id
          and lf2.flag_id = 2
          and lf2.value <> 'YES'
      ) ;


Setting up replication for various mongodb micro instances We currently have 5 mongodb micro instances on aws, each of them serve different modules and the calls are made through REST services.
We are planning to setup replication for these instances and my question would be, is it possible to setup replication for all these instances in a single instance?
The data is pretty small in these instances, so i do not want to have primary and secondary for each one them.

Thanks in advance.

You have 5 servers serv1,serv2,serv3,serv4,serv5 and you want to set up 5 instanses inst1,inst2,inst3,inst4,inst5. Each instance should have at least 2 data nodes (PRIMARY and SECONDARY) and 1 ARBITER

serv1: inst1_PRI, inst5_SEC, inst4_ARB

serv2: inst2_PRI, inst1_SEC, inst5_ARB

serv3: inst3_PRI, inst2_SEC, inst1_ARB

serv4: inst4_PRI, inst3_SEC, inst2_ARB

serv5: inst5_PRI, inst4_SEC, inst3_ARB

This architecture provide fault tolerance of 1. If one server goes down all instances are continue to work. If you loose 2 servers one instance should be affected ...


MySQL reads Date columns in Excel as DateTime? I'm trying to export Excel sheets to MySQL as new table using the plugin.

The export fails on "MySQL error 1064" when I tried to change the data type to Date.

I have a few columns in Excel that are formatted as Short Date, although some of them are generated by functions like =TODAY(), some are numbers like "42445", etc.

But all of them date type not Date Time.

For example it bothers me that MySQL detects '1/1/2016' in Excel as '1/1/2016 12:00:00 AM' as VarChar(225). And whenever I changed the type to Date the error message mentioned above appears.

I did try to re-format the columns in spreadsheet to YYYY-MM-DD but MySQL always convert them to MM/DD/YYYY HH:MM:SS

Thanks in advance for any help.

MySQL for Excel 1.3.6

Office 2016 32-bit

edit: this looks more like the plugin's bug of this version
https://bugs.mysql.com/bug.php?id=80079

Kindly Note that MySQL stores date in YYYY-MM-DD format by default.

One way is to convert all dates into YYYY-MM-DD format so that it will be compatible with MYSQL.

We can format the date column in excel before exporting it to MYSQL by following the below steps:

Select the column which contains the date
Right click and select format cells
i. Choose date under category(on left-hand side)
ii. Then choose custom under category
iii. Under type insert the format --> YYYY-MM-DD
You will see all the dates in the columns get converted into the format --> YYYY-MM-DD

Then you can import the date into MYSQL safely.

Another way is to keep the column datatype as VARCHAR and use TO_DATE() function to parse the string data into DATE format.

Hope this solves your problem !!


When is the AS keyword required? In the following statement (PostgreSQL 11):

=> SELECT c cost FROM tt;
ERROR:  syntax error at or near "cost"
LINE 1: SELECT c cost FROM tt;

I get an error. Adding parenthesis around the field expression doesn't help (SELECT (c) cost FROM tt;). But adding the AS keyword fixes it.

=> SELECT c AS cost FROM tt;
 cost
------
    1
...

I realize that cost is a keyword, but my impression is that the AS keyword is optional.

From a language perspective, why is the AS keyword required (or helpful) here? Is the PostgreSQL behavior here standard or documented somewhere?

Are there any other cases when the AS keyword is required?

From the documentation:

  In the SQL standard, the optional key word AS can be omitted before an
  output column name whenever the new column name is a valid column name
  (that is, not the same as any reserved keyword). PostgreSQL is
  slightly more restrictive: AS is required if the new column name
  matches any keyword at all, reserved or not

cost is a keyword, so as is required.

Also:

  Recommended practice is to use AS or double-quote output column names,
  to prevent any possible conflict against future keyword additions.

... but my preference is to not quote & instead be consistent with using as.


Database Blocking, kill the session which is responsible after 5 minutes I have been struggling with database blocking at my database server from last so many months. Have tried to use many ways to capture them and also aware that who are leaders of blocking. This application uses typical primary key-foreign key relationship and all the tables are tightly coupled. Few tables have as many as 30 foreign keys and hence whenever there is an insert/update/delete, all of those underlying tables get blocked. I have communicated this to vendor and they also have tried to remove PK-FK for possible tables and deployed in the test environment however its taking ages to go to production as lot needs to be tested and its failing in some or other scenario.

Since, I am supporting vendor based application and any change needs to be tested by multiple stakeholders, I don't know when will it really go to production. Blocking becomes so severe at time that whole portal goes down and I am left with no option but to kill the blocking SPID.

I am using below query to check the blocking by using sp_whoisactive procedure Mr. Adam Machanic:

EXEC sp_WhoIsActive
    @find_block_leaders = 1,
    @sort_order = '[blocked_session_count] DESC',

So far, I was working on reactive approach meaning, I was killing SPID who are responsible for blocking however this doesn't seem to go well and sometime blocking escalates and I have to run like fire-fighter. I would like to switch my side from reactive to proactive and planning to put a procedure in place which will take input as SPID from above procedure, check the duration and if blocking is more than 5 minutes, kill it.

At times, I have seen there are many blockers responsible for blocking as shown below:

Don't really know how will these kind of blocking be handled. Read something similar by Mr. Jason Hall at this link and second one at this forum however I am looking for something having base of sp_whoisactive.

This is to add very pertinent point about database isolation that I have changed database isolation level from default to "Read Committed Snapshot". On the very same topic, I had asked this question when I joined this forum however it was closed as I was very new to this forum and was unclear on my question.

Any input/help on this will be appreciated.

Version: Microsoft SQL Server 2014 (SP3) (KB4022619) - 12.0.6024.0 (X64)   Sep  7 2018 01:37:51   Copyright (c) Microsoft Corporation  Enterprise Edition (64-bit) on Windows NT 6.3  (Build 14393: ) (Hypervisor)

You can use the SQL script I wrote up below to build a dynamic SQL string that will kill all blocking sessions that have been running for at least 5 minutes.
You'll want to create a SQL Agent job (SQL Agent Job Docs) that executes this SQL script. (You can schedule the SQL Agent job to run as often as every second if you want, but the Agent Job GUI only allows you to select a minimum of 1 minute when scheduling, so if you need greater frequency you have to script the job out.)
-- Variable to hold dynamic SQL of what blocking sessions will be killed
DECLARE @DynamicSQL AS NVARCHAR(MAX) = '';

-- CTE of blocking sessions
WITH CTE_BlockingSessions AS
(
    SELECT blocking_session_id
    FROM sys.dm_exec_requests
    WHERE blocking_session_id <> 0
)

-- Sets the @DynamicSQL variable = 'KILL ' + all blocking session IDs that have been running for at least 5 minutes
SELECT @DynamicSQL = @DynamicSQL + 'KILL ' + CAST(BS.blocking_session_id AS VARCHAR(10)) + ';'
FROM sys.dm_exec_requests AS ER
INNER JOIN CTE_BlockingSessions AS BS
    ON ER.session_id = BS.blocking_session_id
WHERE DATEDIFF(s, ER.start_time, GETDATE()) >= 300 -- 300 seconds = 5 minutes
    AND ER.[status] = 'RUNNING' -- Ensures the blocking session is still active

-- PRINT @DynamicSQL -- For testing, outputs the dynamic SQL to SSMS Messages tab
EXEC sp_ExecuteSQL @DynamicSQL -- Executes the dynamic SQL