Create index on very large table with many shared values I'm looking to create an index on a large table (~50 million rows) on a field with lots of non-unique values.

Table schema looks like:

 Column |         Type          | Modifiers | Storage  | Stats target | Description
--------+-----------------------+-----------+----------+--------------+-------------
 gid    | character varying(20) |           | extended |              |
 word   | character varying(30) |           | extended |              |
 stat   | double precision      |           | plain    |              |
Has OIDs: no

I want to create an index on the 'word' column. There is a fairly regular pattern where each word appears about 1000 times. I need to do fast SELECT * FROM mytable WHERE word='something';queries. Creating a regular B-Tree index on these tables takes a ton of time but does substantially improve performance.

I'm uncomfortable with my solution right now for several reasons

(1) The selection of a B-Tree index isn't particularly motivated. Are there alternative indexing schemes that perform better on fields with highly duplicated values?

(2) I'm in a production environment where these tables pop into and out of existence fairly regularly. Because not all tables will always be heavily queried I've opted to only build indexes on a table when certain (outside the DB) applications are triggered, such that I know 10k+ queries will be performed on the table+field. Waiting 20 minutes while an index is being created isn't ideal however. The situation is delicate; optimization gained by creating the index competes with the initial time-sink required to create the index. Are there 'cheaper' indexes to create? perhaps ones that will perform overall slightly worse than B-Tree but have less initial creation cost?

For starters gid should probably be a numeric type. integer should be good enough or bigint if the key space shouldn't be big enough. Much smaller footprint, faster processing than with character data, faster and smaller indexes.

More importantly, to improve performance I suggest database normalization.

Quote:

  There is a fairly regular pattern where each word appears about 1000 times.

Create a separate table for unique words:

CREATE TABLE word (
   word_id serial
 , word    text
);

Fill it with unique instances of word in your big_tbl:

INSERT INTO word (word)
SELECT DISTINCT word
FROM   big_tbl
ORDER  BY word;

ORDER BY is optional, not needed for query at hand. But it speeds up index creation and might be cheaper overall.

The table should be small in comparison: only ~ 50k rows for 50M rows in your big table.
Add indexes after filling the table:

ALTER TABLE word
    ADD CONSTRAINT word_word_uni UNIQUE (word) -- essential
  , ADD CONSTRAINT word_word_id_pkey PRIMARY KEY (word_id);  -- expendable?

If those are read-only tables, you can do without the pk. It's not relevant to the operations at hand.

Replace your big table with a much smaller new table. You may have to lock the big table to avoid concurrent writes. Concurrent reads are not a problem.

CREATE TABLE big_tbl_new AS
SELECT b.gid      -- or the suggested smaller, faster numeric replacement
     , w.word_id, b.stat
FROM   big_tbl b
JOIN   word w USING (word)
ORDER  BY word;   -- sorting by word helps query at hand

ORDER BY clusters the data (once) making the query at hand faster, because far fewer blocks have to be read (unless your data is clustered mostly already). The sort carries a cost, weigh cost and benefit once more.

DROP big_tbl;     -- make sure your new table has all data!
ALTER big_tbl_new RENAME TO big_tbl;

Recreate indexes:

ALTER TABLE big_tbl ADD CONSTRAINT big_tbl_gid_pkey PRIMARY KEY (gid);  -- expendable?
CREATE INDEX big_tbl_word_id_idx ON big_tbl (word_id);  -- essential

Your query looks like this now and should be faster:

SELECT b.*
FROM   word w
JOIN   big_tbl b USING (word_id)
WHERE  w.word = 'something';

Reorganization is meant to be a one-time operation to re-organize your data. Keep the new form and also consider keeping indexes permanently.

All of this together (including new indexes) should occupy about half of what you had before on disk, also cutting the time for creation in half (at least). Index creation should be considerably faster, the query as well. If RAM is a limiting factor, these modification pay double.

If you have to write to the table as well, it becomes more expensive (but you did not mention anything about that). You'd need to adjust your logic for DELETE / UPDATE / INSERT:
Example for INSERT: Fetch word_id for existing words or insert a new row in word returning the new word_id. Details for this:
How do I insert a row which contains a foreign key?


Tables within Tables within Tables I am new to database management/design but am fascinated by the subject and the possibilities it provides. I have had some experience with the following:

tables
attributes
fields and data types
defining foreign and primary keys
establishing one to many, one to one and many to many relationships using junction tables
developing queries using SQL

I have encountered one data structure that I have not been able to model properly in SQL for instance. Namely, the case where a field contains itself another table. This is frequently encountered in procedural and object oriented programming where data structures (such as derived types in fortran) can contain subtypes which hold only one value at a time but also subtypes which hold arrays... etc. In python for example it is possible that a "field" in a dict can hold a list which itself can hold fields of any given datatype including another list, dict or class. As you can see this allows for considerable complexity which I am not sure how to represent in a classical "database" schema in SQL where a table can have relationships with other tables but where they key: value schema is strictly enforced.

Anyone else know what I'm talking about? Is it possible/necessary to have tables within tables? If not, what SQL functionality renders this obsolete?

THanks

The concept you are exploring is called "nested tables".  Doing a search on this will yield a lot of items to read.  I haven't read those items, so I'm not going to make recommendations.

Many database products, such as Oracle, have a provision for defining table types as domains, and then referencing those domains when defining columns of other tables.  These are called nested tables.  To my knowledge, standard SQL does not have such a provision.

The concept of nested tables is closely related to the concept of First Normal Form, which Ed Codd originally called simply Normal Form, back in 1970.  In his paper, he talked about how databases could be limited to First Normal Form without limiting the expressive power of the relational model of data.  It turns out that a relational model that conforms to 1NF can be implemented in SQL without using nested tables.

You might also want to revisit CJ Date's treatment of the subject, where he presents the idea that a relation is always in 1NF.

Revisiting the history of ideas in computing can seem dry and boring, but given the question you are exploring, these historical pointers I'm providing are vital to understanding why SQL has not latched onto this concept.

As you go deeper, you'll begin to see how this connects to another phenomenon, which is the difference between relational data modeling and object modeling.  Object modeling is profoundly different from relational data modeling, to the point where experts in one form of modeling think the other is incomprehensible.  This often causes problems when an application is designed using an object model and the database is designed using the relational model.  This is sometimes called the "object relational mismatch".

I can't do justice to Codd's thinking here, but in very simple terms, he expected the first few relational DBMS systems to employ a very simple form of indexing to speed things up, and a table with nested tables can't use simple indexes to speed up keyed reference to data in subtables.


Keeping sales and purchase data in the same table? If my purchase and sales tables retain similar information, is there any problem with moving them to the same table and using an INT column to denote whether a row represents a purchase or a sale?

If there's additional specific information for either, could I retain it in, say, a link table?

Even if you can at this point, it's not clear that you should.

We'll assume most of the potential technical issues don't apply: that sales or purchases don't connect to some other tables for example.

If you were going to do it, then shifting one or two columns, especially if not heavily used, off to a new linked table could work.

You could update indexes to include the new type column.

However, sales and purchases are logically different, and are used in different ways. Even if today they have few differences, that could change dramatically with time. New fields could be required, or new business rules or constraints, that might be difficult to support from the new single table.

Any reports would need to be updated, and would have to reference the new type column, and reference it correctly (which might seem easy, but....).

Various activities performed by your finance people could be complicated by this. If you ever moved to a double-entry accounting system, then the accounts affecting by the purchase of an item vs the sale of an item might be completely different. You would want them to sign off on the change.

Finally, you'd need to make sure that any auditors you may have to deal with would be OK with the change.

Meanwhile, what's the benefit? You might actually take up more database space (you have all the existing data, plus the need for at least one new column in every row). You've added another field to check to most queries involving the two tables now.

Ultimately, the two entities are logically significantly different, and your new structure hides that complexity, rather than emphasizing it.

All that said, I wouldn't say with 100% certainty that this change couldn't work out well for you - I would just want to be sure that it was very well thought out, that all relevant stakeholders were aware, and agreed - and, perhaps most importantly, that it resolved a real problem or concern about the current structure.


Is SQL Server 2008 going to be obsolete "soon"? I was thinking about it today. We are in 2015. Close to SQL Server 2016, but almost every company I see, is still using SQL Server 2008 R2.

I'm studying SQL Server 2008, 2012 and now, 2014  (5 months school).

My main focus now is SQL Server 2008, because I'm currently working with it, but, should I be worried about it? It would be better to start to focus on SQL Server 2012+?

My company don't even think about an update. We have a lot of software here, and we have 0 problems. And I think, even with 0 problems, is there a need to update?

One good blog I read:
http://www.sqlservercentral.com/articles/Editorial/116525/

Here's everything you need to know about Microsoft Support Lifecycle Policy and from here you can search for specific products lifecycle information. Specific for 2008 family here.

Interestingly enough it was announced on the 18th of July this year that SQL Server 2008 Service Pack 4 update is to be supported until 7/9/2019.
Source


Keep both accented and unaccented versions in a tsvector column? I am facing a situation where users SHOULD submit items that are written in plain English. However, it happens that names have accents - coming from various languages. I have a tsvector column in my table which is indexed for fulltext search.

Here is the info about this column, and relevant info about the table:

                                                         Table "public.companies"
     Column     |          Type          |                       Modifiers                        | Storage  | Stats target | Description
----------------+------------------------+--------------------------------------------------------+----------+--------------+-------------
 id             | integer                | not null default nextval('companies_id_seq'::regclass) | plain    |              |
 name           | character varying(128) | not null                                               | extended |              |
 description    | text                   |                                                        | extended |              |
 tsmeta         | tsvector               |                                                        | extended |              |

Indexes:
    "companies_pkey" PRIMARY KEY, btree (id) CLUSTER
    "companies_search_idx" gin (tsmeta)
Triggers:
    companies_tsmeta_update BEFORE INSERT OR UPDATE ON companies FOR EACH ROW EXECUTE PROCEDURE companies_tsmeta_trigger()

Now the problem comes when the user creates a name like français. Not all the users have the ç character at hand and those who don't will have troubles finding this entry - meaning that it will be stored with the cedilla-c in the tsvector column. So my thought was to use the unaccent extension in order to strip these accents. However now users that just type français will have problems and will not find the entry. So I came up with a function that just concatenates both versions (accented and accent-stripped) of the name and descriptions. However, descriptions can get pretty large and I worry the index will take too much storage.

create or replace function companies_tsmeta_trigger() returns trigger as $$
begin
  new.tsmeta :=
    setweight(to_tsvector('english', coalesce(new.name, '')), 'A') ||
    setweight(to_tsvector('english', unaccent(coalesce(new.name, ''))), 'B') ||
    setweight(to_tsvector('english', coalesce(new.description, '')), 'C') ||
    setweight(to_tsvector('english', unaccent(coalesce(new.description, ''))), 'D');
  return new;
end;
$$ language plpgsql;

What are your suggestions?

My first impulse was this: Use an expression index on the unaccented strings only. Don't store tsmeta in the table redundantly. So you also don't need the trigger. And certainly don't bloat the index with original and unaccented string. This way your table and index are smaller and faster on read and write.

However, The index expression needs to be IMMUTABLE. setweight() and to_tsvector(regconfig,text) are IMMUTABLE, that's good. But unaccent() is only STABLE. Read the detailed assessment here first:

Does PostgreSQL support “accent insensitive” collations?

If you have multiple use cases, create the function wrapper f_unaccent() as instructed over there.

For just the case at hand, we can wrap the whole tsvector creation in a single IMMUTABLE function:

CREATE OR REPLACE FUNCTION f_tsmeta(_a text, _b text)
  RETURNS tsvector AS
$func$
  SELECT setweight(to_tsvector('english', public.unaccent('public.unaccent', _a)), 'A')
      || setweight(to_tsvector('english', public.unaccent('public.unaccent', COALESCE(_b, ''))), 'B')
$func$  LANGUAGE sql IMMUTABLE;

Assuming that the additional module unaccent is installed in the schema public as explained in the linked answer.

Since name is defined NOT NULL, we don't need COALESCE for that column.

Then the index can look like this:

CREATE INDEX companies_search_idx ON companies USING gin (f_tsmeta(name, description));

Used in queries like:

SELECT * FROM companies
WHERE f_tsmeta(name, description) @@ to_tsquery('english', 'foo');

On second thought, weights are not included in a GIN index. So it may pay after all to include the pre-computed value in the table. The manual:

  GIN indexes store only the words (lexemes) of tsvector values, and not
  their weight labels. Thus a table row recheck is needed when using a
  query that involves weights.

With the demonstrated expression index, the expression has to be re-evaluated when using weights, which adds extra cost.

Redundant column or expression index, it should be an improvement to integrate the unaccent dictionary into a custom TEXT SEARCH CONFIGURATION directly and build index (and table column) using that, as demonstrated by Evan in this related answer:

Creating a case-insensitive and accent/diacritics insensitive search on a field

Simplifies things and should be more efficient.


Centos 6.2 mysql configuration file help I have a new centos 6.2 setup. Below is the configuration file. The machine is going to be fully innodb and it had 8Gb ram. What else must I include in the .cnf?

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

The first thing you should configure is the innoDB buffer pool size. According to this blog entry by Percona, the ideal buffer pool size is 10% larger than your total data size.

Other settings to add: InnoDB log file size, query cache size.

innodb_buffer_pool_size=12G

query_cache_size=500M

innodb_log_file_size=64M


Cannot access Properties page of a database I'm in the process of decommissioning a database server and migrating the databases from one server to another.  I was attempting to get the the properties of the database and received a SQL Error popup.

Property Owner is not available for Database '[database name]'.  This property may not exist for this object, or may not be retrievable due to insufficient access rights.  (Microsoft.SqlServer.Smo)

Facts:

Server 2003 Standard with SP2
SQL Server 2005 Standard - 9.0.1399
I'm a sysadmin, so it shouldn't be perms issue
Had another sysadmin perform the operation with the same error
DBCC CheckDB listed no errors
Able to backup w/o error
Took DB offline/online w/o error

Any suggestions or tests that I can perform and report on?

You could probably have just set the database owner to [sa] (or any valid login really) rather than detach / re-attach.

ALTER AUTHORIZATION ON DATABASE::dbname TO [sa];

This probably happened because the login responsible for creating, attaching or restoring the database initially no longer exists. The UI isn't smart enough to fail gracefully when there isn't a valid owner.


Have to run same queries against many servers (30+), use CURSOR or LOOP I have to do the following 30 times
connect to server with unique IP using TSQL
download a table to temp table
do some query on the temp table (same query 30times)
at end union all the tables for a final solution
Would loop or cursor or some other approach be a better approach?  I don't want to copy paste similar SQL 30 times but that is also an option.
I am currently using linked server to connect to each server

Connecting to other instances without Linked Servers is not convenient unless you use Registered Servers or Central Management Server.
If you have registered servers or CMS, you can execute the same query against all the instances in a group (or all registered servers) at once via SSMS (see the links above). But I don't know if you can output the results to a single table in one instance; you may be stuck with copy & paste to Excel.
If you're willing to go with Powershell, the dbatools module has a very convenient solution in Invoke-DbaQuery.
Invoke-DbaQuery -Query "select fields from table" -SqlInstance instance1,instance2,instance3 will return an object representing the results of the query plus a field with the corresponding instance name that each returned record came from.
From there, you can filter the results with Where-Object, or you can copy the results into a table in another instance with Write-DbaDbDataTable


NHibernate Parameter Sniffing, SQL Server 2005 vs SQL Server 2008 NHibernate automatically uses sp_executesql with different paramters to execute it's data. Now what we're noticing in production is that we're beeing hit by parameter sniffing sometimes. In such a way that query performance isn't just effected it's actually causing timeouts.

I'm not a DBA so i'm somewhat winging it but to me it seems like a query is beeing optimised for a small amount of data (10 rows) and then has to query a big amount (150000 rows) which is grinding it to a halt.

Now i've foundout about the 4136 Trace which disables parameter sniffing entirely for an instance of SQL Server.

Now since we're also in the process of upgrading some of our servers i'm wondering if NHibernate behaves different on SQL Server 2008 then it does in 2005. Does it for example add the OPTIMISE FOR UNKOWN bits and pieces.

As far as i know some "tools" have been added into SQL 2008 to allow for better ways of handling these parameter sniffing issues. I'm very curious if NHibernate has taken these tools and uses them or if we change to SQL Server 2008 we'd still have to use this trace or something simular to handle these issues.

I don't know which version of NH you're using, but I'm using 3.1 with SQL 2008 and it does not have any special features to handle Parameter Sniffing. I know this because I've suffered quite a bit trying to overcome this issue.

I don't know if you use HQL/Criteria/QueryOver/LINQ, but I found two ways to (kind of) solve this:

Appending "option(recompile)" on the query -> with an hook on the query execution if you're using the likes of QueryOver/LINQ.

Although this works, it no longer fetches existing query plans from the cache, and every query has to build it ground-up.

Conditionally, add a dummy predicate to the query

Or something that would force a different query plan to be fetched. For example, if the date interval is less than a month add a .Where(x => true). It would then have a different plan for large intervals and a plan for small intervals.


Select Into a temp table in DB2 Let me preface this question in that while I have a strong background in T-SQL but I am still just a newbie when it comes to DB2.

The root query looks roughly like this:

Select
    Col1, Col2, Col3
From (
    Select A.Col1, B.Col2, B.Col3
    From SchemaA.TableA as A
        Inner Join SchemaA.TableB as B
            On A.Id = B.ParentId
    Where
        A.Col2 = 'TypeValue'
    Fetch First 5 Rows Only -- This is not allowed in a union or sub select

    Union

    Select A.Col1, B.Col2, B.Col3
    From SchemaB.TableA as A
        Inner Join SchemaB.TableB as B
            On A.Id = B.ParentId
    Where
        A.Col2 = 'TypeValue'
    Fetch First 5 Rows Only -- This is not allowed in a union or sub select
) as Hybrid

Because Fetch First X Rows is not allowed in subselects or with unions, this isn't going to work. Since we're limiting the output, it makes sense to limit the input.

I figure I can achieve a fake union by feeding my subselects into temp tables instead and returning that output instead.

In T-SQL, I can create a temp table on the fly with a query like this how would I do that in DB2?

Select *
Into #Temp
From TableA

How would I do that in DB2? Or would do I need to create the table before inserting data into it?

Without an order by your query does not really make sense (IMHO). But nevertheless you can still use a query like:

select * from
( select A.x
  from A
  order by x
  fetch first 2 rows only
)
union all
(  select B.x
   from B
   order by x
   fetch first 3 rows only
)
;

I made up a small example for you to play with at Fiddle


Option to uncheck all or invert selection in SSDT? In SQL Server Data Tools (SSDT) after compared the schema, I want to update few changes of mine only (as red-circled in the screenshot).

But there are many other changes are listed in the window, I need to manually uncheck all the other items.

Is there any option to uncheck all or invert selection the items?

For those coming here from Google looking for a solution to this (like me), I found the answer here.

  All you have to do is right click on the section and ta-da, you can Include or Exclude all objects depending on the existing state of the objects.

In this case, section means the Delete, Change, and Add parent folders in the schema compare window.


SQL Server: Transaction log still very large after log backup I have a database that is on Full recovery mode.

I previously made a full backup, and then on a daily basis add differential backups.

Now I have backed up the transaction log for the first time, which reflected in the greatly increased file size of the .bak file. However the log file in the \DATA directory is still the same size.

I was under the impression that the transaction log file would automatically be truncated on a log backup, as mentioned here and elsewhere.

Previously I asked a question on Stack Overflow (here) where the answer indicated that the log file info was not up-to-date with the actual log on disk. Is that the case with this observation?

If so, how can I get the Windows file to refresh to display up-to-date data?

The transaction log does not automatically shrink because you did a backup.  This is actually a good thing because processes actually stop when the log grows.  Your log has grown to the current size because you had processes that required the log to grow to that size.  These could have been long running units of work that generated a lot of log records (like index maintenance).  The log 'truncation' you're referring to means that, after a log backup, log space is available for 'reuse'.  Again, this process does not automatically reduce the size of the log.  To reduce the log size,, you'd need to 'shrink' the log file.  Repeated shrinking of the log is discouraged because it typically just re-grows, which affects performance.  If you do 'shrink' the log, you should monitor the growth rate.  Here is a helpful article.  I also found this article that provides additional information.


Suitability of MySQL and SQLite? I am about to build a C# wpf application. I need to easily port the application e.g. one click installation. I am not sure should I use SQLite or MySQL db? The range of data won't run into billion at most it maybe go upto few millions? What is the best suggestion here? I will have both write and read functionality with also transaction controls for some operation.

The two aren't necessarily mutually exclusive, I have built apps which use MySQL for the production database, but used a SQLite database for testing, because of SQLite's ability to run a database in memory.
i.e. prior to every test, you can build the schema from scratch, seed the database with data and then run your test, once the test finishes the SQLite database is flushed from memory and you can start from scratch with your next test.
You obviously need to be careful that you use code and features that are portable between the 2 systems - MySQL has lots of features that are specific to it for example, ALTER COLUMN - and re-positioning a column after an existing column wont transfer to SQLite.
Because its hard to anticipate the future, you could start out on SQLite and stick to portable SQL as much as possible and then if you find you find you can't achieve your goals on SQLite, move to MySQL.


NULL as a @variable within WHERE clasue I would like to SET 'IS NULL' as a variable and use it in a WHERE clause.
I will be unioning many tables together that carry the same WHERE clause and would like flexibility to use a variable for my yearCompleted column - see example below-

DECLARE @YEARCOMPLETED
SET @YEARCOMPLETED = NULL

SELECT * FROM TABLE1
WHERE yearCompleted = @YEARCOMPLETED
UNION ALL
SELECT * FROM TABLE2
WHERE yearCompleted = @YEARCOMPLETED

In this instance is it possible to find rows where yearCompleted IS NULL using a variable?

This isn't the most elegant solution, but it'll work to return matching rows if @YEARCOMPLETED is a value, or if it is null. This works decently because it's able to make use of an index on the yearCompleted column, but it basically doubles your code with a near duplicate:

DECLARE @YEARCOMPLETED smallint;
SET @YEARCOMPLETED = NULL;

SELECT *
FROM TABLE1
WHERE yearCompleted = @YEARCOMPLETED
UNION ALL
SELECT *
FROM TABLE2
WHERE yearCompleted = @YEARCOMPLETED
UNION ALL
SELECT *
FROM TABLE1
WHERE yearCompleted IS NULL
AND @YEARCOMPLETED IS NULL
UNION ALL
SELECT *
FROM TABLE2
WHERE yearCompleted IS NULL
AND @YEARCOMPLETED IS NULL;

If you don't care about SARGability and using an index on that column, you could do this. It's less duplicated code, but probably with worse performance:

DECLARE @YEARCOMPLETED smallint;
SET @YEARCOMPLETED = NULL;

SELECT *
FROM TABLE1
WHERE COALESCE(yearCompleted,9999) = COALESCE(@YEARCOMPLETED,9999)
UNION ALL
SELECT *
FROM TABLE2
WHERE COALESCE(yearCompleted,9999) = COALESCE(@YEARCOMPLETED,9999);

The most elegant solution, to balance reducing duplicated code and good performance, is to use dynamic SQL:

DECLARE @YEARCOMPLETED smallint;
SET @YEARCOMPLETED = NULL;

DECLARE @SQL nvarchar(Max);

SET @SQL = 'SELECT *
FROM TABLE1
WHERE ';

IF (@YEARCOMPLETED IS NULL)
BEGIN
    SET @SQL = @SQL + ' yearCompleted IS NULL';
END
ELSE
BEGIN
    SET @SQL = @SQL + ' yearCompleted = @YEARCOMPLETED ';
END

SET @SQL = @SQL + ' UNION ALL
SELECT *
FROM TABLE1
WHERE ';
IF (@YEARCOMPLETED IS NULL)
BEGIN
    SET @SQL = @SQL + ' yearCompleted IS NULL';
END
ELSE
BEGIN
    SET @SQL = @SQL + ' yearCompleted = @YEARCOMPLETED';
END

EXEC sp_executesql @stmt = @SQL, @params = '@YEARCOMPLETED smallint', @YEARCOMPLETED = @YEARCOMPLETED;


Grouping operator in DBMS (γ) is duplicate-impervious. Why? I'm reading Database Systems by Ullman where its mentioned that γ is duplicate-impervious. I don't understand why a simple group operation should cause elimination of duplicates. Aliter, Duplicate-elimination operator δ is a special Aggregation operator. Why?

In the book, "Garcia-Molina H., Ullman J., Widom J., Database Systems: The Complete Book. Pearson Prentice Hall, 2009", Chapter 5, "Algebraic and Logic Query Languages", on top of page 217 of the 2nd edition, there is a box titled "δ is a Special Case of γ", that explains how the duplicate removal operation δ is redundant, since it can be replaced by the use of γ.

Simply group by all the attributes of the relation, without any aggregation. This will replace each group, consisting only of tuples wich coincide on all the attributes, with a single tuple. And this is exactly the semantics of the duplicate elimination operator.


Would daily/weekly db memory cache clearing be of use? So memory use is hitting limits. Would clearing the database memory cache on a daily/weekly schedule be of use?

(Ignoring the fact I probably should be looking at reducing size of database/indexes)

Background:

Bunch of SQL Server database (all on one SQL Server instance), one server, and some memory.
Tons of data is written to database, but most of this is just relevant to the current day, its a monitoring system thing. So over a couple of days, the cache will be eaten up by info of the previous day, which may or may not be needed/viewed by anyone for some time, or ever.

So as it writes data, and checks what's in database, it compares against data of the current day. Once the day rolls over, it not comparing against that data any more.

So my thinking is that by clearing the memory at the end of the day, the memory use in the next day will only be relevant of what needed?
Would this be suitable case of FreeSystemCache/DropCleanBuffers

And yes - long term would be better to look a reducing size of database/indexes, however that also falls into what the data being stored is for, which actually makes archiving of data somewhat tricky without some interlinked program logic.

Would clearing the database memory cache on a daily/weekly schedule be of use?

The straight answer is No. This would be a very bad idea, I suggest you don't do this.

  So my thinking is that by clearing the memory at the end of the day, the memory use in the next day will only be relevant of what needed? Would this be suitable case of FreeSystemCache/DropCleanBuffers

You probably need to understand how SQL Server memory works. SQL Server caches as much data pages as possible to avoid physical I/O, which of course is costly. If you clear memory used by database somehow, next day when it has to read pages it would have to bring it back into memory forcing I/O, perhaps lots of I/O on a busy system. Please note reading of database pages is always done in memory. I would also question your assumption about being sure that pages which are read today won't be, in any case, required tomorrow. This is not a correct idea.

It does seems like SQL Server is holding memory so how would it cater to new requests? This is simple, if your request needs pages which are already in memory, read would be done in memory. If the page is not there in memory I/O would be issued to bring it in memory and if space is not there in memory, least used pages would be flushed out to the disk to create space. This happens many a times on busy system without affecting performance.

It's costly for SQL Server to keep on taking and releasing memory because this process involves CPU and other resources; so SQL Server limits these by grabbing as much as it can in cache.

  long term would be better to look a reducing size of database/indexes

If you take my advice and considering you don't have unused indexes, it would be better to increase RAM on system and provide more to SQL Server. Yes, of course your database design must be of a standard which does not have redundant indexes, and properly sized data and log files with an appropriate autogrowth value.


Trying to check if a string contains a number exclusively I have been trying to write a function to check if a string contains a number without that number being part of a bigger number (in other words if the number being searched for is '6' and the string is '7+16+2' it should return false, because the '6' in this string is part of the number '16')

I wrote the function below (It is lengthy but I was going to test it first before refactoring it)

Upon testing I found a bug whereby it only runs the first instance of a found number through the logic.  So running this function with '6' against '16+7+9+6' would return false because it determines that the firrst '6' is part of a bigger number and stops processing.

I figured that to get around this I have to implement a loop to shorten the 'haystack' string (so that, using the example '16+7+9+6' the function continues checking '+7+9+6' after eliminating the first '6') but before spending the time to make an already convoluted function even more convoluted I wanted to check if there is a simpler way to acheive the same goal?

drop function dbo.runners_contain_runner
go
create function dbo.runners_contain_runner(@runner varchar(max), @runners varchar(max))
returns int
as
begin

    /*
    eliminate the plus sign from @runners so that the
    'isnumeric' function doesn't return false positives (it returns 1 for '+')
    */
    set @runners = replace(@runners,'+','_' )

    declare @ret int;
    set @ret = 0;

    -- if the runner is the only runner return 1
    if @runners = @runner
        set @ret = 1
    else
    begin
        declare @charindex int;
        set @charindex = charindex(@runner,@runners)
        if @charindex > 0
        begin

            -- if it is at the beginning then check the char after it
            if @charindex = 1
            begin
                if isnumeric(substring(@runners,@charindex + len(@runner),1)) = 0
                    set @ret = @charindex
            end

            -- if it is at the end then check the char before it
            else if @charindex = len(@runners) - (len(@runner) - 1)
            begin
                if isnumeric(substring(@runners,@charindex - 1,1)) = 0
                    set @ret = @charindex
            end

            -- if it is in the middle check the chars either side of it
            else
            begin
                if isnumeric(substring(@runners,@charindex - 1,1)) +
                isnumeric(substring(@runners,@charindex + len(@runner),1)) = 0
                    set @ret = @charindex
            end
        end
    end
    return @ret
end

Perhaps you are over-complicating this by focusing too much on wanting a number. Take a step back for a moment. What you actually want is a substring without any digits on either side of it. The only way a number could be part of a larger number is to have at least 1 digit on either side of it, right? So as long as you only pass in numbers, then this definition should still produce numbers that do not have any digits on either side.

With that in mind, we just need 3 PATINDEX predicates to cover the passed-in value being on the far left, on the far right, or in the middle. Try the following as it seems to work:

GO
CREATE PROCEDURE #TestFindRunner
(
  @Runner VARCHAR(10)
)
AS
SET NOCOUNT ON;

DECLARE @Data TABLE
(
  [ID] INT NOT NULL PRIMARY KEY,
  [Runners] VARCHAR(50) NULL
 );

INSERT INTO @Data ([ID], [Runners]) VALUES (1, '16+7+9+6');
INSERT INTO @Data ([ID], [Runners]) VALUES (2, '16+7+9+5');
INSERT INTO @Data ([ID], [Runners]) VALUES (3, '26+77+9+5');
INSERT INTO @Data ([ID], [Runners]) VALUES (4, '6+3+45');
INSERT INTO @Data ([ID], [Runners]) VALUES (5, '63,808,111,92');
INSERT INTO @Data ([ID], [Runners]) VALUES (6, '1-7-9,6');
INSERT INTO @Data ([ID], [Runners]) VALUES (7, '1-6-9,7');
INSERT INTO @Data ([ID], [Runners]) VALUES (8, '1-7-9,63');
INSERT INTO @Data ([ID], [Runners]) VALUES (9, '1-63-9,7');
INSERT INTO @Data ([ID], [Runners]) VALUES (10, NULL);
INSERT INTO @Data ([ID], [Runners]) VALUES (11, '6');

SELECT tmp.*
FROM   @Data tmp
WHERE  @Runner COLLATE Latin1_General_100_BIN2 = tmp.[Runners]
OR     PATINDEX('%[^0123456789]' + @Runner COLLATE Latin1_General_100_BIN2,
                tmp.[Runners]) > 0
OR     PATINDEX(@Runner + '[^0123456789]%' COLLATE Latin1_General_100_BIN2,
                tmp.[Runners]) > 0
OR     PATINDEX('%[^0123456789]' + @Runner + '[^0123456789]%'
                COLLATE Latin1_General_100_BIN2, tmp.[Runners]) > 0
GO

And then test with:

EXEC #TestFindRunner 0;
EXEC #TestFindRunner 2;
EXEC #TestFindRunner 4;
EXEC #TestFindRunner 8;
EXEC #TestFindRunner 11;
-- 0 rows

EXEC #TestFindRunner 3;   -- 4
EXEC #TestFindRunner 77;  -- 3
EXEC #TestFindRunner 111; -- 5
-- 1 row

EXEC #TestFindRunner 5; -- 2 and 3
-- 2 rows

EXEC #TestFindRunner 1; -- 6, 7, 8, and 9
-- 4 rows

EXEC #TestFindRunner 6; -- 1, 4, 6, 7, and 11
-- 5 rows

EXEC #TestFindRunner 7; -- 1, 2, 6, 7, 8, and 9
-- 6 rows

EXEC #TestFindRunner 9; -- 1, 2, 3, 6, 7, 8, and 9
-- 7 rows

The reason for having 3 variations of the PATINDEX is that PATINDEX search patterns are not Regular Expressions (RegeEx), contrary to what many people say / think (same with LIKE patterns). PATINDEX and LIKE patterns do not have quantifiers, so it is not possible to specify that the [^0123456789] single character replacement should be "0 or more"; it is "one and only one; no more, no less".

Forcing the binary collation (i.e. the COLLATE Latin1_General_100_BIN2 after each @Runner reference) ensures that we are only dealing with these 10 decimal digits and not any other character that might be considered equivalent

To put the above logic into an Inline Table-Valued Function (TVF) so that it is easier to use (and more efficient than a similarly easy-to-use scalar UDF), try the following:

USE [tempdb];
GO
CREATE FUNCTION dbo.IsRunnerPresent
(
  @Runner VARCHAR(10),
  @Runners VARCHAR(8000)
)
RETURNS TABLE
WITH SCHEMABINDING
AS RETURN

  SELECT CONVERT(BIT,
    CASE WHEN @Runner COLLATE Latin1_General_100_BIN2 = @Runners
           OR PATINDEX('%[^0123456789]' + @Runner
                       COLLATE Latin1_General_100_BIN2, @Runners) > 0
           OR PATINDEX(@Runner + '[^0123456789]%'
                       COLLATE Latin1_General_100_BIN2, @Runners) > 0
           OR PATINDEX('%[^0123456789]' + @Runner + '[^0123456789]%'
                       COLLATE Latin1_General_100_BIN2, @Runners) > 0
           THEN 1
         ELSE 0
      END) AS [RunnerFound];
GO

And then test with:

DECLARE @Runner VARCHAR(10);
SET @Runner = '6';

DECLARE @Data TABLE
(
  [ID] INT NOT NULL PRIMARY KEY,
  [Runners] VARCHAR(50) NULL
 );

INSERT INTO @Data ([ID], [Runners]) VALUES (1, '16+7+9+6');
INSERT INTO @Data ([ID], [Runners]) VALUES (2, '16+7+9+5');
INSERT INTO @Data ([ID], [Runners]) VALUES (3, '26+77+9+5');
INSERT INTO @Data ([ID], [Runners]) VALUES (4, '6+3+45');
INSERT INTO @Data ([ID], [Runners]) VALUES (5, '63,808,111,92');
INSERT INTO @Data ([ID], [Runners]) VALUES (6, '1-7-9,6');
INSERT INTO @Data ([ID], [Runners]) VALUES (7, '1-6-9,7');
INSERT INTO @Data ([ID], [Runners]) VALUES (8, '1-7-9,63');
INSERT INTO @Data ([ID], [Runners]) VALUES (9, '1-63-9,7');
INSERT INTO @Data ([ID], [Runners]) VALUES (10, NULL);
INSERT INTO @Data ([ID], [Runners]) VALUES (11, '6');

SELECT tmp.[ID],
       tmp.[Runners],
       fnd.[RunnerFound]
FROM   @Data tmp
CROSS APPLY dbo.IsRunnerPresentTVF(@Runner, tmp.[Runners]) fnd;

Which returns:

ID    Runners          RunnerFound
1     16+7+9+6         1
2     16+7+9+5         0
3     26+77+9+5        0
4     6+3+45           1
5     63,808,111,92    0
6     1-7-9,6          1
7     1-6-9,7          1
8     1-7-9,63         0
9     1-63-9,7         0
10    NULL             0
11    6                1


Exclude certain table data for all non public schemas (tenants) I have tried running the pg_dump command with the following flag:

--exclude-table-data foo_bar

However it seems this flag is ignored completely if the public schema does not contain a foo_bar table. In my case foo_bar only exists for tenant schemas. Is there an undocumented function to achieve this?

Thanks to @a_horse_with_no_name's comment I figured out the correct wildcard syntax

--exclude-table-data='*.foo_bar'


Script all tables in a database In SQL Server 2008 - is there any script available which will list all tables (incl column names, data type, length) for the entire database - with one script.

You can use below script :

SELECT QUOTENAME(SCHEMA_NAME(tb.[schema_id])) AS 'SchemaName'
    ,QUOTENAME(OBJECT_NAME(tb.[OBJECT_ID])) AS 'TableName'
    ,C.NAME AS 'ColumnName'
    ,T.NAME AS 'DataType'
    ,C.max_length
    ,C.is_nullable
FROM SYS.COLUMNS C
INNER JOIN SYS.TABLES tb ON tb.[object_id] = C.[object_id]
INNER JOIN SYS.TYPES T ON C.system_type_id = T.user_type_id
WHERE tb.[is_ms_shipped] = 0
ORDER BY tb.[Name]

For Complete Documenting sql server, I highly recommend :

SQL Server & Windows Documentation Using Windows PowerShell written by Kendal Van Dyke

Brief description from the link :

  SQL Power Doc is a collection of Windows PowerShell scripts and modules that discover, document, and diagnose SQL Server instances and their underlying Windows OS & machine configurations. SQL Power Doc works with all versions of SQL Server from SQL Server 2000 through 2012, and all versions of Windows Server and consumer Windows Operating Systems from Windows 2000 and Windows XP through Windows Server 2012 and Windows 8. SQL Power Doc is also capable of documenting Windows Azure SQL Databases.


SQL Server Threshold of Write per X when considering Indexes? I am not entirely sure if this is even a question worth asking. However, I was curious as if there was some sort of standard, or specifically a metric, that some more seasoned DBA's use to gauge whether or not an index would be worth adding based on the general activity with the table. I mean anecdotally, yes we all understand tables that get written too frequently shouldn't have too many indexes and the more you have the bigger the issue is/can be. However, I was curious as to whether or not there is a generic standard which can be approach using some sort of formula?

I know through my research there's a lot of "it depends answer" because all environments are not the same, not too mention all infrastructures differ as well. I was hoping one of you smarter individuals would be so nice as to bestow some wisdom on me.

To add to it, I just assumed that there was some sort of "sweet spot"
  that could be evaluated by looking some numbers. How likely/difficult
  is it to generate that information?

I do not think that there is a golden ticket measure to get the index writes <> index reads.

One of the reasons for this is that the sys.dm_db_index_usage_stats dmv explained below might show:

  10 USER_SEEKS vs. 200000 USER_UPDATES.

When looking at this difference the knee jerk reaction would be to say remove this index as it is not used that much.

But these 10 index seeks might be making a very important query go from 5 minutes to 5 seconds. While these 200000 user inserts might be single insert statements on a very big table.

These inserts might even have happened during non production hours for all we know.

Things to look at

This is not a complete list, simply listing a few things you could look at

Index Usage stats

The index usage stats from the sys.dm_db_index_usage_stats dmv Query source (Greg Robidoux) to get a basic idea of the SEEKS / SCANS / LOOKUPS vs. USER_UPDATES.

I would use this one to check if the index is even used and to get a basic idea of the  READS <> WRITES

SELECT   OBJECT_NAME(S.[OBJECT_ID]) AS [OBJECT NAME],
         I.[NAME] AS [INDEX NAME],
         USER_SEEKS,
         USER_SCANS,
         USER_LOOKUPS,
         USER_UPDATES
FROM     SYS.DM_DB_INDEX_USAGE_STATS AS S
         INNER JOIN SYS.INDEXES AS I
           ON I.[OBJECT_ID] = S.[OBJECT_ID]
              AND I.INDEX_ID = S.INDEX_ID
WHERE    OBJECTPROPERTY(S.[OBJECT_ID],'IsUserTable') = 1;

Note that these counters reset each time you restart SQL Server.

Datatypes & Table Size (+ Future growth)

After this, I would look at the datatypes of the key columns / included columns you want to create the index on + the total Data size / Index size to get a general idea of the amount of data you are adding to your database.

An example:

The total data space of my table = 6MB, and I only have 0,1MB index space.

In most cases, indexing this table to remove a query that gets executed a lot and uses too much resources would be a no brainer.

Most of the time, a query you need indexed would be on bigger tables.

In these cases it comes down to:

The data you are indexing (INT?, VARCHAR(100)?, datetime?,...)
The size of your table
The queries / access ratios on the clustered index / heap table.

2 thirds of these you can easily find, the other one you can find with the DMV and by monitoring the queries executing on this table.

After validating this, and proceeding

These two points are not the only ones to look at, there is much more than this.

Another important part is to test & monitor

Most of the time, you will not be 100% sure when applying indexes, it comes down to using common sense and looking at the most obvious parts. The unexpected can always happen.


How to restore a database with SMK and Cerificate to another computer On ComputerA, I have a database with SMK and a certificate. I've created this with the following code:

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'Password123';
GO

CREATE CERTIFICATE Certificate1
WITH SUBJECT = 'Protect Data';
GO

CREATE SYMMETRIC KEY SymmetricKey1
WITH ALGORITHM = AES_128
ENCRYPTION BY CERTIFICATE Certificate1;
GO

Now I have created a backup file for this to restore it on ComputerB. The restore works fine but when I try to execute the following code on ComputerB:

OPEN SYMMETRIC KEY SymmetricKey1
DECRYPTION BY CERTIFICATE Certificate1;
GO
-- Now list the original ID, the encrypted ID
SELECT Customer_id, Credit_card_number_encrypt AS 'Encrypted Credit Card Number',
CONVERT(varchar, DecryptByKey(Credit_card_number_encrypt)) AS 'Decrypted Credit Card Number'
FROM dbo.Customer_data;

 -- Close the symmetric key
CLOSE SYMMETRIC KEY SymmetricKey1;

It returns the error:

Please create a master key in the database or open the master key in the session before performing this operation.
The key 'SymmetricKey1' is not open. Please open the key before using it.

I've tried deleting the SMK and Cert. under the Security Folder it still returns the same error. Any idea how to solve this?

I think you might be encountering the same problem as How to restore symmetric key on the new database?.  Excerpted from the accepted answer on that post:

  A symmetric key that was created without specifying the KEY_SOURCE and
  IDENTITY_VALUE can never be scripted or copied.

If you can recreate the symmetric key on the source database and specify the KEY_SOURCE and IDENTITY_VALUE, I think you will be able to open it on the target server.

Referencing Create Identical Symmetric Keys on Two Servers:

  In order to decrypt ciphertext, you need the key that was used to
  encrypt it. When both encryption and decryption occur in a single
  database, the key is stored in the database and it is available,
  depending on permissions, for both encryption and decryption. But when
  encryption and decryption occur in separate databases or on separate
  servers, the key stored in one database is not available for use on
  the second database

From that post, here is an example of creating a symmetric key using  KEY_SOURCE and IDENTITY_VALUE:

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'My p@55w0Rd';
GO
CREATE CERTIFICATE [cert_keyProtection] WITH SUBJECT = 'Key Protection';
GO
CREATE SYMMETRIC KEY [key_DataShare] WITH
    KEY_SOURCE = 'My key generation bits. This is a shared secret!',
    ALGORITHM = AES_256,
    IDENTITY_VALUE = 'Key Identity generation bits. Also a shared secret'
    ENCRYPTION BY CERTIFICATE [cert_keyProtection];
GO

On the target server, after your restore, you might need to run the following:

-------------------------------------------------------------------------------------
-- When a backup is restored to a different server, the following script must be run
-- so the encrypted data will be accessible with the standard stored procedures.
-- Run this in the database containing the encrypted data.
-------------------------------------------------------------------------------------
OPEN MASTER KEY DECRYPTION BY PASSWORD = '<original password>';
GO
-------------------------------------------------------------------------------------
-- This will alter the master key to drop service master key encryption
-- The service master key is server-specific so it's tied
-- to the server the backup came from.
-------------------------------------------------------------------------------------
ALTER MASTER KEY DROP ENCRYPTION BY SERVICE MASTER KEY;
GO
-------------------------------------------------------------------------------------
-- This will alter the master key to add new service master key
-- encryption based on the current server.
-- This is done so you don't have to open the master key
-- with a password each time you use the certificate and symmetric key.
-------------------------------------------------------------------------------------
ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY;
GO
CLOSE MASTER KEY;


Ubuntu Multiple MySQL instances and Apparmor I am trying to set up multiple instances of MySQL on an Ubuntu dev machine and running into AppArmor problems.

I get this error in the syslogs:

kernel: [227631.776360] type=1400 audit(1329786835.208:146): apparmor="DENIED"
    operation="mknod" parent=7957 profile="/usr/sbin/mysqld"
    name="/run/mysqld/mysqld-5-5-20.sock" pid=8066 comm="mysqld"
    requested_mask="c" denied_mask="c" fsuid=114 ouid=114

mysqld_safe: mysqld from pid
    file /usr/local/mysql-5.5.20/data/naveen-desktop-ubuntu.pid ended

I checked threads here that suggest to use mysql_multi - but I do not know if mysql_multi allows instances of servers with different versions to be run.

I already have mysql-5.1 running and want to install mysql-5.5.

I know there has to be something wrong the way I have configured AppArmor - but no help from Google!

Here are details and what I have done so far:

OS : Ubuntu 11.10 desktop 32 bit
MySQL installed: default Ubuntu package 5.1.58-1ubuntu1
Trying to install: mysql-server 5.5.20
running all commands as root

To keep most of the new installation files separately into a single folder (/usr/local/mysql-5.5.50) I created 4 new directories under the main mysql-5.5.50

source: downloaded and unzipped MySQL files
data : to hold data (owned by MySQL)
log : for all log files
tmp : for tmp files

user=mysql has write access to the entire top folder mysql-5.5.50
created new my.cnf file in the same dir with these details:

[client]
port        = 3307
socket      = /var/run/mysqld/mysqld-5-5-20.sock

[mysqld_safe]
socket      = /var/run/mysqld/mysqld-5-5-20.sock
nice        = 0

[mysqld]
user        = mysql
socket      = /var/run/mysqld/mysqld-5-5-20.sock
port        = 3307
basedir     = /usr
datadir     = /usr/local/mysql-5.5.20/data
tmpdir      = /usr/local/mysql-5.5.20/tmp

log_error   = /usr/local/mysql-5.5.20/log/error.log

AppArmor details:

/etc/apparmor.d/usr.sbin.mysqld has the line #include  at the end.

I added below lines to local/usr.sbin.mysqld:

/usr/local/mysql-5.5.20/* r,
/usr/local/mysql-5.5.20/** rwk,
/{,var/}run/mysqld/mysql-5.5.20.pid w,
/{,var/}run/mysqld/mysql-5.5.20.sock w,

I've restarted AppArmor with /etc/init.d/apparmor restart.

MySQL install:

mysql_install_db --user=mysql --datadir=/usr/local/mysql-5.5.20/data/

This command completed with PLEASE REMEMBER TO SET A PASSWORD FOR THE MySQL root USER ! and I can see that /data now has files.

Start MySQL

/usr/bin/mysqld_safe --defaults-file=/usr/local/mysql-5.5.20/my.cnf

Now I get the error in the syslog.

What am i doing wrong?

I ran into apparmor headaches getting multiple instances going on ubuntu myself.  Your configs look legit but I'm not super familiar w/ apparmor to spot a subtle problem if one lied there.  In my case I was just running multiple copies of the same binaries w/ different cnfs

Your initial error cited

name="/run/mysqld/mysqld-5-5-20.sock"

It seems maybe there's a config option that didn't come through in what you pasted that's looking to work with /run (instead of /var/run) ?


Is heap a table without indexes at all or without a clustered index? BOL seems to define a heap as a table without a clustered index.

But many online posts seem to equate a heap to a table without any kind of indexes.

Is there any subtlety I am not aware of?

Thanks

You need to be very careful what you read on the interwebs ;-) (of course, that also goes for this answer or pretty much anything anywhere, but still). Just as there is a lot of good information out there, there is also a lot of misinformation (and sadly, this is not confined to technical info). And people copy and paste / repost / share both. So, it is good to ask the question :-).

Eventhough BOL does have some mistakes, in this case (and most cases) it is correct: a Heap is specifically a table without a Clustered index. It has nothing to do with non-Clustered indexes. Of course, this could be a simple case of misunderstanding what is being said in these "online posts" since "a table without any kind of indexes" is a Heap, but only because having no indexes means that there is no Clustered index. However, if these posts claim that a table with only non-Clustered indexes is not a Heap, then they would definitely be incorrect.

If you look at sys.indexes you will see that index_id of 1 is a Clustered Index and 0 is a Heap. A Table will have one or the other. It is not possible to have both. Non-clustered indexes start at index_id of 2 and go up from there. All tables will have an index_id of either 0 or 1 and optionally one or more of index_id >= 2.

You can even test this by using the following query:

SELECT COUNT(*)
FROM   sys.indexes si
WHERE  si.index_id IN (0, 1)
GROUP BY si.[object_id]
HAVING   COUNT(*) > 1;

It should never return a row.

Here is a second test which is even more obvious and does not allow for someone to speculate that the condition can exist but just doesn't, which I suppose is possible with the test above:

-- DROP TABLE #tmp;
CREATE TABLE #tmp (Col1 INT, Col2 INT);
SELECT * FROM tempdb.sys.indexes si WHERE si.[object_id] = OBJECT_ID(N'tempdb.dbo.#tmp')
-- 1 row; index_id = 0 and type_desc = HEAP

CREATE NONCLUSTERED INDEX [IX_#tmp] ON #tmp (Col2 ASC);
SELECT * FROM tempdb.sys.indexes si WHERE si.[object_id] = OBJECT_ID(N'tempdb.dbo.#tmp')
-- 2 rows; index_id = 0 / HEAP and index_id = 2 / NONCLUSTERED

CREATE CLUSTERED INDEX [CIX_#tmp] ON #tmp (Col1 ASC);
SELECT * FROM tempdb.sys.indexes si WHERE si.[object_id] = OBJECT_ID(N'tempdb.dbo.#tmp')
-- still 2 rows (not 3!!); index_id = 1 / CLUSTERED and index_id = 2 / NONCLUSTERED

The point of this second test is that the authority on what is and is not true about SQL Server is always going to be SQL Server itself. So it is important to test to essentially ask SQL Server the question, and not a human. Even the experts are sometimes wrong, but SQL Server is always correct (with regards to questions about SQL Server, of course).

Also, while this is not "proof" in any definitive way, the title of the following MSDN page is rather telling:

Heaps (Tables without Clustered Indexes)


Why is the query in ETL via SSIS slow but via a local stored procedure it is fast? I'm seeing the classic 'runs fast in studio manager but slow in application' problem. Sounds like it might be parameter sniffing. However, my experience with ETL and SSIS is zero.
From the DBA I received the following query and it ends with a ?  instead of a parameter. Here is an obfuscated sample of the query:
SELECT
tablex.x_id,
tablex.create_ts,
tablex.update_ts,
tablex.myStatus,
tablex.x_type,
tablex.ami_uploaded,
tablex.work_id,
tablex_capture_ts,
[column1],
[column2],
[column3],
[column4]
FROM     sqltable..tablex
INNER JOIN
sqltable..tableWork ON tablex.work_id = tableWork.work_id
WHERE
(tablex.update_ts >= ?)

According to the DBA, the question mark is replaced with a 'time/date' argument that is one hour in the past.
When I execute this same query locally from a stored procedure,
passing in a parameter that is one hour in the past,
it returns in less than a second. (which to me, means it 'can' use the existing index)
Watching this execute from the ETL, it takes minutes and the
execution plan shows table scans.
There is an update_ts index.

The query engine recommends a second update_ts index with several include columns. I'd like to avoid that if possible as it will add memory pressure and I'm not convinced it solves the real issue. Thoughts?
This seems like a case of the query statistics being skewed and when the query engine sniffs the parameter it avoids using the existing index because the estimated number of rows is beyond the threshold.
My questions:

How does the ? in the SSIS query get handled by sql server? I know parameter sniffing is a complex issue. I've been studying this: http://www.sommarskog.se/query-plan-mysteries.html
If it is the query engine sniffing the parameter (of one hour in the past) and thinking the estimated number of rows is beyond the tripping point, what do I do to fix this?  The DBA has refused the hint  OPTIMIZE for RECOMPILE as an option and I can't say I disagree. (He has a point regarding the bug history) However, these queries happen ONLY from the ETL on schedule times and perhaps that is reason enough to use the HINT regardless of the potential bug??

Also, this is a long issue I've been struggling with. All of these posts are related to this same issue. What a voyage of discovery:
Is this an excessively large 'Lock' time and is it indicative of a problem?
SQL Server - Can I surgically remove a bad cached query plan or am I chasing the wrong idea?
Any advice is greatly appreciated.

UPDATE 1
This should be the actual execution plan from the local stored procedure version. This version returns within 1 second and exhibits the behavior I WISH the ETL would have:
https://www.brentozar.com/pastetheplan/?id=ry4wy6dBO
UPDATE 2
Now, this is a screen grab of the ETL version that takes minutes to complete. Sorry I cannot provide more details on this particular query:

UPDATE 3
This is a screen grab from a profiler trace done over an hour period. I think this is how the ETL commands are being executed.  I don't yet, know why these all have the same time. I also need to find the prepare as well. Look at those cpu, reads, and duration columns!

We had a similar issue with a query that was called from EntityFramework.
Was fast in SSMS, but slow from application.
It turned out there was an error in mapping of parameters, their type, which caused the query from application to do a scan, due to the query becomming non-SARGable.
After fixing this issue, the query was fast from application.


How to trunc double precision number without rounding? I have '9.999999999'::double precision number and I want to convert it to decimal(8,2) without any rounding. The result I am expecting is 9.99. So far I tried:

select trunc('9.999'::double precision::numeric(8,3), 2)

and it works but I have to know number of precision digits for double precision value because if there will be more 9 then ::numeric(8,3) will automatically round it to 10.000.

What I can do about it?

Why not simply do the following?

SELECT trunc(num, 2)
 FROM (VALUES (         9.999999                  ),
              (         9.999999999999999999999999),
              (9999999999.999999999999999999999999)
      ) t(num);

/*
     trunc
───────────────
          9.99
          9.99
 9999999999.99
*/

This way you just truncate it, without assuming anything about the digits.

Notes:

it looks like you have numbers stored as text.  Avoid this, if you can.  If this is not the case, just don't explicitly cast anything.  If it is, then:
It might not be obvious, but my example is using numeric - this is the type the parser interprets 9.99999, for example:

SELECT pg_typeof(9.99999);
 pg_typeof
───────────
 numeric

That is, use an unlimited numeric in your example.  This would accomodate all your digits.

According to the documentation:

  The types decimal and numeric are equivalent.


designing a database with type and sub type I am trying to build a publications database, and i want to develop a genre relation that has various (maybe look up tables) sub-genres attached. as an example, in the genre relation there would be a row for 'POETRY' but somehow i want to divide that into sub genres like 'PROSE', 'HAIKU' or 'FREE_VERSE'.

what is the best way to design such a thing? would i have a separate look-up table for each row in the genre relation for the various sub-genres? that doesn't seem very flexible. any input would be greatly appreciated.

This seems nearly identical to the following question, and my answer there should be detailed enough to explain the concept (and is just too much to copy and paste here).

Don't know how to transform variable entity into relational table

For your case specifically I would think it should work itself out to be something along the lines of:

Publication table is the main entity with the common properties, a PK on PublicationID, and a PublicationTypeID field that FKs to PublicationType
Lookup table for PublicationType that has a PK of PublicationTypeID. Entries would be the various genres.
Sub-class tables for the genres, such as Poetry, Fiction, etc. Each sub-class table has a PK named PublicationID that also FKs back to the Publication table, and fields for properties that are specific to the particular genre.
Where applicable, sub-class tables have sub-type field, such as PoetryTypeID and FKs to the PoetryType table.
Lookup tables for sub-classes, one table per each sub-class type. The PK would be PoetryTypeID (e.g. Prose, Haiku, Free Verse, etc).

In light of additional details being made available that give context to the scope of the request (specifically the following comment on the question:

  the data files ... are all in fact printed, no audio of image/video files. ... one sub-genre is sufficient, and ... it is possible to have just a genre without any sub sections..

), it seems clear now that the flexibility allowed for by my suggestion above is not needed as sub-types aren't really being stored. Instead, the request now seems to be for simply a means of categorization. For that, if you want a rigid structure of Genre / Sub-Genre such that:

those are the only two levels, and
you always know which level you are dealing with (in queries), as opposed to a dynamic structure where each Publication can be associated with a Genre / Sub-Genre at any level (1 through N) and you would need to query to get the heirarchy

then you can do something along the lines of:

Publication
-------------
PublicationID  INT           NOT NULL  PRIMARY KEY
SubGenreID     TINYINT       NOT NULL  FOREIGN KEY  REFERENCES(SubGenre.SubGenreID)
Title          VARCHAR(200)  NOT NULL
...

SubGenre
-------------
SubGenreID     TINYINT      NOT NULL  PRIMARY KEY
GenreID        TINYINT      NOT NULL  FOREIGN KEY  REFERENCES(Genre.GenreID)
Name           VARCHAR(50)  NOT NULL

Genre
-------------
GenreID        TINYINT      NOT NULL  PRIMARY KEY
Name           VARCHAR(50)  NOT NULL

In this model you know that what is in the Publication table is always the SubGenre, and it always has a parent Genre. You did say that it is possible to not have a SubGenre (though is that really the case?), you can use the same Name for both SubGenre and Genre (e.g. News -> News instead of Poetry -> Bad Teenage).

Or, if you want a dynamic structure of Genre / Sub-Genre such that:

There can be just one level or any number of levels, and
You can only easily tell if you are either at:

a top-level Genre (ParentID IS NULL), or
one or more levels deep in SubGenre-land (ParentID IS NOT NULL).

Else you need to query to get the full hierarchy

then you can do something along the lines of:

Publication
-------------
PublicationID       INT           NOT NULL  PRIMARY KEY
PublicationTypeID   SMALLINT      NOT NULL  FOREIGN KEY
                                              REFERENCES(PublicationType.PublicationTypeID)
Title               VARCHAR(200)  NOT NULL
...

PublicationType
-------------
PublicationTypeID        SMALLINT     NOT NULL  PRIMARY KEY
ParentPublicationTypeID  SMALLINT     NULL      FOREIGN KEY
                                              REFERENCES(PublicationType.PublicationTypeID)
Name                     VARCHAR(50)  NOT NULL

Please note that the ParentPublicationTypeID field is NULLable, which indicates a top-level item when it is NULL. This is the model that Walter Mitty suggested.


SQL server suddenly backups are slow and async_io_completion shows suspended for long time 50GB database - full backup normally takes 14 (10 minutes backup, 4 minutes to compress).  Suddenly the backup execution time has been climbing upward to 3-4 hours.   Backup dest is a Network path.

To eliminate the network as an issue, used backup to 'NUL:' - same results 4 hrs.

During the full backup async_io_completion is showing "suspended" examples below:
(1161299ms)ASYNC_IO_COMPLETION  1pm / 22% complete
(2838894ms)ASYNC_IO_COMPLETION  2pm / 60% complete
(3969124ms)ASYNC_IO_COMPLETION  230pm / 77% complete
(4818588ms)ASYNC_IO_COMPLETION  245pm / 80% complete
(5816122ms)ASYNC_IO_COMPLETION  3pm / 86% complete
(6695511ms)ASYNC_IO_COMPLETION  315pm / 89% complete

checked VLF count is low (less than 100) /  Tried SQL native backup - same results / DBCC checkdb - no errors.

HERE IS THE KICKER....Took a full backup of this 50gb and created a new db on the same SQL instance (alongside orig db).  Executed a full backup on the newly created db and the backup time is back to normal 14 minutes.

Anyone has suggestions on why the newly copy of the same db would perform correctly?  Is there something SQL related that can be checked/verified to determine root cause?  Leaning towards SAN or VM issue, need proof.

Env info:
VM environment
Windows 2016 32GB
SQL Standard 2017 CU16

Check for log file fragmentation

use slow_database
go

select count(*) from sys.dm_db_log_info ( db_id() )

if this number is high (>10,000s) then maybe the log file fragmentation is the culprit


SQL Server engine in one place with database files on NAS My office uses accountant programs which run on SQL Server 2008 R2. The SQL Server engine must be installed on one of the laptops (called "server") and the other laptops (called "clients") connect to it via local network. Database files are stored in the same place as the SQL Server engine, so on the "server" laptop. From the security point of view, this is the most ridiculous solution anyone could have so I'm trying to change that.

Is it possible to MOVE only the database files (.mdf) to the NAS which we own, so the SQL Server engine will still run on the "server" laptop but the database will be stored on secured NAS in other place?

Unfortunately it's impossible to install SQL Server on the NAS because it's Synology so it doesn't have MS compatibility (it does have MySQL though).

To be honest, I wouldn't want my database on a NAS as it is most likely accessed throughout the office and it is also very vulnerable as everyone connects to it. Ideally, your SQL database should be hidden in the deepest parts of your network.

However, I seeing how this doesn't sound like a high end operation, I would see if they can give you the money to get a desktop and install SQL express there to run the database for them. That way all of your clients can just connect to that server from within the network and you have a central point to pull backups from.


If else statement on join query i am working with a Postgresql database

having two tables:
professionals[professional_id,user_id,account_id]

users[user_id email]

accounts[account_id email]

professionals

 [professional_id,user_id,account_id]

    1,          2000,       null
    2,          3000,        120
    3,          4000,        155

users

   [user_id,   email]
    2000,   mike@gmail.com
    3000,   joe@gmail.com
    4000,   jack@gmail.com

accounts

   [account_id ,    email]
   120,          maria@gmail.com
   155,         travis@gmail.com

Here i have to get the email with doing  a join between accounts and professionals.

If account_id is not null we get the email

If the account_id is null we do the join between the users and professionals (we are sure that user_id is always not null)

Here is what i did :

select email from professionals
IF (u.id=m.user2ID)
left JOIN accounts ON accounts.account_id = professionals.account_id
ELSE
LEFT JOIN users  ON users.user_id = professionals.user_id;

Thanks indeed for any help

UNTESTED - You did not provide any DDL or sample data scripts...
IF is a flow control construct that determines which statement block will be executed.
What you need are Conditional Expressions. In your case, the COALESCE function should do the trick, also look at CASE for non null condition predicates.

SELECT COALESCE(accounts.email, professionals.email) AS Email
FROM professionals
LEFT JOIN accounts ON accounts.account_id = professionals.account_id
LEFT JOIN users  ON users.user_id = professionals.user_id;


The purpose of transaction log in Restore First Based on this article , the golden role for restore first

  After restoring full database backup, restore latest differential
  database backup and all the transaction log backup after that to get
  database to current state.

Differential backup saves all the data changes since the last full backup.

I dont understand why must we restore transaction log backup since ( full database backup + differential backup ) should restore my database to its latest state.

My apologies , I cannot think of a better question title

Restoring a full + differential backup restores you to the point in time that the differential backup started. If you just took the differential this second and nothing has happened in the database since it started, then yes, that is the latest state. But it's quite unlikely that you will have a disaster the minute you take a differential backup and that nothing has happened in the meantime.

Which means you can't restore to the point in time right before the disaster - everything that happened between the differential and <now> is gone. If you take a differential every hour, this means that disaster could strike at hh:59:59.9999999 and then you'd have lost almost an hour's worth of data.

This is the whole point of the full recovery model - to minimize data loss. If you're not going to bother taking log backups, then switch to simple.

If you take transaction log backups in between differentials (or just don't bother with differentials, since all they do is reduce the number of transaction log backups you need to restore in the event of a disaster), then your maximum potential data loss is your log backup schedule. Because with log backups, you can apply the following during a restore:

restore latest full backup b
restore latest differential backup d taken after b
restore n log backups taken after d up to point in time t

You need to decide what your tolerance is for data loss in order to determine how often you should be running transaction log backups. If you don't know what your tolerance is, you need to discuss this with management / stakeholders.

Could you just take a differential every minute? Or take full backups every five minutes? Sure. But buy some bigger disks.


How can I insert into a Destination Table from multiple tables concurrently..? I have a monthly single threaded process that restores about 2,000 databases and pumps data into a table then drops the DB. It takes about 18 hours for the insert to complete (Its via an SSIS Package). How can I make this multi threaded; avoiding locking/deadlocks on the destination table. I have thought of partition switching but it won't work because the destination has to end into one partition in the destination table.

This article is for you then.

One quote directly in regards to your question of multi-threading.

  The package property that determines how many executables can run in parallel is MaxConcurrentExecutables, as shown in Figure 2. The default is -1, which means that the control flow engine will use the number of logical processors on the server plus 2. For example, if you’re executing the package with the default setting on a quad-core server, you can have up to six executables running in parallel.

This article will talk about avoiding blocking and locking and should give you enough examples and ideas to give you your own questions to research on. Evaluating what types of locks and blocks will occur and what level of isolation you need will help you avoid blocking and locking.

Deadlocking does not sound like a problem you will encounter based on what you have typed. Here's a good explanation on how it occurs.


Does the size of the database depend on the amount of data stored in the column or on the type of data stored? Does the size of the database depend on the amount of data stored in the column or on the type of data stored?

E.g: if I have  Varchar column and I'm just typing like word (Hello) in it, it will take the same size if I write 255 characters in it?

For your specific case the size on disk will be longer if you type more characters. This is because you defined your column as VARCHAR i.e. "varying-length character". Varying-length fields have to indicate the actual number of character present so they have an additional internal filed to show this. Therefore even empty strings consume some space when held in VARCHAR. If the column had been declared as CHAR the number of characters typed would not affect the space used. The remainder would be filled with spaces.

When looking at the bigger picture things get more complicated. Disk space is only ever consumed in chunks, often called "pages." One page is the minimum amount of disk the DB can request from the operating system and is also the minimum amount that can be read from or written to disk. Defining a table with one column of CHAR(1) and a single row will still consume a whole page of disk, typically 16kB.

Further, compression can be applied. Then the amount of space used depends on the length of the source strings and how similar they are to other strings held on that page. To be clear, the page size is still fixed (16kB) but more rows can be held on that one page compared to uncompressed storage. Then a single IO (which is typically the slowest step in query processing) will return more data, fewer IOs will be needed overall and the average query latency will reduce.

When designing tables it is best to declare the smallest size that will satisfy the actual business requirement. Don't declare them too short because those limits are hard limits and data truncation will occur. But definitely don't get lazy and use the maximum size just because it is easy. The declared sizes affect how many rows can fit on a page and so how many IOs are needed to satisfy a query. They can also affect memory usage, potentially reserving space for data which isn't actually present. Overly-generous size declarations can affect performance.


Help with Access many-to-many relationship (beginner) I'm a beginner with MS Access hoping for some help with a database I am trying to create. This is (will be) a database of land parcels in a given area, and their associated landowners.

Here are my table fields, summarized in Excel (bold are my current PKs):

County Plat contains the parcels; the 'Owner' field is the listed owner for the land and could be an LLC, etc. so could have multiple individuals with varying stakes.

The Owners table associates these 'owners' with their corresponding landholders and their ownership percentage.

Finally, Contact List contains a given landholder's contact details.

So, for example, Parcel 0001 and Parcel 0009 are listed in County Plat as owned by Owner TOFU FAMILY TRUST, and the trust is listed in Owners as owned 50% by Jim Tofu and 50% by Bob Tofu. Their respective contact information is listed in Contact List.

Also, I would prefer not to edit the setup of the County Plat database if possible, as it comes from an outside source.

I would greatly appreciate any advice on how to best set up this database. I am currently getting errors trying to enforce referential integrity, and I'm pretty sure it is due to my lack of understanding of database design. Although I've read articles on it, I'm having difficulty applying those lessons to my own database. It would be really helpful to get some feedback from the pros on how to implement this.

Thank you!

Your design seems almost correct. I can pinpoint the following problems though:

You're using the table Owners for two purposes:

Have a list of owners (as entities/companies/societies)
Have a list of people with stakes in each entity

You need to break Owners in two, for example:

CREATE TABLE Owners (
  OwnerID int autoinc primar key,
  [Entity name] varchar(200)
);
CREATE TABLE Stakeholders (
  OwnerID int not null references Owners,
  PersonID int not null references "Contact List",
  [Ownership %] float not null,
  unique (OwnerID, PersonID)
);

The above requires you modify Contact List to include a PersonID int autoinc not null primary key, since you could have more than one person with the same name and surname.


Are there limitations on using pg_dump on a materialized view such that the data won't be included in the results? I have a database, call it maps, which contains 2 schemas, a & b. In each schema I have a number of tables: a.t1, a.t2, b.t1, b.t2 (plus others). The column sets on each of these tables is different, but there are a number of columns in common.

I have defined a materialised view, a.mv, which brings in the common columns of the 4 tables listed, including a geometry column (which represents a geographic outline).

I want to backup the current contents of the view so that I can restore it on another server. To do this, I use this pg_dump command:

pg_dump -h hostname -U username -t a.mv -f mv.sql maps

What I get as a result is the SQL to define the table, but no data. The view definitely has data in it, because I can select from it in PgAdmin (it was created multiple days ago and the underlying tables haven't been changed since)

I can dump the underlying tables, including data, with (eg)

pg_dump -h hostname -U username -t a.t1 -f t1.sql maps

but not the view. From the limited matches I've found with googling this, what I'm trying should work, but I'm wondering if the presence of a geometry column in the dump is causing the issue (or this might be a complete red herring). FWIW, the total data in the view is fairly substantial - probably around 1GB. However, I've dumped all the underlying tables in schema a successfully (including the 2 tables referenced by the view, and others), which was larger (1.5GB)

Any ideas what the issue could be here? On the face of it, what I'm trying to do should work, but just doesn't and with no indicated errors.

You can not reload data manually to a MV, so it doesn't make much sense for pg_dump to dump the data itself out for a MV.

If you dump the underlying table plus the materialized view, the dump file should load correctly by loading the tables, then refreshing the materialized view from them.  I don't think there is any simple way to give it just the MV name and have it include the dependencies, you have to track those yourself.

If you want the materialized view restored as a stand-alone table, you can stitch those together yourself.  Get the definition of the materialized view from pg_dump -s -t a.mv, but then you will have to edit it to make it create a real table, not an mv.  Then you can dump the data itself with

\copy (select * from mv) to mv.txt

And load it back on the other server with

\copy mv from mv.txt


Enable user authentication in Mongo DB We are at Mongo 3.2. We deployed our first app with mongo without user authentication in db schema.
Now we want to enable authentication at db level but want to avoid application downtime doing the same.
Is it possible in Mongo that we enable this authentication at DB level to avoid anyone to log in and run commands through console.

Application should not take down time.

MongoDB 3.2 and earlier require downtime and a coordinated restart of your deployment and application to enable access control: all clients and members of a deployment must use authentication once enabled. There is a localhost exception that allows you to create the first user on the admin database after authentication is enabled.

With planning this can be a relatively quick process, but to minimize potential downtime I strongly recommend first testing in a representative staging environment.

Enabling access control and authentication is an obvious security measure but still leaves you vulnerable to other possible attacks. For example, you should also Configure TLS/SSL to secure your network communication and restrict remote network access via firewall or perhaps a VPN/VPC between your application servers and your MongoDB deployment.

For a full list of security measures and links to relevant tutorials, see the Security Checklist in the MongoDB manual.

There are several steps that you can test in a staging environment to help ensure the transition goes smoothly, including:

Generate a valid keyfile or x.509 certificate for internal authentication between members of your replica set or sharded cluster.
Write a JavaScript file to create the initial user administrator and any required user accounts/roles.
Ensure that you have properly configured authentication by trying to login without any credentials as well as with valid/invalid credentials.
Test external access to your deployment to confirm your firewall configuration.
Make sure the MongoDB driver you are using has been updated to support MongoDB 3.2 and that your application is able to successfully login after you have enabled authentication and any other security measures.

MongoDB 3.4 --transitionToAuth

Another option worth mentioning for future consideration (although generally not the fastest path to enabling authentication on an existing deployment) would be upgrading to MongoDB 3.4.

MongoDB 3.4 includes a new --transitionToAuth option that enables a rolling upgrade to authentication; see: Enforce Keyfile Access Control in a Replica Set without Downtime.


Does not writing a checkpoint record damage anything? I got an alert

  Event ID: 3619. Could not write a checkpoint record in database 'ID ####' because the log is out of space

I know why it happened, I have addressed the issue, backed up the t-logs, shrunk the log that overgrew and other wise done my best to ensure it does not occur again. The issue that caused the logs to fill was an index rebuild.

Other than checkpoint not getting written and the specific index rebuild not finishing, I don't see anything else going wrong.

Does the failed checkpoint write (by its self) cause any damage that I might need to address?

Reference CHECKPOINT (Transact-SQL)

Does the failed checkpoint write (by its self) cause any damage that I might need to address?

There would be no damage as such, what checkpoint does is it writes dirty pages from buffer pool to the disk reducing the amount of recovery time and amount of work SQL Server has to do after restart or crash recovery. In extreme case if database goes under crash recovery the amount of time taken to recover database would increase.

In your case it is good you found out that it was actually the index rebuild which produced some good amount of logs and eventually failed because of low disk space on log drive. This would just rollback the operation and would bring fragmentation as it was before the rebuild


Unable to convert a table to hypertable due to unique index error I am creating a table using Flask SQL Alchemy as follows:

class Price(db.Model):
    __tablename__ = "prices"
    id = db.Column(db.Integer, primary_key=True)
    country_code = db.Column(db.String(2), nullable=False)
    value = db.Column(db.Float(precision=2), nullable=False)
    start = db.Column(db.DateTime(timezone=True), nullable=False)
    end = db.Column(db.DateTime(timezone=True), nullable=False)
    price_type = db.Column(db.String(32), nullable=False)

and then I want to create the table and convert it into hypertable by doing:

def register_timescale():
    result = db.session.execute(
        "select exists(select * from timescaledb_information.hypertable " +
        " where table_name='prices')")
    is_hypertable = [row[0] for row in result]
    if is_hypertable[0] is False:
        db.session.execute(
            "SELECT create_hypertable('prices', 'start', chunk_time_interval=>INTERVAL '1 day')")
        db.session.commit()

def create_app(app_config):
    app = Flask(__name__)

    app.config.from_object(app_config)

    register_extensions(app)
    register_blueprints(app)
    with app.app_context():
        db.create_all()
        register_timescale()
    return app

I want to create a hypertable of the table prices with the start column being time_column_name as described at create_hypertable and the chunk_time_interval should be 1 day. Meaning, one chunk must have data of 1 day

Example:

One chunk must contain data where start value is 2020-04-19 00:00:00+02 and end value is 2020-04-20 00:00:00+02 (from row 1 to 24 in the below image) and then the second chunk must contain value from 2020-04-20 00:00:00+02 till 2020-04-21 00:00:00+02 (from row 25 to 48 in the below image) and so on.

When I do this, I am getting the following error:

  Exception has occurred: DatabaseError (psycopg2.DatabaseError) cannot
  create a unique index without the column "start" (used in
  partitioning)

  [SQL: SELECT create_hypertable('prices', 'start',
  chunk_time_interval=>INTERVAL '1 day')] (Background on this error at:
  http://sqlalche.me/e/4xp6)

Then, I tried changing the register_timescale() function as follows:

def register_timescale():
    result = db.session.execute(
        "select exists(select * from timescaledb_information.hypertable " +
        " where table_name='prices')")
    is_hypertable = [row[0] for row in result]
    if is_hypertable[0] is False:
        db.session.execute(
            "ALTER DATABASE mydb SET timezone TO 'Europe/Berlin'")
        db.session.execute("SELECT pg_reload_conf()")
        db.session.execute("ALTER TABLE prices ADD PRIMARY KEY (id, start)")
        db.session.execute(
            "SELECT create_hypertable('prices', (id, start), chunk_time_interval=>INTERVAL '1 day')")
        db.session.commit()

And this gives me the following error:

  Exception has occurred: ProgrammingError
  (psycopg2.errors.InvalidTableDefinition) multiple primary keys for
  table "prices" are not allowed

  [SQL: ALTER TABLE prices ADD PRIMARY KEY (id, start)] (Background on
  this error at: http://sqlalche.me/e/f405)

My data looks as follows

Can someone please point out the mistake I am doing?

Thanks

TimescaleDB requires that unique and primary keys include the time dimension column. While only one column can be defined as the time dimension. You can read about limitations in create_hypertable doc.

Since Flask SQL Alchemy requires a primary key, you can define a compound primary key on the time dimension column, which is start in your case, and id. Based on this doc I believe it will be:

class Price(db.Model):
    __tablename__ = "prices"
    id = db.Column(db.Integer, primary_key=True)
    country_code = db.Column(db.String(2), nullable=False)
    value = db.Column(db.Float(precision=2), nullable=False)
    start = db.Column(db.DateTime(timezone=True), primary_key=True)
    end = db.Column(db.DateTime(timezone=True), nullable=False)
    price_type = db.Column(db.String(32), nullable=False)

Then you should be able to execute create_hypertable statement successfully. It should include only column start as the time dimension, i.e.:

SELECT create_hypertable('prices', 'start', chunk_time_interval=>INTERVAL '1 day')

Note that create_hypertable will also create the default index on start. Depending on your application queries you might not need this index and the primary key might be enough. In such case you can drop the index on start either by setting option create_default_indexes to false during the call to create hypertable or by executing DROP INDEX statement afterwards. The best practice section contains more information.


How to configure/setup data at rest encryption in Couchbase 5.1 I am evaluating best database solution for a sizeable application. My basic requirements include:

Data should be encrypted at disk level
Mobile support
Scalable
Enterprise support

I realized Couchbase Server solution would be sufficient for my requirements.
I've tested some of the above with Couchbase Server and got stuck on how data encryption on Couchbase Server is applied.

I could find only few high level documentation related to this topic. Including below official documentations

Encryption at Rest

They say there are 4 possible third party encryption tools that can be used:

Linux Unified Key Setup (LUKS)
Vormetric
Gemalto
Protegrity

But they are not explaining on how to use or configure the above tools.

Question 01

What would be the best recommended encryption tool out of above?

Question 02

Is there any documentation(with samples) on how to setup encryption on Couchbase Server?

See also the Field Level Encryption feature in Couchbase Server 5.5 Enterprise Edition (released last week).  From the docs there, you can select the platform you use.  Note this is not part of Couchbase Mobile (which as of this writing has encryption planned for the 2.1 Couchbase Lite release), but can obviously be used with mobile apps if crypto at the app server works for you.  There's also a specification for how the fields are handled if you wanted to handle it elsewhere.


Why only 8kb per page? I just want to know how they came to 8kb when striking a balance between:
smaller page = more page splits
bigger page = longer to look through to find value

I expect there are many more pros and cons to having a bigger page but if we could start again would we pick 8kb again?

I'm not saying it's the wrong decision and I know it would be a massive change to the product to alter the size.

Also I know 8kb was chosen a fair few years ago now and I wonder does it still make sense when rows are likely to be longer now due to new data types?

At the SQL Server conference I attended in Sydney in 1999, Richard Waymire said that Microsoft's intention was to allow a variable page size in SQL Server 2000 but that it turned out to be really difficult, so they put it in the too-hard-basket and went with 8k as a compromise.


Postgres pg_read_all_stats default role cannot read from my schema I have a postgres user created with the pg_read_all_stats default role permissions which states the user should be able to read as if superuser. This works fine for any of the pg_stat_* tables.

However I need some data from the pg_stats table. These queries do not show rows for my applications in the public schema.

The documentation does not indicate how this table should behave when interacting with my applications public schema. Am I completely out of luck for querying this table with pg_read_all_stats permissions?

Looking at the definition of pg_stats:

SELECT ...
FROM pg_statistic s
  JOIN pg_class c ON c.oid = s.starelid
  JOIN pg_attribute a ON c.oid = a.attrelid AND a.attnum = s.staattnum
  LEFT JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE NOT a.attisdropped AND has_column_privilege(c.oid, a.attnum, 'select'::text)
  AND (c.relrowsecurity = false OR NOT row_security_active(c.oid));

you can see that you need to have SELECT rights on the column in question to see the statistics.

The underlying table pg_statistic can only be examined by the bootstrap superuser.

One way to work around this is to create a function owned by a superuser with SECURITY DEFINER that selects from pg_stats and returns the results.

Make sure to

REVOKE EXECUTE on this function FROM PUBLIC.
ALTER FUNCTION ... SET search_path = pg_catalog, pg_temp;


How to drop a procedure across a lot of databases (SQL Server 2016) I have a simple procedure that exists in over 150 databases. I can delete them one by one, which is the most stupid work to do in the world of software development. Or I can somehow dynamically delete them all in one query, which is smart but doesn't work.

I've tried:

execute sp_msforeachdb 'drop procedure Usages'

I got:

  Cannot drop the procedure 'Usages', because it does not exist
  or you do not have permission.

So I thought maybe I create a cursor for it. Thus I wrote:

declare @command nvarchar(max) = '
drop procedure Usages
';
declare @databaseName nvarchar(100)
declare databasesCursor cursor for select [name] from sys.databases where database_id > 5
open databasesCursor
fetch next from databasesCursor into @databaseName
while @@fetch_status = 0
begin
    print @databaseName
    execute sp_executesql @command
    fetch next from databasesCursor into @databaseName
end
close databasesCursor
deallocate databasesCursor

Again, I got that same message. I thought, maybe it doesn't change the context of the database, thus I prefixed the delete command with the name of the given database, so commands would become similar to drop procedure [SomeDatabase].dbo.Usages, but then I received:

  'DROP PROCEDURE' does not allow specifying the database name as a
  prefix to the object name.

So, I figured out to dynamically execute use @databaseName so that I can drop procedure in the context of that database. But it's not working. What should I do? I'm stuck at the smart method, and I've spent more time than the stupid method now.

You were very close, but forgot a small thing :

execute sp_msforeachdb 'USE [?] drop procedure Usages'

The use [?] will actually go to each db, and execute it there, this should solve your issue.
You could however add something that firsts checks if the procedure exists before dropping it, cause now you'll still get the error if it doesn't exists on the database.

Edit:
A cleaner version would be this:

DECLARE @SQL NVARCHAR(MAX)

SET @SQL = '
USE [?]
IF EXISTS ( SELECT  *
            FROM    sys.objects
            WHERE   object_id = OBJECT_ID(N''Usages'')
                    AND type IN ( N''P'', N''PC'' ) )
drop procedure Usages
'

EXEC sp_MSforeachdb @SQL

It'll check if it exists before dropping, so if there's so such sp, it won't give you an error.

EDIT2:
ElliotMajor made the comment

  I would note that sp_msforeachdb is undocumented and shouldn't be
  used. Plenty of known bugs

And he's right about that. For what you're doing right now it should be fine, but if you're ever in need to put this in some production code, there's a couple replacements for this sp.

Improved versions by :Spaghetti DBA and Aaron Bertrand


Retaining access to table that will be dropped and recreated I have a small GIS table that is is dropped and recreated from a third-party tool called MAPINFO.

Each time I drop and recreate that table I grant access to roles created. Is it possible to retain access even after drop and create?

I cannot run SQL through Mapinfo. It does not provide truncate table - it just drops the table and creates a new one.

Answer left in comments by spaghettidba

Do you have control over the schema where the table gets created?

If so, you could grant privileges on the schema.

Another option is a DDL trigger to grant permissions on the table upon creation.