{"id":605,"date":"2013-05-18T07:16:55","date_gmt":"2013-05-18T05:16:55","guid":{"rendered":"http:\/\/www.extradrm.com\/?p=605"},"modified":"2013-05-18T17:13:28","modified_gmt":"2013-05-18T15:13:28","slug":"t-sql-user-guide","status":"publish","type":"post","link":"https:\/\/www.extradrm.com\/?p=605","title":{"rendered":"T-SQL user guide"},"content":{"rendered":"<p>==================== Calcul d&#8217; un max d&#8217; un champs ====================<br \/>\nUnicode = 2 caract\u00e8res<br \/>\nSQL Server also has a DATALENGTH() function.\u00a0 This function can be used on all data types<br \/>\n(Text, NText or Image data types) in your table.<\/p>\n<pre>\r\nSELECT a020, DATALENGTH(a020)\r\nFROM dbo.notices\r\n\r\n\r\nOrdre d\u00e9croissant :\r\nSELECT a020, DATALENGTH(a020)\r\nFROM dbo.notices\r\nORDER BY 2 DESC\r\n\r\nle maximun :\r\nSELECT TOP 1 a020, DATALENGTH(a020)\r\nFROM dbo.notices\r\nORDER BY 2 DESC\r\n\r\n<\/pre>\n<p>=================================================================<br \/>\nMerge de tables SQL \/ As for performance, so long as the id field(s) in tablea (the big table)<br \/>\nare indexed, you should be fine.<br \/>\n===================================================<\/p>\n<pre>\r\nbegin tran;\r\ndelete from Source1 where exists (select 1 from Depouillement where Depouillement.isn=Source1.isn);\r\ninsert into Depouillement select * from source1;\r\ncommit tran;\r\n\r\noption 2:\r\nbegin tran;\r\ndelete from tablea where exists (select 1 from tableb where tablea.id=tableb.id);\r\ninsert into tablea select * from tableb;\r\ncommit tran;\r\n\r\noption 3:\r\nbegin tran;\r\ndelete from tableb where exists (select 1 from tablea where tablea.id=tableb.id);\r\ninsert into tablea select * from tableb;\r\ncommit tran;\r\n<\/pre>\n<p>============== Incr\u00e9menter un champ type ID =================<\/p>\n<pre>DECLARE @id int\r\nset @id = 0\r\nUPDATE A230 SET id = @id, @id = @id + 1\r\n<\/pre>\n<p>======================= timestamp binary data sql =====<\/p>\n<pre>\r\nselect convert(int, timestamp) as version from products\r\nselect convert(bigint, timestamp) as version from products\r\n<\/pre>\n<p>===================== How to empty Log files ===============<\/p>\n<pre>\r\nbackup log [wahda] with truncate_only\r\ngo\r\nDBCC SHRINKDATABASE ([wahda], 10, TRUNCATEONLY)\r\ngo\r\n<\/pre>\n<p>SECURISER BASE SQL + ACCES INETPUB (Application Web)<br \/>\n=============================================<\/p>\n<pre>\r\nif not exists (select * from master.dbo.syslogins where loginname = N'WidgetStoreDAL')\r\nexec sp_addlogin N'WidgetStoreDAL', N'password', N'WidgetStore', N'us_english'\r\nGO\r\nif not exists (select * from dbo.sysusers where name = N'WidgetStoreDAL')\r\nEXEC sp_grantdbaccess N'WidgetStoreDAL', N'WidgetStoreDAL'\r\nGO\r\n\r\nexec sp_addrolemember N'db_denydatareader', N'WidgetStoreDAL'\r\nGO\r\n\r\nexec sp_addrolemember N'db_denydatawriter', N'WidgetStoreDAL'\r\nGO\r\n\r\n=======================================================================\r\nif not exists (select * from master.dbo.syslogins where loginname = N'DOTNET2010\\ASPNET')\r\nexec sp_grantlogin N'DOTNET2010\\ASPNET'\r\nexec sp_defaultdb N'DOTNET2010\\ASPNET', N'WidgetStore'\r\nexec sp_defaultlanguage N'DOTNET2010\\ASPNET', N'us_english'\r\nGO\r\n\r\nif not exists (select * from dbo.sysusers where name = N'DOTNET2010\\ASPNET')\r\nEXEC sp_grantdbaccess N'DOTNET2010\\ASPNET', N'DOTNET2010\\ASPNET'\r\nGO\r\n\r\nexec sp_addrolemember N'db_denydatareader', N'DOTNET2010\\ASPNET'\r\nGO\r\n\r\nexec sp_addrolemember N'db_denydatawriter', N'DOTNET2010\\ASPNET'\r\nGO\r\n<\/pre>\n","protected":false},"excerpt":{"rendered":"<p>==================== Calcul d&#8217; un max d&#8217; un champs ==================== Unicode = 2 caract\u00e8res SQL Server also has a DATALENGTH() function.\u00a0 This function can be used on all data types (Text, NText or Image data&#46;&#46;&#46;<\/p>\n","protected":false},"author":1,"featured_media":2845,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":[],"categories":[28],"tags":[144,145],"youtube_video":null,"_links":{"self":[{"href":"https:\/\/www.extradrm.com\/index.php?rest_route=\/wp\/v2\/posts\/605"}],"collection":[{"href":"https:\/\/www.extradrm.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.extradrm.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.extradrm.com\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.extradrm.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=605"}],"version-history":[{"count":0,"href":"https:\/\/www.extradrm.com\/index.php?rest_route=\/wp\/v2\/posts\/605\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.extradrm.com\/index.php?rest_route=\/wp\/v2\/media\/2845"}],"wp:attachment":[{"href":"https:\/\/www.extradrm.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=605"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.extradrm.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=605"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.extradrm.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=605"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}