Paging through data that is frequently changing

You could use SQL Server's built-in row-versioning functionality to ensure you only see rows that haven't been touched since the start of your session.  This is test code, so we'll run it in tempdb:

USE tempdb;

IF OBJECT_ID(N'dbo.CatalogList', N'U') IS NOT NULL
DROP TABLE dbo.CatalogList;
CREATE TABLE dbo.CatalogList
(
    ListKey int NOT NULL
        IDENTITY(1,1)
        CONSTRAINT PK_CatalogList
        PRIMARY KEY CLUSTERED
    , rv rowversion NOT NULL /* rowversion is a binary(8) value */
    , ItemName varchar(30) NOT NULL
);

The table above is a very simplified product catalog, with a rowversion column named rv.  rowversion is a data type that exposes automatically generated, unique binary numbers within a database. rowversion is generally used as a mechanism for version-stamping table rows. The storage size is 8 bytes. The rowversion data type is just an incrementing number and does not preserve a date or a time.  See rowversion-transact-sql for more details.

The rv column can be used to eliminate newly added/updated rows from being seen my the client.  To show how that works, we'll add some initial data to the catalog list, and create a stored procedure to give us paginated results:

INSERT INTO dbo.CatalogList (ItemName)
VALUES ('Item1')
    , ('Item2');

GO
IF OBJECT_ID(N'dbo.GetCatalogItems', N'P') IS NOT NULL
DROP PROCEDURE dbo.GetCatalogItems;
GO
CREATE PROCEDURE dbo.GetCatalogItems
(
    @rowversion binary(8) = NULL
    , @PageNumber int = 1
    , @PageSize int = 100
)
AS
BEGIN
    SET NOCOUNT ON;
    IF @rowversion IS NULL SET @rowversion = @@DBTS;
    SELECT cl.*
    FROM dbo.CatalogList cl
    WHERE cl.rv <= @rowversion
    ORDER BY cl.ListKey
    OFFSET @PageSize * (@PageNumber -1) ROWS
    FETCH NEXT @PageSize ROWS ONLY
    RETURN @rowversion;
END
GO

Next, we'll run the stored procedure to show the first result:

DECLARE @rv binary(8);
EXEC @rv = dbo.GetCatalogItems @PageNumber = 1, @PageSize = 1;

@rv is used to save the current rowversion value, for use in subsequent calls.

The results:

╔═════════╦════════════════════╦══════════╗
║ ListKey ║ rv                 ║ ItemName ║
╠═════════╬════════════════════╬══════════╣
║ 1       ║ 0x00000000000007EE ║ Item1    ║
╚═════════╩════════════════════╩══════════╝

Now, we'll add another row to the table, and show the table contents:

INSERT INTO dbo.CatalogList (ItemName)
VALUES ('Item3');

SELECT *
FROM dbo.CatalogList;

Table contents:

╔═════════╦════════════════════╦══════════╗
║ ListKey ║ rv                 ║ ItemName ║
╠═════════╬════════════════════╬══════════╣
║ 1       ║ 0x00000000000007EE ║ Item1    ║
║ 2       ║ 0x00000000000007EF ║ Item2    ║
║ 3       ║ 0x00000000000007F0 ║ Item3    ║
╚═════════╩════════════════════╩══════════╝

Now, we'll re-run the stored procedure, passing in the @rv obtained by the initial call:

EXEC @rv = dbo.GetCatalogItems @rowversion = @rv, @PageNumber = 2, @PageSize = 1;

The results:

╔═════════╦════════════════════╦══════════╗
║ ListKey ║ rv                 ║ ItemName ║
╠═════════╬════════════════════╬══════════╣
║ 2       ║ 0x00000000000007EF ║ Item2    ║
╚═════════╩════════════════════╩══════════╝

And one more time, to see if we can see the third row:

EXEC @rv = dbo.GetCatalogItems @rowversion = @rv, @PageNumber = 3, @PageSize = 1;

The results:

╔═════════╦════════════════════╦══════════╗
║ ListKey ║ rv                 ║ ItemName ║
╠═════════╬════════════════════╬══════════╣
╚═════════╩════════════════════╩══════════╝

As you can see, nothing is returned by the third call, since we're limited to rows that existed at the start of the initial call to the stored procedure.

Using rowversion like this will have the sideffect of removing updated rows from the result set.  This might not be desirable, however if we replace the rowversion column with datetime columns to record creation and modification times, we can easily get around this limitation.

IF OBJECT_ID(N'dbo.CatalogList', N'U') IS NOT NULL
DROP TABLE dbo.CatalogList;
CREATE TABLE dbo.CatalogList
(
    ListKey int NOT NULL
        IDENTITY(1,1)
        CONSTRAINT PK_CatalogList
        PRIMARY KEY CLUSTERED
    , ItemName varchar(30) NOT NULL
    , CreateDate datetime NOT NULL
        CONSTRAINT DF_CatalogList_CreateDate
        DEFAULT (GETDATE())
    , UpdateDate datetime NOT NULL
        CONSTRAINT DF_CatalogList_UpdateDate
        DEFAULT (GETDATE())
);
GO

Here, I'll add a simple trigger to the table to ensure the UpdateDate column is always updated whenever any data in the table is updated:

CREATE TRIGGER dbo.CatalogListTrigger
ON dbo.CatalogList
AFTER UPDATE
AS
BEGIN
    ;WITH src AS (
        SELECT cl.*
        FROM dbo.CatalogList cl
            INNER JOIN inserted i ON cl.ListKey = i.ListKey
    )
    UPDATE src
    SET UpdateDate = GETDATE();
END
GO

Initial test data:

INSERT INTO dbo.CatalogList (ItemName)
VALUES ('Item1')
    , ('Item2');

The new stored procedure:

IF OBJECT_ID(N'dbo.GetCatalogItems', N'P') IS NOT NULL
DROP PROCEDURE dbo.GetCatalogItems;
GO
CREATE PROCEDURE dbo.GetCatalogItems
(
    @InitialDate datetime
    , @PageNumber int = 1
    , @PageSize int = 100
)
AS
BEGIN
    SET NOCOUNT ON;
    SELECT cl.*
    FROM dbo.CatalogList cl
    WHERE cl.CreateDate <= @InitialDate
    ORDER BY cl.ListKey
    OFFSET @PageSize * (@PageNumber -1) ROWS
    FETCH NEXT @PageSize ROWS ONLY
END
GO

The first run of the stored procedure:

DECLARE @d datetime = GETDATE();
EXEC dbo.GetCatalogItems @InitialDate = @d, @PageNumber = 1, @PageSize = 1;

╔═════════╦══════════╦═════════════════════════╦═════════════════════════╗
║ ListKey ║ ItemName ║ CreateDate              ║ UpdateDate              ║
╠═════════╬══════════╬═════════════════════════╬═════════════════════════╣
║ 1       ║ Item1    ║ 2017-07-27 14:31:35.317 ║ 2017-07-27 14:31:35.317 ║
╚═════════╩══════════╩═════════════════════════╩═════════════════════════╝

Inserting an additional row:

INSERT INTO dbo.CatalogList (ItemName)
VALUES ('Item3');

SELECT *
FROM dbo.CatalogList;

╔═════════╦══════════╦═════════════════════════╦═════════════════════════╗
║ ListKey ║ ItemName ║ CreateDate              ║ UpdateDate              ║
╠═════════╬══════════╬═════════════════════════╬═════════════════════════╣
║ 1       ║ Item1    ║ 2017-07-27 14:31:35.317 ║ 2017-07-27 14:31:35.317 ║
║ 2       ║ Item2    ║ 2017-07-27 14:31:35.317 ║ 2017-07-27 14:31:35.317 ║
║ 3       ║ Item3    ║ 2017-07-27 14:31:35.353 ║ 2017-07-27 14:31:35.353 ║
╚═════════╩══════════╩═════════════════════════╩═════════════════════════╝

EXEC dbo.GetCatalogItems @InitialDate = @d, @PageNumber = 2, @PageSize = 1;

╔═════════╦══════════╦═════════════════════════╦═════════════════════════╗
║ ListKey ║ ItemName ║ CreateDate              ║ UpdateDate              ║
╠═════════╬══════════╬═════════════════════════╬═════════════════════════╣
║ 2       ║ Item2    ║ 2017-07-27 14:31:35.317 ║ 2017-07-27 14:31:35.317 ║
╚═════════╩══════════╩═════════════════════════╩═════════════════════════╝

The extra row is not returned by the stored procedure:

EXEC dbo.GetCatalogItems @InitialDate = @d, @PageNumber = 3, @PageSize = 1;

╔═════════╦══════════╦═════════════════════════╦═════════════════════════╗
║ ListKey ║ ItemName ║ CreateDate              ║ UpdateDate              ║
╠═════════╬══════════╬═════════════════════════╬═════════════════════════╣
╚═════════╩══════════╩═════════════════════════╩═════════════════════════╝

We update Item1:

UPDATE dbo.CatalogList
SET ItemName = 'Updated Item1'
WHERE ListKey = 1;

And go back to page 1 in the results, we see the updated item:

EXEC dbo.GetCatalogItems @InitialDate = @d, @PageNumber = 1, @PageSize = 1;

╔═════════╦═══════════════╦═════════════════════════╦═════════════════════════╗
║ ListKey ║ ItemName      ║ CreateDate              ║ UpdateDate              ║
╠═════════╬═══════════════╬═════════════════════════╬═════════════════════════╣
║ 1       ║ Updated Item1 ║ 2017-07-27 14:31:35.317 ║ 2017-07-27 14:31:35.700 ║
╚═════════╩═══════════════╩═════════════════════════╩═════════════════════════╝

For a better fetch/offset solution, see this post by Aaron Bertrand


If the table is that simple (INT and TEXT), you cannot change the design any more.
Suppose your table is called mydb.myarticles. If you run this TableSize Query,
SELECT
    FORMAT(data_length,2)  / POWER(1024,1) DataPagesKB,
    FORMAT(index_length,2) / POWER(1024,1) IndexPagesKB,
    FORMAT(data_length,2)  / POWER(1024,2) DataPagesMB,
    FORMAT(index_length,2) / POWER(1024,2) IndexPagesMB,
    FORMAT(data_length,2)  / POWER(1024,3) DataPagesGB,
    FORMAT(index_length,2) / POWER(1024,3) IndexPagesGB,
    (data_length+index_length) DataAndIndexesInBytes
FROM information_schema.tables
WHERE table_schema='mydb' and table_name='myarticles'\G

You will see how much space the data pages are taking up. If you feel DataPagesGB is significantly lower than 98GB, then run:
OPTIMIZE TABLE mydb.myarticles;

which will actually execute
ALTER TABLE mydb.myarticles ENGINE=InnoDB;
ANALYZE TABLE mydb.myarticles;

This will shrink the table as much as possible. Then, run the TableSize Query again. Chances are, you may not get much shrinkage.
Keep this in mind: The default size of an InnoDB Page is 16KB (InnoDB_page_size). Any TEXT fields over 16KB will cause InnoDB to split the row up into two or more pages. In other words

Text field > 32 KB may need up to 3 pages for the one row
Text field > 48 KB may need up to 4 pages for the one row
...
Text field > 16n KB may need up to (n+1) pages for the one row

At least one of those pages will have lots of wasted space.
CAVEAT
I would strongly advise against changing the InnoDB_page_size for compression purposes because I wrote a post back on Mar 02, 2012 on how InnoDB creates additional wasted space in the Buffer Pool changing to the Barracuda Format, requiring a much larger Buffer Pool. This would be in addition to the chaining of the increased number of InnoDB pages.
Your Original Question

Can someone explain if such increase in disk space of a table comparing to a text file is normal ? Is there a way to store such content more efficiently ?

Yes, it is normal for such an increase especially if many rows have TEXT values bigger than 16KB. If you add any additional indexes (such a FULLTEXT index or a prefix index), you will bloat the Index Pages.
You can estimate the bookkeeping info by subtracting DataAndIndexesInBytes from the size of the .ibd and dividing that difference by 3 million. That answer will give you the bookkeeping info needed per row.
SUGGESTIONS

Use a different character set and collation (One byte per character)
Limit the characters of the TEXT fields to the most commonly used characters
Avoid using multibyte characters
Use TEXT field lengths less than 16KB


You need to ensure the LastName column is a Unicode type, for example nvarchar(30), and use the N prefix on any literal strings you use in insert/update/merge statements. (Alternatively, you could use a non-Unicode type with a code page that supports all the characters you need.)

SQL Server replaces characters it cannot convert with the ? symbol.

See nchar and nvarchar (Transact-SQL)

  Prefix Unicode character string constants with the letter N. Without the N prefix, the string is converted to the default code page of the database. This default code page may not recognize certain characters.

The code page is determined by the collation. Taking SQL_Latin1_General_CP1251_CI_AS as an example:

SELECT FH.[description]
FROM sys.fn_helpcollations() AS FH
WHERE FH.name = N'SQL_Latin1_General_CP1251_CI_AS';

Output:

Latin1-General, case-insensitive, accent-sensitive,
    kanatype-insensitive, width-insensitive for Unicode Data
SQL Server Sort Order 106 on Code Page 1251 for non-Unicode Data

Code page 1251 (used for non-Unicode data in that collation) does not have the æ character, so:

SELECT 'Gjøsæther' COLLATE SQL_Latin1_General_CP1251_CI_AS;

Produces:

Gjos?ther

Notice that as well as the ?, the ø character was translated to o.

The question does not state which collation the instance/database/column is using, but it seems very likely it is the wrong one for your data.

More general information:

Data Types (Transact-SQL)
Collation and Unicode Support


Can sp_executesql be configured / used by default?

The reason the SQL statements are getting wrapped with sp_executesql is the setting of the SqlCommand.Commandtype property and passing any Parameters to the command.

SqlCommand cmd = new SqlCommand("proc1", con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@param1", 1);
con.Open();
cmd.ExecuteNonQuery();
con.Close();

The code above ends with this T-SQL:

exec proc1 @param1=1

SqlCommand cmd = new SqlCommand("proc1", con);
cmd.CommandType = CommandType.Text;
cmd.Parameters.AddWithValue("@param1", 1);
con.Open();
cmd.ExecuteNonQuery();
con.Close();

This code ends with execution of the following T-SQL:

exec sp_executesql N'proc1',N'@param1 int',@param1=1

Addition 23.12.15: Using a CommandType.Text command, the results are similar: As soon a parameter is added to the command object, .NET will  wrap the whole query into sp_executesql and pass the parameters to it.

Addition: After diving deeper into sp_executesql, parameter sniffing and plan caching this behavior of the .NET classes totally makes sense in order to avoid high frequent query compilation and number of plans. So it's basically designed to ensure a better SQL Server performance in general while it at the same time could lead to poor performance of some queries (parameter sniffing issue) that are used with different parameter values than the initial created query plan.

See:

https://blogs.technet.microsoft.com/mdegre/2011/11/06/what-is-parameter-sniffing/
http://sqlmag.com/database-performance-tuning/don-t-fear-dynamic-sql

http://www.sqlskills.com/blogs/kimberly/exec-and-sp_executesql-how-are-they-different

http://www.brentozar.com/archive/2013/06/optimize-for-unknown-sql-server-parameter-sniffing/
https://sqlblog.org/2011/09/17/bad-habits-to-kick-using-exec-instead-of-sp_executesql

The above sample were created using .NET Framework 4.5 and SQL Server 2008 Developer Edition.


How to insert records using select statement?

WITH temp_mytbl AS ( -- without aggregation in output expression / HAVING clause
                     -- GROUP BY must be replaced with DISTINCT
                     -- which is less expensive
                     SELECT DISTINCT iex_id, dte, agent_name, schd_total
                     FROM source_xrx_iex6_sandy_1.iex6_agent_adh_sum
                   )
SELECT i.dte, idm.empl_id, 1 AS entity_id, SUM(i.schd_total) AS s_schdhours
FROM temp_mytbl i
JOIN source_odw_usac.t_incontact_agentstatelog a ON a.winid = i.iex_id::text
JOIN reporting_prod.idm_dte idm ON idm.src_id = a.agent_no
                               AND idm.src_type_id = 618
                               AND soar_date(a.calendar_date_key) >= idm.start_dte
                               AND soar_date(a.calendar_date_key) <= COALESCE(idm.end_dte, now()::date)
GROUP BY i.dte, idm.empl_id /* , entity_id */
-- LIMIT without ORDER BY makes no sense
ORDER BY i.dte /* or another sorting expression */
LIMIT 100;

or the same in subquery form
SELECT i.dte, idm.empl_id, 1 AS entity_id, SUM(i.schd_total) AS s_schdhours
FROM ( SELECT DISTINCT iex_id, dte, agent_name, schd_total
       FROM source_xrx_iex6_sandy_1.iex6_agent_adh_sum
     ) i
JOIN source_odw_usac.t_incontact_agentstatelog a ON a.winid = i.iex_id::text
JOIN reporting_prod.idm_dte idm ON idm.src_id = a.agent_no
                               AND idm.src_type_id = 618
                               AND soar_date(a.calendar_date_key) >= idm.start_dte
                               AND soar_date(a.calendar_date_key) <= COALESCE(idm.end_dte, now()::date)
GROUP BY i.dte, idm.empl_id
ORDER BY i.dte
LIMIT 100;


Permission Denied on shared object (.so) file postgresql

Permissions on the path and file should be postgres:postgres.

ls -l /home/pedro/.../.../example.so
chown -R postgres: /home/pedro/.../.../

You will probably need sudo for changing the file ownership.


Execute job every time database changes - execute job on each change-event

In sql server  database level triggers comes in handy like this situation ,  you can call job to start and leave   inside the trigger like

CREATE TRIGGER dbo.TableName_IUD
ON dbo.TableName
AFTER INSERT, UPDATE, DELETE
AS
BEGIN
    SET NOCOUNT ON;
    EXEC dbo.sp_start_job N'jobname'
END


Create dump file using Oracle SQL Developer

As far as I know, Import/Export and Data Pump are command-line-only types of tools.  But if you really wanted to use that functionality from within SQL Developer (or SQL Plus), Data Pump uses the built-in DBMS_DATAPUMP and DBMS_METADATA packages.  You should be able to call those packages directly, so I would start there.  There's documentation available in the
Oracle® Database PL/SQL Packages and Types Reference on both of them (DBMS_METADATA and DBMS_DATAPUMP).


How do I insert a value into table2 from a trigger on table1

inserted is a table. So try:

INSERT INTO FS_WHO (WHO)
SELECT DISTINCT i.WHO
FROM inserted AS i
WHERE NOT EXISTS (SELECT *
    FROM FS_WHO f
    WHERE f.WHO = i.WHO);

And you don't need to consider @Action for this...


SQL 2008 R2 Merge Replication - Clearing Skipped Schema Changes

Luckily the Network Admin found some space for a couple of VM to test the solution. I tested the solution and it worked perfectly.


Performance hit of CONVERT from nvarchar to money (SQL Server)

Performance cost of cast is minor. Database cost is usually in IO and a cast seldom even shows on the radar.

But that is not the problem. The real problem is going to be data purity. If you allow arbitrary nvarchar then rest assured, non-numeric values will show up in the database. Despite every precaution you take in the app, experience tells us that down the road somewhere somehow will insert 'abagft' as the value. And then the casts will start throwing runtime exceptions and you (or whoever will be running the show then) will curse and curse and curse. And you'll try to 'fix' it with a boolean shortcircuit WHERE, just like everybody else, and it won't work. This road is paved with bodies, trust me.

There are also problems related to data type precedence, type inference in dev tools (fields ends up as C# string) and, most importantly, index seek sargability problems.

Use a numeric type for numeric values. DECIMAL is good for moneys. And the time for change is always before you go live with millions of values. Right now is the cost of code change and risk of regression. In 6 months will be the same cost of code change and risk of regression plus downtime and size-of-data update operation.


Optimize Query: Group By while tracking Count

Add to box:  INDEX(userId, createdOn).

Then provide EXPLAIN SELECT ... if that is not sufficient.


Set value of character_set_client to utf8mb4

Disclosure: I’m the author of How to support full Unicode in MySQL databases, the guide you’re following.

Where did you save the modified settings? Check where mysqld loads the default options from. It’s usually /etc/my.cnf (as mentioned in the guide) but it may be different on your system. Run the following command to find out:

$ mysqld --help --verbose 2> /dev/null | grep -A1 'Default options'
Default options are read from the following files in the given order:
/etc/my.cnf /etc/mysql/my.cnf /usr/local/etc/my.cnf ~/.my.cnf

Did you restart mysqld by running mysqld restart after making the changes?


Have you tried using dynamic sql?  Something like this:

declare @fieldLogID nvarchar(256) = ?
declare @accountTypes int = ?
declare @sql nvarchar(1000) = ''

SET @sql = 'select fl.[Version]
           ,fl.ObjectID
           ,[FieldLogID]
           ,Cast(fl.WorkStartDateTime as date) as WorkStartDate
           ,Cast(fl.WorkStartDateTime as time) as WorkStartTime...
           ...
           where fleja.FieldLog_FieldLogID IN (' + @fieldLogID + ')'
EXEC (@sql)

The syntax may be a little off and the @sql nvarchar variable type would probably need to be expanded more.   The format for @fieldLogID would need to be something like this though:

@fieldLogID = n'''1800'',''1900'',''2000'''

Instead of executing the @sql variable, you can always print it to the screen to verify your syntax.  Using this approach I would be a little worried about the @fieldLogID variable and I would like to validate that it is correct and nothing malicious is going in there.


1) SET DATEFORMAT has no effect on the actual storage of the table. It is only used to format output and interpret strings. Behind the scenes, all the date formats are stored as integers in a canonical format.

The actual representation of the data type depends on which type is used. For example, SMALLDATETIME is an integer that stores the number of seconds since 1900-01-01. Some of this bubbles to the surface when you cast like this:

SELECT CAST(0 AS SMALLDATETIME)

Result: 1900-01-01 00:00:00

Similarly, for DATETIME:

SELECT CAST(0 AS DATETIME)

Result: 1900-01-01 00:00:00.000

Interestingly, this fails:

SELECT CAST(0 AS DATE)

With exception: Explicit conversion from data type int to date is not allowed.

As should be obvious, something isn't quite fleshed out with the new types.

2) This limitation is documented here: http://msdn.microsoft.com/en-gb/library/ms189491.aspx

  The DATEFORMAT ydm is not supported for date, datetime2 and
  datetimeoffset data types.

Why is this so? Inside the database engine DATE, DATETIME2 and DATETIMEOFFSET take different code paths than DATETIME and SMALLDATETIME (you can validate this with a profiler). Because the new date formats arrived in the SQL Server code base long after DATETIME/SMALLDATETIME - different programmers worked on it and hence, some functionality is not 100% aligned (yet). Another example: In early versions of SQL Server 2008R2, bulk loading a DATE was much slower than bulk loading a SMALLDATETIME, even though one is smaller than the other. This was fixed before release. It's the kind of thing that happens in a big organisation with lots of coders.

3) This means that it is not possible for you to know if the date format is valid until you actually try it. In your case, it means that even if you do an "show query plan" without execution, you will not know that there is an error in the format until you actually execute the query.


Slow running aggregate of aggregate query

As you are repeating this query for multiple months then you will be continually re-aggregating the same rows.

For example the rows in the first month will always be brought back by the t1.post_date < @report_date criteria so will be re-processed for every month.

To avoid this I'd probably consider working through it in an iterative way a month at a time from the start. Dependent on the volatility of historic data I might also consider storing the pre-calculated results in the database rather than re-calculating these each month.

To calculate this at run time you could create a temporary table with the following structure.

CREATE TABLE #balance
  (
     department_id   INT NOT NULL,
     location_id     INT NOT NULL,
     account_id      INT NOT NULL,
     balance_to_date MONEY NOT NULL,
     PRIMARY KEY (department_id, location_id, account_id)
  );

You could also consider adding the following index on your transactions table

ALTER TABLE transactions
  ADD post_date_year_month AS (10000 * YEAR(post_date) + MONTH(post_date))

CREATE INDEX ix
  ON transactions(post_date_year_month, department_id, location_id, account_id)
  INCLUDE (amount)

Then extract a month at a time from transactions and merge into #balance (with a when matched then increment, when not matched insert).

The leading post_date_year_month column means that as long as you write the query sargably the extraction of each month can be done efficiently and the extracted rows for a month will be ordered by department_id, location_id, account_id making a merge join against #balance possible without a sort.

Whilst that could benefit this particular query you'd need to assess the utility of this index against your overall workload.

Then calculate the department_id, location_id totals from #balance (can leverage the PK order to avoid a sort) and store those somewhere and move onto the next month.

(Or possibly instead of #balance you could use a "temporary" permanent table balance and create an indexed view on that to avoid the separate explicit aggregation step and just copy the values straight from that before moving on)


Considering Id is the PK, does it make any sense to have it as the third column in Index3, before QuxId?

It depends, and the reason behind that is because the nonclustered index leaf pages contain the clustered index key (Id).  Take a look at the following example:

use TestDB;
go

create table FooBar
(
    Id int identity(1, 1) not null
        constraint PK_FooBar_Id primary key clustered,
    FooId int not null,
    BarId int not null,
    QuxId int not null
);
go

create index Index1
on FooBar(FooId);
go

create index Index2
on FooBar(FooId, BarId);
go

create index Index3
on FooBar(FooId, BarId, QuxId);
go

insert into FooBar(FooId, BarId, QuxId)
values
    (1, 2, 3),
    (2, 3, 4),
    (4, 5, 6),
    (5, 6, 7),
    (6, 7, 8),
    (7, 8, 9);
go

select
    Id,
    FooId,
    BarId,
    QuxId
from FooBar
where FooId = 4;

By capturing the execution plan for the last query (the SELECT), you can see that it is an index seek on [FooBar].[Index3], and notice my CREATE INDEX doesn't include the Id column.

Martin brings up a good point in his comment (see below for quote):

  But specifying Id in a particular position in the index rather than just accepting the defaults can avoid a sort if the requirement is to ORDER BY that column order. Also allows a straight forward lookup on FooId, BarId, Id which an index on FooId, BarId, QuxId, Id wouldn't do (whether or not Id is included in the NCI key or leaf depends if the index is declared as unique or not)

Here is what he's talking about.  With a query like this (forced index table hint to show the Sort operation):

select
    Id,
    FooId,
    BarId,
    QuxId
from FooBar with (index(Index3))
where FooId = 4
order by Id;

This would generate the below plan:

But by changing the structure of the index, like so:

create index Index3
on FooBar(FooId, Id, BarId, QuxId)
with (drop_existing = on);
go

And re-executing the above query:

select
    Id,
    FooId,
    BarId,
    QuxId
from FooBar -- with (index(Index3))
where FooId = 4
order by Id;

You now don't have the expensive Sort operation:

Now take a look at what happens if your query does in fact look like the following (again, table hint there just to force Index3):

select
    Id,
    FooId,
    BarId,
    QuxId
from FooBar with(index(Index3))
where FooId = 4
and BarId = 5
order by Id;

You will again have that Sort operation.  Now if your index looked like the following:

create index Index3
on FooBar(FooId, BarId, Id, QuxId)
with(drop_existing = on);
go

By having Id as the third key column in the index structure, you will see the benefit of having it already ordered by Id when the Seek Predicates are on FooId and BarId.  Here is proof with that altered index:

To get a visual of that, let's look at the leaf page of Index3.  You can do this with the following:

dbcc ind('TestDB', 'FooBar', -1);
go
-- Index3 (index_id = 4) leaf page PID = 288
dbcc traceon(3604);
go
dbcc page('TestDB', 1, 288, 3);
go

You will get similar output like the following:


Are you aware of the "what3words" system??  https://en.wikipedia.org/wiki/What3words

Or, how to address mail from the U.S. to anywhere in the world  - a problem similar to yours - https://blink.ucsd.edu/facilities/services/mail/international/addressing/index.html

I've seen geographical data represented with latitude/longitude or similar coordinates, plus a code representing how specific or accurate the coordinates are.

You could use coordinates together with a textual address.  The text is searchable but the coordinates -are-specific.

I think the proper way at the moment to store arbitrary text with markups for field identities - name of country / county / town etc. - is XML.  A similar effort of your own would be just re-inventing XML.

How does the Smithsonian do it?  Or is that where you're doing this?


Something tells me that when you run SHOW SLAVE STATUS\G you get

Slave_IO_Running=No
Slave_SQL_Running=Yes

It is a connectivity issue back to the master. This is a not connectivty due to the network but with the binary logging mechanism of the master. This is definitely the case because, the error message (1236) is complaining about the first entry of the master's list of binary logs.

When you had setup the line

[mysqld]
log-bin=mysql-bin

This would naturally create a file called mysql-bin.index, a files that would contain the list of all binary logs that starts with mysql-bin.

Just run this on the master:

SHOW BINARY LOGS;

and you will see the list of binary logs on the master. It shold look something like this:

+------------------+------------+
| mysql-bin.000001 | 1073741824 |
| mysql-bin.000002 | 1073741824 |
| mysql-bin.000003 | 1073741824 |
    .
    .
    .

There are a few scenarios that could have occurred:

Not every entry in the master's mysql-bin.index existing in the master's datadir
The master had a problem writing a binary log that the slave needs
One of the master's binary logs is either zero-length or corrupt

You may need to sync the data like this:

service mysql stop on the slave
Run RESET MASTER on the master to zap the master's binary logs
Perform rsync of data from master to slave multiple times until rsync < 1 min
service mysql stop on the master
Perform final rsync of data from master to slave
Find the master's log file and its filesize
service mysql start on the master
service mysql start --skip-slave-start on the slave
On the slave, perform change master to using master_log_file='mysql-bin.000001',master_log_pos=filesize of master's log fiie
Perform START SLAVE; on the slave

Give it a Try !!!

I answered another question back on Feb 23, 2011 about creating a slave from a running master : MySQL Replication - Introduce new Slave to replication


Distributed database for worldwide LIDAR point cloud

I have dealt with something very similar to this. This is quite a broad problem but I'll try to offer the pointers I can after a year (and then the rest) of dealing with massive geographic data sets.

I'm not saying "do these", I'm saying these ideas will be resourceful depending on your hardware situation and software needs. You clearly already have good ideas about distributing out the data, that's good.

Firstly, I'd make sure not to have monolithic tables with too many sets of datatypes. Normalise out - I've found that splitting your geospatial columns into an entirely separate table.

Secondly, write down the sort of queries you'd expect a typical client to need. There's a 'chicken and egg' situation here, but I always like to write the SQL then design the table and indexes around that. Rick James' blog (former DBA at Yahoo!) always has nuggets of wisdom on this.

Thirdly, shard by location, which you have already touched upon. You can try doing this by 5-10 degree boxes around the world by latitude and longitude, depending on how dense your data is. Or if your data density is not uniform around the world, try a more sophisticated approach based on densities.

Our approach to sharding involved creating multiple Amazon RDSs, and then having a meta data table that mapped a range to the correct database and table. This is a simpler approach if you have the cash - personally I was against this due to the spiralling costs, but the uptime of AWS is top-notch. Something for you to think about.

Good luck, sounds like a cool project!


Percona: Do I need apply-log switch for a weekly full backup and hourly incremental?

You do not need apply-log for the backup, but you need it for the restore. Depending if you perform differential or incremental backups, you will need to execute one apply-log for rolling forward the backup and application of the changes since the last full or incremental backup. --apply-log --redo-only --incremental-dir

Whether you perform that after the backup or before the restore is entirely up to you and your backup policy. Some people prefer to have a ready-to-copy-back rolling-forward full backup, so they execute the --apply-log just after the backup. Others do the whole process just in case of the restore is needed. Both approaches have different properties in mean time to recovery, detection of faulty backups, space needed, time to perform the backup, etc.

Think on your recovery policy (not your backup strategy) and make the tool suit to it, not the other way round.

More info: http://www.percona.com/doc/percona-xtrabackup/innobackupex/incremental_backups_innobackupex.html


How do I translate a Windows SID to an SQL Server server_user_sid?

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

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

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

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

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

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

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

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

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


What is the difference between CHECK and CONSTRAINT CHECK?

There is no difference in SQL Server between those two statements. Each results in NOT NULL column (not a constraint) with a single check constraint. The only difference is that second one creates a system-named check constraint, something like 'CK__t__ProjNum__4AB81AF0'.


How would you design a table for approval system between User and Sports Club

Add a self-reference to User. Call this reference "approved by". I'd suggest implementing this as a separate table but if you're absolutely sure you'll only ever need three approvers it can be colums in the user table.

As a current member approves an application count the number of approvals, compare it to the number of required approvals, and the number of current members and proceed accordingly. Remember to account for the case where the current applicant is the first to apply to a club.

I think it's an error to make a user exclusive to one club. Wouldn't it be good to know that the same person plays polo, frisbee, netball and Jai-Alai? It would be better to have an intersection table called Membership.


How comes that login with no rights cam give himself db_creator?

Is the other person a local admin on the server? You need to look at the simplest explanation, which is he either knows the SA password, or he reset the password to the SA account, which a local admin at the server level can do.

Dbatools even has a cmdlet for it. If neither of those things is true, then he knows your password. If you keep your passwords on the same instance in a  "password table" encrypted with the default encryption it is relatively easy to retrieve the clear text using any one of the 100s of programs that can do it. Apex makes one that you can download for free.

The simplest explanation isn't you have a magical SQL server that lets users with no rights grant themselves high-level rights. The simpler explanation is he figured out your password or the SA password somehow.


How to change "tmpdir" on a running MySQL instance

Seems like there is no easy and unobtrusive way to do it. However there is a way to increase the size of the tmpfs.

Change the size in /etc/fstab accordingly
Issue a mount -o remount and it will be resized without a need to release file handle locks.


How to fix SQL Server instances with different builds on same server?

Sounds like someone applied a security update, but only selected one of the instances to apply the update (or it was installed prior to the instance at 5500 was installed).

To fix it, you can run the update again (5512 is from a security update I blogged about here). It should give you the option of which instances to apply; check all that you can. The ones that are already at that patch level (or higher!) will be greyed out.

In fact, I recommend you apply the most recent cumulative update, Cumulative Update #15.


SQLite - get every row that has a common entry in the second column

String have to be in single quotes
Also i don't see ItemListName  be filled, i hoe there is Listname in it
See like:
String selectQuery = "SELECT  * FROM " + LIST_ITEM_TABLE + " WHERE "
        + ItemListName + " like  '" + listname +"'";


I can just give you rules of thumb, of course. But it may help you.

Disk space and setup

In order to be able to create backups in a proper way, you should have at least have a RAID10 (the suggested RAID level for MongoDB) with a payload of around 2.5TB. Let me explain why:

You don't want to have 100% utilization of your disk space, so we add a reasonable security margin of 20% to give you enough time to take measures.

( 1024GB payload * 100 / 80 ) = 1280GB with security margin

Then, you want to be able to create backups using LVM snapshots. You need to allocate some space for changes which occur during the lifetime of the snapshot. This is hard to calculate without information, but to be on the safe side, we assume it is about 50% during the time of the backup, so we add another 500GB. Then, you would need some space to compress the backup to. To be on the safe side again, we add another 500GB. Since you don't want a utilization of 100% of the backup space, too, the above calculation for the space needed for backup applies again! effectively doubling the space.

The closest would be a RAID 10 with 6 disk of 1TB each giving you a payload of 3TB. That should give you the best balance between redundancy, performance and required payload. Please use 15k SAS disks at least. SSDs give an performance boost of about an an order of magnitude right out of the box, but that does not seem to be an issue with your requirements.

CPU

Hard to say what computing power you need, but as a rule of thumb you should buy fewer, but more powerful cores.

RAM

That is very hard to say without knowing how many and which indices you have. But again a rule of thumb jumps in: With MongoDB, there is no such thing as too much RAM. With 1TB payload, MongoDB could utilize 1TB+ to keep not only the indices, but the data, too in in RAM for fast reads.

The amount of RAM you need is hard to calculate. I usually would roughly calculate 10% to be on the safe side for optimal performance, since additional RAM never hurts. Especially when using spinning disks instead of SSDs.

That would be 128GB in your case. But it may well be that you get along with just 64 or even 32GB. My suggestion would be to start with 32GB and upgrade if you see a lot of page faults in MMS.

Network bandwidth

Without knowing how much data is send per query, it is impossible to calculate this. If you plan to set up a replica set (which you almost always should), you should not go below 1Gb internal bandwidth on write heavy applications. If you really have a lot of load, you might even want to bond two interfaces.

Disclaimer: These are only rules of thumb, based on the very limited information of the original question. Depending on your application, system administration skills and environment, your actual needs may vary.


A DBaaS such as MongoDB Atlas is beneficial to the longterm growth of an application.  Running a database as a localhost server provides no redundancy and no capability of enduring failure.

A localhost database will eventually run out of local disk space, a
DBaaS should allow you to expand disk on the fly.
A localhost database will never simply notify you of issues, a DBaaS
should contact you about any pre-defined problems.
A localhost database can't back itself up, a DBaaS should provide
database backup options.
A localhost database will not know how to handle a large influx of
new reads, a DBaaS will allow you to dynamically scale your DB while
allowing your front end app and cache to handle incoming new traffic.

Network speeds in both traditional datacenter and "cloud" solutions such as Amazon Web Services can leverage low latency networks.  These low latency networks have the capabilities of providing high performance data access speeds to keep up with the most frequently accessed documents in a database such as MongoDB.

There's never a reason to install a database in production and leave it alone on your prod server.  Your database will likely not use the same best practices as you should configure when in production.  Based on these very basic examples, you can see why so many companies do not believe in using a standalone DB on localhost, they put trust in DBaaS services.


Import batches of rows from Excel in parallel

I would Export the Excel file to a Text file (e.g. tab-delimited) and load it into SSIS in that format.  It will be much quicker and less subject to odd errors which sometimes come up when reading Excel files.

I would expect any attempt at parallel Excel imports to be slower than a single import - Excel is not a server.


It's actually a very good question and can go a lot deeper but I'll give you the short version.
It all boils down to your scenario.

If your requirements demand this dataset to be always sorted in the same way, consider creating an index to tune the query.

If your requirements don't demand any kind of sorting, don't bother yourself (and the engine).

If your requirements ask for a dynamic sort, there are lots of techniques you can try, sometimes even a dynamic query can save the day here.

If there's a dynamic sort thing in your app and the dataset is the same, just upload the data and let the app worry about sorting, with the added bonus of not hitting the SQL Server again just to bring the same dataset in a different order.

If you are actually getting paginated data from the DB and just fetching one page at a time from it so you must sort it in the query, there's no magic here.


there's a space missing before to in 'to myuser'.

BEGIN
FOR X in (select * from all_tables where owner in ('TESTDTA', 'TESTCTL'))
LOOP
    EXECUTE IMMEDIATE 'GRANT SELECT, INSERT, UPDATE, DELETE ON '
        ||X.owner||'.'||X.table_name||
        ' to TESTUSER';
END LOOP;
END;
/


I would use explicit JOIN syntax, but reduce the noise:
SELECT *  -- really? *all* columns?
FROM   t1
JOIN   t2 ON t1.id = t2.col
JOIN   t3 ON t1.id = t3.col
JOIN   t4 ON t1.id = t4.col
-- ... more tables
WHERE ...

Place conditions linking two tables in the JOIN clause.
Place other conditions in the WHERE clause.
INNER is a noise word. Parentheses are not required around join conditions.
To answer your question:

Are implicit joins as efficient as explicit joins in Postgres?

Yes. There is no difference in efficiency. Postgres is typically free to rearrange the order of join operations and apply JOIN and WHERE conditions in any order it sees fit. The manual:

Explicit inner join syntax (INNER JOIN, CROSS JOIN, or unadorned JOIN)
is semantically the same as listing the input relations in FROM, so it
does not constrain the join order.

There are special considerations for OUTER joins that do not apply here.
And be aware of some limitations:

Explicit joins bind stronger than implicit joins. This can have side effects when mixing both - which you should avoid unless you understand the implications. See:

Why does this implicit join get planned differently than an explicit join?

What does [FROM x, y] mean in Postgres?

With more than join_collapse_limit items in the FROM list (default is currently 8), Postgres switches from an exhaustive search for the best query plan to generic methods. Hence the order of explicit join items becomes yet more important - or even instructive.

Since you mentioned:

+many more tables

... you probably need to consider how to optimize planning time and query execution time by carefully choosing the order of explicit joins and placement of conditions. You may want to use subqueries, CTEs, mix explicit and implicit joins, use parentheses among groups of FROM clause items or play with configuration settings to get best results.
Related:

Postgres JOIN conditions vs WHERE conditions
Complex view becomes slow when adding ORDER BY in outer query with small LIMIT


Select rows from two joined table that not in a third table

SELECT A.*, B.*
FROM A
JOIN B ON A.user_id = B.user_id
WHERE NOT EXISTS ( SELECT NULL
                   FROM C
                   WHERE C.user_id = B.user_id )

or

SELECT A.*, B.*
FROM A
JOIN B ON A.user_id = B.user_id
LEFT JOIN C ON C.user_id = B.user_id
WHERE C.user_id IS NULL


DR/Reporting solution for a large SQL Server database

If you don't need to query the server, the classic answer here is transaction log shipping: simply restoring your backups on a different SQL Server.

It's a great fit here because:

It's independent of your Always On Availability Group: even if the AG fails or the cluster fails, you can go live with this DR server quickly
It can't become the primary node until you manually run RESTORE WITH RECOVERY and point your clients over to it (can be tuned by using a DNS CNAME that normally points to your primary, but during disaster, you change it to point to the log shipped secondary)
It can stay reasonably in sync - you said within 24 hours, and that's fairly easy to do with log shipping

That meets all  of the requirements in your question.

Just be aware that there is a licensing gotcha: if you have Software Assurance, then for every one primary you buy, you get one free secondary as long as it's not queried. If you're using multiple secondaries (like one in your primary data center, plus adding this new one,) you may need to purchase additional licensing.

If you DO need to query the server while it continues to update the data underneath you, your only two answers are Always On Availability Groups or transactional replication. All other answers (SAN replication, log shipping, VM replication, etc) will require queries to stop each time the data is updated.


SELECT inside trigger gives wrong result (it ignores the WHERE clause)

I've just tested this with MariaDB 10.4.12 with innodb_version 10.4.12 on Fedora Linux, but I don't see the problem you have - I get an empty query3.csv file. The only difference I have (besides the Linux distro) is that I don't have your foreign key constraints since I don't have those tables.

Are you sure you're looking at the correct output file?

Note also that you will get a warning when you run the query or create a trigger with that query:

SHOW WARNINGS;
+---------+------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Level   | Code | Message                                                                                |
+---------+------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Warning | 1287 | '<select expression> INTO <destination>;' is deprecated and will be removed in a future release. Please use 'SELECT <select list> INTO <destination> FROM...' instead |
+---------+------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.000 sec)

So you could try moving the FROM and WHERE clauses to the end of the statement. I get the same result with this, just without the warning.

You can also try enumerating the columns in the SELECT instead of SELECT *, just to see if it changes anything.

This is my trigger:

CREATE TRIGGER trigger1 AFTER UPDATE ON stock_totals
  FOR EACH ROW
    SELECT * FROM `stock_totals`
    WHERE `product_id` = 2 AND `stock_place_id` = 3
    INTO OUTFILE '/tmp/query10.csv'
    FIELDS TERMINATED BY ','
    ENCLOSED BY '"'
    LINES TERMINATED BY '\n';


Here is the quick and dirty answer

Running SHOW TABLES; will go to the OS and check for the existence of .frm files only.  I mentioned this 5.5 years ago (MySQL tables do not show up in the phpMyAdmin and MySQLWorkbench)

In your case, there is a file called foo.frm. That file exists in the database folder of your database.

When you try doing things to the table at the storage engine level, that's when you get that message because there are no storage engine level files for the foo table ( no foo.ibd file if the storage engine was InnoDB or no foo.MYD/foo.MYI if the table was MyISAM).

My guess is that someone deleted the storage engine level files for the table but forgot to delete the corresponding .frm.

The solution would be to find foo.frm in the OS and delete it. The information_schema will quickly disavow any knowledge of the foo table.


Encountered unexpected growth Transaction log file size

How to reduce the size of transaction log file without shrink process (Because it’s Bad – Increases Fragmentation – Reduces Performance)

That is not true. log shrink is quite benign, you are thinking data shrinks. See How to shrink the SQL Server log for an explanation why it grows, how to shrink it and why is benign.

My first recommendation is to use a smart index maintenance plan. Overactive rebuild is expensive, harmfull and completely unnecessary. Many swear by Ola Hallengren's index maintenance scripts.

You must also look into leveraging minimal logging. Index maintenance are a prime candidate for minimally logged operations, but you must use enable them by using the bulk-logged recovery model for your database. See Operations That Can Be Minimally Logged:

  If the database is set to the simple or bulk-logged recovery model, some index DDL operations are minimally logged whether the operation is executed offline or online.


Is SQL Server on Mac equally well performing as it is on Windows?

You can install SQL Server on Mac using docker images. The procedure for that installation can be found here

However, a lot of features still aren't supported when not running on windows (even though Microsoft is working on adding some of them). If you don't need things like Distributed Transaction Coordinator or some of the unsupported features you should be OK.

SQL Server Management Studio will not run on Mac natively, maybe you might have some success using Parallels Desktop.

SQL Server operations studio  will run natively on a Mac.

Bob Ward has a good step by step guide on how to set everything up: Take the SQL Server Mac challenge

SQL Server Operations Studio is still in prerelease status so it's likely to have a lot more bugs than Management Studio and it still lacks a lot of features SSMS has.
If you look at their release notes you will see that the team releases often (sometimes more than one release per month) and you will also see some releases fix regressions in previous releases (which means they broke stuff in one release they had to fix in the next one). You will also note that they add features at a high pace, so trying to list the missing features now isn't going to help you since the list will change again in a few weeks.

If your goal is to start learning SQL Server and T-SQL however I think you should be fine running it on a Mac.


Red Gate comparison not working after upgrading SQL Server 2016

If this actually is SQL Compare 10.7 or earlier, I believe it may not work with SQL Server 2016.
https://documentation.red-gate.com/display/SC10/SQL+Compare+10.7+release+notes


Where can I find database tuning exercises for learning?

Paul Randal provides some sample DBs that are actually corrupt: http://www.sqlskills.com/BLOGS/PAUL/post/Conference-corruption-demo-scripts-and-example-corrupt-databases.aspx

Good exercise to go through working with DBCC commands and such on SQL Server.


Never mind. Found it.

sp_helptext 'TriggerName'

worked like a charm.


What we do to estimate the usage of tempdb and its size is to track its growth via data tracked in tables for a period of time before reboot happens as a part of maintenance activity.

P.S note: It may not be an alternative, but helps in tracking how tempdb grows over a period of time

Step 1 Create table to store the data:

CREATE TABLE [dbo].[TempDBFileSize] (
[TFSID] [int] IDENTITY (1, 1) NOT NULL ,
[FileID] [int] NULL ,
[File_Logical_Name] [varchar] (128) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[State_Desc] [varchar] (128) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Type_Desc] [varchar] (128) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Default_File_SizeMB] [int] NULL ,
[ActualKB] [int] NULL ,
[ActualMB] [int] NULL ,
[File_MaxSize] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[File_Growth] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Growth_Type] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Physical_File_Name] [varchar] (128) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[DateCaptured] [datetime] NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[TempDBFileSize] WITH NOCHECK ADD
CONSTRAINT [PK_TempDBFileSize] PRIMARY KEY CLUSTERED
(
[TFSID]
) ON [PRIMARY]
GO

Step 2 Create SP to catch the tempdb data and log file usage within above created table:

CREATE PROCEDURE dbo.spTempdbFileSize
AS
/*
------------------------------------------------------
-- Object Name: dbo.spTempdbFileSize
-- Project: SQL Server TempDB Sizing
-- Business Process: SQL Server Capacity Planning
-- Purpose: Capture the TempDB growth
-- Detailed Description: Capture the TempDB size, name, file, etc for the TempDB files
-- Database: TempDB
-- Dependent Objects: dbo.TempDBFileSize
-- Called By: Admin - TempDB Sizing
-- Upstream Systems: None
-- Downstream Systems: None
--
------------------------------------------------------
-- Rev | CMR | Date Modified | Developer | Change Summary
------------------------------------------------------
-- 001 | N\A | 12.02.2007 | Edgewood | Original code
-- 002 |     | 03.30.2009 | Penberth | Modified the code to get the actual size
--                                     of the tempdb alongside the the default sizes.
--                                     Added the ActualKB and ActualMB columns and
--                                     renamed the [File_SizeMB] to [Default_File_SizeMB]
--
*/
SET NOCOUNT ON
INSERT INTO dbo.TempDBFileSize (FileID, File_Logical_Name, State_Desc, Type_Desc,
[Default_File_SizeMB], ActualKB, ActualMB, File_MaxSize, File_Growth, Growth_Type,
Physical_File_Name, DateCaptured)
SELECT File_ID,
MasterTbl.[Name],
MasterTbl.State_Desc,
MasterTbl.Type_Desc,
(MasterTbl.[Size] * 8)/1024 AS 'File_SizeMB',
(TempTbl.[size]*8) AS ActualKB,
(TempTbl.[size]*8)/1024 as ActualMB,
File_MaxSize = CASE
WHEN MasterTbl.[Max_Size] = 0 THEN 'NoGrowth'
WHEN MasterTbl.[Growth] = 0 THEN 'NoGrowth'
WHEN MasterTbl.[Max_Size] = -1 THEN 'UnlimitedGrowth'
WHEN MasterTbl.[Max_Size] = 268435456 THEN 'TLogMax'
ELSE CAST((MasterTbl.[Max_Size] * 8)/1024 AS varchar(10)) END,
File_Growth = CASE
WHEN MasterTbl.[Growth] = 0 THEN 'NoGrowth'
WHEN MasterTbl.[Growth] > 0 AND MasterTbl.[is_percent_growth] = 0
THEN CAST((MasterTbl.[Growth]* 8)/1024 AS varchar(10))
WHEN MasterTbl.[Growth] > 0 AND MasterTbl.[is_percent_growth] = 1
THEN CAST(MasterTbl.[Growth] AS varchar(10))
ELSE 'Unknown' END,
Growth_Type = CASE
WHEN MasterTbl.[Growth] = 0 THEN 'NoGrowth'
WHEN MasterTbl.[is_percent_growth] = 0 THEN 'MegaBytes'
WHEN MasterTbl.[is_percent_growth] = 1 THEN 'Percentage'
ELSE 'Unknown' END,
MasterTbl.[Physical_Name],
GETDATE() AS 'DateCaptured'
FROM Master.sys.master_files MasterTbl (NOLOCK)
LEFT OUTER JOIN TEMPDB.SYS.SYSFILES TempTbl (NOLOCK)
 ON MasterTbl.[Physical_Name] = TempTbl.[filename]
WHERE Database_ID = 2
SET NOCOUNT OFF
GO

Step 3: Execute the above SP via SQL agent job: we schedule it to run every 4 hours. Therefore by the end of the week we have enough data to predict how much tempdb is growing and thus estimating accordingly.

Just execute

    SELECT *
FROM [dbo].[TempDBFileSize]
ORDER BY ActualMB

to view the stored results


You should be able to easily combine your current queries to get the group and users by using the following:

SELECT g.name group_name, u.id, u.name
FROM groups g
INNER JOIN user_groups ug
  ON g.id = ug.group_id
INNER JOIN users u
  on ug.user_id  = u.id;

See SQL Fiddle with Demo. This will give you multiple rows for each group if there is more than one user per group, similar to this:

| GROUP_NAME | ID | NAME |
--------------------------
|      admin |  1 | dave |
|      admin |  2 | cher |

You could also use the GROUP_CONCAT() function to return a comma separated list of the users with each group:

SELECT g.name group_name,
  GROUP_CONCAT(u.name SEPARATOR ', ') name
FROM groups g
INNER JOIN user_groups ug
  ON g.id = ug.group_id
INNER JOIN users u
  on ug.user_id  = u.id
GROUP BY g.name;

See SQL Fiddle with Demo. This returns a result:

| GROUP_NAME |       NAME |
---------------------------
|      admin | cher, dave |

Then if you want to also include the users that have no group associated with them, you could use a UNION ALL query:

SELECT g.name group_name, u.id, u.name
FROM groups g
INNER JOIN user_groups ug
  ON g.id = ug.group_id
INNER JOIN users u
  on ug.user_id  = u.id
UNION ALL
SELECT '' group_name, u.id, u.name
FROM users u
WHERE id not in (select user_id
                 from user_groups);

See SQL Fiddle with Demo. This returns:

| GROUP_NAME | ID |  NAME |
---------------------------
|      admin |  1 |  dave |
|      admin |  2 |  cher |
|            |  3 |   meg |
|            |  4 | sarah |


A quick solution might be to make a trigger in the Work_Area table that checks the Interests table to see if that field already exists, and if it does not then insert it as a new record. This would prevent the need to manually add an author's new interests, just add their new article and let the interests table take care of itself


I think this is what you are after, no need for Regex. It's just a correlated subquery, with the only difference being the LIKE condition instead of the common equality condition:

SELECT b.*
FROM tablename AS b
WHERE EXISTS
      ( SELECT *
        FROM words AS w
        WHERE b.name LIKE '%' || w.word || '%'
      ) ;


Performance impact of Splitting Table across files

The problem we are facing is from the network team who backup and
  transfer the MDF file over to our DR site every night.

Are they really transferring the mdf file to the DR site? If so, stop, now.

If thankfully backups are being taken, or you can persuade them to start taking backups instead of shifting the mdf, split the backup to multiple files.

BACKUP DATABASE [MyDatabase]
TO
    DISK = 'D:\MyDatabase_file1.bak'
    , DISK = 'D:\MyDatabase_file2.bak'
    , DISK = 'D:\MyDatabase_file3.bak'
    , DISK = 'D:\MyDatabase_file4.bak'
WITH
    NAME = 'MyDatabase backup'
    , STATS = 10

  They are also saying it would make it easier to manage allocating
  space for future growth from our virtual infrastructure that it is
  sitting on.

If you want to add additional storage to the database by way of additional files, that is also possible.

ALTER DATABASE [MyDatabase]
ADD FILE
(
    NAME = MyDatabase_file2,
    FILENAME = 'D:\MSSQL\Data\MyDatabase_file2.ndf',
    SIZE = 64GB,
    FILEGROWTH = 8GB
)

For this particular situation however, one does wonder why the administrators can't manage storage by way of the (storage) virtualisation layer.


Specify type on SQL Server Computed Column

You should be able to CAST to  your preferred data type inside the CASE:

...IS NULL THEN CAST(0 as BIT) ELSE CAST(1 as BIT) END


Select from linked server to local server causing waits on both sides

Pulling data through  a linked server is unlikely to be quick especially if you are joining remote tables to local tables. (Just use Profiler to watch what your local server sends to the remote server and I think that you'll be convinced.) Linked Servers are convenient and often "good enough", but not when you start blowing through your batch processing windows.

I have seen overall processing go faster by pulling the remote data into local (temporary or permanent) tables and then "doing the joins" locally to figure out what to put into the production tables.

If you have a large amount of data, I suggest looking into using a SSIS package or bcp and probably bringing the data into a local staging table first and converting the code to run off of local tables. This might involve a lot of surgery to your jobs since you need to have a job step (or steps) to run bcp code and/or packages.

If you are using SQL 2008 or better, MERGE should be available and it could provide more efficient operations. I am unsure if MERGE works with tables on a linked server and, if it does, performance may not be much better than what you have. So, you are still stuck getting the data onto the local server.

Also, you could look into partition switching, but I think would be the most costly thing to do in terms of time spent on getting things to work. You could view it as a learning experience.

Alternatively, you might consider increasing the query timeout, which is a server-level setting on the local server. That might help with the timeout error (until your data volume increases, then you would need to tweak it again), but I doubt that it will help with the TDS error problem.

If you want to minimize the length of time that your production tables are locked (due to the INSERT, not the SELECT with nolock), you would want to get all of the data from the ETL system onto the local box first, then go and insert it all. You want to avoid pulling data for one table, inserting that, pulling the data for the next table, inserting that, etc. (I'm presuming that you are doing all of this in a declared transaction that I'm not seeing.)


The Short Answer:
I misread your question a little bit. The below discussion still applies and explains my answer, but I would suggest UTCDate, ID for your index. Especially because of your partitions and the fact that UTCDate is included in every query. The question of fragmentation is a good one, and I imagine you'll see some in either of your two choices, but the performance benefit would be best with this way, I feel.

The Previous Discussion:

This is one you'll likely get a few answers on and I've asked a couple questions in the comments. That said a few quick thoughts to get started (ignoring the multiple system question and the way the IDs are being used):

1.) Presuming you are talking about SQL Server partitioning, a partition aligned clustered index is generally best. So in my mind that is a good vote for UTCDate.

2.) You indicate that filters will always (is that really always? or figuratively always?) have UTCDate. In my mind that is another good vote for UTCDate.

3.) Because you'll be having IDs coming in at both a high range and a low range, you will experience some level of index fragmentation on ID. Assuming UTCDate is always increasing and you aren't entering data from dates all over the place, you may actually experience less fragmentation going with UTCDate.

You also didn't indicate if this is a fact table or dimension. Assuming it is a fact table, that is another good argument for date in my book. If you are always querying filtering on a date or date range, then the clustered index being on that range scan value should help your queries. The fact that it would be partition aligned would also likely help.

If this is a dimension and you will sometimes join on ID to pull data out of it, then there could be a stronger argument for going with ID, though I'd argue that the unaligned nature of that index to your partition could cause you more grief in the future performance wise.

I would not bother adding ID to the index. Unless you include it enough as a filtering predicate and could actually see an improvement in performance of most queries. Yes, the date column alone wouldn't be unique, but if this table is a BIGINT, the 4 byte uniqueifier SQL Server adds to the clustered index to make it unique would actually cost less in terms of storage space than that ID column.

These are just my thoughts and they are quick thoughts. This is one of those things you should really test, try a couple ways and see how it works out. Though partitioning sometimes forces your hand a bit. So I would probably end up with the date column only as my first choice and date plus ID as my second choice. Others will likely have alternative ideas on that one, too. And more information edited back into your question will help.


PostgreSQL - How does multicolumn B-Tree index work with order by on 1st column and IN lookup for 2nd?

I wouldn't let this bug you. FILTER in that context, I believe just means more than one conditional statement on the index (which is how IN and array operations  get translated to, afaik). In either one, they're all executed under Index Only Scan Backward. It works the same way with OR

                                                                        QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=0.56..1219.95 rows=500 width=11) (actual time=0.061..16.159 rows=500 loops=1)
   ->  Index Only Scan Backward using id1_id2_idx on scale_data  (cost=0.56..679161.56 rows=278484 width=11) (actual time=0.060..16.086 rows=500 loops=1)
         Filter: ((id2 = '50'::numeric) OR (id2 = '52'::numeric))
         Rows Removed by Filter: 24673
         Heap Fetches: 25173
 Planning time: 0.206 ms
 Execution time: 16.235 ms
(7 rows)

test=# EXPlAIN ANALYZE select id2 from scale_data
where id2 in (50, 52)
order by id1 desc
limit 500
;
                                                                        QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=0.56..1153.17 rows=500 width=11) (actual time=0.072..18.604 rows=500 loops=1)
   ->  Index Only Scan Backward using id1_id2_idx on scale_data  (cost=0.56..645299.05 rows=279930 width=11) (actual time=0.070..18.506 rows=500 loops=1)
         Filter: (id2 = ANY ('{50,52}'::numeric[]))
         Rows Removed by Filter: 24673
         Heap Fetches: 25173
 Planning time: 0.187 ms
 Execution time: 18.695 ms
(7 rows)


How do you create a view with SNAPSHOT_MATERIALIZATION in SQL Server 2017?

You can't. The feature is disabled in 2017 RTM.

That said, you can...

Using AdventureWorks:

CREATE VIEW dbo.TH
WITH SCHEMABINDING
AS
SELECT P.ProductID, COUNT_BIG(*) AS cbs
FROM Production.Product AS P
JOIN Production.TransactionHistory AS TH
    ON TH.ProductID = P.ProductID
GROUP BY P.ProductID;
GO
CREATE UNIQUE CLUSTERED INDEX cuq ON dbo.TH (ProductID)
WITH (SNAPSHOT_MATERIALIZATION = ON);

Changes to the underlying tables are not immediately reflected in the view (as is normally the case with SQL Server). Likewise, data modifications against the underlying tables do not have to maintain the snapshot indexed view.

To refresh the view contents, one needs to call one of the new stored procedures:

EXECUTE sys.sp_refresh_single_snapshot_view
    @view_name = N'dbo.TH',
    @rgCode = 0; -- don't know what this is for yet

This produces the execution plan:

This likely won't work for you, because either an undocumented trace flag is needed, or you need to do the particularly nasty thing I did: writing to the memory location holding the feature flag (using a debugger) to enable this feature.

If you're curious, the feature flag is the byte at sqllang!g_featureSwitchesLangSvc+0x10f. It is checked during sqllang!SpRefreshSingleSnapshotView.

If you want to play along, and are fully prepared to accept the consequences of hacking about in SQL Server's code while it is running, and using a feature that Microsoft does not think is ready yet:

Attach a debugger to the SQL Server 2017 process. I use WinDbg.
Set a breakpoint:

bp sqllang!SpRefreshSingleSnapshotView

Resume SQL Server using the Go command (g)
Create the view above, but not the unique clustered index yet
Run the sys.sp_refresh_single_snapshot_view command above
When the breakpoint is hit, step through until you see the code line:

cmp byte ptr [sqllang!g_featureSwitchesLangSvc+0x10f (00007fff`328dfbcf)],0

The offset may be different in other builds, for example in 2017 RTM CU3 it is sqllang!g_featureSwitchesLangSvc+0x114
The memory address inside the parentheses may be different. Use the one you see.
Use the display memory command to see the current value at the memory address you found:

db 00007fff`328dfbcf L1

This should show a zero, indicating that the feature is disabled.
Change the zero to a one, using the enter values command (again with your memory address):

eb 00007fff`328dfbcf 1

Disable the breakpoint and resume running SQL Server.
The feature is now enabled.
Build the unique clustered index on the view.
Play around.

Note SNAPSHOT_MATERIALIZATION allows us to materialize a snapshot of a query specification that ordinarily could not be indexed, for example the below uses MAX:

CREATE VIEW dbo.TH2
WITH SCHEMABINDING
AS
SELECT TH.ProductID, MaxTransactionID = MAX(TH.TransactionID)
FROM Production.TransactionHistory AS TH
GROUP BY TH.ProductID;
GO
CREATE UNIQUE CLUSTERED INDEX cuq ON dbo.TH2 (ProductID)
WITH (SNAPSHOT_MATERIALIZATION = ON);

Result:

Commands completed successfully.


How are foreign keys resolved when creating tables in batch?

the batch will fail.

do all the table creates first, then do all the foreign key constraints AFTER all the tables are created.

OR

create the tables with constraints in the correct ORDER so that constraints don't fail.


Automatically have Nonclustered indexes in a Different Filegroup

Clustered index is just a well arranged table. When we create clustered index on heap, it arrange data of the tables based on key column. So moving clustered index means you are moving table to different file group.

There’s no inbuilt feature in SQL Server to automatically move indexes to specific file group when we create index, instead we can write index maintenance script to move indexes to specific file group based on index type or we'll have to specify file group while we are creating indexes.

Also, this link will help, https://stackoverflow.com/questions/55187160/automatically-create-indexes-in-different-filegroup-edit-publish-profile-script


SQL Server data files in Windows Azure: filegroups and partitioning still applicable?

If you anticipate that your database file(s) will exceed more than 1TB in size you should configure the server to put the data files across multiple VHDs.
How can spread my databases across multiple VHDs?

Create one or more dynamic volumes consisting of multiple VHDs at the operating system level and put your database files on these volumes.
Split your tables and indexes into separate database filegroups and then place those filegroups in different files (MDF + NDF) which are spread across separately attached VHDs. Note that this will not offer any benefit for transaction logs because SQL Server does not stripe across transaction log files but uses them sequentially.

If you are migrated to Azure and want to compare the performance of your databases before and after the migration then take a SQL Server baseline(enter link description here)


Correcting the imported latin1 data to be utf8

You can restore your database using this command

mysql -u someuser -p --default-character-set=utf8 somedatabase < backup.sql

This overrides the default client character set.

I would also make sure that my tables are also in utf8.

So when you open up your backup file with a text editor, you should see utf8 as the DEFAULT CHARSET

CREATE TABLE `sometable` (
  `c1` varchar(10) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8

If you are still having problems, check your backup and make sure that the actual data being restored is correctly formatted.  If you had a character like é for example, it should show up as é when you view it in a text editor and not as &eacute; or some unreadable symbol.

One additional note.  utf8_general_ci and latin1_swedish_ci are collations and not character sets.   Character sets like UTF8 can have multiple collations, some are case sensitive _cs and others are case insensitive _ci.  Collations set rules for character comparison and sorting.

http://en.wikipedia.org/wiki/Collation


Oracle: Scopes of the CURSOR_SHARING parameter

You can create LOGON TRIGGER ON SCHEMA and use
execute immediate 'alter session set CURSOR_SHARING=...';

to set it on session level


How to troubleshoot db/app latency during transaction log backup

It sounds like your transaction log probably has a lot of VLFs on it which causes the SQL Server to take a hit when doing the transaction log backups.  You can find this out by running

use {MyDatabase}
GO
DBCC LOGINFO
GO

The important part to look at is the number of rows which are returned.  If there are more than about 100 rows returned then you've got problems (please comment with the number of rows that are returned).

The fix is pretty easy.  Make a note of the size of the transaction log.  Shrink the transaction log down to as small as possible (the above command will return 2 rows).  This is done using the DBCC SHRINKFILE command.  You may need to run the shrink command a few times to get it small enough.  Try waiting a minute between shrink commands for the logs to cycle and run a log backup just before running the shrinkfile command.

DBCC SHRINKFILE (2,1)
GO

Then once the transaction log file is as small as possible grow the file in 8000 meg chunks using either the GUI in SQL Server Management Studio or by using the ALTER DATABASE command.

ALTER DATABASE MyDatabase
MODIFY FILE (name='MyLogFile', FILESIZE=8000MB)
GO

If you need the file to be larger than 8000 Megs do 8000 megs in size, then 16000 megs in size, then 24000 megs in size always growing 8000 megs at a time.  Compared to the prior size of the transaction log grow the low to the 8000 meg mark past the size that the log was.

If this doesn't do the trick let me know.


Postgres 10 not returning disk space after dropping indexes

Have you previously used pg_upgrade -k to get to this version, and then didn't delete the old version's cluster directory?  If so, that old directory has hard links to the index data files which cause the underlying storage to be retained even when the link in the new version's directory gets unlinked.


Facing permission error while trying to execute a stored procedure published through CLR

This setting must be enabled on the database in order to allow CLR procedures to run.  Query this setting which should show a value of 1

SELECT name,
CAST(value as int) as value_configured,
CAST(value_in_use as int) as value_in_use
FROM sys.configurations
WHERE name ='clr enabled';

If it is not then using a login with dba privileges run this

EXECUTE sp_configure 'clr enabled',1;
RECONFIGURE;

It would be advisable when creating the assembly to mark what access it needs.  From the documentation an example

CREATE ASSEMBLY SQLCLRTest
FROM 'C:\MyDBApp\SQLCLRTest.dll'
WITH PERMISSION_SET = SAFE;

The choices of access are SAFE (most restrictive), EXTERNAL_ACCESS and UNSAFE (least restrictive).  What to mark the assembly depends on what it accesses and where the resource is located.

If your assembly requires EXTERNAL or UNSAFE access such as the ability to access local data or native code then you are also required to do either of these:

the assembly is signed with a certificate and has a login with EXTERNAL ACCESS ASSEMBLY or UNSAFE ASSEMBLY permission. This is preferred as it restrict permissions to the lowest level: one user
database owner has EXTERNAL ACCESS ASSEMBLY or UNSAFE ASSEMBLY

From a security audit perspective using CIS standards CLR assemblies are always flagged for further review.  This does not mean you should not do this, just that there should be no other way of accomplishing what is required.


Oracle installation fails due to ORA-00443

Your OS is x86 but not amd64?
It seems SGA setting larger than system limit. Set your oracle memory under 2GB.


So as @RickJames, suggested in one of the comments, cardinality has little to do with order of indexes. Therefore if your query is similar to

SELECT id, search_id, provider_id FROM t1 WHERE search_id=123 AND provider_id=567

Then the index should be ordered search_id,provider_id. If however you are running a more range based query.

  For efficiency, the "=constant" column should come before the "range"
  column in the INDEX

That means whenever you have a query as follows.

SELECT id, search_id, provider_id FROM t1 WHERE search_id>123 AND
provider_id<567 AND columnX=1

Then your index should be created as follows columnX,search_id,provider_id.


Here are a few methods you can compare. First let's set up a table with some dummy data. I'm populating this with a bunch of random data from sys.all_columns. Well, it's kind of random - I'm ensuring that the dates are contiguous (which is really only important for one of the answers).
CREATE TABLE dbo.Hits(Day SMALLDATETIME, CustomerID INT);

CREATE CLUSTERED INDEX x ON dbo.Hits([Day]);

INSERT dbo.Hits SELECT TOP (5000) DATEADD(DAY, r, '20120501'),
  COALESCE(ASCII(SUBSTRING(name, s, 1)), 86)
FROM (SELECT name, r = ROW_NUMBER() OVER (ORDER BY name)/10,
       s = CONVERT(INT, RIGHT(CONVERT(VARCHAR(20), [object_id]), 1))
FROM sys.all_columns) AS x;

SELECT
  Earliest_Day   = MIN([Day]),
  Latest_Day     = MAX([Day]),
  Unique_Days    = DATEDIFF(DAY, MIN([Day]), MAX([Day])) + 1,
  Total_Rows     = COUNT(*)
FROM dbo.Hits;

Results:
Earliest_Day         Latest_Day           Unique_Days  Total_Days
-------------------  -------------------  -----------  ----------
2012-05-01 00:00:00  2013-09-13 00:00:00  501          5000

The data looks like this (5000 rows) - but will look slightly different on your system depending on version and build #:
Day                  CustomerID
-------------------  ---
2012-05-01 00:00:00  95
2012-05-01 00:00:00  97
2012-05-01 00:00:00  97
2012-05-01 00:00:00  117
2012-05-01 00:00:00  100
...
2012-05-02 00:00:00  110
2012-05-02 00:00:00  110
2012-05-02 00:00:00  95
...

And the running totals results should look like this (501 rows):
Day                  c   rt
-------------------  --  --
2012-05-01 00:00:00  6   6
2012-05-02 00:00:00  5   11
2012-05-03 00:00:00  4   15
2012-05-04 00:00:00  7   22
2012-05-05 00:00:00  6   28
...

So the methods I am going to compare are:

"self-join" - the set-based purist approach
"recursive CTE with dates" - this relies on contiguous dates (no gaps)
"recursive CTE with row_number" - similar to above but slower, relying on ROW_NUMBER
"recursive CTE with #temp table" - stolen from Mikael's answer as suggested
"quirky update" which, while unsupported and not promising defined behavior, seems to be quite popular
"cursor"
SQL Server 2012 using new windowing functionality

self-join
This is the way people will tell you to do it when they're warning you to stay away from cursors, because "set-based is always faster." In some recent experiments I've found that the cursor out-paces this solution.
;WITH g AS
(
  SELECT [Day], c = COUNT(DISTINCT CustomerID)
    FROM dbo.Hits
    GROUP BY [Day]
)
SELECT g.[Day], g.c, rt = SUM(g2.c)
  FROM g INNER JOIN g AS g2
  ON g.[Day] >= g2.[Day]
GROUP BY g.[Day], g.c
ORDER BY g.[Day];

recursive cte with dates
Reminder - this relies on contiguous dates (no gaps), up to 10000 levels of recursion, and that you know the start date of the range you're interested (to set the anchor). You could set the anchor dynamically using a subquery, of course, but I wanted to keep things simple.
;WITH g AS
(
  SELECT [Day], c = COUNT(DISTINCT CustomerID)
    FROM dbo.Hits
    GROUP BY [Day]
), x AS
(
    SELECT [Day], c, rt = c
        FROM g
        WHERE [Day] = '20120501'
    UNION ALL
    SELECT g.[Day], g.c, x.rt + g.c
        FROM x INNER JOIN g
        ON g.[Day] = DATEADD(DAY, 1, x.[Day])
)
SELECT [Day], c, rt
    FROM x
    ORDER BY [Day]
    OPTION (MAXRECURSION 10000);

recursive cte with row_number
Row_number calculation is slightly expensive here. Again this supports max level of recursion of 10000, but you don't need to assign the anchor.
;WITH g AS
(
  SELECT [Day], rn = ROW_NUMBER() OVER (ORDER BY DAY),
    c = COUNT(DISTINCT CustomerID)
    FROM dbo.Hits
    GROUP BY [Day]
), x AS
(
    SELECT [Day], rn, c, rt = c
        FROM g
        WHERE rn = 1
    UNION ALL
    SELECT g.[Day], g.rn, g.c, x.rt + g.c
        FROM x INNER JOIN g
        ON g.rn = x.rn + 1
)
SELECT [Day], c, rt
    FROM x
    ORDER BY [Day]
    OPTION (MAXRECURSION 10000);

recursive cte with temp table
Stealing from Mikael's answer, as suggested, to include this in the tests.
CREATE TABLE #Hits
(
  rn INT PRIMARY KEY,
  c INT,
  [Day] SMALLDATETIME
);

INSERT INTO #Hits (rn, c, Day)
SELECT ROW_NUMBER() OVER (ORDER BY DAY),
       COUNT(DISTINCT CustomerID),
       [Day]
FROM dbo.Hits
GROUP BY [Day];

WITH x AS
(
    SELECT [Day], rn, c, rt = c
        FROM #Hits as c
        WHERE rn = 1
    UNION ALL
    SELECT g.[Day], g.rn, g.c, x.rt + g.c
        FROM x INNER JOIN #Hits as g
        ON g.rn = x.rn + 1
)
SELECT [Day], c, rt
    FROM x
    ORDER BY [Day]
    OPTION (MAXRECURSION 10000);

DROP TABLE #Hits;

quirky update
Again I am only including this for completeness; I personally wouldn't rely on this solution since, as I mentioned on another answer, this method is not guaranteed to work at all, and may completely break in a future version of SQL Server. (I'm doing my best to coerce SQL Server into obeying the order I want, using a hint for the index choice.)
CREATE TABLE #x([Day] SMALLDATETIME, c INT, rt INT);
CREATE UNIQUE CLUSTERED INDEX x ON #x([Day]);

INSERT #x([Day], c)
    SELECT [Day], c = COUNT(DISTINCT CustomerID)
    FROM dbo.Hits
    GROUP BY [Day]
    ORDER BY [Day];

DECLARE @rt1 INT;
SET @rt1 = 0;

UPDATE #x
SET @rt1 = rt = @rt1 + c
FROM #x WITH (INDEX = x);

SELECT [Day], c, rt FROM #x ORDER BY [Day];

DROP TABLE #x;

cursor
"Beware, there be cursors here! Cursors are evil! You should avoid cursors at all costs!" No, that's not me talking, it's just stuff I hear a lot. Contrary to popular opinion, there are some cases where cursors are appropriate.
CREATE TABLE #x2([Day] SMALLDATETIME, c INT, rt INT);
CREATE UNIQUE CLUSTERED INDEX x ON #x2([Day]);

INSERT #x2([Day], c)
    SELECT [Day], COUNT(DISTINCT CustomerID)
    FROM dbo.Hits
    GROUP BY [Day]
    ORDER BY [Day];

DECLARE @rt2 INT, @d SMALLDATETIME, @c INT;
SET @rt2 = 0;

DECLARE c CURSOR LOCAL STATIC READ_ONLY FORWARD_ONLY
  FOR SELECT [Day], c FROM #x2 ORDER BY [Day];

OPEN c;

FETCH NEXT FROM c INTO @d, @c;

WHILE @@FETCH_STATUS = 0
BEGIN
  SET @rt2 = @rt2 + @c;
  UPDATE #x2 SET rt = @rt2 WHERE [Day] = @d;
  FETCH NEXT FROM c INTO @d, @c;
END

SELECT [Day], c, rt FROM #x2 ORDER BY [Day];

DROP TABLE #x2;

SQL Server 2012
If you are on the most recent version of SQL Server, enhancements to windowing functionality allows us to easily calculate running totals without the exponential cost of self-joining (the SUM is calculated in one pass), the complexity of the CTEs (including the requirement of contiguous rows for the better performing CTE), the unsupported quirky update and the forbidden cursor. Just be wary of the difference between using RANGE and ROWS, or not specifying at all - only ROWS avoids an on-disk spool, which will hamper performance significantly otherwise.
;WITH g AS
(
  SELECT [Day], c = COUNT(DISTINCT CustomerID)
    FROM dbo.Hits
    GROUP BY [Day]
)
SELECT g.[Day], c,
  rt = SUM(c) OVER (ORDER BY [Day] ROWS UNBOUNDED PRECEDING)
FROM g
ORDER BY g.[Day];

performance comparisons
I took each approach and wrapped it a batch using the following:
SELECT SYSUTCDATETIME();
GO
DBCC DROPCLEANBUFFERS;DBCC FREEPROCCACHE;
-- query here
GO 10
SELECT SYSUTCDATETIME();

Here are the results of the total duration, in milliseconds (remember this includes the DBCC commands each time as well):
method                          run 1     run 2
-----------------------------   --------  --------
self-join                        1296 ms   1357 ms -- "supported" non-SQL 2012 winner
recursive cte with dates         1655 ms   1516 ms
recursive cte with row_number   19747 ms  19630 ms
recursive cte with #temp table   1624 ms   1329 ms
quirky update                     880 ms   1030 ms -- non-SQL 2012 winner
cursor                           1962 ms   1850 ms
SQL Server 2012                   847 ms    917 ms -- winner if SQL 2012 available

And I did it again without the DBCC commands:
method                          run 1     run 2
-----------------------------   --------  --------
self-join                        1272 ms   1309 ms -- "supported" non-SQL 2012 winner
recursive cte with dates         1247 ms   1593 ms
recursive cte with row_number   18646 ms  18803 ms
recursive cte with #temp table   1340 ms   1564 ms
quirky update                    1024 ms   1116 ms -- non-SQL 2012 winner
cursor                           1969 ms   1835 ms
SQL Server 2012                   600 ms    569 ms -- winner if SQL 2012 available

Removing both the DBCC and loops, just measuring one raw iteration:
method                          run 1     run 2
-----------------------------   --------  --------
self-join                         313 ms    242 ms
recursive cte with dates          217 ms    217 ms
recursive cte with row_number    2114 ms   1976 ms
recursive cte with #temp table     83 ms    116 ms -- "supported" non-SQL 2012 winner
quirky update                      86 ms     85 ms -- non-SQL 2012 winner
cursor                           1060 ms    983 ms
SQL Server 2012                    68 ms     40 ms -- winner if SQL 2012 available

Finally, I multiplied the row count in the source table by 10 (changing top to 50000 and adding another table as a cross join). The results of this, one single iteration with no DBCC commands (simply in the interests of time):
method                           run 1      run 2
-----------------------------    --------   --------
self-join                         2401 ms    2520 ms
recursive cte with dates           442 ms     473 ms
recursive cte with row_number   144548 ms  147716 ms
recursive cte with #temp table     245 ms     236 ms -- "supported" non-SQL 2012 winner
quirky update                      150 ms     148 ms -- non-SQL 2012 winner
cursor                            1453 ms    1395 ms
SQL Server 2012                    131 ms     133 ms -- winner

I only measured duration - I'll leave it as an exercise to the reader to compare these approaches on their data, comparing other metrics that may be important (or may vary with their schema/data). Before drawing any conclusions from this answer, it'll be up to you to test it against your data and your schema... these results will almost certainly change as the row counts get higher.

demo
I've added a sqlfiddle. Results:

conclusion
In my tests, the choice would be:

SQL Server 2012 method, if I have SQL Server 2012 available.
If SQL Server 2012 is not available, and my dates are contiguous, I would go with the recursive cte with dates method.
If neither 1. nor 2. are applicable, I would go with the self-join over the quirky update, even though the performance was close, just because the behavior is documented and guaranteed. I'm less worried about future compatibility because hopefully if the quirky update breaks it will be after I've already converted all my code to 1. :-)

But again, you should test these against your schema and data. Since this was a contrived test with relatively low row counts, it may as well be a fart in the wind. I've done other tests with different schema and row counts, and the performance heuristics were quite different... which is why I asked so many follow-up questions to your original question.

UPDATE
I've blogged more about this here:
Best approaches for running totals – updated for SQL Server 2012


views restored from backup in a different server referring to linked servers

Synonyms will not be useful for your need since that will require changing the view definition to specify 2-part names.

  I also thought about defining a linked server named Y in Z, but
  pointing to itself. Here I was only able to make a linked server named
  localhost pointing to itself, and this does not help solve my problem.

It is possible to create a loopback linked server with the name Y using SSMS by selecting "Other data source" (instead of SQL Server) and then specifying a SQL Server provider. This can also be done via T-SQL:

EXEC master.dbo.sp_addlinkedserver
      @server = N'Y'
    , @srvproduct=N''
    , @provider=N'SQLNCLI11'
    , @datasrc=N'.'
    , @catalog=N'YourDatabase';

You'll need to also configure the linked server security context according to your requirements.


Does a read replica help if the problem are heavy queries?

If you had a lot of concurrent heavy queries, the replica could help spread the workload.  Or if the heavy query was interfering with other work the master database needs to do.  But if you don't run multiple queries at the same time and the problem is only that they take too long, there is no reason to think a replica would help that situation.

  make the DB choke

What does that mean? Databases don't get food lodged in their trachea. Are you getting error messages(other than apparently query time-outs)?

  The solution so far has been optimizing these queries, by trying e.g. to fetch less fields, and scaling up the DB.

There is a lot more to performance optimization than that.  What about indexes?  Are you using a version of PostgreSQL that supports parallelism?  See https://dba.stackexchange.com/tags/postgresql-performance/info for more info on how to optimize (or at least, how to ask optimization questions).

What are alternatives?  Well, if the only problem is that the queries time out, then change your time outs.  Tune your queries, as above.  Upgrade the database version so it can use parallelism.


Migration 100 TB of data from SQL Server 2008 to 2016

The most important part of your question is:

  can anyone please provide detail plan for migration

And I've got great news: Microsoft used to build a really detailed Upgrade Technical Guide (429-page PDF) that covered all kinds of details. They haven't updated it since 2014, but the information in there is still really useful for folks who are just starting from scratch and looking for guidance.

The bad news: SQL Server has only gotten more complicated since then. The 429-page length should help explain why it doesn't get updated frequently - it's expensive to build.


Just keep one table. Then replace the other table with a view on the table. Now you should be able to insert/update/delete a row in either the view or the table. Normally this should work. If the insert/update/delete in the view cause a problem then you can always create triggers on the view that replace insert, delete, and update (create trigger t_x instead of update on view). Check the oracle documentation for more information about the triggers


I want to get all the weeks of a month with the start and end dates of those weeks without creating a table


Slow query on large table with GROUP BY and ORDER BY

The LEFT JOIN in @dezso's answer should be good. An index, however, will hardly be useful (per se), because the query has to read the whole table anyway - the exception being index-only scans in Postgres 9.2+ and favorable conditions, see below.

SELECT m.hash, m.string, count(m.method) AS method_ct
FROM   methods m
LEFT   JOIN nostring n USING (hash)
WHERE  n.hash IS NULL
GROUP  BY m.hash, m.string
ORDER  BY count(m.method) DESC;

Run EXPLAIN ANALYZE on the query. Several times to exclude cashing effects and noise. Compare the best results.

Create a multi-column index that matches your query:

CREATE INDEX methods_cluster_idx ON methods (hash, string, method);

Wait? After I said an index wouldn't help? Well, we need it to CLUSTER the table:

CLUSTER methods USING methods_cluster_idx;
ANALYZE methods;

Rerun EXPLAIN ANALYZE. Any faster? It should be.

CLUSTER is a one-time operation to rewrite the whole table in the order of the used index. It is also effectively a VACUUM FULL. If you want to be sure, you'd run a pre-test with VACUUM FULL alone to see what can be attributed to that.

If your table sees a lot of write operations, the effect will degrade over time. Schedule CLUSTER at off-hours to restore the effect. Fine tuning depends of your exact use-case. The manual about CLUSTER.

CLUSTER is a rather crude tool, needs an exclusive lock on the table. If you can't afford that, consider pg_repack which can do the same without exclusive lock. More in this later answer:

Configuring PostgreSQL for read performance

If the percentage of NULL values in the column method is high (more than ~ 20 percent, depending on actual row sizes), a partial index should help:

CREATE INDEX methods_foo_idx ON methods (hash, string)
WHERE method IS NOT NULL;

(Your later update shows your columns to be NOT NULL, so not applicable.)

If you are running PostgreSQL 9.2 or later (as @deszo commented) the presented indexes may be useful without CLUSTER if the planner can utilize index-only scans. Only applicable under favorable conditions: No write operations that would effect the visibility map since the last VACUUM and all columns in the query have to be covered by the index. Basically read-only tables can use this any time, while heavily written tables are limited. More details in the Postgres Wiki.

The above mentioned partial index could be even more useful in that case.

If, on the other hand, there are no NULL values in column method, you should
1.) define it NOT NULL and
2.) use count(*) instead of count(method), that's slightly faster and does the same in the absence of NULL values.

If you have to call this query often and the table is read-only, create a MATERIALIZED VIEW.

Exotic fine point:
Your table is named nostring, yet seems to contain hashes. By excluding hashes instead of strings, there is a chance that you exclude more strings than intended. Extremely unlikely, but possible.


You can try this query to get good performance without using temp table or joining to delete from T1. And it's very simple.

SET ROWCOUNT 10000

WHILE EXISTS (SELECT TOP 1 'EXISTS' FROM T1 WHERE COLUMN=VALUE)
BEGIN
WAITFOR DELAY '00:00:10' -- You can pass it if you want interval between next delete
DELETE FROM T1
OUTPUT DELETED.* INTO T2
WHERE COLUMN=VALUE
END

SET ROWCOUNT 0

You can comment or remove WHERE clause if not required.

Thanks


In Oracle 12c you should use the Database Migration Assistant for Unicode (DMU) to perform this operation.

https://docs.oracle.com/database/121/DUMAG/toc.htm

Your proposed conversion is very straightforward and shouldn't be a problem, however: Are you sure this is what you want to do? Oracle recommends AL32UTF8 above UTF-8, unless it is absolutely neccessary:

  Oracle supports two encodings of Unicode as the database character
  set: UTF-8 through the AL32UTF8 character set and CESU-8 through the
  UTF8 character set. (Note the use of the hyphen in the name of Unicode
  encoding and the lack of a hyphen in the name of Oracle character set.
  This differentiation is used throughout Oracle documentation.) UTF-8
  is a multibyte varying width Unicode encoding using 1 to 4 bytes per
  character. CESU-8 is a compatibility-only encoding, discouraged for
  information exchange by the Unicode standard. CESU-8 is very similar
  to UTF-8 except that so-called supplementary characters encoded by
  UTF-8 in 4 bytes, CESU-8 encodes as pairs of 3-byte codes. Oracle
  Database has deprecated the UTF8 character set, so it should not be
  used as a database character set except when explicitly required by an
  application, such as Oracle E-Business Suite Release 11i.


mySQL changing output unit of ST_Distance function

Update Oct, 2018

Hello everyone,

If someone is still landing here wondering for an answer. I have found one. My scope was to get all cities under 50 miles radius of a city selected by the user.

In the database, I have longitude and latitude column of all the cities.

What I did, is that I made a new column loc of "Point" datatype to store the combination of longitude and latitude of each city.

Then lets say user has chosen a city with some $latitude and $longitude, so I get all nearby cities (within 100 miles) as:

st_distance(POINT( '.floatval($longitude).', '.floatval($latitude).' ) , loc) <= 1.448

Putting this in you WHERE statement of MySQL query will give you all cities under 50 miles radius of that center city.

The formula I found to convert miles in angle is simple:

1 miles = 1/69.0585729 degree
Hence,
50 miles = 50/69.0585729 degree = 0.724 degree and 100 miles = 100/69.0585729 degree = 1.448 degree

If you need to have it in km, the formula goes like this:

1 degree = 111.139 km

Now you can convert km into degrees as well so that you can compare it with the output of st_distance() MySQL function

Please bear in mind that it might exclude some cities as mentioned by Rick in the comments. If you really want to go in depth of it, her eis the link posted by Rick James in the comment section, maybe this can help you out:

http://mysql.rjweb.org/doc.php/latlng

I hope it helps